前几天有个同事带着一脸困惑跑来找我说他写了一条SQLwhere条件里两个字段都建了索引执行计划却告诉你他根本没走索引。我一问表里建了两个单列索引优化器觉得还不如全表扫描执行计划果断把索引扔了。这类问题我见过太多次了根源往往不在SQL写法上而在对索引底层的结构理解不够——你不知道索引在磁盘上长什么样就猜不透优化器的决策逻辑。今天的重点就是索引的数据结构从最底层的B树、哈希索引讲起一直聊到联合索引怎么建、哪些场景会让索引失效。写个后端、做数据库调优、或者正在准备面试的人这篇都适合。1. 索引数据结构全景从一次慢查询开始1.1 一个典型的慢查询事故现场先还原一个真实场景。某天线上反馈订单查询接口很慢慢日志里抓出来了这样一条SQLSELECT * FROM orders WHERE user_id 10086 AND status 1 ORDER BY create_time DESS LIMIT 20;开发同学说user_id和status都建了索引怎么会慢DBA一看user_id和status各有一个单列索引优化器在这个查询里只能选其中一个来用另一个索引帮不上忙。更麻烦的是ORDER BY create_time还得单独排序。如果数据量是几百万行这个排序会直接拖垮查询。这个例子引出了索引数据结构的第一课索引不是越多越好也不是随手给每个字段挂一个就完事。你得知道索引是怎么组织数据的才能理解为什么联合索引更合适、为什么某些条件下索引会失效。1.2 索引的本质为了少读磁盘索引到底是干什么的说白了就是给数据建一个“目录”让数据库不用把整张表翻一遍就能找到目标行。全表扫描就像在厚厚的一本书里从第一页翻到最后一页找一句话索引则像书末的术语表直接告诉你关键词在第几页。但数据库的目录不是普通目录它必须解决一个核心矛盾数据量巨大内存装不下大部分数据在磁盘上。磁盘IO是极其昂贵的操作一次随机IO往往要消耗几毫秒到几十毫秒比内存访问慢几个数量级。所以在设计索引的数据结构时最重要的指标不是“查找次数有多快”而是“要读多少次磁盘块、每次能带回多少有效信息”。很快你会发现常见的二叉树、二分查找、哈希表这些数据结构到了磁盘环境下都各有各的致命伤。这也是为什么MySQL InnoDB最终选择了B树作为索引的默认结构但哈希索引也有它的广阔舞台。1.3 索引结构演进从二叉树到B树把数据结构课上的知识串起来看事情会清晰很多。最朴素的想法是用二叉查找树做索引左小右大查找时和根节点比一下向左或向右走。问题在于普通二叉查找树在插入有序数据时会退化成一条链表查找复杂度直接掉到O(n)更麻烦的是每个节点只能存一个键一次磁盘IO只能取一个节点树的层数稍微一深磁盘IO次数就爆炸。于是有了平衡二叉树AVL和红黑树它们通过旋转把树的高度控制住让查找稳定在O(log n)。但红黑树的问题仍然是“太瘦高”——一个节点存一个键高度通常还在十几层以上而每一层都意味着一次磁盘IO。数据库里动辄几百万行数据十几层的高度是不可接受的。真正让数据库性能发生质变的是B树和B树。它们的核心理念是“矮胖”——一个节点不只存一个键而是存几十上百个键并且一个节点对应一个磁盘页一次IO就把一整块数据读进内存。这样几百万行的数据量树的高度通常只有2到3层意味着最多两三次磁盘IO就能定位到目标。结构节点存储树高度磁盘IO次数范围查询数据库使用场景二叉查找树1个键高可能退化为链表多差基本不用红黑树1个键较高log n每层1次IO一般内存场景B树多个键数据矮2到3次较差需回溯早期文件系统B树内部节点只存键叶子存数据矮2到3次极好叶子链表顺序遍历InnoDB默认索引看到这张表你就明白了数据库选B树不是偶然而是IO模型倒逼出来的必然。2. 一层层拆开B树与B树2.1 B树的结构与查找过程B树是一种多路平衡搜索树。所谓“多路”是指一个节点可以包含多个键值和多个指向子节点的指针。假设一个节点能存4个键它就有了5个分叉所有键在节点内按顺序排列。查找时先在节点内部做二分查找命中就结束没命中就根据键的大小走向对应的子节点直到命中或到达叶子。用数据举个例子。假设一个B树节点能存100个键那么树有3层时最多可以容纳约100万个键。每一层对应一次磁盘IO三次IO就能从100万条数据里找到目标这个效率在工程上非常可观。B树的一个特点是所有节点都可以存储数据行或指向数据行的指针。这意味着你在非叶子节点就能找到目标不用非要走到叶子。听起来不错但这带来两个问题。第一数据散落在不同层的节点上查找性能不稳定有的人找2层就到有的人要一路走到第3层。第二范围查询很尴尬——比如要查某个区间内的所有数据B树得先找到起点然后在中序遍历过程中不断向上回溯父节点不断切换分支产生了大量额外的磁盘IO。2.2 B树的结构数据都在叶子上B树针对B树的问题做了两个关键改动。第一个改动是内部节点非叶子节点只存键值和子节点指针不存数据。这样一来同样大小的磁盘页能容纳更多的键树变得更“宽”更“矮”——16KB的页大概能存上千个键树高度很容易控制在2到3层。第二个改动是所有数据都集中在叶子节点上并且叶子节点之间通过双向链表按顺序串联起来。这两个改动让B树在数据库场景下优势尽显。查找任意一条数据都必须走到叶子节点每一次查询的IO次数稳定在同一量级不存在“运气好走两层、运气差走五层”的波动。范围查询直接从叶子链表头开始往后扫就行不需要在树中间反复回溯。排序操作也爽了叶子本身就有序遍历叶子链表就是有序结果。对数据库来说“稳定”和“有序”恰恰是最值钱的特性。2.3 为什么InnoDB选B树而不是红黑树或B树很多人刷算法题时觉得红黑树很强大为什么数据库不拿来当索引关键在于衡量标准不同。红黑树是内存数据结构它的旋转操作在内存里很快但在磁盘面前它一次IO只能读一个节点高度再矮也扛不住几百万行数据。简单算一下InnoDB的B树高度为2时叶子节点能容纳数百万条记录查找只需两次IO同样数据量用红黑树树高度至少20层就是20次磁盘IO差了十倍量级。那为什么不直接用B树B树在范围查询上的劣势是致命的。电商订单按时间范围查、分页查询、各种报表查询全都是范围操作。B树的叶子链表让这些操作变得像遍历数组一样轻松这是B树给不了的。B树还天然契合InnoDB的聚簇存储结构——后续会提到主键对应的索引叶子节点直接存放整行数据这个特性必须依赖“数据只在叶子”的设计。所以在InnoDB内部B树不是可选项而是牢牢绑定在存储引擎核心里的默认方案。2.4 为什么不用跳表LevelDB的对比Redis的Sorted Set用跳表LevelDB的MemTable用跳表为什么MySQL不换跳表查找复杂度也是O(log n)但它的每个节点通常只有两个指针相邻节点之间跨度小要素过多。跳表在内存中表现极佳因为它按指针逐层跳跃一旦换到磁盘上节点离散存储查找一个目标可能要跨越多层指针、多次随机IO页缓存也无法充分利用。B树的优势在于把大量键聚在同一个磁盘页里一次IO读回一大堆“可用的键”局部性原理被发挥到极致。数据结构好不好不看理论上限看它在特定介质上的适配程度——这就是工程思维。3. 不只是B树哈希索引与全文索引3.1 哈希索引等值查找的王者B树合适不代表所有场景都该用它。哈希索引是另一个世界里的强者它的思路完全绕开“比较大小”直接通过哈希函数把键值映射到桶里。查找时计算一次哈希定位到桶再处理桶内的冲突链等值查询能做到接近O(1)。这正是哈希索引的黄金场景精确匹配且对范围无欲无求。在MySQL里Memory引擎默认就使用哈希索引因为它快、简单、不做磁盘持久化。InnoDB则更聪明它搞了一个自适应哈希索引AHI。这玩意不是让你手动创建的而是InnoDB在内存中自动监测热点数据当某些B树索引页被高频访问时自动为它们建立哈希索引把等值查询从多次IO降低到一次内存查找。很多人在慢查询日志里看到“AHI命中率低”之类的信息其实就是访问模式太离散自适应哈希索引发挥不出来。哈希索引的致命短板也很明显做不了范围查询、、BETWEEN通通没法用不支持排序因为哈希值无序也做不了前缀匹配。所以生产环境里主键索引、联合索引几乎全是B树哈希只能当配角。有些开发同学一看到“哈希”两个字就觉得它更快非要给业务唯一键建哈希索引结果范围查询一来直接全表扫描这就是对数据结构适配性不理解。3.2 全文索引倒排索引的应用还有个常见的索引类型是全文索引它背后的数据结构是倒排索引。传统索引是从文档ID找词倒排索引反着来——从词找文档ID列表。建全文索引时MySQL会对文本做分词把每个词映射到包含它的行ID列表上存储成类似“词 → 文档ID集合”的结构。搜索时先查词再快速拉出所有包含该词的行。很多人用LIKE %关键词%去搜大段文本结果慢到怀疑人生。这就是数据结构选错了LIKE %xxx%无法使用B树的前缀匹配特性索引失效只能全表扫描。正确的做法是给文本字段建立全文索引再用MATCH ... AGAINST语法做全文检索。InnoDB从5.6版本开始支持中文全文索引但要先处理分词器问题中文不按空格分词需要选好ngram解析器。自己搭搜索引擎时Elasticsearch的倒排索引也是同一个思想明白了底层是倒排结构你就知道为什么它能秒级搜索上亿文本。3.3 空间索引以R树为代表的几何结构聊到索引数据结构空间索引不能完全忽略。MySQL的MyISAM和InnoDB都支持空间索引底层是R树。R树专门为多维几何数据设计把空间上相邻的对象用最小边界矩形MBR包起来上层节点用更大的矩形包含下层矩形查询时逐层判断矩形是否相交快速剪枝掉不相干的分支。地图上“找附近1000米的餐厅”这类查询如果数据量大会用到空间索引。但对绝大多数业务来说地理坐标直接用GeoHash编码存成字符串配合普通B树前缀查询一样能解决问题还不必引入新结构。4. InnoDB的聚簇索引与MyISAM的非聚簇索引4.1 聚簇索引数据跟着主键走聊完几种底层结构回到存储引擎这个层面来看索引形态。InnoDB里主键索引就是聚簇索引B树的叶子节点直接存放整行数据。也就是说表数据本身就是按主键顺序聚簇在B树上的。查询走主键时在叶子节点找到目标的同时就拿到了这一行的全部列不需要再回表快得很。这个设计带来一个强约束InnoDB表必须要有主键。如果你建表时不指定主键InnoDB会先找第一个非空的唯一索引作为聚簇索引实在找不到它会自动生成一个隐藏的6字节rowid作为聚簇索引。很多开发同学不在意这个但底层逻辑就是没有明确的聚簇索引InnoDB也得自己造一个对不对齐你的数据访问习惯它管不着。聚簇索引还深刻影响插入顺序。如果主键是随机的UUID新插入的数据在B树上的位置是跳来跳去的经常导致叶子节点的页分裂和页重排产生大量碎片插入性能明显下降。这也是为什么我强烈建议用自增整数或bigint做主键而不是UUID——从数据结构的角度看顺序插入能让B树的叶子节点平稳扩展避免页分裂。4.2 二级索引回表是怎么发生的除了主键索引之外的索引都叫二级索引。二级索引的B树叶子节点不存整行数据只存索引列的值加上主键值。当查询条件命中了二级索引却又要读取非索引列时数据库会先从二级索引叶子拿到主键再根据主键去聚簇索引上找完整行这个过程叫回表。回表意味着一次查询可能要跑两遍B树所以性能不如直接走主键索引。一个经典的优化思路是覆盖索引如果你查询的列全部都在二级索引里那么叶子节点上的数据已经足够InnoDB就不回表了。比如SELECT user_id, status FROM orders WHERE status 1如果(status, user_id)上有联合索引查出来的两列都能直接从索引拿Extra列的Using index就说明发生了覆盖索引扫描。很多慢查询的优化方案说白了就是把SELECT *改成覆盖SELECT所需列加联合索引把回表次数降成0。4.3 主键索引和唯一索引到底差在哪这个问题被问过无数遍。主键索引和唯一索引从数据结构上看都是B树但两者的约束和用途有明显区别。一张表只能有一个主键索引但可以有多个唯一索引。主键列不允许为NULL唯一索引列允许有多个NULL值——在InnoDB里唯一索引对NULL是放行的因为NULL本身代表“未知”未知和未知不算重复。在InnoDB中主键索引是聚簇索引承载整行数据唯一索引是二级索引叶子节点只存主键值。主键是物理存储的锚点唯一索引只是逻辑上的“不允许重复”约束加一个可用的加速路径。从实用的角度说业务上要求某个字段唯一时比如手机号、身份证号一定要给字段加唯一索引。这里有个容易踩的坑数据库的并发环境里两个请求同时插入相同手机号如果没有唯一索引兜底先靠应用层判断会存在竞态条件导致脏数据。有了唯一索引第二次插入就被数据库拒绝业务层的判断数据库唯一索引双保险才可靠。4.4 MyISAM的非聚簇索引一张对照表MyISAM引擎的索引和InnoDB完全不同它是非聚簇结构B树的叶子节点存放的是数据行的物理地址而不是数据本身。索引文件和表数据文件分开存储索引定位到地址后还需要再读一次数据文件所以无论走主键还是二级索引本质上都是两层结构。这种设计的优点是索引结构简单统计和压缩方便缺点也很明显数据在物理文件中的顺序和索引顺序可能不一致范围查询和批量插入的局部性不如InnoDB。如今主流业务基本都用InnoDB因为崩溃恢复能力强、支持事务聚簇索引本身也让主键查询更快。我在新项目里几乎不再建MyISAM表只有个别只读、查全表的分析场景还在用。5. 联合索引的结构与最左前缀where a and b究竟该怎么建5.1 联合索引是怎么排序的回到开头那个让人困惑的问题where条件里有多个字段到底怎么建索引答案通常不是给每个字段各建一个单列索引而是建联合索引。联合索引在B树里的排序逻辑是先按第一个字段排第一个字段相同的记录再按第二个字段排以此类推。比如(user_id, status)这个联合索引叶子节点上所有数据先按user_id分组每组内部按status有序排列。这个排序规则带来一个重要的结论——最左前缀原理。查询条件如果包含联合索引的最左列就能利用这个索引如果查询条件跳过第一个字段只命中第二个或后面的字段那么B树的排序顺序帮不上忙索引就会失效。WHERE user_id 10086 AND status 1能用到(user_id, status)但WHERE status 1在这个联合索引上毫无用武之地。5.2 顶层设计等值先行、区分度高的放左边那么where a and b应该怎么建索引关键是看查询条件和数据分布。如果a和b都是等值查询即a 1 AND b 2那么联合索引的两个列放谁在前问题不大因为等值条件下优化器会按照索引来检索都能快速命中。要注意的是若后面还有范围条件比如a 1 AND b 100那么范围条件右边的列就再也用不上了。这种情况下把等值列放在联合索引的前面范围列放最后才能最大化索引利用率。还需要考虑区分度。简单来说某列的数据越分散区分度越高。性别只有男、女两种值区分度就很低手机号基本不重复区分度就高。建联合索引时一般把区分度高的列放前面因为B树排序后高区分度列能更快把数据缩到很小的范围。比如(user_id, status)明显比(status, user_id)好因为user_id的取值范围大能直接把几百万行缩到几十行。5.3 为什么不建两个单列索引有一种错误的做法是给a和b分别建单列索引。虽然查询条件里同时出现了两列但优化器在一个查询里通常只能选择一个主要的B树索引来定位另一个索引最多通过index merge做交集合并。index merge在很多版本里都有额外开销性能不如一个联合索引直接定位。开头的那个订单查询正好踩在这个坑上——user_id和status各有一个单列索引执行计划只能用其中一个然后对剩下的条件做过滤如果过滤比例不高还不如全表扫描。联合索引还有个容易被忽视的好处覆盖索引。比如(user_id, status, create_time)这个索引查询只需要这三列时走索引就能直接返回不用回表。这个特性往往被忽略但它对优化SELECT指定列的场景极其有效。6. 索引失效场景排查与explain实战6.1 常见失效场景速查表数据结构搞懂了还得能把它变成日常排障的直觉。下面这些场景我在实际排障中几乎每周都会遇到整理成一张速查表失效场景根本原因正确做法对索引列使用函数如WHERE DATE(create_time) 2025-01-01函数改变了列的原始顺序B树无法对上改成范围条件create_time ... AND create_time ...隐式类型转换如WHERE phone 13800138000phone是varchar发生类型转换后索引列被视为函数处理查询参数写成字符串保持类型一致LIKE %keywordB树只能按前缀匹配前导通配符破坏了顺序改用前缀LIKE keyword%或上全文索引OR条件连接非索引列OR两边任意一边不能走索引整体降级改写为UNION或确保OR两边都有索引NOT IN、!范围扫描的边界条件不满足索引定位视业务改写为IN或范围查询联合索引跳过了最左列B树排序逻辑根本用不上调整查询条件或索引列顺序优化器认为全表扫描更快表数据量小或查询返回比例太高重新设计索引或检查统计信息是否过期6.2 explain实战看穿执行计划排查索引问题时我最依赖的工具就是EXPLAIN。它的核心列就几个看懂了基本能定位问题。type一列的值从好到差依次为system const eq_ref ref range index ALL看到ALL基本就是全表扫描是慢查询的重灾区。key列告诉你实际用到的索引是什么key_len表示用到了联合索引的多少前缀列这个数字变化很大——都是联合索引用得深不深它是客观证据。rows是估算扫描的行数行数越大越危险。Extra列如果出现Using filesort意味着排序没走索引额外多了一次排序操作这在海量数据下非常耗时。举一个最常见的场景EXPLAIN SELECT * FROM orders WHERE user_id 10086 AND status 1 ORDER BY create_time DESC LIMIT 20;建了(user_id, status, create_time)联合索引后type从ALL或index变成了refExtra里的Using filesort消失说明排序也直接利用上了索引顺序。看到这个变化就可以放心地把这个索引固化下来。排查慢查询的时候先看type、再看rows、最后查Extra十有八九能定位问题。6.3 自检索引设计的几条经验法则建索引之前先回答几个问题WHERE条件里哪些列是等值、哪些是范围SELECT需要哪些列能不能覆盖这个索引会用在哪些SQL上会不会同时服务多个查询回答完再动工。几条经过检验的经验法则数据量小几千行的维度表别建索引全表扫描最快。索引列不要参与任何运算包括函数、类型转换、四则运算。优先用联合索引替代多个单列索引别让单列索引“各自为政”。索引数量控制在单表5个以内索引会拖慢写入和存储空间。更新频繁的列慎建索引每次更新都要同步维护B树。排序字段能进联合索引就进避免Using filesort。这些经验不是书上看来的是线上事故和慢查询日志教出来的。数据结构决定了理论边界explain决定了实际效果两个都抓牢才能少踩坑。我个人最大的体会是数据结构课上学B树、B树时觉得它们离业务很远像个概念性的东西工作几年后发现数据库的所有性能问题最后都要回到这颗树上找答案。别看B树只有两三层的“身高”它承载的是整个InnoDB的存储世界理解它能让你面对索引问题时真正站到底层视角。遇到慢查询别急着加索引先停下来想想这颗树是怎么排序、怎么查找的往往答案自己就浮出来了。