SQL 优化之 Explain 命令
背景
在SQL优化的时候我们都会使用Explain命令去查看该条SQL的执行计划信息。
使用 Explain 关键字可以模拟优化器执行SQL语句,分析查询语句或是结构的性能瓶颈。在select语句之前增加Explain关键字,MySQL会在查询上设置一个标记,执行查询会返回执行计划的信息。因此我们可以根据这个信息去判断该条SQL是否按照我们的预期执行,他的效率和性能是不是良好的
Explain 信息结构详解
执行Explain命令后会返回如下信息:
根据上面的信息,我们讲解下各列的作用。
id列
id列的编号是select的序列号,有几个select就有几个id,并且id的顺序是按select出现的顺序增长的。id越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行
出现多个id的情况通常适用于关联子查询,即查询的表是select查询后的虚拟表(临时表)
select_type列
他表示对应行是简单还是复杂的查询,有以下几个值
- simple: 简单查询。查询不包含子查询和union
- primary: 查询中若包含任何复杂的子查询,最外层查询则标记为primary,也就是最后加载的那个select
- subquery:包含在select或者where子句中的子查询(不在from子句中)
- derived: 包含在from子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表
derived只会在子查询作为临时表时出现
- union:在union关键字随后的select
该列的例子sql语句
explain select (select 1 from actor where id = 1) from (select * from film
where id = 1) der;
explain select 1 union all select 1;
table列
这一列标识Explain的一行正在访问哪个表
当from子句中有子查询时,table列是格式,表示当前依赖id=N的查询,于是先执行id=N的查询。
当有union时,union result 的table列的值为 <union 1,2>
1和2标识参与union的select行id值
type列
这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行对应的大概范围。
依次从最优到最差分别为: system > const > eq_ref > ref > range > index > All
一般来说,除了All都会使用到索引,得保证查询能达到range级别,最好是ref。
NULL: MySQL能够在优化阶段分解查询语句,在执行阶段用不着访问表或者索引。例如:在索引列中取最小值,可以单独查询索引来完成,不需要执行时访问表。
下面依次说下各值的含义:
- const 、 system: mysql能对查询的某部分进行优化并将其转换成一个常量(可以看做是show warnings的结果)。 用于primary key或者unique key的所有列与常数比较时,所以表最多有一个匹配行,读取依次,读速较快。system是const的特例,表中只有一行元素匹配时为system。
- eq_ref: primary key或者unique key索引的所有部分被连接使用,最多返回一条符合条件的记录。这可能是const之外最好的联接类型,简单的select查询不会出现这种type。
- ref: 相比eq_ref,不适用唯一索引(unique key),而是使用普通索引或者唯一索引的部分前缀,索引要和某个值比较,可能会找到多个符合条件的行
- range: 范围扫描通常出现在in(),between,>,<,>=等操作中。使用一个索引来检索给定范围的行
- index:扫描全表索引,通常比ALl快一些
- All:全表扫描,意味着MySQL需要从头到尾去查找所有行。这种情况下需要增加索引来进行优化。
possible_keys列
这一列显示select可能会使用那些查询来查找(通常展示会用到的所有索引名称,以,连接)
key列
这一列显示MySQL实际采用哪个索引对该表的访问。
如果没有使用索引,则该列为NULL。
key_len列
这一列显示了mysql在索引里使用的字节数,通过这个值可以估算出具体使用了索引中的那些列
例如复合索引中,没有使用全部字段,只使用了部分,可以通过该列推断出具体情况
计算规则:
- 字符串
- char(n):n字节长度
- varchar(n):n字节存储字符串长度,如果是ntf-8,则长度为3n+2
- 数值类型:
- tinyint:1字节
- smallint:2字节
- int:4字节
- bigint:8字节
- 时间类型
- date:3字节
- timestamp:4字节
- datetime:8字节
ref列
这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有: const(常量),字段名等。一般是查询条件或关联条件中等号右边的值,如果是常量,那么ref列是const,非常量的话就是字段名
row列
这一列是mysql估计要读取并检测的行数,注意这个不是结果集的行数。
filtered列
5.7版本后默认出现这个列,标识存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量比例(是百分比不是具体记录数)。
Extra列
这一列是额外信息。
- Using index: 使用覆盖索引(结果集的字段是索引,即select后的字段是film_id)
- Using index condition: 查询的列不完全被索引覆盖,where条件是一个前导的范围
- Using where:使用where语句来处理结果,查询的列未被索引覆盖
- Using temporary: mysql需要创建一张临时表来处理查询。出现这种情况一般要进行优化,首先就是索引优化
参考资料:Mysql Explain详解