从零开始学PostgreSQL (三)：索引篇

DBA实战

已于 2024-07-23 18:25:40 修改

阅读量1.2k

点赞数 22

文章标签： postgresql 数据库

于 2024-07-18 07:30:00 首次发布

本文链接：https://blog.csdn.net/qq_34692500/article/details/140539723

版权

1、索引类型

2、多列索引

3、索引和ORDER BY

4、组合多个索引

5、唯一索引

6、表达式的索引

7、部分索引

8、索引扫描和覆盖索引

9、检查和优化索引的使用情况

10、索引的选择和使用

索引类型

B-tree 索引：

适用场景：范围查询、等值查询、排序操作。

特点：适用于大部分查询场景，是 PostgreSQL 默认的索引类型。

哈希索引：

适用场景：等值查询，对于频繁的等值查询有性能优势。

特点：不支持范围查询和排序操作，大小写敏感。

GIN 索引：

适用场景：全文搜索、数组包含查询、JSONB 数据类型查询。

特点：支持对复杂查询条件的优化，如使用数组和 JSONB 类型的数据。

GiST 索引：

适用场景：空间数据类型（如几何形状）、全文搜索。

特点：支持多种数据类型的复杂查询优化。

BRIN 索引：

适用场景：大表的列存储，适合有序数据。

特点：适合于大数据量表的存储，减少索引的存储空间。

Partial 索引：

适用场景：对表中特定子集数据的查询优化。

特点：只对表中满足条件的行建立索引，节省存储空间和提高查询性能。

SP-GiST 索引：

适用场景：空间数据类型（如几何形状）、全文搜索。

特点：支持多种数据类型的复杂查询优化。

bloom 索引：

适用场景：适用于高基数列的等值查询优化。

特点：布隆过滤器索引，适合于检查元素是否属于一个集合，但可能存在误报（false positive），因此需要使用实际数据再次验证

多列索引

只有B 树、GiST、GIN 和 BRIN索引类型支持多列键索引。索引是否可以有多个键列与是否可以向索引中添加列无关。每个索引最多可以有32列，包括键列

示例 
CREATE INDEX test2_mm_idx ON test2 (major, minor);

1）B 树索引

多列B 树索引可以与涉及索引任意子集的查询条件一起使用，但在约束条件应用于前导（最左边）列时效率最高。

对于多列索引，等式约束应用于前导列，并且在第一个没有等式约束的列上应用不等式约束，这些约束将限制扫描索引的部分。

对于后续列的约束也会在索引中检查，这样可以减少对实际表的访问次数，但并不会减少需要扫描的索引部分。

2）GiST 索引

多列GiST索引可以与涉及任意子集的查询条件一起使用。对额外列的条件限制会限制索引返回的条目，但第一列上的条件最为重要，影响需要扫描的索引部分。

3）GIN 索引：

多列GIN索引可以与涉及任意子集的查询条件一起使用。与B 树或GiST不同的是，无论查询条件使用哪些索引列，索引搜索的效果都是相同的。

4）BRIN 索引：

多列BRIN索引可以与涉及任意子集的查询条件一起使用。与GIN类似，与B 树或GiST不同的是，无论查询条件使用哪些索引列，索引搜索的效果都是相同的。

在单个表上，多个BRIN索引通常没有必要，除非需要使用不同的存储参数(pages_per_range)。

总结：

每种索引类型对多列索引的支持和效果略有不同，应根据具体查询模式、数据类型和性能需求选择合适的索引类型。

多列索引应谨慎使用，因为单列索引通常已经足够提供良好的性能，并节省存储空间和维护成本。

对于超过三列的索引，除非表的使用非常特殊，否则可能不会有帮助。

索引和ORDER BY

目前 PostgreSQL 支持的索引类型中，只有 B 树能够产生排序的输出结果 — 其他索引类型返回的匹配行的顺序是未指定的，依赖于具体实现。

默认情况下，B 树索引以升序存储条目，空值排在最后。这意味着对于列 x 的索引正向扫描会生成满足 ORDER BY x 或 ORDER BY x ASC NULLS LAST 的输出。索引也可以反向扫描，生成满足 ORDER BY x DESC 或 ORDER BY x DESC NULLS FIRST 的输出。

在创建 B 树索引时，可以通过包括 ASC、DESC、NULLS FIRST 和 NULLS LAST 选项来调整索引的排序顺序。

例如：
CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST); 
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

在单列索引中，这些选项可能看起来有些冗余，但在多列索引中它们非常有用。考虑一个两列索引 (x, y)：正向扫描可以满足 ORDER BY x, y，反向扫描可以满足 ORDER BY x DESC, y DESC。但如果应用程序频繁需要使用 ORDER BY x, y ASC，则普通索引无法提供此顺序，但可以通过定义为 (x ASC, y ASC) 或 (x DESC, y ASC) 来实现。

显然，具有非默认排序顺序的索引是一种相对特殊的功能，但有时它们可以为某些查询带来巨大的性能提升。是否值得维护这样的索引取决于查询中需要特定排序顺序的频率。

组合多个索引

单索引限制：

单个索引扫描仅能使用涉及索引列和相应操作符的查询子句。例如，复合条件如 (a = 5 AND b = 6) 可以使用索引，但像 (a = 5 OR b = 6) 这样的查询则不能直接使用索引。

多索引组合优势：

PostgreSQL 提供了能力来结合多个索引（包括同一索引的多次使用），以处理单个索引无法覆盖的查询情况。系统可以通过多次索引扫描形成 AND 和 OR 条件来实现复杂的查询需求。

操作原理：

结合多个索引时，系统会对每个索引进行扫描，生成一个位图表示匹配该索引条件的表行位置。这些位图根据查询的需要进行 AND 和 OR 运算。最终，实际的表行按物理顺序访问并返回结果。

性能考虑：

尽管多索引组合可以解决复杂查询，每个额外的索引扫描会增加时间成本。因此，对于需要大量扫描的查询，可能会选择使用简单的索引扫描，而不是结合多个索引。

索引设计策略：

在设计索引时，数据库开发人员需权衡决策哪些索引能够最好地支持常见的查询模式。有时候选择多列索引是最优的，但在某些情况下，创建单独的索引并依赖索引组合功能可能更为有效。

唯一索引

声明唯一索引：

使用 CREATE UNIQUE INDEX 语句可以创建唯一索引，目前只有 B 树索引支持唯一性约束。
```
示例：CREATE UNIQUE INDEX name ON table (column [, ...]);
```

唯一索引特性：

声明唯一索引后，索引列的数值在表中必须唯一，不允许出现相同的索引值对应多行数据。
默认情况下，唯一索引对空值不视为相同，因此允许多个空值存在于索引列中。使用 NULLS NOT DISTINCT 可以修改此行为，使得空值视为相同。

自动创建唯一索引：

当为表定义唯一约束或主键时，PostgreSQL 会自动创建唯一索引。该索引覆盖构成主键或唯一约束的列（如果适用，会创建多列索引），并用于实施约束。

注意事项：

在唯一约束列上手动创建索引通常是多余的，因为系统会自动创建该索引。手动创建索引可能会导致重复，不建议这样做。

表达式的索引

索引列不必只是基础表的一列，还可以是从表的一列或多列计算得出的函数或标量表达式。此功能对于根据计算结果快速访问表非常有用。

例子：大小写不敏感比较

使用函数 lower 进行大小写不敏感的比较：

SELECT * FROM test1 WHERE lower(col1) = 'value';

如果在 lower(col1) 的结果上定义了索引，这个查询可以利用索引：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

这种索引可以防止插入值仅在大小写上有区别的行，以及确保实际值相同的行不会重复插入，因此索引表达式可以用于实施不能定义为简单唯一约束的约束。
如果经常执行像下面这样的查询：

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

可以考虑创建以下索引，结合 first_name 和 last_name：

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

在定义索引表达式时，通常需要在表达式周围加上括号，如第二个例子所示。当表达式仅为函数调用时，可以省略括号，如第一个例子。

索引表达式的性能

索引表达式的维护成本较高，因为对于每次行插入和非 HOT 更新，必须计算派生表达式。然而，在索引搜索期间，不需要重新计算索引表达式，因为它们已经存储在索引中。
索引表达式适用于检索速度比插入和更新速度更重要的场景。

部分索引

主要用途：

避免索引常见值：部分索引的一个主要原因是避免索引常见值。如果一个查询搜索的是常见值（即占表行总数超过几个百分点的值），那么索引将不会被使用，因此没有必要在索引中保留这些行。通过部分索引，可以减小索引的大小，加快那些使用索引的查询速度。此外，由于索引不需要在所有情况下都更新，部分索引还可以加快许多表更新操作的速度。
索引数据：不在索引范围内的数据，不能使用部分索引。

例 11.1设置部分索引以排除公共值

假设你在数据库中存储 Web 服务器访问日志。大多数访问来自于你组织的 IP 地址范围，但有些来自于其他地方（比如员工使用拨号连接）。如果你的 IP 地址搜索主要针对外部访问，那么你可能不需要索引与你组织子网对应的 IP 范围。

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

例 11.2.设置部分索引以排除不感兴趣的值

如果您的表同时包含计费订单和未计费订单，其中未计费订单仅占总表的一小部分，但这些行是访问次数最多的行，则可以通过仅在未计费行上创建索引来提高性能。创建索引的命令如下所示：

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

例 11.3.设置部分唯一索引

假设我们有一个描述测试结果的表格。我们希望确保给定的主题和目标组合只有一个“成功”条目，但可能存在任意数量的“不成功”条目。这是一种方法：

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    faill boolean
    ...
);


CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

当成功的测试很少而失败的测试很多时，这是一种特别有效的方法。也可以通过创建具有限制的唯一部分索引，在列中只允许一个 null。IS NULL

例 11.4.不要使用部分索引来替代分区

在数据库中，不应该通过创建大量非重叠的部分索引来替代分区。例如，像下面这样创建一组部分索引：

CREATE INDEX mytable_cat_1 ON mytable (data) WHERE category = 1;
CREATE INDEX mytable_cat_2 ON mytable (data) WHERE category = 2;
CREATE INDEX mytable_cat_3 ON mytable (data) WHERE category = 3;
...
CREATE INDEX mytable_cat_N ON mytable (data) WHERE category = N;

这种做法是不明智的！更好的方式是使用一个单独的非部分索引，如下所示：

CREATE INDEX mytable_cat_data ON mytable (category, data);

注意事项

这种部分索引需要预先确定共同的值，因此最适合用于数据分布不会经常变化的情况。这样的索引偶尔需要重新创建以适应新的数据分布，但这会增加维护工作量。

仅索引扫描和覆盖索引

索引类型与索引只扫描：

所有的索引在PostgreSQL中都是辅助索引（Secondary Index），与表的主数据区域（Heap）分开存储。
索引只扫描要求索引类型必须支持，例如B-tree索引始终支持，GiST和SP-GiST索引则根据操作类别支持不同的索引只扫描。

索引只扫描的条件：

查询必须仅引用存储在索引中的列，如果查询引用了非索引列，则无法使用索引只扫描。
对于表扫描，还需要验证每个检索的行对于查询的MVCC快照是否可见，这是通过可见性映射（Visibility Map）实现的。

覆盖索引（Covering Index）：

为了有效利用索引只扫描功能，可以创建覆盖索引，即包含查询所需列的索引。
使用INCLUDE子句可以添加非搜索键的列到索引中，提高特定查询模式的性能。

注意事项：

添加非键列到索引可能会增加索引的大小，可能导致性能下降，尤其是对于宽列。
在表数据变化较慢的情况下，才有利于索引只扫描不访问堆。
目前表达式索引不支持作为包含列，而且只有B-tree、GiST和SP-GiST索引支持包含列。

检查和优化索引的使用情况

使用 EXPLAIN 命令分析单个查询：

使用 EXPLAIN 命令可以分析单个查询的执行计划，包括查询是否使用了索引以及使用了哪些索引。
这对于了解实际查询工作负载中索引的使用情况非常重要。

收集统计信息：

在优化索引前，始终先运行 ANALYZE 命令。这个命令用于收集关于表中值分布的统计信息。
统计信息对于评估查询返回行数的分布是必要的，优化器需要根据这些信息为每个可能的查询计划分配合理的成本。
如果没有实际的统计信息，优化器会使用默认值，这几乎肯定是不准确的。

使用真实数据进行实验：

在设置索引时，使用真实数据进行实验可以告诉你针对测试数据集需要哪些索引。
使用非常小的测试数据集通常是不可取的，因为这可能无法准确反映真实数据的查询性能。

强制使用索引：

当索引未被使用时，可以通过运行时参数强制其使用，例如关闭顺序扫描 (enable_seqscan) 或嵌套循环连接 (enable_nestloop)。
如果强制使用索引后确实使用了索引，那么有两种可能性：系统正确地判断索引不适合使用，或者查询计划的成本估算不符合实际情况。

使用 EXPLAIN ANALYZE 命令：

使用 EXPLAIN ANALYZE 命令可以获取查询的详细执行统计信息，包括实际执行时间和成本估算。
这对于评估查询计划的实际性能表现非常有用，特别是在调整查询计划的成本估算时。

调整查询计划的成本估算：

如果成本估算不准确，可以通过调整运行时参数来调整计划节点的成本估算，或者通过优化统计信息收集参数来提高查询选择性估算的准确性。

总体而言，优化索引的过程涉及实验、分析和调整，需要根据具体的查询工作负载和实际数据来进行。通过这些步骤，可以更有效地提升 PostgreSQL 数据库的查询性能和响应速度。

索引的选择和使用

在设计和选择索引时，需要考虑以下因素：

查询模式：经常执行的查询类型是什么？
数据类型：表中存储的数据类型及其特点。
数据分布：索引列上数据的分布情况，是否均匀？
写入操作：索引对写入操作的影响如何？

综上所述，每种索引类型在不同的场景下都有其优势和劣势。正确选择和设计索引是优化 PostgreSQL 数据库性能的关键一步。

微信关注公众号 DBA实战 ，觉得帖子写的不错，点点关注，点点赞，多多转载。

DBA实战

关注

22
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
从零开始学PostgreSQL (三)：索引篇

目录1、索引类型2、多列索引3、索引和ORDER BY4、组合多个索引5、唯一索引6、表达式的索引7、部分索引8、索引扫描和覆盖索引9、检查和优化索引的使用情况10、索引的选择和使用索引类型B-tree 索引：适用场景：范围查询、等值查询、排序操作。特点：适用于大部分查询场景，是 PostgreSQL 默认的索引类型。哈希索引：适用场景：等值查询，对于频繁的等值查询有性能优势。特点：不支持范围查询...
复制链接

扫一扫