1. 一条三秒的查询把索引这件事重新摆到我面前凌晨一点半被告警叫醒订单列表接口 P99 从 80ms 涨到 3.2s。翻了下监控数据库 CPU 打满慢日志里躺着一条再普通不过的 SQLselect * from t_order where user_id 10086 order by create_time desc limit 20;。这张表 2400 万行user_id上有索引理论上不该慢。但explain出来typeALL、rows2400万全表扫。事后复盘原因是一次数据订正脚本把user_id从bigint改成了varchar而代码里传参还是数字隐式类型转换直接把 MySQL索引 干废了。这件事之后我把索引相关的知识重新梳理了一遍从最底层的 B树 结构到索引的几种类型再到实际使用中那些神出鬼没的失效场景。这篇内容就是那次梳理的产物偏实战、偏排查不讲教科书式的定义堆砌。适合已经能写 SQL、但一遇到慢查询就只会加索引或者加机器的同学也适合想搞明白为什么索引会失效这类问题背后逻辑的人。读完之后你至少能做到三件事看一眼explain就知道问题出在哪、知道什么情况下不该建索引、遇到明明有索引却没用上能自己排查到底。需要说清楚的是索引不是银弹它是拿空间和写入性能换查询性能的一笔交易。2400 万行的表上加一个索引磁盘多占几百 MB 到几 GB每次insert/update/delete都要多维护一棵树。所以给每个 where 字段都加上索引这种做法短期看着爽长期一定是灾难。搞清楚它怎么工作比记住要给字段加索引这句话重要得多。2. B树凭什么成了MySQL索引的默认数据结构2.1 先从磁盘IO的最小单位说起理解 B树得先接受一个前提数据库的数据存在磁盘上而磁盘读写的最小单位不是一行记录而是一个数据页InnoDB 里默认 16KB。这意味着你哪怕只想读一行 100 字节的数据操作系统也可能要搬 16KB 进内存。所以衡量索引好坏的核心指标不是比较次数而是磁盘IO次数具体说就是树有多高。二叉树、红黑树这类结构在内存里跑得飞快但一放到磁盘上就废了1000 万条数据红黑树的树高大概在 2×log₂(1000万) ≈ 46 层最坏情况要 46 次磁盘随机读。而 B树 的特性是矮胖——非叶子节点只存键值和一个页指针不存真实数据行所以单个页能塞下的键非常多树高能压到 3 到 4 层。给你算一笔账这也是面试里最爱问的一道题。假设主键是bigint8 字节InnoDB 的页指针固定 6 字节那么非叶子节点里一个键值 指针的组合是 14 字节。16KB 的页去掉页头页尾大约剩 15KB 可用15000 ÷ 14 ≈ 1070也就是说一个非叶子节点能指向 1070 个下层页。叶子节点存的是真实数据行假设一行 1KB一页能放 16 行。那么三层 B树 的容量是1070 × 1070 × 16 ≈1832 万行。四层就是 1070 倍接近 200 亿行。结论很直接**一棵三层 B树 就能撑住两千万行级别的表任何一次等值查询最多三次磁盘IO而且根节点和第二层大概率常驻内存Buffer Pool 缓存实际往往只有一到两次物理IO。**这就是 B树 统治 MySQL索引 的根本原因不是什么高深算法纯粹是把磁盘特性利用到了极致。2.2 和B树、哈希索引的正面比较很多人会问B树 不也是多路平衡树吗为什么不用它差别在两点都非常关键。第一B树 的非叶子节点也存数据行。这会导致单个页能容纳的键数量骤减树被迫长高。同样的数据量B树 可能比 B树 多一层甚至两层磁盘IO 直接翻倍。而且非叶子节点里的数据行在查询时是路过的属于浪费了页空间。第二B树 的叶子节点用双向链表串起来了。这个设计对范围查询是决定性的where create_time between 2024-01-01 and 2024-01-31B树 只需要先定位到起始位置然后沿着链表顺序往后扫就行全程顺序IO。而 B树 要做中序遍历节点在磁盘上东一块西一块随机IO 一大堆。至于哈希索引它确实能做到 O(1) 的等值查找但完全不支持范围查询、排序也不支持最左前缀。所以它只适合纯等值场景。InnoDB 默认建的都是 B树 索引但它有一个自适应哈希索引Adaptive Hash Index的小机制当某个索引页被频繁以相同模式访问时InnoDB 会自动在内存里为它建一套哈希索引来加速这个动作是引擎自己做的你既不用配置也没法直接干预。我在实际使用中发现这个特性在高并发点查场景下收益可观但也会占用额外的 Buffer Pool 内存有时候反而挤压了正常页的缓存空间属于权衡项。2.3 聚簇索引与二级索引一次查询走了几条路InnoDB 是索引组织表意思是数据行本身就存在主键索引的叶子节点上这棵主键索引树就叫聚簇索引Clustered Index。这一点直接决定了所有二级索引的查询方式。你给user_id建的索引是一棵独立的 B树它的叶子节点存的不是整行数据而是user_id的值 主键值。所以当你执行select * from t_order where user_id 10086时实际走了两步先在user_id索引树上找到10086拿到对应的主键 id 列表再拿这些 id 去主键索引树上一个个查出完整行。这个第二步就是老生常谈的回表。提示回表是二级索引查询的固有成本不是故障但可以通过覆盖索引消除。判断依据是explain的 Extra 里出现Using index。回表的代价很大因为主键值通常是乱序的回表意味着大量随机IO。这也是为什么我一直建议主键用自增或者趋势递增的值比如雪花ID的某种变体——如果主键是随机的比如 UUID二级索引里存的主键没规律回表就是完全随机的磁盘访问性能会差一大截同时插入时还会引发频繁的页分裂。3. 索引家族的全景从数据结构到功能分类3.1 按数据结构分的四种索引MySQL 支持多种索引但它们归属于不同的存储引擎不能混用。索引类型底层结构支持引擎适用场景局限B树索引多路平衡树InnoDB、MyISAM等值、范围、排序、分组全文检索效果差哈希索引哈希表Memory、NDB纯等值查询不支持范围与排序全文索引倒排索引InnoDB5.6、MyISAM大段文本关键词检索中文分词需额外处理空间索引R-TreeMyISAM、InnoDB5.7地理位置数据使用场景窄日常 95% 的场景都是 B树 索引。全文索引要注意一个坑InnoDB 的全文索引对中文的支持依赖ngram分词器默认配置下中文分词粒度不理想做商品搜索这类需求往往还是老老实实上专门的检索组件。哈希索引在 Memory 引擎里有个特点只能整列匹配where a 1能用where a 1就是全表扫。3.2 按功能分的六种索引以及它们真正的差别这部分是实操中最容易混淆的我把它们摊开讲。主键索引一张表只有一个非空且唯一InnoDB 里它就是聚簇索引。如果你建表时没指定主键InnoDB 会挑一个非空唯一索引顶上再没有就自动生成一个 6 字节的隐藏行 ID。我强烈建议永远显式指定主键不要让引擎帮你猜。唯一索引值必须唯一但允许 NULL多个 NULL 不冲突这点和标准 SQL 的直觉不一样。它的查询性能和普通索引几乎一致区别在于插入时多一次唯一性校验。别为了查询快把普通索引改成唯一索引如果业务上真的能保证唯一那加上是合理的如果保证不了写入时会频繁报错。普通索引二级索引最常用的类型没有任何约束纯粹为了加速查询。联合索引复合索引多个列组合成一棵索引树。它的核心规则是最左前缀——idx(a, b, c)能被a、a,b、a,b,c使用但单独用b或c用不上。这里有个常见的误解需要澄清联合索引的叶子节点里是先按a排序a相同再按b排b相同再按c排。所以where b 2这种查询b的值在整棵树里是局部有序、全局无序的没法做二分定位。前缀索引针对长字符串列比如varchar(255)的 URL只取前 N 个字符建索引。alter table t add index idx_url(url(20));。它能显著减小索引体积代价是无法用于覆盖索引因为不存完整值必须回表而且区分度可能不够。选 N 的方法是用select count(distinct left(url, 20)) / count(*) from t;逐步试探让区分度接近全列的区分度。全文索引见上一节的说明。3.3 联合索引的列顺序到底该怎么排这是被问得最多的问题。我的排序原则是三条按优先级来等值查询的列放最前面。等值条件下列在索引里的先后顺序对定位效率影响不大但把等值列前置能保证后续列可以继续用于排序或过滤。区分度高的列放前面。区分度 count(distinct col) / count(*)。区分度越高一次定位筛掉的数据越多。需要排序的列遵循等值列在前、排序列在后。因为order by只有在索引里天然有序时才能免排序。举个实例select * from t_order where shop_id 1 and status 2 order by create_time desc limit 20;正确的索引是idx(shop_id, status, create_time)。这样shop_id和status等值定位后create_time在这个子集里天然有序order by直接顺着链表取 20 条就结束Extra 里会显示没有Using filesort。如果建成idx(create_time, shop_id, status)虽然也能过滤但create_time在树里是全局有序的优化器大概率会用它来做排序然后回表过滤扫的行数反而更多。我踩过这个坑一个列表页从 20ms 掉到 400ms换成正确顺序后立刻恢复。4. 建完索引之后怎么确认它真的被用上了4.1 explain 里必须看懂的几列建完索引不代表生效explain才是唯一的裁判。我一般只看四列其它列作为参考。type访问类型从好到坏的顺序是system const eq_ref ref range index ALL。生产环境的慢查询里如果出现index或ALL基本可以判定有问题。index是扫整棵二级索引树比全表扫略好因为索引文件小ALL是扫聚簇索引全表。key实际用上的索引。注意possible_keys有值但key是 NULL说明优化器评估后放弃了索引这种情况通常出现在区分度低或者统计信息不准的时候。rows预估扫描行数是优化器的估算值不是精确值。但它的量级很有参考意义比如 rows 是 200 万而最终只返回 10 条说明索引过滤能力不足。Extra信息量最大的一列。几个关键值Using index覆盖索引没有回表最理想。Using index condition索引下推ICP生效。Using where在存储引擎返回数据后Server 层还要再过滤一遍。Using filesort额外排序尽量消除。Using temporary用了临时表通常出现在group by场景要警惕。4.2 一个真实的执行计划改写过程原始 SQL 和索引-- 表结构简化 create table t_order ( id bigint primary key auto_increment, user_id bigint not null, shop_id int not null, status tinyint not null, amount decimal(10,2) not null, create_time datetime not null, remark varchar(255) default null, key idx_user (user_id) ); -- 慢查询统计某用户近30天的订单总额 select sum(amount) from t_order where user_id 10086 and create_time 2024-06-01 and status in (1,2,3);explain显示typerefkeyidx_userrows≈12000ExtraUsing where。也就是说索引只帮它定位了user_id的 12000 行create_time和status都是回表之后再过滤的。12000 次回表就是这个查询慢的原因。改成联合索引并把过滤条件都放进去alter table t_order add index idx_user_time_status(user_id, create_time, status);注意这里的顺序user_id等值在前create_time是范围查询放在第二status是in等值但只能放在范围后面。范围列后面的列在MySQL 5.6 之前是完全用不上的5.6 引入索引下推Index Condition Pushdown之后status虽然不能用于定位但可以在存储引擎层直接过滤掉不符合条件的行减少回表次数。改完之后rows≈300ExtraUsing index condition耗时从 800ms 降到 15ms。注意范围列右边的列只能做过滤、不能做定位这是个硬性规则。所以联合索引里范围查询的列尽量往后放。5. 那些让索引看起来失效的真实场景还原5.1 第一类列被动了手脚这类问题的共同特征是索引列参与了运算或函数调用导致 B树 无法按值定位。-- 反例索引列被函数包裹 select * from t_order where date(create_time) 2024-06-01; -- 正例改成范围 select * from t_order where create_time 2024-06-01 00:00:00 and create_time 2024-06-02 00:00:00; -- 反例索引列参与运算 select * from t_order where id 1 100; -- 正例 select * from t_order where id 99;原理很直白B树 是按create_time的原始值排好序的你把每一行都套上date()之后原有的顺序就失去意义了优化器只能全表扫一遍挨个算。隐式类型转换是这类里最阴的一个因为它藏在参数里看不见。如果user_id是varchar类型你写where user_id 10086数字MySQL 会把字符串列转成数字再比较等价于cast(user_id as signed) 10086索引直接失效。反过来如果列是数字类型、参数是字符串10086MySQL 是把字符串转成数字索引仍然有效。所以规则是字符串列绝不能用数字去比。我文章开头那次线上事故就是栽在这上面。我的建议是在代码层做参数类型校验ORM 层尽量用强类型绑定别让原始字符串直接拼进 SQL。5.2 第二类最左前缀被破坏-- 索引idx(a, b, c) select * from t where a 1 and b 2 and c 3; -- 全部命中 select * from t where a 1 and c 3; -- 只用到 a select * from t where b 2; -- 完全用不上 select * from t where a 1 and b 2 and c 3; -- a、b 定位c 只能过滤有个特殊情况很多人不知道where a 1 and b 2里b用的是等值所以索引树里b的部分是局部有序的order by a, b可以免排序。但如果是where a 1 and b 2 order by b, cb是范围c就失去有序性了会触发 filesort。另外like也遵守最左原则like abc%能用索引它是范围查询like %abc和like %abc%用不上。如果业务上非得做后缀匹配可以考虑把字符串反转后存一列用反转列建索引这是个土办法但确实有效。5.3 第三类优化器主动放弃索引这一类最容易被误解成失效其实索引好好的是优化器算了一笔账觉得全表扫更便宜。典型场景是区分度低的列。比如status只有 0 和 1 两个值表里 99% 都是 1。你查where status 1优化器会算走索引要扫 99% 的索引页然后回表 99% 的行还不如直接顺序扫全表。这种时候加索引不仅是无效的还会拖累写入。判断标准是当预估要返回的行数超过全表 20%~30% 时优化器大概率会放弃索引。还有几个会让优化器放弃的写法select * from t where col ! 1; -- 不等值 select * from t where col not in (1,2); -- 大概率走全表 select * from t where col is not null; select * from t where a 1 or b 2; -- b 没索引则整体失效最后那个or的问题解法是给b也建上索引让优化器做index_merge或者改写成union all两条查询。还有一种是统计信息不准导致的选错索引。InnoDB 的统计信息是采样估算的有时候会偏差很大。可以通过analyze table t_order;重新采集。我在线上遇到过一次同一张表同样的 SQL在从库上走了索引在主库上不走最后就是统计信息差异造成的。5.4 一个完整的三小时排查链路把上面这些串起来讲一次真实排查。现象报表接口偶发超时SQL 是select count(*) from t_log where biz_type 5 and create_time 2024-06-01;biz_type和create_time上都有单列索引。第一步先看explain。结果typeALL。先排除函数包裹、隐式转换这两类检查发现biz_type是tinyint参数是数字create_time是datetime参数是标准字符串排除类型问题。第二步看区分度。select count(distinct biz_type), count(*) from t_log;结果biz_type只有 6 个不同值而biz_type 5的行占了全表的 40%。到这里基本能定性了优化器判定走biz_type索引要回表 40% 的行不如全表扫。第三步验证。强制走索引select count(*) from t_log force index(idx_biz_type) where biz_type 5 and create_time 2024-06-01;耗时反而从 1.2s 涨到 4s印证了优化器的判断是对的。第四步求解。建立联合索引idx(biz_type, create_time)并且让它成为覆盖索引——count(*)只需要索引里已有的列不用回表。建完之后typerangeExtraUsing index耗时 60ms。这里的关键是count(*)走覆盖索引时扫描的是一棵体积小得多的二级索引树IO 量天然就少。这条链路里每一步都在做一件事把猜测变成证据。不猜是不是索引失效了而是用explain、用区分度统计、用force index对比来验证。6. 绕不开的几个名词讲透它们比背定义有用6.1 回表、覆盖索引、索引下推回表前面讲过二级索引拿到主键后再去聚簇索引查完整行。回表次数等于二级索引命中的行数所以索引过滤能力越弱回表越多性能越差。覆盖索引查询需要的所有列都在索引里不需要回表。判断标志是ExtraUsing index。它的价值不只是省几次IO更重要的是避免了随机IO。举个典型select id, user_id from t_order where user_id 10086;因为二级索引叶子节点存的就是user_id id这个查询天然覆盖速度飞快。如果写成select *就必然回表。所以我在写查询时有个习惯只 select 需要的列不做无脑select *。索引下推ICPMySQL 5.6 引入的优化。在没有 ICP 的年代where a 1 and b like %x%这种存储引擎只能靠a定位然后回表把行交给 Server 层Server 层再判断b的条件。有了 ICP 之后b的判断被下推到存储引擎层在索引里就能判断不符合的直接不回表。减少的是回表次数不是扫描行数。explain里显示Using index condition。6.2 区分度、基数与索引选择性基数Cardinality索引列里不同值的个数。show index from t_order;里的Cardinality列就是这个它是估算值。基数越大说明值越分散。区分度选择性Cardinality / 总行数越接近 1 越好。大于 0.3 通常算不错低于 0.01 基本没有建索引的必要。这个指标是决定要不要建索引的第一道筛子。6.3 页分裂、页合并与自增主键的价值这一组概念解释了为什么主键不要用随机值。B树 的叶子节点是一个个 16KB 的页页里的记录按主键顺序排列。如果主键是自增的新记录永远追加在最后一页写满就开新页顺序写顺序IO效率极高。如果主键是随机的比如 UUID新记录会随机插到中间某个页。当目标页满了InnoDB 必须把这个页拆成两个页分裂并把一半记录挪到新页同时更新父节点的指针。这个过程伴随大量的数据搬移和随机IO而且拆出来的页往往填充率只有 50% 左右空间浪费严重树也更容易变高。反过来当相邻页因为删除导致填充率过低时InnoDB 会做页合并回收空间。所以频繁的随机删除插入也会引发页的反复分裂与合并。提示关于 UUID 主键还有一个隐藏成本——UUID 是 36 字节的字符串而bigint是 8 字节。二级索引里每一行都要存主键值用 UUID 会让所有二级索引的体积膨胀好几倍。我在做新表设计时主键一律用自增bigint或者趋势递增的分布式ID保证大致有序即可这个习惯省下来的性能非常可观。6.4 MRR 与 Buffer Pool 的关系MRRMulti-Range Read是 MySQL 5.6 引入的另一个优化。回表时主键通常是乱序的直接挨个回表就是随机IO。MRR 的做法是先把一批主键收集起来排序再去聚簇索引里顺序读取。它减少的是随机IO次数在机械盘上收益明显在 SSD 上收益相对小一些。Buffer Pool是 InnoDB 最重要的内存区域缓存数据页和索引页。前面算过B树 的前两层通常会被缓存这就是为什么三层树的实际查询往往只有一次物理IO。如果你的 Buffer Pool 设置得过小根节点频繁被换出性能会断崖式下跌。一般建议设置为物理内存的 50%~70%。7. 索引该加还是该删线上维护的几条实操经验7.1 用系统视图找出冗余和没用的索引索引不是越多越好维护成本是实打实的。MySQL 自带两个视图能帮上大忙-- 查看冗余索引前缀重复 select * from sys.schema_redundant_indexes; -- 查看从未被使用过的索引需先开启 performance_schema 相关采集 select * from sys.schema_unused_indexes;schema_redundant_indexes能找出idx(a)和idx(a, b)这类重复——后者完全覆盖前者的功能前者可以删。我在一次清理中删掉了 40 多个冗余索引那张表的写入 TPS 提升了约 30%因为每次写入要维护的 B树 从 3 棵变成了 1 棵。7.2 大表加索引别在业务高峰干这事alter table t add index ...在 MySQL 5.6 之后支持Online DDL可以做到加索引期间不阻塞读写alter table t_order add index idx_shop_time(shop_id, create_time), algorithminplace, locknone;但要注意几个前提ALGORITHMINPLACE不是万能的改列类型、改字符集这类操作仍然需要COPY重建整表Online DDL 期间会产生大量InnoDB临时日志需要保证innodb_online_alter_log_max_size足够大否则会报错重建索引会消耗大量 IO 和 CPU千万别在业务高峰期做。我一般会先在一个只读从库上试跑记录耗时和资源占用再决定主库的执行窗口。对于超大表上亿行更稳妥的做法是用第三方工具做在线表结构变更它通过建影子表增量同步的方式全程可控、可暂停、可回滚。7.3 上线前的索引自查清单最后把我自己用的一张清单分享出来每次提交涉及 SQL 变更的代码前过一遍联合索引的列顺序是否满足等值在前、范围在后、排序最后。查询列是否都包含在索引里能否做成覆盖索引。索引列的区分度是否足够Cardinality / 总行数。是否存在函数、运算、隐式类型转换包裹索引列。是否存在以%开头的like。order by的列是否与索引顺序一致能否免排序。是否存在可被更长的联合索引覆盖的短索引冗余索引。新增索引带来的写入成本是否在可接受范围内。explain的type是否达到range或更好。7.4 我的个人体会做数据库这几年我最深的感受是索引优化的核心不是建更多索引而是用更少的索引覆盖更多的查询。一张表上挂十几个单列索引和挂三四个精心设计的联合索引后者在读写两方面都更优。真正的难点在于你知道业务会跑哪些 SQL——所以每次加索引之前我都会先把这一批查询收集起来看看它们能不能被同一棵索引树照顾到。还有一个经验别迷信规则要相信explain。网上流传的!一定不走索引or一定失效之类说法都是特定数据分布下的结论换一张表可能完全不成立。优化器是基于成本的成本又取决于数据分布所以同一句 SQL 在今天走索引、下个月数据量涨了可能就不走了。养成改完索引就看执行计划的习惯比背一百条规则都有用。