最近在排查线上一个慢查询的时候我重新把 MySQL 索引相关的内容完整过了一遍。说实话索引这块内容属于典型的“用起来简单用好了难”很多人建索引就是凭感觉写 SQL 就是跑通了算完结果等到数据量上来、接口超时、数据库 CPU 飙高的时候再回头翻执行计划才发现一串的坑等着填。这篇第八章的内容就是要把索引从原理到实战完整梳理一遍包括 B 树为什么是 MySQL 的默认选择、复合索引怎么设计才不会失效、EXPLAIN 到底怎么看、覆盖索引和索引下推这些高级特性怎么用上以及我这些年踩过的索引失效案例。这一章适合两类读者一类是刚开始学 MySQL 没多久索引概念停留在“给 where 后面的字段加个索引”这个层面的新手另一类是已经写过不少 SQL但从来没认真打开过执行计划、没系统排查过索引失效问题的开发者。看完之后你至少能独立完成一次慢查询的索引优化并且知道为什么这样设计索引是合理的。1. 索引到底是什么先搞懂它解决的核心问题1.1 没有索引时数据库是怎么找数据的要理解索引的价值最好先感受一下没有索引的检索过程。假设有一张用户表里面有 1000 万行数据你要查找nickname 张三的记录。如果没有索引InnoDB 只能从表的第一行开始一行一行往下扫逐条比对 nickname 字段的值直到把所有记录全部扫完这个过程叫全表扫描full table scan。1000 万行的全表扫描有多慢呢如果每行数据平均 200 字节一次扫描要读大约 2GB 的数据这还只是数据读入内存的量再加上逐行比对的开销日常线上场景里几秒钟跑不完是很正常的。更麻烦的是全表扫描还会把大量无关数据页加载到内存把 InnoDB 的 buffer pool 搞得很紧张连带影响其他查询的性能。索引解决的就是这个问题。它的本质是拿额外的存储空间和维护开销换查询时的快速定位能力。类比一下就是一本没有目录的技术书你想找“索引下推”这个词只能从头翻到尾有了目录你可以直接翻到对应章节甚至通过页码定位到具体那一页。额外代价是这本书多了一个目录部分而且每次内容更新目录也得同步维护。1.2 为什么 MySQL 选择了 B 树而不是哈希表或二叉树很多人第一次接触索引底层结构的时候都会疑惑为什么 MySQL 的默认索引结构是 B 树而不是查询复杂度看起来更低的哈希表或者更简单的二叉树先说哈希表。哈希索引的等值查询确实非常快理论上是 O(1) 的复杂度但它的缺陷也很致命哈希表是按哈希值散列存储的数据在物理上完全没有顺序所以它只能支持等值查询、、BETWEEN、ORDER BY这类范围查询全都无能为力。而实际业务 SQL 里范围查询和排序的需求非常频繁光是这一条就决定了哈希索引只能作为辅助结构存在。MySQL 的 Memory 引擎支持哈希索引但 InnoDB 的默认索引始终是 B 树。再说二叉树。普通二叉树在最坏情况下会退化成链表查询复杂度变成 O(n)完全不可控。AVL 树和红黑树虽然解决了平衡问题但它们是二叉树每个节点最多只有两个子节点树的高度会随着数据量增长快速变大。InnoDB 一次磁盘 IO 默认能读取 16KB 的数据页如果一棵树高度是 20一次查询就要经历 20 次磁盘 IO这在机械硬盘时代是不可接受的。B 树解决这个问题的思路很直接让每个节点尽可能多存键值和指针把树的宽度做大、高度压低。InnoDB 中一个节点就是 16KB 的数据页假设每个键值对占 16 字节一层就能存放大约 1000 个键值对。两层就是 100 万个三层就是 10 亿级别。换句话说千万级甚至亿级数据量的表B 树的查找路径通常只需要 3 到 4 层。再加上 B 树所有数据都存在叶子节点、叶子节点之间用链表串联天然适合范围查询和排序扫描这就是它成为 InnoDB 默认索引结构的原因。1.3 聚簇索引、二级索引和回表看索引之前必须懂的三个概念建索引之前有三个概念必须掰扯清楚否则后面看执行计划、分析索引失效时会一头雾水。第一个是聚簇索引clustered index。InnoDB 中表数据本身就是按主键顺序组织存放的主键索引的叶子节点直接保存了整行数据这种索引结构就叫聚簇索引。这就是为什么 InnoDB 表强烈建议要有主键而且主键最好是自增的没有主键时InnoDB 会选一个非空唯一索引作为主键再不行就隐式生成一个 rowid怎么都不如自己设计的可控。自增主键则保证每次插入都追加在末尾减少页分裂。第二个是二级索引secondary index也叫辅助索引或普通索引。二级索引的叶子节点不存整行数据只存索引列的值和主键值。比如你在 nickname 上建了一个索引那么这棵 B 树的叶子节点就是nickname 主键 id的组合。第三个是回表table lookup。当通过二级索引找到记录的主键后还需要再用主键去聚簇索引里查一次完整数据这个过程就叫回表。回表不是免费的它意味着额外的一次 B 树查找。理解了这个机制后面讲覆盖索引时你就懂了如果查询的字段恰好都在二级索引里那就根本不需要回表性能自然高出一截。2. 索引分类与创建策略看清每一类索引到底在干什么2.1 主键索引、唯一索引、普通索引区别不只是唯一性MySQL 里的索引可以从不同维度分类但日常打交道最多的就是主键索引、唯一索引和普通索引。主键索引就是聚簇索引一张表只能有一个叶子节点存整行数据通常建表时通过 PRIMARY KEY 定义。主键索引除了查询还决定了数据在磁盘上的物理组织方式所以它不只是“一个索引”那么简单还会影响插入性能和数据页的空间利用率。唯一索引保证索引列的每一行值不重复允许 NULLNULL 可以有多个。它的作用有两层一层是约束防止脏数据写入另一层是加速查询。对优化器来说唯一索引还有个额外优势——它知道同一值最多只有一行遇到等值查询时找到第一条就能停下来不需要继续扫描下一条记录。普通索引就纯粹是为了查询性能了没有任何约束作用。实际工作中唯一约束尽量在业务层面保证不要依赖数据库的普通唯一索引去兜底因为唯一索引的检查在写入时是有额外开销的高并发写入场景下这个开销会被放大。2.2 复合索引与最左前缀原则建索引最容易忽视的规则复合索引composite index指的是在多个列上建立的索引。很多人误以为复合索引等同于“给每个列分别建一个索引”这是完全错误的。复合索引的本质是一棵 B 树排序规则按索引定义的列顺序逐级比较先按第一列排序第一列相同的再按第二列排序依此类推。这个排序规则直接推导出最左前缀原则查询条件里必须包含复合索引的最左列索引才会被用到。比如在(user_id, status, create_time)上建了复合索引那么WHERE user_id ? AND status ?能用到索引WHERE status ? AND create_time ?大概率用不到因为查询条件没有从最左列开始。这里有个常见的认知误区最左前缀不是说 SQL 的 where 条件里必须把最左列写在最前面优化器会自己调整条件顺序。而是说查询条件里必须出现最左列。比如WHERE status ? AND user_id ?优化器识别到 user_id 存在依然会走复合索引。2.3 前缀索引和冗余索引细节决定查询效率前缀索引是只对字符串列的前 N 个字符建立索引适用于字段值较长且前缀区分度足够的情况。典型例子是邮箱地址不需要对整串邮箱建立索引只需要给email(20)这样截取前 20 个字符建索引索引体积大幅缩小缓存命中率提升查询性能反而可能更好。但前缀索引有个副作用无法使用覆盖索引。因为索引中只存了前缀查询其他字段时必须回表。另外ORDER BY email这种排序也无法用上前缀索引。所以前缀长度不是越小越好通常的做法是不断调整前缀长度对比区分度选一个区分度接近完整列但长度尽量短的值。再说冗余索引。冗余索引是指功能上完全被其他索引覆盖的索引。比如已经建了(a, b)复合索引又单独建了a索引那个单独的a索引就是冗余的。冗余索引的代价不仅是浪费存储空间更关键的是每次 INSERT、UPDATE、DELETE 时所有索引都要同步维护索引越多写入越慢。我见过一个极端案例线上有一张每天写入几百万数据的表历史遗留了 7 个索引其中 3 个是冗余的后来删掉冗余索引后写入耗时直接降了 40%。2.4 什么样的表不建议加索引索引不是越多越好有些场景下加索引反而得不偿失。数据量很小的表比如几百行全表扫描本身比走索引还快加索引纯属浪费空间。频繁写入的表索引维护的开销会拖慢写入速度需要权衡查询需求和写入压力。区分度极低的列比如性别、状态这类只有两三个取值的字段单列索引过滤掉的数据比例太低优化器通常不屑于使用。还有一个很多人忽略的点TEXT 和 BLOB 等大字段类型不能直接建普通索引必须指定前缀长度而且业务上也很少需要对大文本做点查这种字段的索引需求通常应该通过全文索引或者外部搜索服务解决。3. 索引失效高频场景这些年踩过的坑一次说清3.1 一张可以直接抄的索引失效清单索引失效是面试八股和实际开发里出现频率都很高的话题但很多资料只是在罗列现象没有解释根本原因。我把它整理成一张对照表并且补充背后的逻辑。失效场景示例失效原因对索引列使用函数WHERE YEAR(create_time) 2024函数改变了列值B 树按原值排序无法二分查找隐式类型转换WHERE phone 13800138000phone 是 varchar和数字比较时会被转换为数字等同于函数操作模糊匹配前导通配符WHERE name LIKE %张%通配符在前面无法确定比较起点OR 连接非索引条件WHERE id 1 OR age 20age 无索引优化器只能全表扫描复合索引不满足最左前缀WHERE status 1 AND create_time 2024-01-01查询条件缺少复合索引最左列索引列参与运算WHERE age 1 30列值被修改索引失效大范围查询超过阈值WHERE create_time 2020-01-01且范围过大优化器认为全表扫描更划算这个清单现在看起来是知识点但在真实生产环境里它们往往藏在一堆看似正常的 SQL 中等你排查慢查询时逐个揪出来。3.2 隐式类型转换和字符集不一致两个最隐蔽的杀手隐式类型转换是排查索引失效时最容易忽略的一种。MySQL 在比较不同数据类型的值时会做隐式转换而一旦对索引列做了转换索引就废了。比如phone字段是 varchar(20)SQL 里写WHERE phone 13800138000数字和字符串比较时MySQL 会把字符串转成数字再比较等同于在 phone 列上用了 CAST 函数索引自然失效。排查方法是专门检查 SQL 里字段类型和传入参数类型是否一致。前端传来的参数经常是字符串数据库字段是数字或者反过来都可能导致隐式转换。更稳妥的做法是养成习惯参数类型和字段类型严格保持一致字符串就加引号数字就传数字。另一个隐蔽问题是字符集不一致。当两个表 join 时如果关联字段的字符集不同比如一张表是 utf8mb4另一张表是 latin1MySQL 需要把其中一边转换成另一边才能比较这个转换同样会导致索引失效。这个问题在存量系统改造、新老表混用的时候特别常见。解决方法是建表时统一规范所有库表一律使用 utf8mb4join 字段的 collation 也要一致。3.3 优化器不选索引索引有效但没用上的特殊情况还有一种情况让人很郁闷索引明明建了字段也没做任何加工但执行计划里就是没用上。这不是索引失效而是优化器在“算账”之后判断走全表扫描更划算。典型的触发条件是范围查询占总数据量的比例太大。比如一个状态字段99% 的行都是status 1你查WHERE status 1优化器一算全表扫描可能比回表查 99% 的数据更快于是直接放弃索引。这就是索引基数cardinality和选择性的问题——索引的价值在于快速过滤掉大部分数据如果某个值占了绝大多数反而成了“低选择性”查询。遇到这种情况不要执着于“强制走索引”FORCE INDEX那是治标不治本。正确的方向是换一个更有区分度的查询条件或者优化表结构设计。比如把“查询状态为正常的所有记录”改成“查询状态为正常且创建时间在一个月内的记录”配合包含 create_time 的复合索引让优化器看到明确的数据裁剪空间。4. EXPLAIN 执行计划让数据自己告诉你索引用得对不对4.1 一条慢 SQL 的完整排查过程先说一个我实际处理过的案例。当时线上有个订单查询接口数据量大概 800 万行最近一个月开始频繁超时。原始 SQL 大致是这样的SELECT order_id, user_id, amount, status FROM orders WHERE status 1 AND create_time 2024-03-01 ORDER BY create_time DESC LIMIT 20;orders 表当时有两个索引一个是idx_create_time(create_time)一个是idx_status(status)。看起来查询条件都有索引覆盖但接口就是慢。用 EXPLAIN 一查才发现问题所在。实际执行计划里优化器选择了idx_status然后对 status 1 的结果集进行 filesort 排序再取前 20 条。status 字段的分布是 1 表示未支付占比非常高status 1扫出了两百多万行。这两百多万行还得在临时表里做排序性能自然就崩了。4.2 type、key、rows 这些关键列到底怎么读EXPLAIN 输出里信息很多但日常优化先看几个核心列就够用了。列名含义关注点type访问类型从好到差依次为system const eq_ref ref range index ALLkey实际选用的索引为 NULL 表示没用索引rows预估扫描行数数值越小越好Extra额外信息Using filesort、Using temporary 都是危险信号其中 type 是最直观的指标看到 ALL 就是全表扫描看到 index 就是扫描了整棵索引树这两种情况只要有慢查询就值得警惕。ref 和 range 属于正常范围const 和 eq_ref 是点查级别的最理想情况。rows 列是优化器基于统计信息估算出来的行数不是精确值但它给了你一个直观参照。如果 rows 显示 200 万而最终返回 20 条说明索引选择性差需要调整索引设计。Extra 列的Using filesort意味着 MySQL 需要额外排序这个操作如果作用在几十万行上基本就是性能瓶颈。Using temporary更严重说明要建临时表通常出现在 GROUP BY、DISTINCT、UNION 的场景也是优化重点目标。4.3 用执行计划反推索引设计回到刚才那个订单案例。既然问题在于 status 的选择性差而且排序字段是 create_time正确的索引设计就应该是复合索引把筛选和排序合并到一棵 B 树里ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);加完索引后把原有idx_status删除避免冗余。新的执行计划里优化器走idx_status_create_time先定位到 status 1 的范围再扫描该范围内按 create_time 有序排列的记录取前 20 条直接返回连 filesort 都不需要了。同样的查询耗时从 2.8 秒降到了 30 毫秒左右。这个案例想说明的核心方法是把 EXPLAIN 当成一个诊断工具从 type、key、rows、Extra 四列分析现状然后反向推导“什么样的索引才能让查询更快”。这个推导过程有几个标准索引能同时处理 where 过滤、order by 排序、group by 分组是最理想的状态如果不行优先保证过滤条件范围查询的字段放在复合索引偏后面的位置避免范围条件阻断后续索引列的使用。5. 索引的高级运用覆盖索引、索引下推与排序优化5.1 覆盖索引查询所有字段都在索引里回表直接免掉我在 1.3 里提到过二级索引不存完整行数据查询非索引字段时需要回表。覆盖索引就是针对这一点做的优化让查询要的所有字段都包含在同一个二级索引中这样查询过程中就能直接通过索引返回结果完全不用回表。举个例子表里有索引(user_id, status)执行SELECT user_id, status FROM orders WHERE user_id 100;这个查询的两个字段都在索引里InnoDB 只需要扫描 idx_user_status 这棵 B 树找到 user_id 100 的叶子节点直接返回索引里的 user_id 和 status 就行不需要再跳到聚簇索引里取整行数据。Extra 列会出现Using index这也是执行计划里非常理想的状态。覆盖索引的使用对性能提升非常明显尤其是在高频查询上。实践中可以针对核心接口的查询字段专门设计一个覆盖索引来“喂饱”查询。但要注意控制索引列的数量字段越多、索引越宽写入和存储开销越大需要适度取舍。5.2 索引下推MySQL 5.6 之后的隐藏优化利器索引下推Index Condition PushdownICP是 MySQL 5.6 推出的优化特性简单理解就是把 where 条件中能够用索引列判断的部分下推到存储引擎层去执行过滤减少回表次数。没有 ICP 时存储引擎扫描索引找到匹配的记录返回给 Server 层由 Server 层再判断剩余条件。有了 ICP存储引擎在扫描索引时直接就把能判断的条件过滤掉了只回表那些真正可能满足全部条件的行。拿复合索引(last_name, first_name)举例。查询WHERE last_name 张 AND first_name LIKE %三%没有 ICP 时引擎先按 last_name 取回所有张姓记录逐行回表再交给 Server 层判断名字是否包含“三”。有 ICP 时引擎在索引扫描环节就把 first_name 的条件一并判断只回表名字里包含“三”的记录。如果张姓有 1 万条包含“三”的可能只有几十条回表次数直接降低两个数量级。这个优化对开发者基本是透明的只要 MySQL 版本足够新5.6 以上都支持执行计划里出现Using index condition就说明 ICP 已经生效了。你能做的是尽量把可过滤的字段都设计进复合索引里让更多条件下沉到引擎层。5.3 用索引彻底优化 ORDER BY 和深分页排序是 MySQL 里比较吃性能的操作因为需要额外排序时MySQL 会把结果集放入 sort buffer 或临时文件数据量一大就非常慢。如果查询的 ORDER BY 字段正好是索引的一部分而且排序方向和索引顺序一致MySQL 可以直接沿着 B 树的叶子链表顺序读取完全省掉排序环节。这就是设计复合索引时把排序字段放在合适位置的意义。比如(user_id, create_time)这个索引天然支持WHERE user_id ? ORDER BY create_time免排序因为同一个 user_id 下的记录在索引里已经按 create_time 排好序了。深分页问题是另一个高频痛点。经典写法LIMIT 100000, 20会让 MySQL 扫描前 100020 条记录然后丢弃前 100000 条越往后翻页越慢。一个非常有效的优化是延迟关联或基于覆盖索引的游标分页-- 先用覆盖索引定位主键 SELECT id FROM orders WHERE create_time ? ORDER BY create_time LIMIT 20; -- 再用主键关联取详情 SELECT * FROM orders WHERE id IN (刚才查到的20个id) ORDER BY create_time;这种写法让排序操作完全在索引上进行避免了扫描大 offset 时回表大量无关行的开销。在千万级数据量下深分页性能差距能达到十倍以上。6. 日常索引设计与维护把踩坑经验变成习惯6.1 定期检查冗余索引与重复索引要知道自己数据库里有哪些索引MySQL 提供了现成的查询方式SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, GROUP_CONCAT(a.COLUMN_NAME ORDER BY a.SEQ_IN_INDEX) AS index_columns FROM information_schema.STATISTICS a GROUP BY a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME HAVING COUNT(1) 1;日常巡检里我会重点找两类问题一是同一列既在复合索引里又在单列索引里二是前缀完全一致的多个复合索引。比如(a, b)和(a, b, c)同时存在时前者就是后者的左前缀子集可以安全删掉因为后者完全覆盖了前者的查询场景。删除冗余索引唯一需要注意的是确认没有 SQL 依赖被删索引做其它事情比如唯一索引还承担着约束职责。实际操作时先禁用慢查询日志观察几天再在低峰期删除这是一个稳妥的流程。6.2 索引基数与统计信息优化器的决策依据索引基数指的是索引列的去重值数量。基数越高索引选择性越好优化器越倾向于使用它。MySQL 的优化器基于基数统计信息来估算不同执行计划的代价所以统计信息的准确性直接决定执行计划的质量。日常维护中有一个很容易被忽略的操作在数据大量变更后更新表统计信息。比如一个表从 100 万行涨到 1000 万行或者批量导入了大量数据旧的统计信息会误导优化器。这时可以执行ANALYZE TABLE主动更新统计信息再观察执行计划是否恢复正常。另外索引列上的值分布如果发生极端变化比如某字段原本分布均匀突然某一天某个取值占到了 99%很可能导致优化器路径变化进而引发线上查询性能波动。这类问题光靠日常开发很难发现建议团队搭建慢查询监控和数据库巡检告警在性能异常的第一时间抓住现场。6.3 一套我自己在用的索引设计流程最后分享一个我每次建索引都会走的固定流程算是这些年踩坑之后沉淀下来的习惯。第一步先收集这个表上所有高频查询 SQL包括 where 条件、order by、group by 和 join 字段。第二步对每条 SQL 分别分析最优索引形态过滤条件怎么组合、排序字段怎么安排、要不要覆盖索引。第三步合并所有 SQL 的索引需求把能共用的索引合并成复合索引优先保留查询频率高、延迟敏感的 SQL 对应的索引。第四步建完索引后用 EXPLAIN 验证执行计划确认 type 达到 ref 或 range 级别Extra 里没有 filesort 和 temporary。第五步观察一段时间线上运行状况确认索引真实生效、没有被用到再考虑清理潜在冗余索引。这个流程看起来很朴素但能挡掉绝大多数索引设计问题。我自己踩过最大的坑就是设计索引时只盯着一条 SQL 看忽略了其它查询场景结果加了索引之后原来正常的查询反而变慢了。索引设计永远是一个权衡过程覆盖多个高频场景的复合索引通常比一堆孤立的单列索引高效得多。另外想特别提醒一点索引优化不是一锤子买卖。业务在变数据量在变执行计划也在变。建议每隔一段时间就把核心表的索引重新梳理一遍至少每季度做一次索引健康检查把新增的慢查询 SQL 纳入分析范围。MySQL 的索引设计规格并不复杂难的是一直保持对执行计划的敏感度以及在每个加索引的决策前多想一步“这个索引为什么会帮到查询”。把这件事变成习惯很多看似玄学的性能问题其实都能在索引层面找到清晰的答案。