初步理解MySQL(5.6)的执行计划

jopen 10年前

声明:以下均来自于MySQL英文手册5.6。

1.MySQL所有的join都是使用 nest-loop join 算法(嵌套循环算法)。

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

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

4.

执行计划中各列的解释:

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

5.type(join的类型):

system(表只有一行),

const(表最多只有一行匹配),

eq_ref(每次与之前的表合并行都只在该表读取一行,这是除了system,const之外最好的一种,特点是使用=,而且索引的所有部分都参与join且索引是主键或非空唯一键的索引),

ref(如果每次只匹配少数行,那就是比较好的一种,使用=或<=>,可以是左覆盖索引或非主键或非唯一键),

fulltext(全文搜索),

ref_or_null(与ref类似,但包括NULL),

index_merge(表示出现了索引合并优化,这个比较复杂,目前的理解是合并单表的范围索引扫描),

unique_subquery(把形如“select primary_key”的子查询替换),

index_subquery(把形如”select key_column“的子查询替换),

range(常数范围),

index(一种情况是索引覆盖的全表扫描,只需查索引(3中表格所指出的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';可以查看优化器的一些选项。

8.什么是执行计划:

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。

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