优化器内部优化规则

在前面介绍Explain使用的文章《Explain使用详解》中，简单的提到了show warnings的使用，通过该语句可以查看一条SQL经过优化器处理后，真正要执行的SQL是什么样子。

这篇文章就介绍一些MySQL优化器内部优化的规则，对于开发人员来说，了解MySQL内部的优化规则可以让优化工作在书写SQL时就完成，不需要优化器再帮忙优化，这样也可以提升SQL执行的效率。

对于一个SQL的执行，MySQL在执行的过程中会有很多的优化措施，比如索引下推、回表中的MRR、索引合并等等。

这里主要介绍三大类优化器内部的优化规则：

一、条件简化

SQL语句本身也是一门编程语言，与其他编程语言一样，对于简单的一些表达式，可以直接进行合并或优化，比如Java中的字符串String name = "li"+"zhi"，编译器可以直接进行优化合并。SQL同样具有类似的功能，通常称为条件简化。

查询语句的搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL的查询优化器会为我们简化这些表达式。

1.1 移除多余括号

有时候表达式里有许多无用的括号，比如这样：

((a = 5 AND b = 10) OR ((a > 3) AND (c < 15)))

优化器会把那些用不到的括号给干掉，就是这样：

(a = 5 AND b = 10) OR (a > 3 AND c < 15)

1.2 常量值传递

某个表达式是某个列和某个常量做等值匹配，比如这样：

a = 5

当这个表达式和其他涉及列a的表达式使用AND连接起来时，可以将其他表达式中的a的值替换为5，比如这样：

a = 5 AND b > a

就可以被转换为：

a = 5 AND b > 5

**等值传递(equality_propagation）**有时候多个列之间存在等值匹配的关系，比如这样：

a = b and b = c and c = 5

这个表达式可以被简化为：

a = 5 and b = 5 and c = 5

1.3 移除冗余条件

对于一些明显永远为TRUE或者FALSE的表达式，优化器会移除掉它们，比如这个表达式：

(a < 1 and b = b) OR (a = 6 OR 5 != 5)

很明显，b = b这个表达式永远为TRUE，5 != 5这个表达式永远为FALSE，所以简化后的表达式就是这样的：

(a < 1 and TRUE) OR (a = 6 OR FALSE)

可以继续被简化为

a < 1 OR a = 6

1.4 表达式计算合并

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

a = 4 + 5

因为4 + 5这个表达式只包含常量，所以就会被化简成：

a = 6

但是这里需要注意的是，如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，就像这样：

ABS(a) > 5

或者：

-a < -8

优化器是不会尝试对这些表达式进行化简的。我们前边说过只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引，所以如果可以的话，最好让索引列以单独的形式出现在表达式中。

二、外连接消除

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

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

2.1 内外连接差异性

内连接：

外连接：

2.2 外连接消除

如果不符合WHERE子句中条件的记录都不会参与连接。

只要在搜索条件中指定被驱动表相关列的值不为NULL，那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了，也就是说：在这种情况下：外连接和内连接也就没有什么区别了！

以上面的SQL为例：

被驱动表中指定name属性不为空，查询结果就与内连接是一样的了。

也可以不用显式的指定被驱动表的某个列IS NOT NULL，只要隐含的有这个意思就行了，比方说这样：

SELECT * FROM stu1 LEFT JOIN stu2 ON stu1.name = stu2.name WHERE stu2.name ='lizhi';

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

SELECT * FROM stu1 INNER JOIN stu2 ON stu1.name = stu2.name WHERE stu2.name ='lizhi';

三、子查询内部优化规则

子查询在SQL中出现的概率是非常高的，优化器内部也对子查询进行了详细的分类和优化。

3.1 子查询语法

在一个查询语句A里的某个位置也可以有另一个查询语句B，这个出现在A语句的某个位置中的查询B就被称为子查询，A也被称之为外层查询。子查询可以在一个外层查询的各种位置出现。

3.1.1 SELECT子句中

比如：

SELECT (SELECT name FROM stu1 LIMIT 1)

其中的SELECT name FROM stu1 LIMIT 1就是子查询

3.1.2 FROM子句中

比如：

SELECT * FROM (SELECT name,age FROM stu1 WHERE age > 22) stu3

其中的SELECT name,age FROM stu1 WHERE age > 22就是子查询，这里可以把子查询的查询结果当作是一个表，子查询后边的stu3表明这个子查询的结果就相当于一个名称为stu3的表，MySQL把这种由子查询结果集组成的表称之为派生表。

3.1.3 WHERE或ON子句中

比如:

SELECT * FROM stu1 WHERE name IN (SELECT name FROM stu2);

这个查询表明我们想要将(SELECT name FROM stu2)这个子查询的结果作为外层查询的IN语句参数，整个查询语句的意思就是我们想找stu1表中的某些记录，这些记录的name列的值能在stu2表的name列找到匹配的值。

3.1.4 ORDER BY子句、GROUP BY子句中

虽然语法支持，但没啥意义

3.2 按返回结果区分子查询

子查询本身也算是一个查询，所以可以按照它们返回的不同结果集类型而把这些子查询分为不同的类型。

3.2.1 标量子查询

只返回一个单一值的子查询称之为标量子查询，比如：

SELECT (SELECT name FROM stu1 LIMIT 1);

3.2.2 行子查询

返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。比如这样：

SELECT * FROM stu1 WHERE (name, age) = (SELECT name, age FROM stu2 LIMIT 1);

其中的(SELECT name, age FROM stu2 LIMIT 1)就是一个行子查询

3.2.3 列子查询

列子查询自然就是查询出一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。比如这样：

SELECT * FROM stu1 WHERE name IN (SELECT name FROM stu2);

(SELECT name FROM stu2)就是一个列的子查询

3.2.4 表子查询

顾名思义，就是子查询的结果既包含很多条记录，又包含很多个列，比如这样：

SELECT * FROM stu1 WHERE (name,age) IN (SELECT name,age FROM stu2);

其中(SELECT name,age FROM stu2)就是一个表子查询

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

3.3.1 不相关子查询

如果子查询可以单独运行出结果，而不依赖于外层查询的值，就可以把这个子查询称之为不相关子查询。前边介绍的那些子查询全部都可以看作不相关子查询。

3.3.2 相关子查询

如果子查询的执行需要依赖于外层查询的值，就可以把这个子查询称之为相关子查询。比如：

SELECT * FROM stu1 WHERE name IN (SELECT name FROM stu2 WHERE stu1.age = stu2.age);

子查询(SELECT name FROM stu2 WHERE stu1.age = stu2.age)中有一个查询条件是stu1.age = stu2.age,stu1.age是外层查询的列，也就是子查询的执行需要依赖外层查询的值，所以这个子查询是一个相关子查询。

对于列子查询和表子查询来说，它们的结果集中包含很多条记录，这些记录相当于是一个集合，所以就不能单纯的和另外一个操作数使用操作符来组成布尔表达式了，MySQL通过下面的语法来支持某个操作数和一个集合组成一个布尔表达式。

IN或者NOT IN

具体的语法形式如下：

操作数 [NOT] IN (子查询)

这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成的集合中。

ANY/SOME（ANY和SOME是同义词）

具体的语法形式如下：

操作数 比较符 ANY/SOME(子查询)

这个布尔表达式的意思是只要子查询结果集中存在某个值和给定的操作数做比较操作，比较结果为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。

ALL

具体的语法形式如下：

操作数 比较操作 ALL(子查询)

这个布尔表达式的意思是子查询结果集中所有的值和给定的操作数做比较操作比较结果为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。

EXISTS子查询

有的时候我们仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是个啥，可以使用把EXISTS或者NOT EXISTS放在子查询语句前边，就像这样：

SELECT * FROM stu1 WHERE EXISTS (SELECT 1 FROM stu2);

对于子查询(SELECT 1 FROM stu2)来说，我们并不关心这个子查询最后到底查询出的结果是什么，所以查询列表里填***、某个列名，或者其他啥东西**都无所谓，我们真正关心的是子查询的结果集中是否存在记录。也就是说只要(SELECT 1 FROM stu2)这个查询中有记录，那么整个EXISTS表达式的结果就为TRUE。

3.4 子查询的执行方式

子查询的优化是根据子查询的类型有所不同，具体如下：

3.4.1 标量子查询、行子查询的执行方式

3.4.1.1 不相关子查询

对于不相关的标量子查询或行子查询，它们的执行方式很简单，比如：

SELECT name FROM stu1 WHERE name = (SELECT name FROM stu2 WHERE age > 25 LIMIT 1);

先单独执行(SELECT name FROM stu2 WHERE age > 25 LIMIT 1)子查询，然后将子查询得到的结果作为查询的参数再执行外层查询SELECT name FROM stu1 WHERE name = ? 。

也就是说，对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，就当作两个单表查询就好了。

3.4.1.2 相关子查询

对于相关的标量子查询或者行子查询，它的执行方式就有所不同了，比如下面的SQL:

SELECT * FROM stu1 WHERE name = (SELECT name FROM stu2 WHERE stu1.age = stu2.age LIMIT 1);

它的执行方式是这样的：

先从外层查询中获取一条记录，也就是先从stu1表中获取一条记录，然后从该记录中取出子查询需要关联的字段值，也就是stu1中age的属性值，将该值作为子查询的参数执行子查询，根据子查询的结果判断外层查询的WHERE条件是否成立，如果成立就把完成查询的记录加入结果集，否则就抛弃。

3.4.2 IN子查询内部优化

与普通的标量子查询或列子查询不同，IN子查询的结果集可能是很庞大的，如果还采用标量子查询或列子查询的方式来执行，效率会受到很大的影响。
比如下面的SQL:

SELECT * FROM stu1 WHERE name IN (SELECT name FROM stu2 WHERE stu2.age > 20);

对于不相关的IN子查询，如果子查询结果集中的记录数很少，那么把子查询和外层查询当作两个单独的单表查询效率是很高的，但如果子查询的结果集中记录数太多，就可能会导致下面的问题：
1、结果集太多，可能内存都放不下
2、子查询结果集的记录太多，就意味着IN子句里面的参数特别多，这就导致无法有效使用索引，只能对外层查询进行全表扫描。
在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。

所以对于IN子查询，优化器内部实现了四种优化策略：

3.4.2.1 物化表

为了改进带有IN查询子句的效率，MySQL不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表。大概过程如下：

1、临时表的列就是IN查询子句中的列

2、写入临时表的记录会被去重，然后为表中记录的所有列建立主键或唯一索引

一般情况下子查询结果集不会太大，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。

如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

MySQL把这个将子查询结果集中的记录保存到临时表的过程称之为物化，存储子查询结果集的临时表称之为物化表。

因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

3.4.2.2 物化表转连接

对于下面的这个SQL:

SELECT * FROM stu1 WHERE name IN (SELECT name FROM stu2 WHERE stu2.age > 20);

当我们把子查询进行物化之后，假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_name，那么上前的查询就相当于表stu1和表materialized_table进行内连接：

SELECT * FROM stu1 INNER JOIN materialized_table on name = m_name

转换成内连接之后，查询优化器可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询。

3.4.2.3 子查询转换为semi-join

将子查询进行物化之后再执行查询都会有建立临时表的成本，能不能不进行物化操作直接把子查询转换为连接呢？
再次分析这个SQL:

SELECT * FROM stu1 WHERE name IN (SELECT name FROM stu2 WHERE stu2.age > 20);

对于上面的SQL可以理解为对于stu1表中记录，可以从stu2表中找出一条或多条记录(stu2.age>20),stu2记录中的name属性值等于stu1记录中的name值，那么该条sut1表中记录就会添加到结果集中。

所以这个过程与将stu1和stu2进行内连接很像：

SELECT stu1.* FROM stu1 INNER JOIN stu2 ON stu1.name = stu2.name WHERE stu2.age > 20;

只不过不能保证对于stu1表中记录，stu2表(准确的说是执行完WHERE stu2.age > 20之后的结果集)中有多少条记录满足stu1.name =stu2.name，可就是可能存在stu1中一条记录对应stu2中多条记录的情况，所以分为三种情况讨论：

1、对于stu1表的某条记录，stu2表中没有任何记录满足stu1.name = stu2.name，那么stu1表中的该记录自然也就不能加入到最后的结果集中

2、对于stu1表中的某条记录，stu2表中只有一条记录满足stu1.name=stu2.name，那么sut1表的该记录会被加入到最终的结果集中

3、对于stu1表中的某条记录，stu2表中有多条记录满足stu1.name=stu2.name，那么stu1表的该记录会被多次加入到最终结果集中。

对于stu1表来说，我们只关心stu2表中是否有记录满足stu1.name=stu2.name，而不关心有多少条记录与之对应，因为有情况三的存在，我们上边所说的IN子查询和两表连接之间并不完全等价。但是将子查询转换为连接又真的可以充分发挥优化器的作用，所以MySQL在这里提出了一个新概念 — 半连接（semi-join）。

将表stu1和表stu2进行半连接的意思是：对于表stu1的记录，我们只关心stu2表中是否有与之匹配的记录，而不关心有多少条记录与之匹配，最终的结果集只保留stu1表中的记录。可以想象MYSQL内部是这么实现的：

SELECT stu1.* FROM stu1 SEMI JOIN stu2 ON stu1.name = stu2.name WHERE stu2.age > 20;

注： semi-join只是在MySQL内部采用的一种执行子查询的方式，MySQL并没有提供面向用户的semi-join语法
对于一些SQL，MySQL会优化SQL的写法，可以通过show warnings来查看实际执行的SQL语句。

对于半连接的具体实现，MySQL有好几种办法，比如Table pullout （子查询中的表上拉）、DuplicateWeedout execution strategy （重复值消除）、LooseScan execution strategy （松散扫描）、Semi-join Materializationa半连接物化、FirstMatch execution strategy （首次匹配）等等。

3.4.2.4 转为EXISTS子查询

并不是所有包含IN子查询的查询语句都可以转换为semi-join，对于不能转换的，MySQL有下面种方法：

1、对于不相关子查询来说，会尝试把它们物化之后再参与查询，比如下面的SQL

SELECT * FROM stu1 WHERE name NOT IN (SELECT name FROM stu2 WHERE stu2.age > 20);

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

2、把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)

经过转换，可以让子查询走上索引。

需要注意的是，如果IN子查询不满足转换为semi-join的条件，又不能转换为物化表或者转换为物化表的成本太大，那么它就会被转换为EXISTS查询。
在MySQL5.5以及之前的版本没有引进semi-join和物化的方式优化子查询时，优化器都会把IN子查询转换为EXISTS子查，随着MySQL的发展，最近的版本中引入了非常多的子查询优化策略，内部的转换工作优化器会自动实现。

3.4.3 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) ...)