从3s到180ms：电商订单复合索引优化实战

问题背景：百万级订单列表查询的性能瓶颈

在绝大多数电商平台的运营后台、C端个人中心场景中，订单列表查询是最高频的SQL场景之一：常见的查询条件包含用户ID、订单状态、支付时间区间，同时需要按支付时间倒序分页，数据量达到百万级甚至千万级之后，未经过优化的查询经常出现3秒以上的响应延迟，严重影响用户体验。某生鲜电商平台的订单表数据量达到120万行时，运营反馈后台查询用户订单列表经常超时，排查后发现开发初期为了图省事，针对查询涉及的3个字段分别建立了单值索引：idx_user_id、idx_status、idx_pay_time，执行多条件复合查询时MySQL触发了索引合并机制，需要同时扫描3个索引再做结果交集，最终还要回表读取整行数据，单次查询扫描行数超过8万行，高峰期数据库CPU使用率经常突破80%。

方案对比：三种索引策略的实测效果

针对该场景我们测试了三种主流索引方案的实际表现，各项指标对比如下：

全表扫描方案：完全不建任何二级索引，查询时逐行匹配条件，单次查询耗时5.7s，扫描全表120万行，完全无法满足业务要求，仅作基准测试参考。
多单值索引+索引合并方案：也就是项目初期的实现方案，查询平均耗时2.9s，索引总占用存储空间超过120MB，每次查询需要合并多个索引结果后再回表，IO开销极高，高峰期很容易出现慢查询堆积。
联合覆盖索引方案：按照最左匹配原则设计符合查询模式的复合索引，同时把高频返回字段加入索引实现覆盖索引效果，优化后平均查询耗时仅180ms，索引总占用存储空间不到40MB，性能提升超过16倍。显然第三种方案是该场景下的最优解，既满足查询性能要求，又能减少冗余索引的存储开销，降低写入链路的索引维护成本。

实操步骤与代码示例

我们基于MySQL 8.0环境完成完整的优化流程，所有SQL语句均可直接运行：

1. 基础表结构与原始慢查询复现

首先创建测试订单表，插入120万条测试数据，复现原始慢查询场景：

sql

-- 创建订单表
CREATE TABLE `order_list` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `order_sn` varchar(32) NOT NULL COMMENT '订单号',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT '订单状态：0待支付1已支付2已完成3已取消',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  -- 原始冗余单值索引
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_pay_time` (`pay_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 复现原始慢查询：查询指定用户2024年以来已支付的订单，按支付时间倒序分页
EXPLAIN SELECT id,order_sn,status,pay_time,amount 
FROM order_list 
WHERE user_id = 10086 AND status = 2 AND pay_time > '2024-01-01 00:00:00' 
ORDER BY pay_time DESC LIMIT 20;

执行EXPLAIN后可以看到执行计划的type字段显示为index_merge，Extra字段显示Using intersect(idx_user_id,idx_status,idx_pay_time); Using where; Using filesort，说明MySQL正在做索引合并，同时还触发了文件排序，这就是查询慢的核心原因。

2. 符合最左匹配的联合覆盖索引设计

根据查询条件的特征：user_id和status都是等值查询，pay_time是范围查询同时还要做排序，我们按照「等值字段放最左、范围字段放最后」的规则设计联合索引，同时把查询需要返回的order_sn、amount字段加入索引，实现覆盖索引避免回表：

sql

-- 删除原有冗余单值索引
DROP INDEX idx_user_id ON order_list;
DROP INDEX idx_status ON order_list;
DROP INDEX idx_pay_time ON order_list;

-- 新建联合覆盖索引：顺序严格遵循最左匹配原则
CREATE INDEX idx_user_status_pay ON order_list(user_id,status,pay_time,order_sn,amount);

再次执行EXPLAIN查看执行计划，type字段变为ref，扫描行数从8万行骤降到47行，Extra字段显示Using index; Using where; Backward index scan，说明直接通过索引就能拿到所有需要的数据，不需要回表，也不需要额外的filesort排序，直接利用索引的有序性完成了pay_time倒序。

3. 冗余索引批量清理

MySQL 8.0内置的sys库提供了两个系统视图，可以直接查询出冗余索引和长期未使用的索引，避免人工排查遗漏：

sql

-- 查询所有冗余索引：如果(a,b)联合索引存在，那么a单值索引就是冗余索引
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = '你的库名';

-- 查询超过7天未被使用过的索引
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = '你的库名';

在本场景中，新建的联合索引第一个字段是user_id，完全可以替代原来的idx_user_id单值索引，清理后可以减少近15MB的索引存储空间，降低订单写入时的索引维护开销。

避坑总结

我们在数十个电商订单场景的索引优化落地过程中，总结出3个最容易踩坑的实践注意事项：

联合索引顺序错误导致最左匹配失效：常见错误是把范围查询字段放在等值字段前面，比如把pay_time放在status前面，那么pay_time之后的所有索引字段都无法匹配，最终导致索引失效扫描行数暴涨。修正方法是严格遵循等值条件在前、范围条件在后的排序规则，排序字段可以和范围字段复用同一个索引位置，避免额外排序开销。
盲目全字段覆盖索引导致写入性能暴跌：很多开发者为了追求极致查询性能，把订单表所有字段都加入联合索引中，最终索引体积膨胀到接近表数据体积的80%，订单提交场景的写入耗时从10ms暴涨到100ms以上。修正方法是仅把高频查询返回的短字段加入覆盖索引，大文本、长备注等低频查询字段不要加入索引，通过主键回表读取即可。
未观察全业务周期就清理冗余索引引发故障：不少团队直接上线后立刻清理冗余索引，很容易把低频但关键的后台统计场景使用的索引误删，导致凌晨跑批任务全表扫描拖垮数据库。修正方法是先通过监控观察至少7天的业务全周期，确认索引没有任何访问记录后，再使用pt-online-schema-change工具在线安全删除索引，避免锁表影响业务。通过这套完整的索引设计与冗余清理流程，我们可以把绝大多数电商订单复合查询的耗时控制在200ms以内，同时把二级索引的总存储空间降低60%以上，大幅提升数据库整体负载能力。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

从3s到180ms：电商订单复合索引优化实战 ​

问题背景：百万级订单列表查询的性能瓶颈 ​

方案对比：三种索引策略的实测效果 ​

实操步骤与代码示例 ​

1. 基础表结构与原始慢查询复现 ​

2. 符合最左匹配的联合覆盖索引设计 ​

3. 冗余索引批量清理 ​

避坑总结 ​