1. 响应时间慢在数据库先定位再动手先聊个我刚帮朋友处理过的线上问题一个订单列表接口的响应时间稳定在 800ms 上下数据库 CPU 不高、SQL 也不复杂最后定位到一张 300 万行的订单表查询一直在全表扫描。改完索引后接口响应时间掉到了 30ms 左右。这篇文章没有高深理论就是把“响应时间优化”里最常用的数据库索引调整流程、原理和坑按我实操的顺序讲清楚。适合被慢查询折磨的后端、DBA以及准备优化线上接口的同学们。很多团队一遇到接口慢第一反应是加 Redis 缓存、加机器、改代码结果缓存穿透、数据一致性、成本全上来了。实际上有相当一部分“后端接口慢”是数据库执行计划走了全表扫描或者扫描行数远超预期。索引调整的成本很低但收益往往是最直接的。前提是先定位再动手。盲目加索引不但可能没用还会拖慢写入。1.1 慢查询日志和 processlist 的两步定位第一步一定不是去猜而是把数据库已经记录下来的“慢 SQL”捞出来看。MySQL 可以开慢查询日志核心参数就这几个SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 设置成 1意思是超过 1 秒的 SQL 都会被记录。log_queries_not_using_indexes 会把没走索引的查询也记下来这个参数在生产环境要慎开因为如果你有一堆低效查询日志量会暴涨。定位完问题后建议立刻关掉。除了离线看日志线上问题更直接的观测手段是SHOW PROCESSLIST。它能实时看到当前哪些会话在跑、跑了多久、在等多个锁。我最常用的是这条SELECT * FROM performance_schema.processlist WHERE INFO IS NOT NULL;如果看到某个查询的 TIME 字段持续上涨基本就能锁定嫌疑 SQL。这个阶段不用急着分析 SQL 写得好不好先把“响应时间长”和“某条具体 SQL”绑定起来。只要绑定关系明确了后面的事就顺了。1.2 EXPLAIN 才是索引调整的“CT 机”拿到候选 SQL 后下一步就是执行计划分析。MySQL 的EXPLAIN能告诉你优化器打算怎么执行这条 SQL虽然它给的是估算值但足够判断索引调整方向。EXPLAIN SELECT id, status, order_time, amount FROM orders WHERE user_id 20240815 AND order_time 2024-07-15 AND order_time 2024-08-15 ORDER BY order_time DESC LIMIT 20;重点看四个东西type、key、rows、Extra。typeref 或 eq_ref 说明走了非唯一索引或唯一索引的等值查找range 表示范围扫描ALL 就是全表扫描。看见 ALL响应时间想快很难。key实际用到的索引。如果是 NULL说明优化器没找到可用的索引。rows优化器估算需要扫描的行数。扫描行数直接决定 IO 次数。ExtraUsing filesort代表排序走了内存或磁盘临时文件Using temporary代表用了临时表Using index代表覆盖索引通常是最理想的。很多初学者只盯着key有没有值这不全面。有时候明明用了索引但rows还是几十万响应时间一样慢。真正健康的执行计划是type 是 range/refrows 接近最终返回的数据量数量级Extra 里没有 filesort 和 temporary。后面所有索引调整都是奔着这个目标去的。2. 索引调整的核心原则让 B 树帮你减少回表索引不是银弹但它背后有一个很朴素的逻辑数据库存储引擎是按页读数据的一次 IO 可能读一整页如果你让引擎只扫描几百行而不是几百万行响应时间自然降下来。理解这一点才会明白为什么要调索引字段顺序为什么要做覆盖索引。2.1 聚簇索引与二级索引的代价InnoDB 的表数据本身就是按主键组织的一棵 B 树这叫聚簇索引。你建的其他索引比如idx_user_id(user_id)是独立的 B 树叶子节点存的是主键值而不是整行数据。通过普通索引查数据时引擎会先在二级索引里找到主键再回聚簇索引查完整行这个过程叫回表。回表不一定会慢但回表次数多了一定慢因为每次回表都伴随一次随机 IO。想象一下你在新华字典的拼音目录里查到了某个字在第 700 页然后需要来回翻字典正文如果查 1000 个字翻 1000 次和直接在正文区顺着找完全不是一个体验。这也是为什么覆盖索引特别值钱。最简单的验证方法就是看 Extra 字段。如果 Extra 里出现Using index说明查询需要的列都包含在索引里不需要回表。如果出现Using where说明索引只帮你定位到了一部分候选数据引擎还要继续过滤。2.2 联合索引的最左前缀字段顺序决定一切联合索引是索引调整里最容易被用错的东西。比如你建了(user_id, status, order_time)它的排序规则是先按 user_id 排user_id 一样时按 status 排status 也一样时再按 order_time 排。查询时只有匹配从左到右的前缀列后续列才能被高效利用。这就是最左前缀原则。WHERE user_id 1能用这个索引WHERE user_id 1 AND status PAID也能用WHERE user_id 1 AND order_time ...只能用 user_id 这一列因为跳过了 status索引里 order_time 的排序关系就无法参与定位。设计联合索引时我的习惯是等值条件的列放在前面范围条件的列放在后面区分度高的字段尽量往前放。但前提是查询真的用到了这个字段。每次加索引都应该问一个问题这个索引是为了哪条 SQL 设计的如果经常有多个不同条件组合的 SQL就得权衡或者拆成多个索引而不是指望一个大而全的索引通吃所有场景。2.3 覆盖索引与索引选择度的取舍覆盖索引的原理不难如果二级索引本身已经包含了查询需要的所有字段引擎就不需要回表。比如SELECT user_id, status FROM orders WHERE user_id 1只要索引是(user_id, status)直接扫索引叶子节点就能返回Extra 显示Using index。但覆盖索引不能无脑加。你把所有字段都塞进索引索引体积变大每个叶子节点能存放的键值变少树变高读写放大效应也会变明显。索引的存储成本和查询收益必须平衡。另一个容易忽略的概念是索引选择度也就是某个列的取值差异性。计算公式是COUNT(DISTINCT 列) / COUNT(*)。选择度越低说明相同值越多B 树通过这个列过滤出来的候选集就越大。拿 status 举例一张 300 万的订单表 status 可能只有 5 种取值选择度只有百万分之几。如果你让 status 做联合索引的第一个字段那引擎找到的不再是“几行”而可能是“几十万行”。不过这里有个例外如果 status 的分布极度不均衡比如 99% 都是 PENDING你查询时只查少数的 PAID 状态那 status 仍然能有效缩小范围。所以选择度是不是“够用”要看具体业务的倾斜程度不能只看数学比例。3. 一次真实优化订单列表从 800ms 到 30ms理论讲再多不如跑一遍真实案例。下面这个场景是我去年处理过的一个订单管理后台。业务很简单用户查询自己最近 30 天的订单按下单时间倒序只显示前 20 条。初始表结构大概是这样CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, status VARCHAR(20) NOT NULL, order_time DATETIME NOT NULL, amount DECIMAL(12,2) NOT NULL, KEY idx_user_id (user_id) ) ENGINEInnoDB;表里有 300 万行模拟数据其中某个高频用户有 2 万多条订单。问题 SQL 长这样SELECT id, status, order_time, amount FROM orders WHERE user_id 20240815 AND status PAID AND order_time 2024-07-15 AND order_time 2024-08-15 ORDER BY order_time DESC LIMIT 20;3.1 基线与瓶颈分析优化前EXPLAIN 结果很典型type 是 refkey 是idx_user_idrows 估算 18000 左右Extra 里有Using where; Using filesort。这条 SQL 虽然用上了idx_user_id但问题在于索引只定位到了这个用户的全部订单包括所有状态和历史订单。接下来引擎要逐行过滤 status 和时间范围再把结果做排序最后取前 20 条。这里有两个明显成本一是过滤掉大量无效行二是在临时文件里排序。实测接口响应时间 800ms 左右排序部分占了很大比重。这里也说明一个问题用了索引不等于优化到位。idx_user_id帮我们避开了全表扫描但没避开“大范围扫描 文件排序”。响应时间的差距就藏在这些细节里。3.2 设计联合索引的完整思考针对这条 SQL我的目标是让索引同时承担三件事定位 user_id、过滤 status、让 order_time 的顺序直接为 ORDER BY 服务。候选索引有两个ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, order_time);为什么把 status 放在 order_time 前面因为 status 是等值条件order_time 是范围条件。按照最左前缀原则等值列放在前面后续的范围列才能发挥完整价值。如果换成(user_id, order_time, status)user_id 之后直接遇到 order_time 的范围比较status 就无法继续参与索引定位了。那 order_time 排序问题怎么解决InnoDB 的二级索引默认是按升序存储的。对于单值范围MySQL 可以反向扫描索引所以ORDER BY order_time DESC不一定需要 filesort。实际执行计划确实没有再出现 filesort而是显示Backward index scan。加索引后查询定位路径变成先用 user_id status 精确定位到一个较小的数据段再在 order_time 的范围里做反向扫描。因为 order_time 在索引里是有序的取前 20 条后直接回表拿 amount回表次数也只有 20 次。这个设计把原本“先扫 18000 行再排序”降成了“扫描符合条件的那几百行然后直接取 20 行”。两条不同的执行路径IO 差距可能差几十倍。3.3 优化前后执行计划对比优化后再看 EXPLAIN优化项优化前优化后typerefrefkeyidx_user_ididx_user_status_timerows18000 左右350 左右ExtraUsing where; Using filesortUsing index condition; Backward index scanrows 从 18000 降到 350filesort 消失接口响应时间从 800ms 降到了 30ms 上下。注意 Extra 里是Using index condition不是Using index因为 amount 字段不在索引里最终 20 条记录需要回表。但这 20 次回表完全可接受量级很小。如果你也想做类似的优化我用两条经验提醒你第一EXPLAIN 里的 rows 是估算值实际加索引前可以用SELECT COUNT(*)粗查一下条件范围内的行数估算和事实偏差过大就要考虑统计信息过期。第二执行计划在数据量少的时候可能看不出来一定要用接近生产的数据量测试否则你很可能被“本地 1 万行数据很快”给骗了。3.4 索引碎片与统计信息维护索引不是建完就一劳永逸。订单表每天都有新增和状态更新索引页也会不断发生页分裂和碎片化。碎片化不是让索引失效而是让索引页的利用率下降同样的查询需要读更多的页响应时间会慢慢变差。针对这个问题MySQL 里最常用的维护手段是ANALYZE TABLE orders它会重新统计索引的基数信息让优化器不再基于过期的统计信息做糟糕的执行计划。如果你发现表经过大量删除后索引空间没有降下来可以执行OPTIMIZE TABLE orders;OPTIMIZE 会重建表和索引但代价是锁表时间长线上大表要慎重。我的建议是优先做 ANALYZE只有在明显碎片化、表体积异常膨胀时才安排运维窗口做重建。索引调整不是“加一个索引就跑”后续的统计信息刷新和新索引的容量评估同样要纳入上线 checklist。4. 索引调整必须避开的坑索引调整翻车最常见的原因不是不懂 B 树而是被各种“看着像能走索引、实际走不了”的细节绊倒。下面这几个坑我几乎每次排查慢查询都会遇到。4.1 隐式类型转换和函数包裹电话、订单号、状态码这类字段很常见是字符串类型。如果你写WHERE user_phone 13800001111查询条件里是整数MySQL 在比较时会把字符串列转换成数值再比较。一旦列上发生了隐式转换索引就可能失效。解决办法很简单始终保持字段类型和参数类型一致。数值列用数值字符串列用引号。如果不确定 MySQL 到底怎么执行的看完执行计划后可以加一句SHOW WARNINGS;它会告诉你优化器把条件改写成什么样了有没有发生 cast。另一个非常常见的坑是函数包裹索引列比如WHERE DATE(order_time) 2024-08-01。这个写法完全没法使用 order_time 上的索引因为索引存储的是原始 DATETIME 值不是经过函数计算的结果。正确做法是改成范围条件WHERE order_time 2024-08-01 00:00:00 AND order_time 2024-08-02 00:00:00;这个改写看起来只是语法变了本质是让索引可以直接生效。记住一句话索引列上不要套函数套了基本就是全表扫。4.2 范围条件会“截断”后续索引字段假设索引是(a, b, c)查询条件是a 1 AND b 10 AND c 2。很多开发以为这三个条件都能用上索引实际上 c 用不上。因为 B 树的排序是先按 a、再按 b、再按 c一旦 b 是范围比较在 b 的值不确定的情况下c 的排序关系没有意义。这就是范围条件截断。如何判断 c 到底没被使用看执行计划里的key_len。key_len 表示实际使用到的索引字节数。如果(a, b, c)的 key_len 只算到 b说明 c 确实被截断了。遇到这种 SQL要么把 c 提到 b 前面前提是等值条件优先于范围条件要么拆成两条查询。我记得有个订单报表就是吃了这个亏状态字段放在时间范围后面。把状态移到前面后扫描行数立刻少了一个数量级。所以设计联合索引时条件里有、、BETWEEN、LIKE这类范围操作时一定要把这个字段放在等值字段后面否则后续字段全部白搭。4.3 排序和 GROUP BY 也依赖索引索引不仅能加速 WHERE 过滤还能加速排序和分组。MySQL 的 ORDER BY 如果无法利用索引顺序就会出现Using filesort。filesort 在小数据量尚可大数据量下很可能把排序数据写入磁盘响应时间直接崩。比如你有索引(user_id, order_time)查询WHERE user_id 1 ORDER BY order_time DESC可以直接反向扫索引不需要排序。但如果查询是WHERE user_id 1 ORDER BY status索引的第二列是 order_time 而不是 status排序顺序对不上filesort 就会跑出来。GROUP BY 同理。GROUP BY user_id, status如果联合索引前缀是(user_id, status)可以避免临时表如果分组字段顺序和索引不一致或者中间隔了一个字段临时表就出现了。观察 Extra 里的Using temporary是一个信号看到它就要想想能不能调整索引顺序消掉临时表。4.4 统计信息过期和冗余索引线上数据库有一个隐蔽问题优化器依据的统计信息是旧的。表数据大量变更后可能明明有更好的索引优化器却选了一个错误的执行计划。这时候最省事的操作就是ANALYZE TABLE 表名刷新统计信息。我见过很多次“索引加了SQL 还是慢”的案例其实不是索引没用而是优化器压根没把它当成候选方案。还有一类问题是索引叠床架屋。比如已经有(user_id, status, order_time)又保留了一个(user_id)索引。前者的最左前缀已经覆盖了 user_id 的单列查询后者的存在就没有意义反而拖慢每次写入。删除冗余索引前可以用pt-duplicate-key-checker这类工具扫描避免人工漏判。我通常会在每个季度做一次全量索引盘点把慢查询日志里的高频 SQL 捞出来对照现有索引一个个跑 EXPLAIN顺手删掉半年内都没被用到、或前缀完全重复的索引。别舍不得冗余索引消耗的是每一次 INSERT 和 UPDATE 的性能长期来看都是成本。5. 把响应时间优化的习惯固化进日常索引调整这件事最大的成本不是建索引而是“发现问题”和“评估影响”的机制。项目上线前多花十分钟做索引评审比线上故障后凌晨三点紧急加索引要舒服得多。我的日常做法是维护一份慢查询台账。每周从日志或监控平台里导出 top 20 慢 SQL按响应时间倒序看一遍。对每一条至少回答三个问题这条 SQL 的执行计划里有没有全表扫描有没有 filesort 和 temporary可用的联合索引字段顺序是否匹配当前查询条件如果三个问题都过了哪怕速度不是最优也能接受。新索引上线前我还会特别确认它对写入链路的影响。一张表加一个二级索引意味着每次 INSERT 都要额外维护一棵 B 树的插入路径UPDATE 如果改了索引列也要同步改索引。所以那些“上线前加一个索引试试”的做法我是不太支持的。测试环境验证 SQL 收益评估生产数据量下的写入压力再安排灰度上线。另外如果你用的是 MySQL 8.0 及以上版本可以留意降序索引和索引跳跃扫描这些新特性。降序索引能让ORDER BY col DESC更彻底地利用索引顺序索引跳跃扫描能在特定场景下让低选择度字段的查询获益。不过新特性在使用前同样要回到 EXPLAIN 上验证不要因为文档说“可以用”就直接用。最后分享一个小技巧排查响应时间慢的时候除了看数据库把接口日志、缓存命中率、网络耗时一起拉出来看。数据库索引调整通常能解决大部分后端瓶颈但不是全部。如果全表扫描也优化了、索引也走到位了、行数也降下来了接口还是慢那问题可能就不在数据库了。先分清边界再动手优化这样每一步都会有实打实的收益。
数据库索引优化实战：从慢查询定位到响应时间骤降的完整流程
1. 响应时间慢在数据库先定位再动手先聊个我刚帮朋友处理过的线上问题一个订单列表接口的响应时间稳定在 800ms 上下数据库 CPU 不高、SQL 也不复杂最后定位到一张 300 万行的订单表查询一直在全表扫描。改完索引后接口响应时间掉到了 30ms 左右。这篇文章没有高深理论就是把“响应时间优化”里最常用的数据库索引调整流程、原理和坑按我实操的顺序讲清楚。适合被慢查询折磨的后端、DBA以及准备优化线上接口的同学们。很多团队一遇到接口慢第一反应是加 Redis 缓存、加机器、改代码结果缓存穿透、数据一致性、成本全上来了。实际上有相当一部分“后端接口慢”是数据库执行计划走了全表扫描或者扫描行数远超预期。索引调整的成本很低但收益往往是最直接的。前提是先定位再动手。盲目加索引不但可能没用还会拖慢写入。1.1 慢查询日志和 processlist 的两步定位第一步一定不是去猜而是把数据库已经记录下来的“慢 SQL”捞出来看。MySQL 可以开慢查询日志核心参数就这几个SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 设置成 1意思是超过 1 秒的 SQL 都会被记录。log_queries_not_using_indexes 会把没走索引的查询也记下来这个参数在生产环境要慎开因为如果你有一堆低效查询日志量会暴涨。定位完问题后建议立刻关掉。除了离线看日志线上问题更直接的观测手段是SHOW PROCESSLIST。它能实时看到当前哪些会话在跑、跑了多久、在等多个锁。我最常用的是这条SELECT * FROM performance_schema.processlist WHERE INFO IS NOT NULL;如果看到某个查询的 TIME 字段持续上涨基本就能锁定嫌疑 SQL。这个阶段不用急着分析 SQL 写得好不好先把“响应时间长”和“某条具体 SQL”绑定起来。只要绑定关系明确了后面的事就顺了。1.2 EXPLAIN 才是索引调整的“CT 机”拿到候选 SQL 后下一步就是执行计划分析。MySQL 的EXPLAIN能告诉你优化器打算怎么执行这条 SQL虽然它给的是估算值但足够判断索引调整方向。EXPLAIN SELECT id, status, order_time, amount FROM orders WHERE user_id 20240815 AND order_time 2024-07-15 AND order_time 2024-08-15 ORDER BY order_time DESC LIMIT 20;重点看四个东西type、key、rows、Extra。typeref 或 eq_ref 说明走了非唯一索引或唯一索引的等值查找range 表示范围扫描ALL 就是全表扫描。看见 ALL响应时间想快很难。key实际用到的索引。如果是 NULL说明优化器没找到可用的索引。rows优化器估算需要扫描的行数。扫描行数直接决定 IO 次数。ExtraUsing filesort代表排序走了内存或磁盘临时文件Using temporary代表用了临时表Using index代表覆盖索引通常是最理想的。很多初学者只盯着key有没有值这不全面。有时候明明用了索引但rows还是几十万响应时间一样慢。真正健康的执行计划是type 是 range/refrows 接近最终返回的数据量数量级Extra 里没有 filesort 和 temporary。后面所有索引调整都是奔着这个目标去的。2. 索引调整的核心原则让 B 树帮你减少回表索引不是银弹但它背后有一个很朴素的逻辑数据库存储引擎是按页读数据的一次 IO 可能读一整页如果你让引擎只扫描几百行而不是几百万行响应时间自然降下来。理解这一点才会明白为什么要调索引字段顺序为什么要做覆盖索引。2.1 聚簇索引与二级索引的代价InnoDB 的表数据本身就是按主键组织的一棵 B 树这叫聚簇索引。你建的其他索引比如idx_user_id(user_id)是独立的 B 树叶子节点存的是主键值而不是整行数据。通过普通索引查数据时引擎会先在二级索引里找到主键再回聚簇索引查完整行这个过程叫回表。回表不一定会慢但回表次数多了一定慢因为每次回表都伴随一次随机 IO。想象一下你在新华字典的拼音目录里查到了某个字在第 700 页然后需要来回翻字典正文如果查 1000 个字翻 1000 次和直接在正文区顺着找完全不是一个体验。这也是为什么覆盖索引特别值钱。最简单的验证方法就是看 Extra 字段。如果 Extra 里出现Using index说明查询需要的列都包含在索引里不需要回表。如果出现Using where说明索引只帮你定位到了一部分候选数据引擎还要继续过滤。2.2 联合索引的最左前缀字段顺序决定一切联合索引是索引调整里最容易被用错的东西。比如你建了(user_id, status, order_time)它的排序规则是先按 user_id 排user_id 一样时按 status 排status 也一样时再按 order_time 排。查询时只有匹配从左到右的前缀列后续列才能被高效利用。这就是最左前缀原则。WHERE user_id 1能用这个索引WHERE user_id 1 AND status PAID也能用WHERE user_id 1 AND order_time ...只能用 user_id 这一列因为跳过了 status索引里 order_time 的排序关系就无法参与定位。设计联合索引时我的习惯是等值条件的列放在前面范围条件的列放在后面区分度高的字段尽量往前放。但前提是查询真的用到了这个字段。每次加索引都应该问一个问题这个索引是为了哪条 SQL 设计的如果经常有多个不同条件组合的 SQL就得权衡或者拆成多个索引而不是指望一个大而全的索引通吃所有场景。2.3 覆盖索引与索引选择度的取舍覆盖索引的原理不难如果二级索引本身已经包含了查询需要的所有字段引擎就不需要回表。比如SELECT user_id, status FROM orders WHERE user_id 1只要索引是(user_id, status)直接扫索引叶子节点就能返回Extra 显示Using index。但覆盖索引不能无脑加。你把所有字段都塞进索引索引体积变大每个叶子节点能存放的键值变少树变高读写放大效应也会变明显。索引的存储成本和查询收益必须平衡。另一个容易忽略的概念是索引选择度也就是某个列的取值差异性。计算公式是COUNT(DISTINCT 列) / COUNT(*)。选择度越低说明相同值越多B 树通过这个列过滤出来的候选集就越大。拿 status 举例一张 300 万的订单表 status 可能只有 5 种取值选择度只有百万分之几。如果你让 status 做联合索引的第一个字段那引擎找到的不再是“几行”而可能是“几十万行”。不过这里有个例外如果 status 的分布极度不均衡比如 99% 都是 PENDING你查询时只查少数的 PAID 状态那 status 仍然能有效缩小范围。所以选择度是不是“够用”要看具体业务的倾斜程度不能只看数学比例。3. 一次真实优化订单列表从 800ms 到 30ms理论讲再多不如跑一遍真实案例。下面这个场景是我去年处理过的一个订单管理后台。业务很简单用户查询自己最近 30 天的订单按下单时间倒序只显示前 20 条。初始表结构大概是这样CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, status VARCHAR(20) NOT NULL, order_time DATETIME NOT NULL, amount DECIMAL(12,2) NOT NULL, KEY idx_user_id (user_id) ) ENGINEInnoDB;表里有 300 万行模拟数据其中某个高频用户有 2 万多条订单。问题 SQL 长这样SELECT id, status, order_time, amount FROM orders WHERE user_id 20240815 AND status PAID AND order_time 2024-07-15 AND order_time 2024-08-15 ORDER BY order_time DESC LIMIT 20;3.1 基线与瓶颈分析优化前EXPLAIN 结果很典型type 是 refkey 是idx_user_idrows 估算 18000 左右Extra 里有Using where; Using filesort。这条 SQL 虽然用上了idx_user_id但问题在于索引只定位到了这个用户的全部订单包括所有状态和历史订单。接下来引擎要逐行过滤 status 和时间范围再把结果做排序最后取前 20 条。这里有两个明显成本一是过滤掉大量无效行二是在临时文件里排序。实测接口响应时间 800ms 左右排序部分占了很大比重。这里也说明一个问题用了索引不等于优化到位。idx_user_id帮我们避开了全表扫描但没避开“大范围扫描 文件排序”。响应时间的差距就藏在这些细节里。3.2 设计联合索引的完整思考针对这条 SQL我的目标是让索引同时承担三件事定位 user_id、过滤 status、让 order_time 的顺序直接为 ORDER BY 服务。候选索引有两个ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, order_time);为什么把 status 放在 order_time 前面因为 status 是等值条件order_time 是范围条件。按照最左前缀原则等值列放在前面后续的范围列才能发挥完整价值。如果换成(user_id, order_time, status)user_id 之后直接遇到 order_time 的范围比较status 就无法继续参与索引定位了。那 order_time 排序问题怎么解决InnoDB 的二级索引默认是按升序存储的。对于单值范围MySQL 可以反向扫描索引所以ORDER BY order_time DESC不一定需要 filesort。实际执行计划确实没有再出现 filesort而是显示Backward index scan。加索引后查询定位路径变成先用 user_id status 精确定位到一个较小的数据段再在 order_time 的范围里做反向扫描。因为 order_time 在索引里是有序的取前 20 条后直接回表拿 amount回表次数也只有 20 次。这个设计把原本“先扫 18000 行再排序”降成了“扫描符合条件的那几百行然后直接取 20 行”。两条不同的执行路径IO 差距可能差几十倍。3.3 优化前后执行计划对比优化后再看 EXPLAIN优化项优化前优化后typerefrefkeyidx_user_ididx_user_status_timerows18000 左右350 左右ExtraUsing where; Using filesortUsing index condition; Backward index scanrows 从 18000 降到 350filesort 消失接口响应时间从 800ms 降到了 30ms 上下。注意 Extra 里是Using index condition不是Using index因为 amount 字段不在索引里最终 20 条记录需要回表。但这 20 次回表完全可接受量级很小。如果你也想做类似的优化我用两条经验提醒你第一EXPLAIN 里的 rows 是估算值实际加索引前可以用SELECT COUNT(*)粗查一下条件范围内的行数估算和事实偏差过大就要考虑统计信息过期。第二执行计划在数据量少的时候可能看不出来一定要用接近生产的数据量测试否则你很可能被“本地 1 万行数据很快”给骗了。3.4 索引碎片与统计信息维护索引不是建完就一劳永逸。订单表每天都有新增和状态更新索引页也会不断发生页分裂和碎片化。碎片化不是让索引失效而是让索引页的利用率下降同样的查询需要读更多的页响应时间会慢慢变差。针对这个问题MySQL 里最常用的维护手段是ANALYZE TABLE orders它会重新统计索引的基数信息让优化器不再基于过期的统计信息做糟糕的执行计划。如果你发现表经过大量删除后索引空间没有降下来可以执行OPTIMIZE TABLE orders;OPTIMIZE 会重建表和索引但代价是锁表时间长线上大表要慎重。我的建议是优先做 ANALYZE只有在明显碎片化、表体积异常膨胀时才安排运维窗口做重建。索引调整不是“加一个索引就跑”后续的统计信息刷新和新索引的容量评估同样要纳入上线 checklist。4. 索引调整必须避开的坑索引调整翻车最常见的原因不是不懂 B 树而是被各种“看着像能走索引、实际走不了”的细节绊倒。下面这几个坑我几乎每次排查慢查询都会遇到。4.1 隐式类型转换和函数包裹电话、订单号、状态码这类字段很常见是字符串类型。如果你写WHERE user_phone 13800001111查询条件里是整数MySQL 在比较时会把字符串列转换成数值再比较。一旦列上发生了隐式转换索引就可能失效。解决办法很简单始终保持字段类型和参数类型一致。数值列用数值字符串列用引号。如果不确定 MySQL 到底怎么执行的看完执行计划后可以加一句SHOW WARNINGS;它会告诉你优化器把条件改写成什么样了有没有发生 cast。另一个非常常见的坑是函数包裹索引列比如WHERE DATE(order_time) 2024-08-01。这个写法完全没法使用 order_time 上的索引因为索引存储的是原始 DATETIME 值不是经过函数计算的结果。正确做法是改成范围条件WHERE order_time 2024-08-01 00:00:00 AND order_time 2024-08-02 00:00:00;这个改写看起来只是语法变了本质是让索引可以直接生效。记住一句话索引列上不要套函数套了基本就是全表扫。4.2 范围条件会“截断”后续索引字段假设索引是(a, b, c)查询条件是a 1 AND b 10 AND c 2。很多开发以为这三个条件都能用上索引实际上 c 用不上。因为 B 树的排序是先按 a、再按 b、再按 c一旦 b 是范围比较在 b 的值不确定的情况下c 的排序关系没有意义。这就是范围条件截断。如何判断 c 到底没被使用看执行计划里的key_len。key_len 表示实际使用到的索引字节数。如果(a, b, c)的 key_len 只算到 b说明 c 确实被截断了。遇到这种 SQL要么把 c 提到 b 前面前提是等值条件优先于范围条件要么拆成两条查询。我记得有个订单报表就是吃了这个亏状态字段放在时间范围后面。把状态移到前面后扫描行数立刻少了一个数量级。所以设计联合索引时条件里有、、BETWEEN、LIKE这类范围操作时一定要把这个字段放在等值字段后面否则后续字段全部白搭。4.3 排序和 GROUP BY 也依赖索引索引不仅能加速 WHERE 过滤还能加速排序和分组。MySQL 的 ORDER BY 如果无法利用索引顺序就会出现Using filesort。filesort 在小数据量尚可大数据量下很可能把排序数据写入磁盘响应时间直接崩。比如你有索引(user_id, order_time)查询WHERE user_id 1 ORDER BY order_time DESC可以直接反向扫索引不需要排序。但如果查询是WHERE user_id 1 ORDER BY status索引的第二列是 order_time 而不是 status排序顺序对不上filesort 就会跑出来。GROUP BY 同理。GROUP BY user_id, status如果联合索引前缀是(user_id, status)可以避免临时表如果分组字段顺序和索引不一致或者中间隔了一个字段临时表就出现了。观察 Extra 里的Using temporary是一个信号看到它就要想想能不能调整索引顺序消掉临时表。4.4 统计信息过期和冗余索引线上数据库有一个隐蔽问题优化器依据的统计信息是旧的。表数据大量变更后可能明明有更好的索引优化器却选了一个错误的执行计划。这时候最省事的操作就是ANALYZE TABLE 表名刷新统计信息。我见过很多次“索引加了SQL 还是慢”的案例其实不是索引没用而是优化器压根没把它当成候选方案。还有一类问题是索引叠床架屋。比如已经有(user_id, status, order_time)又保留了一个(user_id)索引。前者的最左前缀已经覆盖了 user_id 的单列查询后者的存在就没有意义反而拖慢每次写入。删除冗余索引前可以用pt-duplicate-key-checker这类工具扫描避免人工漏判。我通常会在每个季度做一次全量索引盘点把慢查询日志里的高频 SQL 捞出来对照现有索引一个个跑 EXPLAIN顺手删掉半年内都没被用到、或前缀完全重复的索引。别舍不得冗余索引消耗的是每一次 INSERT 和 UPDATE 的性能长期来看都是成本。5. 把响应时间优化的习惯固化进日常索引调整这件事最大的成本不是建索引而是“发现问题”和“评估影响”的机制。项目上线前多花十分钟做索引评审比线上故障后凌晨三点紧急加索引要舒服得多。我的日常做法是维护一份慢查询台账。每周从日志或监控平台里导出 top 20 慢 SQL按响应时间倒序看一遍。对每一条至少回答三个问题这条 SQL 的执行计划里有没有全表扫描有没有 filesort 和 temporary可用的联合索引字段顺序是否匹配当前查询条件如果三个问题都过了哪怕速度不是最优也能接受。新索引上线前我还会特别确认它对写入链路的影响。一张表加一个二级索引意味着每次 INSERT 都要额外维护一棵 B 树的插入路径UPDATE 如果改了索引列也要同步改索引。所以那些“上线前加一个索引试试”的做法我是不太支持的。测试环境验证 SQL 收益评估生产数据量下的写入压力再安排灰度上线。另外如果你用的是 MySQL 8.0 及以上版本可以留意降序索引和索引跳跃扫描这些新特性。降序索引能让ORDER BY col DESC更彻底地利用索引顺序索引跳跃扫描能在特定场景下让低选择度字段的查询获益。不过新特性在使用前同样要回到 EXPLAIN 上验证不要因为文档说“可以用”就直接用。最后分享一个小技巧排查响应时间慢的时候除了看数据库把接口日志、缓存命中率、网络耗时一起拉出来看。数据库索引调整通常能解决大部分后端瓶颈但不是全部。如果全表扫描也优化了、索引也走到位了、行数也降下来了接口还是慢那问题可能就不在数据库了。先分清边界再动手优化这样每一步都会有实打实的收益。