先交代背景这篇文章的读者画像我大概画了三类——刚把MySQL环境装好、正在啃mysql安装配置教程的新手写过几年SQL、但一说索引失效就支支吾吾的业务开发以及准备mysql面试题、想系统捋一遍索引知识点的求职者。如果你属于其中任何一类这篇文章都能帮上忙。我尽量把B树的数据结构、InnoDB的物理存储、联合索引的设计取舍、索引失效的典型场景一次讲透再附上我这些年踩过的坑和排查工具的使用习惯。1. 为什么索引选择了B树从数据结构的取舍讲起1.1 哈希、数组和树的各自短板先说结论MySQL里InnoDB存储引擎的默认索引结构是B树这不是拍脑袋选的而是被磁盘I/O的现实逼出来的。哈希索引的等值查询确实是极致速度O(1)复杂度但问题也很明显不支持范围查询、不支持排序WHERE id 100这种条件在哈希索引里只能全表扫。而且哈希冲突后链表一长性能照样跳水。你日常SQL里有多少是只做等值查询的大概率是少数。数组倒是支持二分查找范围查询也能做但数组的插入和删除是O(n)级别的——中间插一条记录后面的元素全部要挪位置。一张千万级表每次插入都挪数组CPU和磁盘都会当场翻脸。二叉搜索树在极端情况下会退化成链表AVL树和红黑树虽然解决了平衡问题但树高还是比较可观。假如一棵树高20查一次要访问20个节点每个节点都是一次磁盘I/O20次随机I/O的时间成本直接到毫秒级甚至十毫秒级——对于一次简单查询来说这不可接受。B树解决了多叉和高度问题但它有一个关键缺陷每个节点既存索引键又存数据。一旦一行的数据很大比如几百字节的多个字段一个节点能装入的键数量就急剧减少树被迫长高。而B树把数据和键分开存让非叶子节点能塞下更多键整体树更矮更胖。B树的具体设计下面拆开讲。1.2 B树的结构拆解非叶子节点和叶子节点各干各的事B树有两个核心特征理解这两个特征你就不用死记硬背了非叶子节点只存索引键不存数据它的作用就是“导航”。一个节点能稳定容纳几百个键树的高度就控制住了。叶子节点既存索引键也存数据指针并且所有叶子节点通过双向链表串联按索引键大小有序排列。这两点设计的收益非常直接第一因为叶子节点有序且双向连接范围查询和排序的代价极低。比如查id BETWEEN 100 AND 200B树先定位到id100那个叶子节点然后顺着链表往后扫就行。而B树如果要范围查询得走中序遍历频繁在父子节点之间跳来跳去磁盘随机读的数量会明显增加。第二因为非叶子节点只放键磁盘I/O次数少。我说个直观数字InnoDB默认一个页16KB假设索引键是8字节bigint指针是6字节一个非叶子节点大约能存16 * 1024 / (8 6) ≈ 1170个键。也就是说两层非叶子节点约1170 * 1170就能索引到一百多万个叶子节点配合叶子节点本身轻松支撑千万级甚至亿级数据量的查询。MySQL内部访问B树是从根节点往下走每一层对应一次磁盘I/O。树高3层一次查询最多3次I/O数据量千万级的表在加了索引后能做到几十毫秒内返回靠的就是这个“矮胖”结构。注意真实磁盘I/O是页级别的随机读所以B树的树高直接决定了一次索引查找的I/O次数。这也是为什么很多数据库调优文章反复提到“减少回表、利用覆盖索引”——本质就是减少磁盘I/O。1.3 一个16KB页能装多少数据三层B树撑起2000万行我当年学B树印象最深的就是自己动手算了一次“单个页容量”。用这个例子比背一百遍概念都管用。假设你的主键是bigint占8字节加上指针6字节一个非叶子节点的键指针条目是14字节。一个16KB页存放的键数量约为16 * 1024 / 14 ≈ 1170所以一层非叶子节点大约可指向1170个叶子页或下层页。三层B树的结构是根节点存储约1170个键每个键指向第二层第二层每个节点又有约1170个键指向第三层第三层就是叶子节点每个叶子节点按行存放数据。如果一行数据平均1KB一个叶子页可以放16行。那么三层B树能支撑的数据总量大约是1170 * 1170 * 16 ≈ 2190万行也就是说一张2000万行左右的表只要主键索引合理查找任意一行最多只需要3次磁盘I/O。这个数据量对于绝大多数业务系统来说已经完全够用。这也是为什么我经常建议技术团队单表数据量没到千万级别时优先考虑优化索引结构而不是一上来就分库分表。很多时候一条SQL慢不是表太大而是索引根本没用对。2. 聚簇索引与二级索引InnoDB的物理存储逻辑2.1 聚簇索引表数据就是主键索引的叶子节点InnoDB的索引和MyISAM有一个本质区别这段几乎每次面试都在考InnoDB中表数据本身就是按主键构建的B树主键索引的叶子节点直接存储整行记录。这个结构叫聚簇索引。什么叫“聚簇”就是数据行跟索引键物理地聚在一起。一张InnoDB表一定有一个聚簇索引。如果你没有显式定义主键InnoDB会找一个非空唯一索引充当再没有就用隐藏的rowid。聚簇索引带来了两个实实在在的好处按主键范围查询时由于数据物理连续顺序读性能极好。二级索引只需存储主键值作为兜底指针当发生页分裂、行移动时二级索引不需要跟着变化减少了维护成本。同时也有一个明显的坑随机主键写入会导致频繁页分裂和页重排。比如用UUID做主键新行的主键大小随机B树为了维持有序可能要把某个叶子页一分为二再调整相邻页的顺序写入放大非常严重。实测下来UUID主键的高并发insert吞吐量可能比自增主键低一个数量级都不奇怪。2.2 二级索引与回表为什么推荐覆盖索引除了聚簇索引其他索引都叫二级索引也叫辅助索引。二级索引的叶子节点不存整行数据而是存两样东西索引键的值聚簇索引键通常是主键的值。用一条SQL说明SELECT * FROM user WHERE name 张三;假设name上有普通索引执行过程是在name索引的B树中先按照name张三定位到叶子节点拿到主键id。再用这个主键id去聚簇索引主键索引里查一次才能取到完整行记录。第2步就是“回表”。回表次数少还好一旦命中的行数很多比如name匹配了1000行就要回表1000次每次都是一次随机I/O性能急剧下坠。所以就有了覆盖索引这个优化点如果你的查询列全部包含在二级索引里就不需要回表SQL优化器会直接从二级索引拿到全部需要的值。比如SELECT name, age FROM user WHERE name 张三;如果(name, age)是一个联合索引这条语句直接走覆盖索引Extra里会显示Using index不会回表。这就是我为什么一直强调业务SQL里禁止无脑SELECT *。改字段列表让二级索引覆盖到查询列可能是最简单的“免费”性能优化。2.3 主键自增还是UUID随机插入引发的页分裂问题页分裂这事值得单独拉一节出来说因为它既影响B树索引的底层稳定性也影响实际写入性能。B树的叶子节点是按索引键值有序排列的。当你插入一条记录它的主键大小如果落在两个已有键值之间而对应页又满了InnoDB就需要申请新页把原来页的一部分数据挪过去同时调整前后指针。这个动作就是页分裂。自增主键几乎完美避开了这个问题新记录的主键总是大于已有的所有主键新数据只往最右侧的叶子节点追加其他页完全不受影响。这也是为什么绝大多数业务表我都劝你用自增主键。UUID主键则是页分裂的重灾区。新主键值是随机的可能落在B树的任意位置导致某个中间页频繁分裂查询路径上的页不断被修改缓存命中率下滑写入放大严重。如果你确实需要全局唯一的业务主键建议用自增主键当内部逻辑主键业务唯一键单独加唯一索引。这样既保证了聚簇索引的写入顺序性又满足了业务约束。实操心得我曾经把一张订单表的业务单号很长的一串字符串直接设成主键结果发现高并发写入时InnoDB的redo log大量刷盘ALERT日志全是页分裂相关等待。后来改成自增主键业务单号唯一索引写入吞吐直接翻倍。3. 联合索引设计实战最左前缀与索引下推3.1 联合索引底层的排序规则联合索引是实战中高频使用的索引类型也是面试问得最细的一块。很多人知道“最左前缀原则”但不知道为什么。要理解它得先明白联合索引在B树中的排序方式。假设在(a, b)上建立联合索引B树的排序规则是先按a排序a相同再按b排序。也就是整个索引树的键是(a, b)这个二元组比较大小的时候先比aa相等才比b。这意味着什么意味着WHERE a 1 AND b 2能直接用索引因为索引键值有序WHERE a 1也能用因为只对a做匹配时逻辑上相当于用前缀(1, 任意)去定位但WHERE b 2就很难用上这个联合索引——因为b单独并不保证全局有序优化器在索引树里找一个b值得遍历大量范围才能确定还不如全表扫。所以最左前缀原则的本质就是联合索引的B树是给从左到右的字段组合排序的跳过了左字段右侧字段的顺序就失去了意义。3.2 最左前缀原则和范围查询右边字段失效的原因再深入一步解释“范围查询右边的列会失效”。看这条SQLWHERE a 1 AND b 100 AND c 3假设联合索引是(a, b, c)。执行时a 1是等值匹配继续走b 100是范围匹配可以继续定位到b100这个边界但b的范围条件判断完成之后c字段在索引中无法继续参与过滤了。原因还是排序规则索引在a相同的前提下先按b排序b相同才按c排序。当b 100这个范围无法确定一个唯一区间时索引树无法直接告诉MySQL“c3”的记录在哪一页只能把b在范围里的所有记录都捞出来再去过滤c。这不是Bug而是B树有序性的必然推论。所以设计联合索引字段顺序时有一条经典原则先把等值条件的字段放在前面范围条件的字段放在后面这样可以最大化利用索引键的过滤能力。3.3 索引下推ICP减少回表次数的隐藏优化索引下推Index Condition PushdownICP是MySQL 5.6引入的优化很多搞了好几年开发的人都说不清楚。它解决的是这样一个问题联合索引(name, age)SQL条件SELECT * FROM user WHERE name LIKE 张% AND age 20;在ICP出现之前MySQL的处理流程是先用name LIKE 张%在索引树定位到符合前缀的叶子节点拿到一批主键然后逐条回表再在完整行上判断age 20。回表行的很多其实是用不上的。开启ICP后MySQL会把age 20这个属于索引列的条件直接下推到存储引擎存储在索引叶子节点上的age值不需要回表就能判断。只有完整满足索引条件的记录才回表回表次数大幅减少。用大白话说ICP就是“在索引这棵树上先帮我们过滤掉一部分再回表”。ICP不是所有场景都生效经验上有几个条件只适用于二级索引聚簇索引本身是整行数据没有“回表”可言。下推的条件必须是索引包含的列。包含了范围条件、LIKE前缀匹配等不会额外改变索引定位逻辑的条件ICP才有收益。想知道是否走了ICP看EXPLAIN里Extra列是否出现Using index condition。如果你建了联合索引发现大部分查询还是Using where那很可能索引字段顺序设计有问题或者ICP没触发。注意ICP不是万能药。它降低了回表数量但过滤本身依然消耗CPU。索引设计的关键still是从源头减少扫到的记录数量而不是把希望全押在ICP上。3.4 区分度、冗余索引和索引数量控制建索引有一个让人纠结的问题是到底给哪些列建索引我一般先算“区分度”也就是某列的不同值占总行数的比例SELECT COUNT(DISTINCT col) / COUNT(*) FROM user;区分度越接近1说明这个列的值越不重复索引过滤能力越强。区分度低的列比如gender、status这种值就那么几个的即使建了索引优化器也可能觉得“走索引回表”性价比还不如直接全表扫。冗余索引也是常见问题。比如你已经建了(a, b)联合索引又单独建了一个a索引。此时a索引完全多余因为(a, b)索引在最左前缀规则下已经能覆盖所有只用a的查询条件。冗余索引的影响不只是占用空间更大的问题是写放大每次插入、更新、删除都要维护所有索引的B树结构索引越多写入越慢。对业务系统来说一个表三五个索引通常是合理范围超过七八个就要警惕了。实操心得排查冗余索引最笨但有效的方法是把SHOW INDEX FROM table的字段列出来按照联合索引最左前缀规则人工比对。也可以借助sys.schema_unused_indexes视图查看哪些索引从未被使用直接drop。4. 索引失效的常见场景与EXPLAIN排查实录4.1 七种典型的索引失效场景这一段是我在各类mysql索引话题下被追问最多的内容。结合个人经验我为常见的索引失效场景整理一个速查清单建议收藏场景示例失效原因对索引列使用函数WHERE LEFT(name, 3) 张函数破坏了索引键的原始顺序隐式类型转换WHERE phone 13800138000phone为varcharMySQL把varchar列转数字相当于在列上套函数前导模糊匹配WHERE name LIKE %张%无法从索引树确定起始位置OR连接非索引列WHERE id 1 OR status 2无法只走索引需要扫描全表再合并联合索引跳列索引(a,b)条件WHERE b 1违反最左前缀原则优化器判断全表更快低区分度字段回表代价高于全表扫描NOT IN / ! 在某些场景WHERE status ! 1类似低区分度优化器选择扫描这里有个反直觉的点MySQL不是所有时候都想走索引。当分布数据显示一个字段80%都是同一个值时优化器会放弃索引选择全表扫描因为回表代价实在太高。所以“索引失效”不一定是SQL写错了也可能是优化器基于统计信息做出的“理性决策”。4.2 EXPLAIN执行计划怎么读排查SQL是否走索引EXPLAIN是最常用的工具。我不打算贴一堆文档只挑几个关键字段讲。type字段从好到差一般有这些级别const按主键或唯一索引等值查询最快eq_ref被驱动表通过唯一索引等值匹配常见于joinref非唯一索引等值匹配range索引范围扫描比如、、BETWEENindex全索引扫描比全表好一点但也不是理想状态ALL全表扫描通常意味着没走上索引key字段实际用到的索引名。如果值是NULL说明这条SQL没用到任何索引。rows字段优化器估算需要扫描的行数。值越小越好。Extra字段信息量很大。看到Using index是覆盖索引好事看到Using index condition是索引下推看到Using where说明存储引擎返回后的过滤还在进行看到Using filesort意味着额外排序如果排序字段没建索引就容易在这里翻车。实战中我习惯先看type和key再看Extra。如果type到了ALL第一反应就是SQL条件或者索引设计出了问题再细化排查。4.3 实战排查案例一个订单查询从2秒到20ms分享一个真实的排查过程涉及索引优化的完整链路能帮你建立具体印象。背景是电商订单表order_table数据量约800万行。线上有一个查询SELECT order_no, user_id, amount, create_time FROM order_table WHERE status 1 AND create_time 2024-01-01 AND create_time 2024-02-01 ORDER BY create_time DESC LIMIT 20;最开始的create_time上有单列索引但查询平均耗时2秒。先用EXPLAIN看发现type是refkey是idx_create_time但Extra里出现了Using where; Using filesort。也就是说走了时间的范围索引后还要对结果做一次额外排序范围定位本身是无序的整体性能自然上不去。后面我把索引改成联合索引(status, create_time)SQL基本不变EXPLAIN SELECT order_no, user_id, amount, create_time FROM order_table WHERE status 1 AND create_time 2024-01-01 AND create_time 2024-02-01 ORDER BY create_time DESC;执行计划里type变成rangekey是idx_status_create_timeExtra里的Using filesort消失了因为联合索引天然按create_time有序排序可以直接走索引顺序。这条线的背后逻辑是status 1把数据范围缩小到极小一部分在(status, create_time)索引中先等值匹配status再对create_time做范围此时create_time的有序性刚好被索引利用省去了一次昂贵的文件排序。上线后这条接口P95耗时从2秒降到了20ms左右。操作禁忌改索引前先在测试库用EXPLAIN跑一遍完整执行计划确认rows估算和Extra的变化再上生产。索引变更对写入性能有负面影响要评估业务低峰执行。5. 避坑指南面试高频与日常误用的细节5.1 隐式类型转换为何让索引失效先解释一个最常见的坑WHERE phone 13800138000phone列是varchar类型。MySQL做比较时如果两端类型不一致就会发生隐式转换。在这个例子里MySQL的策略是把字符串列转换成数字再比较。等价于WHERE CAST(phone AS SIGNED) 13800138000前面说过对索引列使用函数会导致索引失效。CAST(phone AS SIGNED)就是对索引列做了运算索引键的原始顺序字符串字典序被打乱B树没法按原来的键顺序定位数据只能全表计算后匹配。正确处理方式是在SQL里传字符串让类型匹配WHERE phone 13800138000这个场景排查起来也比较简单看到EXPLAIN里type是ALL并且字段类型是varchar条件值写法却是数字基本就是隐式转换。5.2 如何通过key_len判断联合索引用到了几列EXPLAIN输出里的key_len字段代表索引使用的字节数很多人忽略它其实它是判断联合索引到底用了几列的黄金工具。手动计算key_len的基本规则字符集utf8mb4下varchar(n)占4 * n 2字节2字节存长度字符集utf8下varchar(n)占3 * n 2字节数字类型int占4字节bigint占8字节列允许为NULL时加1字节举个例子联合索引(status, create_time)status是tinyint1字节非NULLcreate_time是datetime8字节那么理论上key_len 1 8 9。如果EXPLAIN显示key_len是1说明只用了status列如果是9说明用完了整根索引。当联合索引无法完全被利用时比如范围条件右边的列没有用上key_len会停留在前几列的长度这对排查SQL执行计划非常直观。5.3 InnoDB索引层面做点什么提升写入性能索引能加速读取但同样拖慢写入。这是一个不能回避的trade-off。对于写多读少的表我有三个方向性建议减少二级索引数量。每次INSERT要在所有二级索引树上插入对应记录索引越多延迟越高。那些“也许以后用得上”的索引趁早删掉。让主键顺序写入。使用自增主键或类似递增方案避免页分裂和缓冲池频繁换页。关注innodb_buffer_pool_size。索引树的节点一旦无法放入buffer pool就会频繁产生磁盘I/O。对于纯索引类操作buffer pool的命中率几乎决定一切。说一个我的实测经验一个日志表原来有5个二级索引写入峰值时每秒只能抗2000条删除3个冗余索引后写入峰值提升到6000条以上。而业务查询通过覆盖索引和定时汇总表性能几乎没受影响。5.4 我平时建索引的checklist压轴给出一份我日常常用的建索引checklist每一条背后都有踩坑记录区分度不达标的字段不要建索引目标是COUNT(DISTINCT col)/COUNT(*)大于0.1经验值不是硬性标准。联合索引字段顺序按“等值优先、范围靠后”排列并参考SQL里的高频查询模式。避免冗余索引已存在(a,b)时不再单独建a索引。尽可能让查询列被覆盖索引包住禁止无脑SELECT *。主键优先使用自增整数大字段字符串不要当主键。控制每张表的索引数量一般不超过5个写密集的表更少。上线前用EXPLAIN验证计划看type、key、key_len和Extra别只在开发环境看“能不能跑通”。定期用慢查询日志巡检long_query_time设置为1秒每周拉一次慢SQL清单看到rows大、typeALL的记录立即排查。这套checklist帮我在多个项目里避免了很多不必要的“慢SQL事故”。它不是银弹但可以让索引设计从“凭感觉”变成“可验证”。最后再分享一个小技巧排查索引问题时别只盯着单条SQL。把同样的业务查询分别在有索引/无索引/不同联合索引顺序下用EXPLAIN跑一遍对比rows和key_len的变化你对B树的理解会深很多。索引这种底层知识光看懂概念远远不够建议直接在本机MySQL里建一张百万行的测试表亲自做几个索引失效的实验把EXPLAIN的输出截图留下这才是最可靠的手感来源。