理解 MySQL 8.0 `EXPLAIN ANALYZE` 的输出与实际执行对比

各位观众老爷们，掌声在哪里？今天咱们聊聊 MySQL 8.0 的“照妖镜”：EXPLAIN ANALYZE！

咳咳，各位程序员界的大侠们，今天小弟在这里斗胆开讲，给大家扒一扒 MySQL 8.0 里面一个神器，它能帮咱们看穿 SQL 的“内心”，直击性能瓶颈。没错，说的就是 EXPLAIN ANALYZE！

想象一下，你写了一段自认为天衣无缝的 SQL，信心满满地丢给 MySQL 执行。结果呢？慢！慢！慢！慢到怀疑人生，怀疑自己是不是进错了行。这时候，你抓耳挠腮，冥思苦想，却怎么也找不到问题所在。

别慌！有了 EXPLAIN ANALYZE，一切问题都将迎刃而解！它就像一个经验丰富的“老中医”，不仅能告诉你 SQL 计划怎么走，还能告诉你每一步实际花了多少时间、用了多少资源。有了它，SQL 性能优化不再是玄学，而是一场有理有据的“手术”！

今天，咱们就来好好研究一下这个“照妖镜”，看看它到底能照出些什么“妖魔鬼怪”，以及如何利用它来提升 SQL 的战斗力。

一、EXPLAIN：SQL 的“体检报告”

在深入 EXPLAIN ANALYZE 之前，咱们先来回顾一下它的“老大哥”：EXPLAIN。 EXPLAIN 可以说是 SQL 优化的入门级工具，它能告诉你 MySQL 优化器是如何执行你的 SQL 语句的。

运行 EXPLAIN SELECT ... 之后，你会得到一张表格，里面包含了各种各样的信息，例如：

id: 查询的序列号，数字越大优先级越高。
select_type: 查询的类型，例如 SIMPLE (简单查询), PRIMARY (最外层查询), SUBQUERY (子查询) 等。
table: 查询涉及的表。
partitions: 如果表是分区表，会显示使用的分区。
type: 访问类型，这是个非常重要的指标，它决定了 MySQL 如何查找表中的数据。常见的类型有：
- system: 表只有一行记录，属于理想情况。
- const: 通过索引一次就能找到记录，通常发生在主键或唯一索引的等值查询中。
- eq_ref: 也是通过唯一索引查找，但用于连接时，保证每个索引键值只有一个匹配的行。
- ref: 通过非唯一索引查找，可能返回多行记录。
- range: 使用索引范围扫描，例如 WHERE id > 10。
- index: 扫描整个索引树，通常比全表扫描快，因为索引文件通常比数据文件小。
- ALL: 全表扫描，这是最慢的访问类型，应尽量避免。
possible_keys: MySQL 可能使用的索引。
key: MySQL 实际使用的索引。
key_len: 使用的索引的长度。
ref: 使用哪个列或常量与索引进行比较。
rows: MySQL 估计需要扫描的行数。
filtered: 返回的行数占扫描行数的百分比。
Extra: 包含一些额外的信息，例如 Using index (使用覆盖索引), Using where (需要通过 WHERE 条件过滤), Using temporary (使用临时表), Using filesort (需要文件排序) 等。

EXPLAIN 就像一份“体检报告”，告诉你 SQL 的大致情况，但它只是一个“理论分析”，并不能告诉你实际执行时的具体情况。就像体检报告告诉你可能患有某种疾病，但并不能告诉你疾病的严重程度。

二、EXPLAIN ANALYZE：SQL 的“手术直播”

现在，咱们的主角 EXPLAIN ANALYZE 闪亮登场！它不仅会告诉你 SQL 计划，还会告诉你每个步骤实际执行的时间、返回的行数等信息。就像一场“手术直播”，让你亲眼看到 SQL 的每一步操作，以及遇到的问题。

使用方法很简单：

EXPLAIN ANALYZE SELECT ...;

执行后，你会得到一份更加详细的报告，里面包含了 SQL 计划的树状结构，以及每个节点的执行统计信息。这些信息包括：

Execution Time: 节点的执行时间，包括第一次执行的时间、总执行时间等。
Rows Produced: 节点产生的行数。
Rows Examined: 节点检查的行数。
Loops: 节点执行的次数。
Memory: 节点使用的内存。

这些信息都是实实在在的执行数据，能够帮助你更准确地判断 SQL 的性能瓶颈。

三、EXPLAIN ANALYZE 实战演练：揪出“性能恶魔”

为了让大家更好地理解 EXPLAIN ANALYZE 的作用，咱们来做一个实战演练。假设我们有一个 users 表和一个 orders 表，结构如下：

users 表:

列名	数据类型	说明
id	INT	主键，自增
name	VARCHAR(255)	用户名
email	VARCHAR(255)	邮箱
created_at	TIMESTAMP	创建时间

orders 表:

列名	数据类型	说明
id	INT	主键，自增
user_id	INT	用户 ID
amount	DECIMAL(10,2)	订单金额
created_at	TIMESTAMP	创建时间

现在，我们要查询所有在 2023 年 1 月 1 日之后创建的订单，并显示用户名和订单金额。 SQL 语句如下：

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2023-01-01';

我们先来用 EXPLAIN 看一下：

EXPLAIN SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2023-01-01';

输出结果（简化版）：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	o	range	created_at	created_at	5	NULL	1000	Using index condition; Using where
1	SIMPLE	u	eq_ref	PRIMARY	PRIMARY	4	o.user_id	1	NULL

从 EXPLAIN 的结果来看，orders 表使用了 created_at 索引进行范围扫描，users 表使用了主键索引进行等值查找。看起来还不错，但实际执行速度却很慢。

接下来，我们用 EXPLAIN ANALYZE 看看：

EXPLAIN ANALYZE SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2023-01-01';

输出结果（简化版）：

-> Filter: (o.created_at > '2023-01-01')  (cost=1.11 rows=1000) (actual time=0.025..1.234 rows=1000 loops=1)
    -> Index range scan on orders o  (cost=1.11 rows=1000) (actual time=0.011..0.876 rows=1000 loops=1) Key: created_at, Key_length: 5, Used_key_parts=1
-> Nested loop inner join  (cost=1101.11 rows=1000) (actual time=0.036..2.567 rows=1000 loops=1)
    -> Table scan on users u  (cost=1000.00 rows=1000) (actual time=0.004..0.123 rows=1000 loops=1)
        -> Filter: (u.id = o.user_id)  (cost=0.10 rows=1000) (actual time=0.031..1.333 rows=1000 loops=1)

仔细观察 EXPLAIN ANALYZE 的输出，我们发现了问题：

users 表进行了全表扫描！ 虽然 users.id 上有主键索引，但 MySQL 并没有使用，而是进行了全表扫描，然后通过 Filter 进行过滤。这说明 MySQL 优化器认为全表扫描比使用索引更划算。 🤡

为什么会这样呢？原因可能有很多，例如：

数据分布不均匀： orders 表中 user_id 的分布可能非常集中，导致 MySQL 认为即使使用索引，也需要扫描大量的 users 表记录。
统计信息不准确： MySQL 的统计信息可能不准确，导致优化器做出了错误的判断。

找到了问题，咱们就可以对症下药了。例如，我们可以尝试以下方法：

强制使用索引： 使用 FORCE INDEX 提示 MySQL 强制使用 users 表的主键索引。

SELECT u.name, o.amount
FROM users u FORCE INDEX (PRIMARY)
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2023-01-01';

更新统计信息： 使用 ANALYZE TABLE users; 更新 users 表的统计信息。
优化数据模型： 如果 user_id 的分布非常集中，可以考虑修改数据模型，例如将订单信息冗余到 users 表中。

通过 EXPLAIN ANALYZE，我们成功揪出了“性能恶魔”，并找到了相应的解决方案。是不是很神奇？

四、EXPLAIN ANALYZE 的注意事项

虽然 EXPLAIN ANALYZE 是一个强大的工具，但使用时也需要注意以下几点：

会实际执行 SQL： EXPLAIN ANALYZE 会实际执行 SQL 语句，因此在生产环境中要谨慎使用，避免对数据库造成不必要的压力。
结果可能不稳定： EXPLAIN ANALYZE 的结果可能会受到数据库负载、数据分布等因素的影响，因此需要多次执行，并取平均值进行分析。
输出结果比较复杂： EXPLAIN ANALYZE 的输出结果比较复杂，需要一定的 SQL 优化经验才能理解。

五、总结：EXPLAIN ANALYZE，你值得拥有！

总而言之，EXPLAIN ANALYZE 是 MySQL 8.0 中一个非常强大的性能诊断工具。它不仅能告诉你 SQL 计划，还能告诉你每个步骤实际执行的时间、返回的行数等信息。有了它，你可以更准确地判断 SQL 的性能瓶颈，并找到相应的解决方案。

下次你的 SQL 跑得慢如蜗牛时，别忘了祭出 EXPLAIN ANALYZE 这个“照妖镜”，让那些隐藏在 SQL 背后的“性能恶魔”无所遁形！ 😈

希望今天的分享对大家有所帮助。如果大家觉得有用，请点赞、评论、转发，让更多的人了解 EXPLAIN ANALYZE 的魅力！

感谢各位的观看！咱们下期再见！ 💖

各位观众老爷们，掌声在哪里？今天咱们聊聊 MySQL 8.0 的“照妖镜”：EXPLAIN ANALYZE！

发表回复 取消回复

发表回复取消回复