mysql explain详解

mysql explain 输出详解

根据官方5.7手册整理(只列出常见的输出)
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

explain输出中表的顺序：mysql会将表以处理sql时读取的顺序列出来 –根据测试，外层的表会列在上面

join的顺序：假设A join B join C，从表A中读取第一行a1，找到B中对应N行中的第一行b1，再根据a1.b1从C表中找到所有匹配行;然后根据B表N行的第二行b2,再根据a1.b2从C表中找到所有匹配行…..

在explain语句执行后使用show warnings可以用来获取额外的信息。（show warnins要求的权限和explain不同）

Output Columns

id:查询中select的序列号，越外层越小
select_type:
1. simple: 没有使用union或子查询
2. primary: 最外层的select
3. union：
4. dependent union:
5. union result:
6. subquery: 子查询中的第一个select
7. dependent subquery： 子查询中的第一个select，依赖于外层查询
8. derived： 派生表(from后的子查询)的select
9. materialized：
10. uncacheable subquery: 无法缓存的子查询，每次外层查询中的行进来匹配时都会重新执行子查询
11. uncacheable union：

dependent 一般表示使用了关联子查询(correlated subqueries):

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);

table: 表名
partitions：对于分区的表，表示在哪个分区
type： join的类型

1. system：表只有一行
2. const：表只有一行匹配，并在查询开始时就被读取，因此被优化器视为常数。
   当主键或者唯一索引和一个常数比较时会出现。
3. eq_ref: 每次匹配时只会读取一行，当主键或者唯一非空索引(unique not null index)的全部部分被使用时会出现。
   const 好于 eq_ref 好于 ref
4. ref: 每次匹配时会读取所有匹配行，当用到了非唯一索引或者唯一索引的一部分时会出现
5. fulltext： 全文索引
6. ref_or_null: 与ref相同，并且mysql还会搜索包含null值的行：
   
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

7. index_merge:
8. unique_subquery:
9. index_subquery:
10. range:根据索引选取一定范围内的行，ken_len列表示使用到的最长的索引部分。
11. index：两种情况
    1.从索引中获取所有的数据，不需要访问原表。(对于innodb引擎，索引中会有主键数据)
    2.根据索引的顺序进行全表扫描
12. ALL：全表扫描

possible_keys： 可能用到的索引
key： 实际使用的索引，不一定在possible_keys中存在。
key_len: 表示使用的索引的长度，可以用来判断多列索引中的哪些部分被使用到了。
一般等于索引列类型的字节长度，因此对于字符串需要考虑其使用的字符集的长度。可以为null的列需要一个额外字节来表示是否为null。(varchar列需要额外字节来表示长度(unsigned int,小于等于255一个字节),不知道为什么在这里是固定两个字节)
ref： 匹配时使用到的列或者常数，如果值是func，表示匹配时用到了由函数生成的值(+-*/也算)。
如果要看使用到了哪些函数，可以在explain语句执行后使用show warnings。
rows: 检查的行数，innodb引擎是个估计值
filtered: 过滤后得到行数的百分比，rows*filtered/100 表示实际用于join的行数。
extra：额外信息。常见的有：
1. using where： 使用了where子句来筛选数据
2. using index：从索引中获取全部数据，不需要访问原表
3. using temporary ：需要使用临时表来保存结果
4. using filesort: 需要对取得的行进行排序，并保存排序的索引，行数据会按照索引的顺序被检索。