使用 Postgres CREATE INDEX：理解操作类、索引类型等

飞象数据 2022-10-15

883

大多数使用数据库的开发人员都知道面临的挑战：新代码被部署到生产环境中，应用程序突然变得很慢。我们调查，查看我们的 APM 工具和数据库监控，我们发现新代码导致发出新查询。我们进一步调查，发现查询无法使用索引。

但是是什么让索引可以被查询使用，我们如何在 Postgres 中添加正确的索引呢？

在这篇文章中，我们将了解使用该命令的实际方面CREATE INDEX
，以及如何分析 PostgreSQL 查询的运算符和数据类型，以便您选择最佳索引定义。

如何在 Postgres 中创建索引？

在深入了解内部结构之前，让我们先来看看在 Postgres 中创建索引的最基本方法。添加索引的本质是这样的：

CREATE INDEX ON [table] ([column1]);

举一个实际的例子，假设我们在 users 表上有一个查询，用于查找特定的电子邮件地址：

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

我们可以看到这个查询正在“email”列中搜索值——所以我们应该创建的索引是在那个特定的列上：

CREATE INDEX ON users (email);

当我们运行这个命令时，Postgres 会为我们创建一个索引。

重要的是要记住索引是冗余数据结构。如果删除索引，您不会丢失任何数据。索引的主要好处是允许更快地搜索表中的特定行。拥有索引的替代方法是让 Postgres 单独扫描每一行（“顺序扫描”），这对于大型表来说当然非常慢。

下面我们来看看 Postgres 是如何判断是否使用索引的幕后花絮。

解析分析：Postgres 如何解释您的查询

当 Postgres 运行我们的查询时，它会逐步执行多个阶段。在高层次上，它们是：

解析（请参阅我们关于 Postgres 解析器的博文）
https://pganalyze.com/blog/pg-query-2-0-postgres-query-parser#how-pg_query-turns-a-postgres-statement-into-a-parse-tree
解析分析
规划
执行

在这些阶段中，查询不再只是文本——它被表示为一棵树。每个阶段都会修改和注释树结构，直到最终执行。要了解 Postgres 索引的使用，我们首先需要了解parse 分析的作用。

让我们选择一个稍微复杂一点的例子：

SELECT * FROM users WHERE users.email = 'test@example.com' AND users.deleted_at IS NULL;

我们可以通过打开debug_print_parse
设置来查看解析分析的结果，然后查看 Postgres 日志（不推荐在生产数据库上使用）：

LOG:  parse tree:
DETAIL:     {QUERY 
     ...
        :quals 
           {BOOLEXPR 
           :boolop and 
           :args (
              {OPEXPR 
              :opno 98 
              :opfuncid 67 
              :opresulttype 16 
              :opretset false 
              :opcollid 0 
              :inputcollid 100 
              :args (
                 ...
              )
              :location 38
              }
              {NULLTEST 
              :arg 
                 ...
              :nulltesttype 0 
              :argisrow false 
              :location 80
              }
...

这种格式有点难以阅读 - 让我们以更直观的方式来看待它，并使用名称而不是 OID：

我们可以在这里看到两个重要的解析节点，一个用于WHERE
子句中的每个表达式。节点，OpExpr
和NullTest
节点。现在，让我们关注OpExpr
节点。

幕后观察：运算符和数据类型

重要的是要记住 Postgres 是一个对象关系数据库系统。也就是说，它从头开始设计为可扩展的。在解析分析中添加的许多引用不是硬编码逻辑，而是引用 Postgres 目录表中的实际数据库对象。

要了解的两个最重要的对象是数据类型和运算符。您很可能熟悉 Postgres 中的数据类型，例如，您在为表指定模式时使用过它们。Postgres 中的运算符定义了如何实现一个或两个值之间的特定比较，例如在 WHERE 子句中。

该OpExpr
节点表示一个表达式，该表达式使用运算符来比较给定类型的一个或两个值。在这种情况下，您可以看到我们正在使用=(text, text)
运算符。该运算符使用=
符号作为其名称，并text
在运算符的左侧和右侧具有数据类型。

我们可以查询该pg_operator
表以查看有关它的详细信息，包括哪个函数实现了该运算符：

SELECT oid, oid::regoperator, oprcode, oprnegate::regoperator
  FROM pg_operator
 WHERE oprname = '=' AND oprleft = 'text'::regtype AND oprright = 'text'::regtype;
 oid |     oid      | oprcode |   oprnegate   
-----+--------------+---------+---------------
  98 | =(text,text) | texteq  | <>(text,text)
(1 row)

如果你真的想知道发生了什么，你可以在Postgres 源码中查找操作符的底层texteq
函数：

https://github.com/postgres/postgres/blob/REL_13_STABLE/src/backend/utils/adt/varlena.c#L1745

/*
 * Comparison functions for text strings.
 */
Datum
texteq(PG_FUNCTION_ARGS)
{
    ...
    if (lc_collate_is_c(collid) ||
    collid == DEFAULT_COLLATION_OID ||
    pg_newlocale_from_collation(collid)->deterministic)
    {
        ...
      result = (memcmp(VARDATA_ANY(targ1), VARDATA_ANY(targ2),
          len1 - VARHDRSZ) == 0);
        ...
    }
    else
  {
        ...
        result = (text_cmp(arg1, arg2, collid) == 0);
        ...
    }
    ...
}

该函数很好地说明了 Postgres 如何考虑排序规则来确定它是否可以进行简单地比较字节的快速比较，或者是否必须进行更昂贵的全文比较。正如我们从源代码中看到的那样，使用 C 语言环境进行排序可以产生性能优势。

当然，您也可以定义自己的自定义运算符来处理您自己的自定义数据类型。Postgres 就是这样可扩展的，这实际上非常简洁。

运算符对于创建正确的索引至关重要。表达式使用的运算符是除了列名之外最重要的细节，它指示是否可以使用特定索引。

您可以将运算符视为我们要在表中搜索值的“方式”。例如，我们可以使用一个简单的=
运算符来匹配输入值的相等值。或者我们可以使用更复杂的运算符，例如@@
在 tsvector 列上执行文本搜索。

找到正确的索引类型

当您想到索引类型时，重要的是要记住它最终是一种特定的数据结构，支持特定的、有限的一组搜索运算符。例如，Postgres 中最常见的索引类型 B 树索引支持=
运算符以及范围比较运算符（<
、<=
、=>
、>
），在某些情况下还支持~
and运算符。~*
它不支持任何其他运算符。

假设我们的表上有一个tsvector
列users
，我们使用@@
运算符搜索该列：

SELECT * FROM users WHERE about_text_search @@ to_tsquery('index');

即使我创建了一个索引，它也会继续进行顺序扫描：

CREATE INDEX ON users(about_text_search);
pgaweb=# EXPLAIN SELECT * FROM users WHERE about_text_search @@ to_tsquery('index');
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Seq Scan on users  (cost=10000000000.00..10000000006.51 rows=1 width=4463)
   Filter: (about_text_search @@ to_tsquery('index'::text))
(2 rows)

这是因为 B 树索引没有正确的数据结构来支持文本搜索。没有匹配 B-Tree 索引和@@(tsvector,tsquery)
运算符的运算符类。

和之前一样，由于 Postgres 的可扩展性，我们可以自省系统来理解操作符类。哪种索引类型可以支持@@
tsvector 列上的运算符？

我们可以查询内部表来回答这个问题：

SELECT am.amname AS index_method,
       opf.opfname AS opfamily_name,
       amop.amopopr::regoperator AS opfamily_operator
  FROM pg_am am,
       pg_opfamily opf,
       pg_amop amop
 WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid
       AND amop.amopopr = '@@(tsvector,tsquery)'::regoperator;
 index_method | opfamily_name |  opfamily_operator   
--------------+---------------+----------------------
 gist         | tsvector_ops  | @@(tsvector,tsquery)
 gin          | tsvector_ops  | @@(tsvector,tsquery)
(2 rows)

看起来我们需要 GIN 或 GIST 索引！我们可以像这样创建一个 GIN 索引：

CREATE INDEX ON users USING gin (about_text_search);

瞧，它可以被查询使用：

=# EXPLAIN SELECT * FROM users WHERE about_text_search @@ to_tsquery('index');
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=8.25..12.51 rows=1 width=4463)
   Recheck Cond: (about_text_search @@ to_tsquery('index'::text))
   ->  Bitmap Index Scan on users_about_text_search_idx1  (cost=0.00..8.25 rows=1 width=0)
         Index Cond: (about_text_search @@ to_tsquery('index'::text))
(4 rows)

tsvector_ops
我们在内部 Postgres 表中看到的那个名字是什么？

这就是使用运算符族和运算符类将索引类型链接到运算符的方式。对于给定的运算符，可以有多个不同的运算符类 - 运算符类定义如何为特定索引类型表示数据，以及该索引的搜索操作如何实现查询中使用的运算符。

在 CREATE INDEX 期间指定运算符类

例如，让我们看一下=(text,text)
，它是前面查询中使用的运算符：

SELECT am.amname AS index_method,
       opf.opfname AS opfamily_name,
       amop.amopopr::regoperator AS opfamily_operator
  FROM pg_am am,
       pg_opfamily opf,
       pg_amop amop
 WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid
       AND amop.amopopr = '=(text,text)'::regoperator;
 index_method |  opfamily_name   | opfamily_operator 
--------------+------------------+-------------------
 btree        | text_ops         | =(text,text)
 hash         | text_ops         | =(text,text)
 btree        | text_pattern_ops | =(text,text)
 hash         | text_pattern_ops | =(text,text)
 spgist       | text_ops         | =(text,text)
 brin         | text_minmax_ops  | =(text,text)
 gist         | gist_text_ops    | =(text,text)
(7 rows)

您可以看到有一个默认操作符类 ( text_ops
)，当您没有明确指定它时会使用它 - 对于文本列，默认操作符类通常就是您所需要的。

但是在某些情况下，我们想要设置一个特定的操作符类。例如，假设我们在数据库上运行 LIKE 查询，而我们的数据库恰好使用 en_US.UTF-8 排序规则 - 在这种情况下，您将看到 LIKE 查询实际上无法使用索引：

CREATE INDEX ON users (email);
pgaweb=# EXPLAIN SELECT * FROM users WHERE email LIKE 'lukas@%';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Seq Scan on users  (cost=10000000000.00..10000000001.26 rows=1 width=4463)
   Filter: ((email)::text ~~ 'lukas@%'::text)
(2 rows)

通常，LIKE 查询很难建立索引，但如果您没有前导通配符，则可以创建一个适用于它们的索引 - 但您需要 (1) 在数据库上使用 C 语言环境（实际上是说您不这样做） t 想要特定于语言的文本排序/比较），或（2）使用text_pattern_ops
运算符类。

让我们创建相同的索引，但这次指定text_pattern_ops
操作符类：

CREATE INDEX ON users (email text_pattern_ops);
pgaweb=# EXPLAIN SELECT * FROM users WHERE email LIKE 'lukas@%';
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Index Scan using users_email_idx on users  (cost=0.14..8.16 rows=1 width=4463)
   Index Cond: (((email)::text ~>=~ 'lukas@'::text) AND ((email)::text ~<~ 'lukasA'::text))
   Filter: ((email)::text ~~ 'lukas@%'::text)
(3 rows)

正如您现在所看到的，相同的LIKE
查询可以使用索引。

现在我们知道了列的索引类型和运算符类，让我们看看创建索引的其他一些方面。

添加 Postgres 索引时指定多列

一项基本功能是可以将多个列添加到索引定义中。

你可以这样做：

CREATE INDEX ON [table] ([column_a], [column_b]);

但这实际上是做什么的？原来它取决于索引类型。每种索引类型对其数据结构中的多个列都有不同的表示。并且某些索引类型（例如 BRIN 或 Hash）不支持多列。

然而，对于最常见的索引类型，B-tree，多列索引运行良好，并且它们被普遍使用。对于多列 B 树索引，最重要的一点是：列顺序很重要。如果您有一些查询只使用column_a
，但所有查询都使用column_b
，则应将column_b
索引定义放在首位。如果你不遵循这条规则，你最终会得到做更多工作的查询，因为它们必须跳过它们无法过滤的所有早期列。另一方面，对于 GIST 索引，这无关紧要 - 您可以按任何顺序指定列。

另一个决定是：我应该创建多个索引，为我查询的每一列创建一个索引，还是应该创建一个多列索引？

CREATE INDEX ON [table] ([column_a]);
CREATE INDEX ON [table] ([column_b]);
--- or
CREATE INDEX ON [table] ([column_a], [column_b]);

查看单个查询时，答案几乎总是：创建与查询匹配的单个多列索引。它会比拥有多个索引更快。

但是如果您有更大的工作量，创建多个单列索引可能是有意义的。请注意，在这种情况下 Postgres 将不得不做更多的工作，您应该通过查看您的 EXPLAIN 计划来验证实际选择了哪些索引。

在索引定义中使用函数和表达式

暂时从特定的索引类型退一步：Postgres 有一个适用于所有索引类型的通用功能，这非常有用：您可以索引引用列数据的表达式，而不是索引特定列的值。

例如，我们通常可以将我们的用户电子邮件地址与lower(..)
函数进行比较：

SELECT * FROM users WHERE lower(email) = $1

如果您要对此运行 EXPLAIN，您会注意到 Postgres 无法在email
此处使用简单索引 - 因为它与表达式不匹配。

但既然lower(..)
是所谓的“不可变”函数，我们可以使用它来创建一个表达式索引，以小写形式索引电子邮件的所有值：

CREATE INDEX ON users (lower(email));

现在我们的查询将能够使用索引。请注意，这不适用于所有功能。例如，如果您要在上创建索引now()
，它将失败：

CREATE INDEX ON users (now());
ERROR:  functions in index expression must be marked IMMUTABLE

此外，请记住，表达式索引仅在与查询匹配时才有效。如果我们只有一个索引 on lower(email)
，那么简单引用的查询email
将无法使用该索引。

指定 WHERE 子句来创建部分 PostgreSQL 索引

让我们回到文章开头看到的一个例子——但现在让我们看一下NullTest
表达式：

在这里，我们确保我们只获取尚未被我们的应用程序标记为已删除的行。根据您的工作量，这可能是需要跳过的大量行。

虽然您可以创建一个包含该deleted_at
列的索引，但拥有所有这些您实际上不想查看的索引条目将是非常浪费的。

Postgres 有一个更好的方法：使用部分索引，您可以限制索引具有索引条目的行。当限制不适用时，该行将不会保存到索引中，从而节省空间。在查询执行期间，这在许多情况下也可以显着节省时间，因为规划器可以通过简单的检查来确定哪些部分索引匹配，并忽略所有不匹配的。

实际上，您需要做的就是WHERE
在索引定义中添加一个子句：

CREATE INDEX ON users(email) WHERE deleted_at IS NULL;

不过，您可能不想这样做是有原因的：

首先，添加此限制意味着只有包含的查询deleted_at IS NULL
才能使用索引。这意味着您可能需要两个索引，一个有该限制，另一个没有。

其次，添加数百或数千个部分索引会导致 Postgres 规划器产生开销，因为它必须进行更昂贵的分析以确定可以使用哪些索引。

使用 INCLUDE 为仅索引扫描创建覆盖索引

最后但同样重要的是，让我们谈谈 Postgres 的一个更新：INCLUDE
可以添加到CREATE INDEX
.

在我们查看这个关键字的作用之前，让我们了解索引扫描和仅索引扫描之间的区别。当所有需要的数据都可以从索引本身中检索出来时，一个仅索引扫描是可能的——而不是必须从磁盘中获取它。

请注意，仅索引扫描仅在表最近被 VACUUMed 时才起作用 - 否则 Postgres 将需要过于频繁地检查每个索引条目的可见性，因此不会选择使用仅索引扫描，而是在大多数情况下更喜欢索引扫描.

让我们看两个示例 - 一个完全匹配索引的查询，另一个不匹配（因为目标列表）：

CREATE INDEX ON users (email, id);
=# EXPLAIN SELECT id FROM users WHERE email = 'test@example.com';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Index Only Scan using users_email_id_idx on users  (cost=0.14..4.16 rows=1 width=4)
   Index Cond: (email = 'test@example.com'::text)
(2 rows)
=# EXPLAIN SELECT id, fullname FROM users WHERE email = 'test@example.com';
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Index Scan using users_email_id_idx on users  (cost=0.14..8.15 rows=1 width=520)
   Index Cond: ((email)::text = 'test@example.com'::text)
(2 rows)

现在，为了获得第二个查询的仅索引扫描，我们可以创建一个索引，该索引在末尾包含该列 - 这使得 Postgres 使用仅索引扫描：

CREATE INDEX ON users (email, id, fullname);
=# EXPLAIN SELECT id, fullname FROM users WHERE email = 'test@example.com';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Index Only Scan using users_email_id_fullname_idx on users  (cost=0.14..4.16 rows=1 width=520)
   Index Cond: (email = 'test@example.com'::text)
(2 rows)

但是，这样做有一些限制：如果您有唯一索引，它就不起作用（因为任何列都会修改正在检查的唯一性），并且它会使存储在索引中的数据膨胀以进行搜索。

对于 B 树索引，新的 INCLUDE 关键字是更好的方法：

CREATE INDEX ON users (email, id) INCLUDE (fullname);

这使此类附加列的开销略低，使用 UNIQUE 约束索引没有问题，并且清楚地传达了意图：您仅添加了一个列以支持仅索引扫描。

这是一个最好谨慎使用的功能：向索引添加更多数据意味着更大的索引值，这本身可能是一个问题——在索引的 INCLUDE 子句中添加大量列通常不是一个好主意。

在生产环境中安全地添加和删除 PostgreSQL 索引

我将以警告结束：在生产数据库上创建索引需要一些思考。不仅要使用哪个索引定义，还包括如何创建它们，以及何时接受正在构建的新索引的 I/O 影响。

最重要的是：记住 Postgres 会在你简单地运行时获取一个排他锁CREATE INDEX
，这将阻止对该表的所有读取和写入。这就是 Postgres 有 specialCONCURRENTLY
关键字的原因。在生产环境中为已有数据的表创建索引时，请始终指定此关键字：

CREATE INDEX CONCURRENTLY ON users (email) WHERE deleted_at IS NULL;

这与使用 -add 删除索引时相同，会稍微降低锁定要求，DROP INDEX
从而CONCURRENTLY
更快地在生产环境中使用此操作。

结论

在这篇文章中，您应该对运算符和运算符类如何与索引相关，以及为什么了解这些概念对于为复杂查询创建最佳索引至关重要。我们还查看了该CREATE INDEX
命令的一些补充功能，在推理要创建哪个索引时通常需要这些功能。

实际上有几件事我们没有讨论：向特定表空间添加索引、使用索引存储参数（对 GIN 索引类型特别有用！）以及指定特定列的排序顺序。我鼓励您进一步查看有关这些主题的 Postgres 文档。

https://www.postgresql.org/docs/current/sql-createindex.html

索引 select 逻辑运算比较运算符 email

文章转载自飞象数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。