简化SQL语句的一般规则

不同的表述:有一个(复杂的)带有连接、子选择、联合的查询,是否可以(或不)通过使用一些转换规则,将其简化为一个更简单、等价的SQL语句,从而产生相同的结果?

这正是优化器为生存所做的(我不是说他们总是做得很好)。

自从 SQL 是一种基于集合的语言,通常有多种方法可以将一个查询转换为另一个查询。

如此查询:

SELECT  *
FROM    mytable
WHERE   col1 > @value1 OR col2 < @value2

可以转化为:

SELECT  *
FROM    mytable
WHERE   col1 > @value1
UNION
SELECT  *
FROM    mytable
WHERE   col2 < @value2

或者:

SELECT  mo.*
FROM    (
        SELECT  id
        FROM    mytable
        WHERE   col1 > @value1
        UNION
        SELECT  id
        FROM    mytable
        WHERE   col2 < @value2
        ) mi
JOIN    mytable mo
ON      mo.id = mi.id

这看起来更丑,但可以产生更好的执行计划。

最常见的操作之一是替换此查询:

SELECT  *
FROM    mytable
WHERE   col IN
        (
        SELECT  othercol
        FROM    othertable
        )

有了这个:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   o.othercol = mo.col
        )

在一些 RDBMS (像) PostgreSQL ) DISTINCT 和 GROUP BY 使用不同的执行计划,因此有时最好用一个替换另一个:

SELECT  mo.grouper,
        (
        SELECT  SUM(col)
        FROM    mytable mi
        WHERE   mi.grouper = mo.grouper
        )
FROM    (
        SELECT  DISTINCT grouper
        FROM    mytable
        ) mo

VS

SELECT  mo.grouper, SUM(col)
FROM    mytable
GROUP BY
        mo.grouper

在 波斯特雷斯尔 , 独特的 种类和 小组通过 散列。

MySQL 缺乏 FULL OUTER JOIN ,因此可以改写为以下内容:

SELECT  t1.col1, t2.col2
FROM    table1 t1
LEFT OUTER JOIN
        table2 t2
ON      t1.id = t2.id

VS

SELECT  t1.col1, t2.col2
FROM    table1 t1
LEFT JOIN
        table2 t2
ON      t1.id = t2.id
UNION ALL
SELECT  NULL, t2.col2
FROM    table1 t1
RIGHT JOIN
        table2 t2
ON      t1.id = t2.id
WHERE   t1.id IS NULL

但是在我的博客中看到这篇文章,关于如何在 MySQL :

• Emulating FULL OUTER JOIN in MySQL

此分层查询位于 Oracle :

SELECT  DISTINCT(animal_id) AS animal_id
FROM    animal
START WITH
        animal_id = :id
CONNECT BY
        PRIOR animal_id IN (father, mother)
ORDER BY
        animal_id

可以转换为:

SELECT  DISTINCT(animal_id) AS animal_id
FROM    (
        SELECT  0 AS gender, animal_id, father AS parent
        FROM    animal
        UNION ALL
        SELECT  1, animal_id, mother
        FROM    animal
        )
START WITH
        animal_id = :id
CONNECT BY
        parent = PRIOR animal_id
ORDER BY
        animal_id

后者表现得更出色。

有关执行计划的详细信息,请参阅我的博客中的这篇文章:

• Genealogy query on both parents

要查找与给定范围重叠的所有范围,可以使用以下查询:

SELECT  *
FROM    ranges
WHERE   end_date >= @start
        AND start_date <= @end

但在 SQL Server 这个更复杂的查询可以更快地生成相同的结果:

SELECT  *
FROM    ranges
WHERE   (start_date > @start AND start_date <= @end)
        OR (@start BETWEEN start_date AND end_date)

不管你信不信由你,我的博客上也有一篇文章:

• Overlapping ranges: SQL Server

SQL Server 也缺乏有效的方法来进行累积聚合,因此此查询:

SELECT  mi.id, SUM(mo.value) AS running_sum
FROM    mytable mi
JOIN    mytable mo
ON      mo.id <= mi.id
GROUP BY
        mi.id

可以更有效地重写,上帝保佑我,诅咒者(你听我说得对: cursors , more efficiently 和 SQL Server 一句话)。

在我的博客中看到关于如何做到这一点的文章:

• Flattening timespans: SQL Server

在金融应用程序中,有一种常见的查询,用于搜索货币的有效汇率,如 甲骨文公司 :

SELECT  TO_CHAR(SUM(xac_amount * rte_rate), 'FM999G999G999G999G999G999D999999')
FROM    t_transaction x
JOIN    t_rate r
ON      (rte_currency, rte_date) IN
        (
        SELECT  xac_currency, MAX(rte_date)
        FROM    t_rate
        WHERE   rte_currency = xac_currency
                AND rte_date <= xac_date
        )

可以大量重写此查询,以使用允许 HASH JOIN 而不是 NESTED LOOPS :

WITH v_rate AS
        (
        SELECT  cur_id AS eff_currency, dte_date AS eff_date, rte_rate AS eff_rate
        FROM    (
                SELECT  cur_id, dte_date,
                        (
                        SELECT  MAX(rte_date)
                        FROM    t_rate ri
                        WHERE   rte_currency = cur_id
                                AND rte_date <= dte_date
                        ) AS rte_effdate
                FROM    (
                        SELECT  (
                                SELECT  MAX(rte_date)
                                FROM    t_rate
                                ) - level + 1 AS dte_date
                        FROM    dual
                        CONNECT BY
                                level <=
                                (
                                SELECT  MAX(rte_date) - MIN(rte_date)
                                FROM    t_rate
                                )
                        ) v_date,
                        (
                        SELECT  1 AS cur_id
                        FROM    dual
                        UNION ALL
                        SELECT  2 AS cur_id
                        FROM    dual
                        ) v_currency
                ) v_eff
        LEFT JOIN
                t_rate
        ON      rte_currency = cur_id
                AND rte_date = rte_effdate
        )
SELECT  TO_CHAR(SUM(xac_amount * eff_rate), 'FM999G999G999G999G999G999D999999')
FROM    (
        SELECT  xac_currency, TRUNC(xac_date) AS xac_date, SUM(xac_amount) AS xac_amount, COUNT(*) AS cnt
        FROM    t_transaction x
        GROUP BY
                xac_currency, TRUNC(xac_date)
        )
JOIN    v_rate
ON      eff_currency = xac_currency
        AND eff_date = xac_date

尽管这是一个庞大的地狱,后一个查询是 6 时间更快。

这里的主要想法是替换 <= 具有 = ,这需要构建内存中的日历表。到 JOIN 用。

• Converting currencies

下面是使用Oracle8&9的一些例子(当然,有时做相反的事情可能会使查询更简单或更快):

如果圆括号不用于替代运算符优先级,则可以删除圆括号。一个简单的例子是当 where 条款相同: where ((a or b) or c) 等于 where a or b or c .

子查询通常(如果不总是)可以 与主查询合并 简化它。根据我的经验,这通常会显著提高性能:

select foo.a,
       bar.a
  from foomatic  foo,
       bartastic bar
 where foo.id = bar.id and
       bar.id = (
         select ban.id
           from bantabulous ban
          where ban.bandana = 42
       )
;

等于

select foo.a,
       bar.a
  from foomatic    foo,
       bartastic   bar,
       bantabulous ban
 where foo.id = bar.id and
       bar.id = ban.id and
       ban.bandana = 42
;

使用 ANSI加入 将许多“代码猴子”逻辑与WHERE子句中真正有趣的部分分开:前面的查询等价于

select foo.a,
       bar.a
  from foomatic    foo
  join bartastic   bar on bar.id = foo.id
  join bantabulous ban on ban.id = bar.id
 where ban.bandana = 42
;

如果要检查是否存在行,请不要使用 伯爵(*) ,而是使用 rownum = 1 或者将查询放入 where exists 子句只提取一行而不是全部。

• 我想最明显的一点是寻找任何可以用基于SQL“set”的操作替换的游标。
• 在我的列表中的下一个,是寻找任何相关的子查询,这些子查询可以作为一个不相关的查询重新编写。
• 在长存储过程中,将单独的SQL语句分解为各自的存储过程。这样他们就可以得到自己的缓存查询计划。
• 查找可以缩短其范围的事务。我经常在事务内部找到可以安全地在外部的语句。
• 子选择通常可以重新编写为直接连接(现代的乐观主义者善于发现简单的连接)。

正如@quassnoi所提到的,乐观主义者经常做得很好。帮助它的一种方法是确保索引和统计是最新的,并且为您的查询工作负载存在合适的索引。

我喜欢用连接查询替换所有类型的子选择。

这一点很明显:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
          SELECT  *
          FROM    othertable o
          WHERE   o.othercol = mo.col
        )

通过

SELECT  mo.*
FROM    mytable mo inner join othertable o on o.othercol = mo.col

这一个还未被估计:

SELECT  *
FROM    mytable mo
WHERE   NOT EXISTS
        (
          SELECT  *
          FROM    othertable o
          WHERE   o.othercol = mo.col
        )

通过

SELECT  mo.*
FROM    mytable mo left outer join othertable o on o.othercol = mo.col
WHERE   o.othercol is null

它可以帮助DBMS在大的请求中选择好的执行计划。

我喜欢团队中的每个人都遵循一套标准,以使代码可读、可维护、可理解、可清洗等。:)

• 每个人都使用相同的别名
• 没有光标。无回路
• 当你能存在的时候为什么还要考虑
• 缩进
• 编码风格的一致性

这里还有一些东西 What are some of your most useful database standards?

考虑到SQL的性质,您必须完全了解任何重构的性能影响。 Refactoring SQL Applications 是一个很好的重构资源,重点关注性能(见第5章)。

虽然简化可能不等于优化,但简化在编写可读的SQL代码时可能很重要,而这反过来又对能够检查SQL代码的概念正确性(而不是开发环境应该检查的语法正确性)至关重要。在我看来,在理想的环境中,我们会编写最简单、最可读的SQL代码,然后优化器会将该SQL代码重写为运行速度最快的任何形式(可能更冗长)。

我发现将SQL语句看作是基于集合逻辑的非常有用,特别是当我需要组合WHERE子句或计算出WHERE子句的复杂否定时。我用 laws of boolean algebra 在这种情况下。

简化where子句最重要的可能是demorgan定律(请注意,“·”是“and”and“+”是“or”):

• 不(x_·y)=not x+not y
• 不(x+y)=not x not y

这在SQL中转换为:

NOT (expr1 AND expr2) -> NOT expr1 OR NOT expr2
NOT (expr1 OR expr2) -> NOT expr1 AND NOT expr2

这些定律对于简化含有大量嵌套的WHERE子句非常有用。 AND 和 OR 部分。

记住这句话也很有用 field1 IN (value1, value2, ...) 等于 field1 = value1 OR field1 = value2 OR ... . 这允许你否定 IN () 两种方法之一:

NOT field1 IN (value1, value2)  -- for longer lists
NOT field1 = value1 AND NOT field1 = value2  -- for shorter lists

子查询也可以这样考虑。例如,这否定了WHERE子句:

NOT (table1.field1 = value1 AND EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))

可重写为:

NOT table1.field1 = value1 OR NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))

这些法则并不告诉您如何使用子查询将SQL查询转换为使用联接的查询,但是布尔逻辑可以帮助您了解联接类型以及查询应返回的内容。例如,使用表格 A 和 B ,一个 INNER JOIN 就像 A AND B A LEFT OUTER JOIN 就像 (A AND NOT B) OR (A AND B) 它简化为 A OR (A AND B) 和A FULL OUTER JOIN 是 A OR (A AND B) OR B 它简化为 A OR B .

我的方法是学习一般的关系理论,特别是关系代数。然后学习找出SQL中用于实现关系代数(例如通用量化a.k.a.除法)和微积分(例如存在量化)运算符的构造。关键是,在关系模型中没有找到SQL的特性,例如nulls,这可能是最好的重构方式。推荐阅读: SQL and Relational Theory: How to Write Accurate SQL Code By C. J. Date .

在这种情况下,我不相信“大多数子select可以重写为join这一事实”代表了一种简化。

以这个查询为例:

SELECT c 
  FROM T1 
 WHERE c NOT IN ( SELECT c FROM T2 );

使用join重写

SELECT DISTINCT T1.c 
  FROM T1 NATURAL LEFT OUTER JOIN T2 
 WHERE T2.c IS NULL;

加入更详细!

或者,识别构造是在投影上实现反连接 c 例如,伪算法

T1 { c } antijoin T2 { c }

使用关系运算符简化:

SELECT c FROM T1 EXCEPT SELECT c FROM T2;