深入MYSQL内核查询优化规则

MYSQL内核查询优化规则

MySQL在执行的过程中会 有很多的优化措施，比如索引条件下推，回表中的MRR、索引合并、ROR等等。但是在执行之前， MySQL会依据一些规则，竭尽全力的把我们的SQL语句进行转换，以期可以达到成某种可以更高效执行的形式，这个过程也可以被称作查询重写或者查询优化，很多时候这些优化可以由我们自行完成以减少MySQL的优化时间。下面就介绍具体有哪些优化规则
官方文档地址

一、Where条件化简

我们编写的查询语句的搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，所以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、常量传递（constant_propagation）

表达式中某个列和某个常量做等值匹配，比如：
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

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

4、表达式计算

查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出来，比如这 个：
a = 5 + 1
因为5 + 1这个表达式只包含常量，所以就会被化简成：
a = 6
但是这里需要注意的是，如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，就像这样：
ABS(a) > 5
或者：
-a < -8
优化器是不会尝试对这些表达式进行化简的。因为只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引，所以如果可以的话，最好让索引列以单独的形式出现在表达式中。

5、常量表检测

MySQL觉得下边这种查询运行的特别快：
使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。
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;

table1
 id  name   age   class
 1   张三     18    一班
 2   李四     20    一班
 3   王五     25    二班
 ...   ...         ...	  ...

table2
class      score
一班		100
二班		120
三班		 90

SELECT * FROM table1 INNER JOIN table2
ON table1.class= table2.class
WHERE table1.id= 1;
优化后：
SELECT 1 as id ,  "张三" as name ,  18 as age  ,  "一班" as class,table2.* 
FROM table1 INNER JOIN table2  ON "一班" = table2.class;

二、连接优化

1、外连接消除

内连接的驱动表和被驱动表的位置可以相互转换，而左（外）连接和右 （外）连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。
外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如 果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到 结果集中，对应的被驱动表记录的各个字段使用NULL值填充；而内连接的驱动表的记录 如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃。
凡是不符合WHERE子句中条件的记录都不会参与连接。只要我们在搜索条件中指定关 于被驱动表相关列的值不为NULL，那么外连接中在被驱动表中找不到符合ON子句条件的 驱动表记录也就被排除出最后的结果集了，也就是说：在这种情况下：外连接和内连接 也就没有什么区别了！
比如：
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.tid WHERE t2.c1 IS NOT NULL;
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.tid WHERE t2.c1 = ‘1’;
优化后：
SELECT * FROM t1 inner JOIN t2 ON t1.id = t2.tid WHERE t2.c1 IS NOT NULL;
SELECT * FROM t1 inner JOIN t2 ON t1.id = t2.tid WHERE t2.c1 = ‘1’;
在外连接查询中，指定的WHERE子句中包含被驱动表中的列不为NULL值的条件 称之为空值拒绝（英文名：reject-NULL）。在被驱动表的WHERE子句符合空值拒绝的条 件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评 估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

三、子查询优化

1、按出现位置分

select子句：出现在select

SELECT (SELECT c1 FROM t1 LIMIT 1);
select 子句只支持单值单列，如果有多个会在执行时报错

from子句：出现在from

SELECT m, n FROM (SELECT c1 + 1 AS m, c2 AS n FROM t2 WHERE c2 > 2) AS t;
这里就是把子查询的查询结果当作是一个表，子查询后边的AS t表明这个子查询的结果就 相当于一个名称为t的表，这个名叫t的表的列就是子查询结果中的列，比如例子中表t就 有两个列：m列和n列。这个放在FROM子句中的子查询本质上相当于一个表，但又和我们 平常使用的表有点儿不一样，MySQL把这种由子查询结果集组成的表称之为派生表。

where/on子句：

把子查询放在外层查询的WHERE子句或者ON子句中
SELECT * FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
ORDER BY子句、GROUP BY子句中 语法支持，但没啥意义。

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

标量子查询

SELECT (SELECT m1 FROM e1 LIMIT 1);
SELECT * FROM e1 WHERE m1 = (SELECT MIN(m2) FROM e2);
SELECT * FROM e1 WHERE m1 < (SELECT MIN(m2) FROM e2);
子查询都返回一个单一的值，也就是一个标量。这些标量子查询可 以作为一个单一值或者表达式的一部分出现在查询语句的各个地方。

行子查询

SELECT * FROM e1 WHERE (m1, n1) = (SELECT m2, n2 FROM e2 LIMIT 1);
返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列 就成了标量子查询了）。

列子查询

SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);
是查询出一个列的数据，不过这个列的数据需要包含多条记录（只包含 一条记录就成了标量子查询了）。

表子查询

SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);
子查询的结果既包含很多条记录，又包含很多个列

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

不相关子查询

子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);

相关子查询

子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查 询。
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2 WHERE e1.n1 = e2.n2);

[NOT] IN/ANY/SOME/ALL子查询

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

IN或者NOT IN

具体的语法形式如下：
操作数 [NOT] IN (子查询)
布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成的集合中
SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);

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

具体的语法形式如下：
操作数 比较符 ANY/SOME(子查询)
布尔表达式的意思是只要子查询结果集中存在某个值和给定的操作数做比较操作， 比较结果为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。
SELECT * FROM e1 WHERE m1 > ANY(SELECT m2 FROM e2);
优化：
SELECT * FROM e1 WHERE m1 > (SELECT MIN(m2) FROM e2);

ALL

具体的语法形式如下： 操作数 比较操作 ALL(子查询)
布尔表达式的意思是子查询结果集中所有的值和给定的操作数做比较操作比较结果 为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。
SELECT * FROM e1 WHERE m1 > ALL(SELECT m2 FROM e2);
优化：
SELECT * FROM e1 WHERE m1 > (SELECT MAX(m2) FROM e2);

EXISTS子查询

有的时候我们仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是个 啥，可以使用把EXISTS或者NOT EXISTS放在子查询语句前边
SELECT * FROM e1 WHERE EXISTS (SELECT 1 FROM e2);
子查询(SELECT 1 FROM e2)来说，并不关心这个子查询最后到底查询出的结果是什么，所以查询列表里填*、某个列名，或者其他啥东西都无所谓，我们真正关心的是子查询的结果集中是否存在记录。也就是说只要(SELECT 1 FROM e2)这个查询中有记 录，那么整个EXISTS表达式的结果就为TRUE。

4、优化

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

a.不相关标量子查询或者行子查询，MySQL会分别独 立的执行外层查询和子查询，就当作两个单表查询就好了

SELECT * FROM s1 WHERE order_note = (SELECT order_note FROM s2 WHERE key3 = 'a' LIMIT 1); 
它的执行方式：
先单独执行(SELECT order_note FROM s2 WHERE key3 = 'a' LIMIT 1)这个子查询。
然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询
SELECT * FROM s1 WHERE order_note= ...。

b.相关的标量子查询或者行子查询，
MYSQL会先从外层查询中获取一条记录，
然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，然后执行子查询。
最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把 外层查询的那条记录加入到结果集，否则就丢弃。
再次执行第一步，获取第二条外层查询中的记录，依次类推。

SELECT * FROM s1 WHERE 
order_note = (SELECT order_note FROM s2 WHERE s1.order_no= s2.order_no LIMIT 1);
执行方式：
先从s1表中获取一条记录
再从s1表中获取的那条记录中找出s1.order_no列的值，然后执行子查询。
根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，
如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。 
再次执行第一步，获取第二条外层查询中的记录，依次类推。

MySQL对IN子查询的优化

物化表:

对于不相关的IN子查询
1、结果集太多，可能内存中都放不下。
2、对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多， 这就导致：无法有效的使用索引，只能对外层查询进行全表扫描。
MySQL的改进是不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写 入一个临时表里。写入临时表的过程是这样的：
1、该临时表的列就是子查询结果集中的列。
2、写入临时表的记录会被去重，临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引。 一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。
如果子查询的结果集非常大，超过了系统变量tmp_table_size或者 max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录， 索引类型也对应转变为B+树索引。
MySQL把这个将子查询结果集中的记录保存到临时表的过程称之为物化（英文名： Materialize）。为了方便起见，我们就把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的 有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

物化表转连接：

子查询进行物化之后，假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_val，那么这个查询就相当于外层表和子查询物化表materialized_table进行内连接：
SELECT 外表 .* FROM 外表 INNER JOIN materialized_table ON 外表.列 = m_val;
比如：
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = ‘a’);
转连接后：
SELECT s1.* FROM s1 INNER JOIN materialized_table ON s1.order_note = materialized_table .m_val;
转链接后，就可以按不同连接顺序计算评估最低成本，从而选择最优的查询计划执行。

将子查询转换为semi-join：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = ‘a’); 对于这个子查询可以理解成：
对于s1表中的某条记录，如果我们能在s2表（准确的说是执行完WHERE s2.order_no= 'a’之后的结果集）中找到一条或多条记录，这些记录的 order_note的值等于s1表记录的order_note列的值，那么该条s1表的记录就会被加入到最终的结果集。
外层表的列条件满足子表中查询得到的列数据条件
如果将s1和s2连接起来：
SELECT s1.* FROM s1 INNER JOIN s2 ON s1.order_note = s2.order_note WHERE s2.order_no= ‘a’;
不能保证对于s1表的某条记录来说，在s2表（准确的说是执行完WHERE s2.order_no= 'a’之后的结果集）中有多少条记录满足s1.order_no = s2.order_no这个 条件，不过我们可以分三种情况讨论：
**情况一：**对于s1表的某条记录来说，s2表中没有任何记录满足s1.order_note = s2.order_note 这个条件，那么该记录自然也不会加入到最后的结果集。
情况二：对于s1表的某条记录来说，s2表中有且只有1条记录满足s1.order_note = s2.order_note 这个条件，那么该记录会被加入最终的结果集。
情况三：对于s1表的某条记录来说，s2表中至少有2条记录满足s1.order_note = s2.order_note 这个条件，那么该记录会被多次加入最终的结果集。
s1表的某条记录来说，由于我们只关心s2表中是否存在记录满足s1.order_no = s2.order_note这个条件，而不关心具体有多少条记录与之匹配，又因为有情况三的存 在，我们上边所说的IN子查询和两表连接之间并不完全等价。但是将子查询转换为连接 又真的可以充分发挥优化器的作用，所以MySQL在这里提出了一个新概念 — 半连接 （英文名：semi-join）。
将s1表和s2表进行半连接的意思就是：对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保 留s1表的记录。
注意：
semi-join只是在MySQL内部采用的一种执行子查询的方式，MySQL并没有提供面 向用户的semi-join语法。
可以通过
show warnings 查询

半连接实现方法

Table pullout （子查询中的表上拉）
当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中，比如假设s2中存在这个一个key2列，列上有唯一性索引：
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’;
为啥当子查询的查询列表处只有主键或者唯一索引列时，就可以直接将子查询转换为连接查询呢？因为主键或者唯一索引列中的数据本身就是不重复的！
DuplicateWeedout execution strategy （重复值消除）

LooseScan execution strategy （松散扫描）
Semi-join Materializationa半连接物化
FirstMatch execution strategy （首次匹配）

不能转为semi-join查询的子查询优化

并不是所有包含IN子查询的查询语句都可以转换为semi-join，对于不能转换 的，MySQL有这几种方法： 1、对于不相关子查询来说，会尝试把它们物化之后再参与查询
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子查询小结

如果IN子查询符合转换为semi-join的条件，查询优化器会优先把该子查询转换为semi- join，然后从前面所说的5种执行半连接的策略（既子查询中的表上拉、重复值消除等等）中选择成本最低的那种执行策略来执行子查询。
如果IN子查询不符合转换为semi-join的条件，那么查询优化器会从下边两种策略中找出
一种成本更低的方式执行子查询： 先将子查询物化之后再执行查询
第二种是将执行IN to EXISTS转换。
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) ...)

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