PostgreSQL中的索引——1

参宿_七

已于 2022-03-15 18:21:28 修改

阅读量1.6k

点赞数

分类专栏： # 索引文章标签： postgresql 数据库

于 2022-01-13 16:13:29 首次发布

原文链接：https://postgrespro.com/blog/pgsql/3994098

版权

索引专栏收录该内容

10 篇文章 8 订阅

订阅专栏

介绍

这一系列的文章与PostgreSQL中的索引非常相关。

任何的学科都可以从不同的角度考虑，我们将讨论一些让使用DBMS的应用开发人员感兴趣的事：哪些索引是可用的，为什么有这么多种，以及如何使用它们来提升查询速度。这一主题可能能被很少的词汇概括，但我们真心希望能有好奇的开发者对内部细节感兴趣，因为理解这些细节能使你不仅仅遵从他人的判断，也能自己得出结论。

开发新的索引类型不在此讨论范围中，这需要C语言开发知识，并且这属于系统开发者的专业领域而不是应用开发者。同样，我们几乎不讨论接口编程，而只关注使用已有索引的相关事物。

在本文中，我们将讨论（DBMS核心相关的通用索引引擎）与（PostgreSQL允许以扩展形式添加的独立索引访问方法）之间的职责分配。在下篇文章中，我们将讨论访问方法的接口和对象、操作符簇的关键概念。在漫长但重要的介绍之后，我们将讲解不同类型索引的结构细节与应用，这些索引包括：Hash, B-tree, GiST, SP-GiST, GIN 和RUM, BRIN, 和Bloom.

索引

（索引的作用）在PosgreSQL中，索引是特殊的数据库对象，主要用于加速数据访问。它们是辅助结构：每个索引都可以从表信息中删除并重建。你也许有时碰巧听过，DBMS不需要索引也能工作，只是会很慢。但是，情况并非如此，因为索引还用于强制执行某些完整性约束。

当前，PosgreSQL9.6中建立了6种不同种类的索引，还有另一种索引可作为扩展使用，这要感谢9.6版的重大更改。因此，预计在不久的将来会出现新的索引类型。

尽管不同种索引（也被称为访问方法）之间有很多区别，但它们最终都将一个键与包含该键的表行相关联，每一行都被TID（tuple id，元组id）标识，TID标识由文件中的块编号与块中行的位置组成。也就是说，如果知道了键或者键的一些信息，我们就可以很快地读取到那些包含我们感兴趣的信息的行，而无需扫描整张表。

（为了方便理解上面这段话，可以参考《PosgreSQL指南：内幕探索》中的1.3 堆表文件的内部布局和1.4 读写元组的方式。）

（索引的成本）索引之所以能加速数据访问是建立在一定的维护成本上的。对已被索引的数据的每个操作，无论是对表行的增、删、改，该表对应的索引也需要被更新，在事务中也一样。请注意，更新没有生成索引的表字段不会导致索引更新。这项技术被称为HOT(Heap-Only Tuples，仅堆元组)。（这里不准确，在更新操作中，如果新元组与旧的死元组在不同的数据页中，也不能使用HOT，那么索引就会更新）

（扩展索引需要实现的方法）扩展性包含一些含义。为了便于向系统中添加新的访问方法，（PostgreSQL）实现了通用索引引擎的接口（如果要实现一种新型索引，需要实现这些通用接口，也就是所有索引引擎都需要实现这些方法？），它的主要任务是从访问方法中获得TID并用它们工作：

从相关版本的表行中读取数据。
按TID或使用预构建位图批量获取行版本TID（每个行都有很多版本，每个版本有自己对于事务的可见性，位图与行版本有关）。
考虑到当前事务的隔离级别，检查行版本的可见性。

（访问方法需要提供自身的信息给优化器等）索引引擎参与执行查询。它是根据在优化阶段创建的计划调用的。优化器在整理和评估执行查询的不同方法时，应该了解所有可能适用的访问方法的能力。该方法是否能够按所需顺序返回数据？或者我们是否应该先排序？我们能使用该方法来搜索NULL吗？这些问题是优化器需要考虑的。

不仅是优化器需要访问方法的信息。当创建索引时，系统必须决定是否可以在多个列上构建索引，以及此索引是否确保唯一性。

因此，每个访问方法需要提供所有关于自身的重要信息。低于9.6的版本使用“pg_am”表来描述这些信息，而从9.6版开始，这些数据被移到了更深层次的特殊函数中。我们将进一步学习这些函数接口。

以下都是访问方法的任务：

实现构建索引的算法，并将数据映射到页面中（以便缓冲区管理器统一处理每个索引）。
通过“indexed-field operator expression”形式的谓词，在索引中搜索信息。
评估索引使用成本。
操纵当前并行程序所需要的锁。
生成预写式日志（WAL）记录。

（访问方法与索引息息相关，如构建索引，评估索引使用成本，但是还有一些索引之外的事。）

我们将先考虑通用索引引擎的能力，然后继续考虑不同的访问方法。

索引引擎

索引引擎使PostgreSQL可以统一地与不同的访问方法工作，而且考虑它们的特性。

主要扫描技术

索引扫描

我们可以使用索引提供的TID进行不同的工作。让我们考虑一个例子：

postgres=# create table t(a integer, b text, c boolean);

postgres=# insert into t(a,b,c)
  select s.id, chr((32+random()*94)::integer), random() < 0.01
  from generate_series(1,100000) as s(id)
  order by random();

postgres=# create index on t(a);

postgres=# analyze t;

我们创建了一个三字段表，第一个字段包含了从1到100,000的值，并在该字段上构建了索引（无论哪种索引）；第二个字段包含各种ASCII字符，不可打印字符除外；最后，第三个字段包含了一个逻辑值，有1%的概率为true，其余为false。行被随机插入到表中。

让我们尝试以"a=1"的条件选择一个值。注意这个条件是“indexed-field operator expression”形式，操作符是“=”,表达式（搜索键）是1，大部分情况下，要使用索引，条件必须如下所示。

postgres=# explain (costs off) select * from t where a = 1;

        QUERY PLAN          
-------------------------------
 Index Scan using t_a_idx on t
   Index Cond: (a = 1)
(2 rows)

在这种情况下，优化器选择使用索引扫描。通过索引扫描，访问方法逐个返回TID值，直到到达最后一个匹配行。索引引擎依次访问由TID指示的表行，获得行版本，根据多版本并发控制检查其可见性，并返回获得的数据。

位图扫描

索引扫描当我们只处理少量值的时候很有效，然而，随着返回的行数增加，它更有可能多次返回到同一个表页。因此，优化器转而使用位图扫描。

postgres=# explain (costs off) select * from t where a <= 100;

             QUERY PLAN            
------------------------------------
 Bitmap Heap Scan on t
   Recheck Cond: (a <= 100)
   ->  Bitmap Index Scan on t_a_idx
         Index Cond: (a <= 100)
(4 rows)

1. 访问方法先返回符合条件（位图索引扫描节点）的所有TID（有些TID可能是不可见的）；

2. 根据这些从这些TID中生成行版本的位图。

3. 然后从表中读取行版本（位图堆扫描），每页只会读取一次。

（重新检查条件的情况）注意在第二步中，条件将会被重新检查（Recheck Cond）.检索到的行数可能太大，行版本的位图无法完全适应RAM（受“work_mem”参数的限制）。在这种情况下，位图仅会为包含至少一个匹配行版本的页面生成。这种“失真”位图需要的空间更少，但当读取页面时，我们需要为其包含的每个行重新检查条件。注意，即使对于返回行数更少的“精确”位图（例如在我们的示例中），也会在计划中标识“重新检查条件”步骤，尽管实际上并没有执行。

（多字段有索引时的位图扫描）如果条件涉及到表的多个字段，并且这些字段都建立了索引，位图索引允许同时使用多个索引（如果优化器认为这是有效的话）。对于每个索引，都会建立行版本的位图，然后对其执行逐位布尔乘法（如果表达式由AND连接）或布尔加法（如果表达式由OR连接）。例如：

postgres=# create index on t(b);

postgres=# analyze t;

postgres=# explain (costs off) select * from t where a <= 100 and b = 'a';

                   QUERY PLAN                    
--------------------------------------------------
 Bitmap Heap Scan on t
   Recheck Cond: ((a <= 100) AND (b = 'a'::text))
   ->  BitmapAnd
         ->  Bitmap Index Scan on t_a_idx
               Index Cond: (a <= 100)
         ->  Bitmap Index Scan on t_b_idx
               Index Cond: (b = 'a'::text)
(7 rows)

此处，BitmapAnd节点通过按位“与”操作连接两个位图。

（计划器如何权衡位图扫描与索引扫描）位图扫描让我们避免了重复访问相同的数据页，但是如果表页中的数据以与索引记录完全相同的方式进行物理排序，该怎么办呢？当然，我们不能完全依赖页中数据的物理排序，如果需要排序，我们必须在查询中明确指定order by子句。但在实际“几乎所有”数据都是有序的情况下，例如，如果行是按所需顺序添加的，并且在此之后或在执行CLUSTER命令之后没有更改，像这种情况，建立位图是很多余的步骤，一个常规的索引扫描就很好了（除非我们考虑join多个索引的可能性）。因此，当选择一个访问方法时，计划器会查看一个特殊的统计数据，这个统计显示了列值的物理行顺序与逻辑顺序之间的相关性。

postgres=# select attname, correlation from pg_stats where tablename = 't';

attname | correlation
---------+-------------
 b       |    0.533512
 c       |    0.942365
 a       | -0.00768816
(3 rows)

绝对值接近1表明高相关（就像c列），相反地，当值接近0，表示一个混乱的分布（a列）。

（这个值可以参考这篇博文中的代码：Postgresql - Cluster_chuckchen1222的博客-CSDN博客_cluster postgresql）

（位图索引的原理可以先看这篇文章，讲得比较通俗易懂：位图索引:原理（BitMap index） - zhanlijun - 博客园 (cnblogs.com)）

顺序扫描

（什么时候倾向于使用顺序扫描，为什么）我们应该注意，在没有选择条件语句的情况下（其实是需要扫描的列非常多的情况下），优化器会正确地选择对整个表进行顺序扫描，而不是使用索引：

postgres=# explain (costs off) select * from t where a <= 40000;

       QUERY PLAN      
------------------------
 Seq Scan on t
   Filter: (a <= 40000)
(2 rows)

因为索引在条件选择越高时工作地越好，即，返回更少的匹配行。但检索行数的增长增加了读取索引页面的开销。

顺序扫描比随机扫描更快，这加剧了这种情况（因为索引扫描时随机扫描）。尤其是硬盘，在硬盘上，将磁头带到磁道上的机械操作所需的时间远远超过数据读取本身。对SSD来说，这种效果更不显著。有两个参数可用于考虑访问成本的差异：seq_page_cost和rondom_page_cost，对于这两个参数，我们不仅可以全局设置，还可以在表空间级别设置，这样可以根据不同磁盘子系统的特性进行调整。

覆盖索引（Covering indexes、仅索引扫描）

通常，访问方法的主要任务是返回匹配表行的标识符，以便索引引擎从这些行中读取必要的数据。但是如果索引已经包含了查询所需的所有数据会如何呢？这种索引被称为覆盖，在这种情况下，优化器可以使用仅索引扫描：

postgres=# vacuum t;

postgres=# explain (costs off) select a from t where a < 100;

             QUERY PLAN            
------------------------------------
 Index Only Scan using t_a_idx on t
   Index Cond: (a < 100)
(2 rows)

这个名字可能会让人觉得索引引擎根本没有访问表，只从访问方法中获取了必要信息。但事实并非如此，因为PG中的索引并不存储能让我们判断行可见性的信息。因此，访问方法会返回匹配检索条件的所有行版本，无论他们在当前事务中是否可见。

然而，如果索引引擎每次都需要在表中检索可见性，那么这种扫描方法将和常规的索引扫描没有区别。

为了解决这一问题，PG为表维护了一张所谓的可见性映射（VM），清理过程在这张映射图上标记了数据修改的时间，那些数据刚被修改的不能被所有事务可见，不管事务的开始时间以及隔离级别。如果索引返回的TID与此类页面相关，则可以避免可见性检查。（其实是VM标记了哪些页的元组一定是可见的（在这些页中没有死元组，活元组都被冻结），哪些页可能包含不可见元组的，仅索引扫描可以根据VM跳过可见页的扫描）

因此，周期性清理提高了覆盖索引的效率。此外，优化器会考虑死元组的数量，如果他预估可见性检查会有很高的成本，它将决定不使用仅索引扫描。

我们可以使用EXPLAIN ANALYZE命令了解强制访问表的次数：

postgres=# explain (analyze, costs off) select a from t where a < 100;

                                  QUERY PLAN                                  
-------------------------------------------------------------------------------
 Index Only Scan using t_a_idx on t (actual time=0.025..0.036 rows=99 loops=1)
   Index Cond: (a < 100)
   Heap Fetches: 0
 Planning time: 0.092 ms
 Execution time: 0.059 ms
(5 rows)

在这种情况下，它不需要访问表（Heap Fetches: 0）,因为刚做了清理（所有页面都对当前事务可见），通常来说，该值越接近0越好。

并非所有索引都存储了行标识的索引值，如果访问方法不能返回数据，它将不会使用仅索引扫描。（比如哈希索引）

NULL

空值在关系数据库中起着重要作用，它是表示不存在或未知值的一种方便方法。

但是特殊值需要特殊对待。正则布尔代数变成了三元的。它比常规值的大小比较也不明了（这需要特殊的排序构造器，NULLS FIRST 和 NULLS LAST）；聚集函数是否应该考虑NULL值也不明显；计划器需要一个特殊的统计信息……

从索引支持的角度来看，我们也不清楚是否需要索引这些值。如果未对空值进行索引，则索引可能会更紧凑。但是，如果索引包含空值，我们将能够在“indexed field IS[NOT] NULL”等条件下使用索引，并且在没有为表指定任何条件时也可以将其用作覆盖索引（因为在这种情况下，索引必须返回所有表行的数据，包括带有空值的行）。

对于每种访问方法，开发者都能独立决定是否索引NULL值，一般来说，它们都会被索引。

多字段索引

为了支持多个字段的条件，可以使用多列索引。例如，我们可以在表的两个字段上建立索引：

postgres=# create index on t(a,b);
postgres=# analyze t;

优化器很可能更喜欢这个索引，而不是加入位图，因为在这里，我们不需要任何辅助操作即可获得所需的TID：

postgres=# explain (costs off) select * from t where a <= 100 and b = 'a';

                   QUERY PLAN                  
------------------------------------------------
 Index Scan using t_a_b_idx on t
   Index Cond: ((a <= 100) AND (b = 'a'::text))
(2 rows)

多字段索引也可用于加速某些字段的数据检索，（但是条件需要）从第一个字段开始：

postgres=# explain (costs off) select * from t where a <= 100;

              QUERY PLAN              
--------------------------------------
 Bitmap Heap Scan on t
   Recheck Cond: (a <= 100)
   ->  Bitmap Index Scan on t_a_b_idx
         Index Cond: (a <= 100)
(4 rows)

通常，如果没有在第一个字段上施加条件，则不会使用索引（与多列索引的B树结构有关，即，在第一个字段上施加条件，才会使用索引，但是上面这个例子中，在第一个字段上施加条件，却是使用位图索引而不是B树索引？）。但有时，优化器会认为使用索引扫描比顺序扫描更加高效。我们将在考虑B树时进一步讨论这一主题。

并不是所有访问方法都支持在多个字段上建立索引。

表达式上的索引（函数索引）

我们已经提到搜索条件必须像“indexed-field operator expression”（字段名操作符表达式，如a<2+3），在下面的例子中，索引将不会被使用，因为使用的是包含字段名的表达式，而不是字段名本身：

postgres=# explain (costs off) select * from t where lower(b) = 'a';

                QUERY PLAN                
------------------------------------------
 Seq Scan on t
   Filter: (lower((b)::text) = 'a'::text)
(2 rows)

重写这个特定查询不需要太多时间，只需将字段名写入操作符的左侧。但如果这不可能，表达式上的索引（函数索引）将很有用：

postgres=# create index on t(lower(b));
postgres=# analyze t;
postgres=# explain (costs off) select * from t where lower(b) = 'a';

                     QUERY PLAN                    
----------------------------------------------------
 Bitmap Heap Scan on t
   Recheck Cond: (lower((b)::text) = 'a'::text)
   ->  Bitmap Index Scan on t_lower_idx
         Index Cond: (lower((b)::text) = 'a'::text)
(4 rows)

函数索引不是建立在表字段上，而是建立在任意表达式上。优化器将考虑该索引，例如“indexed-expression operator expression”。如果计算索引表达式是一项代价高昂的操作，则索引的更新也将需要大量的计算资源。

请记住索引表达式也会被收集独立的统计信息，我们可以通过索引名字从“pg_stats”中了解这些统计信息：

postgres=# \d t

       Table "public.t"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
 b      | text    |
 c      | boolean |
Indexes:
    "t_a_b_idx" btree (a, b)
    "t_a_idx" btree (a)
    "t_b_idx" btree (b)
    "t_lower_idx" btree (lower(b))

postgres=# select * from pg_stats where tablename = 't_lower_idx';

（这里可以看到不论是多字段索引还是函数索引都是用的B树索引，因为默认的索引结构就是B树）

如有必要，可以采用与常规数据字段相同的方式控制直方图篮的数量（注意，根据索引表达式的不同，列名可能会有所不同）：

（注：直方图篮，原文为histogram baskets，应该是统计一列数据的界值直方图，查询计划在生成索引扫描的代价时，会通过该直方图考虑一个值的选择率，通过手动Analyze命令或者是autovacuum进程启动的自动分析来收集，默认直方图包括100个柱，可以参考：The Internals of PostgreSQL : Chapter 3 Query Processing (interdb.jp)）

postgres=# \d t_lower_idx

 Index "public.t_lower_idx"
 Column | Type | Definition
--------+------+------------
 lower  | text | lower(b)
btree, for table "public.t"

postgres=# alter index t_lower_idx alter column "lower" set statistics 69;

部分索引

有时，只需要索引部分表行。这通常与高度不均匀的分布有关：通过索引搜索不经常出现的值是有意义的，但通过对表进行完全扫描更容易找到经常出现的值。我们当然可以在“c”列上建立一个常规索引，它将按照我们预期的方式工作：

postgres=# create index on t(c);
postgres=# analyze t;
postgres=# explain (costs off) select * from t where c;

          QUERY PLAN          
-------------------------------
 Index Scan using t_c_idx on t
   Index Cond: (c = true)
   Filter: c
(3 rows)

postgres=# explain (costs off) select * from t where not c;

    QUERY PLAN    
-------------------
 Seq Scan on t
   Filter: (NOT c)
(2 rows)

索引大小为276页：

postgres=# select relpages from pg_class where relname='t_c_idx';

 relpages
----------
      276
(1 row)

但是因为c列只有1%的值为true，99%的索引事实上根本没用，这种情况下，我们可以建立局部索引：

postgres=# create index on t(c) where c;
postgres=# analyze t;

索引的大小下降5页：

postgres=# select relpages from pg_class where relname='t_c_idx1';

 relpages
----------
        5
(1 row)

有时，索引大小的不同会导致很大的性能差别。

排序

如果访问方法以某种特定的顺序返回行标识符，这将为优化器提供执行查询的附加选项。

我们可以（顺序）扫描表然后对数据进行排序：

postgres=# set enable_indexscan=off;

postgres=# explain (costs off) select * from t order by a;

     QUERY PLAN      
---------------------
 Sort
   Sort Key: a
   ->  Seq Scan on t
(3 rows)

但我们可以按照所需的顺序使用索引轻松读取数据：

postgres=# set enable_indexscan=on;

postgres=# explain (costs off) select * from t order by a;

          QUERY PLAN          
-------------------------------
 Index Scan using t_a_idx on t
(1 row)

在所有访问方法中，只有“btree”可以返回已排序的数据，因此，在考虑这种类型的索引之前，我们暂且不进行更详细的讨论。

并行构建

通常，构建索引需要一个表的共享锁，这个锁允许从表中读取数据，但是当正在构建索引时不能进行任何修改。

我们可以保证这一点，比如，在表t上构建索引时，我们再另一个会话中执行一下查询：

postgres=# select mode, granted from pg_locks where relation = 't'::regclass;

   mode    | granted
-----------+---------
 ShareLock | t
(1 row)

如果表足够大，并且频繁地用于增删改，这似乎是很难接收的，因为修改进程将花费很长时间来等待锁释放。

在这种情况下，我们可以使用并行构建索引。

postgres=# create index concurrently on t(a);

此命令以共享更新独占模式锁定表，该模式允许读取和更新（仅禁止更改表结构，以及在此表上并发清空、分析或构建另一个索引）。

然而，也有另一面。首先，索引的构建速度将比通常慢，因为在表中完成了两次而不是一次索引的构建，而且还需要等待修改数据的并行事务完成。

第二，在并发构建索引期间，可能出现死锁或者破坏唯一性约束。然而，尽管索引不可用，索引也会被构建。这种索引必须被删除重建，在psql \d命令的输出中,不可用索引被标记为INVALID，下面的查询将返回这些索引的完整列表：

postgres=# select indexrelid::regclass index_name, indrelid::regclass table_name
from pg_index where not indisvalid;

 index_name | table_name
------------+------------
 t_a_idx    | t
(1 row)

参宿_七

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
PostgreSQL中的索引——1

本文翻译自：Indexes in PostgreSQL — 1 : Postgres Professional目录介绍索引索引引擎主要扫描技术索引扫描位图扫描顺序扫描NULL多字段索引表达式上的索引（函数索引）部分索引排序并行构建介绍这一系列的文章与PostgreSQL中的索引非常相关。任何的学科都可以从不同的角度考虑，我们将讨论一些让使用DBMS的应用开发人员感兴趣的事：哪些索引是可用的，为什么有这么多种，以及如何使用它们来提升查询速度。
复制链接

扫一扫