在这里对 explain 的各个字段进行详细的分析, 来帮助大家分析自己所写的 sql 是否最佳的使用了索引, 需要的朋友可以参考下
explain 显示了 mysql 如何使用索引来处理 select 语句以及连接表可以帮助选择更好的索引和写出更优化的查询语句
使用方法, 在 select 语句前加上 explain 就可以了, 如:
explain select * from statuses_status where id = 11;
explain 列的解释
table: 显示这一行的数据是关于哪张表的
type: 这是重要的列, 显示连接使用了何种类型从最好到最差的连接类型为 consteq_regrefrangeindexhe 和 all
possible_keys: 显示可能应用在这张表中的索引如果为空, 没有可能的索引可以为相关的域从 where 语句中选择一个合适的语句
key: 实际使用的索引如果为 null, 则没有使用索引很少的情况下, mysql 会选择优化不足的索引这种情况下, 可以在 select 语句中使用 use index(indexname)来强制使用一个索引或者用 ignore index(indexname)来强制 mysql 忽略索引
key_len: 使用的索引的长度在不损失精确性的情况下, 长度越短越好
ref: 显示索引的哪一列被使用了, 如果可能的话, 是一个常数
rows:mysql 认为必须检查的用来返回请求数据的行数
extra: 关于 mysql 如何解析查询的额外信息将在表 4.3 中讨论, 但这里可以看到的坏的例子是 using temporary 和 using filesort, 意思 mysql 根本不能使用索引, 结果是检索会很慢
extra 列返回的描述的意义
distinct: 一旦 mysql 找到了与行相联合匹配的行, 就不再搜索了
not exists: mysql 优化了 left join, 一旦它找到了匹配 left join 标准的行, 就不再搜索了
range checked for each record(index map:#): 没有找到理想的索引, 因此对于从前面表中来的每一个行组合, mysql 检查使用哪个索引, 并用它来从表中返回行这是使用索引的最慢的连接之一
using filesort: 看到这个的时候, 查询就需要优化了 mysql 需要进行额外的步骤来发现如何对返回的行排序它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的, 这发生在对表的全部的请求列都是同一个索引的部分的时候
using temporary 看到这个的时候, 查询需要优化了这里, mysql 需要创建一个临时表来存储结果, 这通常发生在对不同的列集进行 order by 上, 而不是 group by 上
where used 使用了 where 从句来限制哪些行将与下一张表匹配或者是返回给用户如果不想返回表中的全部行, 并且连接类型 all 或 index, 这就会发生, 或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)
system 表只有一行: system 表这是 const 连接类型的特殊情况
const: 表中的一个记录的最大值能够匹配这个查询 (索引可以是主键或惟一索引) 因为只有一行, 这个值实际就是常数, 因为 mysql 先读这个值然后把它当做常数来对待
eq_ref: 在连接中, mysql 在查询时, 从前面的表中, 对每一个记录的联合都从表中读取一个记录, 它在查询使用了索引为主键或惟一键的全部时使用
ref: 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分 (比如, 利用最左边前缀) 时发生对于之前的表的每一个行联合, 全部记录都将从表中读出这个类型严重依赖于根据索引匹配的记录多少越少越好
range: 这个连接类型使用索引返回一个范围中的行, 比如使用>或<查找东西时发生的情况
index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比 all 更好, 因为索引一般小于表数据)
all: 这个连接类型对于前面的每一个记录联合进行完全扫描, 这一般比较糟糕, 应该尽量避免
分析索引分析
在这里对 explain 的各个字段进行详细的分析, 来帮助大家分析自己所写的 sql 是否最佳的使用了索引
首先是 select_type: 将 select 查询分为简单 (simple) 和复杂两种类型
复杂类型又分为子查询 (subquery) 和 from 列表中包含子查询(drived)
simple:
drived:
就 type 进行详细的介绍:
System,const,eq_ref,ref,range,index,all
all: 即全表扫描
index: 按索引次序扫描, 先读索引, 再读实际的行, 结果还是全表扫描, 主要优点是避免了排序因为索引是排好的
range: 以范围的形式扫描
explain select * from a where a_id > 1\G
ref: 非唯一索引访问(只有普通索引)
- create table a(a_id int not null, key(a_id));
- insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
- mysql> explain select * from a where a_id=1\G
eq_ref: 使用唯一索引查找(主键或唯一索引)
const: 常量查询
在整个查询过程中这个表最多只会有一条匹配的行, 比如主键 id=1 就肯定只有一行, 只需读取一次表数据便能取得所需的结果, 且表数据在分解执行计划时读取
当结果不是一条时, 就会变成 index 或 range 等其他类型
system: 系统查询
null: 优化过程中就已经得到结果, 不在访问表或索引
possible_keys: 可能用到的索引
key: 实际用到的索引
key_line: 索引字段最大可能使用长度
ref:
指出对 key 列所选择的索引的查找方式, 常见的值有 const, func, NULL, 具体字段名当 key 列为 NULL , 即不使用索引时, 此值也相应的为 NULL
rows: 估计需要扫描的行数
Extra: 显示以上信息之外的其他信息
Using index
此查询使用了覆盖索引(Covering Index), 即通过索引就能返回结果, 无需访问表
若没显示 "Using index" 表示读取了表数据
Using where
表示 MySQL 服务器从存储引擎收到行后再进行后过滤 (Post-filter) 所谓后过滤, 就是先读取整行数据, 再检查此行是否符合 where 句的条件, 符合就留下, 不符合便丢弃因为检查是在读取行后才进行的, 所以称为后过滤
Using temporary
使用到临时表
建表及插入数据:
- create table a(a_id int, b_id int);
- insert into a values(1,1),(1,1),(2,1),(2,2),(3,1);
- mysql> explain select distinct a_id from a\G
- Extra: Using temporary
MySQL 使用临时表来实现 distinct 操作
Using filesort
若查询所需的排序与使用的索引的排序一致, 因为索引是已排序的, 因此按索引的顺序读取结果返回, 否则, 在取得结果后, 还需要按查询所需的顺序对结果进行排序, 这时就会出现 Using filesort
select * from a order by id;
对于没有索引的列进行 order by 就会出现 filesort
来源: http://www.phperz.com/article/18/0217/363122.html