基于规则的优化（内含子查询优化二三事）-优快云博客

本文链接：https://blog.youkuaiyun.com/cpxsxn/article/details/144168597

MySQL本质上是一个软件，设计MySQL的大佬并不能要求使用这个软件的人个个都是数据库高高手，就像我写这本书的时候并不能要求各位在学之前就会了里边儿的知识。也就是说我们无法避免某些同学写一些执行起来十分耗费性能的语句。即使是这样，设计MySQL的大佬还是依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写（就是人家觉得你写的语句不好，自己再重写一遍）。本章详细介绍一下一些比较重要的重写规则。

条件化简

条件化简
移除不必要的括号
常量传递（constant_propagation）
等值传递（equality_propagation）
移除没用的条件（trivial_condition_removal）
表达式计算
HAVING子句和WHERE子句的合并
常量表检测
- 查询的表中一条记录没有，或者只有一条记录
- 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。
  设计MySQL的大佬觉得这两种查询花费的时间特别少，少到可以忽略，所以也把通过这两种方式查询的表称之为常量表（英文名：constant tables）

外连接消除

我们知道内连接的驱动表和被驱动表的位置可以相互转换，而左（外）连接和右（外）连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。

另外WHERE子句的杀伤力比较大，凡是不符合WHERE子句中条件的记录都不会参与连接。只要我们在搜索条件中指定关于被驱动表相关列的值不为NULL，那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了，也就是说：在这种情况下：外连接和内连接也就没有什么区别了！比方说这个查询：

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.01 sec)

由于指定了被驱动表t2的n2列不允许为NULL，所以上面的t1和t2表的左（外）连接查询和内连接查询是一样一样的。当然，我们也可以不用显式的指定被驱动表的某个列IS NOT NULL，只要隐含的有这个意思就行了，比方说这样：

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+
1 row in set (0.00 sec)

在这个例子中，我们在WHERE子句中指定了被驱动表t2的m2列等于2，也就相当于间接的指定了m2列不为NULL值，所以上面的这个左（外）连接查询其实和下面这个内连接查询是等价的：

mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+
1 row in set (0.00 sec)

我们把在外连接查询中，指定的WHERE子句中包含被驱动表中的列不为NULL值的条件称之为空值拒绝（英文名：reject-NULL）。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

子查询优化

子查询语法

在一个查询语句里的某个位置也可以有另一个查询语句，这个出现在某个查询语句的某个位置中的查询就被称为子查询（我们也可以称它为宝宝查询），那个充当“妈妈”角色的查询也被称之为外层查询。不像人们怀孕时宝宝们都只在肚子里，子查询可以在一个外层查询的各种位置出现，比如：

SELECT子句中： SELECT (SELECT m1 FROM t1 LIMIT 1);

FROM子句中： SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;

这个例子中的子查询是：(SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2)，很特别的地方是它出现在了FROM子句中。
FROM子句里边儿不是存放我们要查询的表的名称么，这里放进来一个子查询是个什么鬼？
其实这里我们可以把子查询的查询结果当作是一个表，子查询后边的AS t表明这个子查询的结果就相当于一个名称为t的表，
这个名叫t的表的列就是子查询结果中的列，比如例子中表t就有两个列：m列和n列。
这个放在FROM子句中的子查询本质上相当于一个表，但又和我们平常使用的表有点儿不一样，
设计MySQL的大佬把这种由子查询结果集组成的表称之为派生表。

WHERE或ON子句中： SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
ORDER BY子句中：虽然语法支持，但没什么子意义，不介绍这种情况了。
GROUP BY子句中：同上

按返回的结果集区分子查询

标量子查询

那些只返回一个单一值的子查询称之为标量子查询，比如这样：
SELECT (SELECT m1 FROM t1 LIMIT 1);

行子查询

顾名思义，就是返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。比如这样：
SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

列子查询

列子查询自然就是查询出一个列的数据喽，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。比如这样：
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);

表子查询

顾名思义，就是子查询的结果既包含很多条记录，又包含很多个列，比如这样：
SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);

按与外层查询关系来区分子查询

不相关子查询

如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。

子查询在 MySQL 中是怎么执行的

物化表

对于不相关的IN子查询，比如这样：

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

将子查询结果集中的记录保存到临时表的过程称之为物化（英文名：Materialize）。为了方便起见，我们就把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

半连接（英文名：semi-join）

将s1表和s2表进行半连接的意思就是：对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录。

小贴士：semi-join只是在MySQL内部采用的一种执行子查询的方式，
MySQL并没有提供面向用户的semi-join语法，所以我们不需要，也不能尝试把上面这个语句放到黑框框里运行，
我只是想说明一下上面的子查询在MySQL内部会被转换为类似上面语句的半连接

优化策略

设计 MySQL 的大叔将 IN 子程序进行了很多优化。如果 IN 子查询符合转换为半连接的条件，查询优化器会优先把该子查询转换为半连接，然后再考虑下面5种半连接查询的策略中哪个成本最低，最后选择成本最低的执行策略来执行子查询

Table pullout （子查询中的表上拉）
DuplicateWeedout execution strategy （重复值消除）
LooseScan execution strategy （松散索引扫描）
Semi-join Materialization execution strategy
FirstMatch execution strategy （首次匹配）