SQL 语句优化及编程方法

DBMS生成的执行计划在很大程度上要受到代码外部结构的影响。因此要想优化查询性能，就必须要知道如何写代码才能使优化器的执行效率更高。

但是，不能为了“效率”牺牲代码的可读性，要让代码清晰。

1 查询优化

在解决SQL造成的性能问题时，我们还需要查看DBMS选择的执行计划来做判断。注意，优化SQL的方法未必能解决所有的性能问题。

1.1 参数是子查询

1 使用EXISTS代替IN。

IN 会扫描全表，并会生成中间临时表。而EXISTS，只要查到一行数据满足条件就会终止查询。
EXISTS会使用索引，而IN查询生成的中间表（铺展开来还会占用内存），很难继承原表的索引。

2 使用连接代替IN。

不一定比EXISTS效率更好，但是与IN相比，更大可能用上索引，同时也不会生成中间表。

1.2 避免排序

会进行排序的具有代表性的运算有：

GROUP BY 子句。
ORDER BY 子句。
聚合函数（SUM、COUNT、AVG、MAX、MIN）。
DISTINCT。
集合运算符（UNION、INTERSECT、EXCEPT）。
窗口函数（RANK、ROW_NUMBER等）。

1.2.1 避免排序的诀窍

1 灵活使用集合运算符的ALL可选项。

集合运算符不加ALL，则会去除重复数据及排序。加上ALL后则不会排序及去除重复数据，例如 UNION ALL。

2 使用EXISTS代替DISTINCT。

1.3 索引

索引能极大的提高查询速度，要善用索引。

1 善用索引。

在极值函数中使用索引。在GROUP BY 和 ORDER BY中使用索引。

2 避免在索引字段上进行运算。

使用索引时，列应该是原始字段。

SELECT * FROM t_num WHERE num * 2 > 5; // 这样并不会使用num的索引

SELECT * FROM t_num WHERE num > 5 * 2; // 这样会使用num的索引。

3 尽量减少NULL值出现在索引字段中。

IS NULL 和 IS NOT NULL 查询时，索引无法使用。 NULL很多的字段也无法使用索引。

4 否定形式可能用不到索引。

例如 != 及 NOT IN 等，将会使索引失效。

5 使用OR的索引效果不佳。

6 使用联合索引时，查询字段的顺序很重要。

联合索引中的第一列必须写在查询条件的开头。查询字段的顺序最好与索引中的顺序一致。

7 使用LIKE时，只有前方一致的匹配才能用到索引。

例如 “a%”,而像”%a”则会使索引失效。

8 默认的类型转换会使索引失效。

默认的类型转换不仅会增加额外的性能开销，还会导致索引不可用。

在需要类型转换时显式地进行类型转换（转换要写在值的一边，而不是列的一边）。

例如下面 col_1 字段类型为varchar(10)

SELECT * FROM t_str WHERE col_1 = 10; // 会执行隐性转换，索引失效

SELECT * FROM t_str WHERE col_1 = ‘10’;// 不会隐性转换，索引有效

SELECT * FROM t_str WHERE col_1 = CAST(10,AS CHAR(5)); // 显式转换，索引有效.

9 减少中间表。

频繁使用中间表会带来两个问题：1）展开数据需要消耗内存资源。2）原始表中的索引不容易被用到（特别是聚合函数）。

1.4 其他

1 筛选条件能在WHERE写就不要在HAVING写。

where 先筛选能减轻排序的负担。
where 可以使用索引，having是针对聚合后生成的视图进行筛选的，但是很多时候，聚合后的视图并没用基础原表的索引结构。

2 合理使用视图，避免在视图中进行聚合操作。

3 先连接再进行聚合。

可以避免产生中间表。

4 需要对多个字段使用IN谓词时，先将它们汇总到一处。

SELECT *
FROM t_address
WHERE city IN (SELECT city FROM t_address2)
AND area IN (SELECT area FROM t_address2);

上面的代码可以将多个IN合并为一个。

SELECT *
FROM t_address
WHERE (area,city) IN (SELECT area,city FROM t_address2);

这样，子查询就只要执行一次就可以了。

2 编程方法

“未来的自己，陌生如他人”，好的编程风格可以提高系统开发效率。

1 去除关联子查询。

使用窗口函数来代替关联子查询，可以提高可读性和性能。

关联子查询不容易编写，调试起来也很困难，因为关联子查询无法单独执行。

2 从FROM子句开始写。

SQL执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT (-> ORDER BY)。严格来说，ORDER BY并不是SQL语句的一部分。