学习笔记--mysql explain

在select关键字里增加explain关键字，mysql会在查询上设置一个标记，这时执行查询时返回执行计划中每一步信息，而不会直接返回。这是调优分析SQL语句的一个重要手段。

对一个表的查询，explain会返回一行信息，如果对多个表连接查询则会返回多行信息

explain 有2个变种，一是explain extended，效果是可以通过紧接其后的运行show warnings看到这个生成的语句。而是explain partitions，会查询将访问的分区

如果存在子查询，explain也会执行实际查询，将子查询结果放入临时表，然后完成外层查询优化并输出explain信息。

explain存在一些限制限制：
1. 无法获取触发器，存储过程或UDF对查询的影响。
2. 不支持存储过程。
3. 无法获取查询中的特定优化。
4. 不区分具有相同名字的事务，比如对于内存排序和临时文件都使用filesort，对磁盘和内存中的临时表都显示using temporary

一个explain的例子：
EXPLAIN SELECT film_id, category_id from sakila.film_category where category_id = 8 and film_id = 31;
结果如下：

explain中的列的含义：

1. id列，标识select所属的行。mysql将select查询分为简单和复杂查询，复杂类型又包含3类：简单子查询，派生表（就是from语句中的子查询）以及union查询，如果全是简单查询ID为1，如果有复杂查询ID会增加（这句没完全看懂）

2. select_type列，标识是简单查询还是复杂查询，简单查询为SIMPLE，表示不包括子查询和UNION，如果查询有复杂子查询，则最外层标记为PRIMARY，其他部分分别标记。如果是不在from语句中子查询，标记为SUBQUERY；在子查询中的select，mysql会递归执行并将结果放到临时表中，内部称为派生表，select_type就标识为DERIVED；如果是UNION后的select，就标记为UNION；如果是从union的匿名表临时检索的select会被标记为union result

3. table列，用来标识对应行正在访问的表的表名或者别名

4. type列，表示访问类型，即说明mysql决定如何查找表中的行包括：
   （1）ALL，全表扫描；
   （2）index，跟全表扫描一样，区别是使用扫描时按索引顺序而不是行，这样可以避免排序，但缺点是要承担按索引次序读取整个表的开销。如果在extra列中看到了’using index’，表示mysql在使用覆盖所有，只扫描索引数据，而不是按索引次序的每一行。
   （3）range，范围扫描，表示有限制的索引扫描。
   （4）ref，索引访问/索引查找，返回所有匹配某个单值的行，属于查找和扫描的混合体，只有访问非唯一索引才会发生这种查找类型。
   （5）eq_ref，使用这种索引查找，mysql知道最多只返回一条符合条件的记录，使用主键或者唯一索引时会看到，对这种访问类型，mysql优化得很好。
   （6）const, system，直接查找常量或者mysql优化器把某个数据变成常量后，再去查找就变成了这种访问类型。
   （7）NULL，表示不需要在执行时访问表
   （9）index_merge，新类型，表示对2个工作索引查找的集合进行merge操作

5. possible_keys列，显示了查询可以使用哪些索引，基于查询访问的列和使用的比较操作符来判断，这个列是在优化过程的早期创建的，有些在优化有实际不会使用。

6. key列，显示mysql实际决定采用哪个索引来优化对表的访问，不一定是possible_keys的子集

7. key_len列，显示mysql在索引里使用的字节数，使用的所有索引的总长度，对字符串来说，编码格式会影响这个长度。同时，这个长度是可能的最大长度，大于实际使用的字节数

8. ref列，表示key列记录的索引中查找值所用的列或者常量，即哪个字段或者常数与key一起被使用（暂时理解不了这什么意思）

9. rows列，表示mysql估计为了找到所需的行数而需要读取的行数。所以，第一，这个不是最终从表里读取的行数，而是为了查找符合查询的每一点上标准的那些行而必须读取的行的平均数。第二，这个是个估计值，有时候很不精确。如果是多个操作（explain出多行），总的rows是每行rows的乘积

10. filtered列，仅在explain extended时出现，表示针对表里某个条件的记录数的百分比做一个悲观估算。

11. extra列，包含不适合在其他列显示的额外信息，常见值有：
    （1）using index，表示mysql使用了覆盖索引，没有访问实际数据表
    （2）using where，意味着mysql服务器在存储引擎检索行以后再进行过滤，表示查询可受益于不同的索引。
    （3）using temporary，表示对查询结果排序时会使用一个临时表
    （4）using filesort，表示mysql会对结果使用一个外部索引排序，而不是按照所有次序从表里读取行。
    （5）range checked for each record(index map:N)，表示没有好用的索引，新的索引将在连接的每一行上重新估算
    （6）using union，表示使用的union联合多个查询结果