索引这个词在 MySQL 里被讨论的频率我估摸着仅次于“慢查询怎么排查”。但我见过太多人张口就是“给表加个索引就行了”真问一句“为什么加了索引能快这么多”又说不上来。尤其当你面对一张千万级数据量的业务表加一个索引可能要跑几分钟加错了还得删删完又要等 DDL 执行线上事故往往就是这么来的。下面这把 InnoDB 索引的完整链路——底层原理、B树的演进、建索引的实操方法以及我实际排查索引问题时的套路——逐一拆开讲适合刚接触数据库的学生、准备面试的开发者也适合每天被慢 SQL 折腾的后端工程师。1. 为什么 MySQL 要选B树做索引从无序扫描聊起1.1 全表扫描到底有多慢严格来说InnoDB 里一张表并不是一堆松散的数据行它本身就是按主键顺序组织的一棵索引树这叫索引组织表。可一旦你查询的列上没有可用索引MySQL 就只能走全表扫描。你可以想象成在一本没有目录的字典里找一个字从第一页开始翻一直看到最后一个字为止。全表扫描最伤的地方不在“行数多”而在“数据页的读取没有顺序性”。InnoDB 默认每个数据页 16KB一张千万行的表光数据页就几百 MB 甚至上 GB。如果这些页不在 buffer pool 里每一次读页都可能是一次真实的磁盘 IO。机械硬盘的寻道时间在十几毫秒级别这个数字乘以几千页结果就是慢查询日志里那些一两秒甚至更久的大哥们。而即使数据全部被缓存到内存里全表扫描依然不划算。每一行都要做一次条件比对几百万行的 CPU 开销非常可观。所以索引要解决的核心问题其实是如何用尽量少的磁盘访问和比对次数快速定位到目标行所在的页。注意我见过有人把慢查询优化押在“把表全放进内存”上这确实能缓解一部分磁盘压力但全表扫描要一行一行比对的开销依然还在。索引的价值是让 MySQL 根本不需要碰那几百万行。1.2 索引的本质空间换时间的导航系统如果把数据表比作一座城市里所有门牌号索引就是一套导航系统。它把你关心的字段值单独拿出来按规则排好序并为每个值记下对应的主键或行位置。查询来的时候先在这一小套导航数据里做查找定位到目标后再精准地去取那一小片数据。代价当然有。第一索引要占用额外的磁盘空间数据量大的表几个大索引加起来比表本身大并不稀奇。第二写入开销。每插入一行、每更新一个索引列、每删除一行MySQL 都要同步维护这些索引结构。这意味着“读多写少”是索引的舒适区而在频繁写入的表上无脑加索引就是在给自己挖坑。1.3 为什么不直接上哈希表或红黑树很多没读过源码的同学听到“索引是数据结构”第一反应就是哈希表。哈希表做等值查询确实厉害WHERE id 123这种场景下平均复杂度 O(1)。但它的弱点同样致命数据是无序的。一旦你要是写WHERE age BETWEEN 20 AND 30或者ORDER BY create_time哈希表就只能把整个表扫出来再做一次排序性能直接崩掉。业务查询里范围查询、排序、分组是躲不掉的所以哈希索引在 MySQL 里只作为自适应哈希索引存在是存储引擎自己偷偷用来给热点等值查询加速的不是用户随手能建的主流索引。那红黑树呢红黑树确实解决了二叉搜索树会退化成链表的问题把高度稳定控制在 O(logN)。但注意一个数字一个节点只有一个 key分叉是 2。一千万条数据红黑树高度大概要 24 层也就是说一次查询可能要访问 24 个节点。而 InnoDB 一次 IO 读一个页如果每个节点分布在不同的页里就是 24 次磁盘 IO。在磁盘面前这个代价没人受得了。解决方向自然就变成了让一个树节点能装更多 key让树变得更矮更宽。B 树和 B树就是顺着这个思路演进的。2. B树的演进之路与底层数据结构详解2.1 先搞懂树高和磁盘 IO 的换算关系在说 B树之前要建立一条最重要的换算关系一次查询访问的磁盘页数约等于索引树的路径长度。一个页在 InnoDB 里是原子 IO 单位树每往下走一层大概率就要读一个新的数据页。所以树高每降一层省下的可能就是一个量级的查询延迟。InnoDB 默认页大小 16KB这是 MySQL 8.0 里最常见的配置。假设索引键是 8 字节的 bigint再加 6 字节的行指针之类的开销一个非叶子节点大约能塞下一千多个索引项。如果我们把树做成三层第一层根节点能指向约一千个中间节点每个中间节点又能指向约一千个叶子页这样光是中间层就可以覆盖上百万个叶子页每个叶子页如果按 1KB 左右的记录大小能放十几行数据。三者一乘一条三层 B树容纳千万级甚至上亿条记录完全不在话下。这就解释了为什么你听 DBA 说“三层足够了”对于绝大多数业务表B树从根到叶子只需要三次磁盘 IO依次是根节点通常常驻内存、中间层节点、叶子页。查询想不快都难。2.2 B树做错了什么从 B树到 B树的关键改进很多人误以为 MySQL 用的是 B 树。准确地说MySQL InnoDB 用的是 B树两者属于同族但不同的数据结构。B 树的节点既存索引键也直接存对应的数据行或行地址好处是查询命中某个节点时当场就能拿到数据。坏处是数据很占空间每个节点塞不了几个 key。一个 16KB 的节点如果塞满了 key 加一两条大记录可能几百个 key 都装不下。节点扇出变小为了容纳同样多的数据树的高度就会被迫增加磁盘 IO 次数自然变多。B树做出的关键改进是把中间节点的定位功能和叶子节点的存储功能彻底分开中间节点只存索引键和下一层节点的指针不存真实数据真实数据或主键只放在最底层的叶子节点里。这样中间节点能塞下的 key 数量比 B 树多得多树高进一步被压低。代价是极端情况下一次查询要从根走到叶子才能拿到数据中间节点永远拿不到真实数据。但和那一点点查询路径的延长相比树高压低带来的收益完全不是一个量级。这也是“B树到底是不是红黑树”这个总有人会问的问题的答案不是它们是两条完全不同的技术路线。2.3 叶子节点链表范围查询的隐形杀手锏B树还有一个常被一笔带过的设计亮点——叶子节点之间用双向链表实现了按 key 的有序串联。别忘了B 树在做范围查询时需要在树的中序序列中来回跳跃找后继节点这一跳就可能触发新的磁盘 IO。而 B树把所有叶子节点用链表串起来之后一旦定位到范围起点剩下的连续取数据只要顺着链表往下走就行查询天然高效。这个特性直接在 MySQL 中落地成两个优势。第一个就是BETWEEN、、这样的范围查询数据库可以顺着链表顺序读取而不是反复从根节点重新发起搜索。第二个是排序和分组因为叶子节点本身就是按索引键有序的读取顺序直接满足ORDER BY的要求多数情况下可以省掉 filesort。这也是为什么索引能“覆盖”排序需求——前提是你的ORDER BY字段和索引列顺序能匹配上。2.4 聚簇索引和二级索引谁真正存了数据InnoDB 里的索引不是孤立的它和表的物理存储强绑定。主键索引就是聚簇索引聚簇索引的叶子节点直接存储整行数据。你建表时如果没显式指定主键InnoDB 会找一个非空唯一键当主键再找不到就偷偷生成一个内部隐藏主键。换句话说InnoDB 表里的数据本身就是一棵以主键为顺序的 B树。而你在其他列上建的普通索引、复合索引都属于二级索引。二级索引的叶子节点不存整行只存“索引列的值 主键值”。查询时先通过二级索引找到主键再拿着主键去聚簇索引里捞完整数据这个过程就叫回表。这也是为什么覆盖索引会特别香如果SELECT后面要的列恰好都在二级索引里MySQL 连回表都省了直接在索引页上把数据取完EXPLAIN 的 Extra 列里会出现一个Using index。这里我还想提醒一句二级索引里存主键值而不是物理行地址是刻意的设计。数据页会分裂、合并、迁移如果索引直接存了物理地址一次行迁移就得改一大堆索引项。存主键值则非常稳定代价就是回表时多一次按主键查找但这点开销完全在可接受范围内。3. 索引操作实战建索引也有门道3.1 三种创建索引的姿势实际操作中建索引的方式无非这三种-- 1. 用 CREATE INDEX 创建 CREATE INDEX idx_user_name ON user(name); -- 2. 用 ALTER TABLE 增加索引 ALTER TABLE user ADD INDEX idx_user_age (age); -- 3. 建表时就声明索引 CREATE TABLE user ( id BIGINT PRIMARY KEY, name VARCHAR(50), age INT, INDEX idx_user_name (name) ) ENGINEInnoDB;从功能上三者没有本质区别。线上环境我最常用的是第二种因为可以顺便指定ALGORITHM和LOCK选项来控制 DDL 的锁策略。比如 MySQL 8.0 下默认大多数索引创建操作已经支持INPLACE算法不会长时间锁表但大表跑 ALTER 也会产生不小的 IO 压力。我会挑业务低峰期执行并先用pt-online-schema-change之类的工具评估这属于进阶话题后面再展开讲。查看索引有没有建成功用SHOW INDEX FROM user;就能看到索引名、列名、区分度等一系列信息。删除索引则用DROP INDEX idx_user_name ON user;删除操作通常会很快但要确认没有 SQL 依赖它否则删完第一时间就会有一堆慢查询冒出来。我自己的习惯是删之前先跑一遍EXPLAIN把可能受影响的 SQL 记下来逐个确认。3.2 复合索引和最左匹配原则复合索引可以说是面试和实战里都绕不开的内容。拿“where 条件 a and b应该怎么建索引”这个问题来说答案不是给 a、b 各建一个单列索引而是优先考虑建一个 (a, b) 的复合索引。为什么因为复合索引的本质是先把 a 排好序a 相同的记录再按 b 排序所以它天然支持“先按 a 精确或范围定位再按 b 继续定位”的查找路径。最左匹配原则说的就是查询条件必须从复合索引的第一个列开始匹配才能用上这个索引如果条件里只有 b没有 a那索引对 b 的排序就派不上用场优化器很可能放弃索引。-- 建一个复合索引 ALTER TABLE user ADD INDEX idx_name_age (name, age); -- 能命中索引 SELECT * FROM user WHERE name zhangsan AND age 18; SELECT * FROM user WHERE name zhangsan; -- 不能命中或只能部分命中 SELECT * FROM user WHERE age 18; SELECT * FROM user WHERE name zhangsan OR age 18;这里有一个很实战的经验复合索引的列顺序不要随意排。等值条件放在前面范围条件放后面是一种比较通用的准则。因为 B树的叶子节点先按第一列排再按第二列排如果第一列是范围条件第二列的排序价值就会大打折扣。比如WHERE status 1 AND create_time BETWEEN ...把 status 放第一列create_time 放第二列效果通常最好。3.3 设计索引时该看哪些指标建索引之前建议先回答三个问题这个查询是不是高频区分度高不高索引带来的写入成本能不能接受区分度是最直观的指标。一个列如果有 1000 万行但值只有“男”“女”两种区分度低到接近零B树虽然能建但优化器扫完索引和扫全表没啥本质区别经常直接走全表。反之像手机号、订单号这种几乎每行都不重复的列索引效果就很理想。想量化区分度可以用COUNT(DISTINCT column) / COUNT(*)值越接近 1 越好。长字符串字段要小心。VARCHAR(255) 甚至更大的列上直接建全列索引索引页会膨胀得很快树高也可能被迫增加。常见解法是前缀索引只取前 N 个字符建索引比如INDEX idx_title (title(20))。代价是前缀索引无法覆盖ORDER BY和完整匹配算是一个取舍。如果你选前缀长度可以先用COUNT(DISTINCT LEFT(title, 20))和总行数对比找到一个区分度损失可接受的阈值。还有一个容易忽略的小细节不要让 MySQL 在索引列上做隐式类型转换。最常见的就是 VARCHAR 列在 WHERE 里不加引号比如WHERE phone 13800138000MySQL 会对索引列做类型转换索引直接失效。这是我见过线上出现频率极高的低级事故后面排查章节我会再提。3.4 索引失效的典型场景提前打预防针我把索引设计原则和失效场景放在同一节是因为它们本质是同一件事的两面。明确说几个常见的失效场景很多人踩过失效场景底层原因解决思路LIKE %abc前导模糊B树无法从字符串中间开始定位改成LIKE abc%或考虑全文索引WHERE YEAR(create_time) 2024索引列被函数包住B树里存的是原始值改成create_time BETWEEN ...范围写法WHERE name a OR age 18age 无索引OR 要覆盖全部可能分支只能全表给 age 补索引或拆成 UNION ALL复合索引跳过第一列叶子节点排序顺序失去前缀支撑调整查询条件或重建复合索引WHERE phone 13800138000且 phone 是 VARCHAR隐式类型转换破坏索引列原值查询参数加引号很多同学会背这些规则但我要强调索引失效看的是最终执行计划不是背出来的。同一句 SQL可能因为数据量分布、统计信息变化而出现不同的执行计划。所以判断标准只有一个EXPLAIN 里 key 列有没有值rows 多不多。别凭感觉用数据说话。4. 索引问题排查与 EXPLAIN 实录4.1 我只挑这几个重点字段看线上排查慢查询我基本不会把 EXPLAIN 返回的每一列都研究透因为大部分字段对定位问题帮助有限。真正要盯死的就这几个type从好到坏大致是systemconsteq_refrefrangeindexALL。看到ALL基本就是没走索引的全表扫描看到index说明走了索引但可能是全索引扫描依然不理想range或ref属于可以接受的水平。key实际使用的索引名。如果为 NULL说明这条语句没用到任何索引问题基本锁定。rows预估扫描行数。它不是精确值但数量级信息很有价值。从几十万行变成几百行这中间的差距就是索引带来的收益。Extra出现Using filesort说明结果集额外排序了Using temporary提示内部临时表重则直接拖垮性能Using index是覆盖索引的正面反馈Using where则可能是做了部分条件过滤。每次排查查询我都会先看这几个字段再用rows的变化验证索引是否真正生效。如果type是ALL但rows只有几百行完全不用折腾数据量小的时候全表扫描往往比走索引更快优化器并不是傻子。4.2 一个真实的慢查询排查过程之前在一个电商后台遇到过一条从订单表里统计某天成交量的 SQL跑了三秒多。表结构简写一下SELECT COUNT(*) FROM trade_order WHERE status 3 AND create_time BETWEEN 2024-05-01 00:00:00 AND 2024-05-01 23:59:59;表里有几百万行status 和 create_time 都各自建了单列索引。EXPLAIN 看下来MySQL 只用了 create_time 的索引去圈范围然后在内存里对 status 3 做过滤。数据量大时回表加内存过滤的开销一下子就上去了。我当时的做法是把两个单列索引合并成一个复合索引(status, create_time)因为 status 是等值条件放前面create_time 是范围条件放后面。改完之后MySQL 先按 status 过滤掉大部分行再在索引页上顺着 create_time 的顺序扫扫描范围大大缩小。这条 SQL 从三秒降到了几十毫秒整个过程只改了一个索引。这里有个容易被忽略的前提复合索引建完之后记得把没有用的单列索引删掉。否则写入时还是要维护三套索引数据量一大吞吐量立刻受影响。删除之前再用 EXPLAIN 验证一下确认没有其他 SQL 依赖这几个旧索引。4.3 排查工具之外还有几个实操习惯除了 EXPLAIN我实际工作中还有几个固定习惯算是踩坑踩出来的经验。第一慢查询日志一定要开。MySQL 的long_query_time设为 1 秒或更短定期把慢 SQL 捞出来批量看。没有这一步你根本不知道线上哪些 SQL 在拖后腿优化自然无从谈起。第二改索引前先做影响面评估。用 information_schema 查一下这张表被哪些存储过程、视图、ORM 映射引用改一个索引顺序可能让所有单列查询全部失效。不要问我怎么知道的线上事故往往就是这么来的。第三大表建索引要趁低峰期DDL 虽然很多已支持INPLACE但依然会产生 IO 峰值。如果表实在太大建议用pt-osc或gh-ost这类工具在线变更先把索引建到临时表上通过 binlog 回放旧表的新写入最后再切换能最大程度减少锁等待时间。5. 写到最后几条实在的经验之谈5.1 索引数量不是越多越好我遇到过把一张表所有查询条件都建上索引的“优化小能手”表只有 30 万行索引建了七个。结果查询没快多少INSERT 和 UPDATE 的响应时间反而肉眼可见地涨了上去。每次写入都要同步维护这些索引buffer pool 里也全是索引页热数据反而被挤了出去。后来删掉三个低频索引整体性能反而更稳了。索引这东西宁缺毋滥。5.2 善用覆盖索引和统计信息覆盖索引算是我最愿意主动加的索引类型。它不只能省回表还能让查询完全在索引页上完成IO 次数急剧下降。特别是那种频繁执行但只需要几个固定列的统计类查询一个覆盖索引往往是最优解。另外也别忘了定期执行ANALYZE TABLE更新统计信息MySQL 优化器是根据统计信息选索引的统计信息太旧再好的索引也可能被优化器无视。我个人习惯是每次大版本升级或批量导数据之后都顺手跑一次投资很小收益却很稳。