📊 生产环境MySQL慢查询优化实战：从30秒到0.5秒的性能突破

一、问题背景与现象

1.1 业务场景

我们公司是一家中型电商平台，用户数约200万，日订单量在5万单左右。2025年双十一期间，运营部门反馈订单查询页面响应极慢，用户投诉激增。

1.2 问题现象

• 症状：订单列表查询接口响应时间从正常的1-2秒飙升至30秒以上
• 影响范围：影响所有需要查询历史订单的用户
• 数据库版本：MySQL 8.0.32
• 数据量：订单表(orders)约800万条记录

1.3 初步监控数据

通过MySQL慢查询日志发现问题SQL：

SELECT o.order_id, o.user_id, o.order_time, o.total_amount, 
       o.status, u.username, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.status IN ('pending', 'processing', 'completed')
  AND o.order_time >= '2025-10-01'
ORDER BY o.order_time DESC
LIMIT 20 OFFSET 0;

慢查询日志显示：

• Query_time: 28.345秒
• Lock_time: 0.002秒
• Rows_examined: 7,850,236行
• Rows_sent: 20行

二、问题分析与诊断

2.1 执行计划分析

首先使用EXPLAIN查看执行计划：

EXPLAIN SELECT o.order_id, o.user_id, o.order_time, o.total_amount, 
       o.status, u.username, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.status IN ('pending', 'processing', 'completed')
  AND o.order_time >= '2025-10-01'
ORDER BY o.order_time DESC
LIMIT 20 OFFSET 0;

执行计划结果：

关键问题识别：

1. ❌ type=ALL：全表扫描，性能杀手
2. ❌ key=NULL：没有使用任何索引
3. ❌ Using filesort：需要额外排序，耗费大量资源
4. ❌ rows=7850236：扫描了几乎全表数据

2.2 索引情况检查

SHOW INDEX FROM orders;

现有索引：

• PRIMARY KEY (order_id)
• KEY idx_user_id (user_id)

问题总结：

1. 缺少status字段索引
2. 缺少order_time字段索引
3. 没有针对WHERE条件和ORDER BY的组合索引

三、优化方案设计

3.1 优化思路

根据分析，制定三步优化策略：

第一步：创建复合索引覆盖WHERE条件
第二步：优化ORDER BY，避免filesort
第三步：考虑索引覆盖，减少回表

3.2 索引设计方案

基于查询特点，设计复合索引：

-- 方案一：status + order_time 复合索引
CREATE INDEX idx_status_ordertime ON orders(status, order_time DESC);

-- 方案二：覆盖索引（包含查询所需字段）
CREATE INDEX idx_status_ordertime_cover ON orders(
    status, 
    order_time DESC, 
    order_id, 
    user_id, 
    total_amount
);

索引设计原则：

• WHERE条件字段在前（status）
• ORDER BY字段在后（order_time）
• 降序索引匹配ORDER BY方向
• 考虑索引覆盖减少回表

四、优化实施步骤

4.1 测试环境验证

步骤1：在测试环境创建索引

-- 在测试库执行
CREATE INDEX idx_status_ordertime ON orders(status, order_time DESC);

步骤2：分析索引大小

SELECT 
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE table_name = 'orders' 
  AND index_name = 'idx_status_ordertime'
  AND stat_name = 'size';

结果：索引大小约120MB，可接受。

步骤3：测试查询性能

-- 开启profiling
SET profiling = 1;

-- 执行优化后的查询
SELECT o.order_id, o.user_id, o.order_time, o.total_amount, 
       o.status, u.username, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.status IN ('pending', 'processing', 'completed')
  AND o.order_time >= '2025-10-01'
ORDER BY o.order_time DESC
LIMIT 20 OFFSET 0;

-- 查看性能
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

测试结果：

• Query_time: 0.48秒 ⬇️ 从28秒降至0.48秒
• Rows_examined: 3,245行 ⬇️ 从785万降至3千
• 性能提升：98.3%

4.2 执行计划对比

优化后的执行计划：

EXPLAIN SELECT ...

改进点：

• ✅ type=range：使用了索引范围扫描
• ✅ key=idx_status_ordertime：使用了新建索引
• ✅ 去掉了Using filesort：直接利用索引排序
• ✅ rows=3245：扫描行数大幅减少

4.3 生产环境部署

步骤1：选择低峰期创建索引

-- 凌晨2点执行（业务低峰）
-- 使用ALGORITHM=INPLACE避免锁表
CREATE INDEX idx_status_ordertime 
ON orders(status, order_time DESC) 
ALGORITHM=INPLACE, LOCK=NONE;

步骤2：监控索引创建进度

-- 查看DDL进度（MySQL 8.0）
SELECT 
    EVENT_NAME,
    WORK_COMPLETED,
    WORK_ESTIMATED,
    ROUND(WORK_COMPLETED/WORK_ESTIMATED*100, 2) AS progress_pct
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/innodb/alter%';

步骤3：验证生产效果

索引创建完成后（耗时约15分钟），立即验证：

-- 强制使用新索引
SELECT /*+ INDEX(o idx_status_ordertime) */ 
       o.order_id, o.user_id, o.order_time, o.total_amount, 
       o.status, u.username, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.status IN ('pending', 'processing', 'completed')
  AND o.order_time >= '2025-10-01'
ORDER BY o.order_time DESC
LIMIT 20 OFFSET 0;

五、效果验证与监控

5.1 性能对比数据

5.2 业务指标改善

• 订单查询接口P99延迟：从30秒降至0.8秒
• 用户投诉量：下降95%
• 数据库服务器CPU负载：从85%降至20%
• 慢查询数量：从日均2000+降至0

5.3 持续监控方案

1. 建立慢查询告警

-- 设置慢查询阈值
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

2. 使用Prometheus + Grafana监控

关键监控指标：

• 查询响应时间趋势
• 索引使用情况
• 慢查询数量统计
• 数据库资源使用率

3. 定期索引优化检查

-- 每周执行，检查索引碎片
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE TABLE_SCHEMA = 'your_database'
  AND STAT_NAME = 'size'
ORDER BY size_mb DESC;

六、进一步优化建议

6.1 分页深度优化

当前方案在大offset时仍有性能问题，针对深度分页场景：

问题SQL：

-- offset很大时性能下降
LIMIT 20 OFFSET 10000;  -- 需要跳过1万条记录

优化方案：使用子查询 + 主键定位

SELECT o.order_id, o.user_id, o.order_time, o.total_amount, 
       o.status, u.username, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.order_id >= (
    SELECT order_id 
    FROM orders 
    WHERE status IN ('pending', 'processing', 'completed')
      AND order_time >= '2025-10-01'
    ORDER BY order_time DESC
    LIMIT 1 OFFSET 10000
)
AND o.status IN ('pending', 'processing', 'completed')
AND o.order_time >= '2025-10-01'
ORDER BY o.order_time DESC
LIMIT 20;

6.2 读写分离架构

针对查询压力大的场景，建议：

1. 主从复制：配置1主2从架构
2. 读写分离：查询走从库，写入走主库
3. 负载均衡：使用ProxySQL或MySQL Router

6.3 缓存策略

对于热点数据，引入Redis缓存：

# 伪代码示例
def get_orders(status, start_date, page):
    cache_key = f"orders:{status}:{start_date}:{page}"
    
    # 先查缓存
    result = redis.get(cache_key)
    if result:
        return json.loads(result)
    
    # 缓存未命中，查数据库
    result = db.query(sql)
    
    # 写入缓存，TTL=5分钟
    redis.setex(cache_key, 300, json.dumps(result))
    
    return result

七、避坑经验总结

7.1 索引设计注意事项

❌ 错误做法：

-- 错误：单字段索引无法覆盖WHERE+ORDER BY
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_ordertime ON orders(order_time);

✅ 正确做法：

-- 正确：复合索引覆盖查询条件
CREATE INDEX idx_status_ordertime ON orders(status, order_time DESC);

7.2 常见问题与解决

问题1：索引创建导致锁表

• 原因：使用了ALGORITHM=COPY
• 解决：改用ALGORITHM=INPLACE, LOCK=NONE

问题2：索引未生效

• 原因：查询条件与索引字段顺序不匹配
• 解决：调整WHERE条件顺序或使用FORCE INDEX

问题3：索引膨胀

• 原因：过多不必要的索引
• 解决：定期分析索引使用情况，删除冗余索引

-- 查找未使用的索引
SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
ORDER BY object_schema, object_name;

八、总结与展望

8.1 本次优化核心要点

1. 精准诊断：通过慢查询日志+执行计划定位问题
2. 科学设计：基于查询特点设计复合索引
3. 小步快跑：先测试验证再生产部署
4. 持续监控：建立监控体系防止性能回退

8.2 性能优化方法论

问题识别 → 执行计划分析 → 优化方案设计 
  ↓
测试验证 → 生产部署 → 效果监控 → 持续优化

8.3 未来优化方向

• 引入分库分表应对数据量持续增长
• 探索列式存储优化OLAP场景
• 结合业务特点优化表结构设计

欢迎 👍点赞✍评论⭐收藏，欢迎指正