上一篇我们探讨了 SQL 查询的执行过程，学会了如何查看和解读执行计划。今天我们来介绍一些常见的查询优化方法和技巧。
首先一点：优化规则千万条，执行计划第一条。不要盲目相信什么规则，包括本文列出的规则；因为数据库优化器在不断改进，许多规则已经或者将来不再适用。不过另一方面，通过执行计划找出性能问题并进行优化的方法不会改变。
一般来说，对于 OLTP 应用减少数据库磁盘 IO 是 SQL 优化需要考虑的首要因素，因为磁盘通常是性能的瓶颈所在。除此之外，还需要考虑降低 CPU 和内存的消耗；DISTINCT、GROUP BY、ORDER BY 等操作都需要涉及大量 CPU 运算，而且还会占用大量内存或者使用临时文件。
本篇使用 MySQL 进行演示，其他数据库同样适用，除非另有说明。
创建合适的索引
索引是优化查询性能的主要方法，因此首先需要考虑创建有效的索引。我们在第 29 篇介绍了索引的原理，现在来看看哪些字段适合创建索引：
- 经常出现在 WHERE 条件或者 ORDER BY 中的字段创建索引，可以避免全表扫描和额外的排序操作；
- 多表连接查询的关联字段，外键涉及的字段；
- 查询中的分组操作字段；
创建索引时还有一些注意事项。首先，尽量选择区分度高的列作为索引，例如各种编号；而性别这种重复性高的字段不适合单独创建索引。其次，对于组合索引，查询条件中最常出现的列放在最左边，这个称为组合索引最左前缀原则。例如：
CREATE TABLE test ( id INT NOT NULL, col1 INT, col2 INT, col3 VARCHAR(100), PRIMARY KEY (id) ); INSERT INTO test WITH RECURSIVE t(id,c1,c2,c3) AS ( SELECT 1 id,1 c1,1 c2,1 c3 UNION ALL SELECT id+1,c1+1,c2+2,c3+3 FROM t WHERE id < 1000 ) SELECT FROM t; 复制
如果经常使用 col1 和 col2 作为查询条件，同时经常使用 col2 作为查询条件，可以创建以下复合索引：
CREATE INDEX idx_test ON test(col2, col1); 复制
注意 col2 在前，col1 在后。我们以 MySQL 为例，查看这两种情况下的执行计划：
EXPLAIN SELECT FROM test WHERE col1 = 6 AND col2 = 9; id|selecttype|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra| —|—————-|——-|—————|——|——————-|————|———-|—————-|——|————|——-| 1|SIMPLE |test | |ref |idx_test |idx_test|10 |const,const| 1| 100| | EXPLAIN SELECT FROM test *WHERE col2 = 9; id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra| —|—————-|——-|—————|——|——————-|————|———-|——-|——|————|——-|_ 1|SIMPLE |test | |ref |idx_test |idx_test|5 |const| 1| 100| | 复制
执行计划显示这两种情况都可以通过索引查找数据。如果只用 col1 作为查询条件，只有 Oracle 会利用索引跳跃扫描查找数据，其他数据库通过全表扫描查找数据。
另外，还需要注意有些情况不适合创建索引。例如，频繁更新的字段不适合创建索引，因为更新索引也需要付出代价；表中记录较少时不需要创建索引，直接通过全表扫描可能更快；大文本数据考虑使用全文索引。
避免使用 SELECT *
SELECT 表示查询表中的所有字段，这种写法可能返回了不必要的信息，导致性能的下降。因为数据库需要读取更多的数据，同时网络需要传输更多的数据，而且客户端可能并不需要这些信息。
以下是 MySQL 中的一个示例：
EXPLAIN SELECT FROM employee e; id|selecttype|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| —|—————-|——-|—————|——|——————-|—-|———-|—-|——|————|——-| 1|SIMPLE |e | |ALL | | | | | 25| 100| | EXPLAIN SELECT emp_name FROM employee e; id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra | —|—————-|——-|—————|——-|——————-|——————|———-|—-|——|————|—————-|_ 1|SIMPLE |e | |index| |idx_emp_name|152 | | 25| 100|Using index| 复制
第一个查询语句使用了全表扫描，需要返回所有的字段；第二个查询只返回员工的姓名，只需要扫描索引 idx_emp_name 就可以获得所需的数据，不需要访问表。其他数据库中也是如此。
我们在学习过程中为了方便，可以使用星号编写查询语句；但是在实际开发中应该严格控制只返回业务需要的字段。
优化查询条件
虽然我们已经为查询条件中的字段创建了合适的索引，但是如果 WHERE 子句编写不当，同样会导致数据库无法使用索引。
首先，在 WHERE 子句中对索引字段进行表达式运算或者使用函数都会导致索引失效。例如：
EXPLAIN SELECT FROM employee WHERE UPPER(email) = ‘GUANYU@SHUGUO.COM’; id|selecttype|table |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra | —|—————-|————|—————|——|——————-|—-|———-|—-|——|————|—————-|_ 1|SIMPLE |employee| |ALL | | | | | 25| 100|Using where| EXPLAIN SELECT FROM employee WHERE email = ‘guanyu@shuguo.com’; id|selecttype|table |partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra| —|—————-|————|—————|——-|——————-|——————|———-|——-|——|————|——-| 1|SIMPLE |employee| |const|uk_emp_email |uk_emp_email|302 |const| 1| 100| | 复制
以上示例中，虽然 email 字段上存在索引，但是第一个查询中的 UPPER 函数会导致索引失效。
其次，使用 LIKE 匹配时，如果通配符出现在左侧无法使用索引。例如以下语句会导致全表扫描：
EXPLAIN SELECT FROM employee WHERE emp_name *LIKE ‘%飞’; id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra | —|—————-|————|—————|——|——————-|—-|———-|—-|——|————|—————-| 1|SIMPLE |employee| |ALL | | | | | 25| 11.11|Using where| 复制
以上执行计划显示使用了全表扫描查找数据。如果确实存在这类需求，可以考虑全文索引或者专用的全文搜索引擎。
另外，如果 WHERE 条件中的字段上创建了索引，尽量设置为 NOT NULL。不是所有数据库使用 IS [NOT] NULL 判断时都可以利用索引，例如以下的 Oracle 示例：
— Oracle PLAN_TABLE_OUTPUT | —————————————————————————————————————| Plan hash value: 1357081020 | | —————————————————————————————————————| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || —————————————————————————————————————| | 0 | SELECT STATEMENT | | 1 | 91 | 2 (0)| 00:00:01 || | 1 | TABLE ACCESS FULL| *TEST | 1 | 91 | 2 (0)| 00:00:01 || —————————————————————————————————————| | Predicate Information (identified by operation id): | —————————————————————————- |_ | 1 - filter(“COL2” IS NULL) | 复制
Oracle 不会针对索引字段都为 NULL 的数据进行索引。而对于其他数据库，NULL 值的索引也需要进行更多的处理；而且 NULL 值可能导致许多意料之外的问题。因此，建议将索引字段设置为非空，并且指定一个特殊的默认值表示缺失值。
多表连接实现
我们在第 16 篇中介绍了 JOIN 查询，连接查询首先需要避免缺少连接条件导致的笛卡尔积，这是非常消耗资源的操作。对于 JOIN 查询使用的关联字段，应该确保数据类型和字符集相同，并且创建了合适的索引。
对于多表连接查询，数据库底层的实现方式通常有三种：
- 嵌套循环连接（Nested Loop Join），针对驱动表（外表）中的每条记录，遍历另一个表找到匹配的数据，相当于两层 for 循环。
- 哈希连接（Hash Join），将一个表的连接字段计算出一个哈希表，然后从另一个表中一次获取记录并计算哈希值，根据两个哈希值来匹配符合条件的记录。
- 排序合并连接（ Sort Merge Join），先将两个表中的数据基于连接字段分别进行排序，然后合并排序后的结果。
MySQL 8.0 之前只支持 Nested Loop Join，所以不建议多个表的连接查询，因为多层循环嵌套会导致查询性能的急剧下降。以下连接查询通过主键进行关联：
EXPLAIN ANALYZE SELECT FROM test t1 JOIN test t2 ON (t1.id = t2.id); -> Nested loop inner join (cost=451.00 rows=1000) (actual time=0.253..21.776 rows=1000 loops=1) -> Table scan on t1 (cost=101.00 rows=1000) (actual time=0.208..3.742 rows=1000 loops=1) -> Single-row index lookup on t2 using PRIMARY (id=t1.id) (cost=0.25 rows=1) (actual time=0.008..0.010 rows=1 loops=1000) 复制
其中，ANALYZE 是 MySQL 8.0 新增的选项，用于返回更加详细的执行计划，包括连接实现的方式、实际执行时间等。以上执行计划使用 Nested Loop Join 连接两个表，t1 扫描了一次，然后通过主键循环查找 t2 1000 次；当然，通过数据缓存实际上并不需要扫描磁盘 1000 次。
如果采用其他数据库，以上语句的执行计划会有不同。你用的是哪个数据库，使用的是哪种连接实现？
从 MySQL 8.0 开始引入 Hash Join，用于优化等值连接。例如以下查询：
EXPLAIN ANALYZE SELECT FROM test t1 JOIN test t2 ON (t1.col3 = t2.col3); -> Inner hash join (t2.col3 = t1.col3) (cost=100103.20 rows=100000) (actual time=11.945..25.363 rows=1000 loops=1) -> Table scan on t2 (cost=0.01 rows=1000) (actual time=0.206..5.947 rows=1000 loops=1) -> Hash -> Table scan on t1 (cost=101.00 rows=1000) (actual time=0.236..6.176 rows=1000 loops=1) 复制
由于两个表的 col3 上没有索引，执行计划使用 Hash Join 连接两个表；此时 t2 和 t1 都只需要扫描一次，比 Nested Loop Join 性能会更好，尤其是数据量大的时候。
以上语句在其他数据库中的执行计划也都是采用 Hash Join 连接。
数据库优化器选择哪种方式取决于许多因素，例如表中的数据量、关联字段是否已经排序或者创建索引等：
- Nested Loop Join 适用于驱动表数据比较少，并且连接表中有索引的时候；
- Hash Join 对于数据量大，且没有索引的情况下可能性能更好；
- Sort Merge Join 通常用于没有索引，并且数据已经排序的情况。
一般连接查询的表较少时，优化器可以自行选择合适的实现方法；当复杂查询性能不够理想时，我们可以通过执行计划查看是否缺少索引、调整多表连接的顺序等方式进行优化。
另外，还有一种减少连接查询的方法，就是增加冗余字段，利用空间换时间。
优化子查询
我们在第 17 篇中介绍了子查询；其中非关联子查询独立运行，比较容易处理；通常需要优化的是关联子查询。
我们来看一个示例，以下查询返回月薪大于部门平均月薪的员工信息：
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
改写之后的查询利用了物化（Materialization）技术，将子查询的结果生成一个内存临时表；然后与 employee 表进行连接。通过实际执行时间可以看出这种方式更快。
以上示例在 Oracle 和 SQL Server 中会自动执行子查询展开，两种写法效果相同；在 PostgreSQL 中与 MySQL 类似，第一个语句使用 Nested Loop Join，改写为 JOIN 之后使用 Hash Join 实现，性能更好。
我们再看一个子查询中的 IN 和 EXISTS 运算符的比较：
EXPLAIN ANALYZE SELECT emp_id, emp_name FROM employee e WHERE e.dept_id IN (SELECT dept_id FROM employee); -> Nested loop inner join (cost=7.60 rows=30) (actual time=0.130..0.624 rows=25 loops=1) -> Index scan on department using PRIMARY (cost=0.85 rows=6) (actual time=0.048..0.070 rows=6 loops=1) -> Index lookup on e using idx_emp_dept (dept_id=department.dept_id) (cost=0.71 rows=5) (actual time=0.041..0.062 rows=4 loops=6) EXPLAIN ANALYZE SELECT emp_id, emp_name FROM employee e WHERE EXISTS (SELECT 1 FROM department WHERE dept_id = e.dept_id); -> Nested loop inner join (cost=7.60 rows=30) (actual time=0.127..0.622 rows=25 loops=1) -> Index scan on department using PRIMARY (cost=0.85 rows=6) (actual time=0.046..0.069 rows=6 loops=1) -> Index lookup on e using idx_emp_dept (dept_id=department.dept_id) (cost=0.71 rows=5) (actual time=0.039..0.062 rows=4 loops=6) 复制
对于以上 IN 和 EXISTS 子查询，MySQL 优化器使用了相同的 Nested Loop Join 实现。不过，由于 [NOT] IN 需要进行等值比较，在子查询的结果中存在 NULL 值时可能导致逻辑错误，推荐使用 [NOT] EXISTS 运算符。
以上示例的 IN 和 EXISTS 在其他数据库中也都等价；而且 Oracle 和 SQL Server 更加智能，直接对 employee 表执行一次全表扫描即可返回所有数据，完全不需要访问 department 和连接操作。大家可以想想为什么？
从以上一些示例可以看出，不同数据库的优化器能力有所差异，同时也都在不断改进；当我们面对不同的数据库时，可能需要采用不同的优化技术。
优化 UNION
我们在第 18 篇中介绍了集合操作，其中 UNION 和 UNION ALL 都表示并集操作。它们的主要区别是 UNION 需要将合并后的结果进行去重操作。以下是这两个操作在 MySQL 中的执行计划：
EXPLAIN SELECT FROM employee UNION SELECT FROM empdevp; id|select_type |table |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra | —|——————|—————|—————|——|——————-|—-|———-|—-|——|————|———————-| 1|PRIMARY |employee | |ALL | | | | | 25| 100| | 2|UNION |emp_devp | |ALL | | | | | 9| 100| | |UNION RESULT|
从上面的结果可以看出，UNION 需要执行一个额外的操作，同时使用了临时表（Using temporary）。实际上数据库需要利用临时表进行数据排序，会增加 CPU 和内存的消耗，在数据量很大时甚至需要利用磁盘文件进行排序。所以，如果确认不会出现重复数据或者不在乎重复数据，使用 UNION ALL。
优化分页查询
我们在第 7 篇介绍了如何利用 FETCH 或者 LIMIT 以及 OFFSET 实现 Top-N 排行榜和分页查询。当表中的数据量很大时，这种方式的分页查询可能会导致性能问题。例如：
— MySQL SELECT FROM large_table ORDER BY id LIMIT 10 OFFSET 1000000; 复制
随着 OFFSET 的增加，查询速度会越来越慢；因为即使我们只返回 10 条记录，数据库仍然需要访问并且过滤掉 1000000 行记录，即使通过索引也会涉及不必要的扫描操作。对于以上分页我们可以记住上一次获取到的最大 id，然后在查询中作为条件：
SELECT FROM large_table WHERE id > last_value ORDER BY id LIMIT 10; 复制
如果查询条件上存在索引，这种分页查询的方式可以基本不受数据量的影响。
小结
SQL 优化本质上是了解优化器的的工作原理，并且为此创建合适的索引和正确的语句。当优化器由于自身的限制无法进一步优化时，我们可以人为进行查询的重写，共同实现查询的优化。另外我们还需要知道，SQL 优化只是数据库性能优化的一部分；相关的技术还包括表结构优化、配置参数优化、操作系统和硬件调整，甚至架构优化（分库分表、读写分离等）。
参考文档：
思考题 1：manager 字段上存在索引，以下查询是否会利用该索引查找数据：
SELECT FROM employee e WHERE manager = ‘10’; 复制
思考题 2：以下查询语句 1 和查询语句 2 哪个性能更好，还是差不多？
— 语句 1 SELECT col1, count() FROM test WHERE col2 = 1997 GROUP BY col1; — 语句 2 EXPLAIN ANALYZE SELECT col1, count() FROM test WHERE col2 = 1997 AND col3 = ‘10’ GROUP *BY col1;
若有收获，就点个赞吧
0 人点赞
