最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

读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  ＊   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）等技术。当视图的定义变得复杂时，可以考虑使用一下

关键词：