MySQL索引深度解析：B+树结构、联合索引与性能优化

前几章我们把MySQL的安装、建库建表、增删改查、事务机制都过了一遍如果你一直跟着敲应该已经能写出完整可用的SQL了。但我想大家心里都有个疙瘩为什么一样的WHERE条件同事的SQL跑得飞快我的数据一多就卡成PPT答案十有八九是索引。第八章我想认真聊透这个话题从B树的底层原理到联合索引的建法再到那些很容易踩的失效场景和8.0的高级特性一次性讲明白。我不打算绕弯子这一章内容信息量比较大但每一节都值得你花时间慢慢读。学习顺序我建议这样走先搞懂索引为什么快底层结构再知道有哪几种索引可用类型选择接着学会给真实业务建索引联合索引实战然后弄明白哪些SQL写法会浪费索引失效排查最后再看排序优化和8.0的新玩法。很多人在索引上栽跟头就是把顺序搞反了——上来就背“最左前缀原则”却不知道索引在磁盘上是怎么组织的遇到问题自然只能瞎猜。1. 索引值得在入门阶段就研究透1.1 先感受一下没有索引有多痛我见过太多业务代码前期数据量小的时候一切正常有一天表里累积了几十万行某个常用查询突然从几十毫秒变成几秒钟。为什么会这样因为没索引的查询是老老实实做全表扫描——从第一行数据读到最后一行逐行匹配你的WHERE条件。数据量从1万涨到100万匹配次数就涨100倍性能当然跟着崩。拿订单表举例你要查WHERE user_id 10086表里有50万行MySQL只能从第1行翻到第50万行把每行的user_id都拿来做一次比较。听起来不算多但如果同时还要排序、再连接几张表响应时间就非常感人。加了索引之后效果是立竿见影的。我在一个真实项目里测过订单表30万行不带索引跑一次按user_id过滤大概需要1.8秒加一个普通索引后直接压到0.01秒左右。这个量级的差距不是调几个参数能追回来的就是从“要不要加索引”这个设计决策里来的。1.2 这一章的定位和学习路线从零起步学MySQL这个系列走到现在前面章节都属于“会用”的层面你会写INSERT、UPDATE、SELECT会调事务隔离级别知道锁的基本概念。但索引是第一个真正逼迫你理解数据库内部机制的知识点。它不只是一个语法问题——CREATE INDEX谁都会敲难点在于判断“这个表该建哪些索引”“这个SQL为什么没用上索引”“线上表索引太多怎么清理”。这一章适合已经有SQL基础、开始被性能问题困扰的读者。如果你还没入门我建议先把系列前面几章补完再过来。索引是优化器的核心依据连执行计划都看不懂就去加索引大概率是瞎忙活。2. B树到底是怎么把查询变快的2.1 索引的本质是数据结构不是“标记”很多初学者把索引理解成“给某个字段打个标记查询时就知道去哪找了”这个类比大方向没错但严重低估了背后的设计。真实的情况是索引是一种独立于数据之外的数据结构MySQL里绝大多数索引是一棵B树。B树长什么样你可以想象成一棵多叉的倒挂树最上层是根节点中间可能还有若干层内部节点最底层是一串叶子节点。把上面这些节点想成磁盘上的“页”InnoDB默认每页16KB。关键区别在于内部节点只存索引键和指向下一层节点的指针叶子节点才存真正的数据——在InnoDB里聚簇索引的叶子存的是完整数据行二级索引的叶子存的是主键值。查询的时候MySQL从根节点开始通过比较键值一层层往下走最终定位到叶子节点。因为每一层只需要读一个页所以IO次数约等于树的层高。这才是索引快的本质把“全表遍历”变成了“沿着树索引查找”复杂度从O(n)降到了O(log n)。2.2 为什么偏偏是B树而不是别的树你可能听说过B树、二叉树、哈希索引为什么InnoDB选B树核心原因有三个。第一树要矮。二叉树每个节点最多两个子节点数据一多树就很高查询时每层一次磁盘IO树越高IO越多。B树的每个节点能装几百上千个键三层树就能支撑千万级数据量。我习惯做个粗略估算一个16KB的页假设内部节点每个键值对占12字节大约能存下1300多个键叶子节点一行数据按1KB算大约16行。1300乘以1300再乘以16结果接近2700万。这意味着千万级数据量的表查询通常只需要两三次磁盘IO。第二B树内部节点也存数据B树不存。B树非叶子节点带了数据地址能放下的指针数量就少树就更高B树内部节点全部用来放键和指针单页容量被利用到极致。第三叶子节点用链表串起来。这个设计对范围查询非常友好比如WHERE id BETWEEN 100 AND 200定位到起点后顺着链表往后读就行。B树的范围查询需要反复从根节点回溯效率差很多。2.3 聚簇索引和二级索引的关系InnoDB里一个表必然有一个聚簇索引通常就是主键索引。它的叶子节点存放整行记录所以聚簇索引和数据实际上是“长在一起”的这也意味着每张表最多只能有一个聚簇索引。二级索引也叫辅助索引、普通索引、非聚簇索引就不一样了它的叶子节点只放索引列的值和主键值。你用二级索引查询时先找到主键再拿着主键去聚簇索引里找完整数据行这个动作叫回表。回表会额外增加IO。所以后面我会反复强调覆盖索引的思路——查询里需要的所有列都恰好包含在二级索引里那样就不需要回表性能提升非常可观。这个设计也解释了为什么我强烈不建议用UUID当主键聚簇索引的物理顺序跟主键相关UUID随机生成新插入的数据可能落在任意位置导致页分裂和碎片写入性能明显下滑。自增主键则是顺序追加新数据永远写在尾部页分裂的概率小得多。3. 索引类型怎么选主键、唯一、普通、前缀、全文3.1 主键索引和唯一索引的区别很多人在面试里被问过这个问题回答“主键不能为NULL唯一索引也不能有重复”这只是表面。我把两者的核心差异列个表大家对照着看维度主键索引唯一索引数量限制每张表最多一个一张表可以有多个存储本质InnoDB中的聚簇索引叶子存整行数据二级索引叶子存主键值查询常需要回表NULL值绝对不允许允许多个NULLNULL之间不冲突作用组织数据物理存储、快速寻址保证业务字段唯一性、辅助查询是否可删极不建议删除随时可删可加唯一索引允许多个NULL这个点很多人搞错。原因是MySQL认为两个NULL不相等所以UNIQUE约束只管非NULL值。如果一个业务字段需要“要么为空要么全局唯一”唯一索引是唯一靠谱的实现方式——纯靠应用层先SELECT再INSERT判断并发场景下几乎必然出现重复写入。我的习惯是每张业务表都放一个自增id BIGINT作为主键所有真正需要唯一性的业务字段比如手机号、订单号、身份证号单独建唯一索引。不要让主键承担过多的业务含义业务是会长变化的。3.2 普通索引、前缀索引和全文索引的实际选择普通索引没约束纯粹是为了加速查询语法也最简单CREATE INDEX idx_user_id ON orders(user_id)。前缀索引是个容易被忽视的技巧。如果索引列是很长的VARCHAR比如URL、邮箱、文章标题全字段索引会占用大量空间而索引页有限能塞进去的键就少树可能变高。前缀索引只取字段的前N个字符建索引空间省很多。代价是区分度可能下降。我建前缀索引前一定会先验证区分度比如SELECT COUNT(DISTINCT email) / COUNT(*) FROM user; SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) FROM user;如果LEFT(email, 10)的区分度已经接近全字段我就放心建idx_email(user(email(10)))。低于0.9就说明前缀太短容易扫出太多伪记录。全文索引主要用于大文本的模糊匹配比如LIKE %关键词%这种需求。说实话除非是简单站内搜索否则真不如上Elasticsearch这类专门的搜索引擎。入门阶段知道InnoDB支持全文索引就可以了日常业务优先用普通索引和联合索引。3.3 单列索引和联合索引该怎么取舍实际项目里一个查询往往涉及多个字段这就引出最关键的问题是给每个字段单独建索引还是建一个联合索引很多新人喜欢一股脑给每个WHERE字段都建索引结果索引建了一大堆性能没提上去反而让每次写入都背上沉重的更新负担。我一般在下一章单独讲联合索引的建法因为它太重要了值得花一整节篇幅。4. 联合索引where a and b 到底怎么建才不浪费4.1 单列索引相加不等于联合索引先看一个最常见的查询WHERE user_id 123 AND status 1。你会怎么建索引选项A是分别建idx_user_id(user_id)和idx_status(status)两个单列索引选项B是建一个联合索引idx_user_status(user_id, status)。A方案的问题在于两个独立的二级索引查询时要么只能先用其中一个索引定位拿到一批主键后再回表筛选另一个条件要么MySQL做Index Merge同时扫两个索引再取交集。不管哪种都意味着两棵B树都要遍历然后大量主键来回折腾。B方案一个联合索引就搞定先按user_id定位到一个小范围再在这个范围内精确匹配status一次索引遍历完成定位。我遇到过很多次线上慢查询最后排查下来就是把多个单列索引改成合理的联合索引性能直接翻倍。单列索引解决“多个条件”问题效率远不如一个精心设计的联合索引。4.2 最左前缀原则联合索引的底层逻辑联合索引idx_user_status(user_id, status)的B树第一排序键是user_id只有user_id相同的情况下才比较status。这个排序规则决定了它的一个核心约束——最左前缀原则。意思是WHERE user_id 123这个查询可以用索引WHERE user_id 123 AND status 1可以用索引但WHERE status 1不带动前面的user_id就没法走到索引了。因为B树的排序是先按user_id排列的只给status条件时你根本不知道去哪一层的哪个分支里找。同理索引(a, b, c)本质上建立了三个索引(a)、(a, b)、(a, b, c)。它覆盖了最左前缀的所有组合这就是为什么一个联合索引能解决好几类查询。4.3 字段顺序怎么定等值在前范围在后联合索引字段的排列顺序直接影响它能服务多少个查询。我常用的排序原则是把等值条件字段放在最前面比如user_id ?用等值定位可以快速收窄扫描范围。范围条件、、BETWEEN尽量放最后因为范围条件后面的字段无法再继续使用索引的有序性。区分度高的字段适当靠前减少每个前缀值下需要扫描的行数。最常用的条件组合优先设计索引是为了真实业务服务不是为了理论最优。举一个WHERE a 1 AND b 100 AND c 2的例子。如果你建的是(a, b, c)MySQL先用a等于值定位然后利用b的范围定位到c这里索引已经“使不上劲”了只能在扫描范围内逐行过滤c2。但如果改成(a, c, b)先a等值、再c等值精确定位最后只在剩下的小范围里处理b的条件。效果差很多。除此之外还有一个优化点如果某列几乎全部等值匹配却不经常出现在范围查询里把它放前面往往是更好的选择因为索引的“定位成本”更低。4.4 实战给订单查询建一组靠谱的索引假设有一个订单表核心查询是SELECT order_id, user_id, amount FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time DESC;第一步我会分析必须等值定位的字段是user_id和status需要排序的是create_time查询结果列是order_id, user_id, amount。建这样一条联合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);这样WHERE user_id ? AND status ?能直接定位到很小的范围create_time天然有序排序也省了。由于查询列不多如果需要进一步优化可以考虑把order_id、amount也放进索引形成覆盖索引下一章细说。但如果只有WHERE status 1 AND create_time ?这种不带user_id的场景这条索引的第一列就用不上你得再单独评估是否值得为status和create_time建另一条索引。这里也顺带回答一个很多人纠结的问题where a and ba和b到底谁放前面如果是两个等值条件通常把区分度高单位值对应的数据行少的那个放前面如果一个是等值一个是范围等值的放前面。别教条拿真实业务统计一下字段的基数再决定。5. 索引失效排查那些悄悄退化成全表扫描的SQL5.1 先说清楚“失效”到底是什么索引失效不是索引坏了而是优化器认为“你的查询走索引还不如全表扫描快”。第二种情况是SQL写法让B树根本无从下手——比如索引列被函数包裹树上的键值就失去了原本可以直接比较的形态。排查索引问题的时候容易被忽略的是优化器是会做选择的即便字段上有索引当它判断扫描比例太高比如一个字段90%的值都满足WHERE条件时也会选择全表扫描。我把最常见的失效场景整理成一张表方便大家对照排查场景示例原因建议对索引列使用函数WHERE DATE(create_time) 2024-01-01索引键值无法参与函数运算改成区间 2024-01-01 AND 2024-01-02对索引列做算术运算WHERE user_id 1 100同理变形后无法用树查找改成user_id 99隐式类型转换WHERE phone 13800001111varchar字段与数字比较字段被函数化参数带上引号前导模糊匹配LIKE %关键词B树按前缀排序无法从尾部定位改用LIKE 关键词%或全文索引OR连接非索引列WHERE idx_col 1 OR other_col 2MySQL需要合并两个条件的结果集改写为UNION或给两个列都建索引NOT IN、NOT NULL等负向查询WHERE id NOT IN (…)负向查询难以利用有序结构结合业务考虑改写必要时接受全表排序方向不一致ORDER BY a ASC, b DESC老版本索引方向无法匹配调整排序方向或等8.0降序索引5.2 函数包裹和隐式类型转换是最常见的坑我现场排过几次慢SQL最后几乎都发现是这两个原因。先说函数。你建了idx_create_time(create_time)索引但SQL写成WHERE DATE(create_time) 2024-01-01索引彻底失效。原因很简单B树里存的是原始时间戳而SQL想比较的是DATE函数处理后的值MySQL只能逐行计算函数结果再做比较。改法不是取消索引而是把SQL改写成一个区间查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;这样索引能直接利用值的范围做区间扫描。再看隐式类型转换。表里phone字段是VARCHAR搜索时传了数字13800001111。MySQL的规则是字符串和数字比较时把字符串转换成数字再比等价于对索引列调用了一次转换函数。同样失效。只要把参数写成13800001111就能恢复正常。5.3 like、or和null的三个易错细节LIKE abc%能用索引因为B树有序可以定位到第一个前缀为abc的记录然后一路向右扫描相当于一个 abc AND abd的区间。但LIKE %abc就没法定位了——前缀未知无从找起点。LIKE %abc%同理还会更糟。OR的问题需要特别注意。很多人以为给OR两边的列都建上索引就完事了有时候优化器确实会走Index Merge把两个索引结果合并但更多时候只要其中一边没有索引整个查询就放弃索引直接全表扫描。我通常建议把它改写成两个查询的UNION各自走各自索引条理清晰优化器也不纠结。NULL相关的坑是IS NULL在某些情况下可以用索引但IS NOT NULL很容易走全表。这里没有绝对答案要看优化器对数据分布的判断但作为经验业务设计能避免NULL就避免NULL尽量用DEFAULT 0或空字符串代替可空字段索引层面的收益立竿见影。5.4 学会用EXPLAIN读懂优化器的决定排查索引失效最快的工具就是EXPLAIN。EXPLAIN SELECT user_id, amount FROM orders WHERE user_id 123 AND status 1;重点关注四个字段。type表示访问类型按性能从好到差大致是system const eq_ref ref range index ALL。看到ALL就是全表扫描基本可以认定索引白建了。key显示实际用到的索引。rows是优化器预估要扫描的行数越小越好。Extra里如果出现Using filesort说明排序没走索引出现Using index则说明覆盖索引生效不需要回表。我优化慢SQL的习惯是先EXPLAIN看到ALL和Using filesort就想办法消灭它们消灭不了的再分析是不是优化器评估有偏差必要时用FORCE INDEX做临时验证但不要长期依赖强制索引。6. order by排序也能吃索引红利6.1 filesort和利用索引排序的原理ORDER BY是另一个性能黑洞。先记住一个结论如果结果集恰好能按索引键的顺序输出MySQL就不需要额外排序直接按序返回否则它会把查询结果加载进sort_buffer_size内存做排序内存不够还要落盘Extra里就会出现Using filesort。数据量小的时候Using filesort无所谓几十万行以上就非常痛苦。比如订单查询要WHERE user_id 123 ORDER BY create_time DESC如果你有联合索引(user_id, create_time)MySQL先定位user_id123的所有记录而它们天然按create_time排序这就不需要任何额外排序。这条SQL如果命中这种模式性能好得离谱。反例是如果你建的是(status, user_id)实际查询却是WHERE user_id ? ORDER BY create_time那排序字段create_time根本不在索引的连续位置MySQL只能把结果全捞出来自己排。6.2 覆盖索引一次查询不回表的魔法前面说过二级索引叶子存的是索引列和主键值。如果一个查询需要的所有列都包含在同一个二级索引里MySQL连回表都可以省掉直接从索引页读取全部结果Extra显示Using index。这就是覆盖索引。比如索引(user_id, status, create_time, order_id, amount)查询SELECT order_id, amount FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time;这条SQL的所有环节都贴合索引结构既不需要回表也不需要filesort。这就是调优的理想状态。6.3 少用select *是对索引用途的尊重很多人觉得SELECT *省事但在二级索引存在的情况下SELECT *几乎一定会回表。因为你把整行数据都查出来了二级索引提供的列满足不了需求。更尴尬的场景是本来查询只涉及两三个字段覆盖索引可以完美服务改成SELECT *后优化器一看回表代价太大干脆选择直接走聚簇索引全表扫描你用心的索引设计全部白费。所以我一直强烈建议生产环境的查询永远只SELECT需要的列这不仅是为了少传几个字节的网络流量更是为了给覆盖索引创造机会。读多写少的核心报表查询尤其值得这么设计。7. MySQL 8.0的索引新特性与相关维护7.1 降序索引和函数索引MySQL 8.0最让索引爱好者兴奋的更新就是降序索引和函数索引。老版本里索引列只能按ASC存储ORDER BY desc经常被迫走filesort。8.0起可以显式声明降序索引ALTER TABLE orders ADD INDEX idx_user_time_desc (user_id, create_time DESC);这样ORDER BY user_id ASC, create_time DESC如果满足最左前缀可以直接按索引顺序扫描连排序都省了。函数索引解决的是5.1节那个DATE()函数的坑。8.0.13之后你可以这样建索引ALTER TABLE orders ADD INDEX idx_created_day ((DATE(create_time)));查询里的WHERE DATE(create_time) 2024-01-01就能命中它。注意函数表达式外面多了一对括号这是MySQL要求的语法。7.2 不可见索引和索引跳跃扫描8.0的不可见索引对我这种要做索引治理的人来说是救命级功能。以前想删一个可能有用的索引只能先删掉观察线上性能出了问题再连夜加回来非常被动。现在可以先把索引设为不可见ALTER TABLE orders ALTER INDEX idx_user_status INVISIBLE;优化器默认不会使用不可见索引但索引本身还在随时可以改回VISIBLE。相当于给你一个“删除前的观察期”跑几天线上流量发现没影响再真正DROP这步操作在我的索引清理流程里已经是标配了。索引跳跃扫描Index Skip Scan解决的是另一个痛点联合索引第一列区分度低而查询里没带第一列。比如索引(gender, id)gender只有男和女两个值查询WHERE id 123。老版本的优化器会因为没带最左前缀直接放弃8.0从8.0.13起可以跳过gender把它当成两个小查询分别扫先扫gender1下id123再扫gender2下id123。跳过的值有多少查询次数就有多少。所以它只是特定条件下的补救机制不要指望它能完全替代合理的最左前缀设计。7.3 索引表空间与碎片处理很多人在运维中遇到过“数据删了一大堆表文件还是那么大”的困惑。这跟索引表空间直接相关。MySQL的InnoDB表在默认配置下innodb_file_per_tableON每张表对应一个.ibd文件数据页和索引页都存里面。DELETE操作并不会物理删除页里的数据只是把记录标记为删除页的空间让后续插入复用。大量增删改之后B树可能发生页分裂产生大量不连续的空洞索引查询的IO路径就越变越长。有人问我“索引表空间是不是单独的文件”答案是索引和数据在同一个表空间里但各有各的页。你可以用information_schema查看表大小和索引统计SELECT TABLE_NAME, INDEX_NAME, CARDINALITY FROM information_schema.STATISTICS WHERE TABLE_SCHEMA 你的库名;碎片整理用OPTIMIZE TABLE orders它会重建表和所有索引物理压缩页空间。注意这个操作在数据量大时很耗时且会锁表线上环境一定要挑业务低峰执行或者考虑用在线DDL方式谨慎操作。更轻量的做法是定期执行ANALYZE TABLE orders不整理物理碎片但会更新索引统计信息让优化器更准确地评估“走不走索引”很多索引选择错误的问题其实靠这一步就能缓解。8. 索引调优的经验清单最后我想把自己这几年做索引优化的几条实战经验整理成一个清单照着逐条核对大多数索引问题都能提前暴露。第一建索引先跑EXPLAIN验证。不要凭感觉设计索引建完立刻用真实业务SQL跑一遍EXPLAIN确认type不是ALLkey用的是你期待的索引Extra里没有Using filesort。第二索引数量要克制。一张表索引太多写入、更新时每一棵B树都要同步修改写入性能会迅速恶化。我通常建议单表索引控制在5到6个以内核心是通过联合索引覆盖多组业务查询而不是每个字段单建一个。第三定期清理冗余和重复索引。有(a, b, c)索引存在又单独建了(a)后者就是冗余。排查的时候用SHOW INDEX FROM 表名把前缀重叠的索引列出来用不可见索引观察后再删除。第四统计信息过期比索引失效更隐蔽。如果明明有索引优化器却走了全表先别怀疑索引坏了先执行ANALYZE TABLE刷新统计信息很多时候一条命令就解决了。第五覆盖索引是读多写少场景性价比最高的优化手段。对于报表类、查询密集的表把高频查询的SELECT列和WHERE条件一起设计进联合索引收益非常明显但也要接受这类索引占空间更多的事实。我在实际项目里见过太多把索引当成万能药的案例慢查询出现就加索引加到十几二十个后写入直接卡住。索引本质上是一个读性能和写性能的取舍要清醒地知道每次加索引背后付出的代价是什么也正因为如此我才把这一章定位成“深入理解”——你只有清楚索引的原理才知道什么时候该用它什么时候该放过它。