交易订单表MySQL索引优化实践：从慢SQL分析到变更SOP

本文将以一个真实的线上慢 SQL 为切入点，系统性地分享如何分析、排查并最终优化交易订单表的索引性能，涵盖理论知识回顾与实践操作 SOP。

一个非典型的慢SQL

7月份对订单表进行慢 SQL 梳理时，发现分页查询类请求较多，典型 SQL 如下：

select order_id from tcorder where is_main = 1
  and buyer_id=2********5
  ***************
  order by create_time desc,order_id asc limit 0,10

该类慢 SQL 的统计信息如下：

字段	结果	说明
sql_id	71d14de3	SQL Parttern ID，基于原始SQL文本通过BKDR的HASH算法生成
execute_count	3106	慢SQL总次数
cost	2017	平均执行耗时，单位ms
send_row_count	10	平均返回行数
examined_row_count	8705	平均扫描行数
logical_read	40023	平均逻辑读，即SQL处理过程中读取的数据页和索引页的数量，包含从Innodb Buffer Pool和磁盘读取两部分的，数据页和索引页的大小都是16kb
physical_sync_read	5174	平均同步物理读，即SQL处理过程中从磁盘读取的数据页和索引页的数量

该慢 SQL 的执行计划如下：

图1：慢SQL执行计划，显示使用了文件排序（Using filesort）

据此分析，该 SQL 实际执行过程中命中了索引 ind_***_buyerid，其中 buyer_id 走索引过滤（key_len等于8），is_main 等大部分字段通过索引下推过滤（Using index condition），只有两个不在索引中的字段通过回表查询过滤（Using where）。这两个字段满足条件的比例通常不超过 1%，因此即使回表，扫描行数也不会大幅上涨。问题的可能原因就在于 Using filesort。这种方式需要把所有满足条件的记录都过滤出来再排序，排序完成后再取前 10 条，导致扫描行数和逻辑读异常。

上述猜想可以通过 MySQL 的 show profile 来验证执行过程：

图2：使用 SHOW PROFILE 分析各阶段耗时，Creating sort index 阶段消耗巨大

Creating sort index 的耗时和 CPU 损耗远大于其他步骤，确认是 filesort 导致。为什么会触发 filesort 呢？因为排序条件 create_time desc, order_id asc 无法利用索引。create_time 在索引 ind_***_buyerid 中，order_id 是主键索引，但这两个是不同的索引，多字段排序时无法利用索引排序。同样可以通过 show profile 来验证：

图3：对比仅按 create_time 排序和使用双字段排序的 Profile 结果，后者耗时显著增加

对比同一个 SQL，仅按 create_time desc 排序（query 3）和按 create_time desc, order_id asc 排序（query 2），可以发现前者利用索引排序（Sorting result），耗时极低（0.000019秒）；后者走文件堆排（Creating sort index），耗时很高（0.094799秒）。

去掉order_id排序？

为了解决上述慢 SQL，最直接的办法似乎是去掉 order_id 排序。但在决定之前，需要先回顾增加 order_id 排序的背景。

24年进行订单列表后置过滤治理时，业务方在查询条件中新增了一个 m_tid 字段用于过滤。为保证压测通过，新增了索引 idx_***_mtid (buyer_id, is_main, **************, create_time, *************)。由于 create_time 是排序条件而非查询条件，所以在新索引中被放在了倒数第三位。

基于新索引，DB 单实例压测和全链路压测均通过。但在放量结束后的第二天上午，突然出现了大量订单找不到或订单重复的舆情，典型 case 如下：

图4：订单合并后分页查询出现缺失和重复的示意图

通过 force index 指定索引的方式复现了上述问题：走老索引 ind_***_buyerid 没问题，走新索引 idx_****_mtid 有问题。二者的 EXPLAIN 差异如下：

*图5：强制使用老索引 ind__buyerid 的执行计划

图6：强制使用新索引 idx__mtid 的执行计划，出现了 Using filesort*

与 DBA 沟通确认：走老索引时，因为 create_time 字段在索引的第二位，create_time desc 排序可以直接利用索引排序，返回的订单 ID 顺序固定。走新索引时，因为 create_time 是索引中靠后的字段，create_time desc 排序只能走文件堆排。而第一页和第二页查询的文件堆排结果可能不同，即 A1-A6 的顺序不确定。一旦两次排序顺序不同，计算分页时获取的订单就可能出现重复或缺失。

由于当时没有足够的时间窗口进行二次索引变更，决策采用风险较小的方案：将排序条件从 create_time desc 改为 create_time desc, order_id asc，通过 order_id asc 来保证创单时间一致时返回订单列表的稳定性。因为二级索引的叶子节点包含 order_id 字段，所以不会新增回表查询，对 DB 的主要影响是排序本身的损耗，压测验证这部分损耗对整体 CPU 水位影响不明显。

明确了增加 order_id 排序的原因后，我们制定了一个风险较小的优化方式：把不带 m_tid 字段的分页查询请求中的 order_id 排序去掉，预期这类 SQL 会走老索引 ind_***_buyerid，从而保证返回列表的稳定。

但放量完成后发现，不带 m_tid 字段的查询有时也会走到新索引 idx_****_mtid：

图7：查询条件中不带 m_tid，但仍可能选择新索引

查询条件中带有 create_time 的范围查询时，理论走老索引更优（可走索引过滤而非索引下推过滤）。实际测试发现，当查询时间为 2025-08-01 会走老索引，但查询时间为 2025-01-01 则不会，初步怀疑 create_time 大于 2025-01-01 的条件被索引选择器忽略。如果去掉时间条件，也是走新索引，相比老索引可以有更多字段走索引过滤，这符合预期。

图8：指定较晚的 create_time 条件，优化器选择了老索引

可以通过 INFORMATION_SCHEMA.INDEX_STATISTICS 表查看走各个索引查询返回的记录数：

图9：查询各索引实际扫描行数的统计信息

基于上述统计结果可知，实际查询中有很多 case 依然走新索引 idx_****_mtid。为了彻底解决新索引带来的排序问题，同时满足业务新增查询字段加索引的需求，我们决策再次调整订单表的索引。

索引知识回顾

在动手优化前，有必要系统回顾一下相关的数据库索引优化理论知识。

索引分类

图10：MySQL索引的多种分类方式

B+Tree数据结构

图11：B+Tree 多层级结构示意

B+Tree 是一个多叉树，一个节点的子节点数目的最大值就是 B+Tree 的阶。通过增加子节点数量，可以大幅增加单层节点数，从而降低整体树高度，减少查询过程中的磁盘访问次数，提升读写效率。

B+Tree 中单个节点对应一个 16KB 的物理 Page。Page 的 Fil Header 中会维护两个链表指针，指向相邻的 Page，使每一层的 Page 组成一个横向的双向链表，从而支持双向遍历，提升列表查询效率。Page 内部包含多条按索引列值升序排列的 Record（用户记录），以及用于提升查找效率的 Directory slot。

B+Tree 的节点分为根节点、叶子节点和中间的非叶子节点。对于二级索引，其叶子节点记录的 Data 部分是二级索引各个字段的值加上主键索引字段的值，如下图所示：

图12：二级索引在叶子节点存储索引列与主键值

二级索引的顺序按照多个索引字段从左往右依次比较。这导致只有第一个字段是有序的，后面的字段实际存储是无序的。只有当二级索引前面字段的值确定时，后面的第一个字段才是有序的。

B+Tree的高度测算

单个 Page 包含的 Record 数量取决于单个 Record 的大小。以 tcorder 表为例分析：

对于主键索引（order_id, BIGINT 类型）：

• 非叶子节点 Record 大小约 8+5(Header)+4(PageNo指针)=17字节。单 Page 最大 Record 数约为 (16*1024-200)/17 ≈ 936。
• 叶子节点大小为单行数据约 5.4KB + 20(Header) ≈ 5.5KB，单 Page 只能存放约 2 条记录。

据此可估算不同高度 B+Tree 支持的最大记录数：	高度	非叶子节点数量	叶子节点数量	最大记录数
1	0	1	2
2	1	936	936*2=1872
3	1+936=937	936*936=876096	9369362=1752192
4	1+936+876096=877033	936936936=820,025,856	936936936*2=1,640,051,712

对于二级索引 ind_***_buyerid (长度 58)：

• 非叶子节点 Record 长度约 58+5+4=67字节。单 Page 最大 Record 数约为 (16*1024-200)/67 ≈ 241。
• 叶子节点 Record 长度约 67+8(主键)+20(Header)=95字节。单 Page 最大 Record 数约为 (16*1024-200)/95 ≈ 170。

据此估算：	高度	非叶子节点数量	叶子节点数量	最大记录数
1	0	1	170
2	1	241	241*170=40970
3	1+241=242	241*241=58081	241241170=9,873,770
4	1+241+58081=58323	241241241=13,997,521	241241241*170=2,379,578,570

通过上述测算可知：

1. tcorder 单表量级约 800w，主键索引 B+Tree 高度约为 4，二级索引高度约为 3。
2. 行长和索引长度增加会导致单个 Page 容纳的 Record 减少，进而增加 B+Tree 高度，降低读写效率。因此需限制单表字段和索引字段数量。
3. 对于二级索引，建议索引长度不超过 50，单表行数控制在 1000w 以内，使 B+Tree 高度不超过 3。
4. 对于主键索引，建议行长不超过 1.5KB，单表行数控制在 1000w 以内，使 B+Tree 高度不超过 3。

EXPLAIN 深度解读

EXPLAIN 是分析 SQL 执行计划的核心工具。除了表格模式，EXPLAIN FORMAT=JSON 能提供更详尽的信息，包括成本估算、索引使用详情、是否使用临时表、排序方式等。

Query Profiler 使用

MySQL 的 Query Profiler 是一个方便的查询诊断工具，可以获取一条 SQL 在整个执行过程中不同阶段的资源消耗情况（耗时、CPU、IO、上下文切换等）。基本用法如下：

命令	说明
SHOW VARIABLES LIKE 'profiling'	查看是否开启 profile 功能，默认关闭
SET profiling = 1	开启 Query Profiler 功能
SHOW PROFILES	查看当前保留的采集了 profile 信息的 SQL 列表
SHOW PROFILE [type] FOR QUERY n	查看指定 SQL 的 profile 详细信息，type 可以是 ALL、CPU、BLOCK IO 等

需要注意的是，Query Profiler 在 MySQL 8.0 及以上版本已被废弃，可使用功能更强大的 EXPLAIN ANALYZE 来分析 SQL 的实际执行过程。

索引下推

索引下推（Index Condition Pushdown, ICP）是 MySQL 5.6 引入的优化。在没有 ICP 时，存储引擎根据索引查找记录，然后回表获取完整行，再交给 Server 层进行 WHERE 条件过滤。开启 ICP 后，部分 WHERE 条件（特别是索引中包含的列）会被“下推”到存储引擎层进行过滤，从而减少不必要的回表操作。

ICP 默认开启。为了充分发挥其效果，应尽可能将区分度大的字段加入二级索引，以大幅降低回表次数。

排序优化

MySQL 处理 ORDER BY 时，会先判断能否使用索引排序。判断标准是：排序字段的顺序必须与索引中字段的顺序完全一致，且所有列的排序方向（升序/降序）也要一致，或者正好相反。

不能使用索引排序时，EXPLAIN 就会显示 Using filesort。filesort 是一个统称，如果排序数据量小则在内存（sort buffer）中快速排序；如果数据量大则借助磁盘临时文件进行多路归并排序。如果 ORDER BY ... LIMIT m,n，则会优化为基于内存的优先级队列排序，但如果 m+n 条数据超过了 sort buffer 容量，仍会退化到使用磁盘临时文件。

filesort 使用的排序算法（快速排序、优先级队列、多路归并）在遇到排序键值相同的记录时，输出的顺序可能不稳定。这并非 MySQL 的 bug，SQL 规范并不要求 ORDER BY 在键值相等时保持输入顺序。为保证排序结果的绝对稳定，必须使用索引排序，或者在排序条件中额外加入具有唯一性的列（如主键）。

图13：MySQL 中 filesort 算法的执行流程

索引变更SOP（标准操作流程）

基于大规模线上集群的实践经验，我们总结出一套适用于核心表的索引变更 SOP。

1. 分析慢SQL类型和根因

1. 从监控系统捞取近期慢 SQL Pattern，识别主要调用来源。
2. 获取具体的慢 SQL 样本及其 traceId。
3. 使用 EXPLAIN 分析 SQL，并结合慢 SQL 统计信息初步定位原因。
4. 若非索引问题，需进一步排查当时 DB 及应用的各项指标（CPU、连接数、GC等）。
5. 与业务方沟通，优先考虑通过调整查询条件或接口来解决慢 SQL。
6. 明确必须通过索引变更解决的慢 SQL 类型和量级，作为后续验证依据。

2. 分析全量查询SQL构成

1. 分析目标表近期所有高频查询的 Pattern、字段及占比。
2. 明确各查询字段的业务含义、用法和区分度。

3. 确认索引字段顺序

联合 DBA、SRE、业务方负责人共同评审，确定新索引的字段顺序。参考原则：

1. 分库分表字段排第一位：如 buyer_id，区分度最大。
2. 排序字段排第二位：如 create_time，避免 filesort。
3. 高频且高区分度字段排中间。
4. 高频但低区分度字段排最后。
5. 低频字段：根据实际慢 SQL 情况决定是否加入索引。

4. 提交结构设计工单

1. 在 DMS 等平台提交工单，先在日常环境验证语法。
2. 注意DRC同步：DDL 会阻塞同步，需监控延迟，必要时调整执行间隔。
3. 选择执行时间：必须在业务低峰期（如凌晨）执行，并设置好停止时间。
4. 影子表：压测用的影子表需同步创建索引。

5. 线上灰度

1. 分批灰度：先选择 1% 左右的流量（按分库或实例维度）进行变更。必须设置为“暂停等待人工干预”模式。
2. 串行执行：DMS 通常按分库分表串行执行变更，拉长观察期，降低风险。
3. 索引静默：第一批变更完成后，联系 DBA 静默老索引（使其不可见但保留）。新索引有问题时可快速回退。
4. 验证与观察：验证新索引生效后，线上观察至少一周。配置监控，对比已变更和未变更实例的 RT 及慢 SQL 量级变化。

6. 压测验证

1. 完成影子表索引变更与老索引静默。
2. 准备覆盖相关业务场景的压测模型，量级参考大促。
3. 对比新老索引实例在压测下的 CPU、连接数、逻辑读、RT 等指标差异。

7. 全量验证

1. 线上分多批逐步灰度（如 1% -> 5% -> 20% -> 全量），每批观察一周，优先灰度慢 SQL 多的库。
2. 全量完成后，完成影子表全量变更。
3. 经历大促压测验证后，再提工单正式删除已静默的老索引。

常见的慢SQL问题与治理

这里指的慢 SQL 是应用侧感知到的 RT 过长。更新类慢 SQL 常由行锁等待导致；查询类慢 SQL 的核心特征是逻辑读和物理读过高。治理的核心在于利用索引的二分查找特性降低逻辑读。

问题原因	说明	治理措施
高区分度字段无索引	需要回表遍历大量记录，产生大量随机IO。	为过滤性强的字段添加索引。
索引字段顺序不当	范围查询字段在前，等值查询字段在后，导致无法有效利用索引缩小扫描范围。	调整索引字段顺序，等值字段在前，范围字段在后。
低效的 LIKE 查询	LIKE ‘%xxx’ 无法利用索引前缀匹配。	更改查询方式，或考虑使用全文索引。
查询条件带函数	如 WHERE IFNULL(column, 0) = 1，优化器无法在计划阶段确定值，无法使用索引。	重写查询条件，避免对索引列使用函数。
优化器选错索引	统计信息不准确或存在多个相似索引时可能发生。	合理设计索引，减少冗余；极端情况下可使用 FORCE INDEX。
filesort 排序	排序数据量大，消耗大量内存/磁盘，且可能因算法导致分页结果不稳定。	优化索引以支持排序，或重写业务逻辑。
反向遍历索引	查询的数据在索引左侧却使用 DESC 排序，导致需遍历大部分索引记录。	根据业务数据分布特点调整排序方向。
大结果集的 COUNT	COUNT(*) 需要扫描大量满足条件的记录。	使用 COUNT(...) LIMIT n 进行近似计数，或取消非精确计数需求。
应用端问题	GC、网络抖动等导致应用感知 RT 高。	治理应用 GC，排查网络问题。
数据库端资源瓶颈	磁盘 IO 瓶颈、CPU 过高、Buffer Pool 冷数据等。	升配、限流保护、推动慢 SQL 治理。

希望通过本文从具体案例到理论梳理，再到标准化操作流程的分享，能为大家在处理类似数据库性能问题时提供一套可循的方法。更多技术实践欢迎在云栈社区交流探讨。