MySQL 索引原理与优化：搞懂 B+ 树与索引失效

小飞兽 MySQL 5 次阅读 2026-07-29

前言

索引是数据库查询性能的核心，理解和掌握 B+ 树的原理以及索引失效的场景，是每个后端工程师的必修课。很多慢查询问题归根结底都是索引使用不当导致的，本文将系统讲解 MySQL 索引的底层原理与实战优化技巧。

B+ 树：MySQL 索引的基石

MySQL 的 InnoDB 引擎使用 B+ 树作为默认索引数据结构，而非二叉树或哈希表。选择 B+ 树并非偶然，而是经过深思熟虑的工程权衡。

为什么不用二叉树？ 二叉树在极端情况下会退化为链表（按顺序插入时），此时查询时间复杂度退化为 O(n)，与全表扫描无异。

为什么不用哈希表？ 哈希表虽然 O(1) 查询极快，但无法支持范围查询（WHERE id > 10）和排序操作，场景局限性太大。

B+ 树的核心特点：

多叉平衡树：每个节点可以包含多个子节点（由 innodb_page_size 决定），树高通常为 3-4 层，这意味着即便数据量达到千万级，也只需 3-4 次磁盘 I/O 就能定位到目标数据。

所有数据都在叶子节点：非叶子节点只存储索引键和子节点指针，这样的设计使得非叶子节点可以容纳更多索引项，从而显著降低树的高度。

叶子节点用双向链表连接：这使得范围查询（如 BETWEEN 10 AND 100）只需定位起点，然后沿链表顺序遍历，无需回溯。

假设一个高度为 3 的 B+ 树，每页 16KB，每行索引约 50 字节，每页可存约 300 条索引，则：

根节点：300 条索引

第二层：300 x 300 = 90,000 条索引

第三层：90,000 x 300 约等于 27,000,000 条索引

仅需 3 层就能索引千万级数据，这就是 B+ 树强大的扩展能力。

聚簇索引与二级索引

InnoDB 中索引分为两种类型：

聚簇索引（Clustered Index）：叶子节点存储完整的行数据。一个表只有一个聚簇索引，即主键索引。如果没有定义主键，InnoDB 会选择第一个 UNIQUE NOT NULL 索引作为聚簇索引，否则自动生成一个隐藏的主键。

二级索引（Secondary Index）：叶子节点存储索引键和主键值。查询时先在二级索引中找到主键，再回表查询聚簇索引获取完整数据，这个过程称为回表。

-- 创建表，主键自动成为聚簇索引
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    created_at DATETIME,
    INDEX idx_name (name),
    INDEX idx_age_email (age, email)
) ENGINE=InnoDB;

-- 查看表的索引结构
SHOW INDEX FROM users;

-- EXPLAIN 分析查询是否使用索引
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type: ref 表示使用二级索引
-- key: idx_name 表示使用的索引名
-- rows: 预估扫描行数
-- Extra: Using index condition 表示索引下推优化

联合索引与最左前缀原则

联合索引是指多个列组成的索引，如 (age, email)。理解最左前缀原则是使用联合索引的关键。

最左前缀原则：查询条件必须从联合索引的最左边连续列开始，才能使用该索引。索引 (A, B, C) 等同于创建了 (A)、(A, B)、(A, B, C) 三个索引。

-- 创建联合索引
ALTER TABLE users ADD INDEX idx_age_email (age, email);

-- 能命中索引的查询
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age = 25 AND email = 'a@b.com';
SELECT * FROM users WHERE age = 25 AND email LIKE 'a%';

-- 无法命中联合索引的查询
SELECT * FROM users WHERE email = 'a@b.com';  -- 跳过了最左列 age
SELECT * FROM users WHERE name = '张三';       -- 不同的索引列

-- 当 age 使用范围查询时，MySQL 只能使用索引的 age 部分
EXPLAIN SELECT * FROM users WHERE age > 20 AND email = 'test@example.com';

索引失效的常见场景

即便字段上建立了索引，如果写法不当，MySQL 查询优化器也会选择放弃索引，执行全表扫描。以下是高频踩坑场景：

-- 场景1：使用函数或运算
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- 失败，字段被包在函数内
SELECT * FROM users WHERE id + 1 = 100;              -- 失败，字段参与运算

-- 正确做法：改写为范围查询
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 场景2：类型转换
SELECT * FROM users WHERE age = '25';  -- 警告，字符串与数字比较
SELECT * FROM users WHERE name = 123;  -- 失败，name 是 VARCHAR，传入数字

-- 场景3：LIKE 以通配符开头
SELECT * FROM users WHERE name LIKE '%张三';   -- 失败，前缀通配符无法使用索引
SELECT * FROM users WHERE name LIKE '张%';     -- OK，后缀通配符可以使用索引

-- 场景4：OR 连接不同条件
SELECT * FROM users WHERE age = 25 OR name = '李四';
-- 如果 name 没有索引，整个查询可能走全表扫描

-- 场景5：IS NULL / IS NOT NULL
-- 索引列允许 NULL 时，IS NULL 可能不走索引

-- 场景6：NOT IN / NOT EXISTS / <> 操作符
SELECT * FROM users WHERE age NOT IN (20, 30, 40);  -- 失败，通常无法利用索引
SELECT * FROM users WHERE age <> 25;

索引优化实战技巧

-- 1. 覆盖索引：查询的所有列都在索引中，无需回表
SELECT age, email, name FROM users WHERE age = 25;  -- OK Index Only Scan

-- 2. 索引下推（ICP）：在索引遍历过程中利用索引列过滤数据
SET optimizer_switch = 'index_condition_pushdown=on';  -- 默认开启
SELECT * FROM users WHERE age = 25 AND name LIKE '张%';
-- 不开启 ICP：先按 age=25 找到所有主键，回表后再用 name 过滤
-- 开启 ICP：在索引树遍历时直接用 name 条件过滤，减少回表次数

-- 3. 前缀索引：长字符串列只索引前 N 个字符
ALTER TABLE users ADD INDEX idx_email_prefix (email(10));
-- 注意：前缀索引不支持 ORDER BY 和 GROUP BY

-- 4. 强制使用指定索引
SELECT * FROM users USE INDEX (idx_name) WHERE name = '张三';
SELECT * FROM users FORCE INDEX (idx_name) WHERE name = '张三';

-- 5. 索引维护：避免过度索引
OPTIMIZE TABLE users;  -- 重建索引，回收碎片空间

常见问题

Q1：索引越多越好吗？
不是。索引会占用磁盘空间，更重要的是，每次 INSERT/UPDATE/DELETE 操作都需要同时更新所有相关索引，增加了写操作的开销。对于写多读少的场景，过多索引反而会成为性能瓶颈。建议为高频查询条件建立索引，定期使用 SHOW INDEX 检查冗余索引。

Q2：主键为什么建议使用自增整型？
InnoDB 的聚簇索引数据按主键顺序物理存储。自增主键保证新记录始终追加到页面末尾，避免了 B+ 树的节点分裂和页分裂，插入性能最优。使用 UUID 或字符串作为主键会导致数据频繁移动，严重影响插入性能。

Q3：慢查询突然变快是索引生效了吗？
不一定。可能的原因包括：数据被缓存（buffer pool）、统计信息更新后优化器选择了不同执行计划、或者数据量发生了变化。建议使用 EXPLAIN 和 SHOW STATUS 配合分析，不要仅凭响应时间判断。