本篇主题为用如何提高SQL性能？
SQL的性能优化是数据库工程师在实际工作中必须面对的重要课题之一。对于某些数据库工程师来说，它几乎是唯一的课题。实际上，在像Web服务这样需要快速响应的应用场景中，SQL的性能直接决定了系统是否可以使用。
因此，本篇文章不再像前面一样介绍SQL的各种功能的应用技巧，而是将重点转向SQL的优化方面，介绍一些使SQL执行速度更快、消耗内存更少的优化技巧。
严格地优化查询性能时，必须要了解所使用数据库的功能特点。此外，查询速度慢并不只是因为SQL语句本身，还可能是因为内存分配不佳、文件结构不合理等其他原因。因此本篇文章即将介绍的优化SQL的方法未必能解决所有的性能问题，但是确实很多时候查询性能不好的原因还是SQL的写法不合理。
接下来将尽量介绍一些不依赖具体数据库实现，而且简单易行的优化方法。希望这些方法能使大家在平常工作中感觉到SQL执行速度慢时得到帮助。
01、使用高效的查询
在SQL中，很多时候不同代码能够得出相同结果。从理论上来说，得到相同结果的不同代码应该有相同的性能，但遗憾的是，查询优化器生成的执行计划很大程度上要受到代码外部结构的影响。因此如果想优化查询性能，必须知道如何写代码才能使优化器的执行效率更高。
参数是子查询时，使用EXISTS代替IN
IN谓词非常方便，而且代码也容易理解，所以使用的频率很高。但是方便的同时，IN谓词却有成为性能优化的瓶颈的危险。如果代码中大量用到了IN谓词，那么一般只对它们进行优化就能大幅度地提升性能。
如果IN的参数是“1, 2, 3”这样的数值列表，一般还不需要特别注意。但是如果参数是子查询，那么就需要注意了。
在大多时候，[NOT] IN和[NOT] EXISTS返回的结果是相同的。但是两者用于子查询时，EXISTS的速度会更快一些。
我们先看个例子。这里使用前面用过的两张用于管理员工学习过的培训课程的表作为测试数据。
Class_A
Class_B
我们试着从Class_A表中查出同时存在于Class_B表中的员工。下面两条SQL语句返回的结果是一样的，但是使用EXISTS的SQL语句更快一些。
--慢
SELECT ＊
FROM Class_A
WHERE id IN
(SELECT id
FROM Class_B);
--快
SELECT ＊
FROM Class_A A
WHERE EXISTS
(SELECT ＊
FROM Class_B B
WHERE A.id = B.id);两个结果都如下所示：
id name
-- ----
1 田中
2 铃木使用EXISTS时更快的原因有以下两个。
● 如果连接列（id）上建立了索引，那么查询Class_B时不用查实际的表，只需查索引就可以了。
● 如果使用EXISTS，那么只要查到一行数据满足条件就会终止查询，不用像使用IN时一样扫描全表。在这一点上NOT EXISTS也一样。
当IN的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时的工作表里（内联视图），然后扫描整个视图。很多情况下这种做法都非常耗费资源。使用EXISTS的话，数据库不会生成临时的工作表。
但是从代码的可读性上来看，IN要比EXISTS好。使用IN时的代码看起来更加一目了然，易于理解。因此，如果确信使用IN也能快速获取结果，就没有必要非得改成EXISTS了。
而且，最近有很多数据库也尝试着改善了IN的性能。也许未来的某一天，无论在哪个数据库上，IN都能具备与EXISTS一样的性能。
参数是子查询时，使用连接代替IN
要想改善IN的性能，除了使用EXISTS，还可以使用连接。前面的查询语句就可以像下面这样“扁平化”。
--使用连接代替IN
SELECT A.id, A.name
FROM Class_A A INNER JOIN Class_B B
ON A.id = B.id;这种写法至少能用到一张表的“id”列上的索引。而且，因为没有了子查询，所以数据库也不会生成中间表。我们很难说与EXISTS相比哪个更好，但是如果没有索引，那么与连接相比，可能EXISTS会略胜一筹。而且，从本文后面的很多例子也可以看出，有些情况下使用EXISTS比使用连接更合适。
02、避免排序
与面向过程语言不同，在SQL语言中，用户不能显式地命令数据库进行排序操作。对用户隐藏这样的操作正是SQL的设计思想。
但是，这样并不意味着在数据库内部也不能进行排序。其实正好相反，在数据库内部频繁地进行着暗中的排序。因此最终对于用户来说，了解都有哪些运算会进行排序很有必要（从这个意义上讲，“隐藏操作”这个目标的实现似乎还任重道远）。
会进行排序的代表性的运算有下面这些。
● GROUP BY子句
● ORDER BY子句
● 聚合函数（SUM、COUNT、AVG、MAX、MIN）
● DISTINCT
● 集合运算符（UNION、INTERSECT、EXCEPT）
● 窗口函数（RANK、ROW_NUMBER等）
排序如果只在内存中进行，那么还好；但是如果内存不足因而需要在硬盘上排序，那么伴随着“呲啦呲啦”的硬盘访问声，排序的性能也会急剧恶化（下面的数据可能不太准确……据说硬盘的访问速度比内存的要慢上100万倍）。因此，尽量避免（或减少）无谓的排序是我们的目标。
灵活使用集合运算符的ALL可选项
SQL中有UNION、INTERSECT、EXCEPT三个集合运算符。
在默认的使用方式下，这些运算符会为了排除掉重复数据而进行排序。
SELECT ＊ FROM Class_A
UNION
SELECT ＊ FROM Class_B;结果：
id name
-- -----
1 田中
2 铃木
3 伊集院
4 西园寺如果不在乎结果中是否有重复数据，或者事先知道不会有重复数据，请使用UNION ALL代替UNION。这样就不会进行排序了。
SELECT ＊ FROM Class_A
UNION ALL
SELECT ＊ FROM Class_B;结果：
对于INTERSECT和EXCEPT也是一样的，加上ALL可选项后就不会进行排序了。
加上ALL可选项是优化性能的一个非常有效的手段，但问题是各种数据库对它的实现情况参差不齐。下表中汇总了目前各种数据库的实现情况。
集合运算符ALL可选项的实现情况
1．Oracle使用MINUS代替EXCEPT
2．MySQL连INTERSECT和EXCEPT运算本身还没有实现
上面这张表从侧面展现出了各个数据库厂商对标准SQL的遵从程度，很有意思。DB2果然忠实地实现了全部功能。而PostgreSQL虽然是开源软件，但是也兼顾到了所有细节，很符合学院派的作风。MySQL和SQL Server稍微差一些，Oracle很重视自己的个性。我们很容易想当然地以为所有的数据库都支持ALL可选项，但事实并非如此，请注意一下。
使用EXISTS代替DISTINCT
为了排除重复数据，DISTINCT也会进行排序。如果需要对两张表的连接结果进行去重，可以考虑使用EXISTS代替DISTINCT，以避免排序。
Items
SalesHistory
我们思考一下如何从上面的商品表Items中找出同时存在于销售记录表SalesHistory中的商品。简而言之，就是找出有销售记录的商品。
使用IN是一种做法。但是前面我们说过，当IN的参数是子查询时，使用连接要比使用IN更好。因此我们像下面这样使用“item_no”列对两张表进行连接。
SELECT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;结果：
it
item_no
-------
10
10
20
20
30
30
30em_no因为是一对多的连接，所以“item_no”列中会出现重复数据。为了排除重复数据，我们需要使用DISTINCT。
SELECT DISTINCT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;
item_no
-------
10
20
30但是，其实更好的做法是使用EXISTS。
SELECT item_no
SELECT item_no
FROM Items I
WHERE EXISTS
(SELECT ＊
FROM SalesHistory SH
WHERE I.item_no = SH.item_no);这条语句在执行过程中不会进行排序。而且使用EXISTS和使用连接一样高效。
在极值函数中使用索引（MAX/MIN）
SQL语言里有MAX和MIN两个极值函数。
使用这两个函数时都会进行排序。但是如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表。以刚才的表Items为例来说，SQL语句可以像下面这样写。
--这样写需要扫描全表
SELECT MAX(item)
FROM Items;
--这样写能用到索引
SELECT MAX(item_no)
FROM Items;因为item_no是表Items的唯一索引，所以效果更好。对于联合索引，只要查询条件是联合索引的第一个字段，索引就是有效的，所以也可以对表SalesHistory的sale_date字段使用极值函数。
这种方法并不是去掉了排序这一过程，而是优化了排序前的查找速度，从而减弱排序对整体性能的影响。
能写在WHERE子句里的条件不要写在HAVING子句里
例如，下面两条SQL语句返回的结果是一样的。
--聚合后使用HAVING子句过滤
SELECT sale_date, SUM(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING sale_date = '2007-10-01';
--聚合前使用WHERE子句过滤
SELECT sale_date, SUM(quantity)
FROM SalesHistory
WHERE sale_date = '2007-10-01'
GROUP BY sale_date;结果：
sale_date sum(quantity)
-------------- --------------
'2007-10-01' 17但是从性能上来看，第二条语句写法效率更高。原因通常有两个。第一个是在使用GROUP BY子句聚合时会进行排序，如果事先通过WHERE子句筛选出一部分行，就能够减轻排序的负担。第二个是在WHERE子句的条件里可以使用索引。HAVING子句是针对聚合后生成的视图进行筛选的，但是很多时候聚合后的视图都没有继承原表的索引结构。
在GROUP BY子句和ORDER BY子句中使用索引
一般来说，GROUP BY子句和ORDER BY子句都会进行排序，来对行进行排列和替换。不过，通过指定带索引的列作为GROUP BY和ORDER BY的列，可以实现高速查询。特别是，在一些数据库中，如果操作对象的列上建立的是唯一索引，那么排序过程本身都会被省略掉。如果大家有兴趣，可以确认一下自己使用的数据库是否支持这个功能。
03、真的用到索引了吗
一般情况下，我们都会对数据量相对较大的表建立索引。简单理解起来，索引的工作原理与C语言中指针数组是一样的。即相比查找复杂对象的数组，查找轻量的指针会更高效。而且最流行的B树索引还进行了一些优化，以使用二分查找来提升查询的速度。
假设我们在一个叫作col_1的列上建立了索引，然后来看一看下面这条SQL语句。这条SQL语句本来是想使用索引，但实际上执行时却进行了全表扫描。很多时候，大家是否也在无意识间就这么写了呢？
在索引字段上进行运算
SELECT ＊
FROM SomeTable
WHERE col_1 ＊ 1.1 > 100;人们普遍认为，SQL语言的主要目的不是进行运算。但是实际上，数据库引擎连这种程度的转换也不会为我们做。
把运算的表达式放到查询条件的右侧，就能用到索引了，像下面这样写就OK了。
WHERE col_1 > 100 / 1.1同样，在查询条件的左侧使用函数时，也不能用到索引。
SELECT ＊
FROM SomeTable
WHERE SUBSTR(col_1, 1, 1) = 'a';如果无法避免在左侧进行运算，那么使用函数索引也是一种办法，但是不太推荐随意这么做。
使用索引时，条件表达式的左侧应该是原始字段
请牢记，这一点是在优化索引时首要关注的地方。
使用IS NULL谓词
通常，索引字段是不存在NULL的，所以指定IS NULL和IS NOT NULL的话会使得索引无法使用，进而导致查询性能低下。
SELECT ＊
FROM SomeTable
WHERE col_1 IS NULL;关于索引字段不存在NULL的原因，简单来说是NULL并不是值。非值不会被包含在值的集合中（详情请参考“三值逻辑和NULL”文章）。
然而，如果需要使用类似IS NOT NULL的功能，又想用到索引，那么可以使用下面的方法，假设“col_1”列的最小值是1。
--IS NOT NULL的代替方案
SELECT ＊
FROM SomeTable
WHERE col_1 > 0;原理很简单，只要使用不等号并指定一个比最小值还小的数，就可以选出col_1中所有的值。因为col_1 > NULL的执行结果是unknown，所以当“col_1”列的值为NULL的行不会被选择。不过，如果要选择“非NULL的行”，正确的做法还是使用IS NOT NULL。上面这种写法意思有些容易混淆，所以也不太推荐，请只在应急的情况下使用。
使用否定形式
下面这几种否定形式不能用到索引。
●
● ! =
● NOT IN
因此，下面的SQL语句也会进行全表扫描。
SELECT ＊
FROM SomeTable
WHERE col_1 100;使用OR
在col_1和col_2上分别建立了不同的索引，或者建立了（col_1, col_2）这样的联合索引时，如果使用OR连接条件，那么要么用不到索引，要么用到了但是效率比AND要差很多。
SELECT ＊
FROM SomeTable
WHERE col_1 > 100 OR col_2 = 'abc';如果无论如何都要使用OR，那么有一种办法是位图索引。但是这种索引的话更新数据时的性能开销会增大，所以使用之前需要权衡一下利弊。
使用联合索引时，列的顺序错误
假设存在这样顺序的一个联合索引“col_1, col_2, col_3”。
这时，指定条件的顺序就很重要。
○ SELECT ＊ FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
○ SELECT ＊ FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ;
× SELECT ＊ FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;
× SELECT ＊ FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;
× SELECT ＊ FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;联合索引中的第一列（col_1）必须写在查询条件的开头，而且索引中列的顺序不能颠倒。有些数据库里顺序颠倒后也能使用索引，但是性能还是比顺序正确时差一些。
如果无法保证查询条件里列的顺序与索引一致，可以考虑将联合索引拆分为多个索引。
使用LIKE谓词进行后方一致或中间一致的匹配
使用LIKE谓词时，只有前方一致的匹配才能用到索引
× SELECT ＊ FROM SomeTable WHERE col_1 LIKE'%a';
× SELECT ＊ FROM SomeTable WHERE col_1 LIKE'%a%';
○ SELECT ＊ FROM SomeTable WHERE col_1 LIKE'a%';进行默认的类型转换
对char类型的“col_1”列指定条件的示例
× SELECT ＊ FROM SomeTable WHERE col_1 = 10;
○ SELECT ＊ FROM SomeTable WHERE col_1 ='10';
○ SELECT ＊ FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));默认的类型转换不仅会增加额外的性能开销，还会导致索引不可用，可以说是有百害而无一利。虽然这样写还不至于出错，但还是不要嫌麻烦，在需要类型转换时显式地进行类型转换吧（别忘了转换要写在条件表达式的右边）。
04、减少中间表
在SQL中，子查询的结果会被看成一张新表，这张新表与原始表一样，可以通过代码进行操作。这种高度的相似性使得SQL编程具有非常强的灵活性，但是如果不加限制地大量使用中间表，会导致查询性能下降。
频繁使用中间表会带来两个问题，一是展开数据需要耗费内存资源，二是原始表中的索引不容易使用到（特别是聚合时）。因此，尽量减少中间表的使用也是提升性能的一个重要方法。
灵活使用HAVING子句
对聚合结果指定筛选条件时，使用HAVING子句是基本原则。不习惯使用HAVING子句的数据库工程师可能会倾向于像下面这样先生成一张中间表，然后在WHERE子句中指定筛选条件。
结果：
sale_date tot_qty
------------ ---------
07-10-01 10
07-10-03 32
07-10-04 22然而，对聚合结果指定筛选条件时不需要专门生成中间表，像下面这样使用HAVING子句就可以。
SELECT sale_date, MAX(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING MAX(quantity) >= 10;HAVING子句和聚合操作是同时执行的，所以比起生成中间表后再执行的WHERE子句，效率会更高一些，而且代码看起来也更简洁。
需要对多个字段使用IN谓词时，将它们汇总到一处
SQL-92中加入了行与行比较的功能。这样一来，比较谓词＝、＜、＞和IN谓词的参数就不能是标量值，而应是值列表了。
我们来看一下下面这道例子。这里对多个字段使用了IN谓词，“id”列是主键。
SELECT id, state, city
FROM Addresses1 A1
WHERE state IN
(SELECT state
FROM Addresses2 A2
WHERE A1.id = A2.id)
AND city IN
(SELECT city
FROM Addresses2 A2
WHERE A1.id = A2.id);这段代码中用到了两个子查询。但是，如果像下面这样把字段连接在一起，那么就能把逻辑写在一处了。
SELECT ＊
FROM Addresses1 A1
WHERE id || state || city IN
(SELECT id || state|| city
FROM Addresses2 A2);这样一来，子查询不用考虑关联性，而且只执行一次就可以。此外，如果所用的数据库实现了行与行的比较，那么我们也可以像下面这样，在IN中写多个字段的组合。
SELECT ＊
FROM Addresses1 A1
WHERE (id, state, city) IN
(SELECT id, state, city
FROM Addresses2 A2);这种方法与前面的连接字段的方法相比有两个优点。一是不用担心连接字段时出现的类型转换问题，二是这种方法不会对字段进行加工，因此可以使用索引。
先进行连接再进行聚合
在“外连接的用法”一文中提到过，连接和聚合同时使用时，先进行连接操作可以避免产生中间表。原因是，从集合运算的角度来看，连接做的是“乘法运算”。连接表双方是一对一、一对多的关系时，连接运算后数据的行数不会增加。而且，因为在很多设计中多对多的关系都可以分解成两个一对多的关系，因此这个技巧在大部分情况下都可以使用。
合理地使用视图
视图是非常方便的工具，相信日常工作中很多人都在频繁地使用。但是，如果没有经过深入思考就定义复杂的视图，可能会带来巨大的性能问题。特别是视图的定义语句中包含以下运算的时候，SQL会非常低效，执行速度也会变得非常慢。
● 聚合函数（AVG、COUNT、SUM、MIN、MAX）
● 集合运算符（UNION、INTERSECT、EXCEPT等）
一般来说，要格外注意避免在视图中进行聚合操作。最近越来越多的数据库为了解决视图的这个缺点，实现了物化视图（materialized view）等技术。当视图的定义变得复杂时，可以考虑使用一下。
05、小结
本篇文章重点介绍了SQL性能优化方面的一些注意事项。虽然这里列举了几个要点，但其实优化的核心思想只有一个，那就是找出性能瓶颈所在，重点解决它。
