从根上理解索引它只是把无序的数据变成有序的查找结构做后端这些年我见过太多因为索引问题导致的线上事故。最典型的一种业务跑着跑着某个接口突然变慢慢查询日志里发现一条SQL要扫描几百万行DBA一看执行计划根本没走索引或者走了个失效的索引。这时候大家第一反应通常是加个索引就好了但为什么加了索引有时还是慢为什么有些查询明明字段上有索引却不走为什么最左前缀原则总是记不住这些问题的答案都藏在MySQL索引的底层数据结构与算法里。MySQL索引、数据结构、算法这三样东西听起来像是大学课本里沉睡的知识点但实际上它们决定了你写的每一条SQL是毫秒级返回还是把数据库拖垮。理解它们不是为了让面试官满意而是为了让你在真正动手建索引的时候能基于原理做判断而不是靠猜、靠试、靠网上抄一段现成的方案。这篇博客不打算复述官方文档也不打算搞一堆术语堆砌。我会从一次真实的慢查询排查开始把B树、聚簇索引、二级索引、索引失效这些概念拆开揉碎配合实际操作和踩坑记录讲清楚为什么以及怎么做。适合正在写业务代码但经常被数据库性能问题困扰的后端开发者也适合准备深入学习MySQL原理的初学者。1. 先从一次慢查询说起1.1 一次接口超时引发的排查前两年接手过一个订单查询服务某天下午监控突然报警一个查询订单列表的接口P99延迟从80ms飙到了2.8秒。看慢查询日志定位到一条SQLSELECT order_id, user_id, amount, status, created_at FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;这条SQL看起来没有任何问题user_id字段也有索引这个我很确定因为建表的时候就加了但执行计划显示扫描行数是80多万Extra里有个一行字Using filesort。当时的第一反应是索引没建对吧SHOW INDEX FROM orders一看user_id上确实有普通索引idx_user_id。那为什么没走后来仔细查了数据分布才发现user_id12345这个用户的订单量本身就超过了10万条。对于优化器来说走idx_user_id需要回表10万次然后还要做一次磁盘上的filesort排序最后才取20条。它一估算成本发现全表扫描再加排序可能比走索引更快于是选择了全表扫描。这个案例特别典型它不是索引失效而是优化器基于成本模型做了最优选择。但问题在于filesort面对80万行数据性能确实扛不住。最终的解决方案是把(user_id, created_at)建成一个复合索引让索引天然支持按用户查订单并按时间倒序直接消除文件和排序。这个场景几乎每个做业务开发的人都遇到过。它的背后就是索引底层数据结构设计上的一个核心问题如何让数据在磁盘上也能快速检索和有序遍历。理解这个问题我们得先把索引的本质看清楚。1.2 索引的本质空间换时间的加速器索引的本质是一个独立于表数据的、额外的数据结构。数据库根据索引列的键值构建出一份经过排序的目录这份目录里只保存索引键和对应的物理位置或主键值占用的存储空间一般远小于原始数据。查询的时候数据库先在目录里快速定位到目标数据的位置再根据位置去数据文件里取具体的行。这很像新华字典的拼音索引你不会为了查一个字从第1页翻到最后一页而是先在拼音索引里定位到字所在页码一跳就过去了。索引存在的意义就是减少磁盘IO的次数因为一次磁盘IO的代价大约是内存访问的几十万倍。但是目录这个说法太笼统了。真正的数据库索引不是一个线性表也不是一个简单的哈希表而是一棵精心设计的树。为什么是树为什么最终选了B树而不是二叉树、不是哈希表这些选择背后全是算法和数据结构的权衡。接下来这章才是重头戏。2. 数据结构选型为什么偏偏是B树2.1 从二分查找说起按照正常的思维推导查找最快的办法是二分查找数据排好序每次取中间元素比较一次排除一半查找时间复杂度是O(logN)。二分查找的前提是数据在内存里是连续数组可以直接用下标访问。但数据库的数据存在磁盘上磁盘的最小IO单位是页Page通常为16KB。如果数据按页存放页与页之间并不是物理连续的你没法像数组一样随机访问第N个元素。更关键的是二分查找依赖随机访问这跟磁盘的机械特性冲突——磁盘寻道速度远远慢于顺序读取。所以数据库索引需要一种能够顺着指针往下走并且一次IO尽量读取更多有效数据的结构。这就把选项收敛到树形结构上了。2.2 二叉树、AVL树、红黑树为什么不合适二叉树Binary Search Tree理论上查找复杂度是O(logN)但它有一个致命问题极端情况下会退化成链表查找复杂度变成O(N)。数据库场景下插入数据是有序递增的比如主键自增那么以主键为索引的二叉树就会无情退化成一条右斜树跟全表扫描没区别。AVL树平衡二叉搜索树解决了退化问题通过旋转保持左右子树高度差不超过1保证查找稳定在O(logN)。但AVL的问题是每个节点最多只有两个孩子树的层数太深。如果数据量是1000万AVL树的层数大约在24层左右——别忘了每向下走一层就意味着大概率要读取一个磁盘页24次磁盘IO在任何场景下都是不可接受的。红黑树是近似平衡的二叉搜索树它允许左右子树高度差最多两倍虽然旋转次数更少、适合高频插入删除的场景但它的树高依然比B树深。内存里的TreeMap、std::map用红黑树没问题放在磁盘上还是太深。数据库索引的第一诉求是矮胖不是身材匀称。2.3 B树和B树从瘦高到矮胖的演变B树Balance Tree是多路平衡搜索树每个节点可以存储多个键值拥有多个孩子。同样1000万条数据如果每个节点能存放100个键B树的层数可能只有3~4层。这就意味着查询一个数据最多只需要3~4次磁盘IO巨大的进步。B树解决了矮的问题但还有一个性能痛点它每个节点都存储数据行或指向数据行的指针。如果数据体量很大每个节点能存放的键值数量就有限。而且做中序遍历范围查询时B树需要在中序递归或多次往返父节点和叶子节点顺序遍历性能很差。于是B树登场了。B树和B树的关键区别在于所有数据都存放在叶子节点非叶子节点只存放索引键值不存数据。这意味着非叶子节点能存放更多的键扇出更高树更矮。叶子节点之间通过双向链表相连形成一个有序的数据链。所有的查询最终都会落到叶子节点查询路径深度完全一致性能稳定。拿订单表来说如果单行数据200字节一个16KB的页大约能放80行如果是B树索引非叶子节点每条索引项大概8字节bigint主键一个页能放约2000个键。3层B树能存放大概80亿行数据的索引。这就是矮胖的威力。2.4 B树 vs Hash索引精确查询和范围查询的取舍还有一种选择是Hash索引基于哈希表实现查询时间复杂度接近O(1)听起来比B树的O(logN)强多了。但数据库里Hash索引从来只是配角因为Hash表无序完全无法支持范围查询BETWEEN、、。Hash表不支持最左前缀原则的复合索引优化。哈希碰撞会导致性能抖动。而B树的叶子节点天生有序范围查询只需要找到起点然后顺着叶子节点的链表往后遍历即可。这个特性让B树完全碾压Hash索引。Redis的ZSET用跳跃表能实现范围查询但跳跃表在磁盘场景下的空间利用率和IO效率不如B树所以也没被数据库采纳。2.5 一张图读懂B树为什么合适简单总结一下B树的三大核心优势这三点直接对应数据库索引的三大需求数据库需求B树的对应能力原理说明减少磁盘IO次数树高恒定且极矮非叶子节点只存键扇出高3~4层够支撑千万级数据高效范围查询叶子节点顺序链表找到边界后沿链表顺序遍历天然支持排序和区间扫描查询性能稳定每次查询都到叶子所有查询路径深度一致不会出现某些数据快某些慢也正是因为这些特性MySQL的InnoDB引擎选择了B树作为索引的默认数据结构。理解了这一点你再去回想那些加个索引查询就快了的案例脑子里就不只是一个模糊的印象而是能看到一棵具体的树。3. InnoDB里到底是怎么存的3.1 聚簇索引与二级索引的区别很多人以为索引是一个东西其实InnoDB里有两类索引区分它们对于理解SQL优化至关重要。聚簇索引Clustered Index是InnoDB表数据存储本身。每个InnoDB表都有一个聚簇索引数据行物理地存储在聚簇索引的叶子节点上。如果表定义了主键主键就是聚簇索引如果没有主键InnoDB会选第一个非空唯一索引作为聚簇索引如果都没有它会生成一个隐藏的6字节row_id作为聚簇索引。这就意味着聚簇索引的叶子节点存的是完整的行数据。你按主键查询走聚簇索引一次IO拿回一整行这就是回表的反面——不需要回表数据就在手里。二级索引Secondary Index也叫辅助索引则是独立于聚簇索引的、额外的B树。它的叶子节点不存储完整行数据只存储索引键值加上对应聚簇索引的主键值。比如你在user_id上建了普通索引这棵B树的叶子节点就是(user_id, 主键id)的集合。当查询条件用了user_id但还需要读取其他字段时流程是先在二级索引的B树里查到符合条件的主键id集合再拿着这些id去聚簇索引里回表取完整行数据。注意这里是一个逐行回表的过程涉及的id越多IO次数越多。这也是为什么有些查询虽然走了索引依然慢得离谱。3.2 复合索引和最左前缀原则的理解方式复合索引联合索引是指在一张表的多个列上创建的索引比如(user_id, created_at)。它的B树按第一列排序第一列相同再按第二列排序。B树的这个排序规则直接衍生出了最左前缀原则查询条件必须能匹配到索引的最左列否则索引无法生效。比如WHERE created_at ?单独查询是无法使用(user_id, created_at)这个复合索引的因为整棵树先按user_id排序created_at只是局部有序。但很有意思的一点是最左前缀不止是必须包含第一列这么简单。它还包括前缀匹配的连续性WHERE user_id 1 AND amount 100可以用到联合索引的user_id部分但amount的过滤是在索引内部做了索引条件下推ICP之后在回表前完成的而WHERE user_id 1 OR amount 100这种条件下优化器往往会选择其他策略因为OR条件无法保证两个列同时利用同一棵B树的排序结构。我见过不少同事把最左前缀当成死记硬背的面试题其实用B树的排序逻辑一想就通了你在字典里查拼音是yang且部首是氵是查不到的因为拼音索引里根本没有按照部首排序你必须先能定位到yang开头的区域最左列然后才能在局部区域里寻找第二条件。复合索引的列排列顺序决定了它就是一棵字典树。3.3 索引下推一个容易被忽视的优化MySQL 5.6引入了Index Condition Pushdown索引下推。在没有ICP之前使用二级索引查询时存储引擎会把所有满足索引键条件的记录都回表然后在Server层再过滤其他条件。有了ICP之后凡是能在索引内部完成的过滤都在索引层先做掉减少回表次数。举个例子SELECT * FROM users WHERE name LIKE 张% AND age 20;假设有联合索引(name, age)。没有ICP流程是先通过name LIKE 张%定位到一批主键id然后回表读每一行完整数据再判断age 20。有了ICP存储引擎在扫描索引的时候就会检查age 20只对符合条件的记录回表。这个优化对SQL的影响有时候是数量级的。用EXPLAIN查看时看到Extra列有Using index condition就说明索引下推被用上了。它不是一条配置不是一个命令是B树存储结构带来的一个天然红利——索引里本来就有列的数据不利用白不利用。4. 建索引的正确姿势从原理到实操4.1 最实用的索引类型选择先看一张InnoDB下索引类型的对比表索引类型使用场景底层结构备注主键索引每表一个聚簇索引B树聚簇叶子存整行建议自增或有序唯一索引保证列值唯一B树二级允许NULLNULL可重复普通索引加速查询B树二级最常用可以建多个复合索引多条件过滤/排序B树二级多键键按顺序排序有最左前缀全文索引文本内容检索倒排索引用于LIKE %xx%的替代方案但中文分词有坑哈希索引等值查询哈希表InnoDB无法手动创建由自适应哈希索引提供建索引的顺序有一个通用的方法论先分析业务查询的WHERE条件、ORDER BY条件、GROUP BY条件找出高频查询组合然后优先考虑复合索引而不是为每个列单独建索引最后用EXPLAIN验证执行计划。要注意的是给每个字段都建索引是最典型的反面教材。二级索引本身要占磁盘空间每次INSERT/UPDATE/DELETE都要同步维护索引树索引越多写入越慢。一张表动辄七八个索引写入性能迟早会出问题。4.2 一个合理的索引设计方案假设我们要为订单表设计索引业务上有三类核心查询按用户查订单并按时间倒序前面案例按订单状态查待处理的订单按商户查订单并按金额排序表结构大致是id, user_id, merchant_id, amount, status, created_at。参考设计方案如下ALTER TABLE orders ADD PRIMARY KEY (id), ADD INDEX idx_user_created (user_id, created_at), ADD INDEX idx_merchant_amount (merchant_id, amount), ADD INDEX idx_status (status);这里几个设计原因值得展开说说idx_user_created这棵复合索引的B树结构里叶子节点按user_id排序user_id相同再按created_at排序。所以WHERE user_id ? ORDER BY created_at DESC直接把排序结果从索引里读出来连filesort都省了Extra列会显示Using index condition不会出现Using filesort。idx_merchant_amount则是为了让ORDER BY amount也能走索引的有序性避免在内存排序10万行。idx_status单独建索引是因为status的区分度不高但它独立成索引后配合ICP可以在索引内过滤掉大部分不需要的行回表压力可控。4.3 用EXPLAIN验证你的设计写完索引后必须跑一遍EXPLAIN看执行计划这个习惯一定要养成。比如上面的例子EXPLAIN SELECT order_id, user_id, amount, status, created_at FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;你期望的结果是key显示idx_user_createdrows显示很小的值Extra里有Using index condition没有Using filesort。EXPLAIN的每个关键字段都要会看字段含义关键点type访问类型ALL全表扫描、ref非唯一索引等值、range范围扫描、const主键等值key实际使用的索引可能为NULL代表没走索引rows预估扫描行数越小越好但只是估算Extra额外信息filesort、temporary、index condition都可能在这出现4.4 实际执行后的优化效果还是拿开头那个案例说改成复合索引(user_id, created_at)之后执行计划中type从ALL变成refrows从80万降到几千行Extra不再有Using filesort接口P99延迟从2.8秒回到了60ms以内。这个效果看起来是加了个索引这么轻巧但实际上整个分析过程非常值得复盘原始索引idx_user_id本身没有失效而是它在等值过滤排序回表limit的综合成本下不占优势。真正解决问题的是让索引结构直接覆盖了排序需求这不只是多建一个索引是对查询模式做了一次结构性适配。5. 索引失效的常见场景与实战排查5.1 隐式类型转换这是最常见的坑没有之一。SELECT * FROM user WHERE phone 13800138000;phone字段是VARCHAR类型但查询值是整数。MySQL会隐式地把字符串字段转成数字再比较相当于对索引列做了类型转换函数导致索引失效。解决办法很简单查询值写成字符串13800138000。5.2 对索引列使用函数或计算SELECT * FROM user WHERE DATE(created_at) 2024-06-01;在索引列上套了DATE()函数B树里存储的是原始的created_at值函数处理后的结果无法直接与树中的键比较只能全表扫描。改进方式是改成范围查询SELECT * FROM user WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;这样既走索引又利用B树的范围扫描特性一举两得。5.3 LIKE以通配符开头LIKE %keyword会导致索引失效因为B树是按前缀顺序排列的无法在整棵树中定位以keyword结尾的键。但LIKE keyword%可以用索引因为前缀是确定的。如果业务必须做后模糊匹配可以考虑全文索引或者搜索引擎而不是跟B树死磕。5.4 OR导致的全表扫描SELECT * FROM user WHERE name 张三 OR age 30;即使name和age都有索引当OR的两个条件涉及不同索引时优化器无法把两个B树的查询结果做并集后去重这是传统B树做不到的往往直接选择全表扫描。解决办法是拆成两个查询用UNION合并或者改成用UNION ALL再在应用层做去重。5.5 复合索引列顺序与查询条件不匹配最左前缀原则失效的主要场景比如索引是(a, b, c)查询条件是WHERE b ?或WHERE c ?直接从第二列开始用索引完全帮不上忙。还有一种更隐蔽的WHERE a ? AND c ?索引只用到了a列c列会在回表后用ICP过滤或者如果MySQL认为回表成本高可能就不走索引了。5.6 区分度太低的选择性陷阱博客、文章这种表上建is_deleted这个布尔列的索引典型低区分度。如果表里90%的数据都是is_deleted0优化器一算发现走索引回表还不如聚簇索引顺序扫描快它会直接放弃索引。这种情况下别怪MySQL它不是笨是算清楚了成本。真正的解法是重构查询模式比如把有效数据和历史数据分开存储或者用(status, created_at)这种带顺序的复合索引而不是只建一个单列状态索引。5.7 一个排查索引失效的通用流程排查慢SQL时我一般按照这个顺序操作用EXPLAIN查看执行计划先确认type是不是ALLkey是否为NULL。如果走了索引但还是慢看rows和Extra确认是否回表次数过多或存在filesort、temporary。检查查询条件中的列与索引列是否类型一致SHOW CREATE TABLE看字段类型。检查是否在索引列上使用了函数、运算、隐式转换。检查复合索引的列顺序是否匹配查询条件的最左前缀。如果是范围查询确认优化器有没有选错索引可以用FORCE INDEX做对比测试但千万别在线上长期用FORCE INDEX。这样一步步排查下来90%的索引问题都能定位到根本原因。6. 从原理到实践的个人体会写到这里我想分享几条自己踩过坑后最深的体会。第一条是索引不是越多越好而是越精准越好。一张表建了8个索引看着每个查询都有索引可走可代价是每插一条数据要维护8棵B树写入磁盘的IO次数直接翻好几倍。最终表现为数据库CPU不高但磁盘IO持续打满。建索引之前先列出所有高频查询确定每个查询需要的索引键能复用就复用绝不为了个别接口单独加索引。第二条是filesort不根除慢查询迟早回来。凡是线上出现过Using filesort的SQL我非常不建议只靠临时加索引解决而是思考这个操作的本质是什么——排序需求是否可以由索引结构天然承担比如ORDER BY字段能不能加入复合索引的尾部如果能排序列的就用上B树的有序链表MySQL连排序这一步都可以跳过。第三条是理解B树之后很多面试题不再是背诵。我也曾经愣背过聚簇索引叶子节点存数据行二级索引叶子节点存主键背完就忘。后来自己在测试库里建了一张10万行的表手动跑了几个对照实验比如同样一条查询用主键查和用普通索引查的区别比如给索引列套一层函数看执行计划的变化。数据不会骗人那些原理在你亲手验证过一遍之后会变成一种本能反应。最后一个小建议如果你正在学MySQL索引别只盯着各种优化技巧看。技巧是有时效性的但底层的数据结构与算法是几十年不变的。花半天时间画一遍B树的分裂过程手动模拟一次复合索引的键排列比刷十篇索引优化手册都管用。理解了那棵树你对索引的理解就不需要靠记忆力了靠的是结构感。 MySQL索引原理与B+树：从慢查询到索引优化实践
从根上理解索引它只是把无序的数据变成有序的查找结构做后端这些年我见过太多因为索引问题导致的线上事故。最典型的一种业务跑着跑着某个接口突然变慢慢查询日志里发现一条SQL要扫描几百万行DBA一看执行计划根本没走索引或者走了个失效的索引。这时候大家第一反应通常是加个索引就好了但为什么加了索引有时还是慢为什么有些查询明明字段上有索引却不走为什么最左前缀原则总是记不住这些问题的答案都藏在MySQL索引的底层数据结构与算法里。MySQL索引、数据结构、算法这三样东西听起来像是大学课本里沉睡的知识点但实际上它们决定了你写的每一条SQL是毫秒级返回还是把数据库拖垮。理解它们不是为了让面试官满意而是为了让你在真正动手建索引的时候能基于原理做判断而不是靠猜、靠试、靠网上抄一段现成的方案。这篇博客不打算复述官方文档也不打算搞一堆术语堆砌。我会从一次真实的慢查询排查开始把B树、聚簇索引、二级索引、索引失效这些概念拆开揉碎配合实际操作和踩坑记录讲清楚为什么以及怎么做。适合正在写业务代码但经常被数据库性能问题困扰的后端开发者也适合准备深入学习MySQL原理的初学者。1. 先从一次慢查询说起1.1 一次接口超时引发的排查前两年接手过一个订单查询服务某天下午监控突然报警一个查询订单列表的接口P99延迟从80ms飙到了2.8秒。看慢查询日志定位到一条SQLSELECT order_id, user_id, amount, status, created_at FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;这条SQL看起来没有任何问题user_id字段也有索引这个我很确定因为建表的时候就加了但执行计划显示扫描行数是80多万Extra里有个一行字Using filesort。当时的第一反应是索引没建对吧SHOW INDEX FROM orders一看user_id上确实有普通索引idx_user_id。那为什么没走后来仔细查了数据分布才发现user_id12345这个用户的订单量本身就超过了10万条。对于优化器来说走idx_user_id需要回表10万次然后还要做一次磁盘上的filesort排序最后才取20条。它一估算成本发现全表扫描再加排序可能比走索引更快于是选择了全表扫描。这个案例特别典型它不是索引失效而是优化器基于成本模型做了最优选择。但问题在于filesort面对80万行数据性能确实扛不住。最终的解决方案是把(user_id, created_at)建成一个复合索引让索引天然支持按用户查订单并按时间倒序直接消除文件和排序。这个场景几乎每个做业务开发的人都遇到过。它的背后就是索引底层数据结构设计上的一个核心问题如何让数据在磁盘上也能快速检索和有序遍历。理解这个问题我们得先把索引的本质看清楚。1.2 索引的本质空间换时间的加速器索引的本质是一个独立于表数据的、额外的数据结构。数据库根据索引列的键值构建出一份经过排序的目录这份目录里只保存索引键和对应的物理位置或主键值占用的存储空间一般远小于原始数据。查询的时候数据库先在目录里快速定位到目标数据的位置再根据位置去数据文件里取具体的行。这很像新华字典的拼音索引你不会为了查一个字从第1页翻到最后一页而是先在拼音索引里定位到字所在页码一跳就过去了。索引存在的意义就是减少磁盘IO的次数因为一次磁盘IO的代价大约是内存访问的几十万倍。但是目录这个说法太笼统了。真正的数据库索引不是一个线性表也不是一个简单的哈希表而是一棵精心设计的树。为什么是树为什么最终选了B树而不是二叉树、不是哈希表这些选择背后全是算法和数据结构的权衡。接下来这章才是重头戏。2. 数据结构选型为什么偏偏是B树2.1 从二分查找说起按照正常的思维推导查找最快的办法是二分查找数据排好序每次取中间元素比较一次排除一半查找时间复杂度是O(logN)。二分查找的前提是数据在内存里是连续数组可以直接用下标访问。但数据库的数据存在磁盘上磁盘的最小IO单位是页Page通常为16KB。如果数据按页存放页与页之间并不是物理连续的你没法像数组一样随机访问第N个元素。更关键的是二分查找依赖随机访问这跟磁盘的机械特性冲突——磁盘寻道速度远远慢于顺序读取。所以数据库索引需要一种能够顺着指针往下走并且一次IO尽量读取更多有效数据的结构。这就把选项收敛到树形结构上了。2.2 二叉树、AVL树、红黑树为什么不合适二叉树Binary Search Tree理论上查找复杂度是O(logN)但它有一个致命问题极端情况下会退化成链表查找复杂度变成O(N)。数据库场景下插入数据是有序递增的比如主键自增那么以主键为索引的二叉树就会无情退化成一条右斜树跟全表扫描没区别。AVL树平衡二叉搜索树解决了退化问题通过旋转保持左右子树高度差不超过1保证查找稳定在O(logN)。但AVL的问题是每个节点最多只有两个孩子树的层数太深。如果数据量是1000万AVL树的层数大约在24层左右——别忘了每向下走一层就意味着大概率要读取一个磁盘页24次磁盘IO在任何场景下都是不可接受的。红黑树是近似平衡的二叉搜索树它允许左右子树高度差最多两倍虽然旋转次数更少、适合高频插入删除的场景但它的树高依然比B树深。内存里的TreeMap、std::map用红黑树没问题放在磁盘上还是太深。数据库索引的第一诉求是矮胖不是身材匀称。2.3 B树和B树从瘦高到矮胖的演变B树Balance Tree是多路平衡搜索树每个节点可以存储多个键值拥有多个孩子。同样1000万条数据如果每个节点能存放100个键B树的层数可能只有3~4层。这就意味着查询一个数据最多只需要3~4次磁盘IO巨大的进步。B树解决了矮的问题但还有一个性能痛点它每个节点都存储数据行或指向数据行的指针。如果数据体量很大每个节点能存放的键值数量就有限。而且做中序遍历范围查询时B树需要在中序递归或多次往返父节点和叶子节点顺序遍历性能很差。于是B树登场了。B树和B树的关键区别在于所有数据都存放在叶子节点非叶子节点只存放索引键值不存数据。这意味着非叶子节点能存放更多的键扇出更高树更矮。叶子节点之间通过双向链表相连形成一个有序的数据链。所有的查询最终都会落到叶子节点查询路径深度完全一致性能稳定。拿订单表来说如果单行数据200字节一个16KB的页大约能放80行如果是B树索引非叶子节点每条索引项大概8字节bigint主键一个页能放约2000个键。3层B树能存放大概80亿行数据的索引。这就是矮胖的威力。2.4 B树 vs Hash索引精确查询和范围查询的取舍还有一种选择是Hash索引基于哈希表实现查询时间复杂度接近O(1)听起来比B树的O(logN)强多了。但数据库里Hash索引从来只是配角因为Hash表无序完全无法支持范围查询BETWEEN、、。Hash表不支持最左前缀原则的复合索引优化。哈希碰撞会导致性能抖动。而B树的叶子节点天生有序范围查询只需要找到起点然后顺着叶子节点的链表往后遍历即可。这个特性让B树完全碾压Hash索引。Redis的ZSET用跳跃表能实现范围查询但跳跃表在磁盘场景下的空间利用率和IO效率不如B树所以也没被数据库采纳。2.5 一张图读懂B树为什么合适简单总结一下B树的三大核心优势这三点直接对应数据库索引的三大需求数据库需求B树的对应能力原理说明减少磁盘IO次数树高恒定且极矮非叶子节点只存键扇出高3~4层够支撑千万级数据高效范围查询叶子节点顺序链表找到边界后沿链表顺序遍历天然支持排序和区间扫描查询性能稳定每次查询都到叶子所有查询路径深度一致不会出现某些数据快某些慢也正是因为这些特性MySQL的InnoDB引擎选择了B树作为索引的默认数据结构。理解了这一点你再去回想那些加个索引查询就快了的案例脑子里就不只是一个模糊的印象而是能看到一棵具体的树。3. InnoDB里到底是怎么存的3.1 聚簇索引与二级索引的区别很多人以为索引是一个东西其实InnoDB里有两类索引区分它们对于理解SQL优化至关重要。聚簇索引Clustered Index是InnoDB表数据存储本身。每个InnoDB表都有一个聚簇索引数据行物理地存储在聚簇索引的叶子节点上。如果表定义了主键主键就是聚簇索引如果没有主键InnoDB会选第一个非空唯一索引作为聚簇索引如果都没有它会生成一个隐藏的6字节row_id作为聚簇索引。这就意味着聚簇索引的叶子节点存的是完整的行数据。你按主键查询走聚簇索引一次IO拿回一整行这就是回表的反面——不需要回表数据就在手里。二级索引Secondary Index也叫辅助索引则是独立于聚簇索引的、额外的B树。它的叶子节点不存储完整行数据只存储索引键值加上对应聚簇索引的主键值。比如你在user_id上建了普通索引这棵B树的叶子节点就是(user_id, 主键id)的集合。当查询条件用了user_id但还需要读取其他字段时流程是先在二级索引的B树里查到符合条件的主键id集合再拿着这些id去聚簇索引里回表取完整行数据。注意这里是一个逐行回表的过程涉及的id越多IO次数越多。这也是为什么有些查询虽然走了索引依然慢得离谱。3.2 复合索引和最左前缀原则的理解方式复合索引联合索引是指在一张表的多个列上创建的索引比如(user_id, created_at)。它的B树按第一列排序第一列相同再按第二列排序。B树的这个排序规则直接衍生出了最左前缀原则查询条件必须能匹配到索引的最左列否则索引无法生效。比如WHERE created_at ?单独查询是无法使用(user_id, created_at)这个复合索引的因为整棵树先按user_id排序created_at只是局部有序。但很有意思的一点是最左前缀不止是必须包含第一列这么简单。它还包括前缀匹配的连续性WHERE user_id 1 AND amount 100可以用到联合索引的user_id部分但amount的过滤是在索引内部做了索引条件下推ICP之后在回表前完成的而WHERE user_id 1 OR amount 100这种条件下优化器往往会选择其他策略因为OR条件无法保证两个列同时利用同一棵B树的排序结构。我见过不少同事把最左前缀当成死记硬背的面试题其实用B树的排序逻辑一想就通了你在字典里查拼音是yang且部首是氵是查不到的因为拼音索引里根本没有按照部首排序你必须先能定位到yang开头的区域最左列然后才能在局部区域里寻找第二条件。复合索引的列排列顺序决定了它就是一棵字典树。3.3 索引下推一个容易被忽视的优化MySQL 5.6引入了Index Condition Pushdown索引下推。在没有ICP之前使用二级索引查询时存储引擎会把所有满足索引键条件的记录都回表然后在Server层再过滤其他条件。有了ICP之后凡是能在索引内部完成的过滤都在索引层先做掉减少回表次数。举个例子SELECT * FROM users WHERE name LIKE 张% AND age 20;假设有联合索引(name, age)。没有ICP流程是先通过name LIKE 张%定位到一批主键id然后回表读每一行完整数据再判断age 20。有了ICP存储引擎在扫描索引的时候就会检查age 20只对符合条件的记录回表。这个优化对SQL的影响有时候是数量级的。用EXPLAIN查看时看到Extra列有Using index condition就说明索引下推被用上了。它不是一条配置不是一个命令是B树存储结构带来的一个天然红利——索引里本来就有列的数据不利用白不利用。4. 建索引的正确姿势从原理到实操4.1 最实用的索引类型选择先看一张InnoDB下索引类型的对比表索引类型使用场景底层结构备注主键索引每表一个聚簇索引B树聚簇叶子存整行建议自增或有序唯一索引保证列值唯一B树二级允许NULLNULL可重复普通索引加速查询B树二级最常用可以建多个复合索引多条件过滤/排序B树二级多键键按顺序排序有最左前缀全文索引文本内容检索倒排索引用于LIKE %xx%的替代方案但中文分词有坑哈希索引等值查询哈希表InnoDB无法手动创建由自适应哈希索引提供建索引的顺序有一个通用的方法论先分析业务查询的WHERE条件、ORDER BY条件、GROUP BY条件找出高频查询组合然后优先考虑复合索引而不是为每个列单独建索引最后用EXPLAIN验证执行计划。要注意的是给每个字段都建索引是最典型的反面教材。二级索引本身要占磁盘空间每次INSERT/UPDATE/DELETE都要同步维护索引树索引越多写入越慢。一张表动辄七八个索引写入性能迟早会出问题。4.2 一个合理的索引设计方案假设我们要为订单表设计索引业务上有三类核心查询按用户查订单并按时间倒序前面案例按订单状态查待处理的订单按商户查订单并按金额排序表结构大致是id, user_id, merchant_id, amount, status, created_at。参考设计方案如下ALTER TABLE orders ADD PRIMARY KEY (id), ADD INDEX idx_user_created (user_id, created_at), ADD INDEX idx_merchant_amount (merchant_id, amount), ADD INDEX idx_status (status);这里几个设计原因值得展开说说idx_user_created这棵复合索引的B树结构里叶子节点按user_id排序user_id相同再按created_at排序。所以WHERE user_id ? ORDER BY created_at DESC直接把排序结果从索引里读出来连filesort都省了Extra列会显示Using index condition不会出现Using filesort。idx_merchant_amount则是为了让ORDER BY amount也能走索引的有序性避免在内存排序10万行。idx_status单独建索引是因为status的区分度不高但它独立成索引后配合ICP可以在索引内过滤掉大部分不需要的行回表压力可控。4.3 用EXPLAIN验证你的设计写完索引后必须跑一遍EXPLAIN看执行计划这个习惯一定要养成。比如上面的例子EXPLAIN SELECT order_id, user_id, amount, status, created_at FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;你期望的结果是key显示idx_user_createdrows显示很小的值Extra里有Using index condition没有Using filesort。EXPLAIN的每个关键字段都要会看字段含义关键点type访问类型ALL全表扫描、ref非唯一索引等值、range范围扫描、const主键等值key实际使用的索引可能为NULL代表没走索引rows预估扫描行数越小越好但只是估算Extra额外信息filesort、temporary、index condition都可能在这出现4.4 实际执行后的优化效果还是拿开头那个案例说改成复合索引(user_id, created_at)之后执行计划中type从ALL变成refrows从80万降到几千行Extra不再有Using filesort接口P99延迟从2.8秒回到了60ms以内。这个效果看起来是加了个索引这么轻巧但实际上整个分析过程非常值得复盘原始索引idx_user_id本身没有失效而是它在等值过滤排序回表limit的综合成本下不占优势。真正解决问题的是让索引结构直接覆盖了排序需求这不只是多建一个索引是对查询模式做了一次结构性适配。5. 索引失效的常见场景与实战排查5.1 隐式类型转换这是最常见的坑没有之一。SELECT * FROM user WHERE phone 13800138000;phone字段是VARCHAR类型但查询值是整数。MySQL会隐式地把字符串字段转成数字再比较相当于对索引列做了类型转换函数导致索引失效。解决办法很简单查询值写成字符串13800138000。5.2 对索引列使用函数或计算SELECT * FROM user WHERE DATE(created_at) 2024-06-01;在索引列上套了DATE()函数B树里存储的是原始的created_at值函数处理后的结果无法直接与树中的键比较只能全表扫描。改进方式是改成范围查询SELECT * FROM user WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;这样既走索引又利用B树的范围扫描特性一举两得。5.3 LIKE以通配符开头LIKE %keyword会导致索引失效因为B树是按前缀顺序排列的无法在整棵树中定位以keyword结尾的键。但LIKE keyword%可以用索引因为前缀是确定的。如果业务必须做后模糊匹配可以考虑全文索引或者搜索引擎而不是跟B树死磕。5.4 OR导致的全表扫描SELECT * FROM user WHERE name 张三 OR age 30;即使name和age都有索引当OR的两个条件涉及不同索引时优化器无法把两个B树的查询结果做并集后去重这是传统B树做不到的往往直接选择全表扫描。解决办法是拆成两个查询用UNION合并或者改成用UNION ALL再在应用层做去重。5.5 复合索引列顺序与查询条件不匹配最左前缀原则失效的主要场景比如索引是(a, b, c)查询条件是WHERE b ?或WHERE c ?直接从第二列开始用索引完全帮不上忙。还有一种更隐蔽的WHERE a ? AND c ?索引只用到了a列c列会在回表后用ICP过滤或者如果MySQL认为回表成本高可能就不走索引了。5.6 区分度太低的选择性陷阱博客、文章这种表上建is_deleted这个布尔列的索引典型低区分度。如果表里90%的数据都是is_deleted0优化器一算发现走索引回表还不如聚簇索引顺序扫描快它会直接放弃索引。这种情况下别怪MySQL它不是笨是算清楚了成本。真正的解法是重构查询模式比如把有效数据和历史数据分开存储或者用(status, created_at)这种带顺序的复合索引而不是只建一个单列状态索引。5.7 一个排查索引失效的通用流程排查慢SQL时我一般按照这个顺序操作用EXPLAIN查看执行计划先确认type是不是ALLkey是否为NULL。如果走了索引但还是慢看rows和Extra确认是否回表次数过多或存在filesort、temporary。检查查询条件中的列与索引列是否类型一致SHOW CREATE TABLE看字段类型。检查是否在索引列上使用了函数、运算、隐式转换。检查复合索引的列顺序是否匹配查询条件的最左前缀。如果是范围查询确认优化器有没有选错索引可以用FORCE INDEX做对比测试但千万别在线上长期用FORCE INDEX。这样一步步排查下来90%的索引问题都能定位到根本原因。6. 从原理到实践的个人体会写到这里我想分享几条自己踩过坑后最深的体会。第一条是索引不是越多越好而是越精准越好。一张表建了8个索引看着每个查询都有索引可走可代价是每插一条数据要维护8棵B树写入磁盘的IO次数直接翻好几倍。最终表现为数据库CPU不高但磁盘IO持续打满。建索引之前先列出所有高频查询确定每个查询需要的索引键能复用就复用绝不为了个别接口单独加索引。第二条是filesort不根除慢查询迟早回来。凡是线上出现过Using filesort的SQL我非常不建议只靠临时加索引解决而是思考这个操作的本质是什么——排序需求是否可以由索引结构天然承担比如ORDER BY字段能不能加入复合索引的尾部如果能排序列的就用上B树的有序链表MySQL连排序这一步都可以跳过。第三条是理解B树之后很多面试题不再是背诵。我也曾经愣背过聚簇索引叶子节点存数据行二级索引叶子节点存主键背完就忘。后来自己在测试库里建了一张10万行的表手动跑了几个对照实验比如同样一条查询用主键查和用普通索引查的区别比如给索引列套一层函数看执行计划的变化。数据不会骗人那些原理在你亲手验证过一遍之后会变成一种本能反应。最后一个小建议如果你正在学MySQL索引别只盯着各种优化技巧看。技巧是有时效性的但底层的数据结构与算法是几十年不变的。花半天时间画一遍B树的分裂过程手动模拟一次复合索引的键排列比刷十篇索引优化手册都管用。理解了那棵树你对索引的理解就不需要靠记忆力了靠的是结构感。