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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;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是ALLrows估算值是 286 万这意味着走的是全表扫描。就算idx_user_id存在优化器在很多情况下也不愿意用它原因很简单user_id 123456这个条件只能把结果集缩小到几千行但还需要回表过滤status 1然后再用create_time做排序。优化器算了一笔账觉得直接扫全表再过滤再排序成本可能更低于是干脆不走索引。1.2 慢查询背后隐藏的索引设计问题这个项目的第一个难点不在于“建索引”这个动作而在于想清楚“订单表的查询模式到底长什么样”。订单表表面上字段不多但不同角色访问它的姿势完全不一样用户端查“我的订单”过滤条件是user_idstatus排序是create_time运营端查“店铺订单列表”过滤条件是store_idstatus 时间范围排序是create_time财务对账查“某时间段的支付单”过滤条件是channelpay_typecreate_time范围。同一个表三种主流查询最合适的索引并不是某一个“万能索引”而是一组索引各自覆盖对应场景。很多同学在这一步容易犯的错是为每一个 where 条件字段都建单列索引结果优化器还是要做大量的“索引合并”或随机回表反而更慢。这次实战中也需要把单列索引合并、重新设计成联合索引并且针对排序字段做特殊处理。2. 排查与设计思路先看执行计划再做索引索引设计最忌讳拍脑袋。拿到慢 SQL 后我习惯先做三件事看慢查询日志确认高频 SQL对每一条 SQL 跑EXPLAIN看执行计划再用SHOW PROFILE或performance_schema看时间消耗在哪。这套流程走下来基本上能把问题定性是扫描行数太大、回表太多还是排序太重。2.1 用 EXPLAIN 定位索引失效的根源以“用户订单列表”这条 SQL 为例EXPLAIN结果如下参数值typeALLpossible_keysidx_user_idkeyNULLrows2860000filtered0.02%ExtraUsing where; Using filesorttypeALL说明是全表扫描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 DESCuser_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 创建索引后的执行计划对比建完索引后重新执行之前那条慢 SQLEXPLAIN 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;执行计划变成参数值typerefpossible_keysidx_user_status_ctimekeyidx_user_status_ctimerows186filtered100.00%ExtraUsing index; Using whereUsing 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中typeALL但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量级、无filesortEXPLAIN 关键字对照字段顺序检查等值条件在前排序字段在中后范围字段谨慎放置手写推导索引扫描顺序覆盖索引取舍高频查询是否避免回表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 分钟梳理业务链路再动手建索引这个时间省不得。
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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;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是ALLrows估算值是 286 万这意味着走的是全表扫描。就算idx_user_id存在优化器在很多情况下也不愿意用它原因很简单user_id 123456这个条件只能把结果集缩小到几千行但还需要回表过滤status 1然后再用create_time做排序。优化器算了一笔账觉得直接扫全表再过滤再排序成本可能更低于是干脆不走索引。1.2 慢查询背后隐藏的索引设计问题这个项目的第一个难点不在于“建索引”这个动作而在于想清楚“订单表的查询模式到底长什么样”。订单表表面上字段不多但不同角色访问它的姿势完全不一样用户端查“我的订单”过滤条件是user_idstatus排序是create_time运营端查“店铺订单列表”过滤条件是store_idstatus 时间范围排序是create_time财务对账查“某时间段的支付单”过滤条件是channelpay_typecreate_time范围。同一个表三种主流查询最合适的索引并不是某一个“万能索引”而是一组索引各自覆盖对应场景。很多同学在这一步容易犯的错是为每一个 where 条件字段都建单列索引结果优化器还是要做大量的“索引合并”或随机回表反而更慢。这次实战中也需要把单列索引合并、重新设计成联合索引并且针对排序字段做特殊处理。2. 排查与设计思路先看执行计划再做索引索引设计最忌讳拍脑袋。拿到慢 SQL 后我习惯先做三件事看慢查询日志确认高频 SQL对每一条 SQL 跑EXPLAIN看执行计划再用SHOW PROFILE或performance_schema看时间消耗在哪。这套流程走下来基本上能把问题定性是扫描行数太大、回表太多还是排序太重。2.1 用 EXPLAIN 定位索引失效的根源以“用户订单列表”这条 SQL 为例EXPLAIN结果如下参数值typeALLpossible_keysidx_user_idkeyNULLrows2860000filtered0.02%ExtraUsing where; Using filesorttypeALL说明是全表扫描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 DESCuser_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 创建索引后的执行计划对比建完索引后重新执行之前那条慢 SQLEXPLAIN 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;执行计划变成参数值typerefpossible_keysidx_user_status_ctimekeyidx_user_status_ctimerows186filtered100.00%ExtraUsing index; Using whereUsing 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中typeALL但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量级、无filesortEXPLAIN 关键字对照字段顺序检查等值条件在前排序字段在中后范围字段谨慎放置手写推导索引扫描顺序覆盖索引取舍高频查询是否避免回表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 分钟梳理业务链路再动手建索引这个时间省不得。
绕过AOP代理的深层原理与解决方案)
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
