在平常工作中,经常会碰到慢SQL问题,这个时候就会把SQL拉出来,用explain执行一下,看下到底卡在哪里。
在执行explain命令之后,现实的结果有12列,如下:
id 选择标识符
select_type 查询类型
table 输出结果集的表
partitions 匹配的分区
type 表的连接类型
possible_keys 查询时可能实用的索引
keys 实际使用的索引
key_len 使用索引字段的长度
ref 列与索引的比较
rows 扫描出的行数
filtered 按表条件过滤的行百分比
Extra 执行情况描述和说明
id
select标识符,可以理解为SQL的执行顺序,从大到小执行,ID相同时执行顺序从上到下,在所有组中,ID值越大,优先级越高,越先执行。
select_type
查询中每个select子句的类型
simple: 简单的select, 没有用union或子查询等
primary:子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为primary
union:union中的第二个或后面的select语句
dependent union:union中第二个或后面的select,取决于外层的查询
union result:union的结果集
subquery:子查询中的第一个select,取决于外面的select
dependent subquery:子查询中的第一个select,取决于外面的select
derived:派生表的select(from子句的子查询)
materialized:物化子查询
uncacheable subquery: 无法缓存结果且必须为外部查询的每一行重新评估的子查询
uncacheable union:属于不可缓存子查询的 UNION 中的第二个或后面的查询
table
输出行所引用的表的名称,这也可以是以下值之一:
<unionM,N>: 行是指 id 值为 M 和 N 的行的并集。
<derivedN>: 该行引用 id 值为 N 的行的派生表结果。例如,派生表可能来自 FROM 子句中的子查询。
<subqueryN>: 该行引用 id 值为 N 的行的具体化子查询的结果。
partitions
匹配的分区,该字段看table所在的分区, 值为NULL表示表未被分区.
type
表的访问方式,常用的访问类型有:all < index < range < ref < eq_ref < const < system,从左到右性能从差到好,一般来说,需要保证查询至少达到range级别,最好能达到ref级别
all 全表扫描,性能最差
index 全索引扫描,index与all的区别为index只遍历索引树。有两种产生方式:1. 如果索引是查询的覆盖索引并且表能满足查询需要的所有数据,则只会扫描索引树,在这种情况下,Extra列会显示Using index。仅扫描通常比ALL更快,因为索引的大小通常小于表数据;2. 使用从索引读取以按照索引顺序查找数据行来执行全表扫描,Using index不会出现在Extra列中,但是避免了重排序。
range 仅检索给定范围内的行,使用索引来选择行。输出行种的key列表示使用了哪个索引,key_len包含使用过的最长的索引部分,此类型下的ref列为NULL。当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 运算符将键列与常量进行比较时,可以使用范围。
index_subquery 非唯一性索引扫描, 返回匹配某个单独值的所有行. 本质上也是一种索引访问, 返回匹配某值(某条件)的多行数据, 属于查找和扫描的混合体。利用索引来关联子查询,不再扫描全表,但是大多数情况下使用select子查询时,MySQL查询优化器会自动将子查询优化为联表查询,因此type不会显示为index_subquery,而是ref。
unique_subquery类似ref, 区别在于使用的索引是唯一索引, 对于每个索引键值, 表中只有一条记录匹配, 常见于主键或唯一索引扫描。该连接类型类似于index_subquery,使用的是唯一索引,大多数情况下使用SELECT子查询时,MySQL查询优化器会自动将子查询优化为联表查询,因此 type 不会显示为 unique_subquery,而是eq_ref。
index_merge 此连接类型表示使用了索引合并优化。在这种情况下,输出行种的key列包含使用的索引列表,key_len包含使用的索引的最长key部分列表。
ref_or_null 这种连接类型类似于ref,但MySQL会额外搜索包含NULL值的行。这种连接优化最常用语解析子查询。
ref 使用了非唯一性索引进行数据的查找,ref 可用于使用 = 或 <=> 运算符进行比较的索引列。
eq_ref 当进行等值联表查询使用主键索引或者唯一性非空索引进行数据查找(实际上唯一索引等值查询type不是eq_ref而是const),eq_ref 可用于使用 = 运算符进行比较的索引列。 比较值可以是常量或表达式,它使用在此表之前读取的表中的列。
const 最多只能匹配到一条数据,通常使用主键或唯一索引进行等值条件查询。
system 表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,不需要进行磁盘io
possible_keys
显示可能使用在当前表上的索引,查询涉及到的紫断殇若存在索引,则该索引将被列出,但不一定被实际查询使用。
keys
实际使用的索引,如果为null,则没有使用索引,查询中若使用了覆盖索引,则该索引和查询的select字段重叠。
key_len
表示索引中使用的字节数,可以通过key_len计算查询中使用的索引长度,在不损失精度的情况下越短越好,索引越大占用的存储空间越大,这样IO的次数和量就会增加,影响执行效率。如果 key 列为 NULL,则 key_len 列也为 NULL。
ref
ref 列显示将哪些列或常量与键列中指定的索引进行比较以从表中选择行。
rows
根据表的统计信息及索引使用情况,大致估算出找出所需记录需要读取的行数,此参数很重要,直接反应的sql找了多少数据,在完成目的的情况下越少越好。
filtered
过滤列表示按表条件过滤的表行的估计百分比。 最大值为 100,这意味着没有发生行过滤。 值从 100 开始减少表示过滤量增加。 rows 显示检查的估计行数,rows × filtered 显示与下表连接的行数。 例如rows为1000,filtered为50.00(50%),则下表join的行数为1000×50%=500。
Extra
此列包含有关 MySQL 如何解析查询的附加信息。常见的几种取值:
Using filesort 表明MySQL无法利用索引进行排序,只能利用排序算法进行排序,会消耗额外的传递
Using temporary 建立临时表来保存中间结果,查询完成之后把临时表把临时表删除
Using index 表示当前的查询时使用了覆盖索引,直接从索引中读取数据,而不是访问数据表。如果同时出现Using where表明索引被用来执行索引键值的查找,如果没有,表明索引被用来读取数据,而不是真的查找
Using index condition 通过访问索引元组并首先测试它们以确定是否读取完整的表行来读取表。 以这种方式,索引信息用于延迟(“下推”)读取全表行,除非有必要。
Using where 使用where进行条件过滤
Using join buffer 使用连接缓存
impossible where where语句的结果总是false
参考:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types