SQL优化法则

发表于 更新于
SQL本身并不难学，编写查询语句也很容易，但是想要编写出能够高效运行的查询语句却有一定的难度。

SQL作为关系型数据库的标准语言，是IT从业人员必不可少的技能之一。SQL本身并不难学，编写查询语句也很容易，但是想要编写出能够高效运行的查询语句却有一定的难度。
查询优化是一个复杂的工程，涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL语句的执行顺序、索引以及统计信息的采集等，甚至应用程序和系统的整体架构。下面介绍几个关键法则，可以帮助我们编写高效的SQL查询；尤其是对于初学者而言，这些法则至少可以避免我们写出性能很差的查询语句。

1、只返回需要的结果

一定要为查询语句指定where条件，过滤掉不需要的数据行。通常来说，OLTP系统每次只需要从大量数据中返回很少的几条记录；指定查询条件可以帮助我们通过索引返回结果，而不是全表扫描。
同理，我们应该避免使用查询表中的所有字段。这种写法通常导致数据库需要读取更多的数据，同时网络也需要传输更多的数据，从而导致性能的下降。

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

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

一般来说，以下字段需要创建索引
（1）经常出现在where条件中的字段建立索引可以避免全表扫描；
（2）将order by排序的字段加入到索引中，可以避免额外的排序操作；
（3）多表连接查询的关联字段建立索引，可以提高连接查询的性能；
（4）将group by分组操作字段加入到索引中，可以利用索引完成分组。

即使创建了合适的索引，如果SQL语句写的有问题，数据库也不会使用索引。

导致索引失效的常见问题包括
（1）在where子句中对索引字段进行表达式运算或者使用函数都会导致索引失效，这种情况还包括字段的数据类型不匹配，例如字符串和整数进行比较；
（2）使用like匹配时，如果通配符出现在左侧无法使用索引。对于大型文本数据的模糊匹配，应该考虑数据库提供的全文检索功能，甚至专门的全文搜索引擎（Elasticsearch 等）；
（3）如果要给where条件中的字段上创建索引，要先把该字段设置为”not null”。

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

3、尽量避免使用子查询

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

1
2
3
4
5
6
7
SELECT emp_id, emp_name
FROM employee e
WHERE salary > (
       SELECT AVG(salary)
       FROM employee
       WHERE dept_id = e.dept_id
);

以下语句将该子查询替换为等价的join语句，实现了子查询的展开（Subquery Unnest）

1
2
3
4
5
6
7
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;

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

4、不要使用offset实现分页

分页查询的原理就是先跳过指定的行数，再返回Top-N记录。

1
2
3
4
SELECT *
FROM large_table
ORDER BY id
LIMIT 10 OFFSET N;

以上查询随着offset的增加，速度会越来越慢；因为即使我们只需要返回10条记录，数据库仍然需要访问并且过滤掉N（比如1000000）行记录，即使通过索引也会涉及不必要的扫描操作。
对于以上分页查询，更好的方法是记住上一次获取到的最大id，然后在下一次查询中作为条件传入：

1
2
3
4
5
SELECT *
FROM large_table
WHERE id > last_id
ORDER BY id
LIMIT 10;

如果id字段上存在索引，这种分页查询的方式可以基本不受数据量的影响。

5、了解SQL子句的逻辑执行顺序

以下是SQL中各个子句的语法顺序，前面括号内的数字代表了它们的逻辑执行顺序：

1
2
3
4
5
6
7
8
9
10
(6) SELECT [DISTINCT | ALL] col1, col2, agg_func(col3) AS alias
(1) FROM t1 JOIN t2
(2) ON (join_conditions)
(3) WHERE where_conditions
(4) GROUP BY col1, col2
(5) HAVING having_condition
(7) UNION [ALL]
...
(8) ORDER BY col1 ASC,col2 DESC
(9) OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;

了解SQL逻辑执行顺序可以帮助我们进行SQL优化。例如where子句在having子句之前执行，因此我们应该尽量使用where进行数据过滤，避免无谓的操作（除非业务需要针对聚合函数的结果进行过滤）。