SQL 优化笔记

（包括但不限于传统 OLTP 数据库）

只返回需要的结果

使用 WHERE 指定查询条件，过滤掉不需要的数据行；
避免使用 SELECT * 这种操作，这种查询方式往往会导致数据库读取更多的数据，同时也会增加网络传输的负担，从而导致性能下降；

实际开发经验：

在之前的大数据开发中，因为使用了全字段，导致了在运行过程中出现了两个重要问题：

Spark 从数据库中拉取数据较慢，大量的数据对网络造成了负担
数据拉到本地并缓存到内存中后，无用字段的数据占用了大量空间，其次在 Spark 运算过程中，当 shuffle 或者 repartition 时，也会对网络 IO 造成严重负载；

正确使用索引

为经常在 where 中经常出现的字段建立索引，避免全盘扫描；
为 ORDER BY 中的字段建立索引，避免额外的排序操作；
多表连接时使用到的字段也建立索引，提高查询性能；
为 GROUP BY 的字段建立索引，可以利用索引完成分组；

在以下几种情况中，索引会失效：

在 WHERE 子句中，对索引字段使用了表达式计算，或者使用了函数，再或者使用了不相同的类型进行比较就会导致索引失效；
在进行模糊匹配时，通配符在左侧会导致索引失效；
如果某个字段设置了索引，则需要为该字段一同设置 NOT NULL，并不是所以数据库使用 IS NOT NULL 判断时都可以利用索引；

尽量避免使用子查询

对于使用子查询的 SQL 可以进行以下优化：

可以将子查询语句执行结果生成为一张内存临时表，在使用时可以通过外连接进行查询（这种方式在 Spark 中经常使用，但要注意释放掉内存）

对于 IN 和 EXISTS 子查询，由于不同的数据库优化能力有差别，所以尽量使用 JOIN 进行重写

SQL 语句执行顺序

(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;

首先，FROM 和 JOIN 是 SQL 语句执行的第一步，决定了接下来要操作的数据集。注意逻辑执行顺序并不代表物理执行顺序，实际上数据库在获取表中的数据之前会使用 ON 和 WHERE 过滤条件进行优化访问；
其次，应用 ON 条件对上一步的结果进行过滤并生成新的数据集；
然后，执行 WHERE 子句对上一步的数据集再次进行过滤。WHERE 和 ON 大多数情况下的效果相同，但是外连接查询有所区别，我们将会在下文给出示例；
接着，基于 GROUP BY 子句指定的表达式进行分组；同时，对于每个分组计算聚合函数 agg_func 的结果。经过 GROUP BY 处理之后，数据集的结构就发生了变化，只保留了分组字段和聚合函数的结果；
如果存在 GROUP BY 子句，可以利用 HAVING 针对分组后的结果进一步进行过滤，通常是针对聚合函数的结果进行过滤；
接下来，SELECT 可以指定要返回的列；如果指定了 DISTINCT 关键字，需要对结果集进行去重操作。另外还会为指定了 AS 的字段生成别名；
如果还有集合操作符（UNION、INTERSECT、EXCEPT）和其他的 SELECT 语句，执行该查询并且合并两个结果集。对于集合操作中的多个 SELECT 语句，数据库通常可以支持并发执行；
然后，应用 ORDER BY 子句对结果进行排序。如果存在 GROUP BY 子句或者 DISTINCT 关键字，只能使用分组字段和聚合函数进行排序；否则，可以使用 FROM 和 JOIN 表中的任何字段排序；
最后，OFFSET 和 FETCH（LIMIT、TOP）限定了最终返回的行数。