MySql
中explain
的使用
explain详解
用一条简单的sql
看看使用explain
关键字的效果:
explain select * from test1;
执行结果:
从上图中看到执行结果中会显示12列信息,每列具体信息如下:
id列
该列的值是select查询中的序号,比如:1、2、3、4等,它决定了表的执行顺序。
某条sql
的执行计划中一般会出现三种情况:
-
id相同
-
id不同
-
id相同和不同都有
那么这三种情况表的执行顺序是怎么样的呢?
- id相同
执行sql
如下:
explain select * from test1 t1 inner join test1 t2 on t1.id=t2.id
结果:
我们看到执行结果中的两条数据id都是1,是相同的。
这种情况表的执行顺序是怎么样的呢?
答案:从上到下执行,先执行表t1
,再执行表t2
。
- id不同
执行sql
如下:
explain select * from test1 t1 where t1.id = (select id from test1 t2 where t2.id=2);
结果:
我们看到执行结果中两条数据的id不同,第一条数据是1,第二条数据是2。
这种情况表的执行顺序是怎么样的呢?
答案:序号大的先执行,这里会从下到上执行,先执行表t2
,再执行表t1
。
- id相同和不同都有
执行sql
如下:
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid
答案:先执行序号大的,先从下而上执行。遇到序号相同时,再从上而下执行。所以这个列子中表的顺序顺序是:test1、t1
、
select_type列
该列表示select的类型。具体包含了如下11种类型:
但是常用的其实就是下面几个:
)
下面看看这些SELECT类型具体是怎么出现的:
-
SIMPLE
执行
sql
如下:explain select * from test1;
结果:
它只在简单SELECT查询中出现,不包含子查询和UNION,这种类型比较直观就不多说了。
-
PRIMARY 和
SUBQUERY
执行
sql
如下:explain select * from test1 t1 where t1.id = (select id from test1 t2 where t2.id=2);
结果:
我们看到这条嵌套查询的
sql
中,最外层的t1
表是PRIMARY类型,而最里面的子查询t2
表是SUBQUERY
类型。 -
DERIVED
执行
sq
l如下:select t1.* from test1 t1 inner join (select max(id) mid from test1 group by id) t2 on t1.id=t2.mid
结果:
最后一条记录就是衍生表,它一般是FROM列表中包含的子查询,这里是
sql
中的分组子查询。 -
UNION 和 UNION RESULT
explain select * from test1 union select * from test2
结果:
test2
表是UNION关键字之后的查询,所以被标记为UNION,test1
是最主要的表,被标记为PRIMARY。而<union1,2>
表示id=1和id=2的表union,其结果被标记为UNION RESULT。
UNION 和 UNION RESULT一般会成对出现。
id列的值允许为空吗?
如果仔细看上面那张图,会发现id列是可以允许为空的,并且是在SELECT类型为: UNION RESULT的时候。
table列
该列的值表示输出行所引用的表的名称,比如前面的:test1、test2
等。
但也可以是以下值之一:
-
<unionM,N>
:具有和id值的行的M并集N。 -
<derivedN>
:用于与该行的派生表结果id的值N。派生表可能来自(例如)FROM子句中的子查询 。 -
<subqueryN>
:子查询的结果,其id值为N
partitions列
该列的值表示查询将从中匹配记录的分区
type列
该列的值表示连接类型,是查看索引执行情况的一个重要指标。包含如下类型:
执行结果从最好到最坏的的顺序是从上到下。
我们需要重点掌握的是下面几种类型:
system > const > eq_ref > ref > range > index > ALL
在演示之前,先说明一下test2
表中只有一条数据:
并且code字段上面建了一个普通索引:
下面逐一看看常见的几个连接类型是怎么出现的:
-
system
这种类型要求数据库表中只有一条数据,是
const
类型的一个特例,一般情况下是不会出现的。 -
const
通过一次索引就能找到数据,一般用于主键或唯一索引作为条件与常数比较的查询
sql
中,执行sql
如下:explain select * from test2 where id=1;
结果:
eq_ref
常用于主键或唯一索引扫描,简单来说就是多表连接中使用主键或者唯一索引作为关联条件。执行
sql
如下:explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id;
结果:
ref
常用于非主键和唯一索引扫描。执行
sql
如下:EXPLAIN SELECT * FROM demo WHERE age = 18;
结果:
-
range
常用于范围查询,比如:between ... and 或 In 等操作,执行
sql
如下:(最起码优化到这个级别)explain select * from test2 where id between 1 and 2;
结果:
-
index
全索引扫描。执行
sql
如下:EXPLAIN SELECT id FROM demo;
结果:
-
ALL全表扫描。执行
sql
如下:explain select * from test2;
结果:
-
possible_keys列
该列表示可能的索引选择。
请注意,此列完全独立于表的顺序,这就意味着possible_keys在实践中,某些键可能无法与生成的表顺序一起使用。
如果此列是NULL,则没有相关的索引。在这种情况下,您可以通过检查该WHERE 子句以检查它是否引用了某些适合索引的列,从而提高查询性能。
key列
该列表示实际用到的索引。
可能会出现possible_keys列为NULL,但是key不为NULL的情况。
演示之前,先看看
test1
表结构:test1
表中数据:使用的索引:
code和name字段使用了联合索引。
执行
sql
如下:explain select code from test1;
结果:
这条
sql
预计没有使用索引,但是实际上使用了全索引扫描方式的索引。key_len
列该列表示使用索引的长度。上面的key列可以看出有没有使用索引,key_len列则可以更进一步看出索引使用是否充分。不出意外的话,它是最重要的列。
有个关键的问题浮出水面:
key_len
是如何计算的?决定
key_len
值的三个因素:1.字符集
2.长度
3.是否为空
常用的字符编码占用字节数量如下:
目前我的数据库字符编码格式用的:
UTF8
占3个字节。mysql
常用字段占用字节数:此外,如果字段类型允许为空则加1个字节。
上图中的 184是怎么算的?
184 = 30 * 3 + 2 + 30 * 3 + 2
再把
test1
表的code字段类型改成char,并且改成允许为空:执行
sql
如下:explain select code from test1;
结果:
怎么算的?
183 = 30 * 3 + 1 + 30 * 3 + 2
还有一个问题:为什么这列表示索引使用是否充分呢,还有使用不充分的情况?
执行
sql
如下:explain select code from test1 where code='001';
结果:
上图中使用了联合索引:
idx_code_name
,如果索引全匹配key_len
应该是183,但实际上却是92,这就说明没有使用所有的索引,索引使用不充分。ref列
该列表示索引命中的列或者常量。
执行
sql
如下:explain select * from test1 t1 inner join test1 t2 on t1.id=t2.id where t1.code='001';
结果:
我们看到表
t1
命中的索引是const
(常量),而t2
命中的索引是列sue库的t1
表的id字段。rows列
该列表示
MySQL
认为执行查询必须检查的行数。对于
InnoDB
表,此数字是估计值,可能并不总是准确的。filtered列
该列表示按表条件过滤的表行的估计百分比。最大值为100,这表示未过滤行。值从100减小表示过滤量增加。
rows显示了检查的估计行数,rows× filtered显示了与下表连接的行数。例如,如果 rows为1000且 filtered为50.00(50%),则与下表连接的行数为1000×50%= 500。
Extra列
Impossible WHERE
表示WHERE后面的条件一直都是false,
执行
sql
如下:explain select code from test1 where 'a' = 'b';
结果:
Using filesort
表示按文件排序,一般是在指定的排序和索引排序不一致的情况才会出现。
执行
sql
如下:explain select code from test1 order by name desc;
结果:
这里建立的是code和name的联合索引,顺序是code在前,name在后,这里直接按name降序,跟之前联合索引的顺序不一样。
Using index
表示是否用了覆盖索引,说白了它表示是否所有获取的列都走了索引。
上面那个例子中其实就用到了:Using index,因为只返回一列code,它字段走了索引。
Using temporary
表示是否使用了临时表,一般多见于order by 和 group by语句。
执行
sql
如下:explain select name from test1 group by name;
结果:
Using where
表示使用了where条件过滤。Using join buffer
表示是否使用连接缓冲。来自较早联接的表被部分读取到联接缓冲区中,然后从缓冲区中使用它们的行来与当前表执行联接。索引优化的过程
1.先用慢查询日志定位具体需要优化的
sql
2.使用explain执行计划查看索引使用情况
3.重点关注:
key(查看有没有使用索引) key_len(查看索引使用是否充分) type(查看索引类型) Extra(查看附加信息:排序、临时表、where条件为false等)
评论区