SQL 教程：如何编写更高质量的查询

The SELECT Statement

写完查询后，首先可以检查 SELECT 语句是否尽量精简。目标是移除不必要的列，只拉取服务于查询目标的数据。

若您有包含 EXISTS 的相关子查询，且只是做存在性检查，尽量在该子查询的 SELECT 中使用常量，而不是实际列值。

请记住，相关子查询会使用外层查询的值。另请注意，虽然在此上下文中 NULL 也可视作“常量”，但它会造成严重困惑！

看下面的示例，理解使用常量的含义：

 SELECT driverslicensenr, name                                    FROM Drivers                                              WHERE EXISTS                                                     (SELECT '1'                                                      FROM Fines                                                       WHERE fines.driverslicensenr = drivers.driverslicensenr);

提示：拥有相关子查询并非总是好主意。您可以考虑用 INNER JOIN 重写以去除它们：

 SELECT driverslicensenr, name                                    FROM drivers                                              INNER JOIN fines ON fines.driverslicensenr = drivers.driverslicensenr; 

The DISTINCT Clause

SELECT DISTINCT 用于仅返回不重复的值。若可避免，应尽量不要使用 DISTINCT。如前例所示，加入该子句通常会增加执行时间。因此务必评估是否确实需要这一步去重才能得到目标结果。

The LIKE Operator

在查询中使用 LIKE 时，若模式以 % 或 _ 开头，将无法使用索引（即便存在索引）。从另一个角度看，这类查询也可能放宽了条件，从而拉取过多与查询目标并不相关的记录。

再次强调，了解数据库中的数据有助于您制定更精准的匹配模式，只筛出真正相关的行。

TOP、LIMIT 与 ROWNUM 子句

您可以在查询中添加 LIMIT 或 TOP 来设置结果集的最大行数。示例如下：

  SELECT TOP 3 *   FROM Drivers;

注意，您还可以进一步指定 PERCENT，例如将第一行改为 SELECT TOP 50 PERCENT *。

 SELECT driverslicensenr, name                                    FROM Drivers  LIMIT 2;

此外，还可添加 ROWNUM 子句，它与 LIMIT 等效：

SELECT *FROM DriversWHERE driverslicensenr = 123456 AND ROWNUM <= 3;

数据类型转换

应尽量使用最高效（即最小）的数据类型。为可用更小类型却提供了超大的类型，往往有风险。

然而，在查询中加入数据类型转换，会增加执行时间。

替代做法是尽量避免类型转换。当然，并非总能移除或省略类型转换；当必须使用时，务必谨慎评估其必要性，并在执行前测试其影响。

The OR Operator

在查询中使用 OR 运算符时，很可能无法使用索引。

请记住：索引是一种数据结构，用于加速从数据库表中检索数据；但它也有代价：需要额外的写入及存储空间来维护索引结构。借助索引，可以在访问表时快速定位数据，而无需每次都逐行扫描整张表。索引可建立在表中的一个或多个列上。

若未能利用数据库中的索引，查询势必更慢。因此应尽量寻找替代写法来避免使用 OR；

考虑下面的查询：

SELECT driverslicensenr, nameFROM DriversWHERE driverslicensenr = 123456OR driverslicensenr = 678910OR driverslicensenr = 345678;

可以替换为：

• 使用 IN 条件；或

SELECT driverslicensenr, nameFROM DriversWHERE driverslicensenr IN (123456, 678910, 345678);

• 使用两个 SELECT 再以 UNION 合并。

提示：谨慎使用 UNION，因为会多次扫描同一张表。同时要意识到，使用 UNION 会增加执行时间。UNION 的替代方案包括：把所有条件合并到一个 SELECT 语句中，或用 OUTER JOIN 代替 UNION。

提示：还要注意，尽管 OR（以及后文提到的其他运算符）通常无法利用索引，但索引查找也并非总是优选！

The NOT Operator

当查询包含 NOT 运算符时，也很可能无法使用索引，与 OR 类似，从而拖慢查询。例如：

SELECT driverslicensenr, nameFROM DriversWHERE NOT (year > 1980);

这条查询比您预期更慢，主要因为写得过于复杂。更好的做法是用比较运算符（如 >、<> 或 !>）替换 NOT。上述示例可改写为：

SELECT driverslicensenr, nameFROM DriversWHERE year <= 1980;

是不是更简洁了？

The AND Operator

AND 也是一种可能无法利用索引、且在复杂低效写法下拖慢查询的运算符，例如：

SELECT driverslicensenr, nameFROM DriversWHERE year >= 1960 AND year <= 1980;

更好的写法是使用 BETWEEN：

SELECT driverslicensenr, nameFROM DriversWHERE year BETWEEN 1960 AND 1980;

The ANY 和 ALL 运算符

ANY 与 ALL 也需谨慎使用，因为包含它们通常无法利用索引。实用的替代方案包括使用聚合函数，如 MIN 或 MAX。

提示：当使用这些替代方案时，要意识到跨多行的聚合函数（如 SUM、AVG、MIN、MAX）可能导致长时间运行。在此情形下，可尝试减少需要处理的行数，或预先计算这些值。再次说明，做出查询选择时，了解环境与查询目标非常重要！

在条件中“隔离”列

当列参与到计算或标量函数中时，也往往无法使用索引。解决办法是将该列从计算或函数中“隔离”出来。示例如下：

SELECT driverslicensenr, nameFROM DriversWHERE year + 10 = 1980;

看起来有些“花哨”，不如重新思考计算方式，改写为：

SELECT driverslicensenr, nameFROM DriversWHERE year = 1970;

联接

• 表的顺序

当联接两张表时，考虑联接中的表顺序可能很重要。若两表相差悬殊，可将更大的表放在联接的最后。

• 联接中的冗余条件

在联接中加入过多条件，会迫使 SQL 选择特定路径，而该路径未必最优。

SELECT state, COUNT(*)  FROM Drivers WHERE state IN ('GA', 'TX') GROUP BY state ORDER BY state

SELECT state, COUNT(*)  FROM Drivers GROUP BY stateHAVING state IN ('GA', 'TX') ORDER BY state

可见，评估查询质量、编写与重写查询并不轻松，尤其当您要求其尽可能高效时；避免反模式并考虑替代写法，也是您在专业环境中为数据库编写查询时应承担的职责之一。

上述清单只是对部分反模式与提示的简要概览，希望能帮助初学者；若想了解资深开发者眼中的高频反模式，参见这段讨论。

基于集合 vs 过程式 的查询思路

前述反模式背后隐含着一个事实：它们归根结底源于基于集合与过程式两种构建查询的差异。

过程式查询更像编程：您告诉系统要做什么、以及如何做。

例如，在联接中添加冗余条件，或滥用 HAVING，又或通过一个函数再接一个函数地处理，或使用包含循环、条件、自定义函数（UDF）、游标等的逻辑来得到最终结果。在这种思路下，您常常是先要一部分数据，再要另一部分，如此逐步推进。

不出所料，这种方法常被称为“逐步”或“逐行”（row-by-row）查询。

另一种是基于集合的方法，您只需声明要做什么。您的职责是声明结果集需要满足的条件或要求；至于如何取数，则交给内部机制去决定具体实现：让数据库引擎选择执行查询的最佳算法或处理逻辑。

鉴于 SQL 天生是基于集合的语言，这种方法通常要比过程式更高效，也解释了为何在某些情况下，SQL 会比代码更快。

提示：数据科学领域的顶级雇主通常要求您掌握基于集合的查询思路！您常常需要在这两种方法间切换。

注意：若发现自己写出了过程式查询，应考虑重写或重构。

从查询到执行计划

反模式并非一成不变；随着您作为 SQL 开发者的成长，它也会演进。再加上在考虑替代写法时，要权衡的因素很多，这意味着避免反模式与重写查询可能相当困难。此时，借助工具、采取更结构化的方法来优化查询会很有帮助。

同时注意，上一节提到的一些反模式，本质上是性能方面的问题，例如 AND、OR、NOT 的索引利用不足。考虑性能不仅需要更结构化的方法，也需要更深入的分析。

无论如何，这种结构化且深入的方法大多以查询计划为基础。正如您记得的，查询计划是查询被解析为“解析树”后的产物，它定义了每个操作所用的算法，以及操作如何协调执行。

查询优化

如引言所述，您有时需要手动检查并调优优化器生成的计划。在这种情况下，您需要基于查询计划再次分析查询。

要获取查询计划，需使用数据库管理系统提供的工具。常见工具包括：

• 有些套件可生成查询计划的图形表示。例如：

• 其他工具能提供文本描述。Oracle 中的 EXPLAIN PLAN 是一个例子，但不同 RDBMS 的指令名称不同。您也可能看到 EXPLAIN（MySQL、PostgreSQL）或 EXPLAIN QUERY PLAN（SQLite）。

注意：在 PostgreSQL 中，EXPLAIN 仅给出计划器拟如何执行查询的描述（不真正运行）；而 EXPLAIN ANALYZE 会实际执行查询，并返回预期与实际计划的对比分析。一般而言，“实际执行计划”指真正运行了查询；“估算执行计划”则是不执行、仅说明会怎么做。尽管二者在逻辑上等价，但实际计划更有用，因为包含执行中发生的更多细节与统计信息。

本节余下内容将介绍 EXPLAIN 与 ANALYZE，以及如何用它们了解查询计划与潜在性能。我们将从两个表的示例开始：one_million 与 half_million。

您可以用 EXPLAIN 获取表 one_million 的当前信息；将其置于查询最前面，运行后会返回查询计划：

EXPLAINSELECT *FROM one_million;QUERY PLAN____________________________________________________Seq Scan on one_million(cost=0.00..18584.82 rows=1025082 width=36)(1 row)

此处可见，查询代价为 0.00..18584.82，行数为 1025082，列宽为 36。

随后，您可以用 ANALYZE 更新统计信息：

ANALYZE one_million;EXPLAINSELECT *FROM one_million;QUERY PLAN____________________________________________________Seq Scan on one_million(cost=0.00..18334.00 rows=1000000 width=37)(1 row)

除 EXPLAIN 与 ANALYZE 外，还可用 EXPLAIN ANALYZE 获取实际执行时间：

EXPLAIN ANALYZESELECT *FROM one_million;QUERY PLAN___________________________________________________________Seq Scan on one_million(cost=0.00..18334.00 rows=1000000 width=37)(actual time=0.015..1207.019 rows=1000000 loops=1)Total runtime: 2320.146 ms(2 rows)

EXPLAIN ANALYZE 的劣势显而易见：它会真正执行查询，请谨慎使用！

到目前为止，您看到的算法是 Seq Scan（顺序扫描）或全表扫描：即按顺序读取表中每一行，并检查是否满足条件。就性能而言，顺序扫描并非最佳，因为仍是全表扫描。但当表装不下内存时，它也不算差：即便磁盘较慢，顺序读也很快。

关于索引扫描，稍后会看到更多。

当然还有其他算法。比如，以下联接的查询计划：

EXPLAIN ANALYZESELECT *FROM one_million JOIN half_millionON (one_million.counter=half_million.counter);QUERY PLAN_________________________________________________________________Hash Join (cost=15417.00..68831.00 rows=500000 width=42)(actual time=1241.471..5912.553 rows=500000 loops=1)Hash Cond: (one_million.counter = half_million.counter)    -> Seq Scan on one_million    (cost=0.00..18334.00 rows=1000000 width=37)    (actual time=0.007..1254.027 rows=1000000 loops=1)    -> Hash (cost=7213.00..7213.00 rows=500000 width=5)    (actual time=1241.251..1241.251 rows=500000 loops=1)    Buckets: 4096 Batches: 16 Memory Usage: 770kB    -> Seq Scan on half_million    (cost=0.00..7213.00 rows=500000 width=5)(actual time=0.008..601.128 rows=500000 loops=1)Total runtime: 6468.337 ms

可见优化器选择了 Hash Join！记住这一操作，稍后估算时间复杂度会用到。注意，这里 half_million.counter 没有索引，您可以在下例中添加：

CREATE INDEX ON half_million(counter);EXPLAIN ANALYZESELECT *FROM one_million JOIN half_millionON (one_million.counter=half_million.counter);QUERY PLAN________________________________________________________________Merge Join (cost=4.12..37650.65 rows=500000 width=42)(actual time=0.033..3272.940 rows=500000 loops=1)Merge Cond: (one_million.counter = half_million.counter)    -> Index Scan using one_million_counter_idx on one_million    (cost=0.00..32129.34 rows=1000000 width=37)    (actual time=0.011..694.466 rows=500001 loops=1)    -> Index Scan using half_million_counter_idx on half_million    (cost=0.00..14120.29 rows=500000 width=5)(actual time=0.010..683.674 rows=500000 loops=1)Total runtime: 3833.310 ms(5 rows)

可以看到，创建索引后，优化器改用了 Merge join，并出现了 Index Scan。

注意 索引扫描与全表（顺序）扫描的区别：后者逐行扫描整表；前者（也称“表扫描”）会扫描数据页或索引页以定位相应记录。

总运行时间减少，性能更好；但由于出现两次索引扫描，内存的重要性上升，尤其当表装不下内存时。此时需要先完成一次完整索引扫描（快速的顺序读，不是问题），随后进行大量随机读以按索引值取行。正是这些随机读，通常比顺序读慢几个数量级。在这种情形下，全表扫描反而可能比完整索引扫描更快。

O(1)：常数时间

若算法无论输入大小如何，都需相同时间，则为常数时间。对应到查询，即无论表大小如何，运行时间相同。

这类查询并不常见，但例如：

SELECT TOP 1 t.* FROM t

时间复杂度为常数，因为您只从表中选择一行任意记录，所需时间与表大小无关。

线性时间：O(n)

若算法的执行时间与输入规模成正比（输入增大，时间线性增长），则为线性时间。对数据库而言，即执行时间与表大小成正比：行数越多，查询时间越长。

一个例子是对未建索引列使用 WHERE 的查询：需要全表扫描或 Seq Scan，时间复杂度为 O(n)。这意味着必须读取每一行以找到目标行。您没有任何“上限”，即使第一行就匹配，也仍需读取所有行。

再看一个例子：若 i_id 未建索引，下述查询的复杂度为 O(n)：

SELECT i_id FROM item;

• 这也意味着，诸如 COUNT(*) FROM TABLE; 的计数查询，若未存储总行数，通常时间复杂度为 O(n)；若表的总行数被存储，则复杂度更接近 O(1)。

与线性时间密切相关的是包含联接的执行计划。示例如下：

• 哈希联接的期望复杂度为 O(M + N)。经典的内连接哈希联接会先对较小表构建哈希表（键为联接列，值为行）；随后扫描较大表，通过哈希表定位较小表中的匹配行。
• 归并联接一般为 O(M + N)，但高度依赖于联接列上的索引，以及在无索引时，数据是否已按联接键排序：
  • 若两表均已按联接键排序，则复杂度为 O(M + N)。
  • 若两表联接列均有索引，索引已维护列的有序性，无需排序，复杂度为 O(M + N)。
  • 若两表联接列均无索引，则需先对两表排序，复杂度约为 O(M log M + N log N)。
  • 若仅一表有索引，则需先对无索引的那一表排序，再做归并，复杂度约为 O(M + N log N)。
• 嵌套联接一般为 O(MN)。当一张或两张表极小（如不足 10 行）时，该联接较高效。这在查询评估中很常见，因为一些子查询只返回一行。

请记住：嵌套联接会将一表的每条记录与另一表的每条记录比较。

对数时间：O(log (n))

若算法的执行时间与输入规模的对数成正比，则为对数时间；对于查询，即执行时间与数据库大小的对数成正比。

当执行计划进行 Index Scan 或聚簇索引扫描时，通常为对数时间。聚簇索引的叶子层存放表的实际数据行；它与其他索引类似，定义在一个或多个列上，这些列构成索引键，聚簇键即该聚簇索引的键列。聚簇索引扫描本质上是 RDBMS 自上而下地在聚簇索引中读取行。

如下例所示，若 i_id 上有索引，通常复杂度为 O(log(n))：

SELECT i_stock FROM item WHERE i_id = N; 

注意：若无索引，时间复杂度将为 O(n)。

二次时间：O(n^2)

若算法执行时间与输入规模的平方成正比，则为二次时间。对数据库而言，即查询执行时间与数据库大小的平方成正比。

一个可能具有二次时间复杂度的查询示例如下：

SELECT * FROM item, author WHERE item.i_a_id=author.a_id 

其最小复杂度可达 O(n log(n))，但最大复杂度可达 O(n^2)，具体取决于联接属性上的索引信息。

总之，您也可以参考这份速查表，据此估计不同时间复杂度下查询的性能表现：