MySQLExplain详解

Ascend1797

已于 2023-07-26 14:33:39 修改

阅读量190

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

于 2023-07-26 10:15:48 首次发布

本文链接：https://blog.csdn.net/Ascend1977/article/details/131932299

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Explain使用场景

查询性能优化：EXPLAIN可以帮助开发者分析查询语句的执行计划，判断是否有效地使用了索引、是否有可能导致全表扫描等性能问题。通过EXPLAIN的输出，可以找到潜在的性能瓶颈，并优化查询语句、创建合适的索引或调整表结构，以提高查询性能和效率。
确认索引使用情况：使用EXPLAIN可以确认MySQL是否使用了预期的索引来执行查询。如果没有使用预期的索引，可能需要重新优化查询语句或调整索引的定义。
查看查询执行顺序：EXPLAIN可以显示查询的执行顺序，包括子查询、连接操作等。通过查看执行顺序，可以了解查询的复杂度，从而判断是否需要优化查询或拆分查询成多个简单查询。
查询执行效率比较：通过EXPLAIN可以比较不同查询语句的执行计划，找到最优的查询方案，以提高查询效率。
监控查询性能：结合MySQL的慢查询日志，可以使用EXPLAIN来监控和分析慢查询，找出慢查询的原因，进一步优化和改进查询性能。
了解索引使用情况：EXPLAIN可以展示查询的可能索引和实际使用的索引，通过比较可以了解索引是否有效，是否需要重新设计索引或增加新的索引。

Explain分析示例

官方文档：MySQL 5.7 Explain参考手册

假设有以下查询语句：

EXPLAIN SELECT * FROM order WHERE order_status = 'SHIPPED' AND order_date >= '2023-01-01';

通过执行EXPLAIN命令来分析上述查询语句的执行计划，可以得到类似以下的输出：

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | order | ref  | order_status  | order_status | 4     |      | 1000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

接下来解释上述输出中的各个字段：

id: 查询的标识符，每个查询都有一个唯一的ID，对于简单查询，通常为1。
select_type: 查询的类型，这里是SIMPLE，表示简单查询。
table: 表名，表示查询涉及的表，这里是orders表。
type: 访问类型，表示MySQL如何访问表，这里是ref，表示使用非唯一索引进行等值查询。
possible_keys: 表示可能使用的索引，这里是order_status，表示可能使用order_status字段上的索引。
key: 实际使用的索引，这里是order_status，表示实际使用了order_status字段上的索引。
key_len: 表示索引字段的长度，这里是4，表示order_status字段的长度为4个字节。
ref: 表示与索引一起使用的字段或常量，这里为空，表示没有与索引一起使用的字段。
rows: 估计查询返回的行数，这里是1000，表示该查询可能返回1000行结果。
Extra: 附加信息，这里是Using where，表示使用了WHERE条件进行过滤。

通过上述EXPLAIN输出，可以得到以下分析结论：

查询使用了名为order_status的非唯一索引，可以提高查询性能。
查询使用了WHERE条件进行过滤，过滤条件为order_status字段等于'SHIPPED'和order_date字段大于等于'2023-01-01'。
查询可能返回约1000行结果。
在查询中的每个表会输出一行，如果有两个表通过 join 连接查询，那么会输出两行。

Explain字段含义

示例表结构

monitor_main表结构：


CREATE TABLE `monitor_main` (
  `id` bigint(30) NOT NULL AUTO_INCREMENT,
  `index_id` bigint(30) DEFAULT NULL,
  `code` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `function` varchar(100) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `end_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `value` bigint(30) DEFAULT NULL,
  `created_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `updated_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_value` (`value`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2711 DEFAULT CHARSET=utf8mb4;

monitor_map表结构：


CREATE TABLE `monitor_map` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `main_id` bigint(20) DEFAULT NULL,
  `map` varchar(255) DEFAULT NULL,
  `mapkey` varchar(255) DEFAULT NULL,
  `mapvalue` varchar(255) DEFAULT NULL,
  `created_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `updated_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_main_id` (`main_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2914 DEFAULT CHARSET=utf8mb4;

Explain中的列

id列

id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的，id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type列

select_type表示对应行是简单还是复杂的查询。

simple：简单查询，查询不包含子查询和union。

primary：复杂查询中最外层的select。

subquery：包含在select 中的子查询（不在from子句中）。

derived：包含在from子句中的子查询，MySQL会将结果存放在一个临时表中，也称为派生表。
UNION: UNION操作。

table列

这一列表示Explain的一行正在访问哪个表，当from子句中有子查询时，table列是格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。

type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围，依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL，一般来说，得保证查询达到range级别，最好达到ref。

NULL：MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。


EXPLAIN SELECT min(monitor_main.id) FROM monitor_main;

const, system：MySQL能对查询的某部分进行优化并将其转化成一个常量。用于primary key或unique key的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条数据匹配时为system。


EXPLAIN SELECT * FROM monitor_main WHERE monitor_main.id=``2400``;

eq_ref：primary key 或unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录，这可能是在const之外最好的连接类型了，简单的select查询不会出现这type。


EXPLAIN SELECT * FROM monitor_main LEFT JOIN monitor_map on monitor_main.id=monitor_map.id;

ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行，简单select查询，monitor_main.value是普通索引（非唯一索引）。


EXPLAIN SELECT * FROM monitor_main WHERE monitor_main.value=``1400``;

range：范围扫描通常出现在in()，between ，> ，<，>=等操作中，使用一个索引来检索给定范围的行。


EXPLAIN SELECT * FROM monitor_main WHERE monitor_main.id>``2520``;

index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。


EXPLAIN SELECT monitor_main.value FROM monitor_main;

ALL：即全表扫描，扫描你的聚集索引的所有叶子节点，通常情况下这需要增加索引来进行优化了。


EXPLAIN SELECT * FROM monitor_main;

possible_keys列

这一列显示查询可能使用哪些索引来查找。Explain时可能出现possible_keys有列，而key显示NULL的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询，如果该列是NULL，则没有相关的索引，在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用Explain查看效果。

key列

这一列显示MySQL实际采用哪个索引来优化对该表的访问，如果没有使用索引，则该列是NULL，如果想强制MySQL使用possible_keys列中的索引，在查询中使用force index。

key_len列

这一列显示了MySQL在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key_len计算规则如下：

字符串：char(n)：如果存汉字长度就是3n字节；varchar(n)：如果存汉字则长度是3n+2字节，加的2字节用来存储字符串长度，因为varchar是变长字符串。

数值类型：tinyint: 1字节 smallint:2字节 int:4字节 bigint:8字节

时间类型：date: 3字节 timestamp:4字节 datetime:8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL 索引最大长
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量）。

rows列

这一列是MySQL估计要读取并检测的行数，注意这个不是结果集里的行数。

Extra列

这一列展示的是额外信息。

Using index：使用覆盖索引，MySQL执行计划Explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有Using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。

Using where：使用where语句来处理结果，并且查询的列未被索引覆盖。

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

Ascend1797

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQLExplain详解

MySQL的EXPLAIN是一个用于查看查询执行计划的命令。它可以帮助开发者分析查询语句的执行过程，了解MySQL是如何处理查询语句的，包括使用了哪些索引、执行顺序、数据访问方式等。通过执行EXPLAIN命令，可以优化查询语句，提高查询性能和效率。
复制链接

扫一扫