一篇學會 OpenGauss 分區(qū)表索引
本文轉載自微信公眾號「數(shù)據(jù)和云」，作者高云龍。轉載本文請聯(lián)系數(shù)據(jù)和云公眾號。
一、概述
openGauss分區(qū)表支持兩種索引：全局(global)索引和本地(local)索引。
分區(qū)表創(chuàng)建索引不支持concurrently語法，默認索引是全局索引，創(chuàng)建本地索引需要指定local。
創(chuàng)建主鍵約束和唯一約束必須要包含分區(qū)字段，創(chuàng)建本地唯一索引也必須要包含分區(qū)字段，但是創(chuàng)建全局唯一索引沒有這個限制。
- postgres=# create index concurrently on part_index_test(col2,col1,col3);
- ERROR: cannot create concurrent partitioned indexes
本文主要闡述添加或刪除分區(qū)對索引的影響。
數(shù)據(jù)庫版本：openGauss 1.1.0
二、測試
1.建表語句
范圍分區(qū)中的間隔語法可以自動追加分區(qū)表，這里以間隔分區(qū)表為例：
- create table part_index_test(
- partid varchar(32) not null,
- col1 varchar(2) not null,
- col2 date not null,
- col3 varchar(8) not null
- )partition by range(col2)
- interval('1 day')
- (
- partition part1 values less than ('20210331'),
- partition part2 values less than ('20210401')
- );
全局索引：
- create index on part_index_test(col2,col1,col3);
本地索引：
- create index on part_index_test(col2,col1,col3) local;
2.測試數(shù)據(jù)
間隔分區(qū)是以1天為單位，所以新增一天的數(shù)據(jù)，會自動增加一個以sys_p開頭的自增分區(qū)：
- insert into part_index_test select generate_series(1,1000),'1','20210401',generate_series(1,1000);
- insert into part_index_test select generate_series(1,1000),'1','20210402',generate_series(1,1000);
- insert into part_index_test select generate_series(1,1000),'1','20210403',generate_series(1,1000);
- insert into part_index_test select generate_series(1,1000),'1','20210404',generate_series(1,1000);
- insert into part_index_test select generate_series(1,1000),'1','20210405',generate_series(1,1000);
- insert into part_index_test select generate_series(1,1000),'1','20210406',generate_series(1,1000);
3.刪除分區(qū)語法
- 先通過pg_partition系統(tǒng)表查看分區(qū)
- select relname,parttype,parentid,boundaries from pg_partition;
- 默認刪除分區(qū)：
- alter table part_index_test DROP PARTITIONpartition_name;
- update global index 方式刪除分區(qū)：
- alter table part_index_test DROP PARTITION partition_name update global index;
4.索引使用
- explain select * from part_index_test where col2=$1 and partid=$2;
5.測試結果
|
- |
添加分區(qū) |
刪除分區(qū) |
備注 |
|
全局索引 |
生效 |
默認語法失效/update global index語法生效 |
索引失效后，需要reindex |
|
本地索引 |
生效 |
生效 |
總結：
1、添加/刪除分區(qū)不影響本地索引使用。
2、添加分區(qū)不影響全局索引使用，默認刪除分區(qū)方式，全局索引失效，需要對全局索引重建;update global index方式刪除分區(qū)，不影響全局索引使用。
三、示例
1.分區(qū)表準備
- 創(chuàng)建分區(qū)表
- create table part_range_lessthan_int(
- id serial primary key,
- col1 varchar(16))
- partition by range(id)
- (
- partition p1 values less than(1000),
- partition p2 values less than(2000),
- partition p3 values less than(3000),
- partition p4 values less than(maxvalue)
- );
- 創(chuàng)建全局索引
- create unique index on part_range_lessthan_int(col1);
- 初始化數(shù)據(jù)
- insert into part_range_lessthan_int select generate_series(1,5000),'tuple'||generate_series(1,5000);
- 默認刪除分區(qū)SQL
- select now();alter table part_range_lessthan_int drop partition p1;
- update global index 刪除分區(qū)SQL
- select now();alter table part_range_lessthan_int drop partition p1 update global index;
- 查詢SQL
- analyze;select now();explain select * from part_range_lessthan_int where col1='tuple2500';
2.默認刪除分區(qū)語法
3.update global index 刪除分區(qū)
關于作者
高云龍，云和恩墨服務總監(jiān)，長期從事PG運維工作，目前在支持openGauss生態(tài)發(fā)展。
