像专业人士一样加速数据库查询：PostgreSQL索引指南
如果你曾经为一个数据库查询等待了太长时间，你就会知道那种低效带来的痛苦。
好消息是！索引来拯救这一天了！索引在这里帮我们解决了问题！
PostgreSQL强大，但如果没有合适的索引，你的数据库运行起来就像是在土豆上一样。
索引就是让查询飞快的秘密武器，但是它们也带来了一些取舍。
什么是索引呢？PostgreSQL 中的索引就像书中的索引一样——而不是逐行扫描，可以直接跳到所需内容的相关部分。
没有索引的情况下，PostgreSQL会执行顺序扫描（seqscan），这意味着要查看每一行记录来找到匹配项。这对性能不是很理想。
索引特别有用，比如：
- 使用
WHERE子句加速查询。 - 提升连接效率。
有了索引，查询执行的速度不再是线性增长，而是对数增长。
想象从 O(n) 变为 O(log n) 复杂度。
但是， 索引并不是魔法。它有一些权衡。
它们会占用存储空间并可能拖慢写入速度。
所以，你得聪明地决定何时何地使用。
PostgreSQL的默认索引类型是B-tree索引（平衡树索引）。
将其想象成一种树形结构，用于保持数据有序，实现快速搜索。这里的工作原理如下：
- 根节点（起点）：所有搜索的起点。
- 分支节点：引导搜索到正确的叶子节点。
- 叶子节点：存储实际的数据地址。
比如说，如果你在一个表格中搜索“Mac”，B-树将会执行：
- 从根开始。
- 比较 “Mac” 与当前节点。
- 根据比较结果左右遍历。
- 重复上述步骤，直到找到完全匹配的项。
这个过程减少了比较次数，让搜索速度快了许多，达到了对数级别。
何时使用索引当你使用索引时，请确保它们明显提高读取速度。常见场景有：
| 场景 | 索引为何有帮助？ |
|---|---|
通过唯一字段（如 id，email）搜索 |
更快查找，避免全表扫描 |
使用 WHERE 子句筛选 |
快速找到匹配的行 |
排序（ORDER BY） |
提升排序效率 |
| 连接大表 | 避免扫描整个表 |
| 全文搜索 | 高效查找文本中的关键字 |
| 外键 | 确保快速查找到关系 |
索引是要收费的。
每次你添加、修改或移除数据时，都需要更新索引。
这可能会拖慢写入密集型的工作负载。如果不需要索引。
- 你的表比较小（PostgreSQL 仍然可以快速扫描）。
- 你的查询很少使用索引列进行过滤。
- 你的表有频繁的写入，而读取速度并不是很重要。
- 你的数据库事务性很强，你需要快速的插入和更新。
PostgreSQL的多版本并发控制（MVCC）机制可能导致“《仅堆元组》”（HOT）更新，从而产生死记录并增加I/O。这会增加I/O。
如图所示
在随便添加索引前，先试试它们是否确实有用。
PostgreSQL 提供 EXPLAIN ANALYZE 来分析查询的执行时间。试试看：
运行 `EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';` 这个SQL查询。这将解释并分析从 `users` 表中选择电子邮件为 `test@example.com` 的用户的所有信息的过程。进入全屏，退出全屏
看看 Seq Scan（差一点）和 Index Scan（好一些）的区别。
如果加索引没缩短查询时间，那就不太划算了。
想要删除一个多余的索引吗？可以使用：DROP INDEX index_name;
删除索引 index_name;全屏 关闭全屏
，此处应有图片描述。
并不是所有的索引都一样，PostgreSQL提供了几种类型的索引，
B-Tree 索引（默认）
默认索引（B-Tree）
大多数数据库都会有一些B树索引。
B树尽量保持平衡，每个分支的数据量大致相等。
因此，为了找到行而必须遍历的层级数始终大致相同。
B树索引结构非常适合用于等值匹配和范围查询。
它们可以针对所有数据类型操作，也可以用于检索 NULL 值。
B-树的设计非常适合缓存，即使只是部分缓存也能很好地工作。
- 适合: 等值和范围搜索 (
=,<,<=,>,>=)。 - 示例:
-- 创建 users 表的 email 列的索引
CREATE INDEX idx_users_email ON users(email);全屏模式 退出全屏
哈希索引（在 PostgreSQL 10+ 版本中得到改进的）
在 Postgres 10 版本之前，唯一索引（unique index）仅用于相等比较，但你几乎永远不会使用它们，因为它们不是事务安全的，在崩溃后需要手动重建，也不会复制到跟随者，因此相比使用 B-树索引，它的优势非常小。
在 Postgres 10 及以上版本中，哈希索引现在被写前日志记录，并复制到从节点。
-
优化了等值比较 (
=)， -
不适合范围查询 (
>和<)。 - 示例：
创建一个名为 idx_users_hash_email 的索引，用于根据 email 字段对 users 表进行哈希索引。
全屏模式下。退出全屏。
GIN (广义倒排索引)
当需要将多个值映射到一行时，GIN 很有用，相比之下，B-Tree 索引结构更适合每行只有一个键值的情况。
GIN不仅适合用来索引数组值，还可以用来实现全文搜索。
- 用于全文检索和JSONB。
- 例如:
创建一个名为idx_users_bio的索引，在users表的bio字段上使用gin索引方法来处理英语的全文搜索向量。
全屏模式 退出全屏
GiST (通用搜索树)
GiST 索引允许构建通用的平衡树，并可用于除等值和范围比较之外的其他操作。
它们用来索引几何数据，也可以用于全文搜索。
- 优化了几何查询和范围查询。
- 用于PostGIS（用于空间数据索引）。
- 例子：
创建一个名为 idx_locations 的索引，该索引基于 places 表中的 location 列，并使用 gist 方法。切换到全屏 退出全屏
BRIN (BRIN 索引)
对于大规模按顺序存储的数据（如时间序列数据）来说，特别高效。
- 占用的存储空间比B树少。
- 示例：
create index idx_logs_timestamp on logs using brin(timestamp);全屏退出全屏
一些高级索引技术组合索引
当这些列经常一起被查询时，可以创建复合索引。
创建一个名为 idx_orders_user_date 的索引，该索引基于 orders 表中的 user_id 和 order_date 字段。进入全屏；退出全屏
仅支持 B-tree、GiST、GIN 和 BRIN 索引类型能够创建多列键索引。
是否可以有多个键字段与是否可以在索引中添加 INCLUDE 列无关。
索引最多可以包含32列，其中包括可用作INCLUDE列的列。
部分索引（partial index）
只对部分数据进行索引以节省空间。
-- 创建一个名为idx_active_users的索引，在users表的email列上，筛选条件为is_active为true的用户。
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;全屏模式, 退出全屏
部分索引是一种在表的一个子集上建立的索引；这个子集是通过条件语句定义的。
这个索引只包括符合这些条件的表格记录。
覆盖索引（Covering Indexes）扫描
为了减少访问主表的次数，我们存储额外的列信息。
创建索引 idx_orders_covering 在 orders(user_id, order_date) 包含 (total_price);按一下进入全屏，再按一下退出
在 PostgreSQL 中，所有索引都是独立于主数据的辅助索引，这意味着索引数据与主数据分开存储。
独特索引
确保列唯一。
CREATE UNIQUE INDEX idx_unique_email ON users(email); -- 创建一个唯一索引，以确保每个用户的电子邮件地址都是唯一的切换到全屏，切换回正常模式
索引也可以用来确保某一列值的唯一性。此外，索引还可以确保多个列组合后的唯一性。
只有B树索引能被设定为唯一索引。
读取性能和写入性能之间的权衡| 操作 | 索引的影响 |
|---|---|
| 读取 | ✅ 更快的查询 |
| 写入 (INSERT/UPDATE/DELETE) | ❌ 更慢 |
| 存储 | ❌ 需要更多磁盘空间 |
| Vacuuming | ❌ 清理垃圾数据 |
如果你的应用程序主要是读操作，索引非常有用。但如果主要进行写操作，请谨慎使用索引。
收尾索引是 PostgreSQL 中 最重要的性能优化工具之一。请合理地使用它们。
- ✅ 使用索引 来进行过滤、排序和连接数据。
- ❌ 不要在经常更新的表上 添加索引。
- 🛠 在添加索引之前，先用 EXPLAIN ANALYZE 测试一下。
- 🎯 根据查询模式挑选合适的索引类型。
阅读更多 📚
我一直都在使用一个非常方便的工具，叫做LiveAPI。
LiveAPI 帮助你在几分钟内完成所有后端 API 的文档化
使用LiveAPI，您可以快速生成交互式API文档，并让用户可以直接在浏览器里试用API。
说明：这是一张示例图片，用于展示如何嵌入图片链接。
厌倦了手动为你的API创建文档？这个工具可能会让生活更轻松一些。
共同学习，写下你的评论
评论加载中...
作者其他优质文章
