openGauss

开源数据库

openGauss社区官网

开源社区

openGauss的索引组织表

贾军锋2020-12-25openGauss核心技术

openGauss 的索引组织表

概述

今天有位小伙伴问我，Oracle 数据库可以通过索引组织表(IOT)将数据按照主键排序存储，有序的数据存储可以有效提高数据库缓冲区的命中率，减少 SQL 查询的 IO 消耗，提升查询效率。而 openGauss 的建表语句中并没有看到索引组织表的相关语法。openGauss 目前是不是不支持索引组织表？

我觉得暂时是这样的(说不定后期版本会新添加)，在 openGauss 的官方文档中有关建表操作中确实没有看到对索引组织表的描述，但是与索引组织表效果类似的操作确是可以看到，那就是 cluster 操作，个人认为这个功能与索引组织表的主要功能类似：数据按照索引顺序进行有序存储。

使用 cluster 操作可以根据表的索引对数据进行聚簇排序，将数据基于索引信息进行物理存储。但在 openGauss 中，这种聚簇排序操作是一次性的，即：当表被更新之后，更改的内容不会自动被聚簇排序后存储。也就是说，系统不会试图按照索引顺序对新的存储内容及更新记录进行重新聚簇排序存储。

当一个表聚簇(CLUSTER 操作)之后，openGauss 会记录在哪个索引上建立了聚簇。用户可以通过CLUSTER操作在之前的同一个索引的表上重新聚簇排序，也可以用ALTER TABLE CLUSTER或SET WITHOUT CLUSTER形式来设置索引来用于后续的聚簇操作或清除任何之前的设置。

表的聚簇操作(CLUSTER)大致分为以下几步：

检查堆表和索引是否满足聚簇要求;
获取目标表和索引的排它锁(exclusive lock);
标记指定的索引为聚簇依据;
根据目标表的数据和索引，拷贝数据并创建一个有序存储的临时表;
交换临时表和目标表的 relfilenode，以确保目标表的 OID 不会发生变化(数据库根据 OID 管理对象);
调用 reindex 重建目标表的索引;
反馈 vacuum full 状态给统计线程;
清除表的 CLUSTER 操作状态。

语法格式

对一个表进行聚簇排序

CLUSTER [ VERBOSE ] table_name [ USING index_name ];

对一个分区进行聚簇排序

CLUSTER [ VERBOSE ] table_name PARTITION ( partition_name ) [ USING index_name ];

对已做过聚簇的表重新进行聚簇
```
CLUSTER [ VERBOSE ];
```

说明：

不含参数的 CLUSTER 会将当前用户所拥有的数据库中的先前做过聚簇的所有表重新处理。

测试示例

创建测试表和索引

postgres=> create tablespace tbs1 relative location 'tablespace/tbs1' ;
CREATE TABLESPACE
postgres=> create table test tablespace tbs1
	   as
	   select relname,relnamespace,relowner,relfilenode from pg_class;
INSERT 0 648
postgres=> create index test_idx on test(relfilenode) tablespace tbs1;
CREATE INDEX

查询表信息

postgres=> \d test
       Table "public.test"
    Column    | Type | Modifiers
--------------+------+-----------
 relname      | name |
 relnamespace | oid  |
 relowner     | oid  |
 relfilenode  | oid  |
Indexes:
    "test_idx" btree (relfilenode) TABLESPACE tbs1, tablespace "tbs1"
Tablespace: "tbs1"

postgres=> select oid,relname,relpages,reltablespace,relfilenode
	   from pg_class
	   where relname like '%test%';
  oid  | relname  | relpages | reltablespace | relfilenode
-------+----------+----------+---------------+-------------
 17187 | test     |        9 |         16385 |       17187
 17190 | test_idx |        4 |         16385 |       17190


postgres=> select ctid,* from test
	   order by relfilenode desc
	   limit 10;
  ctid  |       relname        | relnamespace | relowner | relfilenode
--------+----------------------+--------------+----------+-------------
 (2,8)  | test                 |         2200 |       10 |       17187
 (1,40) | emp1_empno_idx       |         2200 |       10 |       17169
 (1,12) | emp1                 |         2200 |       10 |       17166
 (0,30) | t3                   |         2200 |       10 |       17154
 (0,29) | t2                   |         2200 |       10 |       17150
 (0,28) | t2_id_seq            |         2200 |       10 |       17148
 (0,27) | seq01                |         2200 |       10 |       17147
 (0,26) | seq02                |         2200 |       10 |       17145
 (0,25) | pg_toast_17129_index |           99 |       10 |       17142
 (0,24) | pg_toast_17129       |           99 |       10 |       17140

备注：ctid表示数据记录的物理行位置信息，格式为(blockid,offset)

通过以上操作我们可以获得以下信息：

当前创建的测试表和索引存储在同一个表空间(16385);
表 test 的 oid 为 17178 且数据文件名为 17178;
索引 test_idx 的 oid 为 17190 且数据文件名为 17190;
表 test 的数据存储物理位置并没有按照索引进行排序(ctid)，查询操作至少需要读取 3 个数据块的数据（默认堆表是无序存储）。

执行 cluster 操作

postgres=> cluster test using test_idx ; CLUSTER

查询表信息

postgres=> \d test
       Table "public.test"
    Column    | Type | Modifiers
--------------+------+-----------
 relname      | name |
 relnamespace | oid  |
 relowner     | oid  |
 relfilenode  | oid  |
Indexes:
    "test_idx" btree (relfilenode) TABLESPACE tbs1 CLUSTER, tablespace "tbs1"
Tablespace: "tbs1"

postgres=> select oid,relname,relpages,reltablespace,relfilenode from pg_class where relname like '%test%';
  oid  | relname  | relpages | reltablespace | relfilenode
-------+----------+----------+---------------+-------------
 17187 | test     |        9 |         16385 |       17191
 17190 | test_idx |        4 |         16385 |       17194

postgres=> insert into test select relname,relnamespace,relowner,relfilenode from pg_class where relfilenode in (17154,17169);
INSERT 0 2

postgres=> select ctid,* from test order by relfilenode desc limit 10;
  ctid  |    relname     | relnamespace | relowner | relfilenode
--------+----------------+--------------+----------+-------------
 (8,48) | test           |         2200 |       10 |       17187
 (8,47) | emp1_empno_idx |         2200 |       10 |       17169
 (8,50) | emp1_empno_idx |         2200 |       10 |       17169
 (8,46) | emp1           |         2200 |       10 |       17166
 (8,45) | t3             |         2200 |       10 |       17154
 (8,49) | t3             |         2200 |       10 |       17154
 (8,44) | t2             |         2200 |       10 |       17150
 (8,43) | t2_id_seq      |         2200 |       10 |       17148
 (8,42) | seq01          |         2200 |       10 |       17147
 (8,41) | seq02          |         2200 |       10 |       17145

通过对表进行 cluster 操作，我们可以看到以下变化：

test 的索引 test_idx 已经被标记为 CLUSTER，openGauss 已经记录在哪个索引上建立了聚簇;
索引 test_idx、表 test 的 OID 和对应的表空间并没有发生变化，但是 relfilenode 却发生了变化，这说明在 cluster 操作过程中，数据文件本身已经发生了变化，不再是原先的那个数据文件。
最明显的是表中的数据已经按照索引列 relfilenode 进行排序，查询的这 10 行数据都在同一个数据块(blockid=8)中存储，只需要读取一个数据块就可以获得目标数据。
后插入的数据(relfilenode=17169 和 17154)并不会按照 test_idx 索引进行聚簇排序存储。

小结

通过对表的 cluster 操作，可以将堆表内的数据进行有序存储(类似于 oracle 的索引组织表功能)，在一定程度上可以降低 SQL 查询的 IO 消耗，但是让人头疼的是每次 cluster 操作时带来的表锁问题(测试数据未显示)使得该操作只能在业务空闲期由 DBA 手动执行，而且 cluster 操作所需的时间和空间的估算需要依据目标表和索引的具体量级和硬件环境而定。另外，当数据进行有序存储后，表的统计信息可能与实际的情况差异较大，为了避免 CBO 优化器的误判，建议对目标表手动发起一次 ANALYZE。

在官方文档中还指出：cluster 操作不支持在事务中执行，且仅支持行存引擎的 Btree 索引，这两点也是需要注意的限制条件。

TIPS

关于数据有序存储，在列存引擎中可以使用 Psort 索引进行数据批量导入时的局部排序，减少 CU 之间的数据交集和误读现象，大幅降低 SQL 查询对磁盘 IO 的消耗。

关于 MOT 这个内存引擎，磁盘的 IO 应该不算是瓶颈问题，有序存储显得也不那么重要了，可以忽略。MOT 更应该考虑的是并发访问的锁瓶颈和 CPU、内存交互带来的延迟等问题。

openGauss

开源数据库

openGauss社区官网

开源社区

openGauss的索引组织表

openGauss 的索引组织表 ​

概述 ​

语法格式 ​

测试示例 ​

小结 ​

TIPS ​

openGauss 的索引组织表

概述

语法格式

测试示例

小结

TIPS