了解 PostgreSQL 中的数据库索引_postgres indexof

开发人员分为三种类型：第一种是知道索引可以加快数据库查询速度，第二种是知道索引可以加快数据库查询速度并占用额外的空间和时间，第三种是对索引及不同的索引类型优点和缺点有更深刻了解的人。一般情况下，第一组和第二组人数最多。

如果您属于前种类型的人员，本文将帮助您理解 SQL 索引。如果您对索引了解很多，本文将帮助您整理知识并提醒您一些好的做法。 SQL 是一种声明性语言，这意味着它告诉数据库我们想要做什么，而不是如何实现它。数据库引擎决定如何提取数据。我们可以通过使用索引来帮助查询规划器。

我在本文中提供的所有信息都是特定于 PostgreSQL 数据库引擎的，但在某些时候，它们对其他流行的数据库引擎也有帮助。我所有的笔记都是基于 iRonin.IT 多年的实践经验——一家顶级软件开发公司，我们为各种技术提供定制软件开发和 IT 人员扩充服务。

1. 没有索引的生活

想象一下，您运行一个每天都有数百万用户使用的 Web 应用程序。该应用程序使用一个数据库，其中有关用户的信息收集在一个名为 users 的数据库中。每次有人请求查询用户的个人资料时，必须先在数据库中找到用户记录，然后再将信息呈现给访问者。一个普通的场景：

SELECT * FROM users WHERE users.slug = 'user411' LIMIT 1;
1

上面的查询是针对具有一百万条记录的表执行的，其中每个 slug 都是唯一的，但没有在其上创建任何索引。获得一条记录耗时 57.419 毫秒。让我们在 slug 列上添加一个唯一索引：

CREATE UNIQUE INDEX slug_idx ON users (slug);
1

再次运行 select 查询，用了 0.055 毫秒就得到匹配的记录。这比以前快了 1043 倍！想象一下，在列上没有适当索引的情况下，对更大的数据集使用更复杂的查询。现在，当我们分析了简单但有意义的示例后，可以深入了解索引的世界，以了解如何以正确的方式设计数据库。

2. 真正理解什么是索引

我在开头提到，大多数开发人员都知道索引可以加快对数据库的查询，但是如果你让他们详细解释索引，他们也不知道答案。

我将索引的定义分为两部分：高级定义和低级定义。第一个用来正确理解索引的一般工作方式，第二个是为那些想知道事情在幕后如何工作的人准备的。

2.1 高级定义

想象一下，您的 iPhone 上有一个联系人列表，但这个列表没有按字母顺序排序。你想打电话给John 。你会怎么办？您将从顶部开始浏览每个联系人，然后在名为 John 的联系人处停止。假设您的列表包含 100 个联系人，如果您足够幸运，您需要筛选 20 - 30 个联系人才能找到合适的联系人。如果 John 是最后一个联系人怎么办？找到联系人会花费很多时间。

这就是索引解决的问题。如果没有列上的索引，当进行过滤时，数据库将进行全扫描，这仅仅意味着它会遍历每条记录，除非找到匹配的记录。这可能需要很长时间，具体取决于数据库大小。

当将索引放在列上时，在我们的例子中是联系人的姓名，数据库会创建一个特殊的结构来对联系人进行排序。因此，如果您正在寻找 John，您会寻找以字母 J 开头的联系人姓名；它加快了搜索过程好几倍。

2.2 低级定义

有几种类型的索引；接下来我将讨论它们。我们着重关注默认的 B 树，这样我就可以演示它在数据库内部的低级别上是什么样子的。

B-tree代表平衡搜索树。它通过允许具有两个以上子节点的节点来简化二叉搜索树。每个节点包含按升序排列的键。如果考虑带有联系人姓名的示例，则 B 树结构看起来类似于以下可视化：

如果你将所有这些名字组成一个数组并按字母顺序对它们进行排序，John 将位于数组的中间。如果在索引上执行搜索，执行引擎会检查 给定值 是在根节点之前还是之后，并对每个下一个节点重复检查。

节点还包含指向数据库中记录的指针，因此当找到匹配节点时，引擎将使用该指针从数据库中获取记录并将其合并到搜索结果中。

3. 不同类型的索引和用例

有多种类型的索引，每一种都适用于不同的使用场景。您已经了解了 B 树索引，它是默认索引，除非您指定另一个索引类型，否则它将被应用。以下是完整列表，以及每种索引类型的使用场景：

• B-tree——默认索引，适用于各种数据。当使用以下运算符时，查询计划器会考虑使用 B 树索引： < 、 <= 、 = 、 >= 、 >
  、 BETWEEN 、 IN 、 IS NULL 或 IS NOT NULL 。此外，查询规划器还可以考虑将此索引用于包含
  LIKE 和 ~ 的模式匹配的查询。然而，模式必须是一个常量，锚点必须在模式的起点。
• Hash - 只能处理 = 运算符的简单相等比较，并且只有在使用这样的运算符时，查询计划器才会考虑使用哈希索引。在 Postgres 版本 10 中，你不应该使用这个索引，因为它不是事务安全的并且有一些其他的缺点。此外，与 b-tree
  相比的优势很小，因此在大多数情况下，b-tree 是更好的选择。
• GiST -
  代表通用搜索树的快捷方式。这种类型的索引适用于几何数据类型、网络地址数据和全文搜索。当使用以下运算符时，查询规划器会考虑使用它： << 、
  &< 、 &> 、 >> 、 <<| 、 &<| 、 |&> 、 |>> 、 @> 、 <@ 、 ~= 或 &&
  。这种类型的索引也适用于优化查找最近邻居的查询。
• SP-GiST - 代表空间分区广义搜索树的快捷方式。这种类型的索引适用于多媒体、电话路由、IP 路由或
  GIS。当使用以下运算符之一时，查询规划器会考虑使用此索引： << 、 >> 、 ~= 、 <@ 、 <<| 或 |>> 。
• GIN - 代表广义倒排索引的快捷方式。 B-tree 索引针对行只有一个键值的情况进行了优化，而
  GIN
  更适合索引必须将许多值映射到一行的情况。想索引数组、hstore、jsonb、实现全文搜索的时候想想GIN。当使用以下运算符时，查询规划器会考虑使用此索引：
  <@ 、 @> 、 = 或 && 。
• BRIN - 代表块范围索引的快捷方式。它通常用于具有线性排序顺序的列，例如，用于报告的记录的创建日期。比 B
  树索引小得多，维护成本更低。当使用以下运算符时，查询规划器会考虑使用此索引： < 、 <= 、 = 、 >= 或 > 。 BRIN
  索引一般更适合时间序列数据的大表而不是随机排序数据的小表。

事实上，在大多数情况下，您应该可以使用默认创建的 b 树索引，除非您要处理数据库中非常具体的信息。

4. 使用索引提高查询性能

现在，很清楚索引在一般情况下和底层是如何工作的，就更容易理解我们如何使用索引更新查询以使其更快。

4.1 检测何时添加索引

知道为什么以及如何。是时候找到答案了。将索引放在错误的列上不会使您受益；数据库只会占用更多磁盘空间。一般来说，以下做法被认为是好的：

• 在查找的列上创建索引 - 在明白索引的工作原理后，这对您来说应该是显而易见的。如果您按 last_name
  列过滤记录，请将索引放在那里。可以使用过滤来缩小搜索结果范围。
• 索引用于数据库级验证的列——仅在后端或前端（或两者）进行验证是不够的。使用索引来验证数据的存在性和完整性被认为是一种很好的做法。
• 用于连接操作的索引列 -
  在用于连接表的列上放置索引可以提高性能。
• 经常用于排序的索引列 -
  索引以一种使排序更有效的方式组织数据，从而减少对昂贵的排序操作的需要。

可能还有一些特殊的数据用例，但上述规则适用于大多数应用程序。每次设计数据库、执行遗留数据库审计或寻找提高查询性能的方法时，请考虑它们。

4.1.1 使用explain 和analyze命令

如果不提及帮助我们确定查询规划器如何查看数据的 explain 和 analyze 命令，本节将是不完整的。

EXPLAIN 命令打印查询的执行计划而不执行它。该计划包含操作顺序、连接方法、索引使用情况和估计成本：

EXPLAIN SELECT title FROM articles WHERE published = true;
1

ANALYZE 命令收集有关表或索引中数据的统计信息。与 EXPLAIN 命令相反，它提供了实际的运行时性能指标：

ANALYZE SELECT title FROM articles WHERE published = true;
1

您还可以结合使用这两个命令来全面了解 PostgreSQL 中的查询优化和微调：

EXPLAIN ANALYZE SELECT title FROM articles WHERE published = true;
1

4.2 基本数据类型的单列或多列索引

在过滤方面，您可以根据单列或多列过滤记录。如果你打算过滤基本数据类型，默认索引（b-tree）就足够了：

CREATE INDEX title_idx ON articles (title);
1

如果你打算在多列上添加索引，只需修改命令的最后一部分：

CREATE INDEX title_category_idx ON articles (title, category);
1

4.3 *部分索引(Partial indexes)

当您创建索引时，默认情况下，您会索引给定表中的全部记录。如果你想避免它，你可以创建一个部分索引。构建查询时，使用 WHERE 关键字来缩小搜索结果范围，创建索引也是如此：

CREATE INDEX title_idx ON articles (title) WHERE published = true;
1

这样，您将通过仅索引所有记录的子集来减少索引大小。

4.4 *表达式索引(Expression indexes)

当简单的值不能满足要求时，您可以根据函数的结果创建索引。一个流行的例子是索引给定字段中值的小写版本：

CREATE INDEX title_idx ON articles (lower(title));
1

请记住，这样的索引可能会占用更多空间（取决于结果）并且可能会影响数据更新的性能。

4.5 *覆盖索引(Covering indexes)

您可能已经知道，扫描索引后，再用指针从表中访问数据。您可以跳过第二部分并通过使用覆盖索引进一步改进查询。

覆盖索引也称为仅索引扫描。它包含满足查询所需的所有列，因此无需执行表访问。创建此类索引的语法如下：

CREATE INDEX idx_covering ON articles (title, category) INCLUDE (author, slug);
1

与往常一样，这样的索引给我们带来了很多好处，但也必须知道，如果**对包含的列进行频繁更新，那么使用这样的索引并不是一个好主意，因为它需要同时更新 索引和原表 **。

4.6 其他类型的索引

除了提到的简单查询之外，您还可以提高更高级的查询的速度。其中包括全文检索和地理空间数据。每当您创建索引时，您都可以参考官方文档，其中详细描述了索引创建命令以及所有可能的参数。

5.维护已存在的索引

仅仅向数据库添加正确的索引是不够的；您还需要维护现有索引以确保数据库结构处于最佳状态。当你从底层启动应用程序时，你没有太多需要维护的索引，但在遗留应用程序中，有很多情况需要处理。

您需要维护索引，因为过去可能会发生以下情况之一：

• 开发人员索引了比需要 更多的列
• 开发者索引了一个列，还没有用到，以后也用不到
• 查询计划不使用索引而是进行全扫描；它可能发生在小表上

这些只是可以创建无效索引的许多情况中的一部分，我们需要维护它们以保持我们的数据库健康和尽可能高性能。

5.1 存储索引 元信息 的表

在 PostgreSQL 中，索引的信息存储在 pg_index 表中。由于它是一个表，您可以像查询数据库中的任何其他表一样查询它。我调查了表列（我使用的是 11.19 版），该表包含以下列：

• indnatts - the total number of columns in the index
  【索引中的总列数】
• indnkeyatts - the total number of key columns in the index 【索引中 键列的总数】
• indisunique - determines if index is unique 【确定索引是否唯一】
• indisprimary - determines if index represents primary key of the
  table 【判断索引是否表示表的主键】
• indisexclusion - determines if the index supports exclusion
  constraint 【判断索引是否支持排除约束】
• indisclustered - determines if table was last clustered on this index
  【确定表是否最后聚集在这个索引上】
• indisvalid - determines if index is valid for queries
  【确定索引是否对查询有效】
• indisready - determines if index is ready for inserts
  【确定索引是否准备好插入】
• indislive - determines if index is in process of being dropped 【确定索引是否正在被删除】
• indisreplident - determines if index has been chosen as replica
  identity 【 确定索引是否已被选为副本标识】
• indkey - an array of indnatts values to indicate which table columns
  the index indexes 【indnatts 值的数组，用于指示索引索引哪些表列】

其他列是 indexrelid 、 indrelid 、 indimmediate 、 indcheckxmin 、 indcollation 、 indclass 、 indoption 、 indexprs 、 indpred 。可以查看官方文档中每一列的详细解释。

5.2 检测未使用的索引

要检测数据库中未使用的索引，我们可以使用包含使用情况统计信息的 pg_stat_user_indexes 表。该表包含以下重要列：

• idx_scan - 对索引执行的扫描次数
• relname - 表名
• indexrelname - 索引名称
• indexrelid - 帮助我们在
  pg_index 表中找到对应记录的关系的标识

您必须记住的是，不能简单地找到 idx_scan 等于零的记录。我们还必须记住以下几点：

• 有时索引并不是简单索引，它是表中一个或多个列的表达式或部分索引。在这种情况下，我们不想在查找未使用索引的查询中考虑它。考虑到这种情况，我们可以使用
  pg_index 表中的 indkey 列——当该列包含0时，表示该索引是一个表达式。
• 对于唯一索引， idx_scan 将包含
  0，因为此类索引的行为与非唯一索引不同。唯一索引用于强制执行唯一约束，并且不执行全扫描。要查找唯一索引，我们可以利用 pg_index
  表中的 indisunique 列。
• 除了唯一性，我们还有其他约束。在搜索未使用的索引时，我们也无法考虑这些。为了改进我们的流程，我们可以查看包含所有约束的
  pg_constraint 表。如果我们的索引不包括在那里，我们可以考虑它。

考虑到以上所有几点，我们最终可以生成一个 SQL 查询，显示哪些索引未被使用：

SELECT s.relname, s.indexrelname, pg_relation_size(s.indexrelid) AS index_size FROM pg_stat_user_indexes s JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid WHERE s.idx_scan = 0 AND 0 <>ALL (i.indkey) AND NOT i.indisunique AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conindid = s.indexrelid) ORDER BY pg_relation_size(s.indexrelid) DESC;
1

上述查询将产生一组结果，其中每个结果包含三个属性：

• relname - 表名
• indexrelname - 索引的名称
• index_size - 索引的大小

5.3 检测膨胀(Detecting bloats)

频繁的更新和删除操作会导致磁盘上的表或索引关系文件有大量未使用的空间；它被称为膨胀。这种情况会导致性能下降。

5.3.1 膨胀是如何产生的

虽然很多开发人员可能会感到惊讶，但当记录被删除时，它并没有从磁盘上物理删除。 PostgreSQL 使其不可见并标记为删除。更新记录时也会出现同样的情况。在数据库中创建了一个新版本，旧版本被标记为不可见。

在 VACUUM 操作期间，PostgreSQL 扫描表中的死行并将它们从表中删除；只是这种情况会物理删除数据并释放空间。但是，默认情况下不启用自动 VACUUM 操作。

当自动 VACUUM 未启用或启用但运行频率不足以跟上数据库上的工作负载时，就会产生膨胀。您可以通过运行以下查询来检查是否启用了此功能：

SELECT name, setting, unit FROM pg_settings WHERE name = 'autovacuum';
1

5.3.2 找到膨胀的大小

数据库本身提供了一个EXTENSION ，提供有关我们数据库中元组的信息。例如，您可以查询 tables 表，并为每个表触发 pgstattuple 函数并使用以下查询读取死元组的百分比：

SELECT table_name, (pgstattuple(table_name)).dead_tuple_percent AS dead_tuple_percent FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' ORDER BY dead_tuple_percent DESC;
1

如果您收到有关未知扩展的错误消息，则必须先启用它：

CREATE EXTENSION pgstattuple;
1

它现在应该工作了。由于本文是关于索引的，所以让我们看看如何找到索引中的膨胀大小。我们可以使用以下查询：

SELECT cls.relname, am.amname, pg_total_relation_size(indexrelid) AS index_size_bytes, pg_total_relation_size(indexrelid) - pg_relation_size(indexrelid) AS index_bloat_bytes FROM pg_index idx JOIN pg_class cls ON cls.oid=idx.indexrelid JOIN pg_am am ON am.oid=cls.relam WHERE indrelid > 16384;
1

此查询返回索引的名称、索引类型和估计的膨胀大小。

5.3.3 从索引中删除膨胀

要从给定索引中删除膨胀，您可以重建索引：

REINDEX INDEX index_name;
1

上面的命令会将数据从旧索引复制到新索引。请记住，此操作可能需要一些时间并影响数据库性能。

5.4 检测重复索引

有些情况下存在两个或多个索引；它们具有不同的名称，但列的组合相同。结果，其中一个（甚至更多）未被使用，只是占用空间并使写入过程更加昂贵。

要查找重复项，我们可以使用以下查询查询 pg_index 表：

SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size, (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2 FROM ( SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key FROM pg_index) sub GROUP BY key HAVING count(*) > 1 ORDER BY sum(pg_relation_size(idx)) DESC;
1

为了更好地理解上面发生的事情，让我们将查询分解成更小的部分。我们有以下子查询：

SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key FROM pg_index
1

• indexrelid::regclass as idx -
  获取索引的名称。如果没有转换，我们只会得到 OID，它是索引的对象标识符。
• indrelid::text ||E’\n’|| indclass::text ||E’\n’|| indkey::text
  ||E’\n’|| coalesce(indexprs::text,‘’)||E’\n’ ||
  coalesce(indpred::text,‘’) - ||
  字符是连接运算符，查询的这一部分根据有关索引的一些详细信息格式化单个字符串

我们还有主要查询：

SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size, key, (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2 FROM ( ... ) sub GROUP BY key HAVING count(*) > 1 ORDER BY sum(pg_relation_size(idx)) DESC;
1

它过滤子查询以获取多次出现的键，获取索引的大小，并提供重复索引的名称。上面的示例只找到两个索引，但如果您怀疑它们可能有更多，只需添加 (array_agg(idx))[n] as idxn 即可获得更多索引。

6. 用constraint验证数据

我敢打赌您经常听说过constraint这个词，但它到底是什么？它是描述表中数据有效状态的条件（或规则）。约束与索引相关，因为每次创建约束时，也会创建一个索引（外键约束除外）。

您可以使用以下类型的constraint：

• Primary key -
  确保一列（或列的组合）唯一标识给定表中的每一行的约束。
• Unique - 确保每个给定表的列（或列组合）唯一的约束。主键和唯一约束的区别是主键不允许空值。
• Not null - 确保列不包含空值的约束。在后端，您可以将其称为表单中给定字段的存在验证。
• Foreign key - 一种确保关系存在的约束，有助于避免在第二个表中删除关联记录并在基表中留下“死”引用的情况。
• Check - 确保每一行都将给定表达式计算为 true 的约束。例如，如果您有一个 salary 列，您可以定义 salary > 10000
  表达式，并且表中的每一行都必须将其计算为真，否则将不被视为有效。
• Exclusion - 确保两个记录在给定列上没有重叠值的约束。最常见的情况是我们不希望
  start_date 和 end_date 列与另一条记录重叠的事件表。

拥有适当的constraints 可以帮助您以正确的格式保存数据，还可以提高性能，因为您可以避免在数据库中包含无效信息，从而减少要处理、扫描或查询的数据。

7. 最佳实践

最后但同样重要的是，了解有关索引的良好实践总是好的。不仅仅是因为技术面试！记住更一般的规则并在每次需要时更深入，而不是试图记住所有可用的信息，这样更容易。

尽量记住以下最佳实践，以尽可能高效地使用数据库：

• 不要索引所有列 - 索引会增加写入时间并占用空间，因此请查找您过滤的列或需要验证的列。
• 如果表中的数据很少，不要索引列 - 如果表中的数据很少，PostgreSQL 不会使用索引。在这种情况下，查询规划器更愿意使用完整扫描。
• 选择合适的索引类型——b
  树索引（默认索引）适用于许多情况，但并非适用于所有情况。当您想要使用全文搜索、地理空间数据或更复杂的数据类型时，默认索引将不是正确的选择。
• 维护你的索引——随着时间的推移，一些索引可能不再需要，或者数据需要重新索引。请记住定期照看您的索引。
• 基准测试和测试——在部署之前始终测试类似生产数据的索引，以确保您将从更改中受益。
• 使用索引——查询规划器做得很好，但你可以通过使用索引来帮助他；然后它将更有效地提取您的数据。

当您养成索引列的习惯时，您将随着时间的推移生成自己的良好实践列表。此外，了解索引在幕后的工作方式将帮助您就数据库架构做出更自信的决策。

原文地址