MySQL慢查询优化：从20秒到200毫秒的调优之旅
引子：一次生产事故引发的思考
凌晨3点，手机疯狂震动。监控告警显示：核心业务接口响应时间超过20秒，用户投诉如潮水般涌来。这是每个运维工程师的噩梦时刻。
快速定位后发现，罪魁祸首是一条看似简单的SQL查询。经过一番优化，最终将查询时间从20秒降到了200毫秒——性能提升100倍。
今天，我想和大家分享这次优化的完整过程，以及背后的思考方法论。
一、问题现场：让人崩溃的慢查询
1.1 业务背景
我们的电商平台有一个订单统计功能，需要实时统计每个商户的订单情况。涉及的核心表结构如下：
-- 订单表（500万条记录）
CREATE TABLE orders (
id BIGINTPRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32),
merchant_id INT,
user_id INT,
amount DECIMAL(10,2),
status TINYINT,
created_at DATETIME,
updated_at DATETIME
) ENGINE=InnoDB;
-- 订单明细表（2000万条记录）
CREATE TABLE order_items (
id BIGINTPRIMARY KEY AUTO_INCREMENT,
order_id BIGINT,
product_id INT,
quantity INT,
price DECIMAL(10,2),
created_at DATETIME
) ENGINE=InnoDB;
-- 商户表（10万条记录）
CREATE TABLE merchants (
id INTPRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
category VARCHAR(50),
city VARCHAR(50),
level TINYINT
) ENGINE=InnoDB;
1.2 问题SQL
运营同学需要查询：过去30天内，北京地区VIP商户（level=3）的订单统计数据，包括订单总数、总金额、平均客单价等。
原始SQL是这样的：
SELECT
m.id,
m.name,
COUNT(DISTINCT o.id) as order_count,
COUNT(DISTINCT o.user_id) as user_count,
SUM(o.amount) as total_amount,
AVG(o.amount) as avg_amount,
SUM(oi.quantity) as total_items
FROM merchants m
LEFTJOIN orders o ON m.id = o.merchant_id
LEFTJOIN order_items oi ON o.id = oi.order_id
WHERE m.city ='北京'
AND m.level =3
AND o.status =1
AND o.created_at >= DATE_SUB(NOW(), INTERVAL30DAY)
GROUPBY m.id, m.name
ORDERBY total_amount DESC
LIMIT 100;
执行这条SQL，足足等了20.34秒！
二、问题分析：庖丁解牛式的诊断
2.1 第一步：看执行计划
EXPLAIN SELECT ...
执行计划显示了几个严重问题：
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------+
| 1 | SIMPLE | m | ALL | NULL | NULL | NULL | NULL | 100000 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 5000000 | Using where; Using join buffer |
| 1 | SIMPLE | oi | ALL | NULL | NULL | NULL | NULL | 20000000| Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------+
发现的问题：
- • 三个表都是全表扫描（type=ALL）
- • 没有使用任何索引（key=NULL）
- • 产生了临时表和文件排序
- • 笛卡尔积效应：100000 × 5000000 × 20000000 的恐怖计算量
2.2 第二步：分析慢查询日志
开启慢查询日志后，发现了更多细节：
# Time: 2024-03-15T03:25:41.123456Z
# Query_time: 20.342387 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 25438921
# Rows_affected: 0 Bytes_sent: 15234
关键信息：
- • 检查了2500万行数据，只返回100行
- • 数据过滤效率极低：25438921 / 100 = 254389倍
2.3 第三步：Profile分析
SET profiling = 1;
-- 执行问题SQL
SHOW PROFILE;
结果显示时间主要消耗在：
- • Sending data: 18.5s（90%）
- • Creating sort index: 1.2s（6%）
- • Copying to tmp table: 0.6s（3%）
三、优化方案：四步走战略
步骤1：添加必要索引
首先解决最基础的索引缺失问题：
-- 商户表索引
ALTER TABLE merchants ADD INDEX idx_city_level (city, level);
-- 订单表复合索引（注意字段顺序）
ALTER TABLE orders ADD INDEX idx_merchant_status_created
(merchant_id, status, created_at);
-- 订单明细表索引
ALTER TABLE order_items ADD INDEX idx_order_id (order_id);
索引设计思路：
- • 遵循最左前缀原则
- • 将选择性高的字段放前面
- • 考虑查询条件和JOIN条件
优化后：20.34秒 → 8.5秒
步骤2：SQL改写 – 减少JOIN数据量
原SQL的问题是先JOIN再过滤，导致中间结果集巨大。改写策略：先过滤，再JOIN。
SELECT
m.id,
m.name,
t.order_count,
t.user_count,
t.total_amount,
t.avg_amount,
t.total_items
FROM merchants m
INNERJOIN (
SELECT
o.merchant_id,
COUNT(DISTINCT o.id) as order_count,
COUNT(DISTINCT o.user_id) as user_count,
SUM(o.amount) as total_amount,
AVG(o.amount) as avg_amount,
SUM(items.item_count) as total_items
FROM orders o
LEFTJOIN (
SELECT order_id, SUM(quantity) as item_count
FROM order_items
GROUPBY order_id
) items ON o.id = items.order_id
WHERE o.status =1
AND o.created_at >= DATE_SUB(NOW(), INTERVAL30DAY)
GROUPBY o.merchant_id
) t ON m.id = t.merchant_id
WHERE m.city ='北京'
AND m.level =3
ORDERBY t.total_amount DESC
LIMIT 100;
优化思路：
- • 使用子查询先聚合订单数据
- • 减少JOIN的数据量
- • 将order_items的聚合独立出来
优化后：8.5秒 → 2.3秒
步骤3：使用覆盖索引
分析发现，查询中需要的字段都可以通过索引覆盖，避免回表：
-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_covering
(merchant_id, status, created_at, id, user_id, amount);
这个索引包含了WHERE条件和SELECT需要的所有字段，实现索引覆盖。
优化后：2.3秒 → 0.8秒
步骤4：终极优化 – 物化视图
对于这种统计查询，如果可以接受一定的数据延迟，使用物化视图是最佳方案：
-- 创建汇总表
CREATE TABLE merchant_order_summary (
merchant_id INT,
summary_date DATE,
order_count INT,
user_count INT,
total_amount DECIMAL(10,2),
avg_amount DECIMAL(10,2),
total_items INT,
PRIMARY KEY (merchant_id, summary_date),
INDEX idx_date (summary_date)
) ENGINE=InnoDB;
-- 定时任务（每小时执行）更新汇总数据
INSERT INTO merchant_order_summary
SELECT
merchant_id,
DATE(created_at) as summary_date,
COUNT(DISTINCT id) as order_count,
COUNT(DISTINCT user_id) as user_count,
SUM(amount) as total_amount,
AVG(amount) as avg_amount,
(SELECTSUM(quantity) FROM order_items WHERE order_id IN
(SELECT id FROM orders WHERE merchant_id = o.merchant_id
ANDDATE(created_at) =DATE(o.created_at))
) as total_items
FROM orders o
WHERE status =1
AND created_at >= CURDATE()
GROUPBY merchant_id, DATE(created_at)
ON DUPLICATE KEY UPDATE
order_count =VALUES(order_count),
user_count =VALUES(user_count),
total_amount =VALUES(total_amount),
avg_amount =VALUES(avg_amount),
total_items =VALUES(total_items);
查询时直接使用汇总表：
SELECT
m.id,
m.name,
SUM(s.order_count) as order_count,
SUM(s.user_count) as user_count,
SUM(s.total_amount) as total_amount,
AVG(s.avg_amount) as avg_amount,
SUM(s.total_items) as total_items
FROM merchants m
INNERJOIN merchant_order_summary s ON m.id = s.merchant_id
WHERE m.city ='北京'
AND m.level =3
AND s.summary_date >= DATE_SUB(CURDATE(), INTERVAL30DAY)
GROUPBY m.id, m.name
ORDERBYSUM(s.total_amount) DESC
LIMIT 100;
最终优化后：0.8秒 → 0.2秒（200毫秒）！
四、优化效果对比
| 优化阶段 | 执行时间 | 提升倍数 | 关键优化点 |
|---|---|---|---|
| 原始SQL | 20.34秒 | – | 全表扫描，无索引 |
| 添加索引 | 8.50秒 | 2.4x | 基础索引优化 |
| SQL改写 | 2.30秒 | 8.8x | 减少JOIN数据量 |
| 覆盖索引 | 0.80秒 | 25.4x | 避免回表查询 |
| 物化视图 | 0.20秒 | 101.7x | 预计算汇总 |
五、通用优化方法论
通过这次优化，我总结了一套MySQL慢查询优化的通用方法论：
5.1 诊断三板斧
- 1. EXPLAIN分析
- • 检查type字段：system > const > eq_ref > ref > range > index > ALL
- • 查看key字段：是否使用索引
- • 观察Extra字段：是否有Using filesort、Using temporary
2. 慢查询日志分析
# 开启慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
# 使用pt-query-digest分析
pt-query-digest /var/log/mysql/slow.log
3. Profile分析
SET profiling = 1;
— 执行SQL
SHOW PROFILE FOR QUERY 1;
5.2 优化六步法
- 1. 索引优化
- • 为WHERE条件创建索引
- • 为JOIN字段创建索引
- • 考虑覆盖索引
- • 注意索引顺序（选择性高的在前）
2. SQL改写
- • 小表驱动大表
- • 先过滤再JOIN
- • 避免SELECT *
- • 合理使用子查询
3. 表结构优化
- • 适当的反范式化
- • 字段类型优化（避免隐式转换）
- • 分区表考虑
4. 缓存策略
- • Query Cache（MySQL 8.0已移除）
- • Redis缓存热数据
- • 应用层缓存
5. 读写分离
- • 主从复制
- • 读负载均衡
6. 数据归档
- • 历史数据定期归档
- • 冷热数据分离
5.3 索引设计原则
-- 好的索引设计
ALTER TABLE orders ADD INDEX idx_merchant_status_created
(merchant_id, status, created_at);
-- 原因：
-- 1. merchant_id 用于JOIN
-- 2. status 选择性较高（假设状态值分布均匀）
-- 3. created_at 用于范围查询，放最后
索引设计口诀：
- • 等值查询放前面
- • 范围查询放后面
- • 排序字段要考虑
- • 选择性高的优先
5.4 常见陷阱避坑
- 1. 隐式类型转换
— 错误：字符串字段用数字查询
WHERE phone = 13812345678 — phone是VARCHAR
— 正确
WHERE phone = ‘13812345678’ - 2. 函数破坏索引
— 错误：对索引字段使用函数
WHERE DATE(created_at) = ‘2024-03-15’
— 正确
WHERE created_at >= ‘2024-03-15’
AND created_at < ‘2024-03-16’ - 3. OR条件陷阱
— 可能不走索引
WHERE merchant_id = 100 OR user_id = 200
— 优化方案：使用UNION
SELECT * FROM orders WHERE merchant_id = 100
UNION
SELECT * FROM orders WHERE user_id = 200
六、实战案例集锦
案例1：分页查询优化
问题SQL：
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 1000000, 20; -- 深分页问题
优化方案：
-- 使用覆盖索引 + 子查询
SELECT * FROM orders o
INNER JOIN (
SELECT id FROM orders
ORDER BY created_at DESC
LIMIT 1000000, 20
) t ON o.id = t.id;
案例2：COUNT优化
问题SQL：
SELECT COUNT(*) FROM orders
WHERE status = 1
AND created_at >= '2024-01-01';
优化方案：
-- 方案1：使用索引覆盖
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
-- 方案2：使用汇总表
CREATE TABLE order_count_summary (
count_date DATE,
status TINYINT,
order_count INT,
PRIMARY KEY (count_date, status)
);
案例3：IN查询优化
问题SQL：
SELECT * FROM orders
WHERE merchant_id IN (
SELECT id FROM merchants
WHERE city = '北京' AND level = 3
);
优化方案：
-- 改写为JOIN
SELECT o.* FROM orders o
INNER JOIN merchants m ON o.merchant_id = m.id
WHERE m.city = '北京' AND m.level = 3;
七、监控与预防
7.1 建立监控体系
-- 创建慢查询监控视图
CREATEVIEW slow_query_monitor AS
SELECT
DATE(start_time) as query_date,
LEFT(sql_text, 100) as query_sample,
COUNT(*) as exec_count,
AVG(query_time) as avg_time,
MAX(query_time) as max_time,
SUM(rows_examined) as total_rows_examined
FROM mysql.slow_log
GROUPBYDATE(start_time), LEFT(sql_text, 100)
ORDERBY avg_time DESC;
7.2 自动化告警脚本
#!/usr/bin/env python3
import MySQLdb
import smtplib
from email.mime.text import MIMEText
defcheck_slow_queries():
db = MySQLdb.connect(host="localhost", user="monitor",
passwd="password", db="mysql")
cursor = db.cursor()
# 检查最近1小时的慢查询
cursor.execute("""
SELECT COUNT(*) as slow_count,
AVG(query_time) as avg_time
FROM mysql.slow_log
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
""")
result = cursor.fetchone()
slow_count, avg_time = result
# 触发告警条件
if slow_count > 100or avg_time > 5:
send_alert(f"慢查询告警：数量={slow_count}, 平均时间={avg_time}秒")
cursor.close()
db.close()
defsend_alert(message):
# 发送邮件告警
msg = MIMEText(message)
msg['Subject'] = 'MySQL慢查询告警'
msg['From'] = 'monitor@example.com'
msg['To'] = 'ops@example.com'
s = smtplib.SMTP('localhost')
s.send_message(msg)
s.quit()
if __name__ == "__main__":
check_slow_queries()
7.3 定期优化建议
- 1. 每周检查
- • 分析慢查询TOP 10
- • 检查索引使用情况
- • 评估表数据增长
2. 每月优化
- • 重建碎片化严重的表
- • 更新统计信息
- • 清理无用索引
3. 每季度评估
- • 架构层面优化需求
- • 分库分表评估
- • 硬件升级评估
八、性能优化工具箱
8.1 必备工具清单
- 1. MySQL自带工具
- • EXPLAIN / EXPLAIN ANALYZE
- • SHOW PROFILE
- • Performance Schema
- • sys schema
2. 第三方工具
- • pt-query-digest（Percona Toolkit）
- • MySQLTuner
- • MySQL Workbench
- • Prometheus + Grafana
3. 在线分析工具
# 实时查看进程
mysqladmin -uroot -p processlist
# 查看当前锁等待
SELECT * FROM information_schema.INNODB_LOCKS;
# 查看事务状态
SELECT * FROM information_schema.INNODB_TRX;
8.2 快速诊断脚本
#!/bin/bash
# quick_check.sh - MySQL性能快速检查脚本
echo"=== MySQL Performance Quick Check ==="
# 1. 检查慢查询设置
echo -e "\n[1] Slow Query Settings:"
mysql -e "SHOW VARIABLES LIKE '%slow%';"
# 2. 查看最近的慢查询
echo -e "\n[2] Recent Slow Queries:"
mysql -e "SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 5\G"
# 3. 检查表索引使用情况
echo -e "\n[3] Index Usage Stats:"
mysql -e "
SELECT
table_schema,
table_name,
index_name,
cardinality
FROM information_schema.STATISTICS
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY cardinality DESC
LIMIT 10;"
# 4. 检查表大小
echo -e "\n[4] Table Sizes:"
mysql -e "
SELECT
table_schema,
table_name,
ROUND(data_length/1024/1024, 2) as 'Data_MB',
ROUND(index_length/1024/1024, 2) as 'Index_MB',
ROUND((data_length+index_length)/1024/1024, 2) as 'Total_MB'
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY data_length + index_length DESC
LIMIT 10;"
# 5. 当前连接状态
echo -e "\n[5] Current Connections:"
mysql -e "SHOW STATUS LIKE '%connect%';"
echo -e "\n=== Check Complete ==="
九、优化心得与经验总结
9.1 优化的黄金法则
- 1. 测量先于优化
- • 不要靠猜，要靠数据说话
- • 建立基准测试，量化优化效果
2. 二八定律
- • 80%的性能问题由20%的SQL造成
- • 优先优化最频繁、最耗时的查询
3. 渐进式优化
- • 每次只改一个地方
- • 记录每步优化的效果
- • 保留回滚方案
9.2 团队协作建议
- 1. 建立SQL Review机制
— SQL审核检查清单
– [ ] 是否有合适的索引？
– [ ] 是否会产生全表扫描？
– [ ] JOIN的表不超过3个？
– [ ] 是否使用了SELECT *？
– [ ] 子查询是否可以改为JOIN？
– [ ] 是否考虑了数据增长？ - 2. 制定开发规范
- • 统一命名规范
- • 索引命名规则
- • SQL编写标准
3. 知识分享
- • 定期的技术分享会
- • 维护优化案例库
- • 建立内部Wiki
9.3 踩坑经验分享
坑1：过度索引
-- 错误：为每个查询条件都建索引
ALTER TABLE orders ADD INDEX idx_merchant (merchant_id);
ALTER TABLE orders ADD INDEX idx_status (status);
ALTER TABLE orders ADD INDEX idx_created (created_at);
-- 正确：建立复合索引
ALTER TABLE orders ADD INDEX idx_merchant_status_created
(merchant_id, status, created_at);
坑2：忽视写入性能
- • 索引越多，写入越慢
- • 需要在查询和写入之间找平衡
坑3：缓存失效风暴
- • 大量缓存同时失效
- • 解决方案：缓存过期时间随机化
十、写在最后
从20秒优化到200毫秒，这不仅仅是一个数字游戏，更是对技术精益求精的追求。每一次优化都是一次学习，每一个问题都是一次成长。
记住三个关键点：
- 1. 优化是持续的过程，不是一次性的任务
- 2. 监控比优化更重要，预防胜于治疗
- 3. 理论结合实践，知其然更要知其所以然
作为运维工程师，我们不仅要解决问题，更要预防问题。建立完善的监控体系，制定合理的优化策略，让系统始终保持在最佳状态。
互动环节
你在工作中遇到过哪些棘手的慢查询问题？是如何解决的？欢迎在评论区分享你的经验！
如果这篇文章对你有帮助，欢迎点赞、收藏、关注，我会持续分享更多运维实战经验。
关注我，一起成为运维专家！ 💪
文末福利
就目前来说，传统运维冲击年薪30W+的转型方向就是SRE&DevOps岗位。
为了帮助大家早日摆脱繁琐的基层运维工作，给大家整理了一套高级运维工程师必备技能资料包，内容有多详实丰富看下图！
共有 20 个模块
1.38张最全工程师技能图谱
2.面试大礼包
3.Linux书籍
4.go书籍
······
6.自动化运维工具
18.消息队列合集
以上所有资料获取请扫码
备注：最新运维资料
100%免费领取
（后台不再回复，扫码一键领取）
本文链接：https://www.yunweipai.com/47388.html
网友评论comments