SQL进阶教程——让SQL飞起来（第十一章）

1. 前言

本节主要介绍一些使SQL执行速度更快，消耗内存更少的技巧。查询速度慢并不只是因为SQL本身问题，还可能是因为内存分配不佳，文件结构不合理等其他原因。

2. 使用高效的查询

在SQL中，很多时候不同代码能够得出相同结果。从理论上来说，得到相同的不同代码应该有相同的性能，但遗憾的是，查询优化器生成的执行计划很大程度上要受到代码外部结构的影响。因此如果想优化查询性能，必须知道如何写代码才能使优化器的执行效率更高。

2.1 参数是子查询，使用EXISTS代替IN

使用EXISTS时更快的原因有以下两个

• 如果连接列（id）上建立了索引，那么查询时不用查实际的表，只需查索引就可以了
• 如果使用EXISTS时，那么只要查到一行数据满足条件就会终止查询，不用像使用IN时一样扫描全表。在这一点上NOT EXISTS也一样。

当IN的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时的工作表里（内联视图），然后扫描整个视图。很多情况下这种做法非常耗费资源。使用EXISTS的花，数据库不会生成临时的工作表

2.2 参数是子查询，使用连接代替IN

--使用连接代替IN
SELECT A.id ,A.name
	FROM Classs_a A INNER JOIN Class_B
	ON A.id = B.id;

3 避免排序

与面向过程语言不同，在SQL语言中，用户不能显示的命令数据库进行排序操作。对用户隐藏这样的操作正是SQL的设计思想。
但是，这并不意味这数据库内部也不能进行排序，其实正好相反，在数据库内部频繁的进行着暗中的排序。因此最终对于用户来说，了解都有那些运算会进行排序很有必要

• GROUP BY 子句
• ORDER BY子句
• 聚合函数（SUM,COUNT,AVG,MAX,MIN）
• DISTINCT
• 集合运算符（UNION,INTERSECT,EXCEPT）
• 窗口函数（RANK,ROW_NEMBER等）

3.1 灵活使用集合运算符的ALL可选项

如果不在乎结果中是否有重复数据，或者事先知道不会有重复数据，请使用UNION ALL代替UNION。这样就不会进行排序了

但是各种数据库对他的实现情况参差不齐，下表中汇总了目前各种数据库对ALL的实现情况

• 集合运算符ALL可选项的实现情况

	Oracle	DB2	SQLServer	PostgreSQL	MySQL
UNION	〇	〇	〇	〇	〇
INTERSECT	X	〇	X	〇	-
EXCEPT	X	〇	X	〇	-

3.2 使用EXISTS代替DISTINCT

如果需要对两张表的连接结果进行去重，可以考虑使用EXISTS代替DISTINCT，以避免排序

• Items

item_no	item
10	FD
20	CD-R
30	MO
40	DVD

• SaleHistory

sale_date	item_no	quantity
2007-10-01	10	4
2007-10-01	20	10
2007-10-01	30	3
2007-10-03	10	32
2007-10-0	30	12
2007-10-04	20	22
2007-10-04	30	7

-- 查找有销售记录的商品
SELECT Items.item_no
FROM Items INNER JOIN SalesHistory
ON Items.item_no = SalesHistory.item_no;

-- 去重(慢)
SELECT DISTINCT Items.item_no
FROM Items INNER JOIN SalesHistory
ON Items.item_no = SalesHistory.item_no;

-- 去重(快)
SELECT item_no FROM Items WHERE EXISTS (SELECT * FROM SalesHistory WHERE Items.item_no = SalesHistory.item_no);

3.3 在极值函数中使用索引（MAX/MIN）

SQL语言里有MAX和MIN两个极值函数，使用这两个函数都会进行排序。但是如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表。对于联合索引，只要查询条件是联合索引的第一个字段索引就是有效的。这种方法并不是去掉了排序这个过程，而是优化了排序前的查找速度，从而减弱排序对整体性能的影响。

-- 这样写需要扫描全表
SELECT MAX(item) FROM Items;

-- 这样写可以用到索引
SELECT MAX(item_no) FROM items;

3.4 能写在WHERE子句里的条件不要写在HAVING子句里

-- 聚合后使用HAVING子句过滤
SELECT sale_date,SUM(quantity) 
FROM SalesHistory
GROUP BY sale_date
HAVING sale_date = '2007-10-01';

-- 聚合前使用WHERE子句过滤
SELECT sale_date,SUM(quantity)
FROM SalesHistory
WHERE sale_date = '2007-10-01'
GROUP BY sale_date;

写法二效率更高的原因：

• GROUP BY聚合时会进行排序，如果事先通过WHERE子句筛选一部分，能够减轻排序的负担；
• WHERE子句的条件里可以使用索引，HAVING子句是针对聚合后生成的视图进行筛选的，但很多时候聚合后的视图并没有继承原表的索引结构

3.5 在GROUP BY子句和ORDER BY子句中使用索引

一般来说，GROUP BY和ORDER BY子句都会进行排序，来对行进行排序和替换。不过，通过指定带索引的列作为GROUP BY和ORDER BY的列，可以实现高速查询。

4. 真的用到索引了吗

一般情况下，我们都会对数据量相对较大的表建立索引。简单理解起来，索引的工作原理与C语言中指针数组是一样的。即相对查找复杂对象的数组，查找轻量的指针会更高效。

4.1 在索引字段上进行运算

-- 没有使用到索引的情况
SELECT * FROM SomeTable
WHERE col_1 * 1.1 > 100; 

-- 使用到索引的情况
SELECT * FROM SomeTable
WHERE col_1  > 100 / 1.1; 

-- 左侧使用函数也用不到索引
SELECT * FROM SomeTable
WHERE SUBSTR(col_1,1,1) = 'a'; -

使用索引时，条件表达式的左侧应该是原始字段

4.2 使用IS NULL谓词

通常索引字段是不存在NULL的，所以指定IS NULL和IS NOT NULL的话，会使得索引无法使用，进而导致查询性能低下。

-- IS NULL没办法继续优化
SELECT * FROM SomeTable WHERE col_1 IS NULL;

-- IS NOT NULL时，修改成 > 一个比最小值还小的数
SELECT * FROM SomeTable WHERE col_1 > 0; -- 假设col_1最小值是1

4.3 使用否定形式

下面这几种否定形式不能用到索引：

• <>
• !=
• NOT IN

4.4 使用OR

在col_1和col_2上分别建立了不同的索引，或者建立了联合索引时，如果使用OR连接条件，那么要么用不到索引，要么用到了但是效率比AND要差很多

SELECT *
	FROM SomeTable
WHERE col_1 > 100
OR col_2 = 'abc';

4.5 使用联合索引时，列的顺序错误

假设存在这样顺序的一个联合索引"col_1,col_2,col_3"

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;                 -- 'x'
SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500;                -- 'x'
SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10;                 -- 'x'
--联合索引的第一列必须写在查询条件的开头，而且索引中列的顺序不能颠倒

4.6 使用LIKE谓词进行后方一致或中间一致的匹配

只有前方一直的匹配才能用到索引

SELECT * FROM SomeTable WHERE col_1 LIKE '%a';  -- 'x'
SELECT * FROM SomeTable WHERE col_1 LIKE '%a%'; -- 'x'
SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';  -- '●'

4.7 进行默认的类型转换

对CHAR类型的列’col_1’指定条件的示例

SELECT * FROM SomeTable WHERE col_1 = 10;                  -- 'X'
SELECT * FROM SomeTable WHERE col_1 = '10';                -- '●'
SELECT * FROM SomeTable WHERE col_1 = CAST(10,AS CHAR(2)); -- '●'
/**默认的类型转换不仅会增加额外的性能开销，还会导致索引不可用，可以说是有百害而无一利**/

5. 减少中间表

在SQL中，子查询会被看成一张新表，这张新表与原始表一样，可以通过代码进行操作。这种高度的相似性使得SQL编程具有非常强的灵活性，，但是，如果不加限制地大量使用中间包，将会导致查询性能下降
频繁使用中间表会带来两个问题，一是展开数据需要耗费内存资源，二是原始表中的索引不容易使用（特别是聚合时）。因此，尽量减少中间表的使用也是调高性能的一个重要方法

5.1 灵活地使用HAVING子句

-- 无意义的中间表
SELECT * FROM 
(SELECT sale_date,MAX(quantity) AS max_qty FROM SalesHistory GROUP BY sale_date) TMP
WHERE max_qty >= 10;

-- HAVING
SELECT * FROM SalesHistory GROUP BY sale_date HAVING MAX(quantity) >= 10;

5.2 需要对多个字段使用IN谓词时，将它们汇总到一处

-- 多个字段使用IN
SELECT id,state,city FROM Address1 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);

-- 通过字段连接(但可能带来类型转换问题，无法使用索引)
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); 
--这种方法与前面的连接字段的方法相比有两个优点。一是不用担心连接字段时出现的类型转化问题;二是这种方法不会对字段进行加工，因此可以使用索引

5.3 先进行连接在进行聚合

连接和聚合同时使用时，先进行连接操作可以避免产生中间表。

5.4 合理地使用视图

视图是很方便的工具，但是如果没有经过深入思考就定义复杂的视图，可能会带来巨大的性能问题。特别是视图的定义语句中包含以下运算的时候，SQL会非常低效，执行速度也会变得非常慢

• 聚合函数（AVG,COUNT,SUM,MIN,MAX）
• 集合运算符（UNION,INTERSECT,EXCEPT）

一般来说，要格外注意避免在视图中进行聚合操作

本节要点：

1. 参数是子查询时，使用EXISTS或者连接代替IN
2. 使用索引时，条件表达式的左侧应该是原始字段
3. 在SQL中排序无法显示的指定，但是请注意很多运算都会暗中进行排序
4. 尽量减少没用的中间表