MySQL 慢查询分析与优化：用 EXPLAIN 定位性能瓶颈

小飞兽 MySQL 4 次阅读 2026-07-29

前言

慢查询是数据库性能的头号敌人。一个不经意的全表扫描，可能在数据量小的时候毫无感觉，一旦数据增长到百万级，就会成为系统的致命瓶颈。本文系统讲解如何利用 EXPLAIN、执行计划分析、索引优化等手段，定位并解决 MySQL 慢查询问题。

开启慢查询日志

慢查询日志是排查慢查询的第一步。MySQL 提供两种方式记录慢查询：日志文件和 Performance Schema 慢查询表。

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/lib/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

-- MySQL 8.0+ 使用 Performance Schema 存储慢查询
SET GLOBAL performance_schema_events_statements_history_size = 100;

-- 查看慢查询汇总（MySQL 8.0+）
SELECT
    DIGEST_TEXT AS query_sql,
    COUNT_STAR AS exec_count,
    AVG_TIMER_WAIT / 1000000000000 AS avg_latency_sec,
    SUM_ROWS_EXAMINED AS rows_scanned
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_TIMER_WAIT > 1000000000000
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 使用 mysqldumpslow 工具分析慢查询日志
-- mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log

-- 使用 pt-query-digest（Percona Toolkit）深度分析
-- pt-query-digest /var/lib/mysql/mysql-slow.log

EXPLAIN 执行计划详解

EXPLAIN 是分析查询性能最核心的工具。它展示 MySQL 查询优化器的执行计划。

-- 基本 EXPLAIN 用法
EXPLAIN SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.age > 25
ORDER BY u.created_at DESC
LIMIT 20;

-- MySQL 8.0+ 支持 EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT ...

-- type: 访问类型（性能从好到差）
--   system：表只有一行
--   const：最多匹配一行
--   eq_ref：连接时通过主键或唯一索引等值匹配
--   ref：非唯一索引等值匹配
--   range：索引范围扫描
--   index：全索引扫描
--   ALL：全表扫描（性能最差，尽量避免）

-- key: 实际使用的索引
-- rows: 预估扫描行数，越大越慢

-- Extra: 额外信息
--   Using index：覆盖索引，无需回表
--   Using where：需要在存储引擎返回后用 WHERE 条件过滤
--   Using temporary：使用了临时表
--   Using filesort：需要文件排序
--   Using index condition：索引下推优化

典型慢查询案例与优化

案例1：全表扫描导致慢查询

-- 原始慢查询：users 表 100 万行，name 无索引
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type=ALL, rows=1000000, 严重全表扫描

-- 优化：为 name 列添加索引
ALTER TABLE users ADD INDEX idx_name (name);

-- 优化后
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type=ref, key=idx_name, rows=10，性能提升数万倍

-- 如果只需要查询 name 和 age，加覆盖索引更优
ALTER TABLE users ADD INDEX idx_name_age (name, age);
EXPLAIN SELECT name, age FROM users WHERE name = '张三';
-- Extra: Using index（Index Only Scan，无需回表）

案例2：隐式类型转换导致索引失效

-- age 是 INT 类型，传入字符串 '25'
EXPLAIN SELECT * FROM users WHERE age = '25';
-- 可能导致索引失效

-- phone 是 VARCHAR 类型，传入数字（最危险）
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
-- phone 索引完全失效！

-- 正确做法：传入正确类型
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';

案例3：深度分页查询优化

-- 深度分页：偏移量很大时，MySQL 需要先扫描并丢弃大量数据
EXPLAIN SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;
-- 扫描 100020 行，只返回 20 行，大量无用扫描

-- 优化方案1：使用主键延迟关联
SELECT * FROM orders
INNER JOIN (
    SELECT id FROM orders ORDER BY id DESC LIMIT 100000, 20
) AS t USING (id);

-- 优化方案2：记录上次的最大 ID，使用 > 条件替代 OFFSET
SELECT * FROM orders
WHERE id < #{last_max_id}
ORDER BY id DESC
LIMIT 20;

-- 优化方案3：使用游标分页
SELECT * FROM orders
WHERE created_at < #{cursor}
ORDER BY created_at DESC
LIMIT 20;

案例4：COUNT(*) 慢查询

-- COUNT(*) 在大表上非常慢
SELECT COUNT(*) FROM orders WHERE status = 'paid';

-- 优化方案1：利用索引覆盖
ALTER TABLE orders ADD INDEX idx_status (status);
SELECT COUNT(*) FROM orders WHERE status = 'paid';

-- 优化方案2：维护计数器表
CREATE TABLE order_stats (
    stat_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL,
    cnt BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (stat_date, status)
);

-- 优化方案3：使用 EXPLAIN 估算行数
EXPLAIN SELECT * FROM orders WHERE status = 'paid';

常见问题

Q1：EXPLAIN 显示 rows 预估不准确怎么办？
使用 ANALYZE TABLE 重新统计索引分布信息。MySQL 8.0+ 可以通过 optimizer_trace 查看优化器的完整决策过程。

Q2：联合查询（JOIN）很慢如何优化？
首先用 EXPLAIN 查看连接顺序和类型。确保被驱动表（大表作为被驱动时）有连接列的索引。如果 JOIN 后的结果集很大，考虑在应用层分两次查询代替 JOIN。

Q3：如何预防慢查询？
建立常态化监控机制，使用 SLO 告警。在新功能上线前执行 EXPLAIN 分析。代码评审阶段检查所有 SQL。使用 ORM 时注意生成的 SQL 是否符合预期。