MySQL慢查询日志分析与EXPLAIN执行计划调优实战

慢查询是MySQL性能问题的首要排查方向。通过慢查询日志定位执行时间超过阈值的SQL语句，再用EXPLAIN分析执行计划，找到全表扫描、索引失效、临时表排序等性能瓶颈，针对性优化索引和SQL写法。

慢查询日志配置与采集

开启慢查询日志并设置阈值：

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启（运行时生效，重启失效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 超过1秒的查询记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 永久配置（my.cnf）
-- [mysqld]
-- slow_query_log = ON
-- long_query_time = 1
-- log_queries_not_using_indexes = ON
-- slow_query_log_file = /var/log/mysql/slow.log

使用mysqldumpslow分析慢查询日志：

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

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

# 按返回行数排序（可能扫描大量行但返回少，典型低效查询）
mysqldumpslow -s ar -t 10 /var/log/mysql/slow.log

# 按出现次数排序（高频慢查询）
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

输出结果中Count表示该SQL执行次数，Time为总耗时和平均耗时，Rows为扫描行数和返回行数。高频且耗时的SQL优先优化。

EXPLAIN执行计划关键字段解读

EXPLAIN SELECT o.order_id, o.user_id, u.username, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 2 AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

EXPLAIN输出关键字段：

访问类型从优到劣排序：

system > const > eq_ref > ref > range > index > ALL

• const：主键或唯一索引等值查询，最多匹配一行
• eq_ref：JOIN时被驱动表使用主键或唯一索引
• ref：非唯一索引等值查询
• range：索引范围扫描（BETWEEN、>、<、IN）
• index：扫描整个索引树，不回表但遍历所有索引行
• ALL：全表扫描，必须优化

索引优化策略与覆盖索引

复合索引遵循最左前缀原则，索引列顺序影响查询是否命中索引：

-- 创建复合索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 命中索引（使用status + created_at）
EXPLAIN SELECT * FROM orders WHERE status = 2 AND created_at > '2024-01-01';

-- 命中索引（仅使用status部分）
EXPLAIN SELECT * FROM orders WHERE status = 2;

-- 未命中索引（跳过status，违反最左前缀）
EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01';

-- 覆盖索引：查询字段全部包含在索引中，无需回表
CREATE INDEX idx_covering ON orders(status, created_at, order_id, user_id);

EXPLAIN SELECT status, created_at, order_id, user_id
FROM orders
WHERE status = 2;

-- Extra列显示 "Using index" 表示覆盖索引生效
-- 避免了通过主键回表查询聚簇索引，减少大量随机IO

复杂查询改写与优化案例

案例一：子查询改JOIN

-- 优化前：相关子查询，每行执行一次子查询
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- 优化后：JOIN改写，MySQL 8.0优化器可自动转换
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 1;

-- 或使用EXISTS（适合users表大的场景）
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 1);

案例二：分页查询优化

-- 优化前：OFFSET越大性能越差，需要扫描前面所有行
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20;

-- 优化后：使用游标分页（延迟关联），先通过索引查出主键再关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 20
) t ON o.id = t.id;

-- 或使用WHERE条件替代OFFSET（记住上一页最后一条记录的值）
SELECT * FROM orders
WHERE created_at < '2024-06-01 12:00:00'
ORDER BY created_at DESC LIMIT 20;

案例三：避免索引失效的常见写法

-- 函数操作导致索引失效
-- 错误：对索引列使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2024-06-01';
-- 正确：改写为范围查询
SELECT * FROM orders
WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02';

-- 隐式类型转换导致索引失效
-- 错误：status是int类型，传入字符串
SELECT * FROM orders WHERE status = '2';
-- 正确：使用正确类型
SELECT * FROM orders WHERE status = 2;

-- LIKE前导通配符导致索引失效
-- 错误：无法使用索引
SELECT * FROM users WHERE username LIKE '%zhang%';
-- 正确：后导通配符可使用索引
SELECT * FROM users WHERE username LIKE 'zhang%';

MySQL 8.0的EXPLAIN ANALYZE提供实际执行统计：

EXPLAIN ANALYZE
SELECT o.order_id, u.username FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 2;

-- 输出实际行数和耗时，对比预估值判断统计信息是否准确
-- 如果实际行数远大于预估行数，执行ANALYZE TABLE更新统计信息
ANALYZE TABLE orders;