数据库索引完全指南
目录
什么是数据库索引
索引的基本原理
索引的类型
索引的创建和使用
索引的优缺点
索引失效的场景
索引优化实践
总结
什么是数据库索引
数据库索引是一种数据结构，它提供了快速访问数据库表中数据的路径。就像书籍的目录一样，索引可以帮助数据库快速定位到具体的数据行，而不需要扫描整个表。
形象化理解
想象你要在一本1000页的字典中查找单词"database"：
没有索引：你需要从第1页开始逐页翻阅，直到找到为止
有索引：你直接翻到"D"开头的目录页，快速定位到具体页码
索引就是数据库的"目录"。
性能对比示意图
1
2
3
4
5
6
7
8
9
graph TD
A["全表扫描
O(n) 时间复杂度"] --> B["逐行检查每条记录"]
B --> C["找到匹配记录"]
D["使用索引
O(log n) 时间复杂度"] --> E["通过索引快速定位"]
E --> F["直接访问目标记录"]
G["1000万记录对比"] --> H["全表扫描: 最多1000万次比较"]
G --> I["B+树索引: 最多4次比较"]
索引的基本原理
数据存储结构
数据库中的数据通常存储在**页(Page)**中，每个页包含多条记录。
1
2
3
4
5
表数据存储示意：
页1: [记录1, 记录2, 记录3, ...]
页2: [记录101, 记录102, 记录103, ...]
页3: [记录201, 记录202, 记录203, ...]
...
B+树索引原理
大多数数据库使用B+树作为索引的数据结构：
1
2
3
4
5
6
B+树索引结构：
根节点
/ \
内部节点1 内部节点2
/ | \ / | \
叶子节点1 叶子2 叶子3 叶子4 叶子5 叶子6
B+树的特点：
所有数据都存储在叶子节点
叶子节点之间有指针连接，便于范围查询
树的高度较低，减少磁盘I/O次数
支持顺序访问和随机访问
B+树详细结构图
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
graph TD
subgraph "B+树索引结构"
Root["根节点
[30, 60]"]
Internal1["内部节点1
[10, 20]"]
Internal2["内部节点2
[40, 50]"]
Internal3["内部节点3
[70, 80]"]
Leaf1["叶子节点1
[1,5,8,10]
→ 数据页1"]
Leaf2["叶子节点2
[15,18,20,25]
→ 数据页2"]
Leaf3["叶子节点3
[35,38,40,42]
→ 数据页3"]
Leaf4["叶子节点4
[45,48,50,55]
→ 数据页4"]
Leaf5["叶子节点5
[65,68,70,75]
→ 数据页5"]
Leaf6["叶子节点6
[78,80,85,90]
→ 数据页6"]
Root --> Internal1
Root --> Internal2
Root --> Internal3
Internal1 --> Leaf1
Internal1 --> Leaf2
Internal2 --> Leaf3
Internal2 --> Leaf4
Internal3 --> Leaf5
Internal3 --> Leaf6
Leaf1 -.-> Leaf2
Leaf2 -.-> Leaf3
Leaf3 -.-> Leaf4
Leaf4 -.-> Leaf5
Leaf5 -.-> Leaf6
end
索引查找过程
从根节点开始
根据比较结果选择分支
逐层向下直到叶子节点
在叶子节点中找到目标数据
查找过程流程图
1
2
3
4
5
6
7
8
9
10
11
flowchart TD
A["查询: SELECT * FROM users WHERE id = 42"] --> B["开始从根节点查找"]
B --> C["根节点: [30, 60]
42 > 30 且 42 < 60"]
C --> D["进入中间节点2: [40, 50]
42 > 40 且 42 < 50"]
D --> E["进入叶子节点3: [35,38,40,42]
找到目标值 42"]
E --> F["通过指针访问数据页3"]
F --> G["返回完整记录数据"]
style A fill:#e1f5fe
style G fill:#c8e6c9
style E fill:#fff3e0
索引的类型
索引分类总览
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
graph TD
A["数据库索引分类"] --> B["按数据结构"]
A --> C["按字段数量"]
A --> D["按功能特性"]
B --> B1["B+树索引
（最常用）"]
B --> B2["哈希索引
（等值查询）"]
B --> B3["全文索引
（文本搜索）"]
C --> C1["单列索引
CREATE INDEX idx_name ON users(name)"]
C --> C2["复合索引
CREATE INDEX idx_name_age ON users(name,age)"]
D --> D1["普通索引
（可重复）"]
D --> D2["唯一索引
（不可重复）"]
D --> D3["主键索引
（主键约束）"]
style B1 fill:#c8e6c9
style C2 fill:#fff3e0
style D3 fill:#ffcdd2
1. 按数据结构分类
B+树索引（最常用）
特点：平衡多路搜索树
适用：等值查询、范围查询、排序
存储引擎：InnoDB、MyISAM
哈希索引
特点：基于哈希表
适用：等值查询
限制：不支持范围查询、排序
存储引擎：Memory
2. 按字段数量分类
单列索引
1
CREATE INDEX idx_name ON users(name);
复合索引（联合索引）
1
CREATE INDEX idx_name_age ON users(name, age);
3. 按功能分类
普通索引
1
CREATE INDEX idx_email ON users(email);
唯一索引
1
CREATE UNIQUE INDEX idx_username ON users(username);
主键索引
1
ALTER TABLE users ADD PRIMARY KEY(id);
全文索引
1
CREATE FULLTEXT INDEX idx_content ON articles(content);
索引的创建和使用
创建索引
建表时创建
1
2
3
4
5
6
7
8
9
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100),
age INT,
created_at TIMESTAMP,
INDEX idx_email (email),
INDEX idx_age_created (age, created_at)
);
建表后创建
1
2
3
4
5
6
7
8
-- 普通索引
CREATE INDEX idx_email ON users(email);
-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);
-- 复合索引
CREATE INDEX idx_name_age ON users(name, age);
查看索引
1
2
3
4
5
-- 查看表的所有索引
SHOW INDEX FROM users;
-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
删除索引
1
DROP INDEX idx_email ON users;
索引的优缺点
优点
1. 提高查询速度
无索引：全表扫描，时间复杂度 O(n)
有索引：B+树查找，时间复杂度 O(log n)
2. 加速排序
1
2
-- 如果name字段有索引，以下查询会很快
SELECT * FROM users ORDER BY name;
3. 提升连接效率
1
2
3
-- 如果user_id有索引，JOIN操作会更快
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;
4. 加速分组
1
2
-- 如果status有索引，GROUP BY会更快
SELECT status, COUNT(*) FROM orders GROUP BY status;
缺点
1. 占用存储空间
索引需要额外的存储空间
复合索引占用空间更大
2. 降低写操作性能
INSERT：需要维护索引结构
UPDATE：可能需要更新索引
DELETE：需要从索引中删除条目
3. 维护成本
数据变更时需要同步更新索引
索引越多，维护成本越高
索引失效的场景
索引失效场景总览
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
graph LR
A["索引失效场景"] --> B["函数操作
WHERE UPPER(name) = 'JOHN'"]
A --> C["类型转换
WHERE phone = 123456"]
A --> D["前导模糊查询
WHERE name LIKE '%john'"]
A --> E["OR连接条件
WHERE name='john' OR age=25"]
A --> F["非最左匹配
复合索引(name,age,city)
WHERE age=25"]
A --> G["不等于操作
WHERE status != 'active'"]
A --> H["NULL值判断
WHERE email IS NULL"]
style B fill:#ffcdd2
style C fill:#ffcdd2
style D fill:#ffcdd2
style E fill:#ffcdd2
style F fill:#ffcdd2
style G fill:#ffcdd2
style H fill:#ffcdd2
1. 函数操作
1
2
3
4
5
-- 索引失效
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
-- 正确做法
SELECT * FROM users WHERE name = 'john';
2. 类型转换
1
2
3
4
5
-- 索引失效（假设phone是VARCHAR类型）
SELECT * FROM users WHERE phone = 12345678901;
-- 正确做法
SELECT * FROM users WHERE phone = '12345678901';
3. 前导模糊查询
1
2
3
4
5
-- 索引失效
SELECT * FROM users WHERE name LIKE '%john';
-- 可以使用索引
SELECT * FROM users WHERE name LIKE 'john%';
4. OR连接的条件
1
2
3
4
5
6
7
-- 如果age没有索引，整个查询无法使用索引
SELECT * FROM users WHERE name = 'john' OR age = 25;
-- 改写为UNION（如果两个字段都有索引）
SELECT * FROM users WHERE name = 'john'
UNION
SELECT * FROM users WHERE age = 25;
5. 复合索引的非最左匹配
1
2
3
4
5
6
7
8
9
-- 假设有复合索引 (name, age, city)
-- 可以使用索引
SELECT * FROM users WHERE name = 'john';
SELECT * FROM users WHERE name = 'john' AND age = 25;
-- 索引失效
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE city = 'beijing';
6. 不等于操作
1
2
3
4
5
-- 可能导致索引失效
SELECT * FROM users WHERE status != 'active';
-- 建议改写
SELECT * FROM users WHERE status IN ('inactive', 'deleted');
7. NULL值判断
1
2
3
-- 索引效果有限
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
索引优化实践
1. 选择合适的字段创建索引
高选择性字段
1
2
3
4
5
-- 好的选择：用户邮箱（几乎唯一）
CREATE INDEX idx_email ON users(email);
-- 差的选择：性别字段（只有几个值）
-- 不建议：CREATE INDEX idx_gender ON users(gender);
经常用于查询条件的字段
1
2
3
4
5
-- 经常用于WHERE子句
CREATE INDEX idx_status ON orders(status);
-- 经常用于JOIN
CREATE INDEX idx_user_id ON orders(user_id);
2. 复合索引设计原则
最左前缀原则
1
2
3
4
5
6
7
8
9
-- 索引：(name, age, city)
-- 可以使用索引的查询：
SELECT * FROM users WHERE name = 'john'; -- ✓
SELECT * FROM users WHERE name = 'john' AND age = 25; -- ✓
SELECT * FROM users WHERE name = 'john' AND city = 'bj'; -- ✓（但不是最优）
-- 不能使用索引的查询：
SELECT * FROM users WHERE age = 25; -- ✗
SELECT * FROM users WHERE city = 'beijing'; -- ✗
字段顺序优化
1
2
3
4
-- 原则：选择性高的字段放前面，经常一起查询的字段放一起
-- 假设查询：WHERE status = 'active' AND created_at > '2023-01-01'
-- 如果status选择性更高：
CREATE INDEX idx_status_created ON orders(status, created_at);
3. 覆盖索引优化
1
2
3
4
5
6
-- 普通查询：需要回表
CREATE INDEX idx_user_id ON orders(user_id);
SELECT order_id, total_amount FROM orders WHERE user_id = 123;
-- 覆盖索引：无需回表
CREATE INDEX idx_user_id_cover ON orders(user_id, order_id, total_amount);
4. 分页查询优化
1
2
3
4
5
6
7
8
-- 深分页问题
SELECT * FROM users ORDER BY created_at LIMIT 100000, 20;
-- 优化方案：使用索引字段
SELECT * FROM users
WHERE created_at > '2023-01-01 10:30:00'
ORDER BY created_at
LIMIT 20;
5. 索引监控和维护
查看索引使用情况
1
2
3
4
5
-- MySQL
SELECT * FROM sys.schema_unused_indexes;
-- 查看索引统计
SHOW INDEX FROM users;
定期重建索引
1
2
3
4
5
-- 重建索引（MySQL）
ALTER TABLE users DROP INDEX idx_email, ADD INDEX idx_email(email);
-- 或者
OPTIMIZE TABLE users;
实际案例分析
案例1：慢查询优化
问题查询：
1
2
3
4
SELECT * FROM orders
WHERE status = 'pending'
AND created_at >= '2023-01-01'
AND user_id IN (1,2,3,4,5);
分析过程：
使用 EXPLAIN 分析执行计划
发现全表扫描，耗时很长
分析查询条件的选择性
优化方案：
1
2
3
4
5
-- 创建复合索引
CREATE INDEX idx_status_created_user ON orders(status, created_at, user_id);
-- 或者根据实际查询模式创建
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);
案例2：复合索引设计
业务场景：
用户表经常按地区、年龄、状态查询
查询模式分析：
WHERE region = ‘beijing’
WHERE region = ‘beijing’ AND age BETWEEN 20 AND 30
WHERE region = ‘beijing’ AND status = ‘active’
索引设计：
1
2
3
4
5
6
-- 方案1：按查询频率和选择性
CREATE INDEX idx_region_age_status ON users(region, age, status);
-- 方案2：如果经常只按region和status查询
CREATE INDEX idx_region_status ON users(region, status);
CREATE INDEX idx_region_age ON users(region, age);
总结
核心要点
索引本质：用空间换时间的数据结构，类似书籍目录
主要原理：B+树结构，减少磁盘I/O，提高查询效率
使用场景：频繁查询、排序、连接的字段
设计原则：
选择性高的字段
遵循最左前缀原则
考虑覆盖索引
避免过多索引
最佳实践清单
为经常出现在WHERE子句的字段创建索引
为经常用于JOIN的字段创建索引
复合索引遵循最左前缀原则
避免在索引字段上使用函数
定期监控和优化索引使用情况
平衡查询性能和存储空间
考虑业务场景，不要盲目创建索引
记住这句话
索引不是万能的，但没有索引是万万不能的。合理使用索引，让你的数据库查询飞起来！
这份指南涵盖了数据库索引的方方面面，建议结合实际项目练习，加深理解。