像专业人士一样加速数据库查询

如果您曾经等待数据库查询完成很长时间，您就会知道性能缓慢的痛苦。

好消息？指数来拯救你了！

PostgreSQL 功能强大，但如果没有适当的索引，您的数据库就会感觉像是在土豆上运行。

索引是实现极快查询的秘诀，但也有其弊端。

什么是索引？

PostgreSQL 中的索引就像书中的索引一样 - 您无需扫描每一页（行），而是直接跳转到相关部分。

如果没有索引，PostgreSQL 会执行顺序扫描，这意味着需要逐行查找匹配项。性能并不理想。

索引对于以下情况特别有用：

使用子句加速查询WHERE。
提高连接性能。

有了索引，查询执行速度就会呈对数增长，而不是线性增长。

想象一下从O(n) → O(log n)复杂度。

但是……索引并不是魔法。它需要权衡利弊。

它们会消耗存储空间并降低写入速度。

因此，您需要明智地选择何时何地使用它们。

索引如何工作：B 树的魔力

PostgreSQL 的默认索引类型是B 树（平衡树）。

可以将其视为一个树状结构，用于对数据进行排序，以便快速搜索。它的工作原理如下：

根节点：所有搜索的起点。
分支节点：引导搜索到正确的叶节点。
叶节点：存储实际数据指针。

例如，如果您在表中搜索名称“Mac”，B 树将：

从根部开始。
将“Mac”与当前节点进行比较。
根据比较结果向左或向右遍历。
重复此操作直到找到完全匹配。

此过程减少了所需的比较次数，使搜索速度呈对数级加快。

何时使用索引

当索引能显著提高读取性能时，请使用索引。常见场景包括：

设想	为什么索引有帮助？
按唯一字段搜索（例如，`id`，`email`）	查找速度更快，避免全表扫描
使用`WHERE`子句进行过滤	快速找到匹配的行
排序（`ORDER BY`）	提高排序性能
连接大型表	避免扫描整个数据集
全文搜索	有效地在文本中查找关键词
外键	确保快速检查关系

何时不使用索引

索引不是免费的。

每次插入、更新或删除数据时，索引都需要更新。

这会降低写入密集型工作负载的速度。在以下情况下，请避免使用索引：

您的表很小（无论如何，PostgreSQL 都可以快速扫描它）。
您的查询很少按索引列进行过滤。
您的表有频繁的写入，并且读取速度并不重要。
您的数据库是高度事务性的，并且您需要快速插入/更新。

PostgreSQL 的 MVCC 机制可能导致“仅堆元组”（HOT）更新，从而产生死行并增加 I/O。

衡量指数表现

在盲目添加索引之前，请测试它们是否真的有帮助。

PostgreSQL 提供了EXPLAIN ANALYZE来分析查询执行时间。试试这个：

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

寻找Seq Scan（坏）与Index Scan（好）。

如果添加索引不能减少查询时间，那就不值得。

想要删除无用的索引吗？使用：

DROP INDEX index_name;

PostgreSQL 中的索引类型

并非所有索引都是一样的。PostgreSQL 提供了几种类型的索引：

B树索引（默认）

几乎所有数据库都会有一些 B 树索引。

B 树试图保持平衡，树的每个分支中的数据量大致相同。

因此，找到行所必须遍历的级别数总是在同一个范围内。

B 树索引可有效地用于相等性和范围查询。

它们可以对所有数据类型进行操作，也可以用于检索 NULL 值。

B 树的设计使其能够很好地与缓存配合，即使只是部分缓存。

适用于：平等和范围查询（=，，，，，）<。<=>>=
例子：

  CREATE INDEX idx_users_email ON users(email);

哈希索引（PostgreSQL 10+ 中改进）

Postgres 10 之前的版本仅对相等性比较有用，但您几乎永远不想使用它们，因为它们不是事务安全的，需要在崩溃后手动重建，并且不会复制到关注者，因此与使用 B 树相比的优势相当小。

在 Postgres 10 及更高版本中，哈希索引现在是预写记录并复制到关注者。

针对相等性比较进行了优化（=）。
对于范围查询来说并不理想>（，<）。
例子：

  CREATE INDEX idx_users_hash_email ON users USING hash(email);

GIN（广义倒排索引）

当索引必须将多个值映射到一行时，GIN 很有用，而 B 树索引针对一行具有单个键值的情况进行了优化。

GIN 适用于索引数组值以及实现全文搜索。

用于全文搜索和JSONB 字段。
例子：

  CREATE INDEX idx_users_bio ON users USING gin(to_tsvector('english', bio));

GiST（广义搜索树）

GiST 索引允许您构建通用的平衡树结构，并可用于超出相等和范围比较的操作。

它们用于索引几何数据类型以及全文搜索。

针对几何和范围查询进行了优化。
用于PostGIS（空间数据索引）。
例子：

  CREATE INDEX idx_locations ON places USING gist(location);

BRIN（区块范围索引）

对于大型、顺序存储的数据（如时间序列数据）来说非常高效。

比 B 树占用更少的存储空间。
例子：

  CREATE INDEX idx_logs_timestamp ON logs USING brin(timestamp);

高级索引策略

综合指数

当多个列通常一起被查询时，对它们进行索引。

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

只有 B 树、GiST、GIN 和 BRIN 索引类型支持多键列索引。

是否可以有多个关键列与是否可以将 INCLUDE 列添加到索引中无关。

索引最多可以有 32 列，包括 INCLUDE 列。

部分索引

仅索引数据子集以节省空间。

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

部分索引是在表的子集上构建的索引；子集由条件表达式定义。

索引仅包含满足条件的表行的条目。

覆盖索引

存储额外的列以避免访问主表。

CREATE INDEX idx_orders_covering ON orders(user_id, order_date) INCLUDE (total_price);

PostgreSQL 中的所有索引都是二级索引，这意味着每个索引都与表的主数据区分开存储。

唯一索引

有效保证列的唯一性。

CREATE UNIQUE INDEX idx_unique_email ON users(email);

索引还可用于强制列值的唯一性，或多个列的组合值的唯一性。

只有 B 树索引才能被声明为唯一的。

权衡：读取与写入性能

行动	指数的影响
读	✅ 更快的查询
写入（插入/更新/删除）	❌由于索引维护而变慢
贮存	❌ 需要更多磁盘空间
吸尘	❌ 死元组需要清理

如果你的应用是读操作密集型的，那么索引是理所当然的。但如果是写操作密集型的，那么就需要有所选择。

总结

索引是PostgreSQL 中最大的性能提升因素之一。请明智地使用它们：

✅使用索引进行过滤、排序和连接。
❌避免在经常更新的表上建立索引。
🛠添加索引之前使用 EXPLAIN ANALYZE 进行测试。
🎯为您的查询模式选择正确的索引类型。

进一步阅读📚

我一直在研究一种超级方便的工具，叫做LiveAPI。

LiveAPI可帮助您在几分钟内记录所有后端 API

使用 LiveAPI，您可以快速生成交互式 API 文档，允许用户直接从浏览器执行 API。

如果您厌倦了手动为 API 创建文档，这个工具可能会让您的生活更轻松。

文章来源：https://dev.to/lovestaco/speed-up-db-queries-like-a-pro-2d95