理解数据库索引以提升性能

你的数据库查询耗时数秒,而实际上应该只需几毫秒。罪魁祸首是什么?缺失或设计不当的索引。数据库索引是扫描数百万行数据与直接跳转到所需数据之间的区别——但如果使用不当,会拖慢写入速度并导致存储膨胀。

本文涵盖数据库索引基础知识,从 B-tree 和复合索引到高级技术,如覆盖索引和查询计划。你将学习如何为现代数据类型建立索引,包括 JSON 和文本字段,最重要的是,如何避免现代数据库中过度索引的性能陷阱。

数据库索引的实际作用

假设查询一个包含 1000 万行的 users 表:

SELECT * FROM users WHERE email = 'user@example.com';

没有索引时,PostgreSQL 或 MySQL 必须检查每一行——全表扫描。在 email 上建立索引后,数据库使用 B-tree 结构直接跳转到匹配行,通常将查找时间从 O(n) 降低到 O(log n)。

索引本质上是特定列的排序副本,带有指向完整行的指针。权衡之处在于:以更慢的写入(必须更新索引)和额外存储(每个索引通常占表大小的 10-20%)为代价换取更快的读取。

B-tree 索引:数据库的主力

B-tree 索引支撑着大多数数据库查询。它们在平衡树结构中维护数据,每个节点包含多个排序键。这种设计最小化了磁盘 I/O——这很关键,因为磁盘访问比内存访问慢几个数量级。

CREATE INDEX idx_users_created_at ON users(created_at);

B-tree 擅长:

• 相等检查(WHERE status = 'active')
• 范围查询(WHERE created_at > '2024-01-01')
• 排序(ORDER BY created_at)
• 前缀搜索(WHERE email LIKE 'john%')

PostgreSQL 和 MySQL InnoDB 使用 B+tree 变体,其中所有数据都存储在叶节点中,使范围扫描更高效。MongoDB 的索引使用类似的 B-tree 结构,尽管文档数据库在嵌套字段索引方面增加了复杂性。

复合索引:列顺序很重要

复合索引覆盖多个列,但列顺序至关重要:

CREATE INDEX idx_events_user_date ON events(user_id, created_at);

此索引支持:

• WHERE user_id = 123
• WHERE user_id = 123 AND created_at > '2024-01-01'

但不支持:

• WHERE created_at > '2024-01-01'(没有 user_id)

索引从左到右工作。将用于相等条件的列放在前面,然后是范围条件。对于按用户和日期范围过滤事件的 API 端点,这个复合索引消除了在每列上分别建立索引的需要。

覆盖索引和查询计划

覆盖索引包含查询所需的所有列,实现仅索引扫描:

-- PostgreSQL 示例
CREATE INDEX idx_orders_covering 
ON orders(user_id, status, created_at) 
INCLUDE (total_amount);

对于此查询:

SELECT total_amount FROM orders 
WHERE user_id = 123 AND status = 'completed';

数据库永远不会访问表——所有内容都来自索引。使用 EXPLAIN 验证:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

在 PostgreSQL 中查找 “Index Only Scan”,或在 MySQL 中查找 “Using index”。查询计划揭示索引是否真正被使用。高成本的顺序扫描表明缺少索引,而未使用的索引则浪费资源。

为 JSON 和文本字段建立索引

现代应用程序存储需要专用索引的非结构化数据:

JSON 索引(PostgreSQL)

CREATE INDEX idx_metadata_gin ON products 
USING GIN (metadata);

-- 支持如下查询:
SELECT * FROM products 
WHERE metadata @> '{"category": "electronics"}';

GIN 索引高效处理 JSON 包含查询,尽管它们比 B-tree 更大且更新更慢。对于高写入表,仅索引特定 JSON 路径可能比索引整个文档更高效。

全文搜索

CREATE INDEX idx_posts_text ON posts 
USING GIN (to_tsvector('english', content));

-- 启用:
SELECT * FROM posts 
WHERE to_tsvector('english', content) @@ 'database & indexing';

文本索引启用类似 Google 的搜索功能,但对于文本密集型表可能使存储需求翻倍。

避免现代数据库中的过度索引

索引并非越多越好。每个索引:

• 减慢 INSERT/UPDATE/DELETE 操作(必须更新索引)
• 增加存储(每个索引 10-20%)
• 增加维护开销(碎片化、重建)

过度索引的迹象:

• 写入密集型表有 5 个以上索引
• 重复索引(例如 (a, b) 和 (a))
• 未使用的索引(检查 pg_stat_user_indexes 或 sys.dm_db_index_usage_stats)
• 索引大小超过表大小

最佳实践:

1. 从主键和外键索引开始
2. 基于慢查询日志而非假设添加索引
3. 尽可能使用复合索引进行整合
4. 监控 30 天以上后删除未使用的索引
5. 对于具有过滤查询的大表,考虑部分索引:

CREATE INDEX idx_orders_recent 
ON orders(created_at) 
WHERE created_at > '2024-01-01';

索引选择性和实际性能

索引选择性——唯一值与总行数的比率——决定有效性。布尔列(2 个值)上的索引选择性低,很少有帮助。email 上的索引(全部唯一)具有完美的选择性。

监控实际生产指标:

• 索引前后的查询执行时间
• 索引命中率
• 写入性能影响
• 存储增长

pgAdmin、MySQL Workbench 或 MongoDB Compass 等工具提供可视化查询分析。对于生产监控,可以考虑 DataDog 或 New Relic。

结论

有效的数据库索引需要理解你的查询模式,而不是遵循僵化的规则。从 WHERE、ORDER BY 和 JOIN 子句中的列上的 B-tree 索引开始。战略性地使用复合索引,考虑列顺序。对读取密集型查询利用覆盖索引,对 JSON 和文本数据使用专用索引。

最重要的是,衡量一切。使用 EXPLAIN 理解查询计划,监控索引使用统计,并跟踪对读写性能的实际影响。目标不是索引所有内容——而是精确索引应用程序所需的内容。

常见问题

Understand every bug

Uncover frustrations, understand bugs and fix slowdowns like never before with OpenReplay — the open-source session replay tool for developers. Self-host it in minutes, and have complete control over your customer data. Check our GitHub repo and join the thousands of developers in our community.