如何分析MySQL语句的性能(MySQL Explain详解)

信天牧

已于 2022-10-31 19:10:26 修改

阅读量844

点赞数

分类专栏： mysql 文章标签： mysql

于 2022-10-31 17:27:40 首次发布

本文链接：https://blog.csdn.net/wuguandi/article/details/127578613

版权

mysql 专栏收录该内容

5 篇文章

订阅专栏

在这里插入图片描述

在实际工作中，我们有两种情况需要分析SQL的性能：(1)写代码时，比如订单模块，提前知道这表的数据将会比较多，这时候需要先把索引建好，再写代码，再造假数据，然后测试，然后不断分析SQL的性能进行调试；(2)慢查询日志里出现慢查询的时候，需要把慢查询的SQL语句拿去性能分析进行调试。

如果有兴趣了解索引的原理、索引的底层数据结构，请看我另外一篇文章：一文掌握MySQL的索引 – 数据结构(认真排版、简洁易懂)

先来个小菜，最简单的分析。

-- 查询某商户下的某个商品。直接在SQL语句前加 Explain 关键字
explain
select * from t_order where merchant_id = 3 and product_id = 1003;

在这里插入图片描述
在SQL前加 Explain 关键字，点查询，出来这样的性能分析结果
上图，key 字段的值如果不是 all，并且 rows 字段值很小，说明你的SQL语句性能很好。反之，性能很差。

详解

Explain 出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

字段意义：
id：执行顺序
select_type：表示查询的类型。
table：输出结果集的表
partitions：匹配的分区
type：表示表的连接类型
possible_keys：表示查询时，可能使用的索引
key：表示实际使用的索引
key_len：索引字段的长度
ref：列与索引的比较
rows：扫描出的行数(估算的行数)
filtered：按表条件过滤的行百分比
Extra：执行情况的描述和说明

下面对这些字段进行解释：

一、 id

SQL子句、表的执行顺序。搭配table列看。

id相同时：执行顺序由上至下
id不同时：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id有相同也有不同：从最大的开始执行，相同的部分从上往下执行。

总结：id的值表示select子句或表的执行顺序，id相同，执行顺序从上到下，id不同，值越大的执行优先级越高。

二、select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。

SIMPLE 简单SELECT，不使用UNION或子查询等。例如:SELECT * from member where age>50;
PRIMARY 包含union或者子查询，最外层的部分标记为primary。例如:SELECT member_id from t_member where member_id = 3 UNION all SELECT member_id from t_member
UNION UNION中的第二个或后面的SELECT语句。例如:SELECT member_id from t_member where member_id = 3 UNION all SELECT member_id from t_member
DEPENDENT UNION UNION中的第二个或后面的查询，依赖了外面的查询。例如:SELECT tm.* from t_member as tm where member_id in (SELECT member_id from t_member where member_id = 3 UNION all SELECT member_id from t_member)
UNION RESULT UNION的结果，union语句中第二个select开始后面所有select
SUBQUERY 子查询中的第一个SELECT，结果不依赖于外部查询。例如:SELECT * from t_member where member_id = (SELECT member_id from t_member where member_id = 5)
DEPENDENT SUBQUERY 子查询中的第一个SELECT，依赖于外部查询。例如:SELECT tm.* from t_member as tm where member_id in (SELECT member_id from t_member where member_id = 3 UNION all SELECT member_id from t_member)
DERIVED 派生表的SELECT, FROM子句的子查询。例如:SELECT * from (SELECT * from t_member where member_id = 1) tbl
UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存，必须重新评估外链接的第一行

三、table

表示当前这一行正在访问哪张表，如果SQL定义了别名，则展示表的别名

四、type

对表的访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL: 最坏的情况,全表扫描
index: 全索引扫描. index与all区别为index只遍历索引树, 通常比all快, 因为索引文件通常比数据文件小.
range: 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
ref: 非唯一性索引扫描, 返回匹配某个单独值的所有行. 本质上也是一种索引访问, 返回匹配某值(某条件)的多行数据,属于查找和扫描的混合体.
eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
const: 针对主键或唯一索引的等值查询扫描, 通过索引一次就找到了. const 查询速度非常快, 因为它仅仅读取一次即可
system：该表只有一行（相当于系统表），system是const类型的特例
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

五、possible_keys

可能会使用的索引

六、Key

实际使用的索引，包含在possible_keys中。如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

七、key_len

索引长度，索引字节数。也就是索引列的字段长度。不损失精确性的情况下，长度越短越好

八、ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

九、rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需记录所需要读取的行数。

十、Extra

该列包含MySQL解决查询的额外信息,有以下一些情况：

Using filesort（九死一生）: 当查询中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”。
Using temporary（十死无生）: 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using index（发财了）: 表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
Using where: 如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现using where信息。
Using join buffer：此值表示在 join 查询时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况去添加索引来改进能。
Impossible where：WHERE子句始终为false，不会命中任何行。
Select tables optimized away: 一般用在某些聚合函数访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询时展示
No tables used: 当此查询没有FROM子句或拥有FROM DUAL子句时出现。例如：explain select 1、explain select now() from dual;

总结：

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 统计信息是估算的，并非精确值
• EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。