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