PostgreSQL 索引管理

前言

索引是关系型数据库离不开的话题，它是数据库中一种快速查询数据的方法。在 SQL 优化中，创建高性能的索引非常重要，本篇文章将对比 MySQL 介绍 PostgreSQL 的索引管理。

1. 主键

1.1 MySQL innodb 中的主键

MySQL 中的主键也叫聚簇索引，按照主键字段组织 B+ tree 叶子节点存储的是完整的行数据，就是说 MySQL 中的主键索引存储了一张表完整的数据。

那如果用户没有指定主键会如何呢？因为 MySQL Innodb 引擎很多内部机制都会用到主键，所以即使用户没有指定主键，引擎内部会使用隐藏 row_id 作为主键，用户不可见。

PS：MySQL 8.0.30 版本推出 Generated Invisible Primary Key 特性，感兴趣可以看 这篇文章。

1.2 PostgreSQL 中的主键

在 PostgreSQL 中数据和索引是分开存储的，非聚簇索引，所有的索引都是辅助索引，主键在 PostgreSQL 中相当于一个唯一 not null 的约束。下方是官方文档对主键的描述：

Relational database theory dictates that every table must have a primary key. This rule is not enforced by PostgreSQL, but it is usually best to follow it.
关系型数据库理论上都会要求每一张表都要有一个主键，但 PostgreSQL 并未强制要求这一点，但最好遵循它。

2. PG 索引类型

2.1 B-tree 索引

该类型索引是我们最常用的索引，按照顺序存储数据，支持对数时间复杂度（O(logN)）的搜索、插入、删除和顺序访问。功能上和 MySQL innodb 引擎相同，支持 等值、范围查询、NOT NULL 等 都可以使用 B-tree 索引，当然也可以优化排序。

除此之外 PostgreSQL 还给辅助索引提供 INCLUDE 的功能，来规避 “回表” 查询。下方查询使用 x = key 条件搜索，需要查 y 字段，如果给 x 字段创建单列索引的话，需要再访问一次主数据区，相当于 MySQL 中 “回表” 查询的概念，MySQL 中可以创建复合索引把两个字段都放到索引列中。

SELECT y FROM tab WHERE x = 'key';

PostgreSQL 除了也可以那样做外，还可以使用 INCLUDE 语法，将需要查询的列包含在索引树的叶子节点中，但是它不参与索引树的组织排序，单纯避免回表，对此字段额外存储，所以 INCLUDE 的语法使用场景比较苛刻。

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

创建 B-tree 辅助索引，不需要额外指定关键字：

CREATE INDEX index_name ON table_name(column_name);

除此之外在创建索引时也可以指定索引 升/降 排序：

CREATE INDEX index_name ON table_name(column_name desc);

如果索引字段包含 NULL 值，也可以指定 NULL 值排在非空前面。FIRST 还是非空后面 LAST：

CREATE INDEX index_name ON table_name(column_name desc NULL FIRST);

2.2 HASH 索引

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

创建哈希索引需要使用 HASH 关键字：

CREATE INDEX index_name ON table_name USING HASH (column_name);

2.2 GiST 索引

GiST 索引并不是一种单独的索引，而是可以用于实现很多不同索引策略的基础设施。相应地，可以使用一个GiST索引的特定操作符根据索引策略（操作符类）而变化。

2.3 SP-GiST 索引

SP-GiST是 “Space Partitioned GiST” 的缩写，即空间分区 GiST 索引。它是从 PostgreSQL9.2 版本开始提供的一种新索引类型，主要是通过一些新的索引算法来提高 GiST 索引在某种情况下的性能。

2.4 GIN 索引

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

2.5 BRIN 索引

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

3. 索引管理语法

这里只讨论 B-tree 类型的索引维护。

3.1 创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name [ASC | DESC] [NULLS FIRST | NULLS LAST]);

3.2 多列索引

PostgreSQL 支持创建多列索引，最大可指定 32 个字段，MySQL 最多可指定 16 个字段。

CREATE [UNIQUE] INDEX index_name ON table_name
[USING method]
(column1 [ASC | DESC] [NULLS FIRST | NULLS LAST], ...);

3.3 函数索引

例如，一种进行大小写不敏感比较的常用方法是使用 lower 函数：

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

这种查询可以利用一个建立在 lower(col1) 函数结果之上的索引：

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

3.4 部分索引

PostgreSQL 可以给表中的部分数据创建索引，从而减少索引占用空间的大小，下面是一个例子：

-- 对于订单表 orders，绝大部的订单都处于完成状态
create table orders(order_id int primary key, order_ts timestamp, finished boolean);

-- 我们只需要针对未完成的订单进行查询跟踪，可以创建一个部分索引
create index orders_unfinished_index
on orders (order_id) where finished is not true;

3.5 覆盖索引

在之前 2.1 节有介绍，可以避免 “回表” 查询：

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

3.6 查看索引

select * from pg_indexes where tablename = 'pgbench_accounts';

-[ RECORD 1 ]--------------------------------------------------------------------------------------
schemaname | public
tablename  | pgbench_accounts
indexname  | pgbench_accounts_pkey
tablespace | 
indexdef   | CREATE UNIQUE INDEX pgbench_accounts_pkey ON public.pgbench_accounts USING btree (aid)

也可以使用 \d 表名，也包含索引信息。

3.7 重命名索引

ALTER INDEX index_name RENAME TO new_name;

3.8 重建索引

ALTER INDEX index_name REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM }  index_name;

3.9 删除索引

DROP INDEX index_name [ CASCADE | RESTRICT ];

CASCADE 表示级联删除其他依赖该索引的对象；RESTRICT 表示如果存在依赖于该索引的对象，将会拒绝删除操作。默认为 RESTRICT。

后记

Reference：

• PostgreSQL b-tree 索引分析
• PostgreSQL Constraints
• https://www.postgresql.org/docs/current/indexes-types.html