MySQL 慢查询优化实战：从 8 秒到 0.03 秒，我做了这 7 件事
上线一年的业务系统，数据库突然开始慢查询告警，查询耗时从毫秒级飙升到 8 秒以上。这篇文章记录了我的完整排查和优化过程。
问题背景
一个运行了一年的用户行为分析系统，随着数据积累到 3000 万行，核心查询接口从 200ms 变成了 8 秒以上，偶尔还会把数据库连接池耗尽，导致服务不可用。
系统配置：
第一步：开启慢查询日志，定位问题 SQL
不知道慢在哪，一切优化都是瞎猜。先把慢查询日志开起来。
-- 查看当前慢查询配置 SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time'; -- 开启慢查询（超过 1 秒的 SQL 会被记录） SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; -- 记录没有使用索引的查询（强烈推荐开启） SET GLOBAL log_queries_not_using_indexes = 'ON';
云上 RDS 可以在控制台直接配置，不需要手动改参数。
用 mysqldumpslow 分析慢查询日志：
# 按执行时间排序，显示 top 10 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 按出现次数排序（频繁出现的 SQL 更值得优化） mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
找到了我们的”头号嫌疑犯”：
-- 这条 SQL 平均耗时 7.8 秒，每分钟执行 20+ 次
SELECT user_id, event_type, COUNT(*) as cnt, MAX(created_at) as last_time
FROM user_events
WHERE created_at >= '2026-01-01'
AND tenant_id = 123
AND event_type IN ('click', 'view', 'purchase')
GROUP BY user_id, event_type
ORDER BY cnt DESC
LIMIT 100;
第二步：EXPLAIN 分析执行计划
EXPLAIN SELECT user_id, event_type, COUNT(*) as cnt, MAX(created_at) as last_time
FROM user_events
WHERE created_at >= '2026-01-01'
AND tenant_id = 123
AND event_type IN ('click', 'view', 'purchase')
GROUP BY user_id, event_type
ORDER BY cnt DESC
LIMIT 100;
输出结果：
id | select_type | table | type | possible_keys | key | rows | Extra 1 | SIMPLE | user_events | ALL | NULL | NULL | 30187264 | Using where; Using filesort
关键问题：
第三步：建立合适的索引
分析查询特征
这条 SQL 的 WHERE 条件是：tenant_id = 123 AND event_type IN (...) AND created_at >= '...'
索引建立原则：区分度高的列放前面，范围查询列放最后。
tenant_id 区分度：假设有 500 个租户，选择性 = 500/3000万 = 1/60000，非常高
event_type 区分度：假设有 20 种事件类型，中等
created_at 是范围查询，放最后
-- 方案一：联合索引（推荐） ALTER TABLE user_events ADD INDEX idx_tenant_event_time (tenant_id, event_type, created_at); -- 方案二：覆盖索引（把 SELECT 的列也加进来，避免回表） ALTER TABLE user_events ADD INDEX idx_tenant_event_time_cover (tenant_id, event_type, created_at, user_id);
加完索引再次 EXPLAIN：
id | type | key | rows | Extra 1 | range | idx_tenant_event_time | 89234 | Using index condition; Using filesort
执行时间：8.2 秒 → 1.4 秒，大幅提升，但还不够快。
第四步：优化 GROUP BY + ORDER BY
ORDER BY cnt DESC 中的 cnt 是聚合计算的结果，MySQL 必须计算完所有 GROUP BY 的结果才能排序，这就是 Using filesort 的根因。
方法：利用子查询减少数据集
-- 优化前：全量 GROUP BY 后再 ORDER BY
SELECT user_id, event_type, COUNT(*) as cnt, MAX(created_at) as last_time
FROM user_events
WHERE created_at >= '2026-01-01'
AND tenant_id = 123
AND event_type IN ('click', 'view', 'purchase')
GROUP BY user_id, event_type
ORDER BY cnt DESC
LIMIT 100;
-- 优化后：先缩小范围，再聚合
SELECT e.user_id, e.event_type,
COUNT(*) as cnt,
MAX(e.created_at) as last_time
FROM user_events e
WHERE e.tenant_id = 123
AND e.event_type IN ('click', 'view', 'purchase')
AND e.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY) -- 限制时间范围
GROUP BY e.user_id, e.event_type
ORDER BY cnt DESC
LIMIT 100;
执行时间：1.4 秒 → 0.3 秒
第五步：分区表——彻底解决历史数据积压问题
真正的问题是：随着数据增长，再好的索引也会变慢。分区表是终极方案。
-- 将 user_events 改造为按月分区
CREATE TABLE user_events_new (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
tenant_id INT NOT NULL,
user_id BIGINT NOT NULL,
event_type VARCHAR(50) NOT NULL,
event_data JSON,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, created_at), -- 分区键必须在主键中
INDEX idx_tenant_event (tenant_id, event_type, created_at)
)
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
PARTITION p202401 VALUES LESS THAN (202402),
PARTITION p202402 VALUES LESS THAN (202403),
PARTITION p202403 VALUES LESS THAN (202404),
-- ... 以此类推
PARTITION p202501 VALUES LESS THAN (202502),
PARTITION p_future VALUES LESS THAN MAXVALUE -- 兜底分区
);
分区后，查询近 3 个月的数据，MySQL 会自动分区裁剪（Partition Pruning），只扫描对应月份的分区，而不是整张表。
-- 验证分区裁剪是否生效 EXPLAIN PARTITIONS SELECT ...; -- 看 partitions 列，只出现 p202503,p202504,p202504 则说明裁剪生效
执行时间：0.3 秒 → 0.05 秒
第六步：读写分离 + 查询缓存
分析类查询往往是重度读操作，可以通过以下方式进一步降低压力：
配置 MySQL 主从读写分离
# SQLAlchemy 读写分离配置
from sqlalchemy import create_engine
# 写操作走主库
master_engine = create_engine(
"mysql+pymysql://user:pass@master-host:3306/dbname",
pool_size=10,
max_overflow=20,
)
# 读操作走从库（腾讯云 MySQL 只读实例）
slave_engine = create_engine(
"mysql+pymysql://user:pass@slave-host:3306/dbname",
pool_size=20, # 读库连接池更大
max_overflow=40,
)
def get_db_engine(readonly=False):
return slave_engine if readonly else master_engine
Redis 缓存统计结果
import redis
import json
import hashlib
from datetime import timedelta
redis_client = redis.Redis(host='your-redis-host', port=6379, db=0)
def get_user_event_stats(tenant_id: int, event_types: list, days: int = 90):
"""带缓存的统计查询"""
# 生成缓存 key
cache_key = f"event_stats:{tenant_id}:{','.join(sorted(event_types))}:{days}"
# 尝试从缓存获取
cached = redis_client.get(cache_key)
if cached:
return json.loads(cached)
# 缓存未命中，查数据库
results = query_from_db(tenant_id, event_types, days)
# 写入缓存，5分钟过期（统计数据可接受轻微延迟）
redis_client.setex(
cache_key,
timedelta(minutes=5),
json.dumps(results, ensure_ascii=False, default=str)
)
return results
执行时间（缓存命中）：0.05 秒 → 0.003 秒
第七步：定期维护，防止性能退化
建立一个定期维护机制，避免问题重复出现：
-- 每周执行：更新统计信息（影响查询计划准确性） ANALYZE TABLE user_events; -- 每月执行：重建碎片化索引 ALTER TABLE user_events ENGINE=InnoDB; -- 重建整张表（有锁，业务低峰期执行） -- 或使用 pt-online-schema-change 在线重建 -- 删除 3 个月前的历史分区（配合分区表使用） ALTER TABLE user_events DROP PARTITION p202301;
设置云监控告警（腾讯云 MySQL 控制台）：
优化效果汇总
| 优化手段 | 执行时间 | 降幅 |
|---|---|---|
| ——— | ——— | —— |
| 优化前（全表扫描） | 8.2 秒 | – |
| 添加联合索引 | 1.4 秒 | ↓ 83% |
| 优化 SQL 语句 | 0.3 秒 | ↓ 79% |
| 分区表裁剪 | 0.05 秒 | ↓ 83% |
| Redis 缓存命中 | 0.003 秒 | ↓ 94% |
| **总体提升** | **0.003 秒** | **↓ 99.96%** |
从 8 秒到 3 毫秒，接近 2700 倍 的性能提升。
经验总结
做 MySQL 优化，我有几条实践原则：
1. 先 EXPLAIN，再优化：不要凭感觉，看执行计划说话
2. 索引不是越多越好：索引会拖慢写入，慎重选择，优先联合索引
3. 时间范围必须有边界：业务层强制限制查询时间窗口（30天/90天/1年）
4. 分区表解决数据量问题：索引优化有上限，分区没有
5. 缓存是最后一道防线：统计报表类查询必须加缓存，接受轻微延迟
6. 监控是优化的保障：建立慢查询告警，问题早发现早处理
关于作者
长期关注大模型应用落地与云服务器实战，专注技术在企业场景中的落地实践。
个人博客：yunduancloud.icu —— 持续更新云计算、AI大模型实战教程，欢迎访问交流。