本文首先以淘天电商交易订单表线上一条非典型慢 SQL 的深入剖析为切入点，示范如何系统地分析与排查慢 SQL；接着详尽归纳了索引分类、B+Tree 与 B‑Tree 的结构差异、B+Tree 高度估算方法、EXPLAIN 与 Query Profile 等诊断工具的使用，以及索引下推与排序的执行流程等索引优化理论；最后结合日常实践经验，提出了适用于大规模线上集群的索引变更 SOP，并总结了常见的慢 SQL 成因与相应的解决策略。

一、前言

交易订单表（tcorder）用于存储集团电商的在线订单记录，该表近60个字段，单个分表近千万行左右（受历史订单迁移影响会上下浮动），平均行长5.4KB，是名副其实的大表，该表的读写性能直接影响上游创单、逆向退款、订单列表等一系列跟订单有关的业务功能，对该表的任何变更都是非常谨慎，需要多方一起综合评估。受业务需求推动，近两年我非常“有幸”各操作了一次订单表索引优化，这里总结下mysql索引优化相关的知识、SOP、之前遇到的慢SQL问题及其对应的优化方法。

二、一个非典型的慢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的explain后执行计划如下：

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

上述猜想可以通过mysql的show profile执行过程来验证，如下：

Creating sort index的耗时和CPU损耗远大于其他几步，确认是filesort导致。为啥出现filesort呢？因为排序条件create_time desc,order_id asc无法利用索引。create_time在索引ind_***_buyerid里面，order_id是主键索引，但是这两个是不同的索引，多字段排序时无法利用索引排序。同样可以通过show 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年做订单列表后置过滤治理时，tm2在订单列表查询条件中新增了一个m_tid字段，该字段用于过滤掉不在手淘上展示的订单，从而实现在DB侧前置过滤掉不在手淘上展示的订单，不用后置到tm2内存过滤。不过m_tid字段没有索引，为保证压测通过，新增了索引 idx_***_mtid(buyer_id,is_main,**************,create_time,*************)。因为create_time是排序条件，不是查询条件，所以新索引把create_time字段放到索引中倒数第三的位置上。基于新索引，DB单实例压测和全链路压测都没问题，但是tm2在放量结束后的第二天上午突然出现了大量订单找不到或者订单重复的舆情，典型的case如下：

在dms上通过force index指定索引的方式复现了上述问题，发现走老索引没问题，走新索引有问题，二者的explain差异如下：

跟DBA沟通确认，走老索引ind_***_buyerid时，因为create_time字段在索引中第二个字段，create_time desc排序可以直接利用索引排序，返回的订单ID是按照索引里面固定的顺序返回。走新索引idx_****_mtid时，因为create_time是索引中倒数第三个字段，create_time desc排序只能走文件堆排，而第一页订单请求和第二页订单请求的文件堆排的排序结果可能不同，即A1-A6的顺序是不确定的，一旦两次排序顺序不同，计算分页时获取的订单就可能出现重复或者订单缺失的问题，比如第一页订单请求时排序是A1,A2,A3,A5,A4,A6,取前面4条，第二页订单请求时排序是A1,A2,A3,A4,A5,A6，取后面2条，即出现A4缺失，A5重复的问题。

因为索引二次变更没有足够的时间窗口，当时决策采用风险相对较小的方案，把排序条件从create_time desc改成create_time desc,order_id asc排序，通过order_id asc来保证创单时间一致的情形下返回的订单列表的稳定。因为二级索引的叶子节点里面本身是包含有order_id字段的，所以不会新增回表查，对DB的影响主要是新增order_id排序本身的损耗，通过DB单实例压测验证这部分损耗对整体CPU水位的影响不明显，后续也通过了双11考验。

明确增加order_id排序的原因后，就制定了一种风险相对较小的优化方式，把非tm2订单列表即不带m_tid字段的分页查询请求中的order_id排序去掉，预期这类SQL应该走老索引ind_***_buyerid，从而保证返回的订单列表的稳定。放量完成后发现不带m_tid字段的分页查询请求也会走到新索引 idx_****_mtid，如下：

查询条件中带有create_time的范围查询，理论来说走老索引ind_***_buyerid更优，可以走索引过滤而不是索引下推过滤。实际测试发现当查询时间是2025-08-01会走老索引ind_***_buyerid：

该用户下创单最早的订单都是大于2025-01-01，初步怀疑create_time大于2025-01-01的查询条件被索引选择器直接忽略。如果把查询时间去掉，也是走新索引 idx_****_mtid，相比老索引可以有更多的字段走索引过滤，符合预期。