21.进阶-索引-性能分析-explain
前面已经讲解了三种sql语句性能分析工具,分别用来查看sql语句的执行频次,查看慢查询日志,以及通过show profiles查看sql语句的好是以及时间耗费在哪里了,这些都是通过时间的层面来评判一条sql语句的性能,执行时间段,那就说明sql语句的性能高,执行时间长,就说明sql语句的性能低。实际上这种判定只是粗略的进行判定,并不能真正的评判一条sql语句的性能。我们要想去看一条sql语句的性能还需要借助于第四种手段explain来查看sql语句的执行计划。
explain在我们sql优化中占据着非常重要的地位,我们经常会通过他来判定sql语句的性能。通过explain可以查看到sql语句的执行计划,它的执行过程当中,到底是否用到了索引,表的连接情况,表的连接顺序都可以看到。
(1)explain
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行 过程中表如何连接和连接的顺序。
语法:
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
(2)Explain 执行计划中各个字段的含义:
字段 | 含义 |
---|---|
id | select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)。 |
select_type | 表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接 或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等 |
type | 表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。 |
possible_key | 显示可能应用在这张表上的索引,一个或多个。 |
key | 实际使用的索引,如果为NULL,则没有使用索引。 |
key_len | 表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长 度,在不损失精确性的前提下, 长度越短越好 。 |
rows | MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值, 可能并不总是准确的。 |
filtered | 表示返回结果的行数占需读取行数的百分比, filtered 的值越大越好。 |
1、id
explain中的id指代的是sql语句当中表的执行顺序。对于这个表的执行顺序,如果id相同,执行顺序是从上到下;如果id不相同,值越大,越先执行。看到这个解释可能有点蒙圈了?这不就一行记录吗?
注意!对于单表查询来说,这一块可能是一行记录。如果是多表查询,select语句对应的执行计划他可能是多条记录。
id值相同的情况:
一共有三条记录,重点介绍id字段。id字段三个都是1,会发现这个id并不是自增的,即使有多个记录,id有相同的,也有不同的。如果id相同,则代表表结构的执行顺序是从上到下的,也就是先执行的是s,再执行sc,再执行c。为什么这样执行呢?因为在这种多表关联关系中,student和course之间有直接关系吗?没有。他们之间产生关系是通过中间表,所以先执行student,再执行student_course,再执行course。
id值不同的情况:查询选修了mysql的学生(通过子查询实现)
可以看到id值有相同的也有不同的。如果id值不同,值越大执行的优先级越高,也就是c先执行,也就是先执行的内层的sql;再执行2的sc;3和2执行完成之后,剩下的两个都是1,所以从上往下执行,所以先执行subquery2,这个代表的是第二个子查询。这个子查询执行完毕之后最后再执行student表。
2、select_type
表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接 或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
3、type
表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。我们在优化的时候,尽量往前面优化。对于我们业务系统当中的sql来说,一般不太可能优化为null。什么时候会出现null呢?当你查询的时候不访问任何表,此时才会出现null,比如:
system在这一块相当于访问系统表。
根据主键还有唯一索引访问一般会出现const。
ref指的是如果我们用非唯一性的索引进行查询就会出现ref,比如:最开始在tb_user表里面创建了几个索引,这个phone是个唯一索引,而name是一个非唯一索引。如果我们使用唯一索引进行查询,那么他会出现const。
如果出现all,代表的是全表扫描。
如果出现index,代表的是用了索引,但是他也会对索引进行扫描,遍历整个索引树,虽然比all快,但是性能也不是特别高。
4、possible_key
在这张表中可能用到的索引,如果有多个,会展示可能用到的多个索引
5、key
实际用到的索引,如果用到了,他会展示实际用到的哪个索引。如果没有用到,会展示null
6、key_len
使用到的索引的字节数,这个值是根据索引字段的最大可能长度来决定的,并非实际的使用值。在不损失精度的情况下,长度越短越好。
可以看到我们根据name字段查询,可能用到的索引就是name字段的索引(我们前面创建的idx_user_name),实际用到的就是name索引。索引长度为202。key_len和字段里面存储的值有关系。
7、ref
预估值,并不是100准确
8、filtered
查询返回的行数占总读取行数的百分比,如果为100%,性能是最高的,也就是这个filter值越大越好。比如根据主键去查询,我们就只读取了一行数据,而返回也值返回了一行数据,那么他的filtered就是100%,这个性能是最好的。
9、Extra
额外的信息。也就是说在执行查询的过程中,前面几个字段没有展示出来的值,将会在这个里面展示出来。
数据,那么他的filtered就是100%,这个性能是最好的。
9、Extra
额外的信息。也就是说在执行查询的过程中,前面几个字段没有展示出来的值,将会在这个里面展示出来。