mysql的explain命令

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

  • id: 选择标识符
  • select_type: 查询类型
  • table: 输出结果集的表
  • partitions: 匹配的分区
  • type: 数据访问/读取操作类型(ALL、index、range、ref、eq_ref、const/system、NULL)
  • possible_keys: 查询时可能使用的索引
  • key: 实际使用的索引
  • key_len: 索引字段的长度
  • ref: 显示了之前的表在key列记录的索引中查找值所用的列或常量
  • rows: 为了找到所需的行而需要读取的行数,估算值,不精确。通过把所有rows列值相乘,可粗略估算整个查询会检查的行数
  • filtered: 按表条件过滤的行百分比
  • extra: 执行情况描述和说明

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.

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ,一般来说,得保证查询至少达到range级别,最好能达到ref。

extra 是 explain 中比较重要的一个属性指标之一,标识着SQL语句的索引使用情况。

接下来 讲讲常用见的几种状态。

  • Using filesort

这种情况是在使用 order by 关键字的时候,如果待排序的内容无法通过索引直接直接进行排序,mysql就有可能进行文件排序。

当然不是说出现了此情况就会对sql语句的效率造成影响。但是由于查询次数过多的话,对于排序的效率还是有一定的影响的。

可以通过设置 max_length_for_sort_data 来 提高 order by 的效率。如果操作的数据大小高于max_length_for_sort_data 的缓存大小时,mysql会产生临时表进行查询,一定程度上会印象效率。 max_length_for_sort_data的默认值是1024。

优化方案:

优化业务逻辑,不使用 order by ,而在业务代码中执行排序方法

设置索引,将带排序的内容放在索引中,直接利用索引进行排序

  • Using index

使用索引,表示索引能够覆盖所有的查询字段,无需进行回表查询所以效率会高。大部分情况代表最优

  • Using where 单独出现

表示当前查询的字段不能被索引覆盖,所以可能会产生回表,效率比前者低

  • Using where;Using index

表示查询的列被索引覆盖,且where筛选条件是索引列前导列的一个范围,或者是索引列的非前导列。 效率也比较高

  • null

表示查询的列未被索引覆盖,且where筛选条件是索引的前导列,这意味着用到了索引,但是部分字段未被索引覆盖,必须通过“回表查询”来实现,因而性能也比前两者差。

  • Using index condition

表示查询条件中虽然出现了索引列,但是有部分条件无法使用索引,会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。