MySQL慢查询优化案例

MySQL慢查询优化：从20秒到200毫秒的调优之旅

引子：一次生产事故引发的思考

凌晨3点，手机疯狂震动。监控告警显示：核心业务接口响应时间超过20秒，用户投诉如潮水般涌来。这是每个运维工程师的噩梦时刻。

快速定位后发现，罪魁祸首是一条看似简单的SQL查询。经过一番优化，最终将查询时间从20秒降到了200毫秒——性能提升100倍。

今天，我想和大家分享这次优化的完整过程，以及背后的思考方法论。

一、问题现场：让人崩溃的慢查询

1.1 业务背景

我们的电商平台有一个订单统计功能，需要实时统计每个商户的订单情况。涉及的核心表结构如下：

-- 订单表（500万条记录）
CREATE TABLEorders (
  idBIGINTPRIMARY KEYAUTO_INCREMENT,
  order_noVARCHAR(32),
  merchant_idINT,
  user_idINT,
  amountDECIMAL(10,2),
  status TINYINT,
  created_at DATETIME,
  updated_at DATETIME
) ENGINE=InnoDB;

-- 订单明细表（2000万条记录）
CREATE TABLEorder_items (
  idBIGINTPRIMARY KEYAUTO_INCREMENT,
  order_idBIGINT,
  product_idINT,
  quantityINT,
  priceDECIMAL(10,2),
  created_at DATETIME
) ENGINE=InnoDB;

-- 商户表（10万条记录）
CREATE TABLEmerchants (
  idINTPRIMARY KEYAUTO_INCREMENT,
  nameVARCHAR(100),
  categoryVARCHAR(50),
  cityVARCHAR(50),
  level TINYINT
) ENGINE=InnoDB;

1.2 问题SQL

运营同学需要查询：过去30天内，北京地区VIP商户（level=3）的订单统计数据，包括订单总数、总金额、平均客单价等。

原始SQL是这样的：

SELECT
  m.id,
  m.name,
 COUNT(DISTINCTo.id)asorder_count,
 COUNT(DISTINCTo.user_id)asuser_count,
 SUM(o.amount)astotal_amount,
 AVG(o.amount)asavg_amount,
 SUM(oi.quantity)astotal_items
FROMmerchants m
LEFTJOINorders oONm.id=o.merchant_id
LEFTJOINorder_items oiONo.id=oi.order_id
WHEREm.city='北京'
 ANDm.level=3
 ANDo.status=1
 ANDo.created_at>=DATE_SUB(NOW(),INTERVAL30DAY)
GROUPBYm.id, m.name
ORDERBYtotal_amountDESC
LIMIT100;

执行这条SQL，足足等了20.34秒！

二、问题分析：庖丁解牛式的诊断

2.1 第一步：看执行计划

EXPLAINSELECT...

执行计划显示了几个严重问题：

+----+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------+
| 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-15T0341.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分析

SETprofiling=1;
-- 执行问题SQL
SHOWPROFILE;

结果显示时间主要消耗在：

• Sending data: 18.5s（90%）

• Creating sort index: 1.2s（6%）

• Copying to tmp table: 0.6s（3%）

三、优化方案：四步走战略

步骤1：添加必要索引

首先解决最基础的索引缺失问题：

-- 商户表索引
ALTER TABLEmerchantsADDINDEX idx_city_level (city, level);

-- 订单表复合索引（注意字段顺序）
ALTER TABLEordersADDINDEX idx_merchant_status_created
  (merchant_id, status, created_at);

-- 订单明细表索引
ALTER TABLEorder_itemsADDINDEX 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
FROMmerchants m
INNERJOIN(
 SELECT
    o.merchant_id,
   COUNT(DISTINCTo.id)asorder_count,
   COUNT(DISTINCTo.user_id)asuser_count,
   SUM(o.amount)astotal_amount,
   AVG(o.amount)asavg_amount,
   SUM(items.item_count)astotal_items
 FROMorders o
 LEFTJOIN(
   SELECTorder_id,SUM(quantity)asitem_count
   FROMorder_items
   GROUPBYorder_id
  ) itemsONo.id=items.order_id
 WHEREo.status=1
   ANDo.created_at>=DATE_SUB(NOW(),INTERVAL30DAY)
 GROUPBYo.merchant_id
) tONm.id=t.merchant_id
WHEREm.city='北京'
 ANDm.level=3
ORDERBYt.total_amountDESC
LIMIT100;

优化思路：

• 使用子查询先聚合订单数据

• 减少JOIN的数据量

• 将order_items的聚合独立出来

优化后：8.5秒 → 2.3秒

步骤3：使用覆盖索引

分析发现，查询中需要的字段都可以通过索引覆盖，避免回表：

-- 创建覆盖索引
ALTER TABLEordersADDINDEX idx_covering
  (merchant_id, status, created_at, id, user_id, amount);

这个索引包含了WHERE条件和SELECT需要的所有字段，实现索引覆盖。

优化后：2.3秒 → 0.8秒

步骤4：终极优化 - 物化视图

对于这种统计查询，如果可以接受一定的数据延迟，使用物化视图是最佳方案：

-- 创建汇总表
CREATE TABLEmerchant_order_summary (
  merchant_idINT,
  summary_dateDATE,
  order_countINT,
  user_countINT,
  total_amountDECIMAL(10,2),
  avg_amountDECIMAL(10,2),
  total_itemsINT,
 PRIMARY KEY(merchant_id, summary_date),
  INDEX idx_date (summary_date)
) ENGINE=InnoDB;

-- 定时任务（每小时执行）更新汇总数据
INSERT INTOmerchant_order_summary
SELECT
  merchant_id,
 DATE(created_at)assummary_date,
 COUNT(DISTINCTid)asorder_count,
 COUNT(DISTINCTuser_id)asuser_count,
 SUM(amount)astotal_amount,
 AVG(amount)asavg_amount,
  (SELECTSUM(quantity)FROMorder_itemsWHEREorder_idIN
    (SELECTidFROMordersWHEREmerchant_id=o.merchant_id
    ANDDATE(created_at)=DATE(o.created_at))
  )astotal_items
FROMorders o
WHEREstatus=1
 ANDcreated_at>=CURDATE()
GROUPBYmerchant_id,DATE(created_at)
ONDUPLICATE KEYUPDATE
  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)asorder_count,
 SUM(s.user_count)asuser_count,
 SUM(s.total_amount)astotal_amount,
 AVG(s.avg_amount)asavg_amount,
 SUM(s.total_items)astotal_items
FROMmerchants m
INNERJOINmerchant_order_summary sONm.id=s.merchant_id
WHEREm.city='北京'
 ANDm.level=3
 ANDs.summary_date>=DATE_SUB(CURDATE(),INTERVAL30DAY)
GROUPBYm.id, m.name
ORDERBYSUM(s.total_amount)DESC
LIMIT100;

最终优化后：0.8秒 → 0.2秒（200毫秒）！

四、优化效果对比

五、通用优化方法论

通过这次优化，我总结了一套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分析

SETprofiling=1;
-- 执行SQL
SHOWPROFILEFORQUERY1;

5.2 优化六步法

1.索引优化

• 为WHERE条件创建索引

• 为JOIN字段创建索引

• 考虑覆盖索引

• 注意索引顺序（选择性高的在前）

2.SQL改写

• 小表驱动大表

• 先过滤再JOIN

• 避免SELECT *

• 合理使用子查询

3.表结构优化

• 适当的反范式化

• 字段类型优化（避免隐式转换）

• 分区表考虑

4.缓存策略

• Query Cache（MySQL 8.0已移除）

• Redis缓存热数据

• 应用层缓存

5.读写分离

• 主从复制

• 读负载均衡

6.数据归档

• 历史数据定期归档

• 冷热数据分离

5.3 索引设计原则

-- 好的索引设计
ALTER TABLEordersADDINDEX idx_merchant_status_created
  (merchant_id, status, created_at);

-- 原因：
-- 1. merchant_id 用于JOIN
-- 2. status 选择性较高（假设状态值分布均匀）
-- 3. created_at 用于范围查询，放最后

索引设计口诀：

• 等值查询放前面

• 范围查询放后面

• 排序字段要考虑

• 选择性高的优先

5.4 常见陷阱避坑

1.隐式类型转换

-- 错误：字符串字段用数字查询
WHEREphone=13812345678-- phone是VARCHAR

-- 正确
WHEREphone='13812345678'

2.函数破坏索引

-- 错误：对索引字段使用函数
WHEREDATE(created_at)='2024-03-15'

-- 正确
WHEREcreated_at>='2024-03-15'
ANDcreated_at< '2024-03-16'

3.OR条件陷阱

-- 可能不走索引
WHEREmerchant_id=100ORuser_id=200

-- 优化方案：使用UNION
SELECT*FROMordersWHEREmerchant_id=100
UNION
SELECT*FROMordersWHEREuser_id=200

六、实战案例集锦

案例1：分页查询优化

问题SQL：

SELECT*FROMorders
ORDERBYcreated_atDESC
LIMIT1000000,20; -- 深分页问题

优化方案：

-- 使用覆盖索引 + 子查询
SELECT*FROMorders o
INNERJOIN(
 SELECTidFROMorders
 ORDERBYcreated_atDESC
  LIMIT1000000,20
) tONo.id=t.id;

案例2：COUNT优化

问题SQL：

SELECTCOUNT(*)FROMorders
WHEREstatus=1
ANDcreated_at>='2024-01-01';

优化方案：

-- 方案1：使用索引覆盖
ALTER TABLEordersADDINDEX idx_status_created (status, created_at);

-- 方案2：使用汇总表
CREATE TABLEorder_count_summary (
  count_dateDATE,
  status TINYINT,
  order_countINT,
 PRIMARY KEY(count_date, status)
);

案例3：IN查询优化

问题SQL：

SELECT*FROMorders
WHEREmerchant_idIN(
 SELECTidFROMmerchants
 WHEREcity='北京'ANDlevel=3
);

优化方案：

-- 改写为JOIN
SELECTo.*FROMorders o
INNERJOINmerchants mONo.merchant_id=m.id
WHEREm.city='北京'ANDm.level=3;

七、监控与预防

7.1 建立监控体系

-- 创建慢查询监控视图
CREATEVIEWslow_query_monitorAS
SELECT
 DATE(start_time)asquery_date,
 LEFT(sql_text,100)asquery_sample,
 COUNT(*)asexec_count,
 AVG(query_time)asavg_time,
 MAX(query_time)asmax_time,
 SUM(rows_examined)astotal_rows_examined
FROMmysql.slow_log
GROUPBYDATE(start_time),LEFT(sql_text,100)
ORDERBYavg_timeDESC;

7.2 自动化告警脚本

#!/usr/bin/env python3
importMySQLdb
importsmtplib
fromemail.mime.textimportMIMEText

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
 
 # 触发告警条件
 ifslow_count >100oravg_time >5:
    send_alert(f"慢查询告警：数量={slow_count}, 平均时间={avg_time}秒")
 
  cursor.close()
  db.close()

defsend_alert(message):
 # 发送邮件告警
  msg = MIMEText(message)
  msg['Subject'] ='MySQL慢查询告警'
  msg['From'] ='[email protected]'
  msg['To'] ='[email protected]'
 
  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"
[1] Slow Query Settings:"
mysql -e"SHOW VARIABLES LIKE '%slow%';"

# 2. 查看最近的慢查询
echo-e"
[2] Recent Slow Queries:"
mysql -e"SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 5G"

# 3. 检查表索引使用情况
echo-e"
[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"
[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"
[5] Current Connections:"
mysql -e"SHOW STATUS LIKE '%connect%';"

echo-e"
=== 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 TABLEordersADDINDEX idx_merchant (merchant_id);
ALTER TABLEordersADDINDEX idx_status (status);
ALTER TABLEordersADDINDEX idx_created (created_at);

-- 正确：建立复合索引
ALTER TABLEordersADDINDEX idx_merchant_status_created
  (merchant_id, status, created_at);

坑2：忽视写入性能

• 索引越多，写入越慢

• 需要在查询和写入之间找平衡

坑3：缓存失效风暴

• 大量缓存同时失效

• 解决方案：缓存过期时间随机化

十、写在最后

从20秒优化到200毫秒，这不仅仅是一个数字游戏，更是对技术精益求精的追求。每一次优化都是一次学习，每一个问题都是一次成长。

记住三个关键点：

1.优化是持续的过程，不是一次性的任务

2.监控比优化更重要，预防胜于治疗

3.理论结合实践，知其然更要知其所以然

作为运维工程师，我们不仅要解决问题，更要预防问题。建立完善的监控体系，制定合理的优化策略，让系统始终保持在最佳状态。