本节介绍一些使SQL执行速度更快、消耗内存更少的优化技巧。
使用高效的查询
如果想优化查询性能，我们必须要知道如何写代码才能使优化器的执行效率更高。
参数使子查询时，使用EXISTS代替IN
IN谓词非常方便，而且代码也很容易理解，所以使用的频率很高。但是方便的同时，IN谓词却有成为性能优化瓶颈的危险。一般来说，如果代码种大量使用IN谓词，那么我们只要对它们进行优化，就能大幅度地提高性能。
如果IN的参数时“1，2，3”这样的数值列表，一般还不需要特别注意。但如果参数时子查询，那么就需要注意了。
在大多数时候，[NOT] IN 和 [NOT] EXISTS 返回的结果时相同的。但是当它们用于子查询时，EXISTS的速度会更快一些。
-- 慢
SELECT * FROM Class_A WHERE id IN (SELECT id FROM Class_B) ;
-- 快
SELECT * FROM Class_A A WHERE id EXISTS (SELECT id FROM Class_B B WHERE A.id=B.id) ;
使用EXISTS时更快的原因有以下两个。
1.如果连接列（id)上建立了索引，那么查询Class_B时不用查实际的表，只需查索引就可以了。
2.如果使用EXISTS，那么只要查到一行数据满足条件就会终止查询，不用像使用IN时一样扫描全表。在这一点上，NOT EXISTS也一样。
当IN的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时的工作表（内联视图），接着再扫描整个视图。在很多情况下，这种做法非常消耗资源，而且工资表中通常没有索引。使用EXISTS的话，数据库不会生成临时的工作表。
但从代码的可读性上来看，IN要比EXISTS好。使用IN时的代码看起来更加一目了然，易于理解。因此，如果确信使用IN也能快速获取结果，就没有必要非得改成EXISTS了。
而且，最近有很多DBMS也尝试着改善了IN的性能。例如，在Oracle数据库中，如果我们使用了建索引的列，那么即使使用IN，也会先扫描索引。此外，PostgreSQL从版本7.4开始也改善了使用子查询作为IN谓词参数的查询速度。
参数是子查询时，使用连接代替IN
改善IN的性能，除了使用EXISTS，还可以使用连接。
-- 使用连接代替IN
SELECT A.id,A.name FROM Class_A A INNER JOIN Class_B B ON A.id=B.id ;
这种写法至少能用到一张表的id列上的索引。而且，因为没有子查询，数据库也不会生成中间表。
避免排序
与面向过程语言不同，在SQL语言中，用户不能显示地命令DBMS进行排序操作。对用户隐藏这样的操作正是SQL的设计思想。DBMS内部会频繁地在暗中进行排序。
会进行排序的具有代表性的运算如下：
1.GROUP BY 子句
2.ORDER BY 子句
3.聚合函数（SUM、COUNT、AVG、MAX、MIN）
4.DISTINCT
5.聚合运算符（UNION、INTERSECT、EXCEPT）
6.窗口函数(RANK、ROW_NUMBER等）
排序如果只在内存中进行，那还好；但是如果因内存不足而需要在硬盘上排序，那么排序的性能也会急剧恶化。
因此，我们的目标时尽可能避免无谓的排序。
灵活使用集合运算符的ALL可选项
SQL中有UNION、INTERSECT、EXCEPT三个集合运算符。在默认情况下，这些运算符会为了排除掉重复数据而进行排序。
如果不在乎结果中是否有重复数据，或者事先知道不会有重复数据，那么请使用UNION ALL来代替UNION。这样就不会进行排序了。
对于INTERSECT和EXCEPT也是一样，加上ALL选项后就不会进行排序了。
使用EXISTS代替DISTINCT
为了排除重复数据，DISTINCT也会进行排序。如果需要对两张表的连接结果进行去重，可以考虑使用EXISTS代替DISTINCT,以避免排序。
SELECT DISTINCT I.item_no FROM Items I INNER JOIN SalesHistory SH ON I.item_no=SH.item_no;
SELECT item_no FROM Items I
WHERE EXISTS (SELECT * FROM SalesHistory SH WHERE I.item_no=SH.itme_no);
在极值函数中使用索引（MAX/MIIN）
SQL语句里有MAX和MIN两个极值函数，使用这两个函数时都会进行排序。但是如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表。
-- 这样写需要扫描全表
SELECT MAX(item) FROM Items;
-- 这样写能用到索引
SELECT MAX(item_no) FROM Items;
因为item_no是表Items的唯一索引，所以效果更好。
能写在WHERE子句里的条件不要写在HAVING子句里
-- 聚合后使用HAVING子句过滤
SELECT sale_date,SUM(quantity) FROM SaleHistory
GROUP BY sale_date HAVING sale_date='2018-10-01';
-- 聚合前使用WHERE子句过滤
SELECT sale_date,SUM(quantity) FROM SaleHistory
WHERE sale_date='2018-10-01'
GROUP BY sale_date ;
上面两条SQL语句返回的结果是一样的，但从性能上看，第二条语句写法效率更高。原因有二：其一，在使用GROUP BY子句聚合时会进行排序或散列运算，如果事先通过WHERE子句筛选出一部分行，就能减轻排序的负担；其二，在WHERE子句的条件里可以使用索引，sale_date可以说时非常重要的列，如果该列有索引，那么筛选的效率也会非常高。
HAVING子句是针对聚合后生成的视图进行筛选的，但是很多时候，聚合后的视图并没有继承原来表的索引结构。
在GROUP BY 子句和ORDER BY子句中使用索引
一般来说，GROUP BY 子句和ORDER BY子句都会进行排序，以对行进行排序和替换。通过指定带索引的列作为GROUP BY和ORDER BY的列，可以实现高速查询。
真的用到索引了吗
一般情况下，我们会对数据量相对较大的表建立索引。简单来说，索引的工作原理与C语言中的指针数组是一样的。即相比查找复杂对象的数组，查找轻量的指针会更高效。而且，最流行的B树索引还进行了一些优化，以使用二分查找来提升查询的速度。
在索引字段上进行运算
SELECT * FROM SomeTable WHERE col_1*1.1 >100 ;
--把运算的表达式放到查询条件的右侧，也就能用到索引了。
SELECT * FROM SomeTable WHERE col_1>100/1.1 ;
SELECT * FROM SomeTable WHERE SUBSTR(col_1，1，1）='a' ；
同样，在查询条件的左侧使用函数时，也不会用到索引。
如果无法避免在左侧进行运算，那么使用函数索引也是一种办法，但操作起来很麻烦。使用索引时，列应该是原始字段。
索引字段中存在NULL
索引中的NULL不好处理，对此的实现也各不相同。这是因为使用IS NULL和IS NOT NULL，索引就无法使用了，而且NULL很多的字段也无法使用索引。
SELECT * FROM SomeTable WHERE col_1 IS NULL;
-- IS NOT NULL的替代方案
SELECT * FROM SomeTable WHERE col_1>0 ;
原理很简单，只要使用大于号并指定一个比最小值还小的数，就可以选出col_1中所有的值。因为NULL代表着unknown，所以当col_1列的值为NULL的行不会被选择。
使用否定形式
下面这几种否定形式不能用到索引
<>
!=
NOT IN
-- 也不会用到索引
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 AND col_2 < 200 ；
如果无论如何都要使用OR，那么有一个办法是使用位图索引。但是如果使用这种索引，更新数据时的性能开销会增大，而且索引本身的用途也存在限制。
使用联合索引时，列的顺序错误
假设存在一个顺序是“col_1,col_2,col_3”的联合索引。这时，指定条件的顺序就很重要。
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 200 AND col_3=500；
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 200 A；
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3=500；
SELECT * FROM SomeTable WHERE col_2 = 200 AND col_3=500；
前两个SQL语句可以使用索引，后两条性能不好。
联合索引中的第一列（col_1）必须写在查询条件的开头，而且索引中列的顺序不能颠倒。有些数据库里顺序颠倒后也能使用索引，但是性能还是比顺序正确时差一些。如果无法保证查询条件里列的顺序与索引一致，可以考虑将联合索引拆分为多个索引。
使用LIKE谓词进行后方一致或中间一致的匹配
使用LIKE谓词时，只有前方一致的匹配才能用到索引。
SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';
进行默认的类型转换
SELECT * FROM SomeTable WHERE col_1 =10 ；
SELECT * FROM SomeTable WHERE col_1 ='10';
SELECT * FROM SomeTable WHERE col_1 = CASE(10,AS CHAR(2));
默认的类型转换不仅会增加额外的新能开销，还会导致索引不可用，可以说是有百害而无一利。在需要类型转换时显式地进行类型转换，有利于我们注意避免编写性能低下的查询。
减少中间表
在SQL中，子查询的结果会被看成一张新表。这张新表与原始表一样，可以通过代码进行操作。这种高度的相似性使得SQL编程具有非常强的灵活性，但是如果不加限制地大量使用中间表，也会导致查询性能的下降。
频繁地使用中间表会带来两个问题：一是展开数据需要耗费内存（或存储器）资源，二是原始表中的索引不容易被用到（特别是聚合时）。因此，尽量减少中间表达使用也是一个提升性能的重要方法。
灵活使用HAVING子句
对聚合结果指定筛选条件时，使用HAVING子句时基本原则。
SELECT *
FROM (SELECT sale_date,MAX(quantity) AS max_qty FROM SalesHistory GROUP BY sale_date) TMP -- 没用的中间表
WHERE max_qty >=10;
SELECT sale_date,MAX(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING max_qty >=10;
HAVING子句和聚合操作是同时执行的，所以比起先生成中间表，然后再执行的WHERE子句，效率会更高一些，代码也看起来更简洁。
需要对多个字段使用IN谓词时，先将它们汇总到一处
SQL-92中加入了行与行比较的功能。这样一来，比较谓词=、<、>和IN谓词的参数就不能时标量值，而应是值列表了。
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 id,state,city
FROM Addresses1 A1
WHERE id || state || city IN (SELECT id || state || city FROM Addresses2 A2 ) ;
这样一来，子查询就不用考虑关联性了，而且只执行一次就行。另外，如果所用的数据库实现了行与行的比较，那么我们也可以像下面这样，在IN中写多个字段的组合。
SELECT id,state,city
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)等技术。当视图的定义变得复杂时，大家可以考虑使用这些技术。
本节小结：
数据库和SQL中最大的瓶颈就是对存储器（具有代表性的是硬盘）的访问，所以我们可以通过增加内存或者使用访问速度更快的闪存等方法来提升性能。不管是减少排序还是使用索引，或是避免中间表的使用，都是为了减少对低速存储器的访问。
1.参数是子查询时，使用EXISTS或者连接代替IN。
2.使用索引时，条件表达式的左侧应该时原始字段。
3.在SQL中无法显式地指定排序，但是请注意很多运算会暗中进行排序。
4.尽量减少没用的中间表。
5.尽量编写能压缩记录个数的条件。欠下的“债”总归是要还的。
