做开发这几年我接过不少线上事故数据库被拖垮的场景见过不止一次其中十次有八次不是 SQL 写得多复杂而是索引加得想当然。索引优化听着是基础课可真到生产环境隐式类型转换、最左前缀、覆盖索引、统计信息过期……随便一个细节都能让一条本该毫秒级返回的查询变成几十秒的全表扫描。这篇避坑指南围绕“索引优化”讲我踩过的坑、帮别人擦过的坑也讲我后来怎么一步步把索引设计从“凭感觉”变成“看执行计划”。适合正在被慢查询折磨的后端开发也适合刚接手线上数据库的运维和 DBA 新人。1. 为什么加了索引反而更慢先拆掉对索引的三大误解1.1 误解一只要查询慢加索引就对了很多人的第一反应是查询慢加索引。这个思路本身不算错但错在“不加分析地加”。我遇到过一张几百万行的业务表前端列表接口超时开发直接给 where 条件里的每个字段都建了一个索引。结果查询确实快了一点但写入和更新肉眼可见地变慢索引文件比数据文件还大。索引的本质是在原表之外再维护一棵 B 树。每次 INSERT、UPDATE、DELETE除了改数据行还要同步更新对应的索引树。索引越多写的放大越明显。一张表如果一半字段都有索引写入性能不降才怪。正确做法是先用慢查询日志把具体 SQL 捞出来通过执行计划分析到底卡在哪一步而不是无差别加索引。我之前排查过一张表被加了 12 个索引实际常用查询用到的不到 5 个剩下全是历史包袱。后来分批清理写入性能提升了差不多 30%。记住加索引不是目的让那条慢 SQL 走对路径才是目的。1.2 误解二索引越多越好这个误解和上一个很像但坑点不同。索引多不只是浪费空间还会干扰优化器的判断。每个二级索引都包含索引列和主键值。InnoDB 里数据本身存在主键聚簇索引上二级索引查完往往还要回表查一次完整数据。如果一条查询能用到两个单列索引优化器可能选择 index merge也可能觉得合并成本太高直接全表扫。这时候开发会困惑明明两个字段都有索引为什么不用不是没用是优化器算完账之后觉得不划算。多索引的另一个问题是优化器的成本估算依赖统计信息索引越多估算路径越多选错的风险也越高。尤其在一些老项目里每个新人都按自己的习惯加索引最后索引泛滥成灾。我见过一张 800 万行的表光二级索引就有十几个数据页没多大索引页倒是占了好几个 G。这种表简单查询都可能变慢因为优化器在路径选择上耗费的时间也在增长。1.3 误解三索引建好就一劳永逸索引不是建完就完事的静态对象。表里的数据天天在变查询模式也在变。有个典型场景某订单表一开始在 status 字段上建了索引上线时效果很好。跑了大半年历史数据越来越多status1 的行占了全表 90%。优化器一算通过 status 索引要读出的数据量太大不如直接全表扫。于是这条 SQL 又开始慢了。索引没变SQL 没变但数据分布变了执行计划就变了。另一个常见情况是原本的高频查询条件加入新字段后旧的联合索引不再匹配。比如原来高频查询是 where user_id ?后来改成 where user_id ? and status ?旧的单列索引 user_id 能定位用户但还要在大量行里过滤 status性能逐渐吃紧。这时候需要建新的联合索引同时评估旧索引是否还有保留价值。所以索引是要持续 review 的而不是装上去就不管了。2. 索引到底是怎么“失效”的7个最容易踩的坑2.1 隐式类型转换字符串字段上查数字先看一个极其常见的例子。表里有个订单号字段定义是 varcharCREATE TABLE user_order ( id bigint unsigned NOT NULL AUTO_INCREMENT, user_id bigint unsigned NOT NULL, order_no varchar(64) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_create (user_id, create_time) ) ENGINEInnoDB;有人在代码里图省事直接查数字SELECT * FROM user_order WHERE order_no 20240115001;order_no 是字符串查询条件是数字。MySQL 在做比较时会把字符串列转换成数字再比较相当于给索引列包了一层 CAST。但 B 树索引里存的是原始字符串没法按转换后的数字直接二分查找于是只能放弃索引。这种问题很隐蔽因为返回结果可能看起来是对的只是扫描行数从几十变成了上百万。如果你建了索引但 EXPLAIN 显示 type 是 ALL先检查一下字段定义和查询参数类型是否一致。手机号、订单号、身份证这种“长得像数字”的字段建议一律用字符串类型存储查询时也严格传字符串不要依赖数据库帮你转。2.2 对索引列使用函数或计算日期范围查询是最典型的重灾区SELECT * FROM user_order WHERE DATE(create_time) 2024-01-15;如果 create_time 上有索引这条 SQL 大概率用不上。原因很简单索引树里存的是 create_time 的原始值不是 DATE(create_time) 这个计算结果。你在索引列上套了函数等于改变了列的内容B 树的排序结构就帮不上忙了。正确写法是把它改成范围查询SELECT * FROM user_order WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;同理WHERE total_amount 1 100这种对列做数学运算的写法也应该改写成WHERE total_amount 99。核心原则是索引列保持独立别让它出现在函数、表达式、运算的右侧或参数里。2.3 前导模糊查询和 OR 条件模糊查询是业务里躲不开的需求。LIKE abc%能用索引因为 B 树可以根据前缀 abc 快速定位。但LIKE %abc用不上因为你要的字符串可能出现在字段任意位置索引树无法从中间开始匹配。更麻烦的是LIKE %abc%不仅前导模糊而且尾部也不确定索引基本用不上。如果业务确实需要任意位置的模糊匹配常规索引帮不了应该考虑全文索引、专门的搜索组件或者采用反向存储之类的手段。这属于架构选型问题不是靠硬调 SQL 能解决的。OR 条件的坑也很典型SELECT * FROM user_order WHERE user_id 1001 OR order_no A1001;如果 user_id 和 order_no 都有索引优化器可能选择 index merge但这不是一定发生。一旦其中一个条件没有可用的索引或者优化器认为合并后的成本仍然高于全表扫描它就会选择 ALL。处理这类查询更稳妥的方式是用 UNION ALL 拆开让两边各自走自己的索引SELECT * FROM user_order WHERE user_id 1001 UNION ALL SELECT * FROM user_order WHERE order_no A1001;当然拆之前要确认两边都不产生重复数据业务允许再去重。2.4 联合索引顺序与最左前缀假设现在只有一个联合索引 idx_user_create(user_id, create_time)执行SELECT * FROM user_order WHERE create_time 2024-01-15;这条 SQL 用不上索引。因为联合索引的排序规则是先按 user_id 排序user_id 相同时再按 create_time 排序。单独拿 create_time 来查等于在一本先按姓再按名排序的电话本里只知道名不知道姓翻遍全本也定位不到。这就是最左前缀原则。你可以把索引定义顺序理解成“从左往右逐层生效的筛选器”查询条件里必须从联合索引的第一个字段开始匹配才有机会用到后续字段。等值条件放前面范围条件、排序字段放后面是联合索引设计的基本准则。比如SELECT * FROM user_order WHERE user_id 123 AND create_time 2024-01-15;这条就能用 idx_user_create因为索引先定位 user_id123再在结果集里按 create_time 范围扫描顺序天然匹配。2.5 字符集与排序规则不一致这个坑多在表关联时出现。比如订单表和支付表都有 order_no 字段但一张表是 utf8mb4_unicode_ci另一张是 utf8mb4_general_ci或者干脆一张是 latin1一张是 utf8mb4。MySQL 在比较两个字符串时发现字符集或排序规则不一致会做隐式转换其中一边的索引列就可能失效。我排查过一个例子两张表关联查询A 表 order_no 是 utf8mb4B 表 order_no 是 utf8mb3执行计划里 B 表走了全表扫描。业务方一直以为数据量问题后来把两边字段统一成 utf8mb4_0900_ai_ci或至少使用相同排序规则关联查询从十几秒降到几十毫秒。建议在建表之初就定好一套统一的字符集规范尤其对订单号、用户名这类需要跨表关联的字符串字段千万别各建各的。检查时可以先用 SHOW CREATE TABLE 看两边的字段定义把字符集和排序规则调成一致再观察执行计划。2.6 索引列上大量的 NULL很多人不太关注 NULL 对索引的影响。MySQL 的 B 树索引本身是允许 NULL 的但 NULL 在索引中会占用额外标记复合索引里某个字段大量为 NULL 时索引的选择性会变得很差。更麻烦的是查询条件。比如SELECT * FROM user_order WHERE status IS NULL;如果 status 列绝大多数是 0 和 1只有极少数 NULL优化器可能仍然走索引因为 NULL 有唯一标记。但如果 NULL 占比很高数据分布很偏优化器算完成本后很可能放弃索引。还有一种情况是IS NOT NULL不等值语义本来就难走索引尤其返回大量行时。设计上的建议是字段尽量不要允许 NULL业务上用默认值代替比如 status 默认 0最后更新时间默认 0 或 1970-01-01。这样不仅索引效果更稳定查询条件写起来也干净很多。2.7 优化器觉得全表扫描更快最后这个“坑”严格说不是索引失效而是优化器主动选择了不用索引。常见原因有两个一是表太小比如只有几百行优化器认为全表扫比走索引回表更划算二是数据分布太偏比如某字段 95% 都是同一个值通过索引要读出的行数过多还不如直接扫。遇到这种情况不要立刻FORCE INDEX硬刚。先看统计信息是否过期执行ANALYZE TABLE user_order;刷新一下。然后看数据分布如果字段枚举值太少且有明显倾斜这个字段本来就不适合做高选择性的索引。真正该做的是改写查询条件、调整索引设计或者接受全表扫描的结果。3. 正确的索引设计实操从 EXPLAIN 到 SQL 改写3.1 先看执行计划而不是猜当你面对一条慢 SQL第一步永远是 EXPLAIN而不是拍脑袋决定要不要加索引。看执行计划时我重点看几个字段字段含义我的判断type访问类型从好到差大致是 system const eq_ref ref range index ALLrows预估扫描行数这个数越少越好但不绝对还要看回表成本Extra额外信息出现 Using filesort、Using temporary 时通常需要优化实际查看时最怕只看 type。有人看到 type 是 ref 就觉得没问题但 rows 显示几十万还带着 Using filesort这明显还要优化。反过来如果表很小type 是 ALL 也不一定需要加索引优化器自己会判断。我常用的检查步骤是EXPLAIN SELECT id, order_no, status, total_amount FROM user_order WHERE user_id 123 AND status 1 AND create_time 2024-01-15 00:00:00 ORDER BY create_time DESC LIMIT 20;先看 type 和 rows再看 Extra 里有没有 filesort 或回表。一条 SQL 如果每一步都能说清楚“索引怎么被用上的、还有多少行需要回表”索引设计才算合格。3.2 联合索引设计顺序等值在前范围在后联合索引的字段顺序直接决定了这条索引能服务哪些查询。设计时遵循一条主线等值条件字段放在最前面范围条件和排序字段放在后面。为什么索引树先按第一个字段排序第一个字段相同再按第二个字段排序。等值条件能把范围一下子缩小到一个确定的分支后续字段在这个分支下依然保持有序所以范围、排序都能顺利用上。如果反过来让范围字段播种后续字段在满足范围的所有节点里不是全局有序的优化器就要额外做过滤甚至排序。来看一个实例。查询是SELECT id, order_no, status, total_amount FROM user_order WHERE user_id ? AND status 1 AND create_time ? ORDER BY create_time DESC LIMIT 20;适合的联合索引是(user_id, status, create_time)而不是(status, user_id, create_time)。原因很直接user_id 和 status 都是等值条件先通过它们把记录定位到很小范围create_time 在这个范围内依旧有序可以服务范围过滤和 ORDER BY。这里还可以算一下字段区分度。比如SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_sel, COUNT(DISTINCT status) / COUNT(*) AS status_sel FROM user_order;假设表有 100 万行user_id 有 10 万种取值选择度约 0.1status 只有 3 种取值选择度约 0.000003。单看选择度status 不适合放第一位但它作为联合索引里 user_id 之后的等值过滤条件能把 User 下的结果再缩小依然有价值。所以选择度不是唯一标准关键要看这条索引到底为哪些查询服务。3.3 覆盖索引用空间换回表InnoDB 的二级索引叶子节点存储的是“索引列 主键值”。如果查询需要的字段正好都包含在索引里就不需要再回表查主键聚簇索引执行计划里会出现Using index。这就是覆盖索引。看这个例子SELECT id, user_id, create_time FROM user_order WHERE user_id 123 AND create_time 2024-01-15 00:00:00 ORDER BY create_time DESC;如果表上有(user_id, create_time)联合索引这个查询需要的 id、user_id、create_time 都在索引里id 是主键二级索引自带所以全程不需要回表Extra 里能看到 Using index。覆盖索引的诱惑很大但不能无脑加字段。如果你为了让查询不回表把 order_no、total_amount、status 全塞进索引那么这个联合索引会变得又宽又大写入时维护成本暴涨。我的判断标准很简单这条 SQL 是不是核心高频查询每次回表是不是真的很多行如果都满足而且追加的字段长度可控覆盖索引才有性价比。否则宁可回表也别把索引堆成一张“小表”。3.4 一个完整案例订单查询优化的全过程用一个实际业务场景收尾。订单模块里有个高频查询用户中心列表页要展示“当前用户最近 30 天成功的订单”按创建时间倒序分页 20 条。初始 SQL 大致是这样SELECT id, order_no, status, total_amount FROM user_order WHERE user_id ? AND status 1 AND create_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY create_time DESC LIMIT 20;第一版表上只有主键没有任何二级索引。EXPLAIN 显示 type 是 ALLrows 接近全表Extra 里有 Using where明显是全表扫描。实际线上查询耗时 2 秒多这还只是单用户查询。第二版我先加了一个(user_id, create_time)联合索引。EXPLAIN 变成了 type 是 range因为 create_time 是范围条件rows 大幅减少ORDER BY create_time 也能利用索引顺序不再 filesort。但这时候还有问题status 1 的过滤需要回表后逐行判断如果某个用户的数据量很大回表行数依然不少。第三版改成联合索引(user_id, status, create_time)。两个等值条件先定位create_time 做范围从里到外顺序正好匹配。EXPLAIN 里 type 是 rangerows 进一步缩小。注意这个场景里 status 不是放在最前面而是放在 user_id 后面因为 user_id 才是把选择性提到最高的字段。优化前后对比大概是这样方案typerowsExtra线上耗时无索引ALL300 万Using where2200 ms(user_id, create_time)range2 万Using where180 ms(user_id, status, create_time)range800Using where40 ms这个案例的教训是一条 SQL 的索引设计不是一步到位而是跟着执行计划一步一步收敛。每一步改动都要用 EXPLAIN 的新结果来验证别改完了不看效果。4. 索引维护与排查上线之后怎么持续避坑4.1 慢查询日志和统计信息索引上线只是开始。真正的考验是它在真实负载下能不能稳定工作。我一般会先打开慢查询日志把执行时间超过一定阈值的 SQL 抓出来看看有没有走偏的索引。慢查询抓到后除了看 SQL还要看统计信息。MySQL 优化器依赖统计信息来估算扫描行数如果表数据发生了大批量导入、删除或者数据分布剧烈变化统计信息可能过时优化器就会选错索引。这时候执行一句ANALYZE TABLE user_order;让优化器重新采样很多“没道理的慢查询”能直接缓解。另外SHOW INDEX FROM user_order;里的 Cardinality 字段可以粗略判断索引区分度。Cardinality 是优化器采样估算出来的不是精确值所以我只看它做初步筛选真正选索引还是以 EXPLAIN 的 rows 和实际耗时为准。4.2 索引冗余与重复怎么清理索引维护里最容易被忽略的是冗余索引。典型场景是表上已有(user_id, create_time)联合索引后来某个开发又加了一个(user_id)单列索引。从功能上看所有能用(user_id)的查询都能用(user_id, create_time)替代因为最左前缀已经覆盖了 user_id。所以单列索引大概率是冗余的。但清理前要谨慎。(user_id, create_time)虽然能覆盖 user_id 查询但索引体积更大如果 user_id 查询极其高频且对写入不敏感保留单列索引也不是完全没道理。我的建议是先做主从或测试环境验证再用不可见索引的方式把候选索引“隐藏”掉观察一段时间确定没有业务依赖后再物理删除。MySQL 8.0 里可以直接用ALTER TABLE user_order ALTER INDEX idx_user_id INVISIBLE;然后跑一段时间的监控。如果慢查询没有增加再执行 DROP INDEX 收尾。这个方法比直接删安全得多。4.3 索引碎片整理和在线变更索引维护还有一个容易忽视的点碎片。频繁的 UPDATE 和 DELETE 会在 B 树里造成页分裂、页合并长期下来索引页可能稀疏扫描效率下降。查看表的数据碎片可以看 information_schemaSELECT table_name, data_free FROM information_schema.tables WHERE table_schema your_db AND table_name user_order;data_free 不是唯一标准但能作为参考。如果碎片严重可以用OPTIMIZE TABLE user_order;或者重建表来整理。不过大表执行 OPTIMIZE 会锁表时间窗口不够的时候非常危险基本只在低峰期才敢碰。对大表加索引、删索引这类结构变更更稳妥的是用在线变更工具在业务低峰期分批完成降低锁持有时间。切记不要在业务高峰期对一个几千万行的表直接执行 DDL哪怕数据库号称支持在线 DDL元数据锁和主从延迟也常常会给你上一课。4.4 新增索引的灰度策略最后说一个常被忽略的流程问题索引变更也要灰度。我见过不止一次开发在测试环境验证了索引没问题直接在生产大表上执行 CREATE INDEX结果主从延迟飙到几百秒业务读写全部受影响。这未必是索引本身不好而是变更方式太粗暴。我现在给大表加索引会这么走先在测试环境准备一份和生产量级相当的数据验证执行计划和耗时。然后查询类索引可以用不可见索引先建上让优化器在真实负载里“试探性”地走一遍确认没问题再 VISIBLE。观察期里看慢查询、CPU、磁盘 IO和加索引之前对比。如果性能没有提升直接删掉不留僵尸索引。同时每一条新建索引都要记录服务哪条 SQL、创建时间、预期收益。这样三个月后再看这张表还能说清楚每个索引活在世上的理由。5. 我后来养成的几条索引习惯这些习惯是我一次次把线上搞慢、又一点点擦回来的教训不一定适合所有团队但大概率能帮你少走弯路。第一先定位最耗时的 SQL再谈索引。没有慢查询日志和 EXPLAIN 支撑的索引优化都是猜。猜对了是运气猜错了是事故。第二每张表主键之外二级索引尽量控制在一个合理数量内。具体数量看写入量写多读少的表宁可让统计报表慢一点也别堆索引。索引是读的加速器也是写的拖累者这个平衡必须心里有数。第三设计联合索引时先把这张表的高频查询都列出来找条件字段的交集别为一个查询单独建一个索引。很多冗余索引就是这么来的每个查询各建各的最后变成索引森林。第四发版前把新增索引和对应 SQL 的执行计划保存好作为 review 依据。下次大促前检查性能先翻历史记录比临时猜靠谱得多。第五不要怕删索引但要用不可见索引安全地删。索引删除不是回滚不回来的事但删错了会让线上大面积变慢先隐藏、再观察、后删除这个顺序别跳。说到底索引优化不是炫技而是理解数据结构和查询成本之后的克制。你每建一个索引都应该能说清楚它服务哪条 SQL会让哪条写入多付出多少代价。说不清楚就别急着建。
