MySQL单表2000万数据，3步优化到50ms：真实案例全解析

订单表从8秒优化到50ms，踩过的坑和最终方案，一次说清。

问题背景

电商平台订单表，数据量2000万。运营反馈："导出最近30天的订单报表特别慢，页面经常超时。"

排查发现这个SQL执行了8秒：

SELECT order_id, user_id, amount, status, created_at
FROM orders
WHERE user_id = 10086
  AND status = 'completed'
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;

今天完整记录这个优化过程，从8秒到50ms的三步走。

第一步：问题诊断

1.1 查看执行计划

EXPLAIN SELECT order_id, user_id, amount, status, created_at
FROM orders
WHERE user_id = 10086
  AND status = 'completed'
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;

结果：

type	key	rows	Extra
ALL	NULL	19823451	Using where; Using filesort

诊断结论：

type=ALL：全表扫描，没走索引
rows=19823451：扫描了近2000万行
Using filesort：额外的排序操作

1.2 分析慢查询日志

# 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

# 查看慢查询
mysqldumpslow -s t /var/lib/mysql/slow.log

确认这条SQL是TOP 1的慢查询，日均执行3000次。

1.3 表结构分析

SHOW CREATE TABLE orders;

CREATE TABLE `orders` (
  `order_id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` varchar(20) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  -- 其他30+字段省略
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB;

问题很明显：除了主键，没有任何索引。

第二步：三阶段优化

阶段一：创建复合索引（8s → 150ms）

根据WHERE条件，创建复合索引：

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);

索引设计原则：

等值条件在前：user_id、status
范围条件在后：created_at
顺序很重要：把选择性高的字段放前面

再看执行计划：

EXPLAIN SELECT ...

type	key	rows	Extra
range	idx_user_status_time	2341	Using where

type从ALL变成range
rows从2000万降到2341
执行时间：8s → 150ms

阶段二：覆盖索引（150ms → 80ms）

观察SELECT的字段：

SELECT order_id, user_id, amount, status, created_at

其中order_id、user_id、status、created_at已经在索引中，但amount需要回表查询。

创建覆盖索引：

ALTER TABLE orders DROP INDEX idx_user_status_time;
ALTER TABLE orders ADD INDEX idx_user_status_time_amount
  (user_id, status, created_at, amount);

执行计划：

type	key	Extra
range	idx_user_status_time_amount	Using where; Using index

Using index表示覆盖索引生效，无需回表。

执行时间：150ms → 80ms

阶段三：分区表（80ms → 50ms）

2000万数据量还是太大，考虑按月分区：

ALTER TABLE orders PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p2023_10 VALUES LESS THAN (TO_DAYS('2023-11-01')),
    PARTITION p2023_11 VALUES LESS THAN (TO_DAYS('2023-12-01')),
    PARTITION p2023_12 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

分区裁剪效果：

查询created_at >= '2024-01-01'时，MySQL只扫描p2024_01、p2024_02、p_future三个分区，跳过历史数据。

EXPLAIN PARTITIONS SELECT ...

partitions	type	rows
p2024_01,p2024_02,p_future	range	856

执行时间：80ms → 50ms

第三步：生产环境注意事项

3.1 在线DDL

2000万数据加索引需要锁表，生产环境使用pt-online-schema-change：

pt-online-schema-change \
  --alter "ADD INDEX idx_user_status_time_amount(user_id, status, created_at, amount)" \
  --execute \
  D=ecommerce,t=orders

原理：

创建影子表，结构与原表一致
在影子表上添加索引
通过触发器同步增量数据
原子性切换表名

3.2 索引字段顺序

复合索引遵循最左前缀原则：

INDEX idx (a, b, c)

-- 能用到索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- 不能用到索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

3.3 分区维护

每月初添加新分区，避免数据插入到p_future：

-- 添加新分区
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
    PARTITION p2024_03 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 定期清理历史分区
ALTER TABLE orders DROP PARTITION p2023_10;

优化效果验证

Benchmark测试

使用sysbench模拟生产负载：

sysbench oltp_read_only \
  --mysql-host=127.0.0.1 \
  --mysql-user=root \
  --mysql-password=xxx \
  --mysql-db=ecommerce \
  --tables=1 \
  --table_size=20000000 \
  --threads=32 \
  --time=60 \
  run

优化前后对比：

指标	优化前	优化后	提升
QPS	12	1850	154倍
平均响应时间	8200ms	52ms	157倍
P99响应时间	12000ms	89ms	134倍

成本分析

方案	效果	成本
复合索引	8s→150ms	索引空间约2GB
覆盖索引	150ms→80ms	索引增加amount字段
分区表	80ms→50ms	需要应用层配合管理分区

常见问题

Q1：为什么不用created_at作为第一个索引字段？

范围查询会中断索引匹配。如果created_at在前：

INDEX idx (created_at, user_id, status)
WHERE created_at >= '2024-01-01' AND user_id = 10086

只能用到created_at部分，后面的user_id无法利用索引。

Q2：分区表有什么缺点？

跨分区查询性能下降
分区键必须包含在主键/唯一键中
需要定期维护分区
某些MySQL功能受限（如外键）

Q3：什么时候考虑分库分表？

单表超过5000万行
单库超过500GB
需要突破单机QPS上限

分区表是"伪分表"，数据仍在同一台机器，遇到瓶颈还是要考虑真正的分库分表。

总结

这次优化的核心思路：

诊断先行：EXPLAIN + 慢查询日志定位问题
索引优化：复合索引 + 覆盖索引，解决80%的问题
架构优化：分区表减少扫描范围

记住一个原则：先优化查询，再优化架构。很多性能问题，一个合适的索引就能解决。

面试考点

Q：如何优化一条慢SQL？

标准回答框架：

定位问题：EXPLAIN分析执行计划，看type、key、rows、Extra
索引优化：根据WHERE/ORDER BY/GROUP BY设计复合索引，考虑覆盖索引避免回表
SQL改写：避免SELECT *，避免在索引列上使用函数
架构优化：分区表、读写分离、分库分表

Q：复合索引的字段顺序怎么定？

等值条件在前，范围条件在后
选择性高的字段在前
排序字段尽量与索引顺序一致

有类似的优化案例想讨论？欢迎在评论区留言。