一、索引概述
索引是数据库中用于加速数据检索的数据结构。索引通过建立数据的有序结构，将数据查找时间从O(n)降低到O(log n)，是数据库性能优化的核心手段。
二、B+树索引原理
2.1 B+树结构
B+树是数据库索引的主要数据结构：
graph TD
A[根节点] --> B[分支节点1]
A --> C[分支节点2]
A --> D[分支节点3]
B --> E[叶子节点1]
B --> F[叶子节点2]
C --> G[叶子节点3]
C --> H[叶子节点4]
D --> I[叶子节点5]
D --> J[叶子节点6]
E --> K[数据记录]
F --> L[数据记录]
G --> M[数据记录]
H --> N[数据记录]
I --> O[数据记录]
J --> P[数据记录]
2.2 B+树特点
- 平衡树：所有叶子节点深度相同
- 有序存储：数据按索引键有序排列
- 范围查询：支持高效的范围查询
- 全表扫描：叶子节点链表支持全表扫描
2.3 B+树查询过程
sequenceDiagram
participant App as 应用
participant Index as B+树索引
participant Data as 数据页
App->>Index: 查询 key=15
Index->>Index: 根节点定位
Index->>Index: 分支节点定位
Index->>Data: 叶子节点查找
Data-->>App: 返回数据记录
三、索引类型
3.1 主键索引
主键索引是唯一且非空的索引：
CREATE TABLE users (
id INT PRIMARY KEY, -- 主键索引
name VARCHAR(50)
);3.2 唯一索引
唯一索引保证索引列的值唯一：
CREATE UNIQUE INDEX idx_email ON users(email);3.3 普通索引
普通索引不保证唯一性：
CREATE INDEX idx_name ON users(name);3.4 复合索引
复合索引包含多个列：
CREATE INDEX idx_user_status ON users(status, created_at);3.5 全文索引
全文索引用于文本搜索：
CREATE FULLTEXT INDEX idx_content ON articles(content);3.6 索引类型对比
| 索引类型 | 特点 | 适用场景 |
|---|---|---|
| 主键索引 | 唯一、非空 | 主键查询 |
| 唯一索引 | 唯一 | 唯一约束 |
| 普通索引 | 无约束 | 普通查询 |
| 复合索引 | 多列 | 多条件查询 |
| 全文索引 | 文本搜索 | 全文搜索 |
四、索引选择策略
4.1 索引列选择原则
- 高频查询列：经常作为查询条件的列
- 区分度高的列：列值分布均匀的列
- 小数据类型列：占用空间小的列
- 排序分组列：经常用于ORDER BY/GROUP BY的列
4.2 复合索引顺序
复合索引遵循最左前缀原则：
graph LR
A[复合索引: a, b, c] --> B[a]
A --> C[a, b]
A --> D[a, b, c]
B --> B1[可使用索引]
C --> C1[可使用索引]
D --> D1[可使用索引]
E[b] --> E1[不可使用索引]
F[c] --> F1[不可使用索引]
G[b, c] --> G1[不可使用索引]
4.3 索引选择性
索引选择性 = 唯一值数量 / 总行数：
-- 高选择性：适合建索引
SELECT COUNT(DISTINCT email) / COUNT(*) FROM users; -- 接近1
-- 低选择性：不适合建索引
SELECT COUNT(DISTINCT gender) / COUNT(*) FROM users; -- 接近0.5五、索引失效场景
5.1 导致索引失效的操作
| 操作 | 示例 | 说明 |
|---|---|---|
| 函数操作 | WHERE YEAR(date) = 2024 | 索引列上使用函数 |
| 类型转换 | WHERE id = '123' | 隐式类型转换 |
| 不等于 | WHERE status != 1 | 不等于操作 |
| LIKE前缀% | WHERE name LIKE '%abc' | 前缀模糊查询 |
| OR条件 | WHERE a=1 OR b=2 | OR条件未全部索引 |
| NOT IN | WHERE id NOT IN (1,2,3) | NOT IN操作 |
5.2 避免索引失效
-- 避免函数操作
-- 不好
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- 好
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
-- 避免类型转换
-- 不好
SELECT * FROM users WHERE id = '123';
-- 好
SELECT * FROM users WHERE id = 123;
-- 使用UNION替代OR
-- 不好
SELECT * FROM users WHERE status = 1 OR status = 2;
-- 好
SELECT * FROM users WHERE status = 1
UNION
SELECT * FROM users WHERE status = 2;六、覆盖索引
6.1 覆盖索引概念
覆盖索引是指索引包含查询所需的所有列：
graph TD
A[查询: SELECT name, age FROM users WHERE status=1]
B[普通索引 idx_status] --> C[status列]
C --> D[回表查询]
D --> E[数据页]
F[覆盖索引 idx_status_name_age] --> G[status, name, age]
G --> H[直接返回]
A --> B
A --> F
6.2 覆盖索引创建
-- 创建覆盖索引
CREATE INDEX idx_status_name_age ON users(status, name, age);
-- 查询可以使用覆盖索引，无需回表
SELECT name, age FROM users WHERE status = 1;6.3 覆盖索引适用场景
- 频繁查询的列组合：经常一起查询的列
- 统计查询：COUNT、SUM等聚合查询
- 分页查询：需要排序和分页的查询
七、索引维护
7.1 索引维护成本
| 操作 | 索引影响 | 说明 |
|---|---|---|
| INSERT | 慢 | 需要更新所有索引 |
| UPDATE | 慢 | 需要更新相关索引 |
| DELETE | 慢 | 需要更新所有索引 |
| SELECT | 快 | 利用索引加速 |
7.2 索引数量控制
索引不是越多越好，需要权衡查询和写入性能：
- 读多写少：可以多建索引
- 写多读少：尽量少建索引
- 一般建议：单表索引不超过5个
7.3 索引重建
-- 重建索引
ALTER TABLE users ENGINE=InnoDB;
-- 重新创建索引
DROP INDEX idx_name ON users;
CREATE INDEX idx_name ON users(name);
-- MySQL重建索引
ALTER TABLE users FORCE INDEX(idx_name);八、索引监控
8.1 查看索引使用情况
-- MySQL查看索引使用
SHOW INDEX FROM users;
-- 查看索引使用统计
SELECT * FROM sys.schema_unused_indexes;
-- PostgreSQL查看索引使用
SELECT * FROM pg_stat_user_indexes;8.2 索引命中率
-- MySQL查看索引命中率
SELECT
SUM(handler_read_key) / (SUM(handler_read_key) + SUM(handler_read_rnd_next)) AS hit_rate
FROM information_schema.global_status;8.3 慢查询分析
-- 开启慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
-- 分析慢查询
EXPLAIN SELECT * FROM orders WHERE status = 1;九、索引最佳实践
9.1 优先使用主键查询
主键查询效率最高，避免全表扫描。
9.2 合理设计复合索引
根据查询模式设计复合索引，遵循最左前缀原则。
9.3 使用覆盖索引
对于频繁查询，使用覆盖索引避免回表。
9.4 定期审查索引
定期检查索引使用情况，删除无用索引。
9.5 注意索引维护成本
在写入密集的表中控制索引数量。
十、总结
索引是数据库性能优化的核心，合理的索引策略能够显著提升查询性能。通过理解B+树原理、选择合适的索引类型、避免索引失效，能够构建高效的数据库查询系统。