SQL 优化极简法则，你知道几个？

SQL作为关系型数据库的标准语言，是 IT 从业人员必不可少的技能之一。SQL 本身并不难学，编写查询语句也很容易，但是想要编写出能够高效运行的查询语句却有一定的难度。

查询优化是一个复杂的工程，涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL 语句的执行顺序、索引以及统计信息的采集等，甚至应用程序和系统的整体架构。本文介绍几个关键法则，可以帮助我们编写高效的 SQL 查询；尤其是对于初学者而言，这些法则至少可以避免我们写出性能很差的查询语句。

以下法则适用于各种关系型数据库，包括但不限于：MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等。

只返回需要的结果

一定要为查询语句指定WHERE 条件，过滤掉不需要的数据行。通常来说，OLTP 系统每次只需要从大量数据中返回很少的几条记录；指定查询条件可以帮助我们通过索引返回结果，而不是全表扫描。绝大多数情况下使用索引时的性能更好，因为索引（B-树、B+树、B*树）执行的是二进制搜索，具有对数时间复杂度，而不是线性时间复杂度。以下是 MySQL 聚簇索引的示意图：

举例来说，假设每个索引分支节点可以存储 100 个记录，100 万（1003）条记录只需要 3 层 B-树即可完成索引。通过索引查找数据时需要读取 3 次索引数据（每次磁盘 IO 读取整个分支节点），加上 1 次磁盘 IO 读取数据即可得到查询结果。

相反，如果采用全表扫描，需要执行的磁盘 IO 次数可能高出几个数量级。当数据量增加到 1 亿（1004）时，B-树索引只需要再增加 1 次索引 IO 即可；而全表扫描则需要再增加几个数量级的 IO。

同理，我们应该避免使用 SELECT * FROM， 因为它表示查询表中的所有字段。这种写法通常导致数据库需要读取更多的数据，同时网络也需要传输更多的数据，从而导致性能的下降。

确保查询使用了正确的索引

如果缺少合适的索引，即使指定了查询条件也不会通过索引查找数据。因此，我们首先需要确保创建了相应的索引。一般来说，以下字段需要创建索引：

经常出现在 WHERE 条件中的字段建立索引可以避免全表扫描；

将 ORDER BY 排序的字段加入到索引中，可以避免额外的排序操作；

多表连接查询的关联字段建立索引，可以提高连接查询的性能；

将 GROUP BY 分组操作字段加入到索引中，可以利用索引完成分组。

即使创建了合适的索引，如果 SQL 语句写的有问题，数据库也不会使用索引。导致索引失效的常见问题包括：

在 WHERE 子句中对索引字段进行表达式运算或者使用函数都会导致索引失效，这种情况还包括字段的数据类型不匹配，例如字符串和整数进行比较；

使用 LIKE 匹配时，如果通配符出现在左侧无法使用索引。对于大型文本数据的模糊匹配，应该考虑数据库提供的全文检索功能，甚至专门的全文搜索引擎（Elasticsearch 等）；

如果 WHERE 条件中的字段上创建了索引，尽量设置为 NOT NULL；不是所有数据库使用 IS [NOT] NULL 判断时都可以利用索引。

执行计划（execution plan，也叫查询计划或者解释计划）是数据库执行 SQL 语句的具体步骤，例如通过索引还是全表扫描访问表中的数据，连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想，我们首先应该查看它的执行计划，通过执行计划（EXPLAIN）确保查询使用了正确的索引。

尽量避免使用子查询

以 MySQL 为例，以下查询返回月薪大于部门平均月薪的员工信息：

EXPLAIN ANALYZE

SELECT emp_id, emp_name

 FROM employee e

 WHERE salary > (

   SELECT AVG(salary)

     FROM employee

     WHERE dept_id = e.dept_id);

-> Filter: (e.salary > (select #2))  (cost=2.75 rows=25) (actual time=0.232..4.401 rows=6 loops=1)

  -> Table scan on e  (cost=2.75 rows=25) (actual time=0.099..0.190 rows=25 loops=1)

  -> Select #2 (subquery in condition; dependent)

      -> Aggregate: avg(employee.salary)  (actual time=0.147..0.149 rows=1 loops=25)

          -> Index lookup on employee using idx_emp_dept (dept_id=e.dept_id)  (cost=1.12 rows=5) (actual time=0.068..0.104 rows=7 loops=25)

从执行计划可以看出，MySQL 中采用的是类似 Nested Loop Join 实现方式；子查询循环了 25 次，而实际上可以通过一次扫描计算并缓存每个部门的平均月薪。以下语句将该子查询替换为等价的 JOIN 语句，实现了子查询的展开（Subquery Unnest）：

EXPLAIN ANALYZE

SELECT e.emp_id, e.emp_name

FROM employee e

JOIN (SELECT dept_id, AVG(salary) AS dept_average

        FROM employee

       GROUP BY dept_id) t

  ON e.dept_id = t.dept_id

WHERE e.salary > t.dept_average;

-> Nested loop inner join  (actual time=0.722..2.354 rows=6 loops=1)

 -> Table scan on e  (cost=2.75 rows=25) (actual time=0.096..0.205 rows=25 loops=1)

 -> Filter: (e.salary > t.dept_average)  (actual time=0.068..0.076 rows=0 loops=25)

     -> Index lookup on t using (dept_id=e.dept_id)  (actual time=0.011..0.015 rows=1 loops=25)

         -> Materialize  (actual time=0.048..0.057 rows=1 loops=25)

             -> Group aggregate: avg(employee.salary)  (actual time=0.228..0.510 rows=5 loops=1)

                 -> Index scan on employee using idx_emp_dept  (cost=2.75 rows=25) (actual time=0.181..0.348 rows=25 loops=1)

改写之后的查询利用了物化（Materialization）技术，将子查询的结果生成一个内存临时表；然后与 employee 表进行连接。通过实际执行时间可以看出这种方式更快。

以上示例在 Oracle 和 SQL Server 中会自动执行子查询展开，两种写法效果相同；在 PostgreSQL 中与 MySQL 类似，第一个语句使用 Nested Loop Join，改写为 JOIN 之后使用 Hash Join 实现，性能更好。

另外，对于 IN 和 EXISTS 子查询也可以得出类似的结论。由于不同数据库的优化器能力有所差异，我们应该尽量避免使用子查询，考虑使用 JOIN 进行重写。