如何分析一条sql的性能

云辰君_9206

云辰君_9206

2019-06-18

7403人浏览

原创

这篇文章将给大家介绍如何使用 explain 来分析一条 sql 。

网上其实已经有非常多的文章都很详细的介绍了 explain 的使用,这篇文章将实例和原理结合起来,尽量让你有更好的理解,相信我,认真看完你应该会有特别的收获。

explain 翻译过来就是解释的意思, 在 mysql 里被称作执行计划,即可以通过该命令看出 mysql 在经过优化器分析后决定要如何执行该条 sql 。

说到优化器,再多说一句,mysql 内置了一个强大的优化器,优化器的主要任务就是把你写的 sql 再给优化一下,尽可能以更低成本去执行,比如扫描更少的行数,避免排序等。执行一条sql语句都经历了什么? 我在前面的文章中有介绍过优化器相关的。

你可能会问,一般在什么时候会要用 explain 呢,大多数情况下都是从 mysql 的慢查询日志中揪出来一些查询效率比较慢的 sql 来使用 explain 分析,也有的是就是在对 mysql 进行优化的时候,比如添加索引,通过 explain 来分析添加的索引能否被命中,还有的就是在业务开发的时候,在满足需求的情况下,你可能需要通过 explain 来选择一个更高效的 sql。

那么 explain 该怎么用呢,很简单,直接在 sql 前面加上 explain 就行了,如下所示。

mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 100332 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)

可以看到,explain 会返回约 10 个字段,不同版本返回的字段有些许差异,每个字段都代表着具体的意义,这篇文章我不打算把每个字段都详细的介绍一遍,东西比较多,怕你也不容易记住,不如先把几个重要的字段好好理解了。

其中 type、key、rows、Extra 这几个字段我认为是比较重要的,我们接下来通过具体的实例来帮你更好的理解这几个字段的含义。

首先有必要简单介绍下这几个字段的字面意思。

type 表示 mysql 访问数据的方式,常见的有全表扫描(all)、遍历索引(index)、区间查询(range)、常量或等值查询(ref、eq_ref)、主键等值查询(const)、当表中只有一条记录时(system)。下面是效率从最好到最差的一个排序。

system > const > eq_ref > ref > range > index > all

key 表示查询过程实际会用到的索引名称。

rows 表示查询过程中可能需要扫描的行数,这个数据不一定准确,是mysql 抽样统计的一个数据。

Extra 表示一些额外的信息,通常会显示是否使用了索引,是否需要排序,是否会用到临时表等。

好了,接下来正式开始实例分析。

还是沿用前面文章中创建的存储引擎创建一个测试表,我们这里插入 10 w 条测试数据,表结构如下:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

然后看下面这条查询语句,注意这个表目前只有一个主键索引,还没有创建普通索引。

DBeaver Community 26.2.1
DBeaver Community 26.2.1

DBeaver Community 26.2.1 Windows x64 官方安装包,适用于数据库连接、SQL 开发、数据浏览和管理。

下载
mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

其中 type 值为 ALL,表示全表扫描了,大家注意看到 rows 这个字段显示有 100332 条,实际上我们一共才 10w 条数据,所以这个字段只是 mysql 的一个预估,并不一定准确。这种全表扫描的效率非常低,是需要重点被优化的。

接下来我们分别给字段 a 和 b 添加普通索引,然后再看下添加索引后的几条 sql 。

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> explain select * from t where a > 1000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | ALL  | a_index       | NULL | NULL    | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

上面这条 sql 看起来是不是有点疑惑呢,type 竟然显示刚刚不是给字段 a 添加索引了么,而且 possible_keys 也显示了有 a_index 可用,但是 key 显示 null,表示 mysql 实际上并不会使用 a 索引,这是为啥?

这里是因为 select * 的话还需要回到主键索引上查找 b 字段,这个过程叫回表,这条语句会筛选出 9w 条满足条件的数据,也就是说这 9w 条数据都需要回表操作,全表扫描都才 10w 条数据,所以在 mysql 的优化器看来还不如直接全表扫描得了,至少还免去了回表过程了。

当然也不是说只要有回表操作就不会命中索引,用不用索引关键还在于 mysql 认为哪种查询代价更低,我们把上面的 sql 中 where 条件再稍微改造一下。

mysql> explain select * from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

这回 type 值为 range 了,key 为 a_index ,表示命中了 a 索引,是一个不错的选择,是因为满足这条 sql 条件的只有 1000 条数据,mysql 认为 1000 条数据就算回表也要比全表扫描的代价低,所以说 mysql 其实是个很聪明的家伙。

我们还可以看到 Extra 字段中值为 Using index condition,这个意思是指用到了索引,但是需要回表,再看下面这个语句。

mysql> explain select a from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

这个 Extra 中的值为 Using where; Using index ,表示查询用到了索引,且要查询的字段在索引中就能拿到,不需要回表,显然这种效率比上面的要高,所以不要轻易写 select * ,只查询业务需要的字段即可,这样可以尽可能避免回表。

再来看一个需要排序的。

mysql> explain select a from t where a > 99000 order by b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

这个 Extra 中返回了一个 Using filesort,意味着需要排序,这种是需要重点优化的的,也就是说查到数据后,还需要 mysql 在内存中对其进行排序,你要知道索引本身就是有序的,所以一般来讲要尽量利用索引的有序性,比如像下面这样写。

mysql> explain select a from t where a > 99990 order by a;
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys    | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | a_index | 5       | NULL |   10 | Using where; Using index |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

我们再创建一个复合索引看看。

mysql> alter table t add index ab_index(a,b);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from t where a > 1000;
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys    | key      | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | ab_index | 5       | NULL | 50166 | Using where; Using index |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

这条 sql 刚刚在上面也有讲到过,在没有创建复合索引的时候,是走的全表扫描,现在其实是利用了覆盖索引,同样是免去了回表过程,即在 (ab_index) 索引上就能找出要查询的字段。

这篇文章通过几个实例介绍了如何使用 explain 分析一条 sql 的执行计划,也提到了一些常见的索引优化,事实上还有更多的可能性,你也可以自己去写一个 sql ,然后使用 explain 分析,看看有哪些是可以被优化的。

这篇文章我断断续续写了有三四天了,本来准备了更多的例子,但每次都是写了一部分,思路也打乱了,好了,有问题欢迎在下面留言交流,文章对你有帮助,点个赞表示鼓励支持。

更多MySQL相关技术文章,请访问MySQL教程栏目进行学习!

相关文章

数码产品性能查询
数码产品性能查询

该软件包括了市面上所有手机CPU,手机跑分情况,电脑CPU,电脑产品信息等等,方便需要大家查阅数码产品最新情况,了解产品特性,能够进行对比选择最具性价比的商品。

下载

相关标签:

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

60

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

60

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

60

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

40

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

140

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

160

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Navicat Monitor 3 用户手册
Navicat Monitor 3 用户手册

共0课时 | 0人学习

SQLAlchemy 2.1 官方统一教程
SQLAlchemy 2.1 官方统一教程

共0课时 | 0人学习

DBeaver生态工具dbvr官方手册
DBeaver生态工具dbvr官方手册

共0课时 | 0人学习