数据库索引完全指南

什么是数据库索引

数据库索引是一种数据结构，它提供了快速访问数据库表中数据的路径。就像书籍的目录一样，索引可以帮助数据库快速定位到具体的数据行，而不需要扫描整个表。

形象化理解

想象你要在一本1000页的字典中查找单词"database"：

没有索引：你需要从第1页开始逐页翻阅，直到找到为止
有索引：你直接翻到"D"开头的目录页，快速定位到具体页码

索引就是数据库的"目录"。

性能对比示意图

1
2
3
4
5
6
7
8
9


graph TD
    A["全表扫描<br/>O(n) 时间复杂度"] --> B["逐行检查每条记录"]
    B --> C["找到匹配记录"]
    
    D["使用索引<br/>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["根节点<br/>[30, 60]"]
        
        Internal1["内部节点1<br/>[10, 20]"]
        Internal2["内部节点2<br/>[40, 50]"]
        Internal3["内部节点3<br/>[70, 80]"]
        
        Leaf1["叶子节点1<br/>[1,5,8,10]<br/>→ 数据页1"]
        Leaf2["叶子节点2<br/>[15,18,20,25]<br/>→ 数据页2"]
        Leaf3["叶子节点3<br/>[35,38,40,42]<br/>→ 数据页3"]
        Leaf4["叶子节点4<br/>[45,48,50,55]<br/>→ 数据页4"]
        Leaf5["叶子节点5<br/>[65,68,70,75]<br/>→ 数据页5"]
        Leaf6["叶子节点6<br/>[78,80,85,90]<br/>→ 数据页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]<br/>42 > 30 且 42 < 60"]
    C --> D["进入中间节点2: [40, 50]<br/>42 > 40 且 42 < 50"]
    D --> E["进入叶子节点3: [35,38,40,42]<br/>找到目标值 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+树索引<br/>（最常用）"]
    B --> B2["哈希索引<br/>（等值查询）"]
    B --> B3["全文索引<br/>（文本搜索）"]
    
    C --> C1["单列索引<br/>CREATE INDEX idx_name ON users(name)"]
    C --> C2["复合索引<br/>CREATE INDEX idx_name_age ON users(name,age)"]
    
    D --> D1["普通索引<br/>（可重复）"]
    D --> D2["唯一索引<br/>（不可重复）"]
    D --> D3["主键索引<br/>（主键约束）"]
    
    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["函数操作<br/>WHERE UPPER(name) = 'JOHN'"]
    A --> C["类型转换<br/>WHERE phone = 123456"]
    A --> D["前导模糊查询<br/>WHERE name LIKE '%john'"]
    A --> E["OR连接条件<br/>WHERE name='john' OR age=25"]
    A --> F["非最左匹配<br/>复合索引(name,age,city)<br/>WHERE age=25"]
    A --> G["不等于操作<br/>WHERE status != 'active'"]
    A --> H["NULL值判断<br/>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的字段创建索引
复合索引遵循最左前缀原则
避免在索引字段上使用函数
定期监控和优化索引使用情况
平衡查询性能和存储空间
考虑业务场景，不要盲目创建索引

记住这句话

索引不是万能的，但没有索引是万万不能的。合理使用索引，让你的数据库查询飞起来！

这份指南涵盖了数据库索引的方方面面，建议结合实际项目练习，加深理解。