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

dreamhua 2020-03-28

一:使用高效的查询
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等)
为避免这个缺点,可以使用物化视图。

相关推荐