在 Amazon Aurora PostgreSQL 和 Amazon RDS for PostgreSQL 中支持的索引类型Btree

重要信息

索引是一种数据结构，数据库系统利用它来加速查询。在大多数传统关系数据库系统中，索引在数据检索中扮演着关键角色。每种类型的索引采用不同的算法，适合不同类型的查询。例如，子优化的索引策略，如不正确的索引类型、索引过多或根本没有索引，可能会导致数据库性能下降。

在本系列文章中，我们将讨论 Amazon Aurora PostgreSQL 兼容版和 Amazon RDS for PostgreSQL 支持的索引类型及其使用场景。在此篇文章中，我们将重点介绍原生 Btree 索引及其变体。接下来的 第二部分 将讨论其他原生索引，包括 GIN、GiST、HASH 和 BRIN。

在决定数据库中需要的索引之前，我们需要理解使用索引检索数据的访问模式。索引伴随维护和空间开销，因此需要合理使用。接下来的部分将讨论在创建索引时，根据数据检索需求和使用场景应应用的策略。

前置条件

要跟随此文，您需要有一个 Amazon RDS for PostgreSQL 或 Amazon Aurora PostgreSQL 兼容的数据库，以及安装了 psql 的客户端机器并与数据库相连。有关更多信息，请参考 连接到运行 PostgreSQL 数据库引擎的数据库实例。

以下脚本用于生成示例数据集。该脚本为员工表生成一个样本数据集，包含各种不同数据类型的列，如字符字符串、整数、日期、数组等。它使用 PostgreSQL 函数如 RANDOM()、REPEAT() 和 GENERATESERIES() 来生成随机样本数据，随后将在整个文章中使用。

sqlCREATE TABLE employee ( empname CHARACTER VARYING COLLATE C empid BIGINT empsalary BIGINT empdept CHARACTER VARYING empdoj DATE emplwd DATE empage INTEGER empcities TEXT empaddresshist JSONB empcitylist TEXT[] emptenure TSRANGE)

INSERT INTO employeeSELECTsubstr(REPEAT(abexoptkhngsadl3)((random()(301)1)integer)5) AS empnameI AS empidTRUNC(RANDOM() 10000) AS empsalaryUPPER(substr(REPEAT(REVERSE(abexoptkhngsadl)3)((random()(301)1)integer)4)) AS empdept1/1/2016DATE (1 YEARINTERVAL FLOOR(RANDOM()5)) AS empdoj1/1/2020DATE (2 YEARINTERVAL FLOOR(RANDOM()6)) AS emplwdfloor(random() (39301) 30)int AS empageCITYICITYXCITYI2 AS empcitiesTOJSONB(ROW(ITEXT CITYICITYXCITYI2)) AS empaddresshistARRAY[CONCATWS(CITYI)CITYXCONCATWS(CITYI2)] AS empcitylist([2015I12I30 2017I12I30))TSRANGE AS emptenureFROM GENERATESERIES(1 5000000) AS IWHERE i12 BETWEEN 1 AND 13 AND i30 BETWEEN 1 AND 28

Btree 索引

平衡树Btree是一种自平衡的 树数据结构，用于维护排序数据并允许搜索。Btree 索引可以处理对可排序数据的相等和范围查询。只要索引字段包含下列运算符的过滤条件，就可以使用 Btree 索引：lt、lt=、=、gt= 和 gt。PostgreSQL 中的 Btree 索引是最常用的索引类型，也是许多数据类型的默认索引。

以下代码展示了没有在 empid 列上使用索引时的查询执行计划：

sqlEXPLAIN ANALYZE SELECT FROM employee WHERE empid = 444

Seq Scan on employee (cost=000184702715 rows=1 width=204) (actual time=1601876416018765 rows=0 loops=1)Filter (empid = 444)Rows Removed by Filter 43333335Planning Time 0059 msExecution Time 16018789 ms注意：此示例中并未启用并行查询执行maxparallelworkerspergather = 0。

sqlCREATE INDEX idxempid ON employee(empid)

现在在 empid 列上创建索引后，我们重新运行相同的查询执行计划。

sqlIndex Scan using idxempid on employee (cost=056858 rows=1 width=201) (actual time=10131016 rows=0 loops=1)Index Cond (empid = 444)Planning Time 1001 msExecution Time 3446 ms

从执行计划中，我们可以观察到，优化器选择了索引扫描在过滤条件列 empid=444 上的索引扫描，创建索引后，我们得到了更快的执行时间。

在本篇文章的剩余部分，我们将回顾可以实施的其他特殊 Btree 索引，以实现更快的查询执行时间，并讨论我们如何利用 Btree 索引来加快查询响应。

仅索引扫描

索引指向实际存储在磁盘上的数据，并且与表的主数据区即表堆分开存储。这意味着每次检索行时，将需要从索引和表堆中获取数据。虽然检索的记录 (EMPID = 444在磁盘上会更靠近，而表行可能存在于表堆的任意位置。因此，索引扫描的堆访问部分需要大量随机访问堆，这可能会很慢。因此，为了加速索引扫描，如果在选择中只获取了过滤条件的列，那么只需使用索引堆区即可检索行，这称为仅索引扫描。

sqlEXPLAIN ANALYZE SELECT empid FROM employee WHERE empid = 444

Index Only Scan using idxempid on employee (cost=056458 rows=1 width=8) (actual time=00320032 rows=0 loops=1)Index Cond (empid = 444)Heap Fetches 0Planning Time 0112 msExecution Time 0057 ms

索引对范围操作的影响

Btree 同样适用于 WHERE 子句包含 “BETWEEN AND” 运算符的查询，它与 gt= 和 lt= 相同。例如，大于等于 111 并且小于等于 222 的值也可以表示为 111 和 222 之间的值。

以下代码使用 Btree 索引来利用 BETWEEN AND 运算符。

sqlEXPLAIN ANALYZE SELECT FROM employee WHERE empid BETWEEN 111 AND 222

Index Scan using employeeempididx on employee (cost=02956942 rows=193 width=192) (actual time=00480374 rows=194 loops=1)Index Cond ((empid gt= 111) AND (empid lt= 222))Planning Time 3170 msExecution Time 0426 ms

使用 Btree 加速排序

PostgreSQL 的 Btree 索引可以生成排序输出。您还可以使用 Btree 返回 “前 N” 排序选择。您可以使用索引来按前 N 行的 SQL 进行排序。因为 Btree 返回的数据是按排序顺序排列的，所以无需对整个数据集进行排序。这同样有利于使用聚合函数如 min/max 的查询。请看以下代码：

sqlEXPLAIN ANALYZE SELECT FROM employee ORDER BY empid DESC LIMIT 10

Limit (cost=056113 rows=10 width=204) (actual time=19561959 rows=10 loops=1)gt Index Scan Backward using idxempid on employee (cost=056243058944 rows=43331792 width=204) (actual time=19551957 rows=10 loops=1)

EXPLAIN ANALYZE SELECT max(empid) FROM employee

Result (cost=059060 rows=1 width=8) (actual time=02080209 rows=1 loops=1)InitPlan 1 (returns 0)gt Limit (cost=056059 rows=1 width=8) (actual time=02030203 rows=1 loops=1)gt Index Only Scan Backward using idxempid on employee (cost=056123355856 rows=43330400 width=8) (actual time=02010201 rows=1 loops=1) Index Cond (empid IS NOT NULL)Heap Fetches 0Planning Time 0215 msExecution Time 0244 ms

将 Btree 索引用于相似查询LIKE

您可以对使用模式匹配运算符LIKE、 或 !的查询使用 Btree 索引。这仅适用于使用 C 排序规则创建的数据库，或者在创建表时需要的索引列应采用 C 排序。在非 C 区域的模式匹配中，如果明确将其定义为运算符类的一部分，也可以使用 LIKE。具体请参见 运算符类和运算符族。

模式匹配应为常量，并且应位于字符串的开头，例如，查询 empname 为 dub。对于 SQL 查询 empname 为 dub 的情况，索引将不会被使用。

看下面的示例，当没有索引时使用 LIKE 子句的情况。

sqlEXPLAIN ANALYZE SELECT FROM publicemployee WHERE empname LIKE dub

Seq Scan on employee (cost=000184699970 rows=1 width=201) (actual time=42471724247176 rows=0 loops=1)Filter ((empname)text dubtext)Rows Removed by Filter 43333335Planning Time 0108 msExecution Time 4247200 ms

让我们看一个例子，其中有两个员工表在不同模式下，其中一个的 empname 采用 C 排序规则，一个则没有。

sqlSELECT tableschema tablename columnname collationname FROM informationschemacolumns WHERE tablename LIKE emp AND columnname = empname

sqlCREATE INDEX idxempname ON testemployee(empname)CREATE INDEX idxempname ON publicemployee(empname)

对于 publicemployee 的 LIKE 查询其列为 empname CHARACTER VARYING COLLATE “C”，将会经过索引扫描。

sqlEXPLAIN ANALYZE SELECT FROM publicemployee WHERE empname LIKE dub

Index Scan using idxempname on employee (cost=044846 rows=1 width=201) (actual time=00250026 rows=0 loops=1)Index Cond (((empname)text gt= dubtext) AND ((empname)text lt ductext))Filter ((empname)text dubtext)Planning Time 0139 msExecution Time 0047 ms

对于 testemployee 中的 LIKE 查询其列为 empname CHARACTER VARYING，即未带 COLLATE “C” 子句，将会经过顺序扫描。

sqlEXPLAIN ANALYZE SELECT FROM testemployee WHERE empname LIKE dub

Seq Scan on employee (cost=000177869 rows=1 width=192) (actual time=1520315204 rows=0 loops=1)Filter ((empname)text dubtext)Rows Removed by Filter 43335Planning Time 0986 msExecution Time 15236 ms

多列索引

在多个列上创建的索引称为 多列索引。如果您的用例中有些查询需要基于列 (empname) 或 (empdep) 进行过滤，同时还有一些涉及两列 (empname empdep) 的查询，您可以创建一个多列索引。一般情况下，单个多列索引会对使用这些列的组合进行的所有查询使用索引扫描。

在下面的示例中，如果我们在 (empname empdep) 上创建了多列索引，那么在 WHERE 子句中使用 empname 或 empdep，甚至同时使用这两列时，都将会使用索引扫描。

sqlCREATE INDEX idxempnamedept ON employee (empname empdep)

EXPLAIN ANALYZE SELECT FROM employee WHERE empname=drnkcev AND empdep=K03YU

Index Scan using idxempnamedept on employee (cost=0291329 rows=1 width=192) (actual time=00170018 rows=0 loops=1)Index Cond (((empname)text = drnkcevtext) AND ((empdep)text = K03YUtext))Planning Time 0183 msExecution Time 0042 ms

EXPLAIN ANALYZE SELECT FROM employee WHERE empname=drnkcev

Index Scan using idxempnamedept on employee (cost=0291329 rows=1 width=192) (actual time=00180019 rows=0 loops=1)Index Cond ((empname)text = drnkcevtext)Planning Time 0074 msExecution Time 0044 ms

EXPLAIN ANALYZE SELECT FROM employee WHERE empdep=K03YU

Index Scan using idxempnamedept on employee (cost=02959179 rows=1 width=192) (actual time=02020202 rows=0 loops=1)Index Cond ((empdep)text = K03YUtext)Planning Time 0073 msExecution Time 0228 ms

不过，索引中列的顺序是很重要的。当多列 Btree 索引的最左边列有约束时，它的效率是最高的。例如，假设一个以 (empage empsalary empid) 为索引的情况下，如果查询条件为 WHERE empage = 45 AND empsalary gt= 1000 AND empid lt 200，那么索引需要从第一个 empage = 45 和 empsalary = 1000 的条目开始扫描，直到最后一个 empage = 45 的条目。尽管会跳过 lt 200 的 empid 的索引条目，但仍需要扫描它们。

部分索引

部分索引 是在表数据的子集上创建的索引。该子集是基于条件谓词定义的。此索引类型的主要用例之一是避免对常见值进行索引。例如，假设有 empage 字段，其中百分比不大的一小部分员工年龄不是 35 岁，而我们只对这些员工感兴趣，而不关心常见值年龄为 35 的员工。我们可以在表数据的此子集年龄 ltgt 35上创建部分索引，并使用索引对 SQL 进行过滤以查找不常见的值年龄不等于 35 的所有员工。此索引类型的另一个优点是其索引大小会小于对没有条件的列进行的索引。

sqlCREATE INDEX idxempagesubset ON employee(empage) WHERE NOT (empage = 35)

Name Type Owner Table Persistence Access method Sizeidxempage index postgres employee permanent btree 286 MBidxempagesubset index postgres employee permanent btree 80 kB

EX