7450 字
37 分钟
MySQL 索引底层与优化
2026-05-24MysqlmysqlSQL 优化性能优化

MySQL InnoDB 存储引擎的索引核心是 B+ 树——一种”矮胖”的多路平衡搜索树，专为磁盘 I/O 优化。理解聚簇索引、二级索引、回表与覆盖索引的底层机制，是写出高性能 SQL 的前提。

目录#

  1. 为什么是 B+ 树
  2. B+ 树的结构与操作
  3. 聚簇索引 vs 非聚簇索引
  4. 回表与覆盖索引
  5. 联合索引与最左前缀法则
  6. 索引优化实战
  7. 常见面试题
  8. 总结

1. 为什么是 B+ 树#

1.1 磁盘 I/O 是瓶颈#

数据库的数据存在磁盘上，而磁盘 I/O 的速度比内存访问慢几个数量级：

存储层级访问延迟量级
L1 Cache~1nsCPU 周期
L3 Cache~10nsCPU 周期
内存 (RAM)~100ns纳秒
SSD 随机读~100μs微秒
机械硬盘寻道~10ms毫秒

一次磁盘 I/O 的开销 ≈ 10,000,000 次内存访问。因此索引设计的核心目标就是：用最少的磁盘 I/O 找到数据。

1.2 从二叉树到 B 树#

二叉搜索树 (BST)
      50
     /  \
   30    70
  /  \   / \
20   40 60  80


高 = log₂(N)，百万数据 → 高 ≈ 20 层 → 20 次磁盘 I/O ❌

问题：二叉树每个节点只存一个键值，树太高，磁盘 I/O 次数过多。

1.3 B 树的改进#

多叉树：每个节点存放多个键值，每个节点大小通常 = 一页（16KB）。

B 树节点（一次磁盘 I/O 加载一整页）：
┌───┬───┬───┬───┬───────────┬───┬───┬───┐
│指针│ 8 │指针│15 │     ...   │35 │指针│40 │   ← 一页 = 16KB，可以放几百个键
└───┴───┴───┴───┴───────────┴───┴───┴───┘
  • 每个节点存储数百个键值 → 树高大幅降低
  • 千万级数据，B 树高度仅 3~4 层 → 3~4 次磁盘 I/O ✨

1.4 B+ 树 vs B 树的区别#

B+ 树是 B 树的变体，MySQL InnoDB 选择 B+ 树的原因：

对比维度B 树B+ 树
非叶子节点存数据 + 索引只存索引（可放更多键 → 更矮）
叶子节点存数据存全部数据 + 双向链表
范围查询需要中序遍历回退到上层顺着叶子节点链表扫，极快
稳定性数据可能在非叶子层数据一定在叶子层，查询路径等长
B+ 树结构（InnoDB 实际使用的结构）：


               [30 | 60]                        ← 非叶子节点：只存键（路由）
              /    |    \
        [10|20] [40|50] [70|80]                 ← 非叶子节点
         / | \   / | \   / | \
        叶子层 ─────────────────────────
        [1]↔[10]↔[20] [30]↔[40]↔[50] [60]↔[70]↔[80]  ← 叶子节点：存完整数据 + 双向链表

B+ 树的两大核心优势：

  1. 矮胖：非叶子节点不存数据，一页能放更多键，树更矮 → 磁盘 I/O 更少
  2. 范围查询无敌：叶子节点用双向链表串联。WHERE age > 18 只需找到起点，顺着链表往后扫，不需要回溯树枝

2. B+ 树的结构与操作#

2.1 InnoDB 页结构#

InnoDB 最小存储单位是页（Page），默认 16KB。B+ 树的每个节点对应一页。

InnoDB 页结构（16KB）：
┌────────────────────────────────────────┐
│  File Header (38B)                     │
├────────────────────────────────────────┤
│  Page Header (56B)                     │
├────────────────────────────────────────┤
│  Infimum + Supremum Records (26B)      │  ← 虚拟最小/最大记录
├────────────────────────────────────────┤
│  User Records (增长方向 →)              │  ← 实际数据行
│  ┌───┬───┬───┬───┐                    │
│  │ROW│ROW│ROW│ROW│ ...                │
│  └───┴───┴───┴───┘                    │
├────────────────────────────────────────┤
│  Free Space                           │
├────────────────────────────────────────┤
│  Page Directory (槽位，用于二分查找)      │
├────────────────────────────────────────┤
│  File Trailer (8B, 校验和)              │
└────────────────────────────────────────┘

2.2 B+ 树的查找过程#

SELECT * FROM users WHERE id = 55;
查找 id=55:


① 根节点（常驻内存/buffer pool）
   [30|60] → 55 在 30 和 60 之间 → 走中间指针
              │
② 中间节点（磁盘页）
   [40|50]  → 55 > 50 → 走最右指针
              │
③ 叶子节点（磁盘页）
   [50]↔[55]↔[58]↔[60] → 找到 55！
   返回整行数据

总共 2~3 次磁盘 I/O（根节点大概率在 Buffer Pool 中缓存）。

2.3 范围查询#

SELECT * FROM users WHERE age BETWEEN 20 AND 35;
[15]↔[20]↔[22]↔[25]↔[30]↔[35]↔[40]↔...
       ↑ 从这里开始      ↑ 到这里结束
       └──→ 顺着链表往后扫，不回退上层 ←──┘

只经过 1 次非叶子节点定位 + 连续叶子节点扫描，时间复杂度 O(log N + K)。

2.4 B+ 树的写入（插入与页分裂）#

当叶子节点写满时，触发页分裂：

插入前：[10|20|30]  (页已满，假设一页放 3 条)
插入 15 时：


分裂后：
   [20]              ← 中间键提升到父节点
  /    \
[10|15] [20|30]      ← 分裂成两页

写入不总是顺序追加——这就是为什么：

  • 乱序主键（UUID）会导致频繁页分裂
  • 自增主键最利于插入性能

3. 聚簇索引 vs 非聚簇索引#

3.1 核心区别#

┌───────────────────────────────────────────────────────────┐
│                    聚簇索引 (Clustered Index)               │
│                                                           │
│   B+ 树─────────────────────────────────                  │
│   [非叶子节点: 只存主键值]                                   │
│         │                                                 │
│   叶子节点: 存的是 ──→ 完整行数据 (row data)                 │
│   ┌──────────────────────────────┐                        │
│   │ id=1, name="张三", age=25, ...│                        │
│   │ id=3, name="李四", age=30, ...│                        │
│   │ id=5, name="王五", age=28, ...│                        │
│   └──────────────────────────────┘                        │
│                                                           │
│   特点：找到了索引就找到了数据，"索引即数据"                    │
└───────────────────────────────────────────────────────────┘


┌───────────────────────────────────────────────────────────┐
│                非聚簇索引 (Secondary Index / 二级索引)       │
│                                                           │
│   B+ 树─────────────────────────────────                  │
│   [非叶子节点: 存索引列的值]                                  │
│         │                                                 │
│   叶子节点: 存的是 ──→ 主键值 (primary key)                  │
│   ┌──────────────────────┐                                │
│   │ age=25, id=1         │                                │
│   │ age=28, id=5         │                                │
│   │ age=30, id=3         │                                │
│   └──────────────────────┘                                │
│                                                           │
│   特点：叶子节点只存"主键指针"，需要回表                        │
└───────────────────────────────────────────────────────────┘

3.2 关键规则#

规则说明
InnoDB 必须有聚簇索引且仅有一个
优先使用用户定义的 PRIMARY KEY作为聚簇索引
无主键时使用第一个 UNIQUE 非空索引
都没有则自动创建隐藏的 row_id6 字节，单调递增

3.3 为什么二级索引存主键而不是物理地址#

InnoDB 的二级索引存的是主键值而非物理地址（MyISAM 存的是物理地址），原因：

  1. 数据移动时无需更新所有索引：页分裂/合并导致行物理位置变化时，只需更新聚簇索引，二级索引不受影响（因为主键值不变）
  2. 节省空间：主键值比物理地址短时（如 INT vs 6 字节 ROWID），节省二级索引空间

4. 回表与覆盖索引#

4.1 回表（Table Lookup）#

回表是 MySQL 中最常见的性能杀手之一：

-- 有二级索引 idx_age ON users(age)
SELECT * FROM users WHERE age = 25;
执行过程（回表）：


① 遍历 idx_age 的 B+ 树 → 找到 age=25 的叶子节点 → 得到主键 id
         │
② 拿着 id 去聚簇索引的 B+ 树再查一次 → 获取完整行数据
         │
③ 返回 SELECT * 需要的所有列
                       idx_age (二级索引)
                  ┌─────────────────────┐
                  │  age=25, id=1       │ ← 叶子节点只存 (age, id)
                  │  age=25, id=7       │
                  │  age=25, id=12      │
                  └────┬────┬────┬──────┘
                       │    │    │
              ┌────────┘    │    └─────────┐
              ↓             ↓              ↓
         ┌─────────┐  ┌─────────┐   ┌──────────┐
         │ 聚簇索引  │  │ 聚簇索引  │   │ 聚簇索引   │
         │ id=1,    │  │ id=7,   │   │ id=12,    │
         │ name,   │  │ name,   │   │ name,     │
         │ email,  │  │ email,  │   │ email,    │
         │ ...     │  │ ...     │   │ ...       │
         └─────────┘  └─────────┘   └──────────┘
               ↑ 每次获取完整行都是一次回表 (随机 I/O)

对于 age=25 的 3 行数据，回表意味着：1 次二级索引查找 + 3 次随机聚簇索引查找。

4.2 覆盖索引（Covering Index）#

优化的终极目标：SQL 需要的所有列都已经在二级索引的叶子节点里了，不需要回表。

-- 有联合索引 idx_age_name ON users(age, name)
SELECT age, name FROM users WHERE age = 25;  -- ✅ 覆盖索引，不回表


-- 对比
SELECT age, name, email FROM users WHERE age = 25;  -- ❌ email 不在索引中，需要回表
覆盖索引的执行路径：


  idx_age_name 二级索引（联合索引）
  ┌─────────────────────────────┐
  │ age=25, name="张三", id=1   │ ← 叶子节点存了 age + name + id
  │ age=25, name="李四", id=7   │
  └─────────────────────────────┘
          │
          └──→ SELECT age, name 所需的列都在这里！
               Extra: Using index （不用回表，直接返回）✨

识别覆盖索引：EXPLAIN 的 Extra 字段显示 Using index。

4.3 回表 vs 覆盖索引性能对比#

-- 场景：100 万行数据中查询 age=25 的 3000 行


-- ❌ 回表版本（需要 3000 次随机 I/O）
SELECT * FROM users WHERE age = 25;
-- 耗时：~200ms


-- ✅ 覆盖索引版本（所有数据在索引中，0 次回表）
SELECT age, name FROM users WHERE age = 25;
-- 耗时：~5ms


-- 性能差距：40 倍

4.4 多个单列索引 vs 联合索引#

这是索引设计中最常见的误区之一：认为建多个单列索引等于建一个联合索引。

根本区别#

表结构：users(id, name, age, city)


方案 A：三个单列索引
    INDEX idx_name ON users(name)
    INDEX idx_age  ON users(age)
    INDEX idx_city ON users(city)


方案 B：一个联合索引
    INDEX idx_name_age_city ON users(name, age, city)

存储层面的差异：

方案 A：三个单列索引 = 三棵独立的 B+ 树


  idx_name B+ 树          idx_age B+ 树          idx_city B+ 树
  ┌───────────────┐      ┌───────────────┐      ┌───────────────┐
  │name="张三",id=1│      │age=25,id=1    │      │city="北京",id=1│
  │name="张三",id=7│      │age=25,id=7    │      │city="北京",id=7│
  │name="李四",id=3│      │age=28,id=5    │      │city="上海",id=3│
  │name="王五",id=5│      │age=30,id=3    │      │city="深圳",id=5│
  └───────────────┘      └───────────────┘      └───────────────┘
  叶子只存 (name, id)     叶子只存 (age, id)      叶子只存 (city, id)




方案 B：一个联合索引 = 一棵 B+ 树


  idx_name_age_city B+ 树
  ┌─────────────────────────────────┐
  │name="张三", age=25, city="北京", id=1 │
  │name="张三", age=28, city="上海", id=7 │
  │name="李四", age=30, city="北京", id=3 │
  │name="王五", age=28, city="深圳", id=5 │
  └─────────────────────────────────┘
  叶子存 (name, age, city, id) ← 三个列的值都在同一棵树里！

查询行为对比#

SELECT name, age, city FROM users WHERE name = '张三' AND age = 25;
方案执行过程Extra
方案 A (三个单列索引)优化器选 idx_name → 找到 name='张三' 的行 → 逐行回表取 age 和 city → Server 层过滤 age=25Using where
方案 B (联合索引)直接在 idx_name_age_city 中精确命中 (张三, 25) → age 和 city 已在叶子节点中 → 不回表，直接返回Using index

核心差异：方案 A 中，idx_name 的叶子节点只有 name + id，没有 age 和 city —— 所以必须回表。方案 B 的叶子节点同时包含 name、age、city——天然就是覆盖索引。

为什么 MySQL 不能同时用多个单列索引#

-- 期望：MySQL 同时用 idx_name 和 idx_age
SELECT * FROM users WHERE name = '张三' AND age = 25;

MySQL 在 大多数情况下一次查询只用一棵索引树：

MySQL 的选择逻辑：
  ① 分析 WHERE 条件中的列
  ② 从可用的索引中选一个"最优的"
      → 通常选区分度最高的那个
  ③ 用这个索引找到主键 → 回表
  ④ 回表后在 Server 层过滤其余条件


对于 name='张三' AND age=25：
  → 假设 idx_name 区分度高，MySQL 用它
  → 回表取出行数据，再检查 age=25
  → idx_age 完全没用上 ❌

特例：Index Merge（索引合并）——MySQL 5.0+ 在特定条件下可以通过 index_merge 同时使用多个索引（intersection / union / sort-union），但触发条件严格且有额外开销，不能依赖它来替代联合索引。

场景分析#

常见 SQL：WHERE name=? AND age=? AND city=?
         ↓
索引方案能覆盖吗需要回表吗评分
三个单列索引 (name), (age), (city)❌ 任一单列索引的叶子只存自己的列✅ 必须回表⭐
联合索引 (name, age, city)✅ 三列都在叶子中❌ 不回表⭐⭐⭐
两个联合索引 (name, age) + (city)看用哪个用 (name,age) 时仍需回表取 city⭐⭐

选择建议#

场景推荐方案原因
查询条件总是一起出现 (name+age)INDEX(name, age) 联合索引一次查询覆盖，可能不用回表
各列独立查询 (有时查 name，有时查 age)分别建单列索引联合索引 (name, age) 对 WHERE age=? 无用（不满足最左前缀）
高频组合查询 + 偶发单独查询联合索引 (name, age) + 单列索引 (age)组合查询走联合索引覆盖，单独 age 查询走单列索引

一句话总结：联合索引等价于按顺序建了多个”覆盖性”逐级递减的索引——INDEX(a,b,c) 包含 INDEX(a,b,c) + INDEX(a,b) + INDEX(a) 的能力，但不等价于三个单列索引。单列索引各存各的、互不知晓，联合索引则共享同一棵 B+ 树的完整列信息。

4.5 UNION 与覆盖索引#

UNION / UNION ALL 将多条查询结果合并。每条子查询独立优化，可以各自利用覆盖索引——但 UNION 和 UNION ALL 在底层的差异会直接影响最终性能。

UNION ALL vs UNION 的底层差异#

-- 场景：分别查"张三"和"李四"的 name + age
-- 有联合索引 INDEX idx_name_age ON users(name, age)


-- UNION ALL
SELECT name, age FROM users WHERE name = '张三'
UNION ALL
SELECT name, age FROM users WHERE name = '李四';


-- UNION（默认去重）
SELECT name, age FROM users WHERE name = '张三'
UNION
SELECT name, age FROM users WHERE name = '李四';

EXPLAIN 对比：

UNION ALL 执行计划：
  id=1: SELECT ... WHERE name='张三'  → key: idx_name_age, Extra: Using index ✅
  id=2: SELECT ... WHERE name='李四'  → key: idx_name_age, Extra: Using index ✅
  (结果直接追加，无需额外操作)


UNION 执行计划：
  id=1: SELECT ... WHERE name='张三'  → key: idx_name_age, Extra: Using index ✅
  id=2: SELECT ... WHERE name='李四'  → key: idx_name_age, Extra: Using index ✅
  id=NULL: UNION RESULT              → Extra: Using temporary  ❌
  (需要临时表 + 去重排序)

关键差异：两条子查询都能各自利用覆盖索引（Using index），但 UNION（无 ALL）额外引入 Using temporary——MySQL 必须创建临时表并对全部结果集执行去重，这一步无法利用任何索引：

UNION ALL（无需去重）：
  query1 结果 ──→ [append] ──→ 直接返回
  query2 结果 ──→
  全程无临时表，覆盖索引红利不被打断


UNION（去重）：
  query1 结果 ──→ [临时表] ──→ [排序/去重] ──→ 返回
  query2 结果 ──→
              ↑ 覆盖索引到此为止，临时表操作无法用索引

各子查询独立使用覆盖索引#

UNION 的核心优势：每条子查询可以针对自己的 WHERE 条件利用不同的覆盖索引：

-- 表：orders(id, user_id, amount, status, create_time)
-- 索引：
--   INDEX idx_user_status (user_id, status)
--   INDEX idx_amount_time (amount, create_time)


-- 两条子查询走完全不同的索引，各取所需
SELECT user_id, status FROM orders WHERE user_id = 100    -- 走 idx_user_status，覆盖
UNION ALL
SELECT user_id, status FROM orders WHERE amount > 1000;   -- 走 idx_amount_time，没覆盖 → 回表

第二条子查询虽然 idx_amount_time 的叶子节点里没有 user_id 和 status，但这是另一个独立查询——优化的方式是给它建自己的覆盖索引。UNION 不管跨查询的索引共享。

与单条 OR 查询的对比#

这是 UNION 的一个高价值使用场景——用 UNION ALL 替换 OR 来”解锁”多个索引覆盖：

-- 表：users(id, name, phone)
-- 索引：INDEX idx_name ON users(name), INDEX idx_phone ON users(phone)


-- ❌ 方案 1：OR 查询 → 通常只能用一棵索引树
SELECT name, phone FROM users WHERE name = '张三' OR phone = '13800138000';
-- 可能走 idx_name 或 idx_phone，另一边的条件需要回表再过滤


-- ✅ 方案 2：UNION ALL 拆分 → 各自走各自的索引
SELECT name, phone FROM users WHERE name = '张三'
UNION ALL
SELECT name, phone FROM users WHERE phone = '13800138000';
-- 第 1 条：走 idx_name，叶子有 (name, id)，需回表取 phone
-- 第 2 条：走 idx_phone，叶子有 (phone, id)，需回表取 name
-- 但每个子查询至少用上了自己的索引！

如果进一步优化——用联合索引 (name, phone) 让第一条子查询覆盖：

-- ✅ 最优方案：联合索引 + UNION ALL
-- INDEX idx_name_phone ON users(name, phone)


SELECT name, phone FROM users WHERE name = '张三'    -- Using index ✅
UNION ALL
SELECT name, phone FROM users WHERE phone = '13800138000';
-- 第 1 条：走 idx_name_phone，叶子有 (name, phone) → 覆盖 ✅
-- 第 2 条：phone 不满足最左前缀，但 idx_name_phone 的叶子仍有 phone → 可走 index 扫描

实践要点#

要点说明
能用 UNION ALL 就别用 UNION省去临时表 + 去重的开销，覆盖索引的红利不会被打断
每条子查询独立建索引UNION 不会帮你组合索引，每个子查询需要自己配好覆盖索引
OR 转 UNION ALL当 OR 两侧条件走不了同一索引时，拆成 UNION ALL 可以让两边各用自己的索引
UNION 的去重排序成本数据量大时，临时表可能写磁盘（Using temporary; Using filesort），代价极高
EXPLAIN 看结果行UNION ALL 的 EXPLAIN 会分别显示每条子查询的 Extra，逐条检查是否有 Using index

5. 联合索引与最左前缀法则#

5.1 联合索引的排序规则#

当你创建 INDEX idx_abc ON t(a, b, c) 时，B+ 树的排序规则是：

先按 a 排序 → a 相同时按 b 排序 → a、b 都相同时按 c 排序
联合索引 (a, b, c) 的叶子节点：
┌───────────────────┐
│ (1, 1, 10)        │
│ (1, 2, 5)         │  ← a=1 的组内：按 b 排序；b 相同按 c 排序
│ (1, 2, 8)         │
│ (1, 5, 3)         │
│ (2, 1, 7)         │
│ (2, 3, 9)         │  ← a=2 的组内：按 b 排序
│ (3, 1, 1)         │
│ (3, 4, 2)         │  ← a=3 的组内：按 b 排序
│ ...               │
└───────────────────┘

5.2 最左前缀法则#

查询条件必须包含联合索引最左边的列，索引才会生效。

查询条件是否走索引原因
WHERE a = 1✅ 走索引a 在最左，按 a 有序
WHERE a = 1 AND b = 2✅ 走索引a 和 b 都参与排序
WHERE a = 1 AND b = 2 AND c = 3✅ 全部匹配完美命中
WHERE a = 1 AND c = 3⚠️ 只用 ac 之前缺 b，c 无法利用排序
WHERE b = 2❌ 全表扫描a 缺失，整体无排序，B+ 树无从下手
WHERE b = 2 AND c = 3❌ 全表扫描同上

5.3 底层原理图解#

联合索引 (a, b, c) 的 B+ 树非叶子节点视角：


WHERE a = 1 AND b = 2  ← 可以快速定位到 (1, 2, *) 的起点
        │
        可以二分查找：因为数据先按 a 排，再按 b 排


WHERE b = 2              ← 无法利用索引
        │
        非叶子节点根本没有 b 的全局排序信息！
        (1,1,*) (1,2,*) (1,5,*) (2,1,*) (2,3,*) ...
        看 b 列：这些值不是有序的 → B+ 树无法二分查找

5.4 范围查询后的列失效#

INDEX idx_abc ON t(a, b, c)


-- ✅ a, b 走索引，c 不走
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3;
--                        a:等值  b:范围  c:失效


-- 原因：b 是范围查询时，多个 b 值对应的 c 不再全局有序
-- b=2 时 c 有序，b=3 时 c 有序，但跨 b 值的 c 无序
范围查询后的列为什么失效：


(a=1, b=2, c=1) → b=2 组内 c 有序 ✓
(a=1, b=2, c=3)
(a=1, b=2, c=8)
───────────────  ← 跨 b 值
(a=1, b=3, c=2) → b=3 组内 c 也有序 ✓
(a=1, b=3, c=5)
────────────────
但在 b ∈ (2, ∞) 的集合中，c 的值是 {1,3,8,2,5} ← 无序 ❌
所以 c 无法利用索引排序来高效过滤

5.5 等价条件与范围条件的排列#

-- ✅ 好：等值条件放前面，范围条件放最后
INDEX idx_good ON t(a, b, c)  -- WHERE a=1 AND b=2 AND c>3  -- 三个都用上


-- ❌ 差：范围条件在中间，后面的列用不上
INDEX idx_bad  ON t(a, b, c)  -- WHERE a=1 AND b>2 AND c=3  -- 只用上 a 和 b

建索引的黄金法则：把 =、IN 等精确匹配的列放前面，范围查询 >、<、BETWEEN、LIKE 'prefix%' 的列放最后。

6. 索引优化实战#

6.1 EXPLAIN 关键字段#

EXPLAIN SELECT * FROM users WHERE age = 25 AND city = '北京';
字段含义关注值
type访问类型ALL(全表) < index < range < ref < eq_ref < const
key实际使用的索引看看是不是你期望的那个
rows预估扫描行数越小越好
Extra额外信息Using index(覆盖索引) ✅ / Using filesort ❌ / Using temporary ❌

6.2 常见优化策略#

策略示例原理
覆盖索引SELECT age,name FROM t WHERE age=25 → 建 (age,name)避免回表
索引下推 (ICP)WHERE name LIKE '张%' AND age=25MySQL 5.6+，在索引层过滤 age，减少回表
避免函数破坏索引WHERE YEAR(create_time)=2025 ❌ → WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31' ✅列上套函数无法使用索引
避免隐式类型转换WHERE phone = 13800138000 ❌（phone 是 VARCHAR） → WHERE phone = '13800138000' ✅类型转换等同于在列上套函数
前缀索引INDEX idx_title ON articles(title(20))长字符串列只索引前 N 个字符
避免 SELECT *只查需要的列更容易触发覆盖索引

6.3 索引下推 (Index Condition Pushdown)#

MySQL 5.6 引入的重要优化：

INDEX idx_name_age ON users(name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age = 25;
无 ICP (MySQL < 5.6)：
  ① 通过索引找到 name LIKE '张%' 的所有行
  ② 对每行都回表取完整数据
  ③ 在 Server 层过滤 age=25
  → 如果 "张%" 有 10000 行但 age=25 只有 3 行，做了 10000 次无用回表


有 ICP (MySQL 5.6+)：
  ① 通过索引找到 name LIKE '张%' 的所有行
  ② 在索引层直接用 age=25 过滤（索引中有 age 列）
  ③ 只对过滤后的 3 行回表
  → 回表次数从 10000 降为 3

6.4 索引选择建议清单#

-- 1. WHERE 条件列 → 建索引
INDEX idx_status ON orders(status);


-- 2. 多条件查询 → 建联合索引
INDEX idx_status_time ON orders(status, create_time);


-- 3. ORDER BY 列 → 尽量与 WHERE 列一起建联合索引
INDEX idx_user_time ON orders(user_id, create_time);
--   WHERE user_id = 1 ORDER BY create_time → 完美


-- 4. JOIN 关联列 → 两个表都建索引
--   SELECT * FROM orders o JOIN users u ON o.user_id = u.id
--   → orders.user_id 和 users.id 都需要索引


-- 5. 区分度低的列 → 不单独建索引（但可放在联合索引前面）
--   INDEX idx_gender ON users(gender) ← 只有 M/F，区分度差
--   INDEX idx_gender_age ON users(gender, age) ← gender 在联合索引中更合理

7. 常见面试题#

Q1：B+ 树为什么比 B 树更适合做数据库索引？#

A1：

  1. 非叶子节点不存数据，一页能放更多键，树更矮 → 更少磁盘 I/O
  2. 叶子节点有双向链表，范围查询只需顺序扫描，不需要中序遍历回退上层
  3. 查询路径等长，所有数据都在叶子节点，查询性能稳定

Q2：为什么 InnoDB 建议使用自增主键？#

A2：

  1. 插入性能：自增主键总是在最后追加，不会触发页分裂（UUID 会随机插入 → 频繁页分裂）
  2. 二级索引空间：主键越小，二级索引叶子节点越小（因为二级索引存的是主键值）
  3. 数据紧凑：自增主键使数据物理上按主键顺序存放，范围查询的磁盘顺序读效率更高

Q3：什么情况下索引会失效？#

A3：常见索引失效场景：

场景示例
列上套函数WHERE YEAR(create_time) = 2025
隐式类型转换WHERE phone = 13800138000（phone 是 VARCHAR）
LIKE 前置通配符WHERE name LIKE '%张'
联合索引不满足最左前缀WHERE b=1（索引是 (a,b,c)）
OR 两边不同列WHERE a=1 OR b=2
使用 != / <> / NOT IN多数情况走全表（视优化器选择）

Q4：覆盖索引是什么？如何判断是否用到了覆盖索引？#

A4：查询需要的所有列都在索引中，EXPLAIN 的 Extra 显示 Using index。例如：

INDEX idx_a_b ON t(a, b)
SELECT a, b FROM t WHERE a = 1;  -- Extra: Using index
SELECT a, b, c FROM t WHERE a = 1;  -- ❌ c 不在索引中，需要回表

Q5：联合索引 (a, b, c) 中，WHERE a=1 AND c=3 会走索引吗？#

A5：部分走。会使用索引的 a 列来定位所有 a=1 的行，但 c 列无法利用索引排序。因为联合索引要求按顺序使用——c 之前缺了 b，所以 c 的排序信息不可用。等于 INDEX(a, b, c) 退化为 INDEX(a) 的效果。

Q6：ICP 技术前后，范围查询的回表过程有什么区别？#

表 user(id, name, age, city)，联合索引 idx_name_age(name, age)。

问题：SELECT * FROM user WHERE name LIKE '张%' AND age = 20; 这条 SQL 能利用联合索引吗？MySQL 5.6 引入 ICP 前后，底层执行过程有什么不同？

A6：能利用索引，但 age 列在 name 范围查询后已经”失效”（无法保证绝对有序）。关键在于 ICP 技术如何减少无效回表。

要理解 ICP，先区分 MySQL 的两层架构：

┌───────────────────────────────────────────┐
│              Server 层                     │
│   负责：解析 SQL、优化器、过滤、排序、函数等  │
├───────────────────────────────────────────┤
│            存储引擎层 (InnoDB)              │
│   负责：索引遍历、回表、数据存取              │
└───────────────────────────────────────────┘

ICP 引入前（MySQL 5.6 之前）：存储引擎”只管找”，Server 层”负责滤”#

idx_name_age 联合索引树                 聚簇索引（主键）树
┌────────────────────┐                ┌─────────────────┐
│ name="张A", age=18 │ ──→ id=7       │ id=7, 完整行数据  │
│ name="张B", age=20 │ ──→ id=3       │ id=3, 完整行数据  │
│ name="张C", age=25 │ ──→ id=12      │ id=12,完整行数据  │
│ name="张D", age=20 │ ──→ id=9       │ id=9, 完整行数据  │
│ ... (共 1000 行)   │                │        ...       │
└────────────────────┘                └─────────────────┘


执行流程（三步走）：


① 存储引擎遍历联合索引 → 找到 name LIKE '张%' 的所有行
   → 得到 1000 个主键 id（age 虽在索引中但被忽略）
         │
② 存储引擎拿 1000 个主键 → 全部回表 → 查出完整行数据
   → 1000 次回表（1000 次随机磁盘 I/O）← 痛点！
         │
③ 把 1000 行完整数据全部返回给 Server 层
   Server 层再逐一检查 age = 20 → 剔除 998 行，只保留 2 行


问题：明明索引树里就有 age 的值，存储引擎却"视而不见"，
     非得回表取出完整行之后才交给 Server 层过滤。

痛点：姓”张”的 1000 人里 age=20 的只有 2 人，却执行了 1000 次回表。

ICP 引入后（MySQL 5.6+）：存储引擎”多看一眼”，在引擎层拦截#

idx_name_age 联合索引树（叶子节点: name + age + id）
┌─────────────────────┐
│ name="张A", age=18  │ → age≠20 → 直接跳过，不回表 ✗
│ name="张B", age=20  │ → age=20 → 回表！ ✓        ──→ 聚簇索引查 id=3
│ name="张C", age=25  │ → age≠20 → 直接跳过，不回表 ✗
│ name="张D", age=20  │ → age=20 → 回表！ ✓        ──→ 聚簇索引查 id=9
│ ...                 │
└─────────────────────┘


执行流程（三步走）：


① 存储引擎遍历联合索引 → 找到 name LIKE '张%' 的记录
         │
② 回表之前，"多看一眼"索引叶子节点里已有的 age 值
   → age=18 ❌ → 跳过（不回表）
   → age=20 ✅ → 拿着 id 去回表
   → age=25 ❌ → 跳过（不回表）
   → age=20 ✅ → 拿着 id 去回表
   → ...只回表 2 次！
         │
③ Server 层收到 2 行完整数据，几乎无需再过滤


1000 次回表 → 2 次回表，性能提升数百倍

核心对比总览#

                    ┌──────────────┬──────────────────┐
                    │  ICP 前       │  ICP 后           │
┌───────────────────┼──────────────┼──────────────────┤
│ 存储引擎的职责      │ 只管找主键    │ 找主键 + 索引过滤  │
│ 过滤 age 的位置    │ Server 层    │ 存储引擎层        │
│ 回表次数(1000行)   │ 1000 次      │ 2 次              │
│ EXPLAIN Extra     │ Using where  │ Using index      │
│                   │              │ condition        │
│                   │              │ (Using ICP)      │
└───────────────────┴──────────────┴──────────────────┘

ICP 能生效的前提#

并非所有查询都能用上 ICP，需要满足以下条件：

条件说明
索引中包含过滤列的完整值联合索引 (name, age) 中 age 的值就在叶子节点里——这是 ICP 能”多看一眼”的前提
范围查询后的列ICP 最经典的场景：第一个列是范围查询，后面的列在索引中”不完全有序”但值还在
不是覆盖索引如果已经是覆盖索引（Using index），不需要回表，ICP 也没意义
查询需要回表SELECT * 这类需要回表的查询。如果只查 name, age，覆盖索引直接搞定，不用 ICP

一句话总结#

ICP 是让存储引擎在回表之前，利用索引叶子节点中已有的列值做一次”低成本预过滤”，把”无效回表”掐死在引擎层。 它的价值不在于”替代覆盖索引”，而在于覆盖索引做不到时（如 SELECT *），最大程度减少回表次数。

8. 总结#

8.1 索引核心模型#

┌─────────────────────────────────────────────────────────────┐
│                   InnoDB 索引全景                              │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│   聚簇索引 (PRIMARY KEY)                                     │
│   ┌─────────────────────────────────────────┐               │
│   │  非叶子: [30|60|90|...]                  │               │
│   │  叶子: [完整行数据] + 双向链表             │               │
│   └─────────────────────────────────────────┘               │
│         ▲ 回表时查询                                          │
│   ┌─────┴───────────────────────────────────┐               │
│   │        二级索引 (idx_age, idx_name...)   │               │
│   │  非叶子: [索引列的值]                     │               │
│   │  叶子: [索引列 + 主键值] + 双向链表        │               │
│   └─────────────────────────────────────────┘               │
│                                                             │
│   查询路径：                                                  │
│   WHERE age = 25                                            │
│     → 二级索引 idx_age → 得到主键 id                          │
│       → 回表：聚簇索引 → 得到完整行                            │
│                                                             │
│   覆盖索引（终极优化）：                                       │
│   SELECT age, name WHERE age = 25                            │
│     → 二级索引 idx_age_name → age 和 name 都在索引中          │
│       → 不回表，直接返回 ✨                                    │
│                                                             │
└─────────────────────────────────────────────────────────────┘

8.2 关键设计原则#

原则说明
B+ 树”矮胖”非叶子节点只存键，一页能塞更多 → 3~4 层覆盖千万级数据
叶子节点链表范围查询的天生优势，单方向扫描，不回退
聚簇索引存整行主键即数据，InnoDB 必须有且仅有一个
二级索引存主键减少维护成本，数据移动不波及二级索引
覆盖索引 > 回表覆盖索引只需扫一个索引树，回表是两次查找
最左前缀联合索引必须从最左列开始匹配，否则 B+ 树排序不可用
自增主键顺序追加，避免页分裂；主键小，二级索引也小

8.3 快速排查清单#

排查点看什么工具
SQL 走索引了吗type 字段EXPLAIN
回表了吗Extra 有 Using index 吗EXPLAIN
索引区分度高吗SHOW INDEX 的 CardinalitySHOW INDEX FROM t
慢查询有哪些超过阈值的 SQL慢查询日志 / pt-query-digest
索引碎片碎片率 > 30% 需要整理OPTIMIZE TABLE

8.4 一句口诀#

等值在前，范围在后；覆盖优先，回表慎用；最左匹配，函数有害。

相关链接#

本文基于 MySQL 8.0 / InnoDB 引擎编写，核心概念（B+ 树、聚簇索引、最左前缀）在所有版本中通用。

MySQL 索引底层与优化
https://shirone.mysqil.com/posts/mysql/mysql-索引底层与优化/
作者
HikariTish
发布于
2026-05-24
许可协议
CC BY-NC-SA 4.0

分享文章

生成精美分享图或复制链接，与更多人分享本文。

继续阅读

沿着主题读

基于共同的标签与分类

换条路线

从其他文章中稳定抽取

最后更新于 ，距今已过 144 天

部分内容可能已过时

MySQL 事务、MVCC 与锁
Go Channel 底层原理