学习mysql6-规则优化和执行计划

规则优化

规则优化，其实很多的场景下就是mysql对你的sql进行重写，提供查询的效率

条件优化

条件优化的核心思路，就是把不必要的一些内容去除，包括on或者where后面的条件，整体的思路上去除一些无效的符号或者判断条件，使得整个sql比较简洁明了
条件化简
1.移除不必要的括号
2.常量传递（constant_propagation）
3.等值传递（equality_propagation）
4.移除没⽤的条件（trivial_condition_removal）
5.表达式计算（如果表达式中包含某个列是不会计算的）
6.HAVING⼦句和WHERE⼦句的合并
常量表检测
查询的表中⼀条记录没有，或者只有⼀条记录
使⽤主键等值匹配或者唯⼀⼆级索引列等值匹配作为搜索条件来查询某个表

mysql认为以上的两种查询方式查询速度很快，可以忽略。在分析⼀个查询语句时，先⾸先执⾏常量表查 询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本

连接优化

主要针对一些外连接优化，可以思考一下连接优化为啥处理外连接？
把外连接消除，整体的思路把外连接变成内连接，何必如此大费周章。是因为内连接可以由优化器决定，那一张表作为驱动表，另一种则作为被驱动表，更高效的执行sql查询
例SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;
当排除了被外连接表的NOT NULL部门的场景下，也就是null值拒绝的概念
在被驱动表的WHERE⼦句符合空值拒绝的条件后，外连接和内连接可以 相互转换

子查询优化

通过子查询的位置区分

1.跟在select后
2.跟在from后
3.跟在where或者on之后（平时用的最多的应该就是in语句）

返回的结果集区分

1.标量子查询
子查询select m1 … limit1
2.行子查询
子查询select m1，n1 … limit1查询多个列
3.列⼦查询
子查询select m1 …
4.表⼦查询
子查询select m1，n1 …

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

1.相关子查询
2.不相关子查询（说⼦查询的执⾏需要依赖于外层查询的值）

⼦查询在布尔表达式中的使⽤

1.使⽤=、>、<、>=、<=、<>、!=、<=>作为布尔表达式的操作符
2.[NOT] IN/ANY/SOME/ALL⼦查询
3.ALL
4.EXISTS⼦查询

⼦查询语法注意事项

1.子查询要用括号
2.在SELECT字句里必须是标量子查询
3.在想要得到标量⼦查询或者⾏⼦查询，但又不能保证⼦查询的结果集只有⼀条记录时，应该使⽤LIMIT 1语句来限制记录数量
4.对于[NOT] IN/ANY/SOME/ALL⼦查询来说，⼦查询中不允许有LIMIT语句
5.不允许在⼀条语句中增删改某个表的记录时同时还对该表进⾏⼦查询

子查询是怎么执行的

1.对于包含不相关的标量⼦查询或者⾏⼦查询的查询语句来说，MySQL会分别独⽴的执⾏外层查询和⼦查询，就当作两个单表查询就好了。
2.对于相关的标量⼦查询或者⾏⼦查询来说，⽐如下边这个查询：
SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);
执⾏⽅式就是这样的：
先从外层查询中获取⼀条记录，本例中也就是先从s1表中获取⼀条记录。
然后从上⼀步骤中获取的那条记录中找出⼦查询中涉及到的值，本例中就是从s1表中获取的那条记录中找出s1.key3列的值，然后执⾏⼦查询。
最后根据⼦查询的查询结果来检测外层查询WHERE⼦句的条件是否成⽴，如果成⽴，就把外层查询的那条记录加⼊到结果集，否则就丢弃。
再次执⾏第⼀步，获取第⼆条外层查询中的记录，依次类推～ 也就是说对于⼀开始唠叨的两种使⽤标量⼦查询以及⾏⼦查询的场景中，MySQL优化器的执⾏⽅式并没有什么新鲜的。

IN⼦查询优化

当数据量少的时候，和上面的执行方式一致。
当数据量比较大的时候，mysql提出了物化的概念，简单的说也就是把in里面的内容建立临时表，去重的方式存下来。in里面的内容不大的情况下存在memory的存储引擎里，且会为该表建⽴哈希索引，当超出系统设定值时存具体的表，会使⽤基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。
物化表转连接：当in里面的内容存在临时表之后，又产生了一个大胆的想法，查询的结构变成了select * from 表1 where 列1 in (select val from 临时表），也就是说表1里面的列1只要在临时表val列有对应的值，就把表1的内容返回。所以就变成了 select * from 表1 inner join 临时表 on 表1.列1 = val
最后我们可以联想到转为内连接之后，系统就能计算驱动表的成本得到最优查询
将⼦查询转换为semi-join：
如果不做物化的操作，直接把in查询变成内连接，会遇到什么样的问题？或者说什么场景下他是可以直接转化的呢？
我们前面提到了物化的过程，是对某个列做一个去重的过程。那么，如果没有去重之后的内连接，是不是会出现表1返回多条同样的数据呢。所以这就是一种必须要考虑到的情况，为此系统又提出了半连接（英⽂名：semi-join）的概念，也可以理解。表1只关心该列在表2列存不存在一个对应的值，而不关心到底有几条内容是啥。
半连接的实现：
Table pullout ：当⼦查询的查询列表处只有主键或者唯⼀索引列时，可以直接把⼦查询中的表上拉到外层查询的FROM⼦句中
DuplicateWeedout execution strategy （重复值消除）：其实是建立一张临时表，每次存的时候判断有没有insert成功，进而判断是不是重复了
LooseScan execution strategy （松散索引扫描）：扫描索引，但只取值相同的记录的第⼀条去做匹配操作的⽅式称之为松散索 引扫描
Semi-join Materialization execution strategy：我们之前介绍的先把外层查询的IN⼦句中的不相关⼦查询进⾏物化，然后再进⾏外层查询的表和物化表的连接本质上也算是⼀种semi-join，只不过由于物化表中没有重复的记录，所以可以直接将 ⼦查询转为连接查询。
FirstMatch execution strategy （⾸次匹配）：FirstMatch是⼀种最原始的半连接执⾏⽅式，跟我们年少时认为的相关⼦查询的执⾏⽅式是⼀样⼀样的，就是说先取⼀条外层查询的中的记录，然后到⼦查询的表中寻找符合匹配条件的记录，如果 能找到⼀条，则将该外层查询的记录放⼊最终的结果集并且停⽌查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉；然后再开始取下⼀条外层查询中的记录，重复上边这个过程。

1.如果IN⼦查询符合转换为semi-join的条件，查询优化器会优先把该⼦查询转换为semi-join，然后再考虑下边5种执⾏半连接的策略中哪个成本最低： Table pullout DuplicateWeedout LooseScan Materialization FirstMatch 选择成本最低的那种执⾏策略来执⾏⼦查询。
2.如果IN⼦查询不符合转换为semi-join的条件，那么查询优化器会从下边两种策略中找出⼀种成本更低的⽅式执⾏⼦查询： 先将⼦查询物化之后再执⾏查询 执⾏IN to EXISTS转换

ANY/ALL⼦查询优化

一句话带过，能用ANY或者ALL都可以用min和max处理

[NOT] EXISTS⼦查询的执⾏

其实简单的说，就子查询对于不相关子查询，可以执行子查询得到结果false或者true然后再调整sql
对于相关子查询，就得按照最原始的方式执行，查每一个值取子查询走一次判断

派生表优化

1.派生表物化
2.派生表和外层合并

执行计划

Explain 详解（上）

从id开始吧，id是为查询一个表生成的一个id
select_type大致的意思是查询的表方式，解释几个常见的SIMPLE单表查询，PRIMARY有子查询的时候，这就是父表（最左边那个）查询，UNION有union查询的时候除了最左边那个就是这个，UNION RESULT针对union查询时做临时表去重用，SUBQUERY子查询，DEPENDENT SUBQUERY不能转化为半连接的字查询，DERIVED采用物化的派生表，MATERIALIZED多张表的时候，第二张表查询结果物化产生的派生表
partitions 分区多半为null
type：走的索引类型，上面有详细的解释过，不再强调
possible_keys和key一个是系统做选择的时候，可能会走的那些索引，key是实际系统选择的索引
key_len表示系统索引字段的字节大小
ref大概意思就是，如果走索引的条件下的话，与索引对比的是什么类型，const？某个列？函数？
rows这不就是行数吗，走二级索引预计行数
filtered用来估算重复数，比如rows10000，改值10的，扇出数1000

Explain 详解（下）

Extra

Extra 顾名思义，Extra列是⽤来说明⼀些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执⾏给定的查询语句。MySQL提供的额外信息有好⼏⼗个，我们就不⼀个⼀个介绍了（都介绍 了感觉我们的⽂章就跟⽂档差不多了～），所以我们只挑⼀些平时常见的或者⽐较重要的额外信息介绍给⼤家哈。
No tables used 当查询语句的没有FROM⼦句时将会提⽰该额外信息
Impossible WHERE 查询语句的WHERE⼦句永远为FALSE时将会提⽰该额外信息
No matching min/max row 当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE⼦句中的搜索条件的记录时
Using index 当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使⽤索引覆盖的情况下，在Extra列将会提⽰该额外信息。
Using index condition 有些搜索条件中虽然出现了索引列，但却不能使⽤到索引
Using where 当我们使⽤全表扫描来执⾏对某个表的查询，并且该语句的WHERE⼦句中有针对该表的搜索条件时，在Extra列中会提⽰上述额外信息
Zero limit 当我们的LIMIT⼦句的参数为0时，表⽰压根⼉不打算从表中读出任何记录，将会提⽰该额外信息
Not exists 当我们使⽤左（外）连接时，如果WHERE⼦句中包含要求被驱动表的某个列等于NULL值的搜索条件，⽽且那个列又是不允许存储NULL值的，那么在该表的执⾏计划的Extra列就会提⽰Not exists额外 信息
Using join buffer (Block Nested Loop) 在连接查询执⾏过程中，当被驱动表不能有效的利⽤索引加快访问速度，MySQL⼀般会为其分配⼀块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法
Using intersect(…)、Using union(…)和Using sort_union(…) 如果执⾏计划的Extra列出现了Using intersect(…)提⽰，说明准备使⽤Intersect索引合并的⽅式执⾏查询，括号中的…表⽰需要进⾏索引合并的索引名称
Using filesort 有⼀些情况下对结果集中的记录进⾏排序是可以使⽤到索引的
Using temporary 在许多查询的执⾏过程中，MySQL可能会借助临时表来完成⼀些功能，⽐如去重、排序之类的
Start temporary, End temporary 我们前边唠叨⼦查询的时候说过，查询优化器会优先尝试将IN⼦查询转换成semi-join，⽽semi-join又有好多种执⾏策略，当执⾏策略为DuplicateWeedout时，也就是通过建⽴临时表来实现为外层 查询中的记录进⾏去重操作时，驱动表查询执⾏计划的Extra列将显⽰Start temporary提⽰，被驱动表查询执⾏计划的Extra列将显⽰End temporary提⽰
LooseScan 在将In⼦查询转为semi-join时，如果采⽤的是LooseScan执⾏策略
FirstMatch(tbl_name) 在将In⼦查询转为semi-join时，如果采⽤的是FirstMatch执⾏策略

Json格式的执⾏计划

以json格式返回执行计划信息，会返回一些查询成本数据，包括cpu成本和io成本，prefix_cost总耗时

EXPLAIN FORMAT=JSON

Extented EXPLAIN

最常见的就是Code为1003的信息，当Code值为1003时，Message字段展⽰的信息类似于查询优化器将我们的查 询语句重写后的语句

SHOW WARNINGS\G

optimizer trace 表的神奇功效

optimizer trace的功能，这个功能可以让我们⽅便的查看优化器⽣成执⾏计划的整个过程，这个功能的开启与关闭由 系统变量optimizer_trace决定，我们看⼀下： mysql> SHOW VARIABLES LIKE ‘optimizer_trace’;

不详细讲该功能了，大概说一下优化过程的三个阶段
prepare阶段
optimize阶段
execute阶段
我们所说的基于成本的优化主要集中在optimize阶段，对于单表查询来说，我们主要关注optimize阶段的"rows_estimation"这个过程，这个过程深⼊分析了对单表查询的各种执⾏⽅案的成本；对于多表 连接查询来说，我们更多需要注"considered_execution_plans"这个过程，这个过程⾥会写明各种不同的连接⽅式所对应的成本。反正优化器最终会选择成本最低的那种⽅案来作为最终的执⾏计划，也 就是我们使⽤EXPLAIN语句所展现出的那种⽅案。 如果有⼩伙伴对使⽤EXPLAIN语句展⽰出的对某个查询的执⾏计划很不理解，⼤家可以尝试使⽤optimizer trace功能来详细了解每⼀种执⾏⽅案对应的成本，相信这个功能能让⼤家更深⼊的了解MySQL查 询优化器。