MySQL索引底层原理：从B+树到聚簇索引

本文深入剖析MySQL索引的底层实现原理，帮助你理解为什么索引能加速查询，以及如何设计高效的索引。

为什么索引能加速查询？

没有索引时，MySQL需要全表扫描（Full Table Scan），时间复杂度O(n)。索引通过有序数据结构，将查询复杂度降低到O(log n)。

B+树：MySQL索引的核心

为什么选择B+树？

B+树结构详解

                    ┌───┐
                    │ 5 │
                    └─┬─┘
              ┌───────┴───────┐
              ↓               ↓
          ┌───┐           ┌───┐
          │ 2 │           │ 7 │
          └─┬─┘           └─┬─┘
       ┌────┴────┐     ┌────┴────┐
       ↓         ↓     ↓         ↓
     ┌─┐ ┌─┐ ┌─┐ ┌─┐ ┌─┐ ┌─┐ ┌─┐ ┌─┐
     │1│ │2│ │3│ │4│ │5│ │6│ │7│ │8│
     └─┘ └─┘ └─┘ └─┘ └─┘ └─┘ └─┘ └─┘
     
     叶子节点形成双向链表，支持范围查询

B+树特点：

1. 非叶子节点只存储索引，不存储数据
2. 叶子节点存储所有数据，形成双向链表
3. 树高稳定，3-4层可存储千万级数据
4. 范围查询高效，遍历链表即可

计算B+树能存储多少数据

假设：

• 索引字段为BIGINT（8字节）
• 每个指针6字节
• 页大小16KB

单个节点可存储指针数 = 16KB / (8 + 6) ≈ 1170

树高3层可存储数据量 = 1170 × 1170 × 1170 ≈ 1.6亿

聚簇索引与非聚簇索引

聚簇索引（Clustered Index）

聚簇索引：数据行与索引存储在一起

┌─────────────────────────────────┐
│  主键索引  │  数据行  │  数据行  │
│    1     │  name=A  │  age=20  │
│    2     │  name=B  │  age=25  │
│    3     │  name=C  │  age=30  │
└─────────────────────────────────┘

特点：

• 每张表只能有一个聚簇索引
• 数据按主键顺序存储
• 查询主键直接获取数据，无需回表

非聚簇索引（Secondary Index）

非聚簇索引：索引与数据分离

索引页：                    数据页：
┌─────────┐               ┌─────────┐
│  1  ────→│──────────────→│ name=A  │
│  2  ────→│──────────────→│ name=B  │
│  3  ────→│──────────────→│ name=C  │
└─────────┘               └─────────┘

特点：

• 可以有多个非聚簇索引
• 索引节点存储主键值
• 查询需要"回表"获取完整数据

回表查询示例

-- 表结构
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX idx_name (name)
);

-- 执行计划分析
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 输出：
-- type: ref
-- key: idx_name
-- Extra: Using index condition
-- 注意：这里需要回表查询age字段

索引下推（ICP）

问题：回表次数过多

-- 查询条件包含索引列和非索引列
SELECT * FROM users WHERE name LIKE '张%' AND age > 20;

传统方式：

1. 在idx_name索引中查找name LIKE ‘张%’
2. 回表获取所有匹配的行
3. 在Server层过滤age > 20

索引下推（ICP）：

1. 在idx_name索引中同时过滤name LIKE ‘张%’ AND age > 20
2. 只回表满足条件的行
3. 减少回表次数

开启ICP

-- MySQL 5.6+ 默认开启
SET optimizer_switch = 'index_condition_pushdown=on';

-- 查看是否使用ICP
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age > 20;
-- Extra: Using index condition

覆盖索引

定义

查询的字段全部在索引中，无需回表。

-- 创建覆盖索引
CREATE INDEX idx_name_age ON users(name, age);

-- 覆盖索引查询
SELECT name, age FROM users WHERE name = '张三';
-- Extra: Using index

覆盖索引的优势

普通索引查询：
索引查找 → 回表 → 获取数据

覆盖索引查询：
索引查找 → 直接返回数据

联合索引与最左前缀原则

联合索引结构

联合索引 (name, age)：

┌─────────┬───────┐
│  name   │  age  │
├─────────┼───────┤
│  张三   │  20   │
│  张三   │  25   │
│  李四   │  30   │
│  王五   │  35   │
└─────────┴───────┘

最左前缀原则

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- ✅ 可以使用索引
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 20;

-- ❌ 无法使用索引
SELECT * FROM users WHERE age = 20;

-- ✅ 可以使用索引（范围查询）
SELECT * FROM users WHERE name LIKE '张%' AND age > 20;

索引失效场景

-- 1. 对索引字段使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2024;
-- 正确写法：
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- 2. 隐式类型转换
SELECT * FROM users WHERE name = 123;  -- name是VARCHAR
-- 正确写法：
SELECT * FROM users WHERE name = '123';

-- 3. 使用OR条件
SELECT * FROM users WHERE name = '张三' OR age = 20;
-- 正确写法：使用UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 20;

-- 4. LIKE以通配符开头
SELECT * FROM users WHERE name LIKE '%张三';
-- 正确写法：使用前缀匹配
SELECT * FROM users WHERE name LIKE '张三%';

实战：索引设计最佳实践

1. 选择合适的索引列

-- 高选择性列优先
-- 低选择性列（如性别）不适合单独建索引
SELECT COUNT(DISTINCT gender) / COUNT(*) FROM users;  -- 选择性低
SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM users; -- 选择性高

2. 索引列顺序

-- 等值查询列在前，范围查询列在后
-- 适合：WHERE status = 'active' AND create_time > '2024-01-01'
CREATE INDEX idx_status_time ON orders(status, create_time);

3. 避免冗余索引

-- 冗余索引：idx_name 和 idx_name_age 存在包含关系
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_name_age ON users(name, age);

-- 删除冗余索引
DROP INDEX idx_name ON users;

4. 监控索引使用

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引大小
SELECT table_name, index_name, 
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size';

总结

索引设计原则：

1. 选择性高的列优先
2. 等值查询列在前，范围查询列在后
3. 避免冗余索引
4. 监控索引使用情况
5. 定期分析慢查询