初步理解MySQL（5.6）的执行计划

声明：以下均来自于MySQL英文手册5.6，本文主要针对执行计划，由于本人才疏学浅，其他部分可能会一句话带过。

引言.什么是执行计划：

The set of operations that the optimizer chooses to perform the most efficient query is called the “query execution plan”, also known as the EXPLAIN plan。

优化器为了最有效的执行查询而选择的一系列操作被称为执行计划。

1.MySQL所有的join都是使用 nest-loop join 算法（嵌套循环算法），当然了这里包括了它的一些变种算法。这里顺便提下MySQL的一些join算法：

• 块嵌套循环 （block nest-loop join）主要思想就是将表分为多个块来执行；
  

• BKA算法，就是Batch Key Access（5.6.3才有的实现） ，适用于使用索引的时候，利用一种叫MRR的接口（multi-range read）将针对join buffer里的行，构建出需要的键，并批量提交给MRR引擎，然后MRR引擎会以一种较优的方式（引擎相关的）在索引中查找，这种方式其实就是：根据键获取rowID或者主键放在缓冲区，然后排序，再按顺序去获取那些行，这就使得原本的随机IO变成了磁盘的顺序IO了，速度将会比原来的快，最后返回匹配的行。需要注意的一点是，如果本身该查询是索引覆盖的，那就根本不需要访问表，直接访问索引树就行了，因此就不会使用BKA算法了；
  

• 至于MRR，除了上面的好处外，它本身也会通过将二级索引的查找条件合并，减少无关索引的读取，来提高速度。总之，MRR的好处就是批处理+排序。
  

2.对于一组joins，MySQL的join算法会从第一个表读取一行，然后一直往后逐个表找匹配行，如果某一行能够从第一个表开始，之后每个表都能找到匹配的行，则输出该“大行”；然后原路返回到之前的表直到能找到一张包含匹配行的表为止，然后继续向后面的每个表找匹配的行。

3.MySQL中索引的使用会被索引的cardinality所影响，cardinality就是基数，集合的势的意思，太低会导致索引被弃用，举个例子，如果向sex这种取值太单一的字段建立索引，该索引可能不会被使用，因为基数太小了。可以通过Analyze Table tbl_name，来分析表，更新表的统计数据（InnoDB,MyISAM一般会自动更新）。

4.执行计划输出（Explain Output）中各列的解释：

列名
解释
特殊说明
idselect的标识符select在查询中的序号，同序号就表明是一组，序号的组越大越先执行，越外层数值越小，如果是union结果则是NULL，同组的话按照从上到下的顺序执行。
select_typeselect类型没有子查询或union时都是simple，否则会有primary和union之类的，这里要注意带有uncacheable的类型，表示无法缓存，外层行切换会导致重新计算该select
table输出行的所属表表名或<unionM,N>，<derivedN>，<subqueryN>
partitions匹配的分区涉及到表的分区，没有使用分区则是NULL
typejoin类型第5点有详细说明
possible_keys可能被选择的索引用于查找行的索引，独立于执行顺序的，这意味着不一定会使用，只是可能
key实际被选择的索引可能会出现不在possible_keys的的key的情况。就是在无法使用possible_keys的时候，如果其余的某个索引覆盖了被选择的列，即该索引不能用于决定是否获取行，但由于索引扫描更高效，因此MySQL也会使用该索引来加速
key_len被选择的键的长度MySQL在多部分索引中使用的部分的长度，可能有多个值
ref需要与索引比较的列列名或者const（常数，where id = 1的时候就是const了）
rows估计要被检验的行数InnoDB中不一定精确，只是一个估计值
filtered被表的条件所过滤的行的百分比估计值
extra额外信息内容太多，需要再查文档吧

5.type(join的类型)：

类型
说明
system表只有一行const表最多只有一行匹配eq_ref每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引ref如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键fulltext全文搜索ref_or_null与ref类似，但包括NULLindex_merge表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
unique_subquery在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！
index_subquery同上，但把形如”select non_unique_key_column“的子查询替换range常数值的范围index1.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；2.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）。另外Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；如单独出现，则是用读索引来代替读行，但不用于查找的，也就是4中提到key的特例
all全表扫描

6.评估查询性能可以通过计算磁盘寻址次数：

• 小表一般一次寻址可以读取一行，因为索引可能被缓存。
  

• 大表则可以通过下列公式：log(row_count) /log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) +1。

• 如果是写入的话，通常需要四次寻址，其中一次寻址是寻找插入新索引的地方，通常更新索引需要两次（B树插入新节点后调整的平均次数是两次？Why？统计学原理？），最后一次写入该行。
  

7.select @@optimizer_switch;或show variables like 'optimizer_switch';可以查看优化器的一些选项。