专业剑 : SQL进阶教程

CASE表达式

自连接

三值逻辑和NULL

NOT IN 和 NOT EXISTS不是等价的

Class_A

Class_B

需求：查询与B班住在sz的学生年龄不同的A班的学生。

希望得到的结果是tt2和tt3。

SELECT * FROM Class_A WHERE age NOT IN (SELECT age FROM Class_B WHERE city='sz');

结果是空。

SELECT * FROM Class_A WHERE age NOT IN (22, 23, NULL);

SELECT * FROM Class_A WHERE NOT age IN (22, 23, NULL);

SELECT * FROM Class_A WHERE NOT (age = 22 OR age = 23 OR age = NULL);

SELECT * FROM Class_A WHERE NOT (age = 22) AND NOT (age = 23) AND NOT (age = NULL);

SELECT * FROM Class_A WHERE (age <> 22) AND (age <> 23) AND (age <> NULL); // 用<>等价改写NOT和=

SELECT * FROM Class_A WHERE (age <> 22) AND (age <> 23) AND (unknown); // 对NULL用<>后，结果为unknown

SELECT * FROM Class_A WHERE false或unknown; // 任何一行记录，当AND运算符包含unknown，则结果不为true

改为EXISTS。

SELECT * FROM Class_A A WHERE NOT EXISTS (SELECT * FROM Class_B B WHERE A.age=B.age AND B.city='sz');

SELECT * FROM Class_A A WHERE NOT EXISTS (SELECT * FROM Class_B B WHERE A.age=NULL AND B.city='sz'); // 当记录为NULL

SELECT * FROM Class_A A WHERE NOT EXISTS (SELECT * FROM Class_B B WHERE unknown AND B.city='sz'); 

当记录为NULL，子查询返回结果为空，因此NOT EXISTS为true。

HAVING子句

SQL是基于面向集合思想设计的语言。

以前的SQL标准要求HAVING和GROUP BY必须一起使用。

现在的SQL标准，HAVING子句可以单独使用。可以认为是对空字段进行了GROUP BY操作，只不过省略了GROUP BY。只不过在SELECT子句中需要使用聚合函数。

外连接

用关联子查询比较行与行

用SQL进行集合运算

EXISTS谓词的用法

什么是谓词？

谓词是特殊的函数，返回值是真值，包括true，false或者unknown。因为SQL采用的是三值逻辑，因此具有三种真值。

EXISTS谓词的参数是行数据的集合，也称为二阶谓词。

EXISTS以外的谓词（比如=、>、<、LIKE、BETWEEN、IN）的参数是一行数据，也称为一阶谓词。

SELECT id FROM Foo F WHERE EXISTS (SELECT * )

用SQL处理数列

SQL性能优化

脚本 → 查询优化器 → 执行计划

高效查询

子查询时，使用EXISTS代替IN

如果IN的参数是简单的数值列表，比如(1, 2, 3)，问题不大。如果参数是子查询时，需要特别注意。

T_students 学生表

 T_enrollments 选课表

查询至少选择了一门课程的学生ID和名字

SELECT ID, Name FROM T_students WHERE ID IN (SELECT ID FROM T_enrollments);

SELECT ID, Name FROM T_students WHERE EXISTS (SELECT 1 FROM T_enrollments WHERE T_students.ID = T_enrollments.ID);

使用EXISTS更快的原因：

1. 如果ID列上建了索引，查询T_enrollments表时不用查询实际表，只需查索引就可以了。

2. 如果使用EXISTS，那么只需要查到一行数据满足条件就会终止查询，不会像使用IN时一样扫描全表。在这一点上，NOT EXISTS也一样。

另外，使用IN时，数据库会首先执行子查询，然后将结果存储在临时工作表（内联视图），然后扫描整个视图，非常耗费资源。使用EXISTS不会生成临时工作表。

子查询时，使用连接代替IN

SELECT ID, Name FROM T_students T1 INNER JOIN T_enrollments T2 ON T1.ID = T2.ID

不会生成临时工作表。如果在ID列上建了索引，会使用索引。

避免排序

会进行排序的代表性运算包括：

GROUP BY

ORDER BY

聚合函数，SUM、COUNT、AVG、MAX、MIN

DISTINCT

集合运算符，UNION、INTERSECT、EXCEPT

窗口函数，RANK、ROW_NUMBER

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

集合运算符，UNION、INTERSECT、EXCEPT，在默认方式下会为了排除重复数据而进行排序。

如果不在乎结果是否有重复数据，或者已知不会有重复数据，可以使用UNION ALL代替UNION，这样不会有排序。

使用EXISTS代替DISTINCT

为了排除重复数据，DISTINCT也会进行排序。

Items表

SalesHistory表

SELECT DISTINCT I.item_no FROM Items I INNER JOIN SalesHistory SH ON I.item_no = SH.item_no;

SELECT item_no FROM Items I WHERE EXISTS (SELECT * FROM SalesHistory SH WHERE I.item_no = SH.item_no);

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

SELECT sale_date, SUM(quantity) FROM SalesHistory GROUP BY sale_date HAVING sale_date = '2007-10-01';

SELECT sale_date, SUM(quantity) FROM SalesHistory WHERE sale_date = '2007-10-01' GROUP BY sale_date;

上面两条语句返回结果一样，但是从性能上，第二条效率更高。

因为第一在使用GROUP BY子句聚合时会进行排序，如果先用WHERE条件筛选，能够减轻排序的性能负担。

第二是在使用WHERE条件时可以使用索引。

HAVING子句是针对聚合后生成的视图进行筛选的，很多时候聚合后的视图都没有继承原表的索引结构。

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

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

索引失效的场景

在索引字段上进行运算

SELECT * FROM T WHERE col_1 * 1.1 > 100;

在col_1建立了索引，实际执行时进行了全表扫描。

因为SQL语言的主要目的不是进行计算。实际上，数据库引擎连上面这种程度的转换都不会为我们做。

SELECT * FROM T WHERE col_1 > 100 / 1.1;

把表达式放到查询条件运算符的右侧，就能用到索引了。

SELECT * FROM T WHERE SUBSTR(col_1, 1, 1) = 'a';

同样，在查询条件左侧使用函数，也不能使用到索引。如果无法避免在左侧进行计算，那么使用函数索引也是一种方法，但不太推荐。

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

使用IS NULL谓词

通常索引字段是不存在NULL的，所以指定IS NULL或者IS NOT NULL的话会让索引无法使用。

SELECT * FROM T WHERE col_1 * 1.1 IS NULL;

索引字段是不存在NULL的原因，简单地说NULL不是值。非值不会包含在值的集合中。

假设col_1的最小值是1，代替IS NOT NULL的方法.

SELECT * FROM T WHERE col_1 * 1.1 > 0;

两种NULL，分别指的是未知unknown和不适用not applicable。

未知unknown：不知道带墨镜的人的眼睛的颜色。

不适用not applicable：不知道冰箱的眼睛的颜色。

比如有一列是分娩次数，而记录是男性，该列记为NULL，区别于0。因为如果是女性，0表示没有分娩过。如果是男性，NULL表示不适用。

比如有一列是眼睛的颜色，某一条记录该列记为NULL，表示未知。

为什么必须写成IS NULL，而不是= NULL

以下子式都会判为unknown

1 = NULL

2 > NULL

3 < NULL

4 <> NULL

NULL = NULL

而WHERE子句只会返回结果为true的行，不会包含判断结果为false或unknown的行。

因为NULL既不是值也不是变量，只是一个表示“没有值”的标记。而比较谓词只适用于值。

NULL是个标记，不是值，也没有类型（字符型或整形等等）。

应该把IS NULL整体当做一个谓词。

使用否定形式

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

<>

!=

NOT IN

下面语句会引起全表扫描。

SELECT * FROM T WHERE col_1 <> 100;

解决方法：改用IN。

关于索引的理解

数据库的表本质上是文件，文件存储到硬盘。IO速度是瓶颈。所以一般数据库会设计缓存。

索引本质是对应表的字段的一个有序数组。

表的数据是无序集合。在无序集合中查找数据，全表扫描，速度慢。在有序数组中查找数据，速度快。

比如一张学生表，对姓名做了索引，相当于建了一张放在内存中的有序数组。

在这个有序数组中查找张三，速度快。

为什么<>或者!=操作一般没有用到索引。

比如说：查 age != 25，数据库可以用索引直接定位到目录中“25”的位置，快速找到结果，然后排除该结果，把其他记录全部返回，不就可以利用索引嘛。

如果表中大部分记录都 != 25（比如只有1条记录是 25，其他99条都不是），那么通过索引排除 25 是高效的。

但如果表中一半记录是 25，一半不是，数据库会发现：

通过索引排除 25 后，仍然需要读取大量分散的数据页（因为非 25 的记录可能遍布整个表）。此时直接全表扫描可能更快（因为顺序读取比随机索引跳转更节省I/O）。

这里的关键是索引存储了记录对应的数据页的位置。

使用OR

比如在列col_1和col_2上分别建立了不同索引，或者建立了联合索引（col_1, col_2）。

如果使用OR连接条件，要么用不到索引，要么用到了效率比AND差很多。

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

解决方法：使用位图索引。但是这种索引在更新数据时性能开销会增大。

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

假设建立了联合索引（col_1，col_2，col_3）。这时指定条件的顺序就很重要。

SELECT * FROM T WHERE col_1 = 100 AND col_2 = 'abc'; // Y

SELECT * FROM T WHERE col_1 = 100 AND col_2 = 'abc' AND col_3 = 500; // Y

SELECT * FROM T WHERE col_1 = 100 AND col_3 = 500; // N

SELECT * FROM T WHERE col_2 = 'abc' AND col_3 = 500; // N

SELECT * FROM T WHERE col_2 = 'abc' AND col_1 = 100; // N

解决方法：拆分联合索引为多个索引，或者多个联合索引。

如果查询条件是（col_1，col_2，col_3，col_4）仍然会匹配该联合索引。

联合索引的最左原则。

注意PGSQL的优化器会自动调整查询条件的顺序，尽量匹配索引。

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

使用LIKE谓词时，只有前方一致的匹配才能用到索引。

SELECT * FROM T WHERE col_1 LIKE 'a%';  // Y

SELECT * FROM T WHERE col_1 LIKE '%a%';  // N

SELECT * FROM T WHERE col_1 LIKE '%a';  // N

使用默认的类型转换

假设col_1类型是char。

SELECT * FROM T WHERE col_1 = 10;  // N，默认的类型转换

SELECT * FROM T WHERE col_1 = '10';  // Y

SELECT * FROM T WHERE col_1 = CAST(10, AS CHAR(2));  // Y，显示的类型转换，注意要写在表达式右侧

默认的类型转换不仅会增加额外的性能开销，而且导致索引不可用。

减少中间表

SQL子查询的结果会被看成一张新表。如果不加限制使用中间表，会导致查询性能下降。

一是展开数据会耗费内存，二是原始表中的索引不容易使用到，特别是聚合时。

灵活使用HAVING子句

对聚合结果指定筛选条件时，使用HAVING子句是基本原则。

SELECT * FROM (SELECT sale_date, MAX(quanity) AS max_qty FROM salesHistory GROUP BY sale_date) TMP // 没用的中间表

WHERE max_qty >= 10;

SELECT sale_date, MAX(quanity) FROM salesHistory GROUP BY sale_date HAVING MAX(quanity) >= 10;

注意与上例的不同，HAVING子句中使用的条件是聚合函数MAX，这是为什么不能用WHERE子句。

HAVING子句和聚合操作是同时进行的，所以比生成中间表再执行WHERE子句，效率更高，而且代码更简洁。

需要对多个字段使用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 Addresses2 A2 WHERE A1.id = A2.id);

通过把字段连接在一起，可以改为以下。

SELECT id, state, city FROM Addresses1 A1 

WHERE id || state || city IN (SELECT id || state || city FROM Addresses2 A2);

如果数据库实现了行与行比较，可以改为以下。

SELECT id, state, city FROM Addresses1 A1 

WHERE (id, state, city) IN (SELECT (id, state, city) FROM Addresses2 A2);

优点一是不用担心连接字段时出现的类型转换问题，二是没有对字段进行加工，因此可以使用索引。

PostgreSQL和Oracle数据库都支持这种多字段组合的IN子句查询。

先进行连接再进行聚合

原因是，从集合运算的角度，连接做的是“乘法运算”。连接表是1对1、1对多关系时，连接运算后数据的行数不会增加。

合理地使用视图

视图是非常方便的工具。如果定义复杂的视图，可能会带来巨大的性能问题。特别是定义视图包括以下运算时会非常低效，运行速度非常慢。

聚合函数（AVG、COUNT、SUM、MIN、MAX）

集合运算符（UNION、INTERSECT、EXCEPT等）

有的数据库为了解决视图这个缺点，实现了物化视图。

SQL编程

GROUP BY和PARTITION BY