1. 场景回顾：慢SQL是怎么暴露出来的
做订单系统的同学应该都有体会：订单表往往是整个业务库里面数据量增长最快、查询模式最多样、索引也最容易“被用坏”的一张表。这个实战项目来自某电商平台的中台订单模块，核心表trade_order存放全渠道的订单主数据，上线一年多已经积累了接近 900 万行，并且以每天 2 到 3 万行的速度增长。一开始只有主键索引和几个简单的单列索引，业务跑起来还算平稳，但到了大促预热期，运营要频繁拉取“某个用户的待付款订单”“某店铺近 7 天已发货订单”这类数据，慢查询日志开始持续报警，最典型的几条 SQL 直接跑到了 800ms 到 1.2s，个别甚至超过 2s。
这个实战项目的目标很明确：把订单表上这几条核心查询从慢 SQL 拉到毫秒级，同时保证新索引方案能扛住接下来的数据量增长。我先把初版表结构和问题 SQL 贴在下面，方便后面逐步拆解。
CREATE TABLE `trade_order` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `order_no` varchar(32) NOT NULL COMMENT '订单编号', `user_id` bigint NOT NULL COMMENT '下单用户ID', `store_id` bigint NOT NULL COMMENT '店铺ID', `status` tinyint NOT NULL COMMENT '订单状态 0待支付 1已支付 2已发货 3已完成 4已取消', `total_amount` decimal(10,2) NOT NULL COMMENT '订单总金额', `pay_amount` decimal(10,2) NOT NULL COMMENT '实付金额', `pay_type` tinyint DEFAULT NULL COMMENT '支付方式', `channel` tinyint DEFAULT NULL COMMENT '渠道来源', `create_time` datetime NOT NULL COMMENT '下单时间', `update_time` datetime NOT NULL COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;1.1 初始索引与最典型的慢查询
表上原本有两个单列索引：idx_user_id (user_id)和idx_create_time (create_time)。这两个索引单独看都能对应一些查询场景，但一旦遇到“多条件筛选 + 排序 + 分页”的组合查询，就完全不够用了。
最典型的一条慢 SQL 是运营后台的“用户订单列表”：
SELECT id, order_no, total_amount, status, create_time FROM trade_order WHERE user_id = 123456 AND status = 1 ORDER BY create_time DESC LIMIT 20 OFFSET 0;这条 SQL 在慢查询日志里平均耗时 900ms，高峰期能到 1.5s。用EXPLAIN看执行计划时，type是ALL，rows估算值是 286 万，这意味着走的是全表扫描。就算idx_user_id存在，优化器在很多情况下也不愿意用它，原因很简单：user_id = 123456这个条件只能把结果集缩小到几千行，但还需要回表过滤status = 1，然后再用create_time做排序。优化器算了一笔账，觉得直接扫全表再过滤再排序，成本可能更低，于是干脆不走索引。
1.2 慢查询背后隐藏的索引设计问题
这个项目的第一个难点不在于“建索引”这个动作，而在于想清楚“订单表的查询模式到底长什么样”。订单表表面上字段不多，但不同角色访问它的姿势完全不一样：
- 用户端查“我的订单”，过滤条件是
user_id+status，排序是create_time； - 运营端查“店铺订单列表”，过滤条件是
store_id+status+ 时间范围，排序是create_time； - 财务对账查“某时间段的支付单”，过滤条件是
channel+pay_type+create_time范围。
同一个表，三种主流查询，最合适的索引并不是某一个“万能索引”，而是一组索引各自覆盖对应场景。很多同学在这一步容易犯的错是：为每一个 where 条件字段都建单列索引，结果优化器还是要做大量的“索引合并”或随机回表，反而更慢。这次实战中也需要把单列索引合并、重新设计成联合索引，并且针对排序字段做特殊处理。
2. 排查与设计思路：先看执行计划再做索引
索引设计最忌讳拍脑袋。拿到慢 SQL 后，我习惯先做三件事：看慢查询日志确认高频 SQL，对每一条 SQL 跑EXPLAIN看执行计划，再用SHOW PROFILE或performance_schema看时间消耗在哪。这套流程走下来，基本上能把问题定性：是扫描行数太大、回表太多，还是排序太重。
2.1 用 EXPLAIN 定位索引失效的根源
以“用户订单列表”这条 SQL 为例，EXPLAIN结果如下：
| 参数 | 值 |
|---|---|
| type | ALL |
| possible_keys | idx_user_id |
| key | NULL |
| rows | 2860000 |
| filtered | 0.02% |
| Extra | Using where; Using filesort |
type=ALL说明是全表扫描，rows接近全表行数，Extra里面的Using filesort是一个关键信号：订单表数据量一旦超过百万，filesort通常意味着要在临时文件或内存中手动排序，耗时自然降不下来。
为什么possible_keys里有idx_user_id，优化器还是选择了全表扫描？这和代价模型有关。idx_user_id筛选出几千个user_id对应的行，但每行都要回表读完整记录、判断status，再参与排序；如果这个用户的订单分布很散，回表成本可能比顺序扫描还高。优化器的判断逻辑并不神秘，关键问题是：现有索引让优化器“两头不讨好”。
这个阶段不要急着删旧索引，也不要直接新建联合索引就完事。正确的做法是先把所有慢 SQL 收集起来，按“查询频率”和“响应时间”排序，挑出影响面最大的 3 到 5 条，再逐一设计索引。我在这套实战中选定了五条 SQL，三条是用户端，两条是运营端，后面所有的索引设计都以它们为基准。
2.2 联合索引选择顺序的决策逻辑
联合索引(user_id, status, create_time)是这条 SQL 最自然的设计，但这里有几个细节需要推敲。
第一，字段顺序。对于WHERE user_id = ? AND status = ? ORDER BY create_time DESC，user_id是最先等值匹配的字段，放在最左边；然后是同为等值条件的status；最后是排序字段create_time。这样设计的好处是：索引既能完成等值过滤，又能让create_time在索引内部有序，排序直接从索引末尾反向扫描即可，完全省掉filesort。
第二，为什么要包含create_time？很多同学会在user_id, status之后停手。如果只建(user_id, status)，过滤后得到的是按user_id和status分组的有序结构，但create_time没有顺序，依然要排序。把create_time加进来之后，索引内部第二列相同的记录天然按第三列排序，ORDER BY create_time DESC就能直接利用索引顺序。
第三，DESC方向问题。MySQL 8.0 之前对索引反向扫描的支持已经足够好，默认索引顺序是升序，倒序查询可以反向扫描，性能不会明显退化。因此一般不需要为倒序单独建降序索引；但如果某条 SQL 要同时处理一个升序、一个降序的排序条件，比如ORDER BY create_time DESC, id ASC，那才需要考虑 8.0 的降序索引特性。这个项目里暂时用不上。
2.3 是否需要覆盖索引
联合索引(user_id, status, create_time)把三个字段都包含了，但查询还会返回order_no、total_amount、id。InnoDB 的二级索引叶子节点自带主键id，所以id不需要额外覆盖；但order_no和total_amount需要回表。如果这几条 SQL 是高频接口，响应时间要求又极苛刻，可以考虑把这两个字段也加进索引形成覆盖索引，比如(user_id, status, create_time, order_no, total_amount)，这样连回表都省了。
但覆盖索引不是越宽越好。索引字段太多会增大存储空间、降低写入吞吐，订单表本身写入频繁，索引过于宽大反而拖累插入和更新。这个项目中我采取的是折中策略：用户端高频查询走覆盖索引，运营端低频率但数据量大的查询只要不产生filesort和全表扫描就足够。
3. 索引落地：从建索引到验证效果
设计阶段确认联合索引方案后，接下来要处理几个实操层面的问题：如何平滑上线、如何验证效果、如何处理历史大表中的锁与耗时。直接在生产库上ALTER TABLE加索引，如果表已经非常大，会引发长时间元数据锁，业务写入会被阻塞。这次项目采用的方法是先在一台只读从库上完成索引创建，再通过主从切换或低峰期操作的方式落地。
3.1 新索引创建语句与字段取舍
以下是最终为用户端查询创建的索引：
ALTER TABLE trade_order ADD KEY idx_user_status_ctime (user_id, status, create_time, order_no, total_amount);这条索引同时覆盖了用户端列表查询的过滤、排序、回表三大需求。加宽到order_no和total_amount后，这条 SQL 基本可以做到索引覆盖扫描，Extra里不出现Using filesort，也不再需要回表。
另一条为运营端查询设计的索引是：
ALTER TABLE trade_order ADD KEY idx_store_status_ctime (store_id, status, create_time);运营端查询通常还会带一个时间范围条件，比如create_time BETWEEN ? AND ?。这种情况下，如果把时间字段放在联合索引的中间位置，范围条件会导致它后面的字段无法用于索引排序。所以这里只保留(store_id, status, create_time)，因为运营查询的store_id区分度足够高，过滤后的数据量不大，排序成本也能接受。
3.2 建索引过程中的注意事项
在几百万行以上的表上建索引，有几个坑需要提前规避。
- 如果业务允许，优先用
pt-online-schema-change或gh-ost这类在线表结构变更工具，避免ALTER TABLE长时间锁表。 - 创建索引后，立刻跑一遍
EXPLAIN，确认key字段是否命中了新索引。 - 别急着删旧索引。旧索引可能在代码里的其他分支还在使用。稳妥做法是先观察一周慢查询日志，确认旧索引没有被任何 SQL 引用后再清理。
3.3 创建索引后的执行计划对比
建完索引后，重新执行之前那条慢 SQL：
EXPLAIN SELECT id, order_no, total_amount, status, create_time FROM trade_order WHERE user_id = 123456 AND status = 1 ORDER BY create_time DESC LIMIT 20 OFFSET 0;执行计划变成：
| 参数 | 值 |
|---|---|
| type | ref |
| possible_keys | idx_user_status_ctime |
| key | idx_user_status_ctime |
| rows | 186 |
| filtered | 100.00% |
| Extra | Using index; Using where |
Using index表示这是一次覆盖索引扫描，不需要回表；Using filesort消失了，排序直接走索引；rows从 286 万降到 186。同一台压力测试机上，这条 SQL 的响应时间从 900ms 降到 8ms 左右。注意：rows估算值是优化器根据索引区分度算出来的，不一定等于实际扫描行数，但它的量级变化能直观反应性能提升效果。
这里需要专门解释一下为什么filtered是 100%：当所有查询列都在索引中时，存储引擎只需要读取索引记录，不需要回表读取聚簇索引里的完整行，因此每一行索引记录都满足条件。这也是覆盖索引的一个额外收益：它让优化器对行数的估算更乐观，间接降低了优化器选择全表扫描的概率。
3.4 多条索引之间的协同与取舍
建索引时还有一个隐蔽问题：如果一条 SQL 命中了多个可用索引，优化器会选择一个，并不会自动把多个索引合并使用。除非符合索引合并的特定优化条件，否则多个单列索引往往不如一个组合索引。因此，我在这个项目中把旧的idx_user_id、idx_create_time的适用场景重新梳理了一遍：
| 旧索引 | 被新索引替代的场景 | 保留原因 |
|---|---|---|
| idx_user_id | 用户订单列表查询 | 可删除，因为idx_user_status_ctime最左前缀已覆盖 |
| idx_create_time | 纯时间范围查询，如“按时间清理过期订单” | 暂时保留，供后台批量清理任务使用 |
在实际操作中，我建议把“纯时间范围查询”这类低频但扫描量大的 SQL 单独评估，不要为了追求索引数量精简而强行删掉所有单列索引。项目中的经验是：先留一周观察期，再决定是否回收。
4. 排序与分页性能优化：不止是加索引
加了联合索引之后，ORDER BY create_time DESC LIMIT 20这类查询基本实现了毫秒级。但订单列表通常还有分页需求，而且运营同学特别喜欢点击“最后一页”，这就带来一个非常经典的深分页问题：LIMIT 100000, 20意味着数据库要扫描并丢弃前 100000 行才能取到目标 20 行，即使有索引，深分页依然很慢。
4.1 深分页优化方案：游标分页
传统的页数分页依赖OFFSET，每次都要从头扫到目标位置。比如：
SELECT id, order_no, total_amount, status, create_time FROM trade_order WHERE user_id = 123456 AND status = 1 ORDER BY create_time DESC LIMIT 100000, 20;这条 SQL 即使走了idx_user_status_ctime，也需要在索引上跳过前 10 万条记录。从测试来看，第 100 页之后的响应时间会逐渐恶化到 200ms 以上，要解决这个问题，最实用的方案是改成“游标分页”：
SELECT id, order_no, total_amount, status, create_time FROM trade_order WHERE user_id = 123456 AND status = 1 AND (create_time < '2024-11-20 10:00:00') ORDER BY create_time DESC LIMIT 20;客户端只需要把上一次查询得到的最后一条create_time作为游标传回来，数据库就能直接从该位置向后扫描，而不是每次从头数 10 万行。实测中，这种方式的响应时间稳定在 10ms 以内，即使翻到一万页也不会劣化。
需要注意一个细节：create_time可能存在重复值。如果两条订单在同一秒创建，游标分页会把重复数据漏掉或重复展示。更稳妥的做法是把游标设计成(create_time, id)复合游标，条件写成：
WHERE user_id = 123456 AND status = 1 AND (create_time < '2024-11-20 10:00:00' OR (create_time = '2024-11-20 10:00:00' AND id < 100023)) ORDER BY create_time DESC, id DESC LIMIT 20;因为id是主键，天然具备唯一性，这个复合游标能精确而稳定地实现翻页。虽然条件稍微复杂一点，但对于“低频但深翻页”的运营后台场景，这是很实用的方案。
4.2 ORDER BY 排序的潜在坑
联合索引(user_id, status, create_time)能够覆盖WHERE user_id = ? AND status = ? ORDER BY create_time DESC，但如果你把WHERE条件里的等值字段去掉一个，比如直接WHERE user_id = ? ORDER BY create_time DESC，排序依然能勉强走索引；如果再加入一个不在索引里的排序字段，比如ORDER BY update_time DESC，那么filesort又会出现。原因是联合索引内部的记录顺序是“先按第一列，再按第二列，最后按第三列”，当第一列或第二列条件缺失时，第三列并不保证全局有序。
因此，凡是看到排序字段突然变了、条件字段少了一个的场景，都要重新审视索引，不能因为“以前建的索引现在也能跑”就忽视filesort隐患。项目里有一条 SQL 让我印象深刻：运营要按“更新时间”拉最近改动的订单，直接在原查询上把ORDER BY create_time改成了ORDER BY update_time，响应时间瞬间从 20ms 飙到 800ms。这就是典型的排序字段与索引不匹配。
5. 常见问题与排查技巧实录
光建索引并不能一劳永逸，实际运维过程中还会遇到各种索引失效、选择错误、统计信息过期的问题。下面这些坑是这个项目里真实踩过的。
5.1 隐式类型转换让索引瞬间失效
订单号order_no是varchar(32)，但某次需求中查询参数直接传了一个数字类型，代码里也没有做类型转换：
SELECT * FROM trade_order WHERE order_no = 1234567890;这条 SQL 表面上可以跑，但执行计划里key = NULL。原因是字符串列与数字比较时，MySQL 会把字符串转换成数字，相当于在索引列上应用了隐式函数，导致索引失效。排查这类问题的思路很简单：看到EXPLAIN中type=ALL但possible_keys非空时，优先检查参数类型是否一致。用CAST(order_no AS CHAR) = ?改写，或者直接让应用层把参数转成字符串再查询。
5.2 范围查询导致联合索引后面的字段失效
运营端查询如果写成：
WHERE store_id = ? AND create_time BETWEEN ? AND ? ORDER BY status那么(store_id, status, create_time)这个索引就帮不上ORDER BY status的忙了。因为create_time的范围条件导致索引中status的顺序被打乱，MySQL 不可能用索引直接完成排序，只能先把满足条件的数据拿出来再排序。这个问题的本质不是有没索引，而是字段在联合索引里的物理排列顺序与查询条件语义不一致。范围条件右侧的字段无法用于索引排序，这在索引设计中属于高频踩坑点。
5.3 冗余索引与重复索引清理
订单表经过多轮迭代后，非常容易出现idx_user_status和idx_user_status_ctime同时存在的状况。由于idx_user_status_ctime的最左前缀已经涵盖了(user_id, status)，两条索引功能几乎重叠，保留两条只会白白增加写入开销。排查时可以用information_schema.statistics查所有索引字段：
SELECT TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) FROM information_schema.statistics WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'trade_order' GROUP BY TABLE_NAME, INDEX_NAME;对照这份清单，把前缀相同的冗余索引逐一标记，观察一周后再删除。
5.4 统计信息过期导致的“错误选择”
有一次索引和 SQL 都没问题，但优化器就是不走新索引，反而选择了老索引。排查后发现，这张表在大量数据变更后没有及时更新统计信息，优化器还拿着旧的行数估算做决策。解决方式有两种：一是跑一次ANALYZE TABLE trade_order;，强制更新统计信息；二是用FORCE INDEX临时验证索引效果，如果FORCE INDEX后性能大幅提升，说明不是索引本身的问题，而是优化器偏航了。
生产环境中，如果经常出现这类情况，优先考虑减少无谓的ANALYZE频率，而是通过定期维护窗口统一处理，否则频繁触发统计信息收集也会带来不必要的系统压力。
6. 索引维护策略与最终效果
这一轮优化做完之后，我对这套方案做了一次更完整的复盘。订单表在这个项目里涉及的主查询路径全部被联合索引覆盖，慢查询数量从一天数千条降到几十条，成交相关接口的 p99 响应时间从 800ms 降到 30ms 以内。效果固然重要，但更值得沉淀的是日常维护索引的方法论。
6.1 上线前必做的核对清单一览
我把这个项目的核对清单整理成了一张表，新接触订单表优化的同学可以照抄：
| 核对项 | 具体检查内容 | 验证方式 |
|---|---|---|
| 查询模式梳理 | 列出所有核心慢SQL，按频率排序 | 慢查询日志 + 业务代码全文搜索 |
| 执行计划复核 | 确认命中索引、rows量级、无filesort | EXPLAIN 关键字对照 |
| 字段顺序检查 | 等值条件在前，排序字段在中后，范围字段谨慎放置 | 手写推导索引扫描顺序 |
| 覆盖索引取舍 | 高频查询是否避免回表 | EXPLAIN Extra 列是否包含 Using index |
| 冗余索引排查 | 联合索引最左前缀是否重复 | information_schema.statistics |
| 分页方案确认 | 深分页是否使用游标 | 压测遍历第1到第500页响应时间 |
这张表每一条都对应一个真实的故障现场，建议放在索引评审的固定模板里。
6.2 订单表后续运维的监控建议
索引上线不是终点。我建议至少保留三类监控：第一类是慢查询日志的实时扫描，监控阈值设置为 200ms，超过就触发告警；第二类是EXPLAIN的定期抽样，关注是否有 SQL 的执行计划发生过变化，比如从ref退化到ALL；第三类是索引使用状态监控，通过sys.schema_unused_indexes找出长期闲置的索引，及时回收。
另外，订单表的数据特性决定了它会持续增长，虽然当前联合索引在 900 万行数据下表现很好，但一旦表规模破亿，可能需要重新评估分区策略。比如按create_time做 RANGE 分区，可以让时间范围查询只扫描对应分区，减少索引扫描范围。这个属于后续扩展话题，但设计索引时就要给分区留出空间，别把联合索引里的分区字段设计得过于复杂。
个人在实际项目中最深的一点体会是：索引优化的核心不是“建索引”，而是“理解查询”。很多慢 SQL 看起来是索引问题，本质上是对业务访问模式的模糊认知。如果你连这个查询会被谁触发、数据分布长什么样、分页极限在哪里都没搞清楚，那建多少索引都是碰运气。建议大家拿到慢 SQL 之后，先花 30 分钟梳理业务链路，再动手建索引，这个时间省不得。
