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）主要思想就是将表分为多个块来执行；
  • 缓冲区，然后排序，再按顺序去获取那些行，这就使得原本的随机IO变成了磁盘的顺序IO了，速度将会比原来的快，最后返回匹配的行。需要注意的一点是，如果本身该查询是索引覆盖的，那就根本不需要访问表，直接访问索引树就行了，因此就不会使用BKA算法了；
  • 至于MRR，除了上面的好处外，它本身也会通过将二级索引的查找条件合并，减少无关索引的读取，来提高速度。总之，MRR的好处就是批处理+排序。

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

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

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

列名

解释

特殊说明

id

select的标识符

select在查询中的序号，同序号就表明是一组，序号的组越大越先执行，越外层数值越小，如果是union结果则是NULL，同组的话按照从上到下的顺序执行。

select_type

select类型

没有子查询或union时都是simple，否则会有primary和union之类的，这里要注意带有uncacheable的类型，表示无法缓存，外层行切换会导致重新计算该select

table

输出行的所属表

表名或<unionM,N>，<derivedN>，<subqueryN>

partitions

匹配的分区

涉及到表的分区，没有使用分区则是NULL

type

join类型

第5点有详细说明

possible_keys

可能被选择的索引

查找行的索引，独立于执行顺序的，这意味着不一定会使用，只是可能

key

实际被选择的索引

决定是否获取行，但由于索引扫描更高效，因此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类似，但包括NULL

index_merge

表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）

unique_subquery

在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！

index_subquery

同上，但把形如”select non_unique_key_column“的子查询替换

range

常数值的范围

index

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';可以查看优化器的一些选项。