当我们写了一条sql语句,想知道这条sql到底是怎么执行的,性能如何,用了哪几个索引,有没有全表扫描,查询了多少行记录等,怎么办呢?
别慌,mysql explain命令,拿走,香,真香,妈妈再也不用担心我不会sql优化了。
explain的用途
- 表的加载顺序如何
- 哪些索引被使用到
- 哪些所以可以使用
- 每个表有多少行被查询了
- 表和表之间的应用关系如何
- …
explain结果字段
如图,执行计划包含了12个字段:id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra
字段 | 说明 |
---|---|
id | 表示查询中执行select子句或者操作表的顺序,id 的值越大,代表优先级越高,越先执行 |
select_type | 表示查询的类型,例如:simple、primary、subquery等 |
table | 正在访问的表名,如果有别名,则显示别名 |
partitions | 匹配到的分区信息,如果是非分区表,值为Null |
type | 查询使用了哪种类型,非常重要的指标,好到差依次为:Null>system>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL |
possible_keys | 可能用到的索引,但不确定是否是最终实际使用到的索引 |
key | 最终实际用到的索引,如果是Null,则没有用到索引 |
key_len | 用到的索引长度(字节数),越短越好 |
ref | 显示索引的哪一列被使用了,可能是一个常数,常见的有:const、func、null、字段名 |
rows | 大致估算找到所需数据读取了多少的行数,rows值越小越好,直观显示SQL性能的好坏 |
filtered | 存储引擎返回的数据在经过过滤后,剩下满足条件的记录数量的比例 |
Extra | 不适合在其他列显示的信息,额外的信息 |
字段详解
一、id
- id相同
顺序执行,由上而下,teacher-subject-student_score
- id不同
如果存在子查询,就会出现id的序号递增情况,id值越大越先执行
- id有相同的,也有不同的
id值大的先执行,相同id的,由上而下执行
二、select_type
select_type如下:
-
SIMPLE
简单select查询,没有子查询、union交并差集操作
-
PRIMARY
主查询,当查询中包含任何复杂的子部分,最外层的查询就称为主查询PRIMARY
-
SUBQUERY
在where或select中包含子查询,那查询就会称为SUBQUERY
-
DERIVED
在from中包含子查询,会被标记为DERIVED,会把结果放到临时表里,不过mysql5.7+进行了优化。 -
UNION
union后面出现select,那select这条查询会标记为union;如果union包含在from的字句中,union前面的select会标记为derived
-
UNION RESULT
意思是从union的临时表中获取数据,union2,3表示用第2个和第3个的结果进行union操作
三、table
表名,并不一定是真实的表名,可能是别名,比如上面的union2,3
四、partitions
表示命中的分区信息,对于非分区,显示Null
五、type
查询使用了何种类型,性能好到坏依次是:
Null>systeml>constl>eq_refl>refl>ref_or_nulll>index_mergel>unique_subqueryl>index_subqueryl>rangel>indexl>ALL
一般来说,查询至少达到range,最好能到ref
-
Null
mysql能够在优化阶段分解查询语句,在执行阶段不用再访问表或索引
-
system
当表仅有一条记录时(系统表),数据量很少,一般不会出现,可以忽略 -
const
表示通过索引一次就能找到,命中primary key主键或unique唯一索引
-
eq_ref
唯一索引或主键,表中只有一条数据与之匹配
-
ref
非唯一索引,匹配到多行
-
ref_or_null
跟ref类型类似,只是增加了null值的比较,实际用的不多 -
index_merge
查询使用了两个以上的索引,可能使用索引合并优化的方法,就是说对于多个索引分别条件扫描,然后将各自结果合并(intersect/union),Mysql5.1开始引入index_merge.
-
unique_subquery
替换下面的in子查询,子查询中的唯一索引,子查询返回不重复的集合
value in (select primary_key from single_table where some_expr) -
index_subquery
用于非唯一索引,可以返回重复值
value in(select key_column from single_table where some_expr) -
range
针对一个索引的字段,给定范围检索数据,在where语句中使用了:between…and、<、>、<=、in等条件语句,查询type都是range
-
index
index和all都是读全表,但index是遍历索引树,all是从硬盘读取,所以通常index比all快
-
all
全表查询
六、possible_keys
可能应用的索引,一个或多个
七、key
查询最终用到的索引,如果为null,则没有可用索引,如果使用的是覆盖索引(查询的列刚好是索引),则该索引只出现在key列
八、key_len
查询用到的索引长度(字节数),越短越好
九、ref字段
显示索引在哪一列被使用了,如果可能的话,是一个常数
十、rows字段
以表的统计信息和索引使用情况,估算查询所需的数据要查询多少行
十一、partitions字段
匹配分区
十二、filtered
存储引擎返回的数据在经过过滤后,剩下满足条件的记录数量的比例
十三、extra
额外重要的信息
-
Using filesort
order by的字段没有索引,mysql无法利用索引完成的排序,称为文件排序,这样的sql都是需要优化的
-
Using temporary
查询后的结果需要使用临时表来存储,一般在排序或者分组时用到
-
Using index
查询使用了覆盖索引(查询的字段刚好有索引),sql优化的理想状态
如果同时出现using where ,说明索引被用来查找
没有同时出现using where,说明索引只用来读取数据,没有用来查找
-
Using where
查询时没有找到可用的索引,从而通过where条件过滤获取结果
-
Using join buffer
连表查询时,连接条件没有用到索引,需要一个连接缓冲区来存储中间结果
当去除了t_no的索引,使用了缓冲区
-
Impossible where
where条件,总是false
-
distinct
一旦mysql找到了与行相联合匹配的行,就不在搜索了
-
select tables optimized away
select 操作已经优化到不能再优化了,mysql没有遍历表或索引就返回数据了