搞MySQL这些年最容易被低估的就是索引。很多人觉得索引不就是建几个字段、走个B树吗直到某天线上频繁爆出死锁或者一条本来毫秒级的查询突然把数据库拖垮才回过头来研究索引的底层行为。我上周排查了一单死锁最终定位到根因UPDATE语句走二级索引更新时InnoDB要先锁二级索引项再回表锁主键这个时间窗口内两条事务相互交叉形成了经典的锁等待闭环。最终问题解决了但整个过程让我意识到索引进阶不仅要会建索引还要理解索引的物理结构、加锁顺序和维护成本。这篇笔记不打算重复教科书里那些索引基础概念而是想从真实场景出发把我在线上实践中学到的索引知识串一遍为什么B树是默认选择、主键索引和二级索引到底差在哪、哪些情况下索引会失效且容易被忽略、一条UPDATE语句加锁的完整时序以及索引表空间和索引维护的隐性成本。内容偏进阶适合已经会用索引、但还想深入理解其内部运行机制的读者。1. 索引底层结构B树为什么能扛住千万级数据1.1 从一次全表扫描说起索引的本质是换先看一次典型的慢查询。表里有500万行记录执行一条WHERE status 1 AND create_time 2024-01-01如果没有任何索引InnoDB只能扫描聚簇索引的全部叶子节点也就是把整张表的所有数据页读一遍。假设每行数据平均1KB500万行大约5GB数据哪怕命中了InnoDB的buffer pool全表扫描的磁盘I/O也是灾难性的。索引其实就是拿额外的存储空间写入时的维护开销去换查询时的磁盘I/O次数。每建一个索引等于在磁盘上多生成一棵B树。查询可以顺着这棵树快速定位到目标数据而不是全表摸一遍。理解了这个交换逻辑你就明白为什么不能无脑给所有字段加索引——索引不是免费的它占用表空间还会拖慢INSERT和UPDATE。InnoDB默认的页大小是16KBB树每个节点都是一页。如果节点存储的是主键少量其他列一页能放下几百上千条索引记录所以一棵B树通常只需要3到4层就能支撑千万级数据量的索引查找。也就是说走索引查询时从根节点到叶子节点通常只需要3到4次磁盘I/O。这就是索引能大幅提升查询性能的核心原因。1.2 InnoDB聚簇索引与二级索引同一棵树两种叶子InnoDB的所有索引本质上都是B树但叶子节点存的东西不一样。聚簇索引通常就是主键索引的叶子节点直接存整行数据因此一旦你通过主键定位到记录无需再做额外操作当前页上就是完整的行数据。这张表的物理存储顺序也是按照聚簇索引键排序的。二级索引唯一索引、普通索引、联合索引都算二级的叶子节点只存索引列的值主键列的值。例如在age字段上建了二级索引那么这棵B树的叶子节点存的是一对(age, id)。查询到二级索引记录后如果需要的列不全在这棵索引树上就必须拿着主键值再回到聚簇索引树里查一次这个动作就叫回表。回表是要付出实际代价的。每次回表都相当于一次额外的B树随机点查。更关键的是回表次数越多性能就越不可控。如果一次查询扫描了1万条二级索引记录那就意味着要做1万次回表这在很多场景下比全表扫描还要慢。所以优化器在决定是否走某个二级索引时会悄悄估算回表成本估出来的成本高于全表扫描它就会弃用索引。这一点在第二部分还会展开讲。1.3 主键索引与唯一索引相似但绝不能混用主键索引和唯一索引看起来都要求值不重复但它们在InnoDB里地位完全不同对比项主键索引唯一索引数量限制每张表最多一个每张表可以有多个是否允许NULL不允许允许且NULL可以重复索引性质聚簇索引叶子存整行二级索引叶子存主键值插入顺序决定物理存储顺序不决定物理顺序等值查找直接定位行无需回表需要回表才能拿到数据实际排查线上问题时我看到有人把唯一索引当主键用结果表没有聚簇索引只能额外生成隐藏主键整个表的行物理排列变得不可预测范围查询性能明显下降。反过来也有人给一个业务上根本不要求唯一的字段强行建唯一索引导致一点小并发就报Duplicate entry错误。这里有一条实用原则能用主键就用主键唯一索引只加给业务上确实需要唯一约束的字段。2. 索引失效排查实录优化器为何拒绝你的索引2.1 三个线上常踩的失效场景网上流传的索引失效十法则大多在新手期有参考价值但很多列举并不准确甚至不同版本MySQL表现都不一样。我分享几个自己真正在线上遇到过、并且反复确认过的场景。第一个是对索引列使用函数或运算。例如在WHERE DATE(create_time) 2024-01-05这种写法里即使create_time上有索引也无法正常走索引。原因是优化器不会自动把函数处理后的结果还原成区间匹配B树里存的是原始值无法直接按函数后的结果搜索。真正的解法是改写为create_time 2024-01-05 00:00:00 AND create_time 2024-01-06 00:00:00让索引能按范围扫描。第二个是隐式类型转换。如果一个varchar类型的列上建了索引但SQL里用数值与其比较比如WHERE phone 13812345678MySQL会先把varchar列转换成数值类型再比较等于对索引列应用了隐式函数索引照样失效。排查方法就是核对表结构和SQL字面量建议字符串条件都加上引号。第三个是跳过了联合索引的前导列。联合索引(a, b, c)在结构上是先按a排序再按b排序再按c排序。当你单独查b或c时B树无法直接定位因为全局有序是在a相同的前提下才成立。所以WHERE b 1基本走不了这个联合索引。这也是最左前缀原则背后的物理原因。2.2 NULL值、OR条件与优化的真实逻辑有一个被过度简化说法是IS NULL会走索引IS NOT NULL不会。实际上是否走索引取决于字段的可空性、索引设计以及优化器对成本的估算。比如在普通索引上IS NULL经常能走索引因为InnoDB里NULL值在索引中会聚集在一起但如果表里绝大多数行都是NULL优化器扫索引占比太大照样会弃用索引选择全表扫描。IS NOT NULL不一定非走全表如果索引覆盖率高并且行数少优化器也可能选择索引扫描后回表。OR条件要注意的点也容易被误解。WHERE name a OR age 20如果name有索引、age没有索引MySQL通常不会拆成两个子查询来分别取并集而是直接选择全表扫描。因为索引只能解决其中一个分支另一个分支还是要全表优化器算下来总成本可能更高。真正想优化得拆成UNION或者确保OR两边的列都建了合适的索引。优化器判断是否使用索引本质上是在对比三条路线的成本全表扫描成本、索引回表成本和预期I/O次数。这由三个因素决定索引的基数不同值数量、分布直方图和统计信息采样。当你快速频繁更新大量数据ANALYZE TABLE对统计信息表进行更新后原本慢的查询可能自动走上索引这就是某些玄学变快的真相。2.3 用EXPLAIN验证索引选择的要点排查索引失效最快的方法是EXPLAIN。我习惯先看几列内容而不是整张输出表。首先是type列从const到ref再到range、index、ALL效率依次递减。其中ALL出现时需要重点确认是否因为没有可用索引。然后是key列显示实际走的是哪个索引如果这里为NULL则基本就是全表。再看rows这是优化器预估扫描的行数和实际行数差异过大时说明统计信息可能过期。还有Extra列值得留意的是Using where和Using index。Using index代表查询在二级索引树上就拿到了所有需要的字段也就是覆盖索引这是效率最高的情况没有回表。Using index condition则是索引下推在二级索引层做一部分条件过滤仍然可能回表。看到Using temporary或Using filesort也要警觉说明排序或去重在临时表里完成通常意味着索引设计没有兼顾排序需求。3. 二级索引更新时的时间窗口锁交叉引发的死锁案例3.1 一条UPDATE语句究竟要锁几次了解了索引结构接下来是很多进阶同学真正容易栽跟头的地方更新数据时的加锁行为。以UPDATE user SET name new WHERE age 20为例假设age上有唯一二级索引目标记录的主键是id 100。InnoDB执行这条语句的锁定过程大致是在age二级索引B树中定位到(20, 100)这条二级索引记录对该记录加锁回表到主键索引定位到id 100的聚簇索引记录对主键记录再加锁如果age是唯一索引期间还要检查是否存在其他冲突的重复值如果age是普通索引且命中了多条记录那么这个锁二级索引项回表锁主键的动作会重复多次。关键在于第1步和第2步之间存在一个微小的时间窗口此刻二级索引记录已经被当前事务锁定但对应的主键记录还没有被锁定。如果第2步发生阻塞比如主键记录正被其他事务持有锁那么当前事务需要等待在等待期间它对二级索引项的锁不会释放。这个先锁二级索引项再回表锁主键的固定时序是理解后续死锁的核心。3.2 交叉锁产生的完整路径现在看一个我实际遇到过的死锁案例。表结构CREATE TABLE user ( id int PRIMARY KEY, age int NOT NULL, name varchar(20), UNIQUE KEY uk_age (age) ) ENGINEInnoDB;事务A先执行UPDATE user SET name a WHERE age 20;执行过程中它锁住了二级索引记录(20, 1)并准备回表锁主键记录id1。事务B同时执行UPDATE user SET name b WHERE age 30;它锁住了二级索引记录(30, 3)并准备回表锁主键记录id3。到这里一切正常。但如果A在稍后又执行一条UPDATE user SET name a2 WHERE age 30;A需要先锁二级索引记录(30, 3)发现被B持有进入等待。而B在稍后也要执行UPDATE user SET name b2 WHERE age 20;B需要先锁二级索引记录(20, 1)发现被A持有同样进入等待。两个事务互相等待对方释放锁InnoDB检测到死锁后选择回滚牺牲较小的那个事务。这类死锁有几个共同特征都发生在同一事务内更新多条不同的二级索引记录加锁顺序是二级索引项在前、主键回表在后不同事务对二级索引的锁定顺序恰好相反只要每个事务都按相同顺序更新记录交叉等待就不会出现。我在实践中发现许多项目中批量更新代码是循环遍历数据集逐条发送UPDATE数据排序来自应用层某个map或list顺序不可控这成了死锁的重灾区。3.3 规避死锁的实战姿势掌握加锁时序之后规避思路就清楚了。最直接的方法是批量更新时先按主键或某个唯一键把记录统一排序再按顺序执行UPDATE。所有事务都按id从小到大更新锁获取顺序全局一致交叉锁自然消失。对于无法排序的业务场景可以尝试把批量更新拆小每批只更新少量记录缩小事务持有锁的时长。另一个有效手段是走主键索引直接更新。如果UPDATE语句的WHERE条件能改成id IN (...)InnoDB先锁定的是聚簇索引记录不需要经历锁二级索引项再回表的时序拆解锁环路径明显缩短。当然这要求业务上能拿到主键列表不能为了规避死锁而乱改查询条件。遇到线上死锁也别慌一般通过SHOW ENGINE INNODB STATUS查看LATEST DETECTED DEADLOCK段里面会显示两个事务当前持有哪些锁、等待哪些锁。结合代码看加锁顺序基本都能定位到是二级索引回表时序导致的交叉。最后还有一个治本思路如果唯一索引或普通索引本身不需要支撑更新操作的高并发可以考虑去掉或改用普通索引配业务侧校验从而减少一把锁住二级索引记录的粒度。4. 索引表空间与维护成本建索引不只是建个目录4.1 索引表空间如何消耗磁盘每个索引都对应一棵独立的B树这棵树占用物理存储空间。在InnoDB中数据和索引默认存储在名为ibdata1的共享表空间或者每个表独立的.ibd文件中。独立表空间更便于管理和收缩也是社区推荐的做法。主键索引的数据页占主导二级索引的页数量往往被低估。举一个真实估算的例子一张1000万行的表主键是bigint8字节有一个namevarchar(64)的二级索引。仅计算二级索引叶子节点每条索引记录大约包含name假设平均40字节、主键8字节以及事务ID、回滚指针等内隐藏列粗略按50-60字节算。如果一个16KB的页能存下约280条索引记录那么叶子节点大约需要3.6万个页换算成空间大约是接近600MB。一个看似简单的二级索引磁盘开销就超过了很多人的预期。如果建五六个索引磁盘占用甚至可能超过数据本身。所以建索引前要想清楚这个查询是高频热点吗是不是可以在多个相近查询之间复用同一个联合索引避免每个查询各建一个索引结果加在一起形成庞大体量的索引表空间拖累写入性能。4.2 索引碎片与维护操作的取舍索引页频繁删除和更新会产生碎片。举个例子一张表按主键做随机删除聚簇索引的叶子页逐渐出现大量空闲空间B树的页利用率下降扫描效率也随之下降。二级索引同样可能因为随机插入和更新产生页分裂、页合并的情况。我看到不少团队定期执行OPTIMIZE TABLE但不是每次都划算。大表重建期间会产生长时间锁表业务高峰期执行基本等于自杀。更稳妥的方案是评估碎片率后再决定是否重建。评估方式可以间接通过information_schema.TABLES中的DATA_FREE字段或者参考information_schema.STATISTICS中的CARDINALITY与实际行数的比值。碎片率明显偏高且表还在持续膨胀时才安排低峰期执行。同时ALTER TABLE ... ENGINEInnoDB也能重建表效果等同OPTIMIZE但可以配合在维护窗口执行。做完之后观察表的DATA_FREE是否大幅下降再确认查询性能是否回升。如果表一直频繁增删改碎片会反复产生做一次重建并非一劳永逸不如从源头优化写入模式比如减少无关索引的数量。5. 索引设计实操从慢查询日志到最优索引5.1 慢查询日志定位问题SQL我每接手一个优化任务第一步永远是看慢查询日志而不是拍脑袋猜哪个SQL慢。打开slow_query_log、设置long_query_time 1跑一段时间后聚焦两类日志一类是出现频率高但单次耗时不太高的SQL这类是业务访问热点优化单条查询收益最大另一类是单次耗时极高的慢SQL通常是缺乏索引或者索引失效导致全表扫描。拿到慢SQL后用EXPLAIN看执行计划尤其注意是否出现ALL类型扫描、Using filesort、Using temporary。如果确认是缺索引再结合表结构和查询条件设计索引。这里我反复吃亏之后沉淀了一条思路优先保证等值查询和范围查询能用上索引再考虑排序和分组。排序也是热搜里经常出现的点。ORDER BY create_time DESC LIMIT 20这类场景中如果create_time没有索引MySQL需要把满足条件的数据找出来放到临时文件里排序即Using filesort。当create_time恰好是联合索引的一部分且排序方向与索引顺序一致时优化器能直接按索引顺序取数性能是数量级上的差距。5.2 联合索引设计区分度、最左前缀与覆盖索引联合索引是索引设计的重心。两个核心原则一是区分度高的字段放前面二是尽量利用覆盖索引减少回表。区分度是某列不同值的数量占总行数的比例。比如gender只有两个值区分度极低放在联合索引最前面会让后续字段难以发挥排序优势。而user_id区分度高放在前面会让等值条件快速定位到很小的范围。例如查询是WHERE user_id 1 AND status 0 ORDER BY create_time DESC设计(user_id, status, create_time)比(status, user_id, create_time)更合理因为前者的等值条件可以在第一列就过滤出目标用户的数据。覆盖索引则是一个容易被提高性能的小技巧。如果查询需要的所有字段都包含在某个二级索引的索引列和主键列里那么InnoDB甚至不需要回表。例如一张表有id,name,age三个字段在name上建索引查询SELECT id, name FROM user WHERE name Tom时二级索引叶子节点里已经包含name和id直接返回即可Extra列显示Using index。反过来如果查询再要求age就必须回表取整行数据。5.3 清理冗余索引的评估表线上系统建了几十个索引的情况非常常见其中不少是冗余的。比如已经存在联合索引(a, b)再单独建一个(a)就是典型冗余因为前者的前缀部分完全覆盖了后者。评估冗余索引时可以做一个简易对照现有索引被冗余覆盖的查询是否冗余建议(a, b)WHERE a ?冗余删除单独索引(a)(a, b)WHERE b ?不冗余保留或考虑加(b)(a, b, c)WHERE a ? AND b ?冗余删除(a,b)等前缀索引(a, b, c)WHERE a ? AND c ?视情况b被跳过最左前缀失效可能需单独(c)清理索引之前建议先基于慢查询日志确认哪些索引一段时间内从未被使用。可以从performance_schema.table_io_waits_summary_by_index_usage或统计sys.schema_unused_indexes视图查出完全未使用的索引。确认无引用后在低峰期删除。每删一个索引不仅释放磁盘空间还减少了UPDATE、INSERT每次写操作需要同步维护的索引树数量。我之前接手过一张表原本有8个索引实际业务查询只有4个热点经过评估删掉4个冗余索引后写入性能提升了约30%磁盘占用也明显下降。索引数量的边际成本往往在业务增长后才体现出来越早做瘦身越划算。写在最后的实践心得索引进阶这条路我最深的体会是索引本身就是一种权衡。你给查询加的每一层速度都要在写入和存储上付出利息。建索引之前问三句话——这个查询是否高频这个索引是否能覆盖足够多的查询变体这个索引的维护成本是否负担得起把问题前置比事后再调优要高效得多。另外一定要重视死锁排查中暴露出的索引与锁的交互。二级索引更新时先锁二级索引项再回表锁主键的时间窗口是真实存在且会引发交叉锁的很多人直到线上死锁爆掉才第一次听说这个机制。如果你也在维护高并发事务型系统建议把新增索引纳入技术评审让参与事务编码的同学都理解这条锁时序而不是等到死锁日志堆满再被动加班。