SQL查询优化技巧

查询优化的本质是让数据库优化器为SQL语句选择最佳的执行计划。一般来说，对于在线交易处理（OLTP）系统的数据库，减少数据库磁盘I/O是SQL语句性能优化的首要方法，因为磁盘访问通常是数据库性能的瓶颈所在。

另外，我们还需要考虑降低CPU和内存的消耗。例如DISTINCT、GROUP BY、ORDER BY等操作都会涉及CPU运算，需要占用内存或者使用临时磁盘文件，这些都是我们优化的目标。

创建合适的索引

索引是优化查询性能的重要方法，因此我们首先需要了解哪些字段适合创建索引：

1. 基于经常出现在WHERE条件中的字段建立索引，可以避免全表扫描。
2. 基于多表连接查询的关联字段（通常是外键）建立索引，可以提高连接查询的性能。
3. 将GROUP BY分组字段加入索引中，可以利用索引实现分组。
4. 将ORDER BY排序字段加入索引中，可以避免额外的排序操作。

另外，我们在创建索引时尽量选择区分度高的字段，比如手机号、姓名等。“性别”这种重复性极高的字段不适合单独创建索引，必要时可以考虑和其他字段一起创建复合索引。

对于复合索引，查询条件中最常出现的字段应该放在索引的最左边，这被称为复合索引最左前缀原则，例如：

--创建表
CREATE TABLE test(
  id number not null,
  col1 number,
  col2 number,
  col3 varchar2(100),
  PRIMARY KEY (id)
);

--利用递归往表中插入1000条记录
INSERT INTO test
WITH t (id,c1,c2,c3) AS (
   SELECT 1 id,1 c1,1 c2,1 c3 FROM dual
   
   UNION ALL
   
   SELECT id+1,c1+1,c2+2,c3+3 FROM t
   WHERE id<1000
)
SELECT * FROM t;

我们首先创建了一个测试表test，然后利用一个递归通用表表达式插入了1000行数据。

假如我们经常同时使用col1和col2字段作为查询条件，另外也会单独使用col2字段作为查询条件，可以创建以下复合索引：

CREATE INDEX idx_test
ON test(col2,col1);

其中col2字段在前，col1字段在后。下面以Oracle数据库为例，查看这两种查询条件下的执行计划：

 

 执行计划显示，在这两种情况下，均可以通过索引idx_test查找数据。

如果我们需要单独使用col1字段作为查询条件，则通过全表扫描来查找数据。

 

另外，我们还需要了解一些不适合创建索引的情况。

例如，频繁更新的字段不适合创建索引，因为更新索引也需要付出代价。表中的数据量很少时无须创建索引，因为在这种情况下全表扫描可能更快。

最后，对于大文本数据的检索可以考虑使用全文搜索技术。

避免索引失效

虽然我们已经创建了合适的索引，但是如果查询语句中的WHERE子句编写不当，仍然可能会导致数据库无法使用索引。

首先，在查询条件中对索引字段进行运算或者使用函数都会导致索引失效，例如：

查询条件中的UPPER函数会导致索引失效，因为索引中并没有存储大写形式的email。 

其次，我们在使用LIKE运算符进行匹配时，如果通配符出现在左侧，也会导致索引失效，例如：

以上语句将会使用全表扫描的方式来查找数据，只有匹配模式左侧是确定的内容（比如“张%”）时，才可能会使用索引查找数据。

如果业务需求中确实存在这类模糊匹配，我们可以考虑使用全文索引或者专门的全文搜索引擎。

如果我们在某个字段上创建了索引，则应该尽量将其设置为NOT NULL。这是因为不是所有的数据库在使用IS [NOT] NULL运算符时，都会通过索引查找数据。

以下是一个Oracle示例：

Oracle不会针对索引字段为NULL的数据进行索引，因此该查询使用了全表扫描。

另外，在之前的文章中介绍了NULL值可能导致的各种问题。因此，建议将索引字段设置为NOT NULL，并且为其指定一个特殊的默认值来表示缺失值。

只返回需要的结果

SELECT*表示查询表中的全部字段，这种写法通常会返回不必要的字段，从而影响查询的性能。

这是因为数据库需要读取更多的数据，同时需要通过网络传输更多的数据，而客户端可能并不需要这些信息。

以下是一个Oracle示例：

 

 

第一个查询语句需要返回所有的字段，使用了全表扫描。

第二个查询只需返回员工的姓名，通过扫描索引 idx_emp_name就可以得到查询结果，甚至不用访问表。

因此，我们在开发和测试过程中可以使用SELECT*这种方式快速编写查询，但是在实际应用中应该严格控制只返回业务需要的字段。

优化多表连接

连接查询首先需要避免缺少连接条件导致的笛卡儿积，因为这是非常消耗资源的操作。

对于连接查询中使用的关联字段，我们应该确保它们的数据类型和字符集相同，并且创建了合适的索引。

对于多表连接查询，数据库的实现算法通常有以下三种。

1. 嵌套循环连接（Nested Loop Join）：针对驱动表（外表）中的每条记录，遍历另一个表并找到匹配的数据，相当于两层FOR循环。这种方式适用于驱动表数据比较少，并且连接表中有索引的情况。
2. 哈希连接（Hash Join）：将其中一个表的连接字段计算出一个哈希表，然后从另一个表中一次获取记录并计算哈希值，根据两个哈希值来匹配符合条件的记录。这种方式在数据量大且没有创建索引的情况下的性能可能更好。
3. 排序合并连接（Sort Merge Join）：首先将两个表中的数据基于连接字段分别进行排序，然后合并排序后的结果。这种方式通常用于没有创建索引，并且数据已经排序的情况。

数据库优化器选择哪种算法取决于许多因素，比如表中的数据量、关联字段是否已经排序或者创建索引等。

一般连接查询的表较少时，优化器可以自行选择合适的实现方法。当复杂查询性能不够理想时，我们可以通过执行计划来查看是否需要采用创建索引、调整多表连接的顺序或者指定连接方法等进行优化。

另外，还有一种优化连接查询的方法，就是通过增加冗余字段来减少连接查询的数量。

尽量避免使用子查询

非关联子查询可以单独执行，比较容易处理。我们通常需要优化的是关联子查询。以下是一个Oracle示例，该查询返回了月薪高于部门平均月薪的员工：

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

从执行计划看，两种方式没有差别，但是如果换作MySQL数据库，使用第二种方法查询效率会更高。建议使用第二种写法。

第二种写法利用了物化（Materialization）技术，将子查询的结果保存为一个内存临时表，然后与employee表进行嵌套循环连接。

优化集合操作

集合操作符，其中UNION和UNION ALL都是并集操作符，它们的主要区别在于UNION需要将合并后的结果进行去重。

例如，以下是一个Oracle中的示例：

 ​​​​​​​

 

 

从执行计划中可以看出，UNION操作符需要执行一个额外 SORT UNIQUE  的操作。

 

记住SQL子句的逻辑执行顺序

完整SQL查询语句：

 以上是SQL查询中各个子句的编写顺序，前面括号内的数字代表了它们的逻辑执行顺序。也就是说，数据库并非按照编写顺序先执行SELECT子句，然后再执行FROM子句等。从逻辑上讲，SQL子句的执行顺序如下：

（1）首先，FROM和JOIN是SQL语句执行的第一步。它们的结果是一个笛卡儿积，该结果决定了接下来要操作的数据集。注意，逻辑执行顺序并不代表物理执行顺序，实际上数据库在获取表中的数据之前会应用ON和WHERE过滤条件进行访问优化。

（2）然后，应用ON条件对上一步的结果进行过滤，并生成新的数据集。

（3）接着执行WHERE子句，对上一步的数据集进行过滤。WHERE和ON子句在大多数情况下的效果相同，但是在外连接查询中有所区别。

（4）下一步，基于GROUP BY子句指定的表达式进行分组，同时对于每个分组计算聚合函数agg_func的结果。经过GROUP BY处理之后，数据集的结构就发生了变化，只保留了分组字段和聚合函数的结果。

（5）如果存在GROUP BY子句，可以进一步利用HAVING子句对分组后的结果进行过滤。

（6）接下来，SELECT子句可以指定要返回的字段。如果指定了DISTINCT关键字，数据库需要对结果进行去重操作。另外，数据库还会为指定了AS的字段生成别名。

（7）如果还有集合操作符（UNION、INTERSECT、EXCEPT）和其他的SELECT语句，执行该查询，之后合并两个结果集。对于集合操作中的多个SELECT语句，数据库通常可以支持并发执行。

（8）随后应用ORDER BY子句对结果进行排序。如果存在GROUP BY子句或者DISTINCT关键字，就只能使用分组字段和聚合函数进行排序；否则可以使用表中的任何字段排序。

（9）最后，利用OFFSET和FETCH（LIMIT、TOP）子句限定返回的行数。

理解以上SQL子句的逻辑执行顺序也可以帮助我们进行查询优化。例如，WHERE子句在HAVING子句之前执行，因此我们应该尽量使用WHERE子句进行数据过滤，除非业务逻辑需要基于聚合函数的结果进行过滤。

另外，了解SQL子句的逻辑执行顺序还可以帮助我们避免一些常见的错误，例如：

该语句的错误在于WHERE条件中引用了列别名。从SQL子句的逻辑执行顺序中可以看出，数据库使用WHERE条件过滤数据时还没有执行SELECT子句，也就还没有生成字段的别名。

另一个需要注意的操作就是GROUP BY，常见错误示例：

经过GROUP BY子句处理之后，结果中只保留了分组字段和聚合函数的值，示例中的emp_name字段已经不存在了。从逻辑上来说，按照部门分组统计之后再显示某个员工的姓名没有意义。如果需要同时显示员工信息和所在部门的汇总结果，可以使用窗口函数。

还有一些逻辑问题可能不会直接导致查询出错，但是会返回不正确的结果，例如外连接查询中的ON和WHERE子句。以下是一个左外连接查询的示例：

 

第一个查询语句在ON子句中指定了连接的条件，然后通过WHERE子句找出“张飞”。

第二个查询语句将所有的过滤条件都放在ON子句中，结果返回了所有员工的姓名。这是因为左外连接会返回左表中的全部数据，即使ON子句中指定了员工姓名，也不会生效。

如果把查询二中的LEFT JOIN 更换为INNER JOIN，也可以避免该问题：

 

SQL语句的声明性使得我们无须关心具体的数据库实现，但同时也可能因此导致查询的性能问题。

SQL语句性能优化只是数据库性能优化的一部分，其他技术还包括表结构的优化、数据库配置参数优化、操作系统和硬件调整以及架构优化（分库分表、读写分离等）。