读SQL进阶教程笔记16_SQL优化让SQL飞起来

艺兴 · 发表于 2023-5-15 12:18:41

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 ＊ FROM SomeTable WHERE col_1 LIKE '%a';
× SELECT ＊ FROM SomeTable WHERE col_1 LIKE '%a%';
○ SELECT ＊ FROM SomeTable WHERE col_1 LIKE '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】我们会及时删除侵权内容，谢谢合作！

读SQL进阶教程笔记16_SQL优化让SQL飞起来

本帖子中包含更多资源