本文首先以淘天电商交易订单表线上一条非典型慢 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，相比老索引可以有更多的字段走索引过滤，符合预期。

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

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

四、索引知识回顾

磨刀不误砍柴工，先系统回顾总结下相关的知识点，打好理论基础。

索引分类

B+Tree数据结构

找了一圈资料，发现只有下面这张图是比较清晰和准确的，下面基于这个图来做补充。

B+Tree是一个多叉树，即子节点的数量可以大于2，与之相对的红黑树最多只有2个子节点。B+Tree中一个节点的子节点数目的最大值就是B+Tree的阶，比如8阶B+Tree最多有8个子节点。B+Tree通过增加子节点的数量可以大幅增加单层B+Tree的节点数（比如3层8阶的B+Tree 第三层的节点数等于8*8*8=252，3层的红黑树第三层的节点数等于2*2*2=8），从而大幅降低整体的树高度，减少查询过程中的磁盘访问，提升读写效率，比如图中树的高度是3，从根节点到叶子节点只需要3次磁盘访问即可返回。

B+Tree中单个节点实际对应一个16kb的物理Page，Page的Fil Header中会维护两个链表指针，指向相邻的两个Page，通过这两个指针，每一层的Page都会组成一个横向的Page双向链表，从而支持双向遍历，提升列表查询效率。每个Page中会包含多条索引记录Record，各个Record在逻辑上基于索引列值升序排列并组成一个单向链表，这些称为User Records。Page内预留了两个固定位置固定值的System Records，在链表头的Infimum记录以及在链表尾的Supremum记录，Infimum指向索引值最小的Record，Supremum通常为null。Page内部还会维护一组Directory slot，每个Directory slot指向一个Record记录，两个Directory slot对应的Record记录中间会相隔4到8个Record记录，实际在Page内部查找满足条件的Record记录时先根据Directory slot做二分查找找到相邻的两个slot记录，再顺序遍历这两个slot中间的Record记录，从而提升查找效率。

B+Tree中的节点分为三种，根节点、叶子节点和中间的非叶子节点，其中主键索引根节点对应PageNo默认是3，可以根据PageNo和PageSize计算出文件偏移量，再根据偏移量从idb文件中读取对应PageSize的字节数据，再按照约定的数据存储格式即可解析出来存储的原始数据。三种节点包含的Record的Header部分基本一致，主要区别是Data部分。其中根节点和非叶子节点记录的Data是索引字段的值和指向下层子节点的PageNo，如果是主键索引则只有一个索引字段，如果是二级索引则可能存在多个索引字段，注意索引字段的值是下层子节点中所有Record中最小的且每个Record都对应一个下层节点，比如图中Page4 Record为0的记录指向Page6，包含0和1两条记录，Record为2的记录指向Page7，包含2和3两条记录。对于叶子节点包含的Record，如果是主键索引则Data是索引字段的值以及其他的非索引字段的值，这两者加起来构成完成的一行数据，比如图中的A,B,C,D,E,F,G,H就是非索引字段的取值；如果是二级索引则Data是二级索引各个字段的值加上主键索引字段的值，如下图：

对应的原始数据如下：

二级索引顺序是按照多个索引字段的顺序从左往右依次比较，跟比较两个字符串的顺序的规则基本一致，比如上面的case，先比较create_time，create_time一致的情况下再比较is_main，is_main一致的情况下再比较status，这样设计会导致二级索引只有第一个字段是有序的，其他的字段实际存储都是无序的；如果二级索引前面的字段的值是确定的，则后面的第一个字段的存储也是有序的。

二级索引的Root Page的PageNo是通过索引的元数据表INFORMATION_SCHEMA.INNODB_SYS_INDEXES 维护的，如下：

B+Tree的高度测算    

单个Page包含的Record记录数取决于单个Record的大小，以tcorder为例分析，主键索引是order_id，BIGINT类型，占8个字节，非叶子节点的Record的大小约为8+5（Header）+4(子节点PageNo指针)=13，单个Page包含的最大的Record数量为（16*1024-200（Header））/13=952。叶子节点的大小为单行的行长5.4kb+20（Header）=5.5kb，单个Page包含的最大的Record数量是2。据此不同高度的B+Tree支持的最大记录数如下：

索引ind_***_buyerid的长度是58，非叶子节点的Record的长度约是58+5+4=67，单个Page包含的最大的Record数量为（16*1024-200（Header））/67=241。叶子节点的Record的长度是67+8（主键索引）+20（Header）=95，单个Page包含的最大的Record数量是（16*1024-200（Header））/95=170个。据此不同高度的B+Tree支持的最大记录数如下：

通过上述测算可知：

1. tcorder单表量级约800w，按照上面测算，主键索引B+Tree的高度是4，二级索引B+Tree的高度是3。

2. 在存储同等量级的数据的前提下，行长和索引长度增加会导致单个Page包含的Record数量减少，导致B+Tree的高度增加，导致读写效率下降，所以需要限制单表的字段数量和索引的字段数量。

3. 对于二级索引，索引长度不超过50，单表控制在1000w行以内，B+Tree的高度不超过3。

4. 对于主键索引，行长不超过1.5kb，单表控制在1000w行以内，B+Tree的高度不超过3。

B-Tree的数据结构

B+Tree是从B-Tree改进而来，两者的区别如下：

同样的数据，B-Tree的数据结构如下：

explain说明

explain的输出默认是表格模式的，还有一个json格式，输出的信息更详尽，通过explain format=json SQL的方式获取，以这个SQL为例：

SELECT
    ********
FROM
    (
        SELECT
            order_id
        FROM
            `tcorder`
        WHERE
            is_main = 1
            AND buyer_id = 1*******1
            **************
        ORDER BY
            create_time DESC,
            `order_id` ASC
        LIMIT
            0, 10
    ) t2
JOIN `tcorder` t1 ON t2.order_id = t1.order_id

对应的表格输出如下，输出的表的顺序并不是实际的执行顺序，而是先外层表再内层表。

对应的json输出如下：

{
  "query_block": {
    "_comment": "最外层的SQL的执行成本",
    "select_id": 1,
    "cost_info": {
      "query_cost": "17.25",
      "_comment": "预估的成本值，不是预估耗时，只能用作横向比较"
    },
    "nested_loop": [
      {
        "_comment": "按照执行顺序列出来多个内层SQL预估的执行成本",
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "_comment_rows_examined_per_scan": "预估的扫描行数",
          "rows_produced_per_join": 5,
          "_comment_rows_produced_per_join": "预估的返回上层的行数",
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "10.25",
            "_comment_read_cost": "预估的单次读取数据的成本",
            "eval_cost": "1.00",
            "_comment_eval_cost": "预估的将单次读取的数据按照where条件过滤一遍的成本",
            "prefix_cost": "11.25",
            "_comment_prefix_cost": "整体的查询成本",
            "data_read_per_join": "80",
            "_comment_data_read_per_join": "预估读取的数据量，单位字节"
          },
          "used_columns": [
            "order_id"
          ],
          "materialized_from_subquery": {
            "_comment": "表示t2是物化子查询产生的",
            "using_temporary_table": true,
            "_comment_using_temporary_table": "使用了临时表保存结果",
            "dependent": false,
            "_comment_dependent": "表示子查询的where条件跟外层SQL无关，在外层执行前或一次性计算并缓存",
            "cacheable": true,
            "_comment_cacheable": "表示子查询的结果可以缓存",
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "20928.00"
              },
              "ordering_operation": {
                "_comment": "表示需要排序",
                "using_filesort": true,
                "_comment_using_filesort": "表示走文件堆排排序",
                "table": {
                  "table_name": "tcorder",
                  "access_type": "ref",
                  "possible_keys": [
                    "ind_***_buyerid",
                    "idx_***_gmtcreate"
                  ],
                  "key": "idx_***_gmtcreate",
                  "used_key_parts": [
                    "buyer_id"
                  ],
                  "_comment_used_key_parts": "表示实际生效的索引列",
                  "key_length": "8",
                  "ref": [
                    "const"
                  ],
                  "rows_examined_per_scan": 17440,
                  "rows_produced_per_join": 5,
                  "filtered": "0.03",
                  "index_condition": "                  ****************                  ((`tcorder`.`buyer_id` <=> 117075031) and (``tcorder`.`is_main` = 1) and                   ***************                  ",
                  "_comment_index_condition": "index_condition表示通过索引下推过滤的查询条件",
                  "cost_info": {
                    "read_cost": "17440.00",
                    "eval_cost": "1.18",
                    "prefix_cost": "20928.00",
                    "data_read_per_join": "236K"
                  },
                  "used_columns": [
                    "order_id",
                    "buyer_id",
                    "*************"
                  ],
                  "attached_condition": "((ifnull(`tcorder`.`attribute4`,0) <> 1) and *************",
                  "_comment_attached_condition": "attached_condition表示通过mysql服务器层过滤的where条件"
                }
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "t1",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "order_id"
          ],
          "key_length": "8",
          "ref": [
            "t2.order_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "5.00",
            "eval_cost": "1.00",
            "prefix_cost": "17.25",
            "data_read_per_join": "200K"
          },
          "used_columns": [
            "order_id",
            "****************"
          ]
        }
      }
    ]
  }
}

综合上面json的输出可知上述SQL的实际执行过程大致如下：

1. 先执行子查询（select_id=2），扫描 tcorder 的索引 idx_***_gmtcreate（约 17440 条候选），应用 index_condition（ICP）以减少要回表的行数；仍需对 attached_condition 做服务器端判断；对结果做排序（using_filesort），把结果物化到临时表（using_temporary_table=true）。物化后临时表（t2）约有 5 行（非常小且 cacheable，因为 dependent=false）。

2. 外层扫描临时表 t2（ALL，5 行），对每个 t2.order_id 用主键在 t1 上做 eq_ref 点查（一次找到一行），最终组合出 5 条输出行。

Query Profiler

MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条Query 在整个执行过程中不同阶段多种资源的消耗情况，如耗时、CPU，IO，IPC，SWAP 等，以及发生的 PAGE FAULTS，CONTEXT SWITCHE次数等等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置，其具体用法如下：

type的取值如下：