数据库索引优化：让查询速度提升 100 倍

数据库性能优化中，索引是最有效的手段之一。正确的索引可以让查询从几秒降到几毫秒。

索引的工作原理

索引就像书的目录。没有索引时，数据库需要全表扫描（O(n)）；有索引时，可以通过 B+ 树快速定位（O(log n)）。

何时创建索引

应该创建索引的场景

1. 主键和外键 - 自动创建
2. 频繁查询的列 - WHERE 条件中的列
3. 排序和分组的列 - ORDER BY、GROUP BY
4. JOIN 连接的列 - 关联字段
5. 高选择性的列 - 区分度高的列

不应该创建索引的场景

1. 表太小 - 全表扫描更快
2. 频繁更新的列 - 索引维护成本高
3. 低选择性的列 - 如性别、状态
4. 很少查询的列 - 浪费空间

索引类型

1. 普通索引（INDEX）

CREATE INDEX idx_email ON users(email);

2. 唯一索引（UNIQUE）

CREATE UNIQUE INDEX idx_username ON users(username);

3. 复合索引（COMPOSITE）

CREATE INDEX idx_name_age ON users(name, age);

最左前缀原则：查询必须从最左列开始才能使用索引。

-- ✅ 使用索引
WHERE name = "Alice" AND age = 25
WHERE name = "Alice"

-- ❌ 不使用索引
WHERE age = 25

4. 覆盖索引（COVERING）

查询的列都在索引中，无需回表：

CREATE INDEX idx_email_name ON users(email, name);

-- 覆盖索引，超快！
SELECT name FROM users WHERE email = "a@test.com";

索引优化实战

1. 分析查询

EXPLAIN SELECT * FROM users WHERE email = "a@test.com";

关注：

• type: 访问类型（ALL > index > range > ref > const）
• key: 实际使用的索引
• rows: 扫描行数
• Extra: 额外信息（Using index 是好的）

2. 避免索引失效

-- ❌ 对索引列使用函数
WHERE DATE(created_at) = "2024-01-01"

-- ✅ 改为范围查询
WHERE created_at >= "2024-01-01" AND created_at < "2024-01-02"

-- ❌ 隐式类型转换
WHERE phone = 13800138000

-- ✅ 保持类型一致
WHERE phone = "13800138000"

-- ❌ LIKE 以通配符开头
WHERE name LIKE "%Alice%"

-- ✅ LIKE 不以通配符开头
WHERE name LIKE "Alice%"

3. 优化 ORDER BY

-- 创建复合索引
CREATE INDEX idx_created_status ON articles(created_at, status);

-- ✅ 使用索引排序
ORDER BY created_at DESC, status ASC

-- ❌ 排序方向不一致，索引失效
ORDER BY created_at DESC, status DESC

索引维护

1. 定期检查

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

-- PostgreSQL 查看索引扫描次数
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes;

2. 删除无用索引

无用的索引浪费空间、降低写入性能。

3. 重建索引

-- MySQL
OPTIMIZE TABLE users;

-- PostgreSQL
REINDEX TABLE users;

性能对比

场景	无索引	有索引	提升
100 万行查询	2.5s	0.02s	125 倍
JOIN 操作	10s	0.1s	100 倍
ORDER BY	3s	0.05s	60 倍

结语

索引是数据库优化的利器，但不是银弹。理解索引原理，根据实际查询场景设计索引，定期监控和优化，才能让数据库保持最佳性能。

记住：没有索引的数据库就像没有目录的书——能找到，但很慢。