DWS 索引详解
GaussDB(DWS)索引详解
1. 前言
- 适用版本：【8.1.3及以上】
本文主要介绍GaussDB(DWS)中支持的索引类型和使用场景，并对部分索引结构进行讲解，揭开索引的神秘面纱，最后对一些索引问题进行阐述。
2. 索引简介
索引是一组映射，Key是我们要搜索的元素，可以是一个属性(单列索引)也可以是多个属性(联合索引)，value是key所在的位置：TID。索引的作用是加快我们对特定属性的搜索速度，不用每次都将整个表扫描一遍，类似于书的目录。下表列出了常见的索引类型
| 索引类别 | 描述 |
|---|---|
| B-tree | 行存默认使用的索引，综合性索引，特别适用于点查、主键 |
| Psort | 列存默认使用的索引，存储空间小，导入性能影响小，查询提升效果比较中庸 |
| Gin | 基于B-tree树结构的倒排索引，存储被索引字段的VALUE或VALUE的元素，主要适用于数组过滤、全文检索的场景 |
| Gist | 一个通用的索引接口，不同的类型支持不同检索方式，主要适用于位置搜索 |
| Hash | 存储的是被索引字段VALUE的哈希值，只支持等值查询，特别适用于字段VALUE非常长的场景(GaussDB(DWS)不支持) |
| 特殊类型 | 表达式索引、部分索引、唯一索引 |
2.1 常见索引介绍
2.1.1 btree
B-tree索引的行数据被组织成页面。叶子页面中的行数据，存放的是<key, TID>键值对。非叶子页面中存放的是<minvalue, childptr>键值对，childptr指向本页面的孩子页面（可以是叶子页面，也可是其它非叶子页面），minvalue是子页面中的最小值。
- B-tree是平衡树，有序存储索引KEY值和TID；
- 对于索引上的过滤条件，通过KEY快速找到对应的叶子节点，然后再通过TID找到实际记录；
- 索引中的数据以非递减的顺序存储（页之间以及页内都是这种顺序），同级的数据页由双向链表连接；
- 支持单列索引和复合（多列）索引，多列复合索引适用于多列组合查询，B-tree索引对于查询条件的顺序有要求；
- B-tree索引可以处理等值和范围查询；
- 索引页面不存储事务信息；
下图是一个整数列的索引btree结构
对于寻找数据n=49的情况，索引扫描。如图从root节点（4, 32,64）出发，根据从 32 ≤ 49 < 64 计算出子节点中的值范围，找到子节点（32,43,49），接下来，递归地重复相同的过程，直到我们到达一个叶节点（43,49)，从中可以获取所需的 TID。
可以看到上面有两个49，由于我们的数据存在重复值，且值可能存在不同的page中，所以上面读到叶节点（43,49）后，还继续往右读，找到另一个叶子节点（49,62）
2.1.2 psort
psort索引用于列存表的索引，它利用了列存表原有min\max稀疏索引的特性，通过局部排序，使得列存索引的效率大大提高。下面首先对列存及其min\max稀疏索引的原理进行简要介绍，再此基础上，进一步介绍psort索引的基本原理。
列存表的每一列会有一个CUDesc表 ，会记录每个 CU 的 min 和 max 值，这个min\max可以作为稀疏索引进行粗过滤， 但如果业务的数据模型较为离散，查询时通过 min 和 max 值去过滤 CU 会出现大量的 CU 误读取，例如每个 CU 的 min 和 max跨度都比较大时，可能导致所有CU都要去扫描，其查询效率接近全表扫描。针对此问题就引出了psort索引
Psort索引数据结构示意如下图所示：
基本原理：
在每批次插入数据时，取出某列的数据，排序之后，将该列数据存储在一个新的列存表记为CPsortIndexRel中，会存储每条数据和对应的tid信息。对改索引表有一个对应的CUDesc，会记录min/max，这里的CU相比原始的CU，每个批次的min/max不重叠，然后在查询的时候可以顺序扫描找到对应cu，进而在有序的cu内找到要过滤的数据。
例如对于查询 select * from … where c = 160
对索引表的Cudesc表进行顺序扫描，根据min/max能找到cu0_1 和cu1_1，接着对着两个cu进行查询，由于cu内的数据是有序的，可以采样二分法进行查询，最后在cu1_1中找到了160，找到其tid信息，就可以找到原始数据位置。
优点：
- 相比于之前的min\max稀疏索引而言，由于每个批次内的数据是有序的，因此利用psort索引的过滤效果大大提高，CU误读取的概率大大降低，因此点查询和小数据量查询的效率大大提高。
- 由于建立psort的过程中是对每个批次的数据分别排序，因此相比于Btree索引，排序的代价大大降低，每批次数据导入的效率较高。
缺点：
- 当批次数目较多时，利用psort误读取的可能性增大，效率下降，平均耗时增加。
- 在查找的过程中对索引的CUDesc表顺序扫描，因此效率远低于Btree的搜索。
2.1.3 gin
GIN是Generalized Inverted Index的缩写。就是所谓的倒排索引。GIN的索引结构是一个B-tree，它的每个节点是存储词素，每个词素指向多个tid，使用为list(posting list)或者b-tree(posting tree)存储这些tid，少的时候用list，多的时候用b-tree。适用于复合类型的元素查询，如array，text, json。
如图下面就是一个gin索引树，对于某些元素出现在多个数据行中，就会以list或者btree存储
create table ts(doc text, doc_tsv tsvector);
update ts set doc_tsv = to_tsvector(doc);
create index on ts using gin(doc_tsv);
下面输出的是ts表的doc分词情情况，第一列是doc的位置，最后一列是每个词对应该doc的index
下图对应着索引树示意图，整个索引结构为btree，例如对于farm这个词出现在多个位置，存储方式为btree
2.2 特殊类型
| 索引方式 | 描述 |
|---|---|
| 唯一索引 | 可用于约束索引属性值的唯一性，或者属性组合值的唯一性。如果一个表声明了唯一约束或者主键， 则GaussDB(DWS)自动在组成主键或唯一约束的字段上创建唯一索引（可能是多字段索引），以实现这些约束。 目前，GaussDB(DWS)只有B-Tree可以创建唯一索引。 |
| 多字段索引 | 一个索引可以定义在表中的多个属性上。目前，GaussDB(DWS)中的B-Tree支持多字段索引，且最多可在32个字段上创建索引。 |
| 部分索引 | 建立在一个表的子集上的索引，这种索引方式只包含满足条件表达式的元组。 |
| 表达式索引 | 索引建立在一个函数或者从表中一个或多个属性计算出来的表达式上。表达式索引只有在查询时使用与创建时相同的表达式才会起作用。只支持行存表 |
创建一个普通表：
DROP TABLE IF EXISTS customer_address;
CREATE TABLE customer_address
(
ca_address_sk INTEGER NOT NULL ,
ca_address_id CHARACTER(16) NOT NULL ,
ca_street_number CHARACTER(10) ,
ca_street_name CHARACTER varying(60) ,
ca_street_type CHARACTER(15) ,
ca_suite_number CHARACTER(10)
)
DISTRIBUTE BY HASH (ca_address_sk);
CREATE TABLE tpcds.customer_address_bak AS TABLE tpcds.customer_address;
2.2.1 唯一索引
可用于约束索引属性值的唯一性，或者属性组合值的唯一性。如果一个表声明了唯一约束或者主键，则GaussDB(DWS)自动在组成主键或唯一约束的字段上创建唯一索引（可能是多字段索引），以实现这些约束。目前，GaussDB(DWS)只有B-Tree可以创建唯一索引。
指定Unique唯一索引时候，对于NULL值有以下三种处理方式：
-
NULLS DISTINCT：NULL值互不相等，即NULL值可重复插入。
-
NULLS NOT DISTINCT：NULL值相等。若索引列全为NULL，则NULL值不可重复插入；部分索引列为NULL，只有非NULL值不相等，才可成功插入数据。
-
NULLS IGNORE：在等值比较时跳过NULL值。若索引列全为NULL，则NULL值可重复插入；部分索引列为NULL，只有非NULL值不相等，才可成功插入数据。
默认取值：该参数默认取值为空，即NULL值可重复插入。
三种处理方式具体的行为如下表所示：
| 字段控制 | 索引列全为NULL | 部分索引列为NULL |
|---|---|---|
| NULLS DISTINCT | 可重复插入 | 可重复插入 |
| NULLS NOT DISTINCT | 不可重复插入 | 非NULL值相等，不可插入；非NULL值不相等，则插入成功 |
| NULLS IGNORE | 可重复插入 | 非NULL值相等，不可插入；非NULL值不相等，则插入成功 |
与Mysql ,Oracle，Postgresql对NULL处理的区别：
- Oracle中NULL是可以重复插入的，对于联合索引，如果有列不为NULL且相等，则NULL的列会认为违背唯一约束，即上面的IGNORE选项，不可插入；
- Mysql中NULL是可以重复插入的，不违背唯一约束*，*对于联合索引则会认为不违背，可以重复插入的，即上面的DISTINCT选项；
- Postgresql中可以通过 [ NULLS [ NOT ] DISTINCT ]来指定NULL处理的行为，也和GaussDB(DWS)一样可以定义NULL是否是相等的，对于联合索引，只有所有的列都相同，则违背唯一约束。
假如对ca_address_id列有唯一性要求，可以创建唯一索引，插入数据会进行唯一性检查
CREATE UNIQUE INDEX index_address_id ON customer_address_bak(ca_address_id);
2.2.2 多字段索引
- 多字段索引一般不建议超过4列
- 不建议在超长字段上建立索引
假如用户需要经常查询表tpcds.customer_address_bak中ca_address_sk是5050，且ca_street_number小于1000的记录，使用以下命令进行查询。
SELECT ca_address_sk,ca_address_id FROM tpcds.customer_address_bak WHERE ca_address_sk = 5050 AND ca_street_number < 1000;
使用以下命令在字段ca_address_sk和ca_street_number上定义一个多字段索引。
CREATE INDEX more_column_index ON tpcds.customer_address_bak(ca_address_sk ,ca_street_number );
2.2.3 部分索引
如果只需要查询ca_address_sk为5050的记录，可以创建部分索引来提升查询效率。
CREATE INDEX part_index ON tpcds.customer_address_bak(ca_address_sk) WHERE ca_address_sk = 5050;
2.2.4 表达式索引
- 只支持行存表
假如经常需要查询ca_street_number小于1000的信息，执行如下命令进行查询。
SELECT * FROM tpcds.customer_address_bak WHERE trunc(ca_street_number) < 1000;
可以为上面的查询创建表达式索引
CREATE INDEX para_index ON tpcds.customer_address_bak (trunc(ca_street_number));
3. 索引的创建原则
索引可以提高数据的访问速度，但同时也增加了插入、更新和删除操作的处理时间。所以是否要为表增加索引，索引建立在哪些字段上，是创建索引前必须要考虑的问题。需要分析应用程序的业务处理、数据使用、经常被用作查询的条件或者被要求排序的字段来确定是否建立索引。
3.1 不同索引适用场景
- btree：B-tree索引使用一种类似于B+树的结构来存储数据的键值，通过这种结构能够快速的查找索引。btree适合支持比较查询以及查询范围。
- gin：GIN索引是倒排索引，可以处理包含多个键的值（比如数组）。
- gist：Gist索引适用于几何和地理等多维数据类型和集合数据类型。
- Psort：Psort索引。针对列存表进行局部排序索引。
行存表支持的索引类型：btree（行存表缺省值）、gin、gist。列存表支持的索引类型：Psort（列存表缺省值）、btree、gin。
说明：对于点查询场景，推荐建立btree索引。
3.2 索引列选择原则：
索引建立在数据库表中的某些列上。因此，在创建索引时，应该仔细考虑在哪些列上创建索引。
-
在经常需要搜索查询的列上创建索引，可以加快搜索的速度。
-
在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构。
-
在经常使用连接的列上创建索引，可以加快连接的速度。
-
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
-
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
-
在经常使用WHERE子句的列上创建索引，加快条件的判断速度。
说明：
- 索引创建成功后，系统会自动判断何时引用索引。当系统认为使用索引比顺序扫描更快时，就会使用索引。
- 索引创建成功后，必须和表保持同步以保证能够准确地找到新数据，这样就增加了数据操作的负荷。因此请定期删除无用的索引
4.分区索引
- 在分区表上创建唯一索引时，索引项中必须包含分布列和所有分区键。
- GaussDB(DWS)在分区表上创建索引时只支持本地（LOCAL）索引，不支持全局（GLOBAL）索引。
- 分区表上不支持并行创建索引、不支持创建部分索引、不支持NULL FIRST特性。
- 不支持单独删除分区索引，支持单独重建分区索引
CREATE TABLE customer_address
(
CA_ADDRESS_SK INTEGER NOT NULL,
CA_ADDRESS_ID CHAR(16) NOT NULL
)
DISTRIBUTE BY HASH(CA_ADDRESS_SK)
PARTITION BY RANGE(CA_ADDRESS_SK)
(
PARTITION p1 VALUES LESS THAN (3000),
PARTITION p2 VALUES LESS THAN (5000) ,
PARTITION p3 VALUES LESS THAN (MAXVALUE)
)
ENABLE ROW MOVEMENT;
insert into customer_address values(2000, 1);
insert into customer_address values(4000, 1);
insert into customer_address values(6000, 1);
-- 不加local关键字则会提示报错
test=# create index idx_01 on customer_address(CA_ADDRESS_SK);
ERROR: partitioned table does not support global index
HINT: please set behavior_compat_options = 'create_partition_local_index'to create local index by default.
指定local关键字创建索引，会为每个分区自动创建一个分区索引
create index idx_01 on customer_address(CA_ADDRESS_SK) local;
test=# select relname, parttype, parentid from pg_partition where parentid='idx_01'::regclass;
relname | parttype | parentid
----------------------+----------+------------
p3_ca_address_sk_idx | x | 2147483773
p2_ca_address_sk_idx | x | 2147483773
p1_ca_address_sk_idx | x | 2147483773
(3 rows)
test=# select oid, relname from pg_class where relname='idx_01';
oid | relname
------------+---------
2147483773 | idx_01
(1 row)
也可以指定分区索引名称，不采样自动创建的分区索引名称
CREATE INDEX idx_02 ON customer_address(CA_ADDRESS_SK) LOCAL
(
PARTITION CA_ADDRESS_SK_index1,
PARTITION CA_ADDRESS_SK_index2,
PARTITION CA_ADDRESS_SK_index3
) ;
test=# select relname, parttype,parentid from pg_partition where parentid='idx_02'::regclass;
relname | parttype | parentid
----------------------+----------+------------
ca_address_sk_index3 | x | 2147483785
ca_address_sk_index2 | x | 2147483785
ca_address_sk_index1 | x | 2147483785
删除分区后对应分区的所有索引都删除了
ALTER TABLE customer_address DROP PARTITION p1;
test=# select relname, parttype, parentid from pg_partition where parentid='idx_01'::regclass;
relname | parttype | parentid
----------------------+----------+------------
p3_ca_address_sk_idx | x | 2147483773
p2_ca_address_sk_idx | x | 2147483773
(2 rows)
test=# select relname, parttype, parentid from pg_partition where parentid='idx_02'::regclass;
relname | parttype | parentid
----------------------+----------+------------
ca_address_sk_index3 | x | 2147483785
ca_address_sk_index2 | x | 2147483785
执行查询
删除分区p1后，剪枝到分区跑,能走idx_02的索引
test=# set ENABLE_SEQSCAN=off;
SET
test=# explain select * from customer_address where CA_ADDRESS_SK < 2000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
id | operation | E-rows | E-width | E-costs
----+-------------------------------------------------------------------+--------+---------+---------
1 | -> Streaming (type: GATHER) | 1 | 21 | 34.27
2 | -> Partition Iterator | 1 | 21 | 28.27
3 | -> Partitioned Index Scan using idx_02 on customer_address | 1 | 21 | 28.27
Predicate Information (identified by plan id)
-------------------------------------------------------------
2 --Partition Iterator
Iterations: 1
3 --Partitioned Index Scan using idx_02 on customer_address
Index Cond: (ca_address_sk < 2000)
Partitions Selected by Static Prune: 1
(13 rows)
剪枝到2,3分区的时候，能走分区索引
test=# explain select * from customer_address where CA_ADDRESS_SK > 3000;
QUERY PLAN
------------------------------------------------------------------------------------------------
id | operation | E-rows | E-width | E-costs
----+------------------------------------------------------------+--------+---------+---------
1 | -> Streaming (type: GATHER) | 1 | 21 | 62.27
2 | -> Partition Iterator | 1 | 21 | 56.27
3 | -> Partitioned Bitmap Heap Scan on customer_address | 1 | 21 | 56.27
4 | -> Partitioned Bitmap Index Scan | 1 | 0 | 52.26
Predicate Information (identified by plan id)
------------------------------------------------------
2 --Partition Iterator
Iterations: 2
3 --Partitioned Bitmap Heap Scan on customer_address
Recheck Cond: (ca_address_sk > 3000)
Partitions Selected by Static Prune: 1..2
4 --Partitioned Bitmap Index Scan
Index Cond: (ca_address_sk > 3000)
(16 rows)
4. bitmap index scan
bitmap索引是一种在基础索引上的二级索引，是为了解决普通索引进行的随机IO效率低的问题。位图索引扫描是将普通索引扫描中的随机IO，尽量转换成顺序IO，降低执行计划的代价。它首先访问索引数据，过滤出符合提交的数据的索引信息(CTID)，然后根据CTID来进行聚合和排序，将要访问数据页面有序化，同时数据访问的随机IO也转换成顺序IO。对于多索引列的情况，可以对命中的bitmap做位运算，过滤出要去真正查询的page
如下图，对a和b列有建立索引，查询时会对建立page的bitmap， 对两列索引命中后的bitmap进行与运算，就能得到最后要扫描的page，且page是排序过的，会进行顺序扫描
CREATE TABLE t1(a INTEGER, b INTEGER) DISTRIBUTE BY HASH(a);
INSERT INTO t1 SELECT generate_series(1, 100), generate_series(1, 100) FROM (VALUES(0)) tmp;
CREATE INDEX ON t1(a);
CREATE INDEX ON t1(b);
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
下面查询是走的bitmap index scan,可以看到生成了一个BitmapAnd算子，对两列的bitmap做与运算，确定出要扫描的page
test=# EXPLAIN(COSTS OFF, NODES OFF)
SELECT /*+ SET(force_bitmapand ON) */ COUNT(*) FROM t1 WHERE a = 3 AND b = 5;
QUERY PLAN
-----------------------------------------------
id | operation
----+--------------------------------------
1 | -> Aggregate
2 | -> Streaming (type: GATHER)
3 | -> Aggregate
4 | -> Bitmap Heap Scan on t1
5 | -> BitmapAnd(6, 7)
6 | -> Bitmap Index Scan
7 | -> Bitmap Index Scan
Predicate Information (identified by plan id)
---------------------------------------------
4 --Bitmap Heap Scan on t1
Recheck Cond: ((b = 5) AND (a = 3))
6 --Bitmap Index Scan
Index Cond: (b = 5)
7 --Bitmap Index Scan
Index Cond: (a = 3)
5. 索引的可见性与可用性
许多索引都作为主键或唯一约束用于保证业务数据的准确性。但在业务执行过程中，索引可能会导致查询业务性能劣化。有时候就需要对索引设置为不可以用（UNUSABLE ）或者不可见（invisible）。
索引的可见性指的是该索引是否对CBO优化器可见，即CBO优化器在生成执行计划的时候是否考虑该索引,可以看作是索引的一个属性。如果一个索引设置为不可见。 默认情况下CBO优化器生成执行计划的时候，不再考虑该索引。与可见索引一样，执行DML操作时，数据库会相应维护索引的数据。
索引可用性是指改索引是否需要失效不再维护，正常情况下，索引都是可用的。当索引不可用(unusable)时, 执行后数据库将不再对被执行索引进行数据更新维护。想要被重新使用，需要重新rebuild索引，或者先drop再create。
- 设置为不可见：
ALTER INDEX … SET (invisible= on)语法，执行后，支持在保持索引数据更新的前提下，查询时使索引不可见。
- 设置为不可用：
ALTER INDEX … UNUSABLE，执行后，索引不可用，且不会更新
ALTER INDEX … REBUILD; 索引重新使用
下面举例来说明：
CREATE TABLE t1 (a int, b int) distribute by hash(a);
INSERT INTO t1 SELECT generate_series(1, 500), 1;
CREATE UNIQUE INDEX index_t1_a ON t1(a);
1.默认索引可见，查询的时候生成了index scan
test=# set enable_fast_query_shipping=off;
SET
test=# set ENABLE_SEQSCAN=off;
SET
test=# explain select * from t1 where a < 20;
QUERY PLAN
------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+-------------------------------+--------+----------+---------+---------
1 | -> Streaming (type: GATHER) | 20 | | 8 | 12.45
2 | -> Bitmap Heap Scan on t1 | 20 | 1MB | 8 | 6.45
3 | -> Bitmap Index Scan | 20 | 1MB | 0 | 4.33
Predicate Information (identified by plan id)
---------------------------------------------
2 --Bitmap Heap Scan on t1
Recheck Cond: (a < 20)
3 --Bitmap Index Scan
Index Cond: (a < 20)
2.对索引设置不可见，可以看到生成的计划就不走索引扫描了
test=# ALTER INDEX index_t1_a SET (invisible= on);
ALTER INDEX
test=# \d+ index_t1_a
Index "public.index_t1_a"
Column | Type | Definition | Storage
--------+---------+------------+---------
a | integer | a | plain
unique, btree, for table "public.t1"
Options: invisible=on
test=# explain select * from t1 where a < 20;
QUERY PLAN
--------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+------------------------------+--------+----------+---------+------------------
1 | -> Streaming (type: GATHER) | 20 | | 8 | 1000000000518.50
2 | -> Seq Scan on t1 | 20 | 1MB | 8 | 1000000000512.50
Predicate Information (identified by plan id)
---------------------------------------------
2 --Seq Scan on t1
Filter: (a < 20)
3.设置为不可用，也不走索引扫描了
test=# ALTER INDEX index_t1_a UNUSABLE;
ALTER INDEX
test=# explain select * from t1 where a < 20;
QUERY PLAN
--------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+------------------------------+--------+----------+---------+------------------
1 | -> Streaming (type: GATHER) | 20 | | 8 | 1000000000518.50
2 | -> Seq Scan on t1 | 20 | 1MB | 8 | 1000000000512.50
Predicate Information (identified by plan id)
---------------------------------------------
2 --Seq Scan on t1
Filter: (a < 20)
4.rebuild后又走索引扫描了
注意：如果失效后，插入了重复的数据，不会进行冲突检查，此后rebuild的时候会检查冲突
test=# ALTER INDEX index_t1_a REBUILD;
REINDEX
test=# explain select * from t1 where a < 20;
QUERY PLAN
------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+-------------------------------+--------+----------+---------+---------
1 | -> Streaming (type: GATHER) | 20 | | 8 | 14.45
2 | -> Bitmap Heap Scan on t1 | 20 | 1MB | 8 | 8.45
3 | -> Bitmap Index Scan | 20 | 1MB | 0 | 4.33
Predicate Information (identified by plan id)
---------------------------------------------
2 --Bitmap Heap Scan on t1
Recheck Cond: (a < 20)
3 --Bitmap Index Scan
Index Cond: (a < 20)
6. 索引的重建
数据库经过多次删除操作后，索引页面上的索引键将被删除，造成索引膨胀。例行重建索引，可有效的提高查询效率。
在以下几种情况下需要使用REINDEX重建索引：
-
索引崩溃，并且不再包含有效的数据。
-
索引变得“臃肿”，包含大量的空页或接近空页。
-
为索引更改了存储参数（例如填充因子），并且希望这个更改完全生效。
使用CONCURRENTLY选项创建索引失败，留下了一个“非法”索引。
-
对于B-tree索引，例行重建索引可有效的提高查询效率。
- 如果数据发生大量删除后，索引页面上的索引键将被删除，导致索引页面数量的减少，造成索引膨胀。重建索引可回收浪费的空间。
- 新建的索引中逻辑结构相邻的页面，通常在物理结构中也是相邻的，所以一个新建的索引比更新了多次的索引访问速度要快。
-
对于非B-tree索引，不建议例行重建。
重建索引有以下两种方式：
-
先删除索引（DROP INDEX），再创建索引（CREATE INDEX）。
在删除索引过程中，会在父表上增加一个短暂的排他锁，阻止相关读写操作。在创建索引过程中，会锁住写操作但是不会锁住读操作，此时读操作只能使用顺序扫描。
-
使用REINDEX语句重建索引。
- 使用REINDEX TABLE语句重建索引，会在重建过程中增加排他锁，阻止相关读写操作。
- 使用REINDEX INTERNAL TABLE语句重建desc表（包括）的索引，会在重建过程中增加排他锁，阻止相关读写操作。
假定在导入表“areaS”上的“area_id”字段上存在普通索引“areaS_idx”。重建索引有以下两种方式：
-
先删除索引（DROP INDEX），再创建索引（CREATE INDEX）
-
删除索引。
DROP INDEX areaS_idx; -
创建索引。
CREATE INDEX areaS_idx ON areaS (area_id);
-
-
使用REINDEX重建索引。
-
使用REINDEX TABLE语句重建索引。
REINDEX TABLE areaS; -
使用REINDEX INTERNAL TABLE重建desc表（包括）的索引。
REINDEX INTERNAL TABLE areaS;
-
-
重建分区索引：
- 使用REINDEX TABLE 可以重建分区上所有的索引
REINDEX TABLE customer_address PARTITION P1;- 使用REINDEX INDEX指定分区索引名称，可以重建指定分区索引
REINDEX INDEX customer_address_p1_idx;
7. 行存和列存表btree区别
列存表 B-tree 索引结构与行存表并无大的差别，列存表的 B-tree 索引在存储引擎侧是以行存形式组织。对于一般用于应对大数据批量分析性负载的列存储引擎来说，B-tree 索引有助于帮助列存储提升自身的点查效率，更好地适应混合负载。
行存储的 B-tree 索引数据中存储的是 **key→ctid (键→行号)的映射，在列存储的场景下，这个映射依旧为 key→ctid。但列存储的结构并不能像行存储一样，通过ctid中的块号(block number)和偏移量(offset)**直接找到此行数据在数据文件页面中的位置。列存储ctid中记录的是(cu_id，offset)，要通过 CUDesc 结构来进行查找。
在基于 B-tree 索引的扫描中，从索引中拿到 ctid 后，需要在对应的 CUDesc表中，根据 CUDesc 在 cu_id 列的索引找到对应的 CUDesc 记录，并由此打开对应的 CU 文件，根据偏移量找到数据。
此操作涉及大量的存储层性能开销，因此列存储的 B树索引，与列存储的其他操作一样，统一都会批量操作，会根据 B-tree 索引找到 ctid 的集合，然后对此集合进行排序，再批量地对排序后的 ctid 进行 CU 文件级别的查找与操作。这样可以做到顺序单调地进行索引遍历，大大减少了反复操作文件带来的 CPU 以及IO 开销。
8.索引与模糊查询
模糊查询的场景中，对于前缀模糊匹配，可以走索引扫描，对于中缀和后缀查询，我们无法走索引扫描的，这主要是由于后两种无法利用B-treee索引的顺序性，导致无法按照前缀匹配搜索。但对于后缀模糊查询，可以利用创建reverse表达式方式来走索引。对于中缀模糊查询，需要建立其他类型的索引来支持高效的查询。下面通过具体的例子说明。
创建数据表：
下面建表通过对车牌和身份证ID模糊查询来举例
drop table test_id;
create table test_id(id text, car_id text);
insert into test_id select
lpad((random()*99)::int::text, 2, '0') ||
lpad((random()*99)::int::text, 2, '0') ||
lpad((random()*99)::int::text, 2, '0') ||
to_char('1990-01-01'::date + random()*10000, 'yyyymmdd') ||
lpad((random()*99)::int::text, 2, '0') ||
random()::int ||
(case when random()*10 >9 then 'X' else (random()*9)::int::text end ) as id,
ARRAY['京', '津', '冀', '晋', '蒙', '辽', '吉', '黑', '沪', '苏', '浙', '皖', '闽', '赣', '鲁', '豫', '鄂', '湘', '粤', '桂', '琼', '渝', '川', '黔', '滇', '藏', '陕', '甘', '青', '宁', '新', '台', '港', '澳'][idx%34] ||
chr(trunc(random()*26)+65) ||
'-' ||
chr(trunc(random()*26)+65) ||
chr(trunc(random()*26)+65) ||
trunc(random()*10) ||
trunc(random()*10) ||
trunc(random()*10)
AS car_id
FROM generate_series(1, 1000000) g(idx);
CREATE TABLE test_id2(LIKE test_id INCLUDING all);
insert into test_id2 select * from test_id;
8.1 基于B-tree索引的模糊查询
建立索引前:
前缀查询耗时211ms
test=# SELECT * from test_id2 where id like '421123%';
id | car_id
--------------------+-----------
42112319920731511X | 台D-CX580
421123201301198606 | 赣C-TF147
(2 rows)
Time: 211.496 ms
test=# explain SELECT * from test_id2 where id like '421123%';
QUERY PLAN
------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+------------------------------+--------+----------+---------+----------
1 | -> Streaming (type: GATHER) | 100 | | 51 | 10078.00
2 | -> Seq Scan on test_id2 | 100 | 1MB | 51 | 10072.00
Predicate Information (identified by plan id)
---------------------------------------------
2 --Seq Scan on test_id2
Filter: (id ~~ '421123%'::text)
建立b-tree索引后
create index idx_id1 on test_id2(id collate "C");
注意：这里需要指定collate “C”，否则不走索引
前缀模糊查询，走了索引扫描7.836ms，性能提升明显
test=# SELECT * from test_id2 where id like '421123%';
id | car_id
--------------------+-----------
42112319920731511X | 台D-CX580
421123201301198606 | 赣C-TF147
(2 rows)
Time: 7.836 ms
test=# explain SELECT * from test_id2 where id like '421123%';
QUERY PLAN
--------------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+---------------------------------------------+--------+----------+---------+---------
1 | -> Streaming (type: GATHER) | 100 | | 51 | 14.27
2 | -> Index Scan using idx_id1 on test_id2 | 100 | 1MB | 51 | 8.27
Predicate Information (identified by plan id)
----------------------------------------------------------------------
2 --Index Scan using idx_id1 on test_id2
Index Cond: ((id >= '421123'::text) AND (id < '421124'::text))
Filter: (id ~~ '421123%'::text)
后缀模糊查询，可以看到没有走索引，耗时317ms
-- 后缀查询，可以看到没有走索引
test=# SELECT * from test_id2 where id like '%06250013';
id | car_id
--------------------+-----------
437193201106250013 | 粤L-MB616
211593199306250013 | 晋Z-VP216
468667199406250013 | 湘I-JL575
931367201406250013 | 黑Y-AP521
(4 rows)
Time: 317.971 ms
test=# explain SELECT * from test_id2 where id like '%06250013';
QUERY PLAN
------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+------------------------------+--------+----------+---------+----------
1 | -> Streaming (type: GATHER) | 100 | | 51 | 10078.00
2 | -> Seq Scan on test_id2 | 100 | 1MB | 51 | 10072.00
Predicate Information (identified by plan id)
---------------------------------------------
2 --Seq Scan on test_id2
Filter: (id ~~ '%06250013'::text)
explain SELECT count(1) from test_id2 where id like '%666%';
explain SELECT count(1) from test_id2 where id like '%12';
建立reverse表达式索引
create index idx_id2 on test_id2(reverse(id) collate "C");
后缀模糊查询，现在可以走reverse表达式索引了，耗时10ms
test=# SELECT * from test_id2 where reverse(id) like reverse('%06250013');
id | car_id
--------------------+-----------
437193201106250013 | 粤L-MB616
211593199306250013 | 晋Z-VP216
468667199406250013 | 湘I-JL575
931367201406250013 | 黑Y-AP521
(4 rows)
Time: 10.382 ms
test=# explain SELECT * from test_id2 where reverse(id) like reverse('%06250013');
QUERY PLAN
----------------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+-------------------------------------+--------+----------+---------+---------
1 | -> Streaming (type: GATHER) | 5000 | | 51 | 3869.52
2 | -> Bitmap Heap Scan on test_id2 | 5000 | 1MB | 51 | 3635.15
3 | -> Bitmap Index Scan | 5000 | 1MB | 0 | 29.26
Predicate Information (identified by plan id)
--------------------------------------------------------------------------------------------
2 --Bitmap Heap Scan on test_id2
Filter: (reverse(id) ~~ '31005260%'::text)
3 --Bitmap Index Scan
Index Cond: ((reverse(id) >= '31005260'::text) AND (reverse(id) < '31005261'::text))
8.2 基于ngram分词的gin索引
对于不能走b-tree索引的场景下，我们可以建立gin索引，通过分词建立索引，去提高查询效率。这边介绍Gauss(DWS)中基于ngram的分词来建立gin索引，它适合中文或者短字符的模糊查询，如车牌号 身份证号等模糊查询。
不建立索引查询
-- 查询包含19930510，以及以0012结尾的身份证
test=# SELECT * from test_id2 where id like '%19930510%0012';
id | car_id
--------------------+-----------
688742199305100012 | 宁I-BE306
(1 row)
Time: 327.087 ms
test=# explain SELECT * from test_id2 where id like '%19930510%0012';
QUERY PLAN
------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+------------------------------+--------+----------+---------+----------
1 | -> Streaming (type: GATHER) | 100 | | 29 | 10076.00
2 | -> Seq Scan on test_id2 | 100 | 1MB | 29 | 10070.00
Predicate Information (identified by plan id)
----------------------------------------------
2 --Seq Scan on test_id2
Filter: (id ~~ '%19930510%0012'::text)
-- 查询陕A开头包含666的车牌号
test=# SELECT * from test_id2 where car_id like '陕A%666';
id | car_id
--------------------+-----------
696477200512279501 | 陕A-OV666
(1 row)
Time: 221.868 ms
test=# explain SELECT * from test_id2 where car_id like '陕A%666';
QUERY PLAN
------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+------------------------------+--------+----------+---------+----------
1 | -> Streaming (type: GATHER) | 100 | | 29 | 10076.00
2 | -> Seq Scan on test_id2 | 100 | 1MB | 29 | 10070.00
Predicate Information (identified by plan id)
---------------------------------------------
2 --Seq Scan on test_id2
Filter: (car_id ~~ '陕A%666'::text)
建立gin索引
对id创建gin索引，这里配合查询习惯，创建分词器ngram1，复制子ngram，采用的是4字符大小的分词
CREATE TEXT SEARCH CONFIGURATION ngram1 (parser=ngram) WITH (gram_size = 4, grapsymbol_ignore = false);
ALTER text search configuration ngram1 ADD MAPPING FOR zh_words, en_word, numeric, alnum, grapsymbol, multisymbol WITH simple;
create index idx_id1 on test_id2 using gin(to_tsvector('ngram1',id));
-- 查询包含19930510，以及以0012结尾的身份证
test=# select * from test_id2 where to_tsvector('ngram1', id) @@ to_tsquery('ngram1', '1993 & 0510 & 0012') and id like '%19930510%0012';
id | car_id
--------------------+-----------
688742199305100012 | 宁I-BE306
(1 row)
Time: 20.532 ms
test=# explain select * from test_id2 where to_tsvector('ngram1', id) @@ to_tsquery('ngram1', '1993 & 0510 & 0012') and id like '%19930510%00
12';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+-------------------------------------+--------+----------+---------+---------
1 | -> Streaming (type: GATHER) | 1 | | 29 | 26.02
2 | -> Bitmap Heap Scan on test_id2 | 1 | 1MB | 29 | 20.02
3 | -> Bitmap Index Scan | 1 | 1MB | 0 | 16.00
Predicate Information (identified by plan id)
---------------------------------------------------------------------------------------------------------
2 --Bitmap Heap Scan on test_id2
Recheck Cond: (to_tsvector('ngram1'::regconfig, id) @@ '''1993'' & ''0510'' & ''0012'''::tsquery)
Filter: (id ~~ '%19930510%0012'::text)
3 --Bitmap Index Scan
Index Cond: (to_tsvector('ngram1'::regconfig, id) @@ '''1993'' & ''0510'' & ''0012'''::tsquery)
对car_id创建gin索引，这里配合查询习惯，采用默认的ngram 2字符的分词
create index idx_car_id1 on test_id2 using gin(to_tsvector('ngram',car_id));
select * from test_id2test=# select * from test_id2 where to_tsvector('ngram', car_id) @@ to_tsquery('ngram', '陕A & 66') and car_id like '陕A%666';
id | car_id
--------------------+-----------
696477200512279501 | 陕A-OV666
(1 row)
Time: 14.751 ms
test=# explain select * from test_id2 where to_tsvector('ngram', car_id) @@ to_tsquery('ngram', '陕A & 66') and car_id like '陕A%666';
QUERY PLAN
-------------------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+-------------------------------------+--------+----------+---------+---------
1 | -> Streaming (type: GATHER) | 19 | | 29 | 64.29
2 | -> Bitmap Heap Scan on test_id2 | 19 | 1MB | 29 | 58.29
3 | -> Bitmap Index Scan | 25 | 1MB | 0 | 12.10
Predicate Information (identified by plan id)
-----------------------------------------------------------------------------------------------
2 --Bitmap Heap Scan on test_id2
Recheck Cond: (to_tsvector('ngram'::regconfig, car_id) @@ '''陕a'' & ''66'''::tsquery)
Filter: (car_id ~~ '陕A%666'::text)
3 --Bitmap Index Scan
Index Cond: (to_tsvector('ngram'::regconfig, car_id) @@ '''陕a'' & ''66'''::tsquery)where to_tsvector('ngram', car_id) @@ to_tsquery('ngram', '陕A & 66') and car_id like '陕A%666';
可以看到上面，查询使用to_tsvector和to_tsquery，使其走了索引扫描，两个模糊查询性能快了16倍。
对于使用gin索引查询的时候一般召回率比较少会有较大的性能提升，具体查询类似上面的上就是先用全文检索进行粗过率，再做like精细过滤，
8.3 理解ngram 、gin索引、 模糊查询的关系
ngram：是分词的一个解析器，他可以指定词的大小，一般适用于中文，默认的分词配置是English，按照英文单词分词的，我们中文肯定是要by汉字的，不能按照英文的。同理英文的不能按照ngram来分，否则失去了一个词的意义。
gin索引：是对某列进行创建倒排索引，生成一个数据结构，便于加速找到要找到的数据，它的类型必须是tsvector分词类型，是根据词素创建的索引。
模糊查询：基本的前缀，后缀，中缀查询，以及正则表达式，去查询某个列符号某些条件的数据，可以走索引扫描
8.冗余索引的检查
有时候重复建了索引，重复的索引效果其实是一样的，但是会消耗存储空间，因此有必要去检查冗余索引，判断是否应该删除。包括下列情况：
1.单字段重复建立索引，
CREATE INDEX idx1 ON t1 (a);
CREATE INDEX idx2 ON t1 (a);
2.多个字段索引，只是顺序不一样
CREATE INDEX idx1 ON t1 (a, b);
CREATE INDEX idx2 ON t1 (b, a);
3.多字段的索引包含了建立索引的单字段
CREATE INDEX idx1 ON t1 (a);
CREATE INDEX idx2 ON t1 (a, b);
下面的脚本可以查找冗余索引：
- optimizable policy为duplicate的检查项表明两个索引字段和字段顺序完全一致，建议直接删除optimizable index指定的索引；
- optimizable policy为redundancy检查项表明optimizable index指定的索引的索引列刚好是base index的索引列的前面字段，建议直接删除optimizable index指定的索引；
- optimizable policy为optimizable检查项，表明optimizable index和base index这两个索引的索引列完全重复，但是索引列的顺序不一致，这种场景需要人工介入分析是否可以优化
WITH info AS(
SELECT
quote_ident(n.nspname) || '.' || quote_ident(c.relname) AS tablename,
pgroup AS nodegroup,
x.indrelid AS indrelid,
x.indexrelid AS indexrelid,
indisunique,
indisprimary,
indnatts,
indkey,
indexprs
FROM pg_index x
INNER JOIN pg_class c ON c.oid = x.indrelid
INNER JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pgxc_class xc ON xc.pcrelid = c.oid
WHERE c.relkind = 'r' AND c.oid >= 16384 AND (c.reloptions IS NULL OR c.reloptions::text NOT LIKE '%internal_mask%')
AND i.relkind = 'i' AND i.oid >= 16384
AND x.indpred IS NULL
),
base AS(
SELECT
tablename,
nodegroup,
i.indrelid,
i.indexrelid baseidx,
i.indisunique AS base_unique,
i.indisprimary AS base_primary,
x.indexrelid AS optidx,
x.indisunique AS opt_unique,
x.indisprimary AS opt_primary,
CASE WHEN opt_primary > base_primary OR opt_unique > base_unique THEN true ELSE false END AS swap,
CASE WHEN i.indkey = x.indkey AND coalesce(pg_get_expr(i.indexprs, i.indrelid), 'NULL') = coalesce(pg_get_expr(x.indexprs, x.indrelid), 'NULL') THEN 'duplicate'::text
WHEN x.indexprs IS NOT NULL OR i.indexprs IS NOT NULL THEN NULL::text
WHEN strpos(i.indkey::text, x.indkey::text||' ') = 1 OR strpos(x.indkey::text, i.indkey::text||' ') = 1 THEN 'redundancy'::text
WHEN i.indkey @> x.indkey AND x.indkey @> i.indkey THEN 'optimizable'::text
ELSE NULL
END AS optpolicy
FROM info i
INNER JOIN pg_index x ON (i.indrelid = x.indrelid AND i.indexrelid > x.indexrelid)
WHERE x.indpred IS NULL AND optpolicy IS NOT NULL
),
tmp AS(
SELECT
tablename,
indrelid,
nodegroup,
CASE WHEN swap THEN optidx ELSE baseidx END AS base_idx,
CASE WHEN swap THEN opt_primary ELSE base_primary END AS base_primary,
CASE WHEN swap THEN opt_unique ELSE base_unique END AS base_unique,
CASE WHEN swap THEN baseidx ELSE optidx END AS opt_idx,
CASE WHEN swap THEN base_primary ELSE opt_primary END AS opt_primary,
CASE WHEN swap THEN base_unique ELSE opt_unique END AS opt_unique,
optpolicy
FROM base
)
SELECT
tablename,
nodegroup,
base_idx::regclass::text AS base_index,
base_primary,
base_unique,
substring(pg_get_indexdef(base_idx) from 'USING .+\)') AS base_idxdef,
opt_idx::regclass::text AS opt_index,
opt_primary,
opt_unique,
substring(pg_get_indexdef(opt_idx) from 'USING .+\)') AS opt_idxdef,
optpolicy,
pg_get_tabledef(indrelid)
FROM tmp
ORDER BY 1, 2, 3;
场景举例：
CREATE TABLE t1 (a int, b int);
CREATE INDEX idx1 ON t1 (a);
CREATE INDEX idx2 ON t1 (a);
CREATE INDEX idx3 ON t1 (a,b);
CREATE INDEX idx4 ON t1 (b,a);
可以看到idx2和idx完全一样，重复了，可以删掉，idx1包含在了idx3也可以删掉，idx3和idx4顺序不一样，可以看哪种索引效率更高进行选择优化。
8. 总结
本文主要介绍dws中支持的索引类型、使用场景以及创建原则。索引类型比较多，针对不同的场景选择不同的索引才能发挥的价值。同时索引也是双刃剑，索引页面占用额外空间，导致一定的磁盘膨胀，需要进行清理重建，而且索引扫描性能并不总是比顺序扫描性能更好，当不好的时候可以调整索引的可见性或者可用性。
9. 参考文档
- 点赞
- 收藏
- 关注作者
评论（0）