最近一次线上排查让我印象挺深业务方反馈某个列表接口响应时间从平时的80毫秒涨到了2.6秒翻页越来越慢。查了一圈SQL很简单条件也很常规但执行计划里typeALL扫描了几百万行。表上不是没有索引甚至单看字段都找得到对应索引可优化器就是不用。后来把索引调整了一轮响应时间回到几十毫秒。这里面的关键不光是“加索引”而是搞清楚索引调整背后的逻辑——什么时候该加、加在哪些列、联合索引怎么排、加了为什么还是慢。这篇就专门聊聊响应时间优化里数据库索引调整到底怎么落地适合刚接触性能调优的后端开发、初转DBA的运维以及所有被慢查询折磨过的同学。1. 响应时间变慢的根源扫描行数与访问方式很多人一遇到查询慢第一反应就是“加索引”。这个方向没错但顺序错了。要调整索引得先理解索引到底在数据库查询链路里解决什么问题以及它为什么能快、又为什么不是万能的。1.1 全表扫描为什么可怕一次查询要走多少页数据库的数据最终都落在磁盘上InnoDB 存储引擎按页组织数据默认一个页 16KB。假设一张表有 300 万行每行平均 200 字节粗略算下来数据量在 600MB 左右也就是要读取大约 3.7 万个页。如果这些页不在内存缓冲池里就得走磁盘 I/O。就算每次 I/O 只要 1 毫秒单表全扫也是一笔巨额开销再加上网络传输、服务端过滤、排序响应时间自然就上去了。这个道理跟图书馆找书是一样的没有索书号目录你只能从第一排书架开始一本一本翻到最后一排。如果书只有 50 本倒无所谓书架上有 3 万本书的时候这种方式注定慢。全表扫描就是“一本一本翻”索引就是“按索书号直接定位到第几排第几格”。所以判断一条 SQL 会不会慢第一看的就是扫描行数。扫描行数越多耗时下限越高。见过太多例子明明查询结果只有 20 条但底层扫了 300 万行性能不可能好。优化响应时间本质上就是尽可能减少数据库为产出这 20 条结果而付出的扫描代价。1.2 B树索引的本质把翻全表变成查目录索引的底层结构在 InnoDB 里通常是一棵 B树。B树的特点是从根节点出发每层做一次比较就能筛选掉大量数据。InnoDB 的页大小是 16KB主键索引一个节点大概能存放上千个键值所以 2000 万行的表索引树高度一般也就 3 到 4 层。定位一条记录只需要几次页读取这就是索引快的原因。但这里有一个很多人忽略的点InnoDB 的索引分聚簇索引和二级索引。聚簇索引就是主键索引叶子节点直接存放整行数据二级索引的叶子节点存放的是索引列的值和主键值。也就是说如果通过二级索引找到了目标主键还要拿着主键再去聚簇索引里查一次完整数据这就是常说的“回表”。回表本身也是一次索引查找在高频查询下会被放大。这也是为什么索引不是越多越好。每多一个二级索引写入数据时就多维护一棵 B树磁盘占用和写入耗时都会增加。而且索引过多还会让优化器陷入选择困难甚至选错索引。后面聊索引调整时你会看到“少而精”比“多而全”重要得多。2. 调整索引前的准备慢查询定位与基线数据我见过不少同学直接对着一个慢 SQL 开始改索引改完发现执行计划变了但业务响应时间没什么改善。原因往往是没有先建立基线数据也没有搞清楚“当前状态到底有多差”。调整索引前至少要做三件事让慢 SQL 浮出来、给慢查询排序、记录调整前的执行指标。2.1 开启慢查询日志先让问题 SQL 浮上来MySQL 的慢查询日志是最直接的入口。平时开发环境可以不开但生产环境建议打开。下面是一组常用的配置以 MySQL 8.0 为例slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ON log_throttle_queries_not_using_indexes 10long_query_time设为 1 秒意味着超过 1 秒的查询才会被记录这个值可以根据业务量调整。高峰期 SQL 量大、不想日志太吵可以调到 2 秒如果正在做精细优化也可以临时调到 0.5 秒。log_queries_not_using_indexes这个参数容易被忽视它会把那些“即使没超过 1 秒但没用索引”的 SQL 也记进日志。很多潜在的问题查询就是靠它暴露出来的。配上log_throttle_queries_not_using_indexes可以避免大量同类 SQL 瞬间刷爆日志文件。在生产环境修改这些参数建议通过SET GLOBAL在线调整不要直接重启数据库。long_query_time对已有连接不生效新建立的连接才会使用新值观察时要注意这一点。2.2 sys库给慢查询排座次找到真正值得调的SQL线上慢查询日志可能成千上万条人工翻不现实。MySQL 5.7 之后自带 performance_schema 和 sys 库可以直接用来做慢查询排名。下面两个查询是我每次排查必跑的。SELECT query, exec_count, total_latency, rows_examined, rows_sent FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 20;这条 SQL 按总延迟给所有语句排序能快速找到“累计耗时最高”的查询。注意这里看的是累计值不是单次耗时的最大值。一个每天执行一百万次、单次只有 200 毫秒的查询累计代价比一个每天执行十次、单次 5 秒的查询高得多。优化前者对整体响应时间的提升反而更明显。另一个视图专门看全表扫描SELECT * FROM sys.statements_with_full_table_scans ORDER BY total_latency DESC LIMIT 10;这类查询是索引调整的重点关注对象。如果一张大表频繁出现全表扫描那大部分慢查询根源就在这里。实际执行时我会结合rows_examined和rows_sent的比值来判断扫描效率。比如扫描了 50 万行只返回 20 行说明过滤严重不足这是典型的索引缺失或索引失效信号。2.3 建立基线数据没有对照就没有优化调整索引之前建议先记录一份“调整前体检表”。内容很简单就四个指标SQL 文本、实际响应时间、EXPLAIN 的 type、rows。我通常用下面这种方式记录指标调整前值SQL响应时间1.8秒EXPLAIN typeALL扫描行数 rows约300万返回行数20ExtraUsing where; Using filesort有了这样一张表调整索引后才有对照依据。很多优化失败其实不是索引本身没起作用而是没有在同一条件下对比被缓存和系统负载干扰了判断。尤其是 InnoDB 的 buffer pool 会把热数据留在内存里同样一条 SQL第一次跑 1 秒第二次跑 100 毫秒不代表索引优化生效了可能只是数据页被缓存了。所以基线测试最好在冷热状态一致的情况下做或者干脆以一周内的 P95 响应时间为准。3. 索引设计核心字段选择、联合索引与覆盖索引准备工作做完才进入真正的索引调整环节。这一步最考验经验不是看到查询里有哪个字段就给哪个字段加索引而是要根据查询条件、数据分布、排序需求综合设计。很多“加了索引还是慢”的案例问题都出在设计阶段。3.1 区分度计算建索引前先跑一条SQL区分度指的是某个字段不同值的比例。比如一张表有 300 万行user_id有 290 万个不同值区分度接近 1status只有 3 个值区分度就是百万分之几。区分度太低的列单独建索引几乎没有意义因为优化器算完发现走索引扫描 100 万行再加 100 万次回表还不如直接全表扫描来得快。建索引前判断字段是否值得建先跑一条 SQL 看看基数分布SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_dist, COUNT(DISTINCT status) / COUNT(*) AS status_dist, COUNT(DISTINCT created_at) / COUNT(*) AS created_dist FROM orders;经验上区分度低于 10% 的字段不建议单独建索引。但这不是绝对的低区分度字段如果作为联合索引的前缀配合其他高区分度字段一起使用仍然能发挥作用。举个例子status只有 3 个值单独索引没意义但where status 1 and created_at between ...这种查询里status放在联合索引的最前面能把范围扫描限制在一个较小的区间。要注意区分度的计算也会受到数据分布影响。一个字段整体区分度很高但某个具体值占了 90% 的行那么当查询条件命中这个高频值时优化器依然可能放弃索引。这是后面排查章节要展开的内容。3.2 联合索引列顺序最左前缀与范围查询的影响联合索引是索引调整里最常用的手段也是问题最多的地方。联合索引遵循最左前缀原则索引按照定义时的列顺序建立 B树查询条件里必须包含最左边的列或者其前几列的组合索引才有可能被用到。列顺序的排列有几个常用经验准则。第一等值条件放在前面范围条件放在后面。因为等值条件可以直接定位范围条件只能圈定一个区间如果把范围条件放前面后面的等值列就很难高效利用。第二高频查询条件优先考虑。第三区分度高的列通常放前面但也要结合等值、范围来权衡。用一个实际场景说明查询条件是where status 1 and created_at 2024-01-01 and created_at 2024-02-01 order by created_at desc limit 20。这里status是等值条件created_at既是范围条件又是排序字段。设计联合索引时优先把等值列status放前面created_at放后面索引形如(status, created_at)。这样既能快速定位到 status1 的区间又能在索引内部按 created_at 有序扫描顺带解决排序问题。这里要特别提醒范围条件后面再放其他列意义是很有限的。联合索引(a, b, c)中如果 b 用了范围查询那么 c 基本只能用索引下推来过滤部分数据而不能继续参与精确定位。设计索引时要把经常用于范围查询的列尽量放在联合索引的后半部分。3.3 覆盖索引与索引下推少回表一次都是赚回表开销是响应时间的重要影响因素。二级索引查询时每命中一条记录就要回聚簇索引取一次完整行数据。如果查询的行数多回表代价会被放大。有一种索引设计能直接消灭回表就是覆盖索引让查询需要的所有列都包含在同一个二级索引里。比如前面订单表例子高频查询只要id, order_no, total_amount三个字段那么设计一个包含这些字段的联合索引查询时直接在二级索引的叶子节点就能拿到全部数据EXPLAIN 里会显示Using index不再回表。这种优化对响应时间特别明显。但覆盖索引不是越宽越好。索引列越多B树占用空间越大写入成本越高。把text、blob之类的长字段放进索引基本是灾难。实际设计时只覆盖高频查询需要的列并控制列数量。通常一张表的索引总宽度控制在合理范围内能用前缀索引的场景就用前缀索引。和覆盖索引经常一起出现的还有“索引下推”Index Condition PushdownICP。MySQL 5.6 以后联合索引中范围条件之后的列可以在索引层直接过滤减少回表次数。EXPLAIN 里显示Using index condition就是这个优化在工作。很多时候看似索引没走全其实 ICP 已经在帮你减少回表量了别误判成索引失效。3.4 冗余索引排查你删掉的索引可能有一半做索引调整不只是做加法也要做减法。冗余索引是数据库性能的隐性杀手写放大、占用空间、优化器选择负担全部来自这些“看似有用但实际重复”的索引。最常见的冗余场景已经建了联合索引(a, b)又单独建了索引(a)。因为联合索引的最左前缀已经能覆盖(a)的查询需求单独索引(a)就是纯冗余。还有一种情况主键是id又建了一个唯一索引在(id)上这也是重复。在 MySQL 8.0 的 sys 库里可以直接查冗余索引SELECT * FROM sys.schema_redundant_indexes;也可以用 Percona Toolkit 里的pt-duplicate-key-checker定期扫描。对于确认冗余的索引建议选业务低峰期先删除。但删除之前要观察一段时间确认没有查询的执行计划依赖它。我一般先做“软删除”——改成不可见索引观察一周没问题再真正 DROP这样最安全。4. 响应时间优化实战订单查询从1.8秒到几十毫秒理论落到实战才是关键。我用一个真实调整案例串起整个流程表结构简化过但思路完全一致。这个案例也是很多订单类业务里常见的查询模式按订单状态和时间范围排序分页。4.1 用EXPLAIN读体检报告type、rows、Extra先看表结构CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id INT NOT NULL, status TINYINT NOT NULL, channel TINYINT NOT NULL, created_at DATETIME NOT NULL, total_amount DECIMAL(10,2) NOT NULL, KEY idx_status (status), KEY idx_created_at (created_at) ) ENGINEInnoDB;业务查询长这样SELECT id, order_no, total_amount FROM orders WHERE status 1 AND created_at 2024-01-01 00:00:00 AND created_at 2024-04-01 00:00:00 ORDER BY created_at DESC LIMIT 20;表里有 300 万行数据status1 大约占 30%。这句 SQL 在线上跑了 1.8 秒。EXPLAIN 结果如下列名调整前值typeALLpossible_keysidx_status, idx_created_atkeyNULLrows3012340ExtraUsing where; Using filesort问题很明显全表扫描、没有用到任何索引、还额外走了文件排序。表上明明有idx_status和idx_created_at两个单列索引为什么优化器一个都不用因为status区分度低用idx_status过滤后还要扫 90 万行回表用idx_created_at扫三个月的数据也有几十万行回表。优化器一算代价发现都不如全表扫描。而order by created_at需要的排序因为索引没选上只能走 filesort雪上加霜。4.2 从单列索引到联合索引调整过程实录第一步计算区分度确认 status 不适合单独建索引但适合和 created_at 组合使用。第二步设计联合索引。查询条件是 status 等值 created_at 范围排序字段是 created_at所以联合索引设计为(status, created_at)ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);加完之后再看 EXPLAIN列名调整后值typerefpossible_keysidx_status_createdkeyidx_status_createdrows约86000ExtraUsing index conditiontype 从 ALL 变成 ref扫描行数从 300 万降到 8.6 万响应时间从 1.8 秒降到 80 毫秒左右。但还能更好。因为 select 的字段是id, order_no, total_amount三个字段里的order_no和total_amount不在索引中所以仍然需要回表。如果这个查询是首页接口、调用频率极高可以考虑进一步做成覆盖索引ALTER TABLE orders ADD INDEX idx_status_created_cover (status, created_at, order_no, total_amount);这里的id不用显式加入二级索引因为 InnoDB 二级索引叶子节点本来就存有主键值。调整后 Extra 变成Using index响应时间降到了 30 毫秒左右。这里要做一个权衡order_no和total_amount加进索引之后索引宽度增加了写入性能会有一定损耗。如果这是核心交易表写入频繁就要根据业务特点来决定要不要牺牲写入换查询。如果不是高频查询只建(status, created_at)就够了。4.3 生产环境加索引的顺序先隐藏再生效线上直接执行ALTER TABLE ADD INDEX有风险尤其是大表可能引起锁表、复制延迟、磁盘占用飙升。MySQL 8.0 的很多索引操作是 INPLACE 的但业务高峰期执行大表 DDL 仍然要谨慎。我推荐一个“先隐藏再生效”的流程全程可控可回滚。首先加索引时直接标记为不可见ALTER TABLE orders ADD INDEX idx_status_created (status, created_at) INVISIBLE;此时索引已经建立但优化器默认不会使用它。接着在一个会话里开启测试SET SESSION optimizer_switch use_invisible_indexeson;再跑一次 EXPLAIN验证执行计划是否符合预期。确认无误后把索引改成可见ALTER TABLE orders ALTER INDEX idx_status_created VISIBLE;如果发现索引有问题或者执行计划反而变差了直接删除索引就可以不会对线上产生任何影响。这个隐藏索引功能是 MySQL 8.0 提供的后面的版本好好利用这是我个人最推荐的生产实践。如果表的体量特别大比如到了千万行级别普通 ALTER 也可能对主库造成较大压力可以考虑 gh-ost 或 pt-online-schema-change 这类在线变更工具。但工具不是银弹核心原则是一样的低峰期操作、观察主从延迟、准备好回滚方案。4.4 上线后验证收益对比响应时间与扫描行数索引上线不等于工作结束。验证收益才是完整闭环。很多人优化完只跑一次 SQL看到变快了就觉得完成这种做法不严谨。我建议从三个维度验证第一个维度是慢查询数量。看sys.statement_analysis里这条 SQL 的rows_examined_avg是否明显下降总延迟是否下降。第二个维度是业务监控里的 P95 响应时间这个是用户真实感受的指标。第三个维度和之前记录的基线对比同样的时间段、同样的查询条件调整前后各自耗时多少。SELECT query, exec_count, total_latency, rows_examined_avg, rows_sent_avg FROM sys.statement_analysis WHERE query LIKE %orders% ORDER BY rows_examined_avg DESC;这段查询能帮助确认扫描行数是否真正下降。响应时间会受到缓存、网络、业务高并发的影响但 rows_examined 是一个相对稳定的指标。如果 rows 降了量级但响应时间没降就要看看是不是有其他瓶颈比如锁等待、网络延迟。5. 索引失效与排查心法加了索引也不生效的原因索引调整过程中最折磨人的不是“没索引慢”而是“明明有索引、也按规则加了索引但执行计划不用它”。这种情况占比相当高。系统性排查这些问题能省下大量重复试错的时间。5.1 函数操作、隐式转换索引失效两大元凶第一个元凶是对索引列做函数运算。比如SELECT * FROM orders WHERE DATE(created_at) 2024-01-15;即使created_at上有索引这条查询也走不了因为索引存储的是原始值不是DATE(created_at)算出来的结果。优化器不知道如何用索引键去匹配一个被函数处理过的值。改成范围查询就能用上索引SELECT * FROM orders WHERE created_at 2024-01-15 00:00:00 AND created_at 2024-01-16 00:00:00;第二个元凶是隐式类型转换。最常见的是字符串列和数字比较。比如mobile是 VARCHAR 类型但查询写成WHERE mobile 13800138000MySQL 会把字符串转成数字来比较索引列被迫发生类型转换索引就失效了。改成带引号的字符串写法执行计划会立刻不同。排查这类问题有个快速方法看 EXPLAIN 里 type 是什么、key 是什么。如果 type 从 ref 变成 ALL而且possible_keys里明明有候选索引那优先检查 SQL 里是否对索引列做了函数操作或者隐式转换。5.2 优化器不选索引低基数、统计信息与数据倾斜还有一种情况是索引存在但优化器“觉得”没必要用。前面说过低区分度的问题这里补充两种情况。一种是统计信息过期。InnoDB 的优化器依赖统计信息来估算扫描行数如果统计信息不准判断就会失准。这种情况下ANALYZE TABLE往往能解决问题ANALYZE TABLE orders;跑完之后执行计划可能会发生变化甚至直接选中了正确的索引。很多“索引加了没用”的假象其实就是统计信息太旧。另一种是数据倾斜。同样的status 1不同时间数据量差距巨大。如果 status1 在平时只占 2%但在活动期间占了 70%那平时优化器用索引活动期间全表扫描反而更快。这种场景下靠单一索引很难稳定要结合查询条件里其他字段来缩小范围或者做条件拆分。优化器真的选错索引时可以用FORCE INDEX临时验证效果但我不建议长期依赖它。写进代码里的FORCE INDEX会让后续索引优化失去灵活性一旦数据分布变化反而成为负担。更合理的做法是重新设计更贴合查询的联合索引。5.3 深分页与排序排查时容易忽略的索引关联问题有时候索引设计没问题执行计划也走了索引但响应时间还是高。这时候要考虑是不是“深分页”在捣乱。limit 100000, 20这种写法即使完全走索引数据库也要先扫描并跳过前 100000 行才能拿到目标 20 行。数据量越大偏移量越大耗时越不可控。一个常见优化手段是“延迟关联”先查出主键范围再回原表取详细数据SELECT t.id, t.order_no, t.total_amount FROM orders t JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY created_at LIMIT 100000, 20 ) tmp ON t.id tmp.id;子查询里只用联合索引和主键扫描的数据量小很多外层再按主键批量取行整体效率提升明显。深分页更彻底的解法是用游标方式比如“上一页最大 created_at”作为下一次查询的起点但这需要业务配合属于越过分页优化的大方向了。排序和分组也经常被忽略。ORDER BY created_at如果索引包含 created_at 且顺序一致就能用上索引有序性避免 filesort。这里要注意的是升序和降序。MySQL 8.0 支持降序索引如果业务里大量使用ORDER BY created_at DESC可以按倒序建索引结构上更贴合查询。不过 8.0 之前的版本还是得靠索引扫描后做反向读取来完成降序需求。5.4 判断索引是否生效的完整排查链路把前面的经验整理成一个可执行的排查流程遇到任何“索引加了但似乎没生效”的问题按顺序走一遍用 EXPLAIN 看type、key、rows、Extra确认优化器最终选了哪条索引。看key_len联合索引里能判断用到哪些列。如果定义了三列索引但key_len只算了一列的长度说明后面列的过滤没有参与进来。对比rows和实际耗时。有时候执行计划显示索引生效了但响应时间没有明显下降要排查是不是回表多、锁等待、深分页这些问题。临时把索引用FORCE INDEX或IGNORE INDEX强制开关对比两次查询的耗时差异。用 performance_schema 里的语句统计表看执行次数和平均扫描行数确认长期趋势。这套链路下来基本能定位到索引问题的真正环节。下面是一张排查速查表现象可能原因处理方向typeALL 且 possible_keys 为空查询条件没匹配任何索引检查字段、重新设计联合索引typeALL 但 possible_keys 有索引低区分度、统计信息过期、隐式转换算区分度、ANALYZE TABLE、改写SQLkey 用了索引但 Extra 有 filesort联合索引列顺序和排序字段不一致调整联合索引列或建降序索引走索引但响应时间依然高深分页、回表过多、锁等待延迟关联、覆盖索引、查锁等待优化器选了错误的索引统计信息不准、数据倾斜更新统计信息、设计更贴合查询的索引我自己的习惯是每次做完一轮索引调整都在本地留一份调整前后的 EXPLAIN 和响应时间记录。下次再遇到类似问题直接拿旧数据对照能少踩很多重复的坑。索引调整这件事没有玄学执行计划就是数据库在告诉你它打算怎么干活顺着它的思路把扫描行数降下来、把回表次数减下去、把排序转成索引序响应时间自然就跟着下来了。