前言

在数据库性能优化中，索引是最常用也最有效的手段。然而，很多开发者对索引的理解停留在"加索引就快"的层面，遇到慢查询时盲目加索引，结果不仅没提升性能，反而拖累了写入速度、浪费了存储空间。

本文将系统性地剖析数据库索引的底层原理、常见类型、设计原则与实战优化技巧，帮助你从"会用索引"进阶到"用好索引"。

一、为什么需要索引？

1.1 没有索引的世界

假设有一张用户表 users，包含 100 万条记录：

SELECT * FROM users WHERE email = 'alice@example.com';

如果没有索引，数据库只能进行全表扫描（Full Table Scan）：逐行读取每条记录，比较 email 字段。这意味着要读取 100 万次，磁盘 IO 和 CPU 消耗巨大。

1.2 索引的本质

索引的本质是用空间换时间：额外维护一份有序的数据结构，让查询从 O(n) 降到 O(log n) 甚至 O(1)。

打个比方，索引就像书籍的目录——你不需要翻遍整本书，通过目录就能快速定位到目标章节。

二、索引的底层数据结构

2.1 B+ 树：关系型数据库的主流选择

MySQL InnoDB、Oracle、SQL Server 等主流数据库都采用 B+ 树作为索引结构。它有几个关键特性：

• 多路平衡树：每个节点可以存储多个键值，树的高度很低（通常 3~4 层就能存千万级数据）。

• 所有数据存储在叶子节点：非叶子节点只存索引键，用于导航。

• 叶子节点通过双向链表连接：非常适合范围查询。

                    [30 | 60]
                   /    |    \
          [10|20]   [40|50]   [70|80]
            ↓          ↓          ↓
       数据页 ←→ 数据页 ←→ 数据页  (叶子节点双向链表)

为什么 B+ 树比 B 树更适合数据库？

2.2 哈希索引

哈希索引基于哈希表实现，等值查询时间复杂度 O(1)，但不支持范围查询和排序。

• 适用场景：Memory 引擎、Redis、等值查询密集的场景。

• 不适用：WHERE age > 18、ORDER BY 等。

2.3 LSM 树：写优化的选择

HBase、RocksDB、LevelDB 等 NoSQL 数据库使用 LSM 树（Log-Structured Merge Tree），将随机写转为顺序写，非常适合写多读少的场景。

2.4 其他结构

• R 树：用于地理空间数据（如 PostGIS）。

• 倒排索引：全文检索（如 Elasticsearch）。

• 位图索引：低基数列（如性别、状态）。

三、聚簇索引与非聚簇索引

这是理解数据库索引的关键概念。

3.1 聚簇索引（Clustered Index）

数据行本身按照索引顺序存储，索引即数据。InnoDB 的主键索引就是聚簇索引。

-- InnoDB 中，主键索引的叶子节点直接存储整行数据
CREATE TABLE users (
    id BIGINT PRIMARY KEY,   -- 聚簇索引
    name VARCHAR(50),
    email VARCHAR(100)
);

一张表只能有一个聚簇索引，因为数据只能按一种顺序物理存储。

3.2 非聚簇索引（Secondary Index / 二级索引）

叶子节点存储的是索引列 + 主键值，而不是完整行数据。

CREATE INDEX idx_email ON users(email);

查询流程：

SELECT * FROM users WHERE email = 'alice@example.com';

1. 在 idx_email 索引树中找到 alice@example.com，拿到主键 id = 100。

2. 回到主键索引树中查找 id = 100 的完整数据。

第 2 步就叫做"回表"（Back to Table）。

3.3 覆盖索引：避免回表

如果查询所需的所有列都包含在索引中，就无需回表：

-- 建立联合索引
CREATE INDEX idx_email_name ON users(email, name);

-- 以下查询只需扫描索引，无需回表
SELECT name FROM users WHERE email = 'alice@example.com';

EXPLAIN 中会显示 Using index，这是性能优化的一个重要标志。

四、联合索引与最左前缀原则

4.1 联合索引的结构

CREATE INDEX idx_a_b_c ON t(a, b, c);

索引按照 a 排序，a 相同按 b 排序，b 相同按 c 排序。

4.2 最左前缀原则

查询条件必须从联合索引的最左列开始，才能利用索引：

-- ✅ 能用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3        -- 只用到 a 列

-- ❌ 不能用索引
WHERE b = 2
WHERE b = 2 AND c = 3
WHERE c = 3

4.3 范围查询的"断点"效应

-- 索引：idx_a_b_c
WHERE a = 1 AND b > 2 AND c = 3

b > 2 是范围查询，c 列无法利用索引（只能用于过滤）。因为范围之后，c 在索引中不再有序。

设计启示：联合索引中，等值查询列放前面，范围查询列放最后。

五、EXPLAIN：索引分析利器

EXPLAIN 是诊断索引问题的核心工具。重点关注以下字段：

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

Extra 字段的关键提示：

• Using index：✅ 覆盖索引，性能优秀

• Using where：需回表后过滤

• Using filesort：❌ 需要额外排序，应优化

• Using temporary：❌ 使用了临时表，常见于 GROUP BY

• Using index condition：索引下推，优化手段

六、索引设计原则

6.1 应该建索引的列

• 频繁出现在 WHERE、ORDER BY、GROUP BY、JOIN ON 中的列。

• 区分度高（基数大）的列，如邮箱、订单号。

• 主键、外键列。

6.2 不应该建索引的列

• 区分度低的列：如性别、状态（只有几个值），索引效果差。

• 频繁更新的列：索引维护成本高。

• 很少查询的列。

• 大字段：如 TEXT、BLOB。

6.3 基数（Cardinality）的重要性

基数指列中不同值的数量。基数越高，索引选择性越好：

性别列：基数 = 2         → 索引几乎无用
邮箱列：基数 ≈ 100万     → 索引效果极佳

可以用以下语句查看：

SHOW INDEX FROM users;
-- 关注 Cardinality 列

6.4 联合索引的列顺序

原则：等值列在前，范围列在后，排序列考虑在内。

-- 查询：WHERE status = 1 AND created_at > '2024-01-01' ORDER BY id
CREATE INDEX idx_status_created_id ON orders(status, created_at, id);

七、常见索引失效场景

这是面试和实战中的高频问题。

7.1 在索引列上做运算或函数

-- ❌ 索引失效
WHERE YEAR(created_at) = 2024
WHERE id + 1 = 100

-- ✅ 改写
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
WHERE id = 99

7.2 隐式类型转换

-- phone 是 VARCHAR 类型
-- ❌ 隐式转换，索引失效
WHERE phone = 13800138000

-- ✅
WHERE phone = '13800138000'

7.3 前导模糊匹配

-- ❌ 索引失效
WHERE name LIKE '%alice'

-- ✅ 可以用索引
WHERE name LIKE 'alice%'

7.4 使用 OR 连接非索引列

-- 如果 b 没有索引，整个查询可能全表扫描
WHERE a = 1 OR b = 2

7.5 违反最左前缀原则

如第 4 节所述。

7.6 !=、NOT IN、IS NOT NULL

这些操作通常导致优化器放弃索引（因为要扫描的数据比例太高，全表扫描反而更快）。

八、实战优化案例

案例一：分页查询优化

问题：深分页性能差。

-- ❌ 越翻越慢，需要扫描前 100 万行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

优化方案一：延迟关联

-- ✅ 先用覆盖索引拿到主键，再关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;

优化方案二：游标分页

-- ✅ 记录上次的最大 id，避免 offset
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

案例二：联合索引优化排序

问题：ORDER BY 产生 Using filesort。

-- 索引：idx_status
SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC;

优化：

-- 建立联合索引，让排序也走索引
CREATE INDEX idx_status_created ON orders(status, created_at);

案例三：COUNT 优化

-- ❌ 慢
SELECT COUNT(*) FROM orders WHERE status = 1;

-- ✅ 用覆盖索引 + 近似值（如业务允许）
SELECT COUNT(id) FROM orders WHERE status = 1;

-- ✅ 或维护计数表，实时更新

九、索引的代价

索引并非免费午餐，它有以下成本：

1. 存储成本：每个索引都占用磁盘空间。

2. 写入成本：INSERT、UPDATE、DELETE 都要维护索引，索引越多写入越慢。

3. 优化器成本：索引过多，查询优化器选择执行计划的时间增加。

经验法则：单表索引数量建议不超过 5~6 个。

十、总结

索引优化的本质，是理解数据如何被存储和检索。掌握了 B+ 树的原理和最左前缀原则，你就能在设计索引时胸有成竹，而不是靠"试一试"来碰运气。

最后送大家一句话：好的索引设计，来自对业务查询模式的深刻理解，而不是对索引数量的盲目堆砌。

参考资料

• 《高性能 MySQL》（第 4 版）

• MySQL 官方文档：Optimization and Indexes

• 《数据库系统概念》（Silberschatz 等著）