不绕弯子MySQL索引这个东西属于后端开发里“平时你不用管一出事就火烧眉毛”的角色。很多同学对索引的认知停留在“查询慢就加索引”这个层面但真到了线上慢查询报警、数据库CPU飙高的时候光会加索引远远不够。你得知道索引底层是什么、为什么有些字段加了索引也没用、为什么同一个SQL换个写法性能差几十倍这才是排查和优化的关键。这篇内容我会从一次真实的慢查询案例切入把索引的类型、B树原理、组合索引设计、失效场景以及EXPLAIN排查套路全部梳理一遍。适合刚接触数据库优化的新手也适合写过几年SQL但没系统研究过索引的老手。我不堆概念讲的都是实际能落地的经验。1. 从一次慢查询看索引的本质1.1 一条三秒才出结果的查询有次线上反馈某个报表页面打开特别慢查了一下日志发现有一条SQL执行了将近3秒SELECT order_id, user_id, amount FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;orders表当时数据量并不算夸张大概800万行。问题就在于user_id上面没有索引每次执行都是全表扫描。MySQL只能把800万行数据一条一条读出来筛出user_id匹配的记录然后再做一次排序最后才取20条返回。后来给user_id加了一个普通索引这条SQL的执行时间直接降到了30毫秒以内性能提升了差不多100倍。这个例子很直观地说明了索引的核心价值它让MySQL从“翻遍整本书找一句话”变成了“直接翻到目录页定位到那一页”。全表扫描的时间复杂度是O(n)而走B树索引查询的时间复杂度是O(log n)数据量越大差距越恐怖。1.2 先搞清楚MySQL是怎么存储数据的要说清楚索引为什么快得先知道数据在MySQL里是怎么放的。InnoDB存储引擎的数据文件本身就是按主键构建的一棵B树也就是说表里的每一行数据真实存放在主键索引的叶子节点上。这个概念非常重要因为很多索引问题想不通都是因为没建立这个模型。你可以把InnoDB的表想象成一本按照页码顺序写满内容的书这本书的目录就是主键索引。你通过目录找到对应页码翻到那一页就能看到完整内容。那其他索引呢比如你在user_id上建了一个普通索引InnoDB会额外构建一棵B树但这棵树的叶子节点不存完整行数据只存两个东西索引列的值以及对应的主键值。所以当你的SQL走了user_id这个索引时MySQL做的事情其实是在user_id索引树上找到匹配的记录拿到主键id。拿着主键id再去主键索引树里查一次取出完整的行数据。这个第二步操作就是大家常说的“回表”。“回表”这个词必须印在脑子里因为很多SQL慢就慢在回表次数太多。1.3 索引到底解决了什么问题我们可以把索引解决的三大问题列出来每一条都对应一类真实场景快速定位数据避免全表扫描。这是索引最基础的作用数据量大时效果立竿见影。加速排序和分组。如果ORDER BY和GROUP BY涉及的字段有合适的索引MySQL可以直接利用索引的有序性不需要额外的排序操作。覆盖索引可以减少随机IO。当索引中包含了查询需要的所有字段时MySQL连回表都省了直接从索引叶子节点取数据。理解索引的本质不是背书而是为了后面排查问题时脑子能转得过弯来。比如你遇到一条SQL慢第一反应就应该是它是不是在走全表扫描它是不是回表次数太多它的排序能不能用索引本身的有序性来解决这三个问题想清楚了80%的索引问题都能定位得八九不离十。2. 索引的底层逻辑与类型选择2.1 为什么选B树而不是二叉树或哈希MySQL索引默认使用B树但很多教材对这块讲得太抽象我换个说法。哈希表查询速度确实快理论上O(1)的复杂度但有个致命问题哈希表没办法做范围查询。你查user_id 1000这种条件哈希表只能全表扫描。而且哈希索引对排序也完全无能为力因为数据是散列存储的没有顺序可言。所以InnoDB的默认索引结构选了B树而不是哈希表只有在某些特定场景下才会用到自适应哈希索引作为内部优化。那为什么不用二叉树呢二叉树在极端情况下会退化成链表比如插入有序数据时BST会变成一条线查询复杂度退化成O(n)。虽然红黑树解决了这个问题但它依然是一棵“矮胖”树的近亲树太高了。每访问一个节点就对应一次磁盘IO树的高度直接决定查询要读多少次磁盘。B树的精髓在于“矮胖”非叶子节点不存数据只存索引键和指针所以一页能放很多很多个键树的高度通常只有2到4层。哪怕3亿行数据的表走主键索引查询基本上3次IO就能定位到目标数据。这就是为什么InnoDB选择B树作为默认索引结构。2.2 几类索引类型的区别MySQL的索引从功能上分常见的有这几种索引类型特点适用场景主键索引每张表只能有一个叶子节点存完整行数据必须有InnoDB表推荐使用自增主键唯一索引保证列值唯一允许NULL业务上需要唯一约束的字段比如手机号、身份证号普通索引无唯一性要求只是为了加速查询查询频繁、区分度高的字段组合索引多个字段组成一个索引多条件查询、排序混合场景全文索引用于全文检索支持中文需NGram大文本字段、站内搜索每种索引都有它的设计意图。比如唯一索引不仅是约束更是优化手段。你要查mobile 138xxxx如果mobile上有唯一索引MySQL知道最多只有一行满足条件查到一个就立即停止扫描效率极高。再说说主键设计。我见过不少业务表没有显式主键或者用UUID当主键这在InnoDB下是有隐患的。因为InnoDB的聚簇索引就是主键索引如果你没有主键它会找一个非空唯一索引替代实在没有就用隐藏的rowid。更麻烦的是如果用UUID当主键插入数据时因为UUID是随机的B树需要频繁分裂调整产生大量碎片写入性能会明显下降。自增主键在顺序插入时对B树最友好这也是很多老DBA坚持用自增ID的原因。3. 组合索引与设计原则3.1 最左前缀原则组合索引的命门组合索引是实践中踩坑最多的地方。很多人以为建了(a, b, c)联合索引那a、b、c三个字段的查询就都能走索引了这个理解是错误的。联合索引本质上是一个“多列拼起来的排序”。在B树里数据先按a排序a相同再按b排序b相同再按c排序。所以你的查询条件只有遵循这个从左到右的顺序索引才能生效。这就是最左前缀原则。具体来说有索引idx(a, b, c)以下情况可以走索引WHERE a 1 WHERE a 1 AND b 2 WHERE a 1 AND b 2 AND c 3以下情况走不了索引WHERE b 2 WHERE c 3 WHERE b 2 AND c 3因为b和c离开了前缀a在索引树里是“乱序”的MySQL没法利用索引的有序性快速定位只能扫更广的范围甚至全表。补充一个容易被忽略的点查询条件里的顺序跟SQL的书写顺序无关。MySQL的优化器会帮你调整条件顺序所以WHERE b 2 AND a 1也能走索引关键是你提供的条件里包含哪些列。3.2 字段怎么挑索引选择性与区分度设计索引时字段的选择比数量更重要。最核心的指标是“区分度”通俗说就是这列有多少种不同的值。比如性别只有男女两种区分度就低身份证号几乎一值一样区分度就高。区分度低的字段加索引往往事倍功半。假设你在性别字段上建了索引查询WHERE gender male如果男性占了一半MySQL扫描到一半行还要回表优化器一算觉得还不如直接全表扫干脆放弃索引。全表扫描虽然慢但有时候确实比回表快这是优化器的成本估算逻辑。所以设计索引时优先考虑那些在WHERE条件里出现的、区分度高的字段。区分度可以用一个SQL直观算一下SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM your_table;这个值与1越接近说明区分度越高越适合建索引。3.3 覆盖索引少一次回表就快一分覆盖索引值得单独拿出来讲因为它在优化场景里效果太明显了。二级索引的叶子节点存的是索引列加主键值。如果一次查询要的所有字段恰好都包含在索引本身的结构里MySQL就不需要回表去主键索引取行数据直接从索引树上就拿到了全部内容。这种情况叫覆盖索引。举个实际例子。我们的用户表上有索引idx(name)有一个高频查询是SELECT id, name FROM user WHERE name 张三;因为id是主键name是索引列这两个字段都在idx(name)这棵索引树上所以MySQL可以直接扫描索引返回结果完全不需要回表。但如果查询变成SELECT id, name, email FROM user WHERE name 张三;name索引上并没有email字段MySQL只能在索引里找到匹配的id再回表去主键索引取email。如果命中的行数很多回表次数就很多性能明显下降。这时候可以考虑一个扩展方案把索引从idx(name)改成idx(name, email)让email也进入索引树。查询需要的字段全都在索引里覆盖索引生效回表一步取消。我自己的经验是在设计组合索引时尽量把SELECT里出现频率高的字段也顺手加进去让它形成覆盖索引。但也要克制不能为了覆盖索引无限加字段因为索引本身也是要占空间、要维护的加太多会拖慢写入性能。3.4 组合索引里字段先后顺序的实战判断组合索引的字段顺序要根据查询场景来定。我的排序习惯是区分度高的字段放前面。经常用于等值查询的字段放前面。用于范围查询或排序的字段放在最后。有人可能会有疑问不是一直说最左前缀吗那就把最常用的查询字段放左边不就行了这个说法对但不完整。因为范围查询会影响后续字段的索引利用。比如索引idx(a, b)查询是WHERE a 100 AND b 1。a走范围后b的等值条件就用不上索引排序了索引的作用被削弱。但如果你把b放前面索引换成idx(b, a)那b 1能精确定位a的范围查询就有序可走整体效率反而更好。所以设计组合索引时多问自己几个问题这个SQL是等值还是范围哪个字段更常作为等值条件哪个字段是用来排序的理清楚这几个问题再决定字段顺序准确性会高很多。4. 索引失效场景与EXPLAIN排查4.1 我踩过的5个索引失效场景索引不是建了就一定生效有很多场景会让MySQL“无视”你的索引。总结一下我在实际工作中踩过的坑。第一个坑是在索引列上做函数操作。SELECT * FROM orders WHERE DATE(create_time) 2024-01-15;create_time上有索引但DATE()函数包住了它索引直接失效。因为MySQL必须先对每一行的create_time执行函数计算才能拿结果去跟右侧的值比较这就变成全表扫描了。正确写法是用范围条件SELECT * FROM orders WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;第二坑是隐式类型转换。如果mobile字段是varchar类型但你写WHERE mobile 13800001111数字字面量会被转换成字符串再比较这通常会破坏索引的匹配方式。更隐蔽的情况是字符集不一致比如表字段用的utf8mb4连接字符集也是utf8mb4时没问题但一旦出现不同字符集比较或运算索引也会失效。第三坑是LIKE前置通配符。SELECT * FROM article WHERE title LIKE %MySQL优化%;%号在开头意味着你无法确定字符串从哪个字符开始B树没法做范围定位。但LIKE MySQL%这种左前缀不会写死是可以走索引的。第四坑是索引列上的计算或表达式操作。WHERE num * 2 100这个跟函数操作同理num列被卷入运算索引列的原值参与不了直接比较。改成WHERE num 50就可以走索引。第五坑是OR条件组合。虽然a和b分别都有索引但写成WHERE a 1 OR b 2时MySQL可能并不像你期望的那样分别走两个索引再合并。只有当OR两边的条件都能拿到索引范围或主键时优化器才可能走索引合并否则就会退化为全表扫描。习惯性写法是把OR写成两个查询用UNION或者改用IN列表。4.2 用EXPLAIN定位慢查询的实战套路索引有没有生效不能靠猜要看执行计划。MySQL的EXPLAIN命令可以展示SQL的执行计划这是排查慢查询的第一工具。EXPLAIN SELECT order_id, user_id, amount FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;输出里有一大堆字段我看得最多的是四个type、key、rows、Extra。type表示扫描方式我把几种常见值按性能从好到差排个序type值含义好坏const主键或唯一索引等值查询最多返回一行极佳eq_ref联表查询时被驱动表走主键或唯一索引极佳ref普通索引等值查询良好range索引范围扫描比如between、in、 中等index索引全扫描比全表好一点但有限一般ALL全表扫描很差key表示实际用到的索引如果是NULL基本可以断定没走索引。rows是预计扫描的行数这个数字越小越好。Extra里面如果出现Using filesort或者Using temporary说明排序或分组没走索引需要检查是否能用索引的有序性来避免这些操作。我总结了一个简单的排查顺序先看type是不是ALL是的话大概率索引没生效再看key是不是用上了你预期的索引然后看rows是否比预期大很多最后看Extra里有没有额外的排序和临时表。把这四步看下来一条SQL的索引问题基本就能定位了。4.3 回表次数怎么判断回表次数在EXPLAIN里没有直接的字段展示但可以间接推断。当type为ref或range且key是二级索引时rows字段表示预计需要扫描的索引记录条数。如果SELECT的字段里有一个不在索引中那扫描多少条索引记录就可能回表多少次。夸张一点的例子一个idx(name)索引查出5万条匹配记录但SELECT还需要email字段那就要回表5万次。这个量级的随机IO数据库肯定会慢。这时候可以用覆盖索引来减少回表。把email加到索引里覆盖索引生效后Extra里会显示Using index这时候连回表的动作都省了效果立竿见影。5. 索引维护与优化实操5.1 索引冗余是个隐形杀手很多团队建索引的习惯是“一条慢查询加一个索引”时间长了索引越来越多但大部分是冗余的。比如你已经有了(a, b, c)联合索引又单独建了a的单列索引这两个索引其实是重复的。前者已经能覆盖所有需要a的条件查询后者纯粹是浪费空间和维护成本。判断索引是否冗余核心思路是看它能不能被其他索引替代。一个索引如果既是另一个索引的最左前缀又没有额外的唯一性约束那它基本就是冗余的。每多一个索引INSERT和UPDATE时就要多维护一棵B树索引越多写入越慢磁盘占用越大。这在百万级数据的表上升级得很明显。所以做索引评审时我经常拿一个SQL出来跑一遍所有候选方案把能删的冗余索引直接删掉。5.2 基于实际工作负载的索引调整索引设计不是“一锤子买卖”要跟随业务和查询模式的变化持续调整。我的做法是每过一段时间把慢查询日志和数据库的性能指标拉出来看一遍找出那些执行频率高、耗时长的SQL逐个分析。分析的时候有一个注意事项线上数据的分布和测试环境差别很大。同一个SQL在测试环境走索引很快上了生产环境因为数据分布不同优化器的成本估算结果也可能不同。所以加索引前最好用生产环境的数据量模拟一下执行计划避免出现“测试明明很快上线依然慢”的情况。5.3 分页深度过滤和优化器误判的实战技巧分页场景里有个经典性能问题LIMIT 100000, 20。MySQL虽然用了索引但会把前100020条记录都扫一遍再丢掉前10万条。po数量大了以后这个偏移代价非常高。一种比较实用的优化方案是“延迟关联”SELECT a.id, a.order_id, a.amount FROM ( SELECT id FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 100000, 20 ) t JOIN orders a ON a.id t.id;内层先用索引把需要的id快速筛出来再通过主键关联去取完整的数据。这样二级索引扫描的压力和回表压力都被控制在比较小的范围内比直接LIMIT深翻页快很多。另外还要留意优化器“不听话”的情况。明明字段上有索引但优化器因为统计信息不准选择了全表扫描。这种时候可以先做一次ANALYZE TABLE更新统计信息。如果还不走索引且你能确认走索引更快可以用FORCE INDEX强制指定索引SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id 12345;不过FORCE INDEX是手工干预业务一旦变化可能就不适用了最好还是通过更新统计信息或者改写SQL来解决问题而不是依赖强制索引。6. 最后聊几句经验我不太喜欢一上来就背索引规则更喜欢把问题场景拉到眼前来判断。每次遇到慢查询我都会在这几个问题里打转这个查询是不是全表扫描了它回表了多少次排序能不能用索引的有序性联合索引的最左前缀用上了没有只要这几个问题有答案索引优化的方向基本就明确里。实际项目中我还养成了一个习惯每个新索引都要和现有的索引做一遍“功能对比”确认它没有在重复建设。有些时候与其新加一个索引不如调整已有索引的字段顺序反而更优雅。MySQL索引这块的门槛不在理解原理而在经验积累。你处理过的慢查询越多踩过的坑越多后面看到一条SQL时就越发能够一眼判断出问题出在哪里。希望这篇文章能把你的索引知识串起来下次再遇到MySQL的慢查询报警能多一分从容。