MySQLExplain详解

左手的ㄟ右手 2024-03-22 21:00 75阅读 0赞

## **Explain使用场景** ##

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

## Explain分析示例 ##

官方文档：[MySQL 5.7 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`输出，可以得到以下分析结论：

1.  查询使用了名为order\_status的非唯一索引，可以提高查询性能。
2.  查询使用了WHERE条件进行过滤，过滤条件为order\_status字段等于'SHIPPED'和order\_date字段大于等于'2023-01-01'。
3.  查询可能返回约1000行结果。
4.  在查询中的每个表会输出一行，如果有两个表通过 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中的列 ###

![7a3c3b5e520d40b4b0d6c54d36e4816d.png][]

#### **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**：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

[MySQL 5.7 Explain]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
[7a3c3b5e520d40b4b0d6c54d36e4816d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/f7e447305921437490f2c1c9bdb1e771.png