做MySQL性能优化的人迟早要在索引上栽几个跟头。我最早接触索引时只知道“查得慢就加索引”结果发现加了索引之后SQL反而更慢的情况也不少。后来折腾了很长一段时间才想明白索引不是银弹它是一套有代价的数据结构设计只有理解它底层是怎么组织的、用什么算法维护和检索才能在真实业务里做出合理的取舍。这篇文章围绕“MySQL索引底层数据结构与算法”这个话题把那些网上讲得比较零散的点重新梳理一遍。适合刚入门没多久、被慢查询折磨过的开发也适合用MySQL做了一些项目但一直没搞懂“为什么联合索引有最左前缀原则”“为什么建议用自增主键”这类问题的人。看完之后你至少能自己推导出什么样的SQL能走索引、建索引时应该看哪些指标、哪些情况下索引会“失效”。1. 为什么索引最终选择了B树1.1 候选数据结构逐个淘汰先说结论InnoDB的索引默认用的是B树不是红黑树不是跳表更不是哈希索引。这个选择不是随意拍板的而是磁盘I/O模型和查询场景共同倒逼出来的。数据库的数据是落在磁盘上的磁盘随机读一次大概要花10毫秒左右而内存读取是纳秒级别。索引的本质是减少数据扫描量也就是减少磁盘读的次数。要减少读取次数就要求索引结构“每次读进来的数据尽可能有用”。哈希索引看起来很美等值查询O(1)复杂度写代码的人都会心动。但哈希索引对范围查询完全没招where id 100这种条件它干不了只能全表扫。而范围的区分度是要靠顺序扫描的哈希天生无序。更致命的是哈希冲突靠链表解决极端情况下会退化成O(n)。所以哈希索引在InnoDB里只是作为一个自适应哈希索引的辅助结构存在不是主力。二叉查找树在顺序插入时会退化成链表这一点很好理解MySQL索引也不可能用裸的二叉查找树。红黑树解决了平衡问题但树的高度依然有限。你想想一个节点只存一个键树高随便就是二十几层。每层至少一次磁盘I/O20多次I/O才能定位到一个叶子节点这谁顶得住。再说一下B树和B树的比较。B树是每个节点既存数据也存索引键范围查询要靠中序遍历——注意是跨节点遍历链式访问能力很差。B树则是数据只存放在叶子节点非叶子节点只存索引键和指针这样做有两个直接好处非叶子节点的扇出变大也就是一个节点能指向更多的子节点树高被压缩了叶子节点用链表串起来范围查询可以沿着链表顺序扫效率极高。1.2 B树的磁盘友好体现在哪里B树一个节点对应InnoDB的一个页默认大小16KB。为什么页大小是16KB而不是4KB或者更小这是经过实测的折中页太小会导致树层数变多、IO次数增加页太大会导致单次IO浪费因为不一定用得上那么多数据。16KB这个值在典型的OLTP负载下表现最好InnoDB也允许你改但基本没人动它。可以做一道算术题感受一下树高。假设一行记录1KB那么一个16KB的页大约能存16行数据。非叶子节点如果主键用bigint8字节加指针6字节一个节点大约能存16KB/14字节约等于1170个键值对。二层B树能覆盖117016大约18720行三层能覆盖11701170*16大约是2190万行。这就是为什么网上常说“MySQL单表两千万行是个坎”其实不是玄学是三层B树几乎可以覆盖两千多万行记录超过这个量级树就会涨到四层访问路径变长性能会肉眼可见地下降。2. 从数据组织角度看聚簇索引与二级索引2.1 InnoDB表本身就是一棵B树InnoDB不是把索引和数据分开存的而是整张表就是按主键聚簇的B树。聚簇索引的叶子节点直接保存了整行数据这意味着表数据的物理存储顺序完全由主键决定。这点跟MyISAM那种索引与数据文件分离的方式有本质区别。用一个生活化的类比帮你记住这个区别新华字典的正文部分本身就是按拼音排序的每一页就是字典的“叶子节点”你在正文里找到某个字它的页面里就带着这个字的完整释义——这就是聚簇索引。而字典末尾的“部首检字表”只告诉你某个字在第几页查到之后你还得翻回正文——这就是二级索引也叫非聚簇索引。所以你在InnoDB里对一张表建立二级索引时实际发生的事情是新建一棵B树叶子节点存储的是当前索引列的值加上主键值。比如你在(age)列上建索引那这棵B树的叶子节点存的就是(age, id)而不是整行数据。当你用where age20查询时数据库先在二级索引树里找到对应的主键id再回到聚簇索引树里根据主键找到完整记录这个过程叫回表。2.2 回表是性能杀手覆盖索引是解药回表本质上多了一次聚簇索引树的搜索数据量小的时候感觉不出来一旦二级索引命中的行数很多比如几千上万行每行都回表就是几千上万次随机IO慢SQL就这么来了。避免回表的最常用手段是覆盖索引。所谓覆盖索引就是查询需要的所有列都在二级索引的叶子节点里不需要回表。比如一个索引建在(age, name)上你查询select age, name from t where age20数据直接从二级索引里拿根本不用回聚簇索引。在MySQL里执行计划中Extra列如果出现Using index就说明这条查询命中了覆盖索引。我在实际优化中见过太多查询因为多select了一个多余的字段导致索引覆盖失效。解决办法很简单看执行计划发现Using index condition或者Using where换成Using index多半就是调整索引列和查询列的关系就能解决的。2.3 为什么主键最好用自增整数这个问题的答案从聚簇索引的结构上就能推出来。如果使用自增整数作为主键新插入的行会追加到B树最右侧的叶子节点数据页几乎是顺序写页面利用率高不需要频繁挪动已有数据。反过来如果使用UUID这种随机字符串作为主键新行的主键值落在已有记录之间为了保持B树有序就得把中间某个叶子节点分裂成两个再把一半数据挪过去。页分裂不仅带来写入开销长期还会在物理文件里留下大量碎片导致表的实际存储膨胀、扫描效率下降。我在一个日志表上做过对比实验同样写入一千万行用UUID做聚簇主键的写入耗时比自增主键多了大约40%表空间占用多了近一倍。如果你确实需要全局唯一ID来标识业务记录正确做法是用自增主键作为聚簇键业务唯一ID建一个唯一索引让二者分离。3. 联合索引、最左前缀与查询路径3.1 联合索引在B树里长什么样联合索引的核心概念是最左前缀原则但很多人只记住了结论没搞懂原因。比如建一个(a, b, c)的联合索引B树的节点键值是按(a, b, c)的字典序排序的。先按a排序a相同再按b排序b相同再按c排序。所以如果查询条件的第一个匹配列是b而a没给就没法用这个索引因为b的有序性是在a固定之后才成立的。从数据结构角度看联合索引其实就是一个按优先级排序的多级键。平时可以把它理解成查电话号码簿先按姓氏排再按名字排。你只知道“叫伟的人电话是多少”没法直接查因为姓氏没定名字的有序性不成立。实操中最容易犯的错是觉得“索引建了所有包含索引列的条件都能用上”。其实不是。where a1 and c3只能命中a这一个前缀c没法用上索引过滤只能作为where条件过滤。所以设计联合索引时字段顺序非常重要通常把等值条件的列放在前面把范围条件列放在后面。3.2 索引下推数据库偷偷帮你做的优化索引下推Index Condition Pushdown简称ICP是MySQL 5.6引入的特性理解它需要结合B树的结构。回到上面的例子索引是(a, b, c)查询是where a1 and c like %3MySQL在5.6之前会先用索引定位到a1的所有记录然后逐行回表再在完整行上判断c的条件。引入ICP之后MySQL在二级索引遍历的时候就直接用c的条件过滤一遍过滤掉明显不满足的记录然后再回表。虽然c不是索引前缀但索引页里已经带了c的值可以在索引层面做一次“预筛选”。怎么看这个优化有没有生效执行计划Extra列出现Using index condition就是ICP在起作用。我在实际调优中遇到过不少慢SQL加了ICP之后回表行数直接降了几个数量级因为回表是消耗IO的大头能在索引层过滤掉一批就不用多跑一趟。3.3 排序与分组如何利用索引的“天生有序”B树的另一个隐藏优势是叶子节点天然按键值有序排序和分组操作可以直接利用这份有序性省掉一次filesort。比如索引是(a, b)查询where a1 order by b因为a1已经让索引定位到固定区间区间里的b本来就是按顺序排列的所以返回结果直接就是有序的不需要额外排序。这个在EXPLAIN里对应的就是Extra列里没有Using filesort。MySQL 8.0还支持降序索引B树叶子节点的链表是双向的这让倒序遍历也变得高效。5.7及之前如果想对某个字段倒序排序数据库得把正序的索引数据读出来再搞一次反向排序。8.0之后可以在建索引时指定desc避免了这个额外开销。4. 索引设计实操与常见失效场景4.1 区分度与索引基数很多人建索引的时候不看数据分布直接在性别、状态这种低区分度字段上建索引然后抱怨“加了索引没效果”。这不是索引没用是索引本身就不该这么建。判断一个字段适不适合建索引最重要的指标是区分度也就是基数Cardinality。基数是某个索引列上不同值的个数。性别字段最多两个值基数差不多就是2走索引反而可能比全表扫描还慢因为MySQL优化器会算账如果估算出来走索引要访问超过一定比例的行它直接放弃索引改走全表扫描。我以前在用户表上建过(status)索引status只有0、1、2三种值查询结果占比超过30%结果执行计划里根本没有用索引那时候还很困惑。后来才明白这是优化器基于叶子节点数量和行数的成本估算做的合理选择。实际操作中可以通过show index from table;查看Cardinality列它与表总行数的比值就是区分度的粗略度量。比值越接近1这个索引的价值越高。像订单表中的order_no、user_id配合时间这类字段区分度基本没问题。4.2 哪些“理所当然”的写法走不了索引我整理了实际开发里最容易踩的几个坑几乎每个都跟数据结构相关。对索引列使用函数或者表达式会导致索引失效。比如where year(create_time)2024因为B树里存的是原始值不是经过函数计算后的值数据库无法直接在索引上比较只能全表扫描然后逐行计算。正确写法是where create_time 2024-01-01 and create_time 2025-01-01。隐式类型转换也会让索引失效。where phone 13812345678如果phone列是varchar类型而等号右边是数字MySQL会先把varchar转成数字再比较索引就不能用了。这种问题排查起来特别隐蔽因为SQL本身看着没问题。前导模糊查询like %abc无法使用索引因为B树有序性是从左往右的前导字符串不确定无法定位区间。但like abc%可以走索引这跟联合索引的最左前缀逻辑是一样的道理。OR条件连接时如果OR两侧的列不是同一个索引也会导致索引失效。比如where namea or age20其中name有索引、age没有索引MySQL要同时满足两种情况只能放弃索引。解决办法是给age也建索引或者用union拆开两条单独查询。4.3 一个实际索引设计案例假设有一张订单表核心查询是“按用户查最近订单”以及“按用户加时间范围过滤”。CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, goods_id bigint NOT NULL, order_time datetime NOT NULL, status tinyint NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_time (user_id, order_time) ) ENGINEInnoDB;按user_id查最近的订单走idx_user_time索引直接定位到该用户的区间再按order_time倒序从头读几个就行不需要额外排序也不需要回表拿完整行只要查询列控制在索引列加主键范围内就能覆盖。如果再加上status的过滤比如“查某人最近且待支付订单”索引还是(user_id, order_time)status在索引后置位无法用于下推但order_time的有序性限制了扫描范围查询压力依然可控。真要是status也需要高频过滤可以考虑把status放到索引里变成(user_id, status, order_time)这时先按user_id和status定位区间再按order_time排序返回效果会更好带来的代价是写入时索引维护开销变大这要根据业务读多写少还是写多读少来权衡。5. 常见问题与排查经验速查在实际开发中我碰到过大量关于索引的问题很多看起来毫不相关根因却是同一个对索引底层结构缺乏理解。我把高频问题整理成了一张速查表。现象原因结构层面解释解决方案表数据不到100万查询却要几百毫秒索引设计不当或没走索引查询需要回表次数过多或者全表扫描分析Where条件重建联合索引调整字段顺序明明建了索引MySQL却不走优化器估算走索引成本更高索引区分度低命中行数占比过高提高索引区分度或改写SQL缩小范围加了索引反而更慢二级索引加回表的开销大于全表扫描回表是随机IO比顺序IO慢得多用覆盖索引替代回表或删除低效索引where条件包含索引列但没走索引对索引列做了函数运算或类型转换B树有序性基于原始值函数破坏比较逻辑改写为范围条件避免列上运算order by字段排序慢索引没有覆盖排序字段filesort是额外排序步骤需要独立内存或临时文件把排序字段加入索引利用B树有序性按日期范围查数据时间戳索引没用上范围条件在联合索引中的位置不对范围列后面的索引列无法用于过滤将范围列放在联合索引末尾列几个排查时特别有用的经验。第一养成看EXPLAIN的习惯。重点不是看type那一列是不是eq_ref而是要关注key、rows、Extra三项。key列告诉你了实际上用了哪个索引rows是预估扫描行数Extra里的Using filesort、Using temporary、Using index condition分别对应着排序、临时表、索引下推。这些信息组合起来就能判断SQL在结构上是怎么执行的。第二考虑索引维护成本。每次插入、更新、删除都需要同步维护每一棵索引树。索引建太多写入性能会被拖垮。生产环境遇到“写入比读取慢得多”的情况不少就是无脑建索引导致的。常规建议是单表索引控制在五个以内并且避免在频繁更新的列上建索引。第三大表加索引要用在线DDL。早期版本ALTER TABLE加索引会锁表好在Percona Toolkit和MySQL 5.6以后的Online DDL解决了大部分场景。生产环境加索引仍然建议在低峰期执行并预估好执行耗时因为即使不锁表大表的索引建立也会消耗大量IO资源。第四偶尔可以用optimize table整理碎片。表数据频繁更新删除会产生页碎片导致叶子节点利用率降低、逻辑IO增多。对主键随机写入的表尤其明显。我在一张多年没有整理过的用户表上执行过optimize table扫描行数没变但查询耗时下降了差不多30%这就是碎片带来的影响。6. 写在最后的个人体会做了这么多年的MySQL慢查询优化我不敢说每个执行计划都能一眼看穿但有一条经验是确定的凡是能徒手把索引为什么走、为什么不走推导清楚的工程师做优化时基本不需要乱试。比如遇到一条慢SQL已经形成肌肉反应的步骤是先看EXPLAIN分析SQL的过滤字段、排序字段、关联字段再结合表的数据量和写入模式判断该不该建联合索引、字段顺序怎么排、要不要用覆盖索引来消掉回表。这些判断全部建立在B树的数据结构认知之上——索引有序、叶存主键、回表有代价、最左前缀才可利用、范围查询有序输出。理解了这些大多数索引问题都能在脑子里推演一遍得出结论而不是陷入“加索引试试、不行再删”的循环里。如果你刚开始接触这块我建议不要急着背那些“索引失效口诀”而是拿一张真实的表建不同索引反复看EXPLAIN的结果差异。只有亲眼看到rows的变化、Extra的变化才能把这些数据结构层面的原理真正内化成自己的直觉。这一套东西值得花时间磨透。