聊起“索引”很多写了好几年业务代码的同行其实都处于一种“会用但说不透”的状态。加个索引查询从几秒变成几毫秒大家都会拍手叫好但要是追问一句“索引凭什么这么快”能讲清楚的人就不多了。这恰恰是最要命的地方——你不懂索引的原理就只能在“失效了、走错了、要回表了”这些坑里反复打转排查一条慢SQL全靠试。今天我用一篇尽量说人话的文章把索引提速的底层逻辑从头到尾捋一遍顺便把我实践中踩过的那些和索引有关的坑也一并交代清楚。这篇文章适合刚接触数据库没太久、想系统理解索引原理的同学也适合工作了几年但一直靠背结论撑场面的朋友。1. 查询慢的根源磁盘IO是躲不掉的第一瓶颈1.1 全表扫描到底在干什么先想一个问题一张表没有索引时数据库执行select * from t where c 100是怎么做的它会把这张表从头到尾读一遍每一行都拿出来判断一下c这一列的值是不是100。这个过程叫全表扫描。听起来好像也没多大事但你得把“一张表”这三个字具象化。MySQL InnoDB存储引擎里表数据是存在一个个数据页里的一个页默认16KB。假设一行数据1KB一个页能放16行100万行的表大概需要6万多个数据页。全表扫描就意味着要把这6万多个页全部从磁盘搬进内存再逐行比对。这里面每一页的读取都对应一次磁盘IO。磁盘IO有多慢我给你一个直观的对比内存随机访问大约几十纳秒而磁盘随机读一次大约需要10毫秒。10毫秒看起来也不长但按纳秒对比那是几十万倍的差距。6万个页就是600秒的量级——当然操作系统有缓存、顺序读有优化实际不会这么极端但量级大家心里要有数。所以在数据库这个场景里优化查询首要目标就是减少磁盘IO次数。谁能用更少的IO把数据捞出来谁就是好方案。索引之所以快本质上就是它大幅减少了需要读的页数量。1.2 为什么有序是解决问题的钥匙如果表里的数据是“排好序的”事情就不一样了。假设c列是从小到大排好的我想查c100这一行不需要从第1行看到最后一行直接看中间那行就行了——中间偏小说明目标在左半边中间偏大说明目标在右半边每次把范围砍半这个操作叫二分查找。100万行用二分查找最多20次比较就能定位到目标。但这里有个问题数据放在一张表里物理存储是“页行”的结构你怎么让它在逻辑上时刻保持有序每次插入、删除、更新都去挪动整表数据代价是不现实的。所以数据库发明了索引把“数据本身有序”改成了“额外维护一份只有关键信息的有序结构”。这个结构就是B树。注意这里请记住一个核心结论——索引的本质就是一份独立于表数据的“有序查找目录”。它牺牲一部分写性能换来查询阶段从“全表遍历”变成“沿目录定位”IO次数从“页数”变成“树高”。2. B树为什么能成为索引的代名词2.1 认识B树的结构B树是一种多路平衡查找树。说人话就是它不是二叉树而是一个节点里可以放很多孩子、很多键的“胖树”。一个典型的B树大概是这个形态最上面是根节点存放几个键和指向下一层的指针中间是非叶子节点存放用于路由的键值最底层是叶子节点真正存放数据主键值或整行数据所有叶子节点通过双向链表串在一起。你可以把它想象成一家超大型图书馆的索引系统根节点相当于“按楼层找区域”中间节点相当于“确定书架号”叶子节点就是“找到书位”。三层定位完事。这里面的关键在于“矮胖”。树越高每次查找要走的层数就越多也就是磁盘IO越多。二叉树存100万条数据树高大概20层等于最坏20次IOB树一个节点能存上千个键存100万条数据树高只有3层最坏3次IO。这就是B树碾压二叉树的最大优势。2.2 一个节点能放多少键由页大小决定InnoDB中索引树的每一个节点本质上就是一个数据页大小固定16KB。我们计算一下扇出一个节点能放多少个键假设主键是BIGINT8字节指针6字节左右一行索引条目算14字节。16KB除以14字节差不多能放1170个键。树的第二层、第三层同理。我们反推一下三层B树根节点1个页第二层1170个页第三层1170×1170 ≈ 137万个叶子节点。每个叶子节点按16KB、每行1KB算能放16行记录。总记录数137万 × 16 ≈ 2192万。你看一个三层的B树就能支撑两千多万行数据。也就是说从根节点一路走到叶子节点只需要3次磁盘IO。而且InnoDB在InnoDB在根节点通常都在缓冲池里实际只有2次物理IO。这就解释了为什么索引能让千万级表的单点查询稳定在毫秒级。2.3 叶子节点用链表串起来专门服务范围查询除了单点查找B树对范围查询也非常友好。这就要感谢叶子节点之间的链表了。比如你要查where c between 100 and 200。B树先二分找到100的所在位置然后沿着叶子链表往后扫一路扫到200为止。整个过程不需要回到树的上一层不需要反复从根节点重新定位等于一次定位顺序扫描。这个设计有多实用MySQL优化器在做范围扫描range scan时用的就是这条叶子链表。反观普通的二叉树范围查询要频繁回溯父节点效率差一大截。B树在数据库这个场景里几乎是量身定制的结构Red-Black Tree也好、Hash也罢都替代不了它这种“单点快范围顺磁盘友好”的组合拳。实操心得搞懂B树结构后很多看起来古怪的现象都能解释。比如为什么where a 1 order by b有时候不额外排序因为索引本身就是按a、b顺序排列的扫描结果天然有序。这就是“索引即排序”的由来后面讲复合索引时还会再展开。3. 聚簇索引与回表InnoDB特有的选择题3.1 主键索引和非主键索引存储的东西不一样InnoDB是聚簇索引组织表。这句话翻译成人话就是表里的数据本身就是按主键顺序组织起来的主键索引的叶子节点直接存“整行数据”。这张表所有的行数据就长在主键索引这棵B树的叶子节点上。所以一张表只能有一个聚簇索引——物理上数据只能有一种排法这一点是由底层决定的。那么非主键索引也叫二级索引呢它的叶子节点存的不是整行数据而是“主键值”。什么意思就是说普通索引只保存索引列和主键列的对应关系。这就带来一个非常重要的操作——回表。假设表里有索引idx_name(name)你要执行select * from t where name 张三。MySQL先在idx_name这棵B树里定位到name张三对应的叶子节点拿到主键id然后再拿着这个id去主键索引里再查一次整行数据。这个“查完二级索引又回头查主键索引”的过程就叫回表。回表好吗不好。每回一次表就多一次磁盘IO。如果查出来的行数有成百上千条回表次数就非常可观。这也是为什么有时候走索引反而比全表扫描还慢——全表扫描顺序读回表则是大量随机读。3.2 为什么二级索引存主键而不存行地址很多刚接触数据库的同学会问二级索引为什么不能直接存一个“行地址”指针这样找到索引直接拿指针取数据岂不快很多这个问题的答案藏在数据移动的场景里。InnoDB的数据页会发生页分裂插入时页满了要拆成两个页、页合并、行迁移行数据并不总在固定的物理位置。如果二级索引叶子节点存的是物理地址一旦数据挪了位置所有相关二级索引都得跟着改维护成本极高。但如果存的是主键值数据再怎么挪位置主键值不变二级索引完全不用动只是回表时多一次按主键的查找。这就是“逻辑指针”和“物理指针”两种设计路线的取舍。InnoDB选了逻辑指针用一次回表的代价换来二级索引维护上的巨大简化。理解这一点你就不会问出“为什么不直接把整行复制进二级索引”这种问题了——那样二级索引的体积会爆炸而且主键更新时所有二级索引都要同步修改彻底得不偿失。3.3 覆盖索引绕过回表的完美方案既然回表费IO能不能干脆不回能这就是覆盖索引的用法。覆盖索引是指查询的列全部包含在某个索引中时MySQL可以直接从这个索引的叶子节点拿到所有需要的数据不需要再回表。听起来很高端其实落地很朴素。举个例子表里有索引idx_name_age(name, age)执行select name, age from t where name 张三。因为name和age这两列都在索引里二级索引的叶子节点虽然存的是主键但索引列本身也存在于节点中。MySQL在读索引叶子节点的时候就已经把name和age的值取出来了主键都懒得用它。覆盖索引对性能的改善很恐怖。二级索引通常比聚簇索引小得多同样一个页能放更多索引记录扫描的页数更少再加个省掉回表IO次数直接腰斩不止。所以写SQL时我会刻意让select的列尽量“贴”着现有索引而不是无脑select *。这属于零成本优化改个查询列就能吃到红利。4. 复合索引与最左前缀原则4.1 复合索引的排序规则决定了最左前缀很多开发对复合索引的理解是“我建了(a,b,c)三个列的组合索引是不是a、b、c分别都能走索引”答案是“一部分能”。复合索引的B树排序规则很朴素先按第一个列排序第一列相同的情况下按第二列排序第二列也相同再按第三列排序。拿(a,b,c)举例索引里的数据先按a全局有序a相同的那段里按b有序b也相同的那段按c有序而不同a之间的b和c其实是不保证有序的。这就推导出了最左前缀原则where a ?能走索引。where a ? and b ?能走索引。where a ? and b ? and c ?能走索引。where b ?走不了因为b的全局有序性不存在索引不知道往左还是往右走。where a ? and c ?a能走但c那部分用不上索引因为缺少b做中间层c在a范围里不是有序的。我常给团队同学打一个比方复合索引就像查通讯录先按姓排序同姓再按名排序。你知道“姓名”查到具体联系人没问题但你只报一个名“张伟”全城叫张伟的有几十个你没法确定翻到第几页。只有姓没有名也能翻到姓的那一区但那只是“缩小范围”不是精确定位。4.2 写复合索引时选对列的先后顺序这可能是开发中最容易范迷糊的地方。我的实战原则很简单第一区分度高的列放前面。比如用户表性别只有两个值区分度低手机号几乎不重复区分度高。(gender, phone)这个顺序gender会把数据分成两半phone再在各自半区里找效果和单独用phone差不多反过来(phone, gender)phone直接定位到那一行gender根本没必要参与索引。所以区分度低的列放前面纯属浪费索引空间。第二高频查询优先。先看代码里最常见的where条件组合是什么。如果where user_id and status出现频率远高于其他组合就优先保证user_id在索引第一位。第三考虑排序需求。order by的列如果能和where条件共用索引可以省掉一次filesort。比如where a ? order by b用(a,b)复合索引a定值后b天然有序排序直接免了。第四尽量少建冗余索引。有了(a,b,c)再建(a)就是重复建设因为(a)本身就是(a,b,c)的最左前缀。这类冗余索引除了占用空间、拖慢写入没什么实际价值。4.3 索引失效的坑我基本都踩过说点实际的。聊索引失效并不是索引这个数据结构“失效”了而是SQL的写法导致优化器没办法沿B树走正常的二分定位。常见几种情况我都中过招列出来给大家避坑对索引列使用函数。where DATE(create_time) 2024-01-01索引列被函数包裹后原本有序的create_time变成了一堆经过函数加工的值B树找不到顺序关系只能全表扫描。正确写法是用范围条件where create_time 2024-01-01 and create_time 2024-01-02。隐式类型转换。表里phone是varchar类型你写where phone 13812345678数字传给字符串列MySQL会自动把列转成数字再比较这一步等价于对索引列用了函数索引直接失效。like %abc。前缀模糊本来就破坏了索引的有序性B树没法从中间开始“二分寻找“abc”的位置。但like abc%没问题因为它利用的是前缀有序。or连接非索引列。where a 1 or b 2哪怕a有索引优化器也得考虑b那条分支最终可能放弃索引改成全表扫描后合并。这种场景改成union all是更稳妥的写法。not in、!。索引本身就是用来快速“定位相等或范围”的不等于的语义是“排除一个点剩下全是目标”扫描索引树的意义不大优化器往往直接走全表。5. 索引不是银弹成本与设计建议5.1 索引也有“维护税”索引提升查询不是没有代价的。每一次insert、update、deleteInnoDB不仅要更新数据页还要同步维护这张表上的每一个二级索引。也就是说索引越多写放大越严重。你在user表上建了8个索引插入一行数据就要往8棵B树里插入对应的索引记录。此外索引本身要占磁盘空间。一个二级索引就是一棵完整的B树几千万行的表索引体积轻松超过数据体积的30%到50%。磁盘这东西云上都是钱。所以“能加索引就加索引”这种想法是不对的。正确的姿势是把这张表的高频查询列出来找出区分度高、组合起来能覆盖最多场景的少数几个复合索引然后果断把冗余索引删掉。5.2 用慢查询日志和explain来定索引方案什么时候该建索引什么时候该调索引不能拍脑袋。我的调试流程一般是这样的第一步打开慢查询日志把超过1秒的SQL捞出来。这一步能把“哪条语句该优化”定位到具体目标。第二步拿到慢SQL之后用explain看执行计划。重点关注这几列type从好到差一般是const eq_ref ref range index ALL。如果看到ALL基本确定全表扫描。key实际用了哪个索引。如果为null说明没走索引。rows预估扫描行数。这个数字越接近最终返回行数越好。Extra出现Using filesort说明排序没走索引出现Using temporary说明用了临时表都要重点关注。第三步根据explain的结果反推索引设计。如果是where条件导致的低效考虑加复合索引如果是排序导致的低效想法把排序列并进现有索引。5.3 什么时候即使有索引也不该用还有一种情况容易让人困惑明明建了索引优化器却偏偏不走。这通常是因为优化器算了笔账觉得走索引反而更贵。比如一张只有几百行的字典表全表扫描也就读一两个页走二级索引反而要“索引查找回表”IO次数更多。优化器又不傻它根据统计信息直接选了全表扫描。这时候别急着怪索引失效先看看表大小和区分度心里就清楚它为什么不走了。还有一种典型场景是select * 二级索引 返回结果集占全表比例较高。优化器估算回表成本太高也会放弃索引转全表扫。这时如果你改成覆盖索引查询减少回表成本优化器就又会走索引。所以查询性能优化不光是建索引连SQL长什么样都有关系。6. 关于索引我最后想说的话在这行干了这么多年我和索引打交道的时间远比想象中多。索引说到底是数据库给开发者的一个“有序化工具”你用得好SQL如丝般顺滑你用不好慢查询、锁等待、磁盘暴涨轮番找上门来。我个人印象最深的一次排查生产环境有一条SQL平时跑得飞快某天突然慢到几十秒。explain一看明明索引还在但rows扫描量暴涨。后来发现是因为表里某列数据的重复度发生了变化优化器基于过期的统计信息做出了错误选择。解决方式很简单ANALYZE TABLE重新收集一下统计信息就好了。这种坑很难提前预防但有一条经验是通用的索引不是一次建好就一劳永逸的数据分布变了、查询模式变了你得回头看看。每过几个月我会从慢查询日志里重新捞一遍Top 10语句看看有没有新的优化空间然后果断删掉那些“当年有用如今吃灰”的冗余索引。这个过程很琐碎但收益是实打实的。最后再分享一个小技巧建索引时别只看开发环境的执行计划一定要在生产库的备份库上先explain一遍。生产环境的数据量、数据分布和本地测试环境完全不是一回事一个小小的统计信息差异就可能让索引方案从天堂掉进地狱。做技术嘛最怕的不是不懂原理而是拿自己的运气和线上环境赌概率。