mysql explain analyze

在 MySQL 中，EXPLAIN ANALYZE 是一个强大的工具（仅支持 MySQL 8.0.18 及以上版本），它可以结合查询的 执行计划（EXPLAIN） 和 实际执行时的性能数据（ANALYZE），帮助你深入分析 SQL 的性能瓶颈。以下是分步解读 EXPLAIN ANALYZE 输出的方法：

1. 执行 EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM users WHERE age > 30 ORDER BY name;

输出结果会包含 执行计划树 和 实际执行指标，格式类似：

-> Sort: users.name (cost=1023.45 rows=10000) (actual time=50.12..52.34 rows=5000 loops=1)
    -> Filter: (users.age > 30) (cost=500.00 rows=10000) (actual time=10.23..40.56 rows=5000 loops=1)
        -> Table scan on users (cost=500.00 rows=100000) (actual time=0.12..30.67 rows=100000 loops=1)

2. 输出结构解析

EXPLAIN ANALYZE 的输出是一个树形结构，每行表示一个执行步骤，从内层向外层逐级分析。每个步骤包含以下关键信息：

(1) 操作类型

• Sort：排序操作。
• Filter：过滤条件（如 WHERE）。
• Index scan：索引扫描。
• Table scan：全表扫描。
• Join：表连接（如 Nested Loop Join）。
• Aggregate：聚合操作（如 GROUP BY）。
• Temporary table：使用临时表。

(2) 预估成本与行数

• cost=xxx：优化器预估的查询成本（数值越大代价越高）。
• rows=xxx：优化器预估该步骤返回的行数。

(3) 实际执行指标

• actual time=start..end：该步骤的实际执行时间（单位：毫秒），start 是首次返回行的时间，end 是完成所有行处理的时间。
• rows=xxx：实际返回的行数。
• loops=xxx：该步骤的循环次数（如嵌套循环连接的次数）。

3. 关键字段解读示例

以下面的输出为例：

-> Sort: users.name (cost=1023.45 rows=10000) (actual time=50.12..52.34 rows=5000 loops=1)
    -> Filter: (users.age > 30) (cost=500.00 rows=10000) (actual time=10.23..40.56 rows=5000 loops=1)
        -> Table scan on users (cost=500.00 rows=100000) (actual time=0.12..30.67 rows=100000 loops=1)

逐层分析：

1. 最内层操作（Table scan）：

   • 操作类型：全表扫描（Table scan on users）。
   • 预估：优化器认为需要扫描 100000 行，成本为 500.00。
   • 实际：实际扫描了 100000 行，耗时 0.12ms 开始，30.67ms 结束。

2. 中间层操作（Filter）：

   • 操作类型：过滤条件 WHERE age > 30。
   • 预估：优化器预计过滤后剩下 10000 行。
   • 实际：实际过滤后剩下 5000 行，耗时 10.23ms 开始，40.56ms 结束。

3. 最外层操作（Sort）：

   • 操作类型：按 name 排序。
   • 预估：排序成本 1023.45，处理 10000 行。
   • 实际：实际排序 5000 行，耗时 50.12ms 开始，52.34ms 结束。

4. 常见性能问题诊断

通过 EXPLAIN ANALYZE 的输出，可以快速定位以下问题：

(1) 全表扫描（Table Scan）

• 现象：Table scan on users。
• 问题：全表扫描效率低，尤其是大表。
• 优化：为 WHERE 或 JOIN 条件的列添加索引。

(2) 预估行数与实际行数差异大

• 现象：预估 rows=10000，实际 rows=5000。
• 问题：优化器的统计信息过时或不够准确。
• 优化：执行 ANALYZE TABLE users; 更新统计信息。

(3) 排序或临时表开销大

• 现象：Sort 或 Temporary table 耗时高。
• 问题：排序或临时表占用内存或磁盘。
• 优化：增加 sort_buffer_size，或通过索引避免排序（如 ORDER BY 列加索引）。

(4) 嵌套循环次数多

• 现象：loops=1000。
• 问题：嵌套循环次数过多（常见于 JOIN 操作）。
• 优化：调整连接顺序，或使用 STRAIGHT_JOIN 强制连接顺序。

5. 输出结果优化示例

原始查询：

EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE customer_id = 100 
ORDER BY order_date;

输出分析：

-> Sort: orders.order_date (actual time=25.0..25.1 rows=50 loops=1)
    -> Filter: (orders.customer_id = 100) (actual time=0.5..20.0 rows=50 loops=1)
        -> Table scan on orders (actual time=0.1..15.0 rows=10000 loops=1)

优化步骤：

1. 添加索引：

   ALTER TABLE orders ADD INDEX (customer_id, order_date);
   

2. 重新分析：

   -> Index lookup on orders using (customer_id, order_date) (actual time=0.1..0.5 rows=50 loops=1)
   

   • 全表扫描变为索引查找，排序被消除，耗时从 25ms 降至 0.5ms。

6. 注意事项

1. 实际执行查询：EXPLAIN ANALYZE 会实际执行 SQL，避免在生产环境直接分析大查询。
2. 时间单位：所有时间单位为毫秒（ms）。
3. 统计信息：优化器的预估基于统计信息，定期更新统计信息可提高准确性：

   ANALYZE TABLE table_name;
   

通过 EXPLAIN ANALYZE，你可以直接看到查询的“实际表现”，结合预估与实际的对比，精准优化 SQL 性能。