MySQL 8.0执行计划分析与慢查询优化实战

MySQL 8.0执行计划：从rows估算到实际耗时

SQL查询优化不是靠经验猜测，而是靠执行计划诊断。MySQL 8.0的EXPLAIN ANALYZE能给出实际执行耗时，比传统EXPLAIN的估算值可靠得多。这篇实战指南覆盖执行计划核心字段解读、索引设计决策、慢查询诊断全流程，用真实案例展示从发现问题到验证修复的完整操作过程。

EXPLAIN ANALYZE：比传统EXPLAIN更可靠的诊断工具

MySQL 8.0.18+引入EXPLAIN ANALYZE，实际执行查询并返回真实耗时：

-- 传统EXPLAIN只有估算值
EXPLAIN SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.created_at > '2026-01-01'
ORDER BY o.amount DESC
LIMIT 50;

-- EXPLAIN ANALYZE返回真实执行耗时
EXPLAIN ANALYZE
SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.created_at > '2026-01-01'
ORDER BY o.amount DESC
LIMIT 50;

EXPLAIN ANALYZE输出中的关键指标：

• actual time：实际耗时（ms），格式”首行耗时..全部行耗时”
• actual rows：实际处理的行数，与rows估算值对比能发现统计偏差
• loops：该节点执行次数，嵌套循环中内层表的loops值=外层表的actual rows
• Filter：过滤条件，估算过滤比例与实际对比

当actual rows远大于rows估算值时，说明索引统计信息不准确，需要执行ANALYZE TABLE更新统计。

索引设计决策：从type字段判断扫描效率

执行计划中type字段直接反映索引使用效率，从优到劣排序：

type=ALL是最需要优化的场景。诊断步骤：

-- 1. 确认表行数和索引情况
SELECT table_rows, data_length, index_length
FROM information_schema.tables
WHERE table_name = 'orders' AND table_schema = DATABASE();

SHOW INDEX FROM orders;

-- 2. 检查是否缺少合适索引
-- WHERE条件列 + ORDER BY列的组合索引
ALTER TABLE orders ADD INDEX idx_status_created_amount (status, created_at, amount);

-- 3. 验证索引是否被使用
EXPLAIN SELECT id, amount FROM orders
WHERE status = 'PAID' AND created_at > '2026-01-01'
ORDER BY amount DESC LIMIT 50;

索引设计三原则：

• 最左前缀匹配：索引(a,b,c)能覆盖WHERE a=?、WHERE a=? AND b=?，但不能覆盖WHERE b=?
• 等值条件放前面：WHERE status=’PAID’ AND created_at>’2026-01-01’，索引(status,created_at)比(created_at,status)高效
• 覆盖索引避免回表：SELECT只取索引列时，Extra显示Using index，无需回表查数据

慢查询诊断全流程：从slow_log到优化方案

Step 1：开启慢查询日志

-- 动态开启，无需重启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL min_examined_row_limit = 100;  -- 扫描行<100不记录

-- 日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

Step 2：用mysqldumpslow分析慢查询Top

# 按平均查询时间排序，取Top 10
mysqldumpslow -s at -t 10 /var/lib/mysql/mysql-slow.log

# 按总锁定时间排序
mysqldumpslow -s al -t 10 /var/lib/mysql/mysql-slow.log

# 按扫描行数排序
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log

Step 3：Performance Schema深度分析

-- 查看耗时最长的SQL语句
SELECT DIGEST_TEXT,
       COUNT_STAR AS exec_count,
       ROUND(SUM_TIMER_WAIT/1e12, 3) AS total_time_sec,
       ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_time_ms,
       SUM_ROWS_EXAMINED AS total_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Step 4：定位具体问题的典型模式

-- 问题1：索引失效 - 隐式类型转换
-- phone是VARCHAR，但查询用整数
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
-- type=ALL，索引失效！改为：
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';
-- type=ref

-- 问题2：索引失效 - 函数包裹列
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2026-07-27';
-- type=ALL，索引失效！改为范围查询：
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2026-07-27' AND created_at < '2026-07-28';
-- type=range

-- 问题3：索引失效 - LIKE前缀通配符
EXPLAIN SELECT * FROM products WHERE name LIKE '%手机壳%';
-- type=ALL。无法用B-Tree索引优化前缀通配符
-- 替代方案：全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机壳');

JOIN优化：驱动表选择与嵌套循环优化

MySQL 8.0使用Hash Join替代了部分Nested Loop Join，但优化器仍可能选择低效的执行计划。手动控制方法：

-- STRAIGHT_JOIN强制指定驱动表顺序
EXPLAIN ANALYZE
SELECT STRAIGHT_JOIN o.id, o.amount, u.name
FROM orders o  -- 先扫描orders（小表驱动）
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID'
LIMIT 1000;

-- 用optimizer_switch控制JOIN算法
SET SESSION optimizer_switch = 'block_nested_loop=on,hash_join=on';

-- 查看实际使用的JOIN算法
EXPLAIN FORMAT=TREE
SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2026-06-01';

驱动表选择原则：小结果集驱动大结果集。用EXPLAIN ANALYZE对比不同驱动顺序的actual time，选择耗时更短的方案。

分页查询优化：深分页性能陷阱

-- 传统分页：OFFSET越大越慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 50;
-- 需要扫描100050行，丢弃前100000行

-- 优化方案1：游标分页（推荐）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 50;
-- 只扫描50行，利用索引定位

-- 优化方案2：子查询延迟关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 50) tmp
ON o.id = tmp.id;
-- 子查询走覆盖索引只取id，回表取50行

-- 优化方案3：禁止深分页，限制最大页码
-- 业务上限制最多翻100页，超过100页要求用户缩小查询范围

索引维护与统计信息更新

-- 大批量数据导入后更新统计信息
ANALYZE TABLE orders, users, order_items;

-- 查看索引统计信息
SELECT index_name, cardinality, nullable, seq_in_index
FROM information_schema.statistics
WHERE table_name = 'orders' AND table_schema = DATABASE();

-- 查看索引使用情况（MySQL 8.0 Performance Schema）
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
       COUNT_READ, COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_db' AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_READ DESC;

cardinality值越接近表行数，索引选择性越好。如果cardinality远小于实际不同值数量，说明统计信息过时，需要ANALYZE TABLE更新。