高性能mysql.附录D使用explain

1.调用Explain可以解释优化器是如何工作的。

Explain输出结果的每一行对于select中的一个表，这里的表是一个广义上的概念，可以是作为别名的表、一个子查询或者一个union结果等。

Explain的两个变体：

Explain extended行为跟普通的explain相似，但是还告知服务器执行“反编译”的功能，然后用show warnings就能看到服务器准备执行的sql语句。

Explain partitions显示的是要查询的分表。

explain并不是不对select语句做任何的执行，事实上它会对子查询进行执行，并把结果放到临时表里，然后再对外面的查询进行分析和优化。

 Explain的局限性

.explain不会告诉你任何可能影响查询的触发器、存储函数、用户定义函数(UDF)的信息

.对存储函数没有用

.显示的统计信息是估算的，并不精确

.无法显示优化器所有的优化工作

……

 

2.Explain输出结果每个列的含义

2.1.Id列表示识别出的这个select是第几个。

2.2Select_type列显示了该行是简单查询还是复杂查询。

Simple表示没有子查询和union；

primary表示最外层部分；

subquery表示在select里出现的子查询（而不是在from里）；

derived表示from里出现的子查询，服务器会递归执行这些子查询，把结果放在临时表里，作为一个“衍生表”来引用；

Union表示union里面的第二个及以后的select；

Union result 表示从union临时表获取结果的select。

除这些外dependent和uncacheable可以用来修饰subquery和union。Dependent表示select依赖着外层的查询；Uncacheable表示select里某些东西阻止了item_cache的缓存。

2. 3.Table列表示当前行访问的是哪个表

2.4.Type列 mysql手册上写的是联接类型，但是可能理解为访问类型更合适，就是mysql在表里找到所需行的方式。

ALL 全表扫描。但是也有例外，如使用了limit，或者在extra列里显示使用了“Using distinct/not exists”。

Index 按照索引的顺序进行全表扫描。优点是避免了排序；缺点是按照索引顺序扫描表意味着随机读，这个代价是很昂贵的。如果在extra列里面出现了“Using index”，说明mysql使用了覆盖索引，这样就只需要扫描索引，而不用扫描表了。

Range 表示范围索引扫描。如在where中使用了between 或者>，或者使用了in（）、or等。

Ref 是一种索引访问（或叫做索引查找），它返回所有匹配某个单值的行。只有当使用非唯一索引或唯一性所以的非唯一前缀时才会发生。叫做ref是因为索引要跟某个参考值作比较，整个参考值可以是个常数，或者是来自前一个表里的多表查询的结果值。Ref_or_null是ref的一个变体，表示mysql必须进行二次查找，在初次查找之后找出NULL条目。

Eq_ref 跟ref一样是个索引访问，但是只返回一个结果。这在使用了主键或唯一索引时会看到。Mysql对该类型查找优化比较好。

Const，system 可以优化部分查询并把它转换成一个常量。如在where中使用了主键，mysql就可以把它转换成一个常量，从而移除了联接。

Null 表示mysql可以在优化阶段解决该查询，而不需要在执行阶段访问索引或表。如选择索引列的最小值，可以直接检查索引而不需要在执行阶段访问表。

2.5.possible_keys列显示了查询可能会使用到的索引。由于这个列表在优化阶段的早期就被得出，在子查询优化后，列表里的一些索引可能没有被用到。

2.6.Key列显示了mysql优化表访问时用到的索引。Possible_keys揭示了哪个索引能有助于行查找更高效，而key显示的是优化器采用哪个索引可以最小化查询成本。参考《高性能mysql》ver2 p.164查询优化过程中优化器的成本衡量值。

2.7.Key_len列显示了mysql在索引里使用的字节数。据此可能推断出选用了哪一列。

2.8.ref列显示了用来查找key列里显示的索引而使用的之前表里的一些列或常量。This column shows which columns or constants from preceding tables are being used to look up values in the index named in the key column.

2.9.rows列是mysql为了找到所需行而需要读取的行数。整个数字是nested-loop join plan里面的一个循环，就是说是一个循环中的平均值。

2.10.filtered列是在5.1版本加进去的，当使用explain extended时才会出现。它显示的是表里符合某个条件（如where或join条件）的行数百分比的一个悲观估计。Rows列与整个百分比相乘就是mysql估计用来联结前表的行数。到目前为止，优化器只把此用在ALL,INDEX,RANGE和INDEX_merge访问方式上。

2.11extra列 该列的全部值请参考mysql官方手册，这里只列取部分：

“using index”表示使用覆盖索引，从而避免了访问表。

“using where”表示存储引擎取回数据后，mysql服务器会做一个后过滤（post-filter）。Where条件的列为索引列时在存储引擎在读取索引时就会被检查，所以并不是所有带where的查询都会出现“using where”。有时候“using where”暗示着查询可以从多个索引中获益。

“using temporary”表示mysql在对查询结果排序时会使用一个临时表。

“using filesort”表示mysql使用一个外部排序法对结果进行排序，而不是按索引顺序。Mysql的两种filesort算法请参考英文第三版第六章。

“Range checked for each record (index map: N)”表示没有合适的索引，而且对联接中的每一行将作索引重估。

3.explain可视化输出

关注percona toolkit中的pt-visual-explain

4. mysql5.6版本的改进

mysql5.6版本中加入了对update，insert等的explain；改进了优化器和执行引擎以允许explain时可以不用执行子查询；给优化器加入optimizer trace功能，可以让用户不但看到优化器做的决定而且还有inputs（如index的集的势）和这样做的理由。

参考O'Reilly.High.Performance.MySQL.3rd.Edition.Mar.2012