mysql explain详解

在日常工作中, 我们会记录一些执行时间比较久的SQL语句, 找出这些SQL语句并不意味着完事了, 我们常常用到explain这个命令来查看一个这些SQL语句的执行计划, 查看该SQL语句有没有使用上了索引, 有没有做全表扫描, 所以我们需要深入了解MySQL基于开销的优化器.

explain select * from t_member where member_id = 1;

在执行explain命令之后, 显示的信息一共有12列, 分别是:

• id: 选择标识符
• select_type: 查询类型
• table: 输出结果集的表
• partitions: 匹配的分区
• type: 表的连接类型
• possible_keys: 查询时可能使用的索引
• key: 实际使用的索引
• key_len: 索引字段的长度
• ref: 列与索引的比较
• rows: 扫描出的行数
• filtered: 按表条件过滤的行百分比
• extra: 执行情况描述和说明

1. id

select标识符, 可以理解为SQL执行的顺序, 从大到小执行.

id相同时执行顺序从上到下, 在所有组中, id值越大, 优先级越高, 越先执行

2. select_type

查询中每个select子句的类型.

1. simple: 简单的select, 不适用union或子查询等. 例如: SELECT * from t_member where member_id = 1;
2. primary: 子查询中最外层查询, 查询中若包含任何复杂的子部分, 最外层的select被标记为primary. 例如: SELECT member_id from t_member where member_id = 3 UNION all SELECT member_id from t_member
3. union: union中的第二个或后面的select语句. 例如: SELECT member_id from t_member where member_id = 3 UNION all SELECT member_id from t_member
4. dependent union: union中第二个或后面的select, 取决于外层的查询. 例如SELECT tm.* from t_member as tm where member_id in (SELECT member_id from t_member where member_id = 3 UNION all SELECT member_id from t_member)
5. union result: union的结果集
6. subquery: 子查询中的第一个select. 例如: SELECT * from t_member where member_id = (SELECT member_id from t_member where member_id = 5)
7. dependent subquery: 子查询中的第一个select, 取决于外面的select. 例如: SELECT tm.* from t_member as tm where member_id in (SELECT member_id from t_member where member_id = 3 UNION all SELECT member_id from t_member)
8. derived: 派生表的select(from子句的子查询). 例如: SELECT * from (SELECT * from t_member where member_id = 1) tbl

3. table

显示这一步所访问数据库中表名称. 有时候不是真实的表明, 可能是简称.

4. partitions

官方定义为The matching partitions(匹配的分区),该字段看table所在的分区, 值为NULL表示表未被分区.

5. type

对表访问方式, 表示MySQL在表中找到所需行的方式, 又称“访问类型”. 常用的类型有： all、index、range、 ref、eq_ref、const、system(从左到右, 性能从差到好), 一般来说, 需要保证查询至少达到range级别, 最好能达到ref级别.

• all: Full Table Scan, 全表扫描.
• index: Full Index Scan, 全索引扫描. index与all区别为index只遍历索引树, 通常比all快, 因为索引文件通常比数据文件小.
• range: 只检索给定范围的行, 使用一个索引来检索行, 可以在key列中查看使用的索引, 一般出现在where条件中, 比如使用between, <, >, in等查询. 这种索引的范围扫描比全表扫描要好, 因为索引的开始点和结束点都固定, 不用扫描全索引.
• ref: 非唯一性索引扫描, 返回匹配某个单独值的所有行. 本质上也是一种索引访问, 返回匹配某值(某条件)的多行数据, 属于查找和扫描的混合体.
• eq_ref: 类似ref, 区别在于使用的索引是唯一索引, 对于每个索引键值, 表中只有一条记录匹配, 常见于主键或唯一索引扫描.
• const、system: 表示通过一次索引就找到了结果, 常见于primary key或unique索引, 因为只匹配一行数据, 所以查询非常快. 如将主键置于where列表中, MySQL就能将该查询转换为一个常量. system是const类型的特例, 当查询的表只有一行的情况下, 使用system.

6. possible_keys

显示可能应用在表中的索引, 可能一个或多个. 查询涉及到的字段若存在索引, 则该索引将被列出, 但不一定被查询实际使用.

7. key

实际中使用的索引, 如为NULL, 则表示未使用索引. 要想强制MySQL使用或忽视possible_keys列中的索引, 在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX.

8. key_len

表示索引中所使用的字节数, 可通过该列计算查询中使用的索引长度. 在不损失精确性的情况下, 长度越短越好. key_len显示的值为索引字段的最大可能长度, 并非实际使用长度, 即key_len是根据表定义计算而得, 并不是通过表内检索出的.

9. ref

显示关联的字段. 如果使用常数等值查询, 则显示const, 如果是连接查询, 则会显示关联的字段.

10. rows

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数. 当然该值越小越好.

11. filtered

百分比值, 表示存储引擎返回的数据经过滤后, 剩下多少满足查询条件记录数量的比例.

12. extra

显示十分重要的额外信息. 其取值有以下几个：

• using filesort: 表明mysql会对数据使用一个外部的索引排序, 而不是按照表内的索引顺序进行读取. 在mysql中, 无法利用索引完成的排序操作称为"文件排序". 当出现using filesort时就非常危险了, 在数据量非常大的时候几乎"九死一生". 出现using filesort尽快优化sql语句.
• using temporary: 使用了临时表保存中间结果, 常见于排序order by和分组查询group by. 非常危险, “十死无生”, 急需优化.
• using index: 表明相应的select操作中使用了覆盖索引, 避免访问表的额外数据行, 效率不错. 如果同时出现了using where, 表明索引被用来执行索引键值的查找. 如果没有同时出现using where, 表明索引用来读取数据而非执行查找动作.
• using where: 不用读取表中所有信息, 仅通过索引就可以获取所需数据, 这发生在对表的全部的请求列都是同一个索引的部分的时候, 表示mysql服务器将在存储引擎检索行后再进行过滤.

总结

explain包含的信息十分的丰富, 主要关注以下几个字段信息.

1. id: select子句或表执行顺序, id相同, 从上到下执行, id不同, id值越大, 执行优先级越高.
2. type: type主要取值及其表示sql的好坏程度(由好到差排序)：system > const > eq_ref > ref > range > index > ALL. 保证range, 最好到ref.
3. key: 实际被使用的索引列.
4. ref: 关联的字段, 常量等值查询, 显示为const, 如果为连接查询, 显示关联的字段.
5. Extra: 额外信息, 使用优先级using index > using filesort > using temporary.