<<SQL进阶教程>>([日]MICK/著 吴炎昌/译)之性能优化篇

一：使用高效的查询
1.参数是子查询时，使用EXISTS代替IN；
如果参数是"1,2,3"这样的数值列表，一般不需要特别注意。
使用EXISTS时更快的原因有以下2个：
1.1如果连接列(id)上建立了索引，那么查询里面表时不要查实际的表，只需查索引就可以了。
1.2如果使用EXISTS，那么只要查到一行数据满足条件就会终止查询，不要像使用IN时一样扫描全表。在这一点上NOT EXISTS也一样。
当IN的参数是子查询时，数据库首先执行子查询，然后将结果存储在一张临时的工作表里（内联视图），然后扫描整个视图，耗费资源，EXISTS不会。
2.参数是子查询时，使用连接代替IN；
可用到索引，如果没有索引，那么与连接相比，可能EXISTS会更快。
二：避免排序
在数据库内部频繁地进行着暗中的排序的运算有下面这些。
GROUP BY 子句
ORDER BY 子句
聚合函数(SUM\COUNT\AVG\MAX\MIN)
DISTINCT
集合运算符(UNION\INTERSECT\EXCEPT(即ORACLE的MINUX))
窗口函数(RANK\ROW_NUMBER等)
内存不足的话需要在硬盘上排序（据说硬盘的访问速度比内存的要慢上100万倍）
1.灵活使用集合运算符的ALL可选项；
UNION ALL不会排序，且几乎每种数据库都支持，DB2和PostgreSQL另外2个集合运算符也支持ALL可选项。
2.使用EXISTS代替DISTINCT；
一对一和一对多时比较实用，当然优先使用EXISTS。
3.在极值函数中使用索引(MAX\MIN)；
SQL语言里有MAX和MIN两个极值函数，使用这2个函数时都会进行排序。
对于联合索引，只有查询条件是联合索引的第一个字段，索引就是有效的。
这种方法并不是去掉了排序这一过程，而是优化了排序前的查找速度（扫描索引而不是扫描整张表），从而减弱排序对整体性能的影响。
4.能写在WHERE子句里的条件不要写在HAVING子句里；
使用GROUP BY子句聚合时会进行排序，如果事先通过WHERE子句筛选出一部分行，就能减轻排序的负担。
WHERE子句可以使用索引，HAVING子句是针对聚合后生成的视图进行筛选的，很多时候并无继承原表的索引结构。
5.在GROUP BY子句和ORDER BY子句中使用索引；
通过指定带索引的列作为GROUP BY和ORDER BY的列，可以实现高速查询。
三：真的用到索引了吗(索引：轻量化查询更高效)
1.在索引字段上进行运算，不会用到索引；
如col_1列建立了索引，where col_1 * 1.1 > 100;或where substr(col_1, 1, 1) = ‘a‘;
使用索引时，条件表达式的左侧应该是原始字段，这一点是优化索引时首要关注的地方。函数索引可用但少用。
2.使用IS NULL或IS NOT NULL，不用到索引，但ORACLE和DB2也能用到索引，可能因为他们实现NULL时赋了某个具有特殊含义的值；
索引字段不存在NULL，因为NULL并不是值，非值不会被包含在值的集合中。
另IS NOT NULL可用>列的最小值代替，基本可得到一样的结果，且能索引，但最准确还是IS NOT NULL，不推荐。
3.使用否定形式，不会用到索引；
下面这几种否定形式不会用到索引。
<>
!=
NOT IN
4.使用OR，不会用到索引；
在col_1和col_2上分别建立了不同的索引，或者建立了(col_1,col_2)这样的联合索引时，如果使用or连接条件，要么用不到索引，要么用
到了但是效率比AND要差很多。如where col_1 > 100 or col_2 = ‘abc‘;
可用位图索引，但这种索引更新数据时性能开销会增大，索引使用之前要权衡一下利弊。
5.使用联合索引时，列的顺序错误，不会用到索引；
联合索引中的第一列必须写在查询条件开头，而且索引中列的顺序不能颠倒（可考虑将联合索引拆分为多个索引）。
假如有这样一个联合索引“col_1,col_2,col_3”，
where col_1=10 and col_2=100;可用到;
where col_1=10 and col_3=500;无法用到;
where col_2=10 and col_3=500;无法用到;
where col_2=10 and col_1=20; 无法用到;
6.使用LIKE谓词进行后方一致或中间一致的匹配，不会用到索引；
使用LIKE谓词时，只有前方一致的匹配才能用到索引，所以where col_1 like ‘%a%‘和where col_1 like ‘%a‘都用不到索引。
7.进行默认的类型转换，不会用到索引；
如对char类型的"col_1"列指定条件查询where col_1=10不会用到索引，而where col_1=‘10‘和where col_1=cast(10,as char(2))可用到。
四：减少中间表
中间表会带来2个问题，1是展开数据需要耗费内存资源，2是原始表中的索引不容易使用到（特别是聚合时）。
1.灵活使用HAVING子句；
对聚合结果指定筛选条件时，使用HAVING子句是基本原则。不要先生成GROUP BY的中间表再在外面套一层用WHERE筛选，直接一层用HAVING筛选。
HAVING子句和聚合操作是同时执行的，索引比起生成中间表后再执行的WHERE子句，效率会更高一些，而且代码看起来更简洁。
2.需要对多个字段使用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 Address2 A2
WHERE A1.id = A2.id);
可以替换成
SELECT *
FROM Addresses1 A1
WHERE id || state || city
IN (SELECT id || state || city
FROM Addresses2 A2);
有点数据库还可以这样替换，没有字段类型隐式转换，而且可以使用到索引
SELECT *
FROM Addresses1 A1
WHERE (id, state, city)
IN (SELECT id, state, city
FROM Addresses2 A2);
3.先进行连接再进行聚合；
连接表双方是一对一、一对多的关系时，连接后数据的行数不会增加，连接和聚合同时使用的话，先进行连接可以避免产生中间表。
4.合理使用视图；
视图的定义语句中包含以下运算的时候，SQL会非常低效，，执行速度也会变得非常慢。
聚合函数（AVG、COUNT、SUM、MIN、MAX）
聚合运算符（UNION、INTERSECT、EXCEPT等）
为避免这个缺点，可以使用物化视图。