写完这篇笔记的时候我正在帮朋友排查一条卡了十几秒的订单查询。他加了一个索引速度确实上来了但当我问他为什么索引能让 800 万行的表瞬间定位到数据为什么这条 SQL 明明有索引却不走的时候他答不上来。这正是绝大多数开发者的真实状态知道 MySQL 索引重要知道要加索引但索引背后的数据结构和失效机制是模糊的。这篇学习笔记就是冲着把索引彻底讲透来的。我从 BTree 的数据结构讲起一路拆到聚簇索引、回表、复合索引的最左前缀原则最后落到真实 SQL 的创建、优化和索引失效排查所有例子都带可直接运行的 SQL 源码。无论你是刚接触数据库的新人还是在准备面试、想系统梳理索引知识的开发这篇文章都能给你一条完整的主线。1. 索引到底是什么先从一个没有索引的场景说起1.1 一次全表扫描的成本比想象中大得多假设有一张用户表里面存了 500 万行数据CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) DEFAULT NULL, age INT DEFAULT NULL, created_at DATETIME DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;现在执行这样一条查询SELECT * FROM user WHERE username zhangsan;如果username列上没有索引MySQL 能做的最可靠的事情只有一件把整张表从头到尾扫一遍一行一行比对username的值。这就是我们常说的全表扫描ALL。我帮你算一笔账。InnoDB 的存储引擎以页Page为最小读写单位默认一页是 16KB。假设一条用户记录平均占用 500 字节字段多、变长字段实际都会占空间一页大约能装下 30 行数据。500 万行数据就需要大约 16 万多个数据页。就算这些页全在内存的 Buffer Pool 里要逐页遍历并逐行比对CPU 开销也相当惊人如果部分页不在内存里还得从磁盘读那就是一次实打实的I/O 风暴。类比一下你想在一本没有目录的词典里找一个词只能从第一页翻到最后一页。运气好翻几页就找到了运气不好要翻完整本书。索引就是给数据表加上一个目录。1.2 索引的本质用空间换时间索引的本质是独立于表数据之外的一种额外存储结构。它把某一列或几列的值按特定顺序组织起来并在这些值和原始数据行之间建立映射关系。在 InnoDB 里索引默认使用 BTree 来组织。它的查找过程大致是从根节点出发利用有序性做二分查找一层一层往下走一般两三次磁盘 I/O 就能定位到目标数据所在的位置。但天下没有免费的午餐。引入索引之后每次INSERT、UPDATE、DELETE都要同步维护索引树插入要往树里加节点删除要标记节点更新如果动了索引列等于先删后插。所以索引越多写入就越慢。这也是为什么索引越多越好是新手最容易踩的坑——空间和写入性能的代价都是真金白银。2. 数据结构选型为什么 MySQL 偏偏选了 BTree2.1 二叉树、B树逐一被淘汰的逻辑如果你跟我一样好奇过为什么 MySQL 不用别的数据结构可以沿着这条技术演进的线走一遍思路会非常清晰。先看最简单的链表。查找一个元素的时间复杂度是 O(n)数据量一大就废了直接淘汰。再看二叉搜索树。它把查找复杂度降到了 O(log n)似乎不错。但有两个问题第一如果插入的数据是有序的比如自增主键二叉搜索树会退化成一个链表查找又变回 O(n)第二即使使用 AVL 树、红黑树这种自平衡树它依然是一棵二叉树。二叉树的问题在于树高太深。拿 1000 万条数据来说二叉树的理论高度大约是log2(1000万) ≈ 24层。这意味着查找一个数据最坏要从根节点走 24 次访问下一层节点的操作。而 MySQL 的数据是存在磁盘上的每访问一个节点往往就意味着一到多次磁盘 I/O。24 层就是 20 多次 I/O这个开销是完全不能接受的。于是出现了B树平衡多路搜索树。B树的特点是每个节点不再只存一个 key而是可以存多个 key 和多个子节点指针。这样同样的数据量树的层数急剧减少比如 3 层就能放下千万级数据I/O 次数一下就降下来了。但 B树 还有一个优化空间它的每个节点既存 key 又存完整的数据data。数据本身占空间很大会直接挤占节点里能容纳的 key 数量导致每个节点的分叉数专业术语叫扇出变小。扇出变小树就会变高I/O 次数又会上去。正是这个矛盾催生了 BTree。2.2 BTree 的三大特性每一个都踩在点上BTree 相比 B树做了三个关键改动这三个改动在我看来每一个都踩在了数据库的痛点需求上。第一只有叶子节点存储数据非叶子节点只存 key 和指针。内部节点因此变得非常轻一页 16KB 能放下非常多的 key扇出大幅提升。第二叶子节点之间通过双向链表串联。这意味着当你查一个范围时比如WHERE age 20 AND age 30找到了第一条符合条件的记录后可以直接顺着链表往下读不需要再回到树根重新查找。这个特性让 BTree 在范围查询上碾压 Hash 索引。第三所有数据都落在叶子节点上并且严格有序。查询任意一条记录的 I/O 次数比较稳定不会出现 B树 那种数据在根节点附近很快就查到、在深叶子节点就要走很多层的极端波动。我们来算一组很有名的数据。假设一个非叶子节点是 16KB索引键是 8 字节比如 BIGINT指针是 6 字节那么一个节点大约能存放16 * 1024 / (8 6) ≈ 1170 个键值对也就是说每个节点有 1170 个分叉。两层非叶子节点能覆盖1170 × 1170 ≈ 137 万个叶子节点每个叶子节点数据页如果按 16KB 存 200 行记录那两层树就能支撑 2.7 亿行数据。这就是为什么我们说InnoDB 的 BTree 一般 2 到 3 层就足够走索引查询最多只需要 2 到 3 次磁盘 I/O。2.3 Hash 索引只适合等值查询的偏科生除了 BTreeMySQL 还有 Hash 索引。InnoDB 引擎并不允许用户直接创建 Hash 索引它内部使用的是自适应哈希索引Adaptive Hash Index用来加速某些热点等值查询这是存储引擎的自动行为我们不需要干预。真正可以手动创建 Hash 索引的是 Memory 引擎但日常业务使用较少。Hash 索引的查找速度确实快时间复杂度 O(1)但它有几个致命短板不支持范围查询、、BETWEEN全都不行不支持排序因为数据在散列表里是无序的不支持部分索引键匹配联合索引必须所有列都用上才行存在哈希冲突极端情况下会退化成链表。相比之下BTree 在等值、范围、排序、前缀匹配上的综合表现是最均衡的所以 InnoDB 选择了它。你在面试里如果被问为什么用 BTree 不用 Hash把上面的对比讲清楚就足够了。3. 聚簇索引、二级索引与回表索引背后的隐藏成本3.1 InnoDB 的聚簇索引数据就是索引前面我提到索引是独立结构但对 InnoDB 来说有个特例主键索引聚簇索引。InnoDB 的表数据文件本身就是按主键组织的一棵 BTree。在这棵树里非叶子节点存放的是主键值叶子节点存放的是完整的数据行。也就是说数据行和主键索引是绑定在一起的主键索引的叶子节点就是数据本身。这就是聚簇的含义。所以 InnoDB 的表强制要求有主键。如果你建表时没有显式指定主键InnoDB 会先找第一个非空的唯一索引作为主键如果连唯一索引都没有它会隐式生成一个 6 字节的ROWID作为主键。这个隐藏主键平时感知不到但会在某些复制场景和空间回收时带来麻烦所以最好显式定义主键。这里有一个我踩过坑的经验主键最好用自增整型不要用 UUID。自增主键在插入时是在 BTree 的末尾追加始终在最后一页写入顺序 I/O效率高UUID 主键是随机分布每插入一条新数据BTree 都可能为了维持顺序去做页分裂产生大量碎片写入性能和空间利用率都会明显下降。3.2 二级索引与回表为什么加了索引反而要多查一次除了主键索引其他索引都叫二级索引也叫辅助索引。二级索引的叶子节点不存完整数据行只存主键值。这就带来一个现象当你通过二级索引查询比如age字段建了索引执行SELECT * FROM user WHERE age 25;MySQL 会先到age索引的 BTree 里找到所有age25的二级索引记录拿到对应的主键值id然后再拿着这些id回到主键的聚簇索引树里去查完整的数据行。这个先查二级索引、再回主键索引查数据的二次查询过程就叫回表。回表意味着至少多一次磁盘 I/O。在数据量大的情况下如果一次查询命中了大量二级索引记录比如age25有 10 万行那就要回表 10 万次。此时 MySQL 优化器可能觉得还不如全表扫描于是干脆不走索引——这就是你经常看到的有索引但没走的原因之一。3.3 覆盖索引让回表彻底消失回表既然有成本那能不能避免能办法是覆盖索引。如果一条查询所需的全部字段都已经包含在某个二级索引的索引树里那么 MySQL 可以直接从索引树里取数据完全不需要回表。执行计划的Extra字段里会出现Using index。举个例子假设有一张订单表CREATE TABLE order_info ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(32) NOT NULL, amount DECIMAL(10,2) DEFAULT NULL, status TINYINT DEFAULT NULL, created_at DATETIME DEFAULT NULL, PRIMARY KEY (id), KEY idx_user_created (user_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;执行SELECT user_id, created_at FROM order_info WHERE user_id 10086;user_id和created_at都包含在idx_user_created这棵索引树里那么查询可以直接在索引树上完成不需要回表。而如果查询的是order_no、amount这些不在索引里的字段就必然要回表。这个特性非常实用。在设计索引时尽可能让高频查询的字段覆盖在同一个复合索引里是性能调优里面性价比很高的一个手段。4. 索引分类与创建实操完整 SQL 源码示例4.1 MySQL 索引到底有哪几类先看一张对照表在动手写 SQL 之前先明确 MySQL 的主要索引类型。把它们放在一张表里对比会清楚很多索引类型创建关键字特点典型应用场景普通索引KEY / INDEX仅加速查询无约束高频查询字段唯一索引UNIQUE KEY加速查询且列值不能重复手机号、邮箱、订单号主键索引PRIMARY KEY唯一索引 聚簇组织每表一个每张表必须有一个全文索引FULLTEXT支持文本内容的关键词检索文章、评论内容搜索空间索引SPATIAL基于地理坐标数据GIS 场景日常业务极少用复合索引KEY (a, b, c)一列以上组合成一个索引多条件组合查询注意普通索引和唯一索引的区别不仅仅是约束唯一索引因为性质特殊在查询优化上往往还能给优化器更多信息让它对计划做更好的判断。4.2 创建索引的完整 SQL建表时和内联两种写法方式一建表时直接创建CREATE TABLE user_profile ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, phone VARCHAR(20) NOT NULL, email VARCHAR(100) DEFAULT NULL, age INT DEFAULT NULL, city VARCHAR(50) DEFAULT NULL, created_at DATETIME DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), KEY idx_username (username), KEY idx_age_city (age, city), FULLTEXT KEY ft_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这个建表语句一次性演示了四种索引主键索引id、唯一索引uk_phone、普通索引idx_username、复合索引idx_age_city顺手建了一个全文索引示例。方式二表已存在用 ALTER 或 CREATE INDEX 追加-- 给 user_profile 表的 email 列添加唯一索引 ALTER TABLE user_profile ADD UNIQUE INDEX uk_email (email); -- 给 age 列添加普通索引 ALTER TABLE user_profile ADD INDEX idx_age (age); -- 或者用独立的 CREATE INDEX 语法效果同上 CREATE INDEX idx_age ON user_profile (age); -- 添加复合索引 ALTER TABLE user_profile ADD INDEX idx_city_age (city, age); -- 添加前缀索引只取 username 前 10 个字符建索引节省空间 ALTER TABLE user_profile ADD INDEX idx_username_prefix (username(10));前缀索引是我实际项目里很喜欢用的小技巧。长字符串比如username、url如果整体建索引索引体积会很大只取前 N 个字符建索引能大幅压缩索引空间查询时也基本不影响区分度。4.3 查看和删除索引管理语句与几个注意点-- 查看某张表的全部索引信息 SHOW INDEX FROM user_profile; -- 删除普通索引 DROP INDEX idx_age ON user_profile; -- 用 ALTER 删除索引 ALTER TABLE user_profile DROP INDEX idx_city_age; -- 8.0 版本可以把索引设为不可见先验证再删很实用 ALTER TABLE user_profile ALTER INDEX idx_age INVISIBLE; ALTER TABLE user_profile ALTER INDEX idx_age VISIBLE;INVISIBLE索引是 MySQL 8.0 引入的一个重要能力。它允许你让一个索引暂时不参与查询优化但索引本身还在不会被后台删除。我在做索引下线的操作时习惯先把它设为不可见观察一段时间线上慢查询没有反弹再真正执行DROP INDEX。这比直接删索引稳妥得多。另外提醒一点在几百万行以上的大表上用ALTER TABLE加索引即使 InnoDB 支持在线 DDL也会在构建索引期间产生额外开销。我建议把这类操作安排在业务低峰期执行并且先评估磁盘空间——建索引很吃临时空间。5. 复合索引与最左前缀原则面试和实战都绕不开的核心5.1 复合索引的存储顺序先按第一列再按第二列复合索引也叫联合索引指的是用多个列共同组成一个索引。它的存储排序规则是先按第一个字段排序第一个字段相同的情况下按第二个字段排序依次类推。比如索引idx_user_created (user_id, created_at)在索引树里数据先按user_id排好同一个user_id内部再按created_at排。理解这个顺序很重要因为最左前缀原则就源于此。一个复合索引(a, b, c)实际上相当于同时创建了三个索引(a)单列索引用法(a, b)两列组合查询(a, b, c)三列组合查询但它不等于建了(b)、(c)或(b, c)这些独立索引。这意味着你可以靠一个复合索引覆盖多种查询模式省掉多个冗余索引。5.2 最左前缀原则哪些查询能命中哪些不能以下面的表为例CREATE TABLE order_info ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(32) NOT NULL, order_type TINYINT DEFAULT 0, created_at DATETIME DEFAULT NULL, PRIMARY KEY (id), KEY idx_user_type_time (user_id, order_type, created_at) ) ENGINEInnoDB;已建立复合索引(user_id, order_type, created_at)下面这些查询都能命中索引-- 第一列直接命中 SELECT * FROM order_info WHERE user_id 100; -- 第一列 第二列 SELECT * FROM order_info WHERE user_id 100 AND order_type 1; -- 三列全用上 SELECT * FROM order_info WHERE user_id 100 AND order_type 1 AND created_at 2024-01-01;而下面这些查询无法充分利用索引-- 跳过了第一列直接查第二列索引失效 SELECT * FROM order_info WHERE order_type 1; -- 只查第二、三列同样跳过 user_id索引失效 SELECT * FROM order_info WHERE order_type 1 AND created_at 2024-01-01;为什么直接查order_type就不行因为索引树是先按user_id排序的没有user_id作为前缀你无法利用索引的全局有序性快速定位order_type。还有一个经典的细节范围查询右边的列会中断索引匹配。比如SELECT * FROM order_info WHERE user_id 100 AND order_type 0 AND created_at 2024-01-01;order_type 0是一个范围条件它左边的user_id用于精确定位order_type用于范围扫描但再往右的created_at就只能作为普通条件过滤无法继续走索引的等值匹配了。MySQL 8.0 里有一个索引下推ICP优化可以把created_at条件下推到索引层提前过滤减少回表次数但这和完整使用索引键是两回事。5.3 复合索引字段顺序怎么排两个真实的判断标准既然最左前缀如此关键复合索引里字段的排列顺序就变得非常重要。我一般按下面两个标准来判断。标准一区分度高的字段放前面。比如(gender, age)和(age, gender)gender只有男和女两个值区分度极低age有几十种取值区分度高很多。把age放前面索引定位时一排就能过滤掉大部分记录效率明显更高。标准二高频等值查询的字段放前面范围查询的字段放后面。因为范围查询右边会导致索引匹配中断所以设计时要把条件的列放在前面把、、BETWEEN这类范围列放在后面。当然这也不绝对如果范围列的区分度极高也可以优先用它定位。我见过一个反例有人给(status, user_id)建索引status只有 0 和 1 两个值查询时希望用user_id精确过滤但受最左前缀限制user_id没法先被使用结果这个索引形同虚设。后来改成(user_id, status)效果立竿见影。6. 索引失效的 8 种典型场景用 EXPLAIN 验证一切6.1 函数运算与隐式类型转换索引失效重灾区场景一对索引列使用函数。-- 索引列套了函数索引失效 SELECT * FROM user WHERE DATE(created_at) 2024-01-15;正确写法是改成范围条件SELECT * FROM user WHERE created_at 2024-01-15 00:00:00 AND created_at 2024-01-16 00:00:00;场景二对索引列做运算。-- 索引列参与了算术运算索引失效 SELECT * FROM user WHERE id 1 5; -- 应改写成 SELECT * FROM user WHERE id 4;场景三隐式类型转换。这是线上出问题最多的一种。如果phone列类型是VARCHAR但查询时用了数字-- 左边是字符串列右边是数字MySQL 会尝试把列转换为数字导致索引失效 SELECT * FROM user WHERE phone 13800138000; -- 正确字符串和字符串比较 SELECT * FROM user WHERE phone 13800138000;反过来如果索引列是整数类型传入字符串一般不会失效因为 MySQL 会把字符串转成数字。但为了统一规范我建议始终让列类型和传入值类型保持一致。6.2 LIKE、OR、NOT IN 的经典陷阱-- 前置通配符%xxx 无法走索引 SELECT * FROM user WHERE username LIKE %zhang%; -- 后缀通配符xxx% 可以走索引 SELECT * FROM user WHERE username LIKE zhang%;LIKE zhang%之所以能走索引是因为它本质上是一个前缀匹配的范围查询从zhang开头的位置扫到zhang之后的下一个位置。而%zhang%里前缀是未知的索引的有序性无法利用。-- OR 连接如果左右两边的字段不是都有索引大概率全表扫描 SELECT * FROM user WHERE username zhangsan OR phone 13800138000; -- 优化思路改成 UNION ALL SELECT * FROM user WHERE username zhangsan UNION ALL SELECT * FROM user WHERE phone 13800138000;OR的情况要特别说明如果username和phone都有索引MySQL 优化器理论上可以把两个索引结果做合并index merge但实践中不稳定。最稳妥的做法是分别查再合并或者考虑用复合索引覆盖。-- NOT IN、NOT EXISTS 通常不走索引 SELECT * FROM user WHERE id NOT IN (100, 200, 300);NOT IN本质上是全量排除优化器计算下来觉得全表扫描成本更低。实在需要这类查询可以改写成LEFT JOIN ... WHERE ... IS NULL的形式但也要结合数据分布来判断。这里我建议用EXPLAIN实测不要背死规则。6.3 用 EXPLAIN 看执行计划验证一切的手段我反复强调实测是因为索引是否失效最终要看执行计划。EXPLAIN是 MySQL 用来解释 SQL 怎么执行的核心命令EXPLAIN SELECT * FROM user WHERE username zhangsan;重点关注这几列type访问类型从好到差依次是system const eq_ref ref range index ALL。看到ALL就是全表扫描这是最需要警惕的。key实际使用的索引名。为 NULL 表示没有使用索引。rows预估扫描的行数越小越好。Extra辅助信息。Using filesort表示额外排序Using temporary表示用临时表Using index表示覆盖索引Using where表示存储引擎层返回后还要过滤。我在排查慢查询时标准动作就是先EXPLAIN一把看type是不是ref或range看Extra里有没有filesort。这两个信息基本决定了这条 SQL 的性能天花板。7. 实战案例订单查询从 12 秒优化到 0.03 秒7.1 问题现场还原朋友发来一条慢查询粗略还原如下。表order_info有 800 万行数据执行SELECT * FROM order_info WHERE user_id 10086 ORDER BY created_at DESC LIMIT 20;执行时间是 12.3 秒。EXPLAIN结果里type是ALLrows估算接近 800 万Extra里还带着Using filesort。问题拆开来看是两个user_id没有索引全表扫描800 万行逐行匹配user_id这步就把耗时拉满了。即使过滤出来少量记录由于没有索引可以支持排序MySQL 还要把这些记录放到内存或磁盘做一次额外排序filesort再次增加开销。7.2 优化动作与结果我建议他加一个复合索引把过滤和排序一并解决ALTER TABLE order_info ADD INDEX idx_user_created (user_id, created_at);这个索引的精妙之处在于user_id用于等值过滤created_at用于排序。因为复合索引内部先按user_id排、再按created_at排MySQL 可以顺着索引顺序直接拿到已经排好的数据filesort也顺带消失了。优化后的EXPLAINtype从ALL变成refkey显示idx_user_createdrows从 800 万降到几千行Extra里不再出现Using filesort实际执行时间从 12.3 秒降到 0.03 秒。这个案例说明一个很重要的事实加索引的方向最好是过滤字段 排序字段一起考虑而不是只盯着 WHERE 里的字段。很多人只给user_id建单列索引排序问题没解决filesort依然存在。7.3 索引维护的日常经验怎么发现和清理无效索引线上数据库跑久了索引会越堆越多其中总有一些是冗余的、多年没被用过的。我维护数据库时有几个固定习惯。第一定期用系统库查未使用索引。-- MySQL 5.7 的 sys 库自带工具 SELECT * FROM sys.schema_unused_indexes;这个视图能列出长期没有被使用的索引结合业务确认后就可以删掉。它们留在那里不仅占空间还会拖慢每次写入。第二识别冗余索引。比如已经有了复合索引(user_id, created_at)再单独建一个(user_id)就是冗余的——因为user_id已经是复合索引的最左前缀单列索引能覆盖的场景复合索引都可以覆盖。见到这种重复建设我一般是删掉单列索引。第三不要给区分度极低的字段建索引。我之前踩过坑给一个status字段建索引这个字段只有 0 和 1 两个值分布比例接近 9:1。结果优化器根本不用这个索引因为区分度太低走索引回表的成本比全表扫描还高。判断区分度可以看选择性-- 选择性 去重后的行数 / 总行数越接近 1 越好 SELECT COUNT(DISTINCT status) / COUNT(*) FROM order_info;一般来说选择性低于 20% 的字段建索引的意义就不大了。当然也有例外如果查询结果占比非常小且查询频率极高单独分析也值得。最后分享一个我自己的习惯。现在每写一条稍复杂的 SQL我都会习惯性地在前面加个EXPLAIN扫一眼type和Extra再决定要不要跑。在这个习惯的帮助下我踩过的坑反而成了最快的成长路径——索引失效的每种场景我几乎都亲手复现过。我建议你也试试建索引之前先想清楚查询模式再想清楚字段顺序最后用EXPLAIN验证一遍。索引不是银弹但当你真正理解它背后的存储结构和设计逻辑它就会成为你最顺手的一件武器。