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)中各列的解释: 5.type(join的类型): 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';可以查看优化器的一些选项。列名 解释 特殊说明 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 实际被选择的索引 可能会出现不在possible_keys的的key的情况。就是在无法使用possible_keys的时候,如果其余的某个索引覆盖了被选择的列,即该索引不能用于决定是否获取行,但由于索引扫描更高效,因此MySQL也会使用该索引来加速 key_len 被选择的键的长度 MySQL在多部分索引中使用的部分的长度,可能有多个值 ref 需要与索引比较的列 列名或者const(常数,where id = 1的时候就是const了) rows 估计要被检验的行数 InnoDB中不一定精确,只是一个估计值 filtered 被表的条件所过滤的行的百分比 估计值 extra 额外信息 内容太多,需要再查文档吧 类型 说明 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 1.当查询是索引覆盖的,即所有数据均可从索引树获取的时候(Extra中有Using Index);2.以索引顺序从索引中查找数据行的全表扫描(无
Using Index)。另外Extra中Using Index与Using
Where同时出现的话,则是利用索引查找键值的意思;如单独出现,则是用读索引来代替读行,但不用于查找的,也就是4中提到key的特例 all 全表扫描