日常工作里最容易被忽略却又对系统性能影响最立竿见影的东西往往就是索引。很多同学对索引的理解停留在“查询慢就加个索引”但对于为什么要这样加、加完为什么生效、什么时候不生效脑子里并没有一条完整的逻辑链。这篇文章我想从一个“导航仪”的角度重新拆解一下 MySQL 索引你看看索引是怎么一步步带着数据库找到数据的也希望你以后在优化慢查询时能有一套自己的判断方法。无论你是刚入行的后端开发还是已经在生产环境里处理过几次慢查询的运维或 DBA这篇文章都适合你。我会从最基础的索引数据结构讲起然后结合真实案例聊索引如何设计、如何用执行计划验证效果再把你最可能踩到的“索引失效”坑一个个捡出来。全程没有晦涩的源码分析尽量用大家都能听懂的大白话把索引的底层逻辑、设计思路和生产实践串成一条线。1. 索引的底层逻辑它凭什么让查询“飞”起来1.1 从“翻目录”到“走捷径”索引到底是个什么东西想象一下你要在一本 500 页的技术书里找一个特定名词最笨的办法是从第一页翻到最后一页看到目标就停下来这叫全表扫描。而书的最后几页通常会有一个“索引”按拼音或笔画顺序排列着所有名词和对应的页码你一翻目录就能直接跳到那一页。数据库里的索引干的就是这个事它是独立于表数据之外的一种结构记录了索引列的值和对应数据行的物理位置或者主键值查询时只需要在索引里做快速查找找到目标后再根据指针去取完整的数据这个动作叫回表。所以索引并不是什么神秘的东西它就是数据库为查询准备的“目录”。但它的意义远不止减少扫描行数。索引还能避免排序、避免临时表、加速连接甚至在某些情况下直接把查询需要的数据全部覆盖掉连回表都省了。这些能力会在后面的实践部分一个一个展开这里先建立一个核心认知数据库里没有索引时查询是线性扫描有合适的索引时查询是树形查找复杂度从 O(N) 降到 O(logN)量级完全不同。1.2 B 树数据库为什么偏爱这棵“多叉树”市面上能见到的索引结构有很多哈希、二叉树、跳表、B 树、B 树各有各的场景。MySQL 里 InnoDB 存储引擎的索引却统一采用 B 树原因是它和磁盘 IO 的配合最默契。B 树对比普通二叉树最大的区别是“多叉”。一个节点可以存多个 key树的高度被压得很低三层到四层的 B 树就能撑住千万级乃至上亿级的数据量也就是说哪怕表里有一千万行查询一个索引键也只需要三次左右的磁盘 IO。这三次 IO 和全表扫描的几万次 IO 相比性能差距是几何级的这玩意儿才是极速查询的真正底气。还有一点容易忽略B 树的所有数据都存在叶子节点上而且叶子节点之间是通过双向链表串联起来的。这个设计和范围查询关系很大当你执行WHERE id BETWEEN 100 AND 200时数据库在索引中找到 100 这条记录后直接顺着链表往后扫就行不需要反复从根节点重新遍历。普通 B 树的叶子节点之间没有这个链表范围查询会变成多次独立的查找代价高得多。这里有个观点方便理解B 树既能精准定位又能流畅地顺序遍历天生就是为关系型数据库的查询模式准备的。1.3 聚簇索引和非聚簇索引说说“行”到底存在哪InnoDB 里每个表都有一个聚簇索引这个索引的叶子节点保存的是整行数据其他所有二级索引的叶子节点保存的是主键值。所以聚簇索引的键值顺序决定了表数据的物理存储顺序一张表只能有一个聚簇索引。InnoDB 会优先选择主键作为聚簇索引如果没有主键就选第一个非空唯一索引二者都没有则内部生成一个隐藏的 rowid 来充当这个角色。理解聚簇索引的第二个关键点是二级索引的查询都要经历一次“找主键再回表”的过程。这里有个很容易踩的误区很多人以为建了索引查询就一定快实际如果查询需要回表且回表次数过多性能依然很差。后面讲覆盖索引的时候会专门解决这个问题。现在只要记住这个模型的链路二级索引查到主键主键去聚簇索引里取行数据两个 B 树的查找各来一次。2. 索引类型怎么选不同业务场景需要不同“导航策略”2.1 主键索引、普通索引、唯一索引的分工差异主键索引的叶子节点存的是整行数据它天然具备唯一性并且在插入记录时 InnoDB 会按主键顺序组织页。如果一个表的主键是自增整数新记录总是追加到 B 树的最右侧页分裂的概率较低写入性能最稳定。如果你的主键是一个随机生成的 UUID那新纪录会随机落在 B 树的各个位置频繁触发页分裂与页重排写入吞吐会明显下降。我处理过一个案例某业务表把 UUID 当主键批量导入 200 万行数据耗时接近一小时的量级换成自增主键后同样数据只用了不到十分钟差别就是这么夸张。唯一索引相比普通索引除了查询加速多了一层约束作用保证列值唯一。业务上可用它来防止脏数据重复比如用户手机号、订单号这类字段。但唯一索引的插入性能略差于普通索引因为每次插入都需要额外检查唯一性约束。于是在“需要去重”和“写入量大”之间就要做取舍。如果业务上能接受“最终一致后再清洗”可以考虑不建唯一索引在应用层或离线任务里统一处理能明显提升写入速度。普通索引则没有任何约束负担它的存在完全是为了查询加速。在创建索引之前你需要问自己一句这个索引是为了哪条慢查询服务如果回答不上来那这个索引大概率是冗余的。2.2 联合索引多条件查询的“组合导航”联合索引也叫复合索引是建立在多个列上的索引。很多人把它理解成“每个列各自建立一个索引”这是本世纪最大的误解之一。联合索引里的列有顺序之分它先按第一列排序第一列相同的情况下再按第二列排序以此类推。它在 B 树里是一个连续的多列键值而不是多个独立索引。联合索引最实用的价值在于能同时过滤多个条件。比如订单表经常按user_id和status查询建立一个(user_id, status)联合索引就能在一次索引查找中同时完成两个条件的过滤几乎不需要回表。相比之下如果分别建user_id和status两个单列索引MySQL 通常只会选择其中一个过滤另一个条件只能靠回表后的数据筛选性能差距非常明显。2.3 最左前缀原则联合索引的“游戏规则”最左前缀原则是联合索引使用中必须刻在脑子里的规则查询条件中必须包含联合索引的最左列这个索引才会被考虑使用。联合索引是从左到右逐列匹配的跳过了左边第一列后面的列再精确也无济于事。举个例子索引(a, b, c)可以支持a ?、a ? AND b ?、a ? AND b ? AND c ?这三类查询但不支持b ?或c ?开头的查询条件。这个规则背后是 B 树的排序逻辑叶子节点按a排序a相同才按b排序b相同才按c排序。所以如果查询条件里没有a整棵索引树都无法定位起始位置只能全树扫描数据库觉得这种情况下还不如直接扫主表划算于是放弃索引。理解最左前缀原则还有一个实际收益设计联合索引时可以把多个频繁使用的单列查询合并成一个索引减少索引数量。比如系统里常用的查询条件是(a)和(a, b)一个(a, b)联合索引就能同时覆盖两种场景完全不需要为a再单独建索引。我见过很多表里单列索引建了七八个真正被用到的只有两三个其余全成了冗余负担后面会专门讲怎么清理。3. 用覆盖索引优化查询能不回表就别回表3.1 回表的代价到底有多大前面说过二级索引找到主键后还需要去聚簇索引里取完整数据这个过程叫回表。回表本身不是灾难真正的灾难是回表次数太多。比如一个二级索引的字段区分度很低像订单状态只有几个枚举值通过它可能一下子筛出几十万行主键。这时候数据库要按主键去聚簇索引里随机访问几十万次每次都是一次独立的 B 树查找IO 压力直接拉满查询自然就慢了。所以我在设计查询时第一反应永远是看能不能让查询所需的所有列都包含在索引树里这样索引本身就能给查询返回全部数据不需要再回表。这种索引被称为覆盖索引。覆盖索引不是一种独立的索引类型而是一种“索引被查询完整覆盖”的状态。3.2 一个实际订单查询的优化对比给你看一个我优化过的订单查询场景。有一张订单表包含order_id、user_id、status、amount、create_time等字段业务上有个高频查询是按照user_id查最近 30 天的订单列表并显示金额。最初表上只有user_id单列索引查询语句长这样SELECT order_id, amount, create_time FROM orders WHERE user_id 12345 AND create_time 2024-01-01 00:00:00 ORDER BY create_time DESC LIMIT 20;这个查询会走user_id索引找到该用户的所有主键再回表取order_id、amount、create_time然后排序取前 20 条。如果这个用户订单量是几千条那就要经历几千次回表听起来还能接受但如果这个查询被用户频繁触发几千次回表累积起来的延迟就很可观了。我把索引调整成联合索引(user_id, create_time, amount, order_id)然后重新分析这条查询user_id和create_time被用于索引范围匹配order_id和amount都可以直接从索引叶子节点拿到查询所需的全部列都覆盖在索引里了回表彻底消失排序也因为索引天然按create_time有序而一并省掉。调整后该接口的 P99 延迟从 120 毫秒降到了 15 毫秒以内索引体积增加了几 MB换来的是十几倍的查询提升这笔交易非常划算。3.3 如何判断一个查询有没有被索引覆盖最直接的方法是看执行计划里的 Extra 列。如果显示Using index说明查询被索引覆盖如果显示Using index condition说明索引下推已经生效但仍有部分数据需要回表过滤如果显示Using where且没有 Using index那就是普通回表查询。补充一下 MySQL 5.6 以后引入的索引下推优化它的作用是把部分 WHERE 条件的过滤下推到索引遍历过程中减少回表次数。假设联合索引是(user_id, create_time)查询条件是user_id 1 AND create_time 2024-01-01 AND status PAID虽然status不在索引里但 MySQL 可以在遍历索引时先过滤掉不满足status条件的主键再回表这样回表数量会大幅下降。这个优化是自动触发的但你得知道它的存在才能解释为什么有时候 Extra 里会出现Using index condition也不用担心是不是索引没建好。4. 用 EXPLAIN 给查询做“体检”读懂执行计划的关键信息4.1 执行计划到底要看哪些字段EXPLAIN 是 MySQL 用来展示一条 SQL 执行规划的语句你只需要在 SELECT 前面加上 EXPLAIN 就能看到。面对一长串字段新手容易懵其实真正需要重点关注的只有几列。首先是type它告诉你 MySQL 是怎么扫数据的。从好到差大致是const、eq_ref、ref、range、index、ALL。const表示按主键或唯一索引精准定位一行ref表示普通索引等值匹配range表示索引范围扫描ALL是全表扫描。如果看到 ALL说明这条 SQL 大概率是性能瓶颈之源除非表本身很小否则必须想办法走索引。然后是key它表示实际选中的索引是什么而possible_keys则列出所有可能被用到的索引。有时候 possible_keys 里有几个候选key 只选了其中一个这是正常现象优化器会根据代价模型挑一个它认为最划算的。如果 possible_keys 是空key 也是空那说明这条 SQL 根本没找到可用索引。再然后是rows这是一个预估的扫描行数虽然不一定准但能帮你判断查询的代价量级。优化前后对比 rows 的变化比盯着耗时更客观因为耗时受缓存影响太大。最后是Extra里面藏着很多特殊标记Using filesort表示需要额外排序Using temporary表示需要临时表常见于 GROUP BY 和去重这两个都是需要重点消灭的目标。4.2 配合实际案例理解执行计划接着用前面订单表的例子先看旧索引下的执行计划EXPLAIN SELECT order_id, amount, create_time FROM orders WHERE user_id 12345 AND create_time 2024-01-01 00:00:00 ORDER BY create_time DESC LIMIT 20;旧索引下 type 是 refkey 是 idx_user_idrows 预估几千行Extra 里出现 Using filesort说明需要排序。改成联合索引(user_id, create_time, amount, order_id)后type 变成 rangekey 是联合索引rows 大幅下降Extra 里同时出现 Using index 和 Using index conditionfilesort 不见了。这一眼就能看出优化前后的差距。我一般在生产环境排查慢查询时会先打开慢查询日志把耗时超过阈值的 SQL 捞出来逐条 EXPLAIN按 type 从坏到好排序优先处理那些 ALL 和 filesort 的语句。这个方法不需要什么高深分析但效率极高大部分性能问题都是这样被定位出来的。4.3 区分度决定索引价值的隐藏指标老生常谈的一句话是“索引不是越多越好”但怎么判断一个列有没有必要建索引很多人其实没有思路。我的经验是先看区分度区分度 某列不同值的数量 / 总行数。区分度越高说明该列的每个值能过滤掉的数据越多索引价值就越大。像性别这种只有两个取值的列区分度极低用索引查出来的结果集可能占全表一半数据库还不如直接全表扫描。计算区分度很简单跑一条 SQL 就行SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;我习惯把 10% 当做一个参考线。区分度低于 10% 的列除非和别的列组成联合索引最左列否则单独建索引大概率是浪费空间。这个参考线不绝对但足够用来筛掉大部分无效方案。5. 索引失效的典型场景导航仪也会“失灵”5.1 函数操作和隐式转换索引失效重灾区最常见的索引失效原因是查询条件里对索引列做了函数处理。比如WHERE DATE(create_time) 2024-01-01MySQL 无法直接利用 create_time 的索引因为 B 树里存储的是原始值不是函数处理后的结果。正确写法是改成范围条件WHERE create_time 2024-01-01 AND create_time 2024-01-02这样索引才能正常命中。隐式转换是另一个隐蔽的大坑。比如手机号字段在表里是 varchar 类型查询时却传入了数字类型MySQL 会自动把字符串列转成数字再比较本质上就是对索引列做了函数操作索引自然失效。解决办法很简单查询参数的类型和字段类型保持完全一致应用层传参时不要偷懒。前导模糊查询也会让索引失效WHERE name LIKE %张无法使用索引但WHERE name LIKE 张%可以因为 B 树是按前缀排序的。如果业务真的需要前导模糊匹配可以考虑在应用层做分词或者换用全文索引而不是硬着头皮扫全表。5.2 联合索引违反最左前缀看似走了索引实则没用还有一种情况特别迷惑人一条查询的 EXPLAIN 显示 key 是某个联合索引但实际只用了其中一部分列。比如索引(a, b, c)查询条件是a 1 AND c 3此时 MySQL 会用索引定位a 1的记录但c 3这个条件要回表后过滤Extra 里会出现 Using index condition甚至 Using where。这种“半命中”状态比完全没走索引还难察觉你以为已经优化了其实查询效率依然不高。解决方式是调整联合索引的列顺序把等值查询条件放在最左边范围查询条件放右边。比如业务上同时支持a ? AND c ?和a ? AND b ? AND c ?那索引设计成(a, c)或(a, c, b)才能让两个查询都完全命中。这里最忌讳的就是生搬硬套“等值在前范围在后”这句话要考虑业务实际查询模式的覆盖情况。5.3 优化器放弃索引选择性差的尴尬时刻有时候索引存在条件也符合但优化器就是不选它。因为优化器会估算扫描行数和回表代价如果它认为全表扫描更便宜就会放弃索引。前文提到的低区分度列就是典型例子查询性别字段走索引可能还不如全表扫描快。这种情况下强行用 FORCE INDEX 反而可能适得其反因为优化器的判断通常基于统计信息它会尽量选择最优路径。遇到这种情况我的建议不是去和优化器较劲而是回到业务本身这个查询能不能去掉低区分度条件能不能改成联合索引把区分度高的列放前面如果一个查询因为低区分度列导致全表扫描而你尝试 FORCE INDEX 后发现性能提升明显那要小心了这往往意味着当前表的统计信息有问题可以先跑一次 ANALYZE TABLE 重新统计再看优化器的选择结果。6. 索引的日常维护创建只是开始治理才是常态6.1 索引冗余识别与清理实践很多系统跑了一两年后DBA 会发现索引越来越多但查询并没有变快写入反而变慢了。原因是每个索引都会拖慢 INSERT、UPDATE 和 DELETE因为每次数据变更都要同步维护全部索引。如果一个索引没有被任何查询真正用过它造成的全部是负收益。怎么找出冗余索引最直接的办法是从慢查询日志和 general log 里统计实际使用过的索引再和表上的索引做对比。MySQL 5.7 之后的 sys.schema_index_statistics 表可以统计每个索引被使用的情况例如SELECT * FROM sys.schema_index_statistics WHERE table_schema your_db ORDER BY rows_selected DESC;如果某个索引的 rows_selected 一直为 0说明它从未被使用过可以考虑删除。另外还要检查索引重复的情况比如idx_a和idx_a_b两个索引前者完全是冗余的因为后者能覆盖前者的查询。执行删除操作时要注意节奏。我会选择业务低峰期先删除明显冗余的索引观察一两天确保没有报警或慢查询回归再继续下一批。千万不要一次删好几个万一有隐藏查询依赖排查起来会很痛苦。6.2 新索引上线前需要做的三件事新索引上线也是一门学问不是写个 ALTER TABLE 执行就完事。在线上环境加索引我一般按三步走。第一步是评估利用前文提到的区分度 SQL 确认索引列的区分度同时通过 EXPLAIN 验证目标查询在使用新索引后的 type、rows、Extra 有没有明显改善。如果改善不明显这个索引就不值得建。第二步是规划执行时间表数据量超过几百万行时加索引会锁表必须放到业务低谷期或者使用在线 DDL 工具让变更对业务的影响降到最低。第三步是灰度验证先在从库上执行加索引观察复制延迟和查询表现确认没问题再切到主库。还有一个我会特别留意的点加索引之前确认存在同类型的查询因为索引本身就是空间换时间。一个小查询如果一天只跑几次给它建个占用几百 MB 的索引长期看并不划算。我见过太多因为“顺手建一下”导致表空间暴涨、写入变慢的案例。6.3 索引维护的常态化机制索引不是建完就一劳永逸的业务在变数据量在变索引也需要动态调整。我建议每季度对整个核心库做一次索引全面体检包括从慢查询日志找出新出现的扫描类型为 ALL 的 SQL检查新增业务查询是否缺少索引用 sys.schema_index_statistics 找出长时间未使用的索引并提交下线方案检查主键增长趋势评估是否需要优化数据归档和分区策略。这套机制看起来简单但能挡住大多数“索引在不知不觉中劣化”的问题比出了故障再救火要省心得多。按我个人这几年处理 MySQL 性能问题的经验线上数据库的慢查询80% 以上都是索引设计不合理或者索引失效导致的。你先把正常的 B 树查找逻辑想明白再把联合索引、覆盖索引、最左前缀这几个关键规则用熟大多数性能问题在写 SQL 的时候就能提前规避掉。遇到线上问题也别慌EXPLAIN 永远是你手里最顺手的工具打开执行计划一行一行看下去问题往往自己就暴露出来了。 
长河君 · 2026/10/20 5:25:20 · 阅读 1.2k MySQL索引优化：从B+树到覆盖索引的慢查询调优实践
日常工作里最容易被忽略却又对系统性能影响最立竿见影的东西往往就是索引。很多同学对索引的理解停留在“查询慢就加个索引”但对于为什么要这样加、加完为什么生效、什么时候不生效脑子里并没有一条完整的逻辑链。这篇文章我想从一个“导航仪”的角度重新拆解一下 MySQL 索引你看看索引是怎么一步步带着数据库找到数据的也希望你以后在优化慢查询时能有一套自己的判断方法。无论你是刚入行的后端开发还是已经在生产环境里处理过几次慢查询的运维或 DBA这篇文章都适合你。我会从最基础的索引数据结构讲起然后结合真实案例聊索引如何设计、如何用执行计划验证效果再把你最可能踩到的“索引失效”坑一个个捡出来。全程没有晦涩的源码分析尽量用大家都能听懂的大白话把索引的底层逻辑、设计思路和生产实践串成一条线。1. 索引的底层逻辑它凭什么让查询“飞”起来1.1 从“翻目录”到“走捷径”索引到底是个什么东西想象一下你要在一本 500 页的技术书里找一个特定名词最笨的办法是从第一页翻到最后一页看到目标就停下来这叫全表扫描。而书的最后几页通常会有一个“索引”按拼音或笔画顺序排列着所有名词和对应的页码你一翻目录就能直接跳到那一页。数据库里的索引干的就是这个事它是独立于表数据之外的一种结构记录了索引列的值和对应数据行的物理位置或者主键值查询时只需要在索引里做快速查找找到目标后再根据指针去取完整的数据这个动作叫回表。所以索引并不是什么神秘的东西它就是数据库为查询准备的“目录”。但它的意义远不止减少扫描行数。索引还能避免排序、避免临时表、加速连接甚至在某些情况下直接把查询需要的数据全部覆盖掉连回表都省了。这些能力会在后面的实践部分一个一个展开这里先建立一个核心认知数据库里没有索引时查询是线性扫描有合适的索引时查询是树形查找复杂度从 O(N) 降到 O(logN)量级完全不同。1.2 B 树数据库为什么偏爱这棵“多叉树”市面上能见到的索引结构有很多哈希、二叉树、跳表、B 树、B 树各有各的场景。MySQL 里 InnoDB 存储引擎的索引却统一采用 B 树原因是它和磁盘 IO 的配合最默契。B 树对比普通二叉树最大的区别是“多叉”。一个节点可以存多个 key树的高度被压得很低三层到四层的 B 树就能撑住千万级乃至上亿级的数据量也就是说哪怕表里有一千万行查询一个索引键也只需要三次左右的磁盘 IO。这三次 IO 和全表扫描的几万次 IO 相比性能差距是几何级的这玩意儿才是极速查询的真正底气。还有一点容易忽略B 树的所有数据都存在叶子节点上而且叶子节点之间是通过双向链表串联起来的。这个设计和范围查询关系很大当你执行WHERE id BETWEEN 100 AND 200时数据库在索引中找到 100 这条记录后直接顺着链表往后扫就行不需要反复从根节点重新遍历。普通 B 树的叶子节点之间没有这个链表范围查询会变成多次独立的查找代价高得多。这里有个观点方便理解B 树既能精准定位又能流畅地顺序遍历天生就是为关系型数据库的查询模式准备的。1.3 聚簇索引和非聚簇索引说说“行”到底存在哪InnoDB 里每个表都有一个聚簇索引这个索引的叶子节点保存的是整行数据其他所有二级索引的叶子节点保存的是主键值。所以聚簇索引的键值顺序决定了表数据的物理存储顺序一张表只能有一个聚簇索引。InnoDB 会优先选择主键作为聚簇索引如果没有主键就选第一个非空唯一索引二者都没有则内部生成一个隐藏的 rowid 来充当这个角色。理解聚簇索引的第二个关键点是二级索引的查询都要经历一次“找主键再回表”的过程。这里有个很容易踩的误区很多人以为建了索引查询就一定快实际如果查询需要回表且回表次数过多性能依然很差。后面讲覆盖索引的时候会专门解决这个问题。现在只要记住这个模型的链路二级索引查到主键主键去聚簇索引里取行数据两个 B 树的查找各来一次。2. 索引类型怎么选不同业务场景需要不同“导航策略”2.1 主键索引、普通索引、唯一索引的分工差异主键索引的叶子节点存的是整行数据它天然具备唯一性并且在插入记录时 InnoDB 会按主键顺序组织页。如果一个表的主键是自增整数新记录总是追加到 B 树的最右侧页分裂的概率较低写入性能最稳定。如果你的主键是一个随机生成的 UUID那新纪录会随机落在 B 树的各个位置频繁触发页分裂与页重排写入吞吐会明显下降。我处理过一个案例某业务表把 UUID 当主键批量导入 200 万行数据耗时接近一小时的量级换成自增主键后同样数据只用了不到十分钟差别就是这么夸张。唯一索引相比普通索引除了查询加速多了一层约束作用保证列值唯一。业务上可用它来防止脏数据重复比如用户手机号、订单号这类字段。但唯一索引的插入性能略差于普通索引因为每次插入都需要额外检查唯一性约束。于是在“需要去重”和“写入量大”之间就要做取舍。如果业务上能接受“最终一致后再清洗”可以考虑不建唯一索引在应用层或离线任务里统一处理能明显提升写入速度。普通索引则没有任何约束负担它的存在完全是为了查询加速。在创建索引之前你需要问自己一句这个索引是为了哪条慢查询服务如果回答不上来那这个索引大概率是冗余的。2.2 联合索引多条件查询的“组合导航”联合索引也叫复合索引是建立在多个列上的索引。很多人把它理解成“每个列各自建立一个索引”这是本世纪最大的误解之一。联合索引里的列有顺序之分它先按第一列排序第一列相同的情况下再按第二列排序以此类推。它在 B 树里是一个连续的多列键值而不是多个独立索引。联合索引最实用的价值在于能同时过滤多个条件。比如订单表经常按user_id和status查询建立一个(user_id, status)联合索引就能在一次索引查找中同时完成两个条件的过滤几乎不需要回表。相比之下如果分别建user_id和status两个单列索引MySQL 通常只会选择其中一个过滤另一个条件只能靠回表后的数据筛选性能差距非常明显。2.3 最左前缀原则联合索引的“游戏规则”最左前缀原则是联合索引使用中必须刻在脑子里的规则查询条件中必须包含联合索引的最左列这个索引才会被考虑使用。联合索引是从左到右逐列匹配的跳过了左边第一列后面的列再精确也无济于事。举个例子索引(a, b, c)可以支持a ?、a ? AND b ?、a ? AND b ? AND c ?这三类查询但不支持b ?或c ?开头的查询条件。这个规则背后是 B 树的排序逻辑叶子节点按a排序a相同才按b排序b相同才按c排序。所以如果查询条件里没有a整棵索引树都无法定位起始位置只能全树扫描数据库觉得这种情况下还不如直接扫主表划算于是放弃索引。理解最左前缀原则还有一个实际收益设计联合索引时可以把多个频繁使用的单列查询合并成一个索引减少索引数量。比如系统里常用的查询条件是(a)和(a, b)一个(a, b)联合索引就能同时覆盖两种场景完全不需要为a再单独建索引。我见过很多表里单列索引建了七八个真正被用到的只有两三个其余全成了冗余负担后面会专门讲怎么清理。3. 用覆盖索引优化查询能不回表就别回表3.1 回表的代价到底有多大前面说过二级索引找到主键后还需要去聚簇索引里取完整数据这个过程叫回表。回表本身不是灾难真正的灾难是回表次数太多。比如一个二级索引的字段区分度很低像订单状态只有几个枚举值通过它可能一下子筛出几十万行主键。这时候数据库要按主键去聚簇索引里随机访问几十万次每次都是一次独立的 B 树查找IO 压力直接拉满查询自然就慢了。所以我在设计查询时第一反应永远是看能不能让查询所需的所有列都包含在索引树里这样索引本身就能给查询返回全部数据不需要再回表。这种索引被称为覆盖索引。覆盖索引不是一种独立的索引类型而是一种“索引被查询完整覆盖”的状态。3.2 一个实际订单查询的优化对比给你看一个我优化过的订单查询场景。有一张订单表包含order_id、user_id、status、amount、create_time等字段业务上有个高频查询是按照user_id查最近 30 天的订单列表并显示金额。最初表上只有user_id单列索引查询语句长这样SELECT order_id, amount, create_time FROM orders WHERE user_id 12345 AND create_time 2024-01-01 00:00:00 ORDER BY create_time DESC LIMIT 20;这个查询会走user_id索引找到该用户的所有主键再回表取order_id、amount、create_time然后排序取前 20 条。如果这个用户订单量是几千条那就要经历几千次回表听起来还能接受但如果这个查询被用户频繁触发几千次回表累积起来的延迟就很可观了。我把索引调整成联合索引(user_id, create_time, amount, order_id)然后重新分析这条查询user_id和create_time被用于索引范围匹配order_id和amount都可以直接从索引叶子节点拿到查询所需的全部列都覆盖在索引里了回表彻底消失排序也因为索引天然按create_time有序而一并省掉。调整后该接口的 P99 延迟从 120 毫秒降到了 15 毫秒以内索引体积增加了几 MB换来的是十几倍的查询提升这笔交易非常划算。3.3 如何判断一个查询有没有被索引覆盖最直接的方法是看执行计划里的 Extra 列。如果显示Using index说明查询被索引覆盖如果显示Using index condition说明索引下推已经生效但仍有部分数据需要回表过滤如果显示Using where且没有 Using index那就是普通回表查询。补充一下 MySQL 5.6 以后引入的索引下推优化它的作用是把部分 WHERE 条件的过滤下推到索引遍历过程中减少回表次数。假设联合索引是(user_id, create_time)查询条件是user_id 1 AND create_time 2024-01-01 AND status PAID虽然status不在索引里但 MySQL 可以在遍历索引时先过滤掉不满足status条件的主键再回表这样回表数量会大幅下降。这个优化是自动触发的但你得知道它的存在才能解释为什么有时候 Extra 里会出现Using index condition也不用担心是不是索引没建好。4. 用 EXPLAIN 给查询做“体检”读懂执行计划的关键信息4.1 执行计划到底要看哪些字段EXPLAIN 是 MySQL 用来展示一条 SQL 执行规划的语句你只需要在 SELECT 前面加上 EXPLAIN 就能看到。面对一长串字段新手容易懵其实真正需要重点关注的只有几列。首先是type它告诉你 MySQL 是怎么扫数据的。从好到差大致是const、eq_ref、ref、range、index、ALL。const表示按主键或唯一索引精准定位一行ref表示普通索引等值匹配range表示索引范围扫描ALL是全表扫描。如果看到 ALL说明这条 SQL 大概率是性能瓶颈之源除非表本身很小否则必须想办法走索引。然后是key它表示实际选中的索引是什么而possible_keys则列出所有可能被用到的索引。有时候 possible_keys 里有几个候选key 只选了其中一个这是正常现象优化器会根据代价模型挑一个它认为最划算的。如果 possible_keys 是空key 也是空那说明这条 SQL 根本没找到可用索引。再然后是rows这是一个预估的扫描行数虽然不一定准但能帮你判断查询的代价量级。优化前后对比 rows 的变化比盯着耗时更客观因为耗时受缓存影响太大。最后是Extra里面藏着很多特殊标记Using filesort表示需要额外排序Using temporary表示需要临时表常见于 GROUP BY 和去重这两个都是需要重点消灭的目标。4.2 配合实际案例理解执行计划接着用前面订单表的例子先看旧索引下的执行计划EXPLAIN SELECT order_id, amount, create_time FROM orders WHERE user_id 12345 AND create_time 2024-01-01 00:00:00 ORDER BY create_time DESC LIMIT 20;旧索引下 type 是 refkey 是 idx_user_idrows 预估几千行Extra 里出现 Using filesort说明需要排序。改成联合索引(user_id, create_time, amount, order_id)后type 变成 rangekey 是联合索引rows 大幅下降Extra 里同时出现 Using index 和 Using index conditionfilesort 不见了。这一眼就能看出优化前后的差距。我一般在生产环境排查慢查询时会先打开慢查询日志把耗时超过阈值的 SQL 捞出来逐条 EXPLAIN按 type 从坏到好排序优先处理那些 ALL 和 filesort 的语句。这个方法不需要什么高深分析但效率极高大部分性能问题都是这样被定位出来的。4.3 区分度决定索引价值的隐藏指标老生常谈的一句话是“索引不是越多越好”但怎么判断一个列有没有必要建索引很多人其实没有思路。我的经验是先看区分度区分度 某列不同值的数量 / 总行数。区分度越高说明该列的每个值能过滤掉的数据越多索引价值就越大。像性别这种只有两个取值的列区分度极低用索引查出来的结果集可能占全表一半数据库还不如直接全表扫描。计算区分度很简单跑一条 SQL 就行SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;我习惯把 10% 当做一个参考线。区分度低于 10% 的列除非和别的列组成联合索引最左列否则单独建索引大概率是浪费空间。这个参考线不绝对但足够用来筛掉大部分无效方案。5. 索引失效的典型场景导航仪也会“失灵”5.1 函数操作和隐式转换索引失效重灾区最常见的索引失效原因是查询条件里对索引列做了函数处理。比如WHERE DATE(create_time) 2024-01-01MySQL 无法直接利用 create_time 的索引因为 B 树里存储的是原始值不是函数处理后的结果。正确写法是改成范围条件WHERE create_time 2024-01-01 AND create_time 2024-01-02这样索引才能正常命中。隐式转换是另一个隐蔽的大坑。比如手机号字段在表里是 varchar 类型查询时却传入了数字类型MySQL 会自动把字符串列转成数字再比较本质上就是对索引列做了函数操作索引自然失效。解决办法很简单查询参数的类型和字段类型保持完全一致应用层传参时不要偷懒。前导模糊查询也会让索引失效WHERE name LIKE %张无法使用索引但WHERE name LIKE 张%可以因为 B 树是按前缀排序的。如果业务真的需要前导模糊匹配可以考虑在应用层做分词或者换用全文索引而不是硬着头皮扫全表。5.2 联合索引违反最左前缀看似走了索引实则没用还有一种情况特别迷惑人一条查询的 EXPLAIN 显示 key 是某个联合索引但实际只用了其中一部分列。比如索引(a, b, c)查询条件是a 1 AND c 3此时 MySQL 会用索引定位a 1的记录但c 3这个条件要回表后过滤Extra 里会出现 Using index condition甚至 Using where。这种“半命中”状态比完全没走索引还难察觉你以为已经优化了其实查询效率依然不高。解决方式是调整联合索引的列顺序把等值查询条件放在最左边范围查询条件放右边。比如业务上同时支持a ? AND c ?和a ? AND b ? AND c ?那索引设计成(a, c)或(a, c, b)才能让两个查询都完全命中。这里最忌讳的就是生搬硬套“等值在前范围在后”这句话要考虑业务实际查询模式的覆盖情况。5.3 优化器放弃索引选择性差的尴尬时刻有时候索引存在条件也符合但优化器就是不选它。因为优化器会估算扫描行数和回表代价如果它认为全表扫描更便宜就会放弃索引。前文提到的低区分度列就是典型例子查询性别字段走索引可能还不如全表扫描快。这种情况下强行用 FORCE INDEX 反而可能适得其反因为优化器的判断通常基于统计信息它会尽量选择最优路径。遇到这种情况我的建议不是去和优化器较劲而是回到业务本身这个查询能不能去掉低区分度条件能不能改成联合索引把区分度高的列放前面如果一个查询因为低区分度列导致全表扫描而你尝试 FORCE INDEX 后发现性能提升明显那要小心了这往往意味着当前表的统计信息有问题可以先跑一次 ANALYZE TABLE 重新统计再看优化器的选择结果。6. 索引的日常维护创建只是开始治理才是常态6.1 索引冗余识别与清理实践很多系统跑了一两年后DBA 会发现索引越来越多但查询并没有变快写入反而变慢了。原因是每个索引都会拖慢 INSERT、UPDATE 和 DELETE因为每次数据变更都要同步维护全部索引。如果一个索引没有被任何查询真正用过它造成的全部是负收益。怎么找出冗余索引最直接的办法是从慢查询日志和 general log 里统计实际使用过的索引再和表上的索引做对比。MySQL 5.7 之后的 sys.schema_index_statistics 表可以统计每个索引被使用的情况例如SELECT * FROM sys.schema_index_statistics WHERE table_schema your_db ORDER BY rows_selected DESC;如果某个索引的 rows_selected 一直为 0说明它从未被使用过可以考虑删除。另外还要检查索引重复的情况比如idx_a和idx_a_b两个索引前者完全是冗余的因为后者能覆盖前者的查询。执行删除操作时要注意节奏。我会选择业务低峰期先删除明显冗余的索引观察一两天确保没有报警或慢查询回归再继续下一批。千万不要一次删好几个万一有隐藏查询依赖排查起来会很痛苦。6.2 新索引上线前需要做的三件事新索引上线也是一门学问不是写个 ALTER TABLE 执行就完事。在线上环境加索引我一般按三步走。第一步是评估利用前文提到的区分度 SQL 确认索引列的区分度同时通过 EXPLAIN 验证目标查询在使用新索引后的 type、rows、Extra 有没有明显改善。如果改善不明显这个索引就不值得建。第二步是规划执行时间表数据量超过几百万行时加索引会锁表必须放到业务低谷期或者使用在线 DDL 工具让变更对业务的影响降到最低。第三步是灰度验证先在从库上执行加索引观察复制延迟和查询表现确认没问题再切到主库。还有一个我会特别留意的点加索引之前确认存在同类型的查询因为索引本身就是空间换时间。一个小查询如果一天只跑几次给它建个占用几百 MB 的索引长期看并不划算。我见过太多因为“顺手建一下”导致表空间暴涨、写入变慢的案例。6.3 索引维护的常态化机制索引不是建完就一劳永逸的业务在变数据量在变索引也需要动态调整。我建议每季度对整个核心库做一次索引全面体检包括从慢查询日志找出新出现的扫描类型为 ALL 的 SQL检查新增业务查询是否缺少索引用 sys.schema_index_statistics 找出长时间未使用的索引并提交下线方案检查主键增长趋势评估是否需要优化数据归档和分区策略。这套机制看起来简单但能挡住大多数“索引在不知不觉中劣化”的问题比出了故障再救火要省心得多。按我个人这几年处理 MySQL 性能问题的经验线上数据库的慢查询80% 以上都是索引设计不合理或者索引失效导致的。你先把正常的 B 树查找逻辑想明白再把联合索引、覆盖索引、最左前缀这几个关键规则用熟大多数性能问题在写 SQL 的时候就能提前规避掉。遇到线上问题也别慌EXPLAIN 永远是你手里最顺手的工具打开执行计划一行一行看下去问题往往自己就暴露出来了。