进阶数据库系列（十二）：PostgreSQL 索引技术详解

前面介绍了 PostgreSQL 数据类型和运算符、常用函数、锁操作、执行计划、视图与触发器、存储过程相关的知识点，今天将为大家介绍 PostgreSQL 索引 相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！

概述

索引主要被用来提升数据库性能，不当的使用会导致性能变差。 PostgreSQL 提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了一种不同的算法来适应不同类型的查询。默认情况下，CREATE INDEX 命令创建适合于大部分情况的 B-tree 索引。

• B-树（默认）：B-树是一个自平衡树（self-balancing tree），按照顺序存储数据，支持对数时间复杂度（O(logN)）的搜索、插入、删除和顺序访问。

• 哈希：哈希索引（Hash index）只能用于简单的等值查找（=），也就是说索引字段被用于等号条件判断。因为对数据进行哈希运算之后不再保留原来的大小关系。

• GiST：GiST 代表通用搜索树（Generalized Search Tree），GiST 索引单个索引类型，而是一种支持不同索引策略的框架。GiST 索引常见的用途包括几何数据的索引和全文搜索。

• SP-GiST：SP-GiST 代表空间分区 GiST，主要用于 GIS、多媒体、电话路由以及 IP 路由等数据的索引。与 GiST 类似， SP-GiST 也支持“最近邻”搜索。

• GIN：GIN 代表广义倒排索引（generalized inverted indexes），主要用于单个字段中包含多个值的数据，例如 hstore、 array、 jsonb 以及 range 数据类型。一个倒排索引为每个元素值都创建一个单独的索引项，可以有效地查询某个特定元素值是否存在。Google、百度这种搜索引擎利用的就是倒排索引。

• BRIN：BRIN 代表块区间索引（block range indexes），存储了连续物理范围区间内的数据摘要信息。BRIN 也相比 B-树索引要小很多，维护也更容易。对于不进行水平分区就无法使用 B-树索引的超大型表，可以考虑 BRIN。

在使用上，除了常见的单列索引，还有多列索引、唯一索引、表达式索引、部分索引、覆盖索引等。更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

• 多列索引：目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引，最多可以指定32个列（该限制可以在源代码文件 pg_config_manual.h 中修改，但是修改后需要重新编译PostgreSQL）。

• 唯一索引：目前，只有 B-tree 能够被声明为唯一。

• 表达式索引：从表的一列或多列计算而来的一个函数或者标量表达式。索引表达式的维护代价较为昂贵，因为在每一个行被插入或更新时都得为它重新计算相应的表达式。然而，索引表达式在进行索引搜索时却不需要重新计算，因为它们的结果已经被存储在索引中了。

• 部分索引：一个部分索引是建立在表的一个子集上，而该子集则由一个条件表达式（被称为部分索引的谓词）定义。而索引中只包含那些符合该谓词的表行的项。使用部分索引的一个主要原因是避免索引公值（查询结果行在一个表中占比超过一定百分比的值不会使用索引）。

• 覆盖索引：目前，B-树索引总是支持只用索引的扫描。GiST 和 SP-GiST 索引只对某些操作符类支持只用索引的扫描。其他索引类型不支持这种扫描。仅访问索引就可获取查询所需的全部数据，无需回表（Index-Only Scan）。

语法

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

UNIQUE：唯一索引，在索引被创建时（如果数据已经存在）或者加入数据时检查重复值。

CONCURRENTLY：在构建索引时不会取得任何会阻止该表上并发插入、更新或者删除的锁。而标准的索引构建将会把表锁住以阻止对表的写（但不阻塞读），这种锁定会持续到索引创建完毕。

IF NOT EXISTS：如果一个同名关系已经存在则不要抛出错误。

INCLUDE：指定一个列的列表，其中的列将被包括在索引中作为非键列。不能作为索引扫描的条件，主要作用是相关数据索存储在索引中，访问时无需访问该索引的基表。当前，有B-树和GiST索引访问方法支持这一特性。

name：要创建的索引名称。这里不能包括模式名，因为索引总是被创建在其基表所在的模式中。如果索引名称被省略，PostgreSQL 将基于基表名称和被索引列名称选择一个合适的名称。

ONLY：如果该表是分区表，指示不要在分区上递归创建索引。默认会递归创建索引。

table_name：要被索引的表的名称（可以被模式限定）。

method：要使用的索引方法的名称。可以选择 btree、hash、 gist、spgist、gin以及brin。默认方法是 btree。

column_name：一个表列的名称。

expression：一个基于一个或者更多个表列的表达式。如语法中所示，表达式通常必须被写在圆括号中。不过，如果该表达式是一个函数调用的形式，圆括号可以被省略。

collation：要用于该索引的排序规则的名称。

opclass：一个操作符类的名称。

opclass_parameter：运算符类参数的名称。

ASC：指定上升排序（默认）。

DESC：指定下降排序。

NULLS FIRST：指定把空值排序在非空值前面。在指定DESC时，这是默认行为。

NULLS LAST：指定把空值排序在非空值后面。在没有指定DESC时，这是默认行为。

storage_parameter：索引方法相关的存储参数的名称。可选的WITH子句为索引指定存储参数。每一种 索引方法都有自己的存储参数集合。

B-树、哈希、GiST以及SP-GiST索引方法都接受这个参数：

fillfactor (integer):索引的填充因子是一个百分数，它决定索引方法将尝试填充索引页面的充满程度。对于B-树，在初始的索引构建过程中，叶子页面会被填充至该百分数，当在索引右端扩展索引（增加新的最大键值）时也会这样处理。如果页面后来被完全填满，它们就会被分裂，导致索引的效率逐渐退化。B-树使用了默认的填充因子 90，但是也可以选择为 10 到 100 的任何整数值。如果表是静态的，那么填充因子 100 是最好的，因为它可以让索引的物理尺寸最小化。但是对于更新负荷很重的表，较小的填充因子有利于最小化对页面分裂的需求。其他索引方法以不同但是大致类似的方式使用填充因子，不同方法的默认填充因子也不相同。

deduplicate_items (boolean)&#