读SQL进阶教程笔记16_SQL优化让SQL飞起来
1. 查询速度慢并不只是因为SQL语句本身，还可能是因为内存分配不佳、文件结构不合理等其他原因
1.1. 都是为了减少对硬盘的访问
2. 不同代码能够得出相同结果
2.1. 从理论上来说，得到相同结果的不同代码应该有相同的性能
2.2. 遗憾的是，查询优化器生成的执行计划很大程度上要受到代码外部结构的影响
2.3. 如果想优化查询性能，必须知道如何写代码才能使优化器的执行效率更高
3. 使用高效的查询
3.1. 参数是子查询时，使用EXISTS代替IN
3.1.1. IN谓词却有成为性能优化的瓶颈的危险
3.1.1.1. 当IN的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时的工作表里（内联视图），然后扫描整个视图
3.1.1.2. 从代码的可读性上来看，IN要比EXISTS好
3.1.2. 示例
3.1.2.1.
--慢
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);3.1.2.1.1. 如果连接列（id）上建立了索引，那么查询Class_B时不用查实际的表，只需查索引就可以了
3.1.2.1.2. 如果使用EXISTS，那么只要查到一行数据满足条件就会终止查询，不用像使用IN时一样扫描全表
3.1.2.1.2.1. 在这一点上NOT EXISTS也一样
3.1.2.1.3. 使用EXISTS的话，数据库不会生成临时的工作表
3.2. 参数是子查询时，使用连接代替IN
3.2.1. 示例
3.2.1.1. --使用连接代替IN
SELECT A.id, A.name
FROM Class_A A INNER JOIN Class_B B
ON A.id = B.id;3.2.1.1.1. 至少能用到一张表的“id”列上的索引
3.2.1.1.2. 没有了子查询，所以数据库也不会生成中间表
3.2.1.1.3. 如果没有索引，那么与连接相比，可能EXISTS会略胜一筹
4. 避免排序
4.1. 在SQL语言中，用户不能显式地命令数据库进行排序操作
4.2. 对用户隐藏这样的操作正是SQL的设计思想
4.3. 在数据库内部频繁地进行着暗中的排序
4.3.1. 会进行排序的代表性的运算
4.3.1.1. GROUP BY子句
4.3.1.2. ORDER BY子句
4.3.1.3. 聚合函数（SUM、COUNT、AVG、MAX、MIN）
4.3.1.4. DISTINCT
4.3.1.5. 集合运算符（UNION、INTERSECT、EXCEPT）
4.3.1.6. 窗口函数（RANK、ROW_NUMBER等）
4.4. 灵活使用集合运算符的ALL可选项
4.4.1. 如果不在乎结果中是否有重复数据，或者事先知道不会有重复数据，请使用UNION ALL代替UNION
4.4.2. 加上ALL可选项是优化性能的一个非常有效的手段
4.4.3. 对于INTERSECT和EXCEPT也是一样的，加上ALL可选项后就不会进行排序了
4.5. 使用EXISTS代替DISTINCT
4.5.1. 为了排除重复数据，DISTINCT也会进行排序
4.5.1.1.
SELECT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;4.5.1.2.
SELECT DISTINCT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;4.5.1.3.
SELECT item_no
FROM Items I
WHERE EXISTS
(SELECT ＊
FROM SalesHistory SH
WHERE I.item_no = SH.item_no);4.6. 在极值函数中使用索引（MAX/MIN）
4.6.1. 使用这两个函数时都会进行排序
4.6.1.1. --这样写需要扫描全表
SELECT MAX(item)
FROM Items;4.6.2. 如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表
4.6.2.1. --这样写能用到索引
SELECT MAX(item_no)
FROM Items;4.6.3. 对于联合索引，只要查询条件是联合索引的第一个字段，索引就是有效的
4.6.4. 这种方法并不是去掉了排序这一过程，而是优化了排序前的查找速度，从而减弱排序对整体性能的影响
4.7. 能写在WHERE子句里的条件不要写在HAVING子句里
4.7.1. --聚合后使用HAVING子句过滤
SELECT sale_date, SUM(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING sale_date = '2007-10-01';4.7.2. --聚合前使用WHERE子句过滤
SELECT sale_date, SUM(quantity)
FROM SalesHistory
WHERE sale_date = '2007-10-01'
GROUP BY sale_date;4.7.2.1. 在使用GROUP BY子句聚合时会进行排序，如果事先通过WHERE子句筛选出一部分行，就能够减轻排序的负担
4.7.2.2. 第二个是在WHERE子句的条件里可以使用索引。HAVING子句是针对聚合后生成的视图进行筛选的，但是很多时候聚合后的视图都没有继承原表的索引结构
4.8. 在GROUP BY子句和ORDER BY子句中使用索引
4.8.1. 通过指定带索引的列作为GROUP BY和ORDER BY的列，可以实现高速查询
4.8.2. 在一些数据库中，如果操作对象的列上建立的是唯一索引，那么排序过程本身都会被省略掉
5. 真正用到索引！
5.1. 在索引字段上进行运算
5.1.1.
SELECT ＊
FROM SomeTable
WHERE col_1 ＊ 1.1 ＞ 100;5.2. 把运算的表达式放到查询条件的右侧，就能用到索引了
5.2.1. WHERE col_1 ＞ 100 / 1.1
5.3. 在查询条件的左侧使用函数时，也不能用到索引
5.3.1.
SELECT ＊
FROM SomeTable
WHERE SUBSTR(col_1, 1, 1) = 'a';5.4. 如果无法避免在左侧进行运算，那么使用函数索引也是一种办法
5.5. 使用索引时，条件表达式的左侧应该是原始字段
5.6. 使用IS NULL谓词
5.6.1. 索引字段是不存在NULL的，所以指定IS NULL和IS NOT NULL的话会使得索引无法使用，进而导致查询性能低下
5.6.1.1.
SELECT ＊
FROM SomeTable
WHERE col_1 IS NULL;5.6.1.2. --IS NOT NULL的代替方案
SELECT ＊
FROM SomeTable
WHERE col_1 ＞ 0;5.6.1.2.1. 如果要选择“非NULL的行”，正确的做法还是使用IS NOT NULL
5.7. 使用否定形式
5.7.1. 否定形式不能用到索引
5.7.1.1. ＜＞
5.7.1.2. ! =
5.7.1.3. NOT IN
5.8. 使用OR
5.8.1. 在col_1和col_2上分别建立了不同的索引，或者建立了（col_1, col_2）这样的联合索引时，如果使用OR连接条件，那么要么用不到索引，要么用到了但是效率比AND要差很多
5.8.2. 如果无论如何都要使用OR，那么有一种办法是位图索引。但是这种索引的话更新数据时的性能开销会增大
5.9. 使用联合索引时，列的顺序错误
5.9.1. 假设存在这样顺序的一个联合索引“col_1, col_2, col_3”
5.9.2.
○ 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 ;5.9.3. 联合索引中的第一列（col_1）必须写在查询条件的开头，而且索引中列的顺序不能颠倒
5.9.4. 有些数据库里顺序颠倒后也能使用索引，但是性能还是比顺序正确时差一些
5.9.5. 如果无法保证查询条件里列的顺序与索引一致，可以考虑将联合索引拆分为多个索引
5.10. 使用LIKE谓词进行后方一致或中间一致的匹配
5.10.1. 只有前方一致的匹配才能用到索引
5.10.2.
× SELECT＊ FROMSomeTableWHEREcol_1LIKE '%a';
× SELECT＊ FROMSomeTableWHEREcol_1LIKE '%a%';
○ SELECT＊ FROMSomeTableWHEREcol_1LIKE 'a%';5.11. 进行默认的类型转换
5.11.1. 默认的类型转换不仅会增加额外的性能开销，还会导致索引不可用
5.11.2. 在需要类型转换时显式地进行类型转换
6. 减少中间表
6.1. 子查询的结果会被看成一张新表，这张新表与原始表一样，可以通过代码进行操作
6.2. 灵活使用HAVING子句
6.2.1. 对聚合结果指定筛选条件时不需要专门生成中间表
6.2.2.
SELECT sale_date, MAX(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING MAX(quantity) ＞= 10;6.2.3. HAVING子句和聚合操作是同时执行的，所以比起生成中间表后再执行的WHERE子句，效率会更高一些，而且代码看起来也更简洁
6.3. 需要对多个字段使用IN谓词时，将它们汇总到一处
6.3.1.
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);6.3.2.
SELECT ＊
FROM Addresses1 A1
WHERE id || state || city
IN (SELECT id || state|| city
FROM Addresses2 A2);6.3.2.1. 子查询不用考虑关联性，而且只执行一次就可以
6.3.3.
SELECT ＊
FROM Addresses1 A1
WHERE (id, state, city)
IN (SELECT id, state, city
FROM Addresses2 A2);6.3.3.1. 不用担心连接字段时出现的类型转换问题
6.3.3.2. 不会对字段进行加工，因此可以使用索引
6.4. 先进行连接再进行聚合
6.4.1. 连接和聚合同时使用时，先进行连接操作可以避免产生中间表
6.4.1.1. 连接做的是“乘法运算”
6.4.1.2. 连接表双方是一对一、一对多的关系时，连接运算后数据的行数不会增加
6.5. 合理地使用视图
6.5.1. 特别是视图的定义语句中包含以下运算的时候，SQL会非常低效，执行速度也会变得非常慢
6.5.1.1. 聚合函数（AVG、COUNT、SUM、MIN、MAX）
6.5.1.2. 集合运算符（UNION、INTERSECT、EXCEPT等）
6.5.2. 物化视图（materialized view）等技术。当视图的定义变得复杂时，可以考虑使用一下
来源:https://www.cnblogs.com/lying7/p/17380168.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]