mysql中基于规则的优化

大家好。我们在平时开发的过程中可能会写一些执行起来十分耗费性能的语句。当MySQL遇到这种sql时会依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程被称作查询重写，今天我们就来聊一下mysql在查询重写时遵循的一些比较重要的重写规则。

一、条件简化

我们编写的查询语句的搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL 的查询优化器会简化这些表达式。下面我们来谈一下mysql的几种条件简化。
1. 移除不必要的括号
有时候表达式里有许多无用的括号，优化器会把那些用不到的括号给干掉，比如：

#优化前
((a = 5 AND b = c) OR ((a > c) AND (c < 5)))
#优化后 
(a = 5 and b = c) OR (a > c AND c < 5)

2. 常量传递
有时候某个表达式是某个列和某个常量做等值匹配，当这个表达式和其他涉及这个列的表达式使用AND连接起来时，可以将其他表达式中的该列的值替换为那个常量。比如这样：

#简化前
a = 5
a = 5 AND b > a
#简化后
a = 5 AND b > 5

3. 移出没用的条件
对于一些明显永远为TRUE或者FALSE的表达式，优化器会移除掉它们，比如：

#简化前
(a < 1 and b = b) OR (a = 6 OR 5 != 5)
#简化后
(a < 1 and TRUE) OR (a = 6 OR FALSE) 
a < 1 OR a = 6

4. 表达式计算
在查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出来，比如：

#简化前
a = 5 + 1
#简化后 
a = 6

注意：如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，例如：ABS(a) > 5 或者：-a < -8 优化器是不会尝试对这些表达式进行化简的。

5. HAVING子句和WHERE子句的合并
如果查询语句中没有出现诸如SUM、MAX等等的聚集函数以及GROUP BY子句，优化器就把HAVING子句和WHERE子句合并起来。

6. 常量表检测
MySQL会认为下边这两种查询运行的特别快：

查询的表中一条记录没有，或者只有一条记录。

注意：这里说的表中的记录数是根据统计数据来确定的。而InnoDB的统计数据数据不准确，所以这一条不能用于使用InnoDB作为存储引擎的表，只能适用于使用Memory或者MyISAM存储引擎的表。

使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。

MySQL把通过这两种方式查询的表称之为常量表（英文名：constant tables ）。优化器在分析一个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本，比如：

SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2 WHERE table1.primary_key = 1;

很明显，这个查询可以使用主键和常量值的等值匹配来查询table1表，也就是在这个查询中table1表相当于常量表，在分析对table2表的查询成本之前，就会执行对table1表的查询，并把查询中涉及table1表的条件都替换掉，也就是上边的语句会被转换成这样：

SELECT table1表记录的各个字段的常量值, table2.* FROM table1 INNER JOIN table2 ON table1表column1列的常量值 = table2.column2

二、外连接消除

我们知道，内连接的驱动表和被驱动表的位置可以相互转换，而左（外）连接和右（外）连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。那么下面我们依然以t1和t2表为例，来讲一下mysql的外连接消除。

在这里插入图片描述

外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃。例如：
在这里插入图片描述

注意：右（外）连接和左（外）连接其实只在驱动表的选取方式上是不同的，其余方面都是一样的，所以优化器会首先把右（外）连接查询转换成左（外）连接查询。
我们知道凡是不符合WHERE子句中条件的记录都不会参与连接。只要我们在搜索条件中指定关于被驱动表相关列的值不为NULL，那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了，也就是说：在这种情况下：外连接和内连接也就没有什么区别了。比如：
在这里插入图片描述

由于指定了被驱动表t2的n2列不允许为NULL，所以上边的t1和t2表的左（外）连接查询和内连接查询是一样的。

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

三、子查询优化

上篇文章我们讲解了子查询的语法和执行过程。今天我们来聊一下mysql中子查询的优化。

1. IN子查询优化

我们知道in子查询在执行时，会将子查询结果集中的记录保存到临时表中。这些子查询建立临时表是有成本的，所以我们思考一下这个问题：能不能不进行物化操作直接把子查询转换为连接呢？

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

上述sql中，对于s1表中的某条记录，如果我们能在s2表中找到一条或多条记录，这些记录的common_field的值等于 s1 表记录的 key1 列的值，那么该条s1表的记录就会被加入到最终的结果集。这个过程其实和把s1和s2两个表连接起来的效果很像：

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key1 = s2.common_field WHERE s2.key3 = 'a';

只不过我们不能确定在s2表中有多少条记录满足s1.key1 = s2.common_field这个条件，不过我们可以分三种情况讨论：

情况一：对于s1表的某条记录来说，s2表中没有任何记录满足s1.key1 = s2.common_field 这个条件，那么该记录自然也不会加入到最后的结果集。

情况二：对于s1表的某条记录来说，s2表中有且只有一条记录满足s1.key1 = s2.common_field这个条件，那么该记录会被加入最终的结果集。

情况三：对于s1表的某条记录来说，s2表中至少有2条记录满足s1.key1 = s2.common_field 这个条件，那么该记录会被多次加入最终的结果集。

对于s1 表的某条记录来说，由于我们只关心s2表中是否存在记录满足s1.key1 = s2.common_field这个条件，而不关心具体有多少条记录与之匹配，又因为有情况三的存在，我们上边所说的IN子查询和两表连接之间并不完全等价。

但是将子查询转换为连接又真的可以充分发挥优化器的作用，所以MySQL提出了一个新概念 — 半连接。将 s1 表和 s2 表进行半连接的意思就是：

对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录。

这里我们假设MySQL内部是这么改写上边的子查询的：

SELECT s1.* FROM s1 SEMI JOIN s2 ON s1.key1 = s2.common_field WHERE key3 = 'a';

注意：semi-join只是在MySQL内部采用的一种执行子查询的方式，MySQL并没有提供面向用户的semi-join语法。

概念是有了，那么怎么实现这种所谓的半连接呢？MySQL提供了下面这几种办法。

1. Table pullout （子查询中的表上拉）

当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中，比如

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

由于key2 列是 s2 表的唯一二级索引列，所以我们可以直接把s2表上拉到外层查询的FROM子句中，并且把子查询中的搜索条件合并到外层查询的搜索条件中，上拉之后的查询就是这样的：

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = 'a';

2. DuplicateWeedout execution strategy（重复值消除）

对于这个查询来说：

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

转换为半连接查询后，s1表中的某条记录可能在s2表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，我们可以建立一个临时表，比方说这个临时表长这样：

CREATE TABLE tmp ( 
     id PRIMARY KEY 
 );

这样在执行连接查询的过程中，每当某条s1表中的记录要加入结果集时，就首先把这条记录的id值加入到这个临时表里，如果添加成功，说明之前这条s1表中的记录并没有加入最终的结果集，现在把该记录添加到最终的结果集；如果添加失败，说明这条之前这条s1表中的记录已经加入过最终的结果集，这里直接把它丢弃就好了，这种使用临时表消除semi-join 结果集中的重复值的方式称之为DuplicateWeedout 。

3. LooseScan execution strategy （松散索引扫描）

SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > ‘a’ AND key1 < ‘b’);
这条sql在子查询中，对于s2表的访问可以使用到key1列的索引，而恰好子查询的查询列表处就是key1列，这样在将该查询转换为半连接查询后，如果将s2作为驱动表执行查询的话，那么执行过程就是这样：

在这里插入图片描述

如图所示，在s2表的idx_key1 索引中，值为 ‘aa’ 的二级索引记录一共有3条，那么只需要取第一条的值到s1表中查找s1.key3 = ‘aa’ 的记录，如果能在 s1 表中找到对应的记录，那么就把对应的记录加入到结果集。依此类推，其他值相同的二级索引记录，也只需要取第一条记录的值到s1表中找匹配的记录，这种虽然是扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为松散索引扫描。

4. Semi-join Materialization execution strategy

我们之前介绍的先把外层查询的IN子句中的不相关子查询进行物化，然后再进行外层查询的表和物化表的连接本质上也算是一种semi-join ，只不过由于物化表中没有重复的记录，所以可以直接将子查询转为连接查询。

5. FirstMatch execution strategy （首次匹配）

FirstMatch是一种最原始的半连接执行方式，先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉；然后再开始取下一条外层查询中的记录，重复上边这个过程。对于某些使用IN语句的相关子查询，比方这个查询：

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

它也可以很方便的转为半连接，转换后的语句类似这样：

SELECT s1.* FROM s1 SEMI JOIN s2 ON s1.key1 = s2.common_field AND s1.key3 = s2.key3;

然后就可以使用我们上边介绍过的DuplicateWeedout 、 LooseScan 、 FirstMatch 等半连接执行策略来执行查询，如果子查询的查询列表处只有主键或者唯一二级索引列，还可以直接使用table pullout 的策略来执行查询，但是需要大家注意的是，由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询。

下面我们聊一下半连接的适用条件：

该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的WHERE或者ON子句中出现。
外层查询也可以有其他的搜索条件，只不过和IN子查询的搜索条件必须使用AND连接起来。
该子查询必须是一个单一的查询，不能是由若干查询由UNION连接起来的形式。
该子查询不能包含GROUP BY 或者 HAVING 语句或者聚集函数。

下面我们聊一下半连接的不适用情况：

外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来的情况。

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

使用NOT IN 而不是 IN 的情况。

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

在SELECT 子句中的IN子查询的情况。

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

子查询中包含GROUP BY 、 HAVING 或者聚集函数的情况。

 SELECT * FROM s1 WHERE key2 IN (SELECT COUNT(*) FROM s2 GROUP BY key1);

子查询中包含UNION的情况。

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

MySQL 仍然留了两手绝活来优化不能转为 semi-join 查询的子查询，那就是：

对于不相关子查询来说，可以尝试把它们物化之后再参与查询。

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

这条sql先将子查询物化，然后再判断key1是否在物化表的结果集中可以加快查询执行的速度。

注意：这里将子查询物化之后不能转为和外层查询的表的连接，只能是先扫描s1表，然后对s1表的某条记录来说，判断该记录的key1值在不在物化表中。

不管子查询是相关的还是不相关的，都可以把IN子查询尝试转为EXISTS子查询。

其实对于任意一个IN子查询来说，都可以被转为EXISTS子查询，通用的例子如下：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

可以被转换为：

EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr=inner_expr)

当然这个过程中有一些特殊情况，比如在outer_expr 或者 inner_expr 值为 NULL 的情况下就比较特殊。因为有NULL 值作为操作数的表达式结果往往是NULL ，比方说：

而EXISTS 子查询的结果肯定是 TRUE 或者 FASLE ：

但是幸运的是，我们大部分使用IN子查询的场景是把它放在WHERE或者ON子句中，而WHERE或者ON子句是不区分NULL 和 FALSE 的，比方说：

所以只要我们的IN子查询是放在WHERE或者ON子句中的，那么IN -> EXISTS 的转换就是没问题的。

2. ANY/ALL子查询优化

如果ANY/ALL子查询是不相关子查询的话，它们在很多场合都能转换成我们熟悉的方式去执行，比方说：

原始表达式	转换为
< ANY (SELECT inner_expr …)	< (SELECT MAX(inner_expr) …)
> ANY (SELECT inner_expr …)	> (SELECT MIN(inner_expr) …)
< ALL (SELECT inner_expr …)	< (SELECT MIN(inner_expr) …)
> ALL (SELECT inner_expr …)	> (SELECT MAX(inner_expr) …)

3. [NOT] EXISTS子查询的执行

如果[NOT] EXISTS 子查询是不相关子查询，可以先执行子查询，得出该 [NOT] EXISTS 子查询的结果是 TRUE 还是FALSE，并重写原先的查询语句，比如：

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE key1 = 'a') OR key2 > 100;

因为这个语句里的子查询是不相关子查询，所以优化器会首先执行该子查询，假设该EXISTS子查询的结果为TRUE ，那么接着优化器会重写查询为：

SELECT * FROM s1 WHERE TRUE OR key2 > 100;

进一步简化后就变成了：

SELECT * FROM s1 WHERE TRUE;

对于相关的[NOT] EXISTS 子查询来说，比如：

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.common_field);

这个查询只能正常的子查询执行方式来执行。不过如果[NOT] EXISTS 子查询中可以使用索引的话，那查询速度也会加快不少，比如：

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.key1);

上边这个EXISTS 子查询中可以使用 idx_key1 来加快查询速度。

4. 对于派生表的优化

我们前边说过把子查询放在外层查询的FROM子句后，那么这个子查询的结果相当于一个派生表，比如下边这个查询：

SELECT * FROM  (SELECT id AS d_id,  key3 AS d_key3 FROM s2 WHERE key1 = 'a') AS derived_s1 WHERE d_key3 = 'a';

子查询( SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = ‘a’) 的结果就相当于一个派生表，这个表的名称是derived_s1 ，该表有两个列，分别是 d_id 和 d_key3 。
对于含有派生表的查询，MySQL提供了两种执行策略：

派生表物化。

我们可以将派生表的结果集写到一个内部的临时表中，然后就把这个物化表当作普通表一样参与查询。在对派生表进行物化时，MySQL使用了一种称为延迟物化的策略，也就是在查询中真正使用到派生表时才去尝试物化派生表，而不是还没开始执行查询呢就把派生表物化掉。比如：

SELECT * FROM (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1 INNER JOIN s2 ON derived_s1.key1 = s2.key1 WHERE s2.key2 = 1;

如果采用物化派生表的方式来执行这个查询的话，那么执行时首先会到s1表中找出满足s1.key2 = 1 的记录，如果压根儿找不到，说明参与连接的s1表记录就是空的，所以整个查询的结果集就是空的，也就没有必要去物化查询中的派生表了。

将派生表和外层的表合并，也就是将查询重写为没有派生表的形式。

我们来看这个查询：

SELECT * FROM (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1;

这个查询本质上就是想查看s1表中满足key1 = ‘a’ 条件的的全部记录，所以和下边这个语句是等价的：

SELECT * FROM s1 WHERE key1 = 'a';

对于一些稍微复杂的包含派生表的语句，比如我们上边提到的那个：

SELECT * FROM (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1 INNER JOIN s2 ON derived_s1.key1 = s2.key1 WHERE s2.key2 = 1;

我们可以将派生表与外层查询的表合并，然后将派生表中的搜索条件放到外层查询的搜索条件中，就像这样：

SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.key1 = 'a' AND s2.key2 = 1;

这样通过将外层查询和派生表合并的方式成功的消除了派生表，也就意味着我们没必要再付出创建和访问临时表的成本了。

可是并不是所有带有派生表的查询都能被成功的和外层查询合并，当派生表中有这些语句就不可以和外层查询合并：

聚集函数，比如MAX()、MIN()、SUM()
DISTINCT
GROUP BY
HAVING
LIMIT
UNION 或者 UNION ALL
派生表对应的子查询的SELECT 子句中含有另一个子查询

所以MySQL 在执行带有派生表的时候，优先尝试把派生表和外层查询合并掉，如果不行的话，再把派生表物化掉执行查询。

好了，到这里我们就讲完了，大家有什么想法欢迎留言讨论。也希望大家能给作者点个关注，谢谢大家！最后依旧是请各位老板有钱的捧个人场，没钱的也捧个人场，谢谢各位老板！