MySQL EXPLAIN ANALYZE获取实际执行时间与行数

EXPLAIN 是 MySQL 优化器的“蓝图”，它告诉你查询计划，但不是真实执行情况。EXPLAIN ANALYZE 才是“行车记录仪”，它记录了查询执行的真实时间和行数。通过 EXPLAIN ANALYZE，你可以精准定位查询中的性能瓶颈，而不是依赖优化器的预估。

1. 理解 EXPLAIN 与 EXPLAIN ANALYZE 的区别

在深入 EXPLAIN ANALYZE 之前，你需要先了解 EXPLAIN。EXPLAIN 命令会展示 MySQL 优化器为你的查询生成的执行计划。它告诉你数据库将如何执行这个查询，但其中的很多数据，比如 rows（预估扫描行数），都是基于统计信息的估算值。

运行 EXPLAIN 查看预估计划：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

EXPLAIN 的输出中，rows 列显示的是优化器预估需要扫描的行数。这个数字可能不准确，尤其是在统计信息过期或数据分布不均的情况下。

EXPLAIN ANALYZE 在 EXPLAIN 的基础上增加了实际执行数据。它会真正执行查询（或一个轻量级的版本），并收集每个步骤的真实执行时间和实际处理的行数。

运行 EXPLAIN ANALYZE 查看真实执行情况：

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

2. 解读 EXPLAIN ANALYZE 的输出

EXPLAIN ANALYZE 的输出比 EXPLAIN 更丰富，主要分为两个部分：Execution Plan（执行计划）和 Execution Details（执行详情）。

2.1 关键列解读

EXPLAIN ANALYZE 的输出中，以下几列是分析性能的核心：

2.2 示例分析

假设我们有一个 orders 表，包含 100 万行数据，并且 customer_id 列上没有索引。

运行 以下查询：

EXPLAIN ANALYZE SELECT order_id, amount FROM orders WHERE customer_id = 123;

你可能会看到类似以下的输出（简化版）：

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | orders  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000000|    100.00 | Using where                                        |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|    |             |         |            |      |               |      |         |      |        |          | Execution details:                                 |
|    |             |         |            |      |               |      |         |      |        |          |   - Actual Time: 0.850s (execution)                 |
|    |             |         |            |      |               |      |         |      |        |          |   - Rows Examined: 1000000                          |
|    |             |         |            |      |               |      |         |      |        |          |   - Rows Produced: 5                                |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

分析 这个输出：

1. type: ALL: 这表明 MySQL 进行了全表扫描，这是非常低效的。
2. Actual Time: 0.850s: 整个查询耗时 0.85 秒，对于 100 万行数据来说，这个时间可能过长。
3. Rows Examined: 1000000: MySQL 确实扫描了整个表（100 万行），这证实了 type: ALL 的判断。
4. Rows Produced: 5: 最终只返回了 5 行数据，说明 99.9995% 的工作都是无效的。

3. 如何利用 EXPLAIN ANALYZE 优化查询

EXPLAIN ANALYZE 的价值在于它能帮你定位问题。看到上述结果，你知道问题出在 orders 表的 customer_id 列上没有索引。

优化步骤：

1. 识别瓶颈: 从 EXPLAIN ANALYZE 输出中，找到 Actual Time 最长、Rows Examined 最大的步骤。这通常就是你的性能瓶颈。
2. 分析访问类型: 如果 type 是 ALL，或者 Extra 显示 Using filesort/Using temporary，这几乎总是需要优化的信号。
3. 应用索引: 针对瓶颈步骤，添加合适的索引。在本例中，我们为 customer_id 添加索引。

运行 添加索引的命令：

ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

4. 重新分析: 索引创建后，再次运行 EXPLAIN ANALYZE 命令，观察输出变化。

EXPLAIN ANALYZE SELECT order_id, amount FROM orders WHERE customer_id = 123;

优化后的输出可能如下：

+----+-------------+---------+------------+-------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders  | NULL       | ref   | idx_customer_id   | idx_customer_id   | 5       | const |    5 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+-------+------+----------+-------+
|    |             |         |            |       |                   |                   |         |       |      |          | Execution details:                                 |
|    |             |         |            |       |                   |                   |         |       |      |          |   - Actual Time: 0.001s (execution)                 |
|    |             |         |            |       |                   |                   |         |       |      |          |   - Rows Examined: 5                               |
|    |             |         |            |       |                   |                   |         |       |      |          |   - Rows Produced: 5                               |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+-------+------+----------+-------+

分析 优化后的输出：

1. type: ref: 访问类型从 ALL 变为 ref，说明现在使用了索引进行查找，而不是全表扫描。
2. Actual Time: 0.001s: 执行时间从 0.85 秒骤降到 0.001 秒，性能提升巨大。
3. Rows Examined: 5: 只扫描了 5 行数据，与 Rows Produced 相同，说明索引非常高效。

4. 常见性能问题与 EXPLAIN ANALYZE 的诊断

4.1 全表扫描 (type: ALL)

这是最常见的性能问题。当 type 为 ALL 时，MySQL 必须扫描表中的每一行来找到匹配的记录。

诊断: EXPLAIN ANALYZE 会显示 Rows Examined 接近或等于表的总行数，而 Actual Time 会很高。

解决方法: 在 WHERE、JOIN 或 ORDER BY 子句中使用的列上创建索引。

4.2 文件排序 (Extra: Using filesort)

当 ORDER BY 子句无法通过索引直接完成排序时，MySQL 会将需要排序的数据加载到内存或磁盘中进行排序，这就是 Using filesort。文件排序通常很慢。

诊断: EXPLAIN ANALYZE 的 Extra 列会显示 Using filesort。

解决方法:

• 确保排序的列上有索引。
• 如果 ORDER BY 的列不是主键，考虑创建一个覆盖索引（包含 SELECT、WHERE 和 ORDER BY 中所有列的索引）。

4.3 临时表 (Extra: Using temporary)

当查询需要创建一个临时表来存储中间结果时（例如，在 GROUP BY 或 DISTINCT 操作中），Extra 列会显示 Using temporary。临时表会消耗大量内存和磁盘 I/O。

诊断: EXPLAIN ANALYZE 的 Extra 列会显示 Using temporary。

解决方法:

• 确保在 GROUP BY 或 DISTINCT 的列上有索引。
• 如果可能，尝试重写查询以避免 GROUP BY 或 DISTINCT。

通过熟练使用 EXPLAIN ANALYZE，你可以从“猜测”性能问题转变为“精确测量”性能问题，从而进行有针对性的优化，显著提升数据库查询的效率。