数据库索引设计与优化指南

索引基础

什么是索引？

索引是数据库表中一列或多列值的副本，按特定顺序存储，用于加速数据检索。

类比：书籍的目录

索引的优势

• 快速查询：大幅减少 I/O 操作
• 排序加速：避免全表扫描
• 唯一性约束：保证数据完整性

索引的代价

• 存储开销：占用额外磁盘空间
• 写入性能：降低 INSERT/UPDATE/DELETE 速度
• 维护成本：需要定期重建和优化

索引类型

1. B-Tree 索引（最常用）

特点：

• 平衡树结构
• 适合范围查询
• 支持排序

适用场景：

-- 等值查询
WHERE id = 100

-- 范围查询
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

-- 排序
ORDER BY created_at DESC

-- 前缀匹配
WHERE name LIKE 'Zhang%'
​

创建索引：

CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_created ON orders(created_at DESC);
​

2. 哈希索引

特点：

• 只支持等值比较
• 查询速度 O(1)
• 不支持范围查询

适用场景：

-- 等值查询
WHERE user_id = 123

-- 哈希连接
WHERE a.user_id = b.id
​

3. 全文索引

特点：

• 支持文本搜索
• 相关性排序
• 支持多语言

使用示例：

-- 创建全文索引
CREATE FULLTEXT INDEX idx_article_content ON articles(content);

-- 全文搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);
​

4. 空间索引

特点：

• 支持地理数据
• 空间查询
• 距离计算

使用示例：

-- 创建空间索引
CREATE SPATIAL INDEX idx_location ON locations(coordinates);

-- 查询附近的位置
SELECT * FROM locations 
WHERE ST_Distance_Sphere(coordinates, POINT(116.4, 39.9)) < 1000;
​

索引设计原则

1. 选择合适的列

适合索引的列

• WHERE 子句中的列
• JOIN 条件中的列
• ORDER BY 中的列
• 经常查询的列

不适合索引的列

• 频繁更新的列
• 数据区分度低的列（如性别）
• BLOB/TEXT 大文本
• 很少查询的列

2. 单列索引 vs 复合索引

单列索引

CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
​

复合索引（推荐）

CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);
​

复合索引规则：

• 最左前缀原则：可以按左边的列查询
• 列顺序重要：区分度高的放前面
• 覆盖索引：包含所有查询字段

使用示例：

-- ✅ 可以使用索引
WHERE user_id = 1
WHERE user_id = 1 AND status = 'pending'
WHERE user_id = 1 AND status = 'pending' AND created_at > '2024-01-01'

-- ❌ 不能使用索引（跳过了 user_id）
WHERE status = 'pending'
WHERE status = 'pending' AND created_at > '2024-01-01'
​

3. 覆盖索引

定义：索引包含查询所需的所有字段，避免回表。

-- 创建覆盖索引
CREATE INDEX idx_user_cover ON users(user_id, name, email);

-- 查询只使用索引，不回表
SELECT user_id, name, email FROM users WHERE user_id = 1;
​

优势：

• 避免回表查询
• 减少随机 I/O
• 提升查询性能

4. 唯一索引

-- 创建唯一索引
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- 唯一约束（自动创建唯一索引）
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);
​

索引优化策略

1. 索引选择性

选择性 = 不重复值数量 / 总行数

理想选择性：接近 1.0

-- 计算选择性
SELECT 
    COUNT(DISTINCT email) / COUNT(*) AS email_selectivity,
    COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity
FROM users;

-- email_selectivity: 0.98 (高，适合索引)
-- gender_selectivity: 0.02 (低，不适合索引)
​

2. 前缀索引

适用场景：长文本字段（如 URL、TEXT）

-- 创建前缀索引（只索引前 20 个字符）
CREATE INDEX idx_url_prefix ON logs(url(20));

-- 计算最优前缀长度
SELECT 
    COUNT(DISTINCT LEFT(url, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS sel_20,
    COUNT(DISTINCT LEFT(url, 30)) / COUNT(*) AS sel_30
FROM logs;
​

3. 函数索引

适用场景：经常对列进行函数计算

-- MySQL 8.0+ 函数索引
CREATE INDEX idx_email_lower ON users((LOWER(email)));

-- 使用函数索引
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
​

4. 表达式索引

-- PostgreSQL 表达式索引
CREATE INDEX idx_price_total ON orders((price * quantity));

-- 使用表达式索引
SELECT * FROM orders WHERE price * quantity > 1000;
​

查询优化

1. 使用 EXPLAIN 分析

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- 结果解读
-- type: ALL(全表), index(索引扫描), range(范围), ref(索引查找)
-- key: 实际使用的索引
-- rows: 扫描的行数（估计）
-- Extra: Using index(覆盖索引), Using filesort(文件排序)
​

2. 避免 SELECT *

-- ❌ 不好的做法
SELECT * FROM users WHERE user_id = 1;

-- ✅ 好的做法
SELECT user_id, name, email FROM users WHERE user_id = 1;
​

3. 避免在索引列上使用函数

-- ❌ 无法使用索引
WHERE YEAR(created_at) = 2024

-- ✅ 可以使用索引
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
​

4. 优化 LIKE 查询

-- ✅ 可以使用索引（前缀匹配）
WHERE name LIKE 'Zhang%'

-- ❌ 无法使用索引（后缀/模糊匹配）
WHERE name LIKE '%Zhang'
WHERE name LIKE '%Zhang%'

-- 解决方案：使用全文索引
CREATE FULLTEXT INDEX idx_name ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('Zhang' IN BOOLEAN MODE);
​

索引维护

1. 分析表

-- MySQL 分析表
ANALYZE TABLE users;

-- PostgreSQL 分析表
ANALYZE users;
​

2. 重建索引

-- MySQL 重建索引
OPTIMIZE TABLE users;

-- PostgreSQL 重建索引
REINDEX TABLE users;
REINDEX INDEX idx_user_email;
​

3. 监控索引使用

-- 查看未使用的索引（PostgreSQL）
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%_pkey';
​

性能测试

1. 对比测试

-- 无索引
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

-- 有索引
CREATE INDEX idx_user_id ON orders(user_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;
​

2. 压力测试

# 使用 sysbench 进行压力测试
sysbench oltp_read_write \
  --mysql-host=localhost \
  --mysql-user=test \
  --mysql-password=test \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  run
​

常见问题

1. 索引失效

原因：

• 使用函数或表达式
• 隐式类型转换
• OR 条件
• LIKE '%...'

解决方案：

• 避免在索引列上使用函数
• 确保类型匹配
• 使用 UNION 替代 OR
• 使用全文索引

2. 索引过多

问题：

• 占用大量磁盘空间
• 降低写入性能
• 优化器选择困难

最佳实践：

• 单表索引不超过 5 个
• 定期清理无用索引
• 监控索引使用情况

3. 索引碎片

现象：

• 查询性能下降
• 磁盘空间浪费

解决：

-- MySQL
OPTIMIZE TABLE table_name;

-- PostgreSQL
VACUUM ANALYZE table_name;
​

最佳实践总结

1. 索引策略：

   • 为高频查询创建索引
   • 使用复合索引提高效率
   • 考虑覆盖索引避免回表

2. 监控维护：

   • 定期分析索引使用情况
   • 删除无用索引
   • 重建碎片化索引

3. 测试验证：

   • 使用 EXPLAIN 分析查询
   • 压力测试验证效果
   • 持续优化调整

4. 权衡取舍：

   • 空间换时间
   • 写入换查询
   • 根据业务场景选择

记住：索引是把双刃剑，合理使用才能发挥最大价值！