1 概述

1.1 案例介绍

分区表在数据库中使用广泛，尤其在数据库大表中对数据拆分，分表等领域，利用分区把表数据划分多个不同数据文件，利用表空间可把不同分区的表数据分布在不同磁盘，可以充分利用磁盘I/O和数据文件并行读取，以分区方式优化索引起到查询性能提升(因为不同分区会使优化器对执行计划剪枝)。

通过实际操作，让大家深入了解如何使用分区表，在日常开发中如何在项目中运用分区表的优点，从而掌握分区表在数据分布、索引优化的应用，体验其在应用开发中的优势。

1.2 适用对象
  • 企业
  • 个人开发者
  • 高校学生
1.3 案例时间

本案例总时长预计60分钟。

1.4 案例流程

基于开发者空间GaussDB数据库的分区表项目实践_GaussDB

说明：

  1. 领取开发者空间GaussDB云数据库生态版；
  2. 在云数据库终端进入GaussDB的SQL界面；
  3. 通过GaussDB数据库DAS工具，在SQL界面实践分区表功能；
1.5 资源总览

资源名称

规格

单价(元)

时长(分钟)

 开发者空间-云数据库GaussDB

鲲鹏通用计算增强型 kc1 | 2vCPUs | 4G | HCE2.0

免费

60

最新案例动态，请查阅 《基于开发者空间GaussDB数据库的分区表项目实践》。小伙伴快来领取华为开发者空间进行实操体验吧！

2 分区表讲解

2.1 开发者空间云数据库GaussDB的领取

面向广大开发者群体，华为开发者空间提供一个生态版的GaussDB云数据库，可快速体验华为根技术和资源。

关于开发者空间云数据库GaussDB的领取和开通，参考如下案例内容。链接如下：  华为开发者空间-GaussDB云数据库领取与实践

2.2 分区表概述

分区表(Partitioned Table)指在单节点内对表数据内容按照分区键以及围绕分区键的分区策略对表进行逻辑切分。在GaussDB中，数据分区是在一个节点内部对数据按照用户指定的策略做进一步的水平分表，将表中的数据按照指定方式划分为多个互不重叠的部分。

2.3 GaussDB支持的分区表

GaussDB数据库支持的分区表为一级分区表和二级分区表，其中一级分区表包括范围分区表、间隔分区表、列表分区表、哈希分区表四种，二级分区表包括范围分区、列表分区、哈希分区两两组合的九种。相对于其他数据库GaussDB支持二级分区。目前行存表不支持数值分区。

2.4 分区表优点

分区表和普通表相比具有以下优点：

  • 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
  • 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
  • 方便维护：对于大型表，数据维护变得非常困难。而分区表可以将数据分散到多个小表中，使得数据维护更加便捷。例如，对某个分区进行删除操作时，只需要删除该分区对应的小表即可。
  • 扩展性好 :随着业务的发展，数据量会不断增加。分区表可以根据业务需求动态调整分区数量，以满足更高的查询性能和存储需求。
  • 均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。

说明：普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。

3 分区表实践

3.1 创建分区表
3.1.1 范围分区表示例

下面示例是带表空间的分区表和不带表空间的分区表。不带表空间分区表，其不同分区数据文件在同一目录下，由多个文件划分数据分区，在读取多个分区时，以多文件并发读写提升性能，但多文件都是在同一个磁盘IO瓶颈限制下。而带表空间的分区表，在多磁盘系统中把表空间设置在不同磁盘下的文件目录，这样分区表的数据会划分到不同磁盘下表空间目录下存储，表数据读取时会以多个磁盘IO速度并发读取，针对大数据量表的读写场景下，性能提升明显。

--创建分区表。
CREATE TABLE test_range2(
    id INT, 
    info VARCHAR(20)
) PARTITION BY RANGE (id) (
    PARTITION p1 START(1) END(600) EVERY(200), 
    PARTITION p2 START(600) END(800), 
    PARTITION pmax START(800) END(MAXVALUE)
);
--查看分区信息。
SELECT relname, boundaries FROM pg_partition 
WHERE parentid = 'test_range2'::regclass AND parttype = 'p' ORDER BY 1;
--删除。
DROP TABLE test_range2;

--创建表空间 VALUES LESS THAN
CREATE TABLESPACE tbs_test_range1_p1 RELATIVE LOCATION 'tbs_test_range1/tablespace_1';
CREATE TABLESPACE tbs_test_range1_p2 RELATIVE LOCATION 'tbs_test_range1/tablespace_2';
CREATE TABLESPACE tbs_test_range1_p3 RELATIVE LOCATION 'tbs_test_range1/tablespace_3';
CREATE TABLESPACE tbs_test_range1_p4 RELATIVE LOCATION 'tbs_test_range1/tablespace_4';
--创建分区表test_range1。
CREATE TABLE test_range1(
    id INT, 
    info VARCHAR(20)
) PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (200) TABLESPACE tbs_test_range1_p1,
    PARTITION p2 VALUES LESS THAN (400) TABLESPACE tbs_test_range1_p2,
    PARTITION p3 VALUES LESS THAN (600) TABLESPACE tbs_test_range1_p3,
    PARTITION pmax VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_test_range1_p4);
--插入1000条数据
INSERT INTO test_range1 VALUES(GENERATE_SERIES(1,1000),'abcd');
--查看p1分区的行数199条，[1,200)。
SELECT COUNT(*) FROM test_range1 PARTITION (p1);
--查看p2分区的行数200条，[200,400)。
SELECT COUNT(*) FROM test_range1 PARTITION (p2);
--查看分区信息。
SELECT a.relname, a.boundaries, b.spcname 
FROM pg_partition a, pg_tablespace b 
WHERE a.reltablespace = b.oid AND a.parentid = 'test_range1'::regclass;
--删除
DROP TABLE test_range1;
DROP TABLESPACE tbs_test_range1_p1;
DROP TABLESPACE tbs_test_range1_p2;
DROP TABLESPACE tbs_test_range1_p3;
DROP TABLESPACE tbs_test_range1_p4;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_02

基于开发者空间GaussDB数据库的分区表项目实践_GaussDB_03

基于开发者空间GaussDB数据库的分区表项目实践_数据库_04

基于开发者空间GaussDB数据库的分区表项目实践_GaussDB_05

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_06

3.1.2 列表分区示例
--创建列表分区表。
CREATE TABLE test_list ( NAME VARCHAR ( 50 ), area VARCHAR ( 50 ) ) 
PARTITION BY LIST (area) (
    PARTITION p1 VALUES ('Beijing'),
    PARTITION p2 VALUES ('Shanghai'),
    PARTITION p3 VALUES ('Guangzhou'),
    PARTITION p4 VALUES ('Shenzhen'),
    PARTITION pdefault VALUES (DEFAULT)
);
--插入数据。
INSERT INTO test_list VALUES ('bob', 'Shanghai'),('scott', 'Sichuan');
--查询分区数据。
SELECT * FROM test_list PARTITION (p2);
SELECT * FROM test_list PARTITION (pdefault);
--删除。
DROP TABLE test_list;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.

基于开发者空间GaussDB数据库的分区表项目实践_数据库_07

基于开发者空间GaussDB数据库的分区表项目实践_分区表_08

基于开发者空间GaussDB数据库的分区表项目实践_分区表_09

3.1.3 哈希分区示例
--创建哈希分区表，指定分区数。
CREATE TABLE test_hash1(c1 int) PARTITION BY HASH(c1) PARTITIONS 3;
--创建哈希分区表，并指定分区名。
CREATE TABLE test_hash2(c1 int) PARTITION BY HASH(C1)(
    PARTITION pa,
    PARTITION pb,
    PARTITION pc
);
--查看分区信息。
SELECT b.relname AS table_name, a.relname AS partition_name 
FROM pg_partition a, pg_class b 
WHERE b.relname LIKE 'test_hash%' AND a.parttype = 'p' AND a.parentid = b.oid; 
--删除。
DROP TABLE test_hash1,test_hash2;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.

基于开发者空间GaussDB数据库的分区表项目实践_分区表_10

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_11

3.1.4 间隔分区示例
CREATE TABLE interval_sales
(
    prod_id       NUMBER(6),
    cust_id       NUMBER,
    time_id       DATE,
    channel_id    CHAR(1),
    promo_id      NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold   NUMBER(10, 2)
)
PARTITION BY RANGE (time_id) INTERVAL ('1 month')
(
    PARTITION date_2015 VALUES LESS THAN ('2016-01-01'),
    PARTITION date_2016 VALUES LESS THAN ('2017-01-01'),
    PARTITION date_2017 VALUES LESS THAN ('2018-01-01'),
    PARTITION date_2018 VALUES LESS THAN ('2019-01-01'),
    PARTITION date_2019 VALUES LESS THAN ('2020-01-01')
);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_12

3.1.5 注意事项
  • 唯一约束和主键约束的约束键包含所有分区键将为约束创建LOCAL索引，否则创建GLOBAL索引。
  • 目前哈希分区仅支持单列构建分区键，暂不支持多列构建分区键。
  • 只需要有间隔分区表的INSERT权限，往该表INSERT数据时就可以自动创建分区。
  • 对于分区表PARTITION FOR (values)语法，values只能是常量。
  • 对于分区表PARTITION FOR (values)语法，values在需要数据类型转换时，建议使用强制类型转换，以防隐式类型转换结果与预期不符。
  • 分区数最大值为1048575个，一般情况下业务不可能创建这么多分区，这样会导致内存不足。应参照参数local_syscache_threshold的值合理创建分区，分区表使用内存大致为(分区数 \* 3 / 1024)MB。理论上分区占用内存不允许大于local_syscache_threshold的值，同时还需要预留部分空间以供其他功能使用。
  • 考虑性能影响，一般建议单表最大分区数不超过2000，子分区数 \*(LOCAL索引个数 + 1) 不超过10000。
  • 当分区数太多导致内存不足时，会间接导致性能急剧下降。
  • 指定分区语句目前不能走全局索引扫描。
  • 不支持XML类型数据作为分区键、二级分区键。
  • 在为数据对象增加或者变更ILM策略的时候，如果追加了行级表达式，需要注意行表达式目前只支持白名单中列出的函数。具体白名单函数列表参考行表达式函数白名单。
3.2 修改分区表
3.2.1 修改表分区名称
--创建前置分区表。
CREATE TABLE test_p1 (col1 INT, col2 INT) PARTITION BY RANGE (col1) 
( 
    PARTITION p1 VALUES LESS THAN (10), 
    PARTITION p2 VALUES LESS THAN (20), 
    PARTITION p3 VALUES LESS THAN (MAXVALUE) 
);
--修改分区名称。
ALTER TABLE test_p1 RENAME PARTITION p3 TO pmax;
--查询分区信息。
SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p1'::regclass AND parttype <> 'r';
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_13

基于开发者空间GaussDB数据库的分区表项目实践_数据库_14

3.2.2 移动分区表空间
--创建分区。
CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';
--移动分区表空间。
ALTER TABLE test_p1 MOVE PARTITION P1 TABLESPACE tbs_data1;
--查看分区表空间。
SELECT relname, spcname FROM pg_partition t1, pg_tablespace t2 WHERE T1.reltablespace=t2.oid and t1.parentid='test_p1'::regclass;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

基于开发者空间GaussDB数据库的分区表项目实践_GaussDB_15

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_16

3.2.3 分区交换
--创建普通表，插入数据。
CREATE TABLE test_ep1(col1 INT,col2 INT);
INSERT INTO test_ep1 VALUES (GENERATE_SERIES(1,30), 1000);
--迁移普通表数据到指定分区。
ALTER TABLE test_p1 EXCHANGE PARTITION (p1) WITH TABLE test_ep1 VERBOSE;
--查询。
SELECT COUNT(*) FROM test_p1 PARTITION (p1);
--删除表test_ep1
DROP TABLE test_ep1;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_17

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_18

3.2.4 分区合并
--将test_p1表中p2,pmax分区合并到pmax中。
ALTER TABLE test_p1 MERGE PARTITIONS p2,pmax INTO PARTITION pmax;
--查看分区。
SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p1'::regclass AND parttype <> 'r' order by 1;
--删除表和表空间。
DROP TABLE test_p1;
DROP TABLESPACE tbs_data1;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_19

基于开发者空间GaussDB数据库的分区表项目实践_分区表_20

3.2.5 分区切割
--建表
CREATE TABLE test_r1 (col1 INT,col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
); 
--切割分区
ALTER TABLE test_r1 SPLIT PARTITION pmax AT (20) INTO (PARTITION p2, PARTITION pmax1);
ALTER TABLE test_r1 SPLIT PARTITION pmax1 INTO (
    PARTITION p3 VALUES LESS THAN (30),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
--查询
SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_r1'::regclass AND parttype <> 'r' order by 1;
--删除表test_r1。
DROP TABLE test_r1;
--建表
CREATE TABLE test_r2(col1 INT, col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1   START(1) END(10),
    PARTITION p2   START(10) END(20),
    PARTITION pmax START(20) END(MAXVALUE)
);
--切割分区
ALTER TABLE test_r2 SPLIT PARTITION pmax INTO (
    PARTITION p3 START(20) END(30),
    PARTITION pmax1 START(30) END (MAXVALUE)
);
--查看
SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_r2'::regclass AND parttype <> 'r' order by 1;
--删除表test_r2
DROP TABLE test_r2;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.

基于开发者空间GaussDB数据库的分区表项目实践_数据库_21

基于开发者空间GaussDB数据库的分区表项目实践_GaussDB_22

基于开发者空间GaussDB数据库的分区表项目实践_分区表_23

基于开发者空间GaussDB数据库的分区表项目实践_分区表_24

3.2.6 添加分区
--建表
CREATE TABLE test_p2 (col1 INT, col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (20)
);
--添加分区
ALTER TABLE test_p2 ADD PARTITION p3 VALUES LESS THAN (30);
--删除表test_p2
DROP TABLE test_p2;
--建表
CREATE TABLE test_p3 (col1 INT, col2 INT) PARTITION BY LIST(col1)(
    PARTITION p1 VALUES (1),
    PARTITION p2 VALUES (2)
);
--添加分区
ALTER TABLE test_p3 ADD PARTITION p3 VALUES (3);
--删除表test_p3
DROP TABLE test_p3;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_25

基于开发者空间GaussDB数据库的分区表项目实践_数据库_26

3.2.7 删除分区
--建表
CREATE TABLE test_p4 (col1 INT, col2 INT) PARTITION BY LIST(col1)(PARTITION p1 VALUES (1),PARTITION p2 VALUES (2));
--删除test_p3表的p2分区
ALTER TABLE test_p4 DROP PARTITION p2;
--查看
SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p4'::regclass;
--删除表test_p4
DROP TABLE test_p4;
--指定partition value删除分区
--建表
CREATE TABLE test_p4 (col1 INT, col2 INT) PARTITION BY RANGE(col1)(PARTITION p1 VALUES LESS THAN(1),PARTITION p2 VALUES LESS THAN (2));
--删除test_p3表的分区键为1时所处的分区
ALTER TABLE test_p4 DROP PARTITION FOR (1);
--查看
SELECT relname, boundaries FROM pg_partition WHERE parentid='test_p4'::regclass order by 1 desc;
--删除表test_p4
DROP TABLE test_p4;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_27

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_28

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_29

3.2.8 清空分区
--建表
CREATE TABLE test_p5 (col1 INT, col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1 VALUES LESS THAN (5),
    PARTITION p2 VALUES LESS THAN (10)
);
--插入数据
INSERT INTO test_p5 VALUES (GENERATE_SERIES(1,9), 100);
--查看p2分区数据
SELECT * FROM test_p5 PARTITION (p2);
--清空p2分区的数据
ALTER TABLE test_p5 TRUNCATE PARTITION p2;
--查看p2分区数据
SELECT * FROM test_p5 PARTITION (p2);
--删除表test_p5
DROP TABLE test_p5;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.

基于开发者空间GaussDB数据库的分区表项目实践_分区表_30

基于开发者空间GaussDB数据库的分区表项目实践_数据库_31

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_32

3.2.9 开启和关闭列表分区的自动扩展功能
--创建一个列表分区
CREATE TABLE list_int (c1 int, c2 int)
PARTITION BY LIST (c1)
(
    PARTITION p1 VALUES (1, 2, 3),
    PARTITION p2 VALUES (4, 5, 6)
);
--开启列表分区的自动扩展功能
ALTER TABLE list_int SET PARTITIONING AUTOMATIC;
--关闭列表分区的自动扩展功能
ALTER TABLE list_int SET PARTITIONING MANUAL;
--删除列表分区
DROP TABLE list_int;
--开启和关闭范围分区的自动扩展功能
--创建一个范围分区
CREATE TABLE range_int (c1 int, c2 int)
PARTITION BY RANGE (c1)
(
    PARTITION p1 VALUES LESS THAN (5),
    PARTITION p2 VALUES LESS THAN (10),
    PARTITION p3 VALUES LESS THAN (15)
);
--开启范围分区的自动扩展功能
ALTER TABLE range_int SET INTERVAL (5);
--关闭范围分区的自动扩展功能
ALTER TABLE range_int SET INTERVAL ();
--删除范围分区
DROP TABLE range_int;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_33

3.3 删除分区表

分区表删除方法与普通表一样，语法如下。

DROP TABLE [ IF EXISTS ] 
    { [schema.]table_name } [, ...] [ CASCADE | RESTRICT ] [ PURGE ];
  • 1.
  • 2.
3.4 分区表性能优化
3.4.1 查询优化
  • 使用分区键：查询语句中应包含分区键，以便MySQL能够快速定位到相关分区，减少扫描的分区数量。
  • 避免跨分区查询：尽量避免跨多个分区的查询，因为跨分区查询会增加扫描的分区数量，影响查询性能。
SELECT * FROM interval_sales WHERE time_id BETWEEN '2021-01-01' AND '2021-12-31';
  • 1.
3.6 分区表查询性能异常
3.6.1 常见原因

使用分区表进行相关查询业务，SQL性能慢时，常见的原因有以下几种：

  • 分区索引失效，顺序扫描导致的SQL性能慢
  • 分区表无法进行分区剪枝导致的SQL性能慢
  • SQL计划选择非最优导致的SQL性能慢
3.6.2 处理方法
3.6.2.1 判断是否存在索引异常行为

部分分区DDL如果不带UPDATE GLOBAL INDEX子句，会导致分区表Global索引失效。同时用户也可以使用ALTER TABLE或者ALTER INDEX使分区表索引失效。当分区表或者部分分区上索引不可用后，会导致查询SQL无法走索引扫描，从而出现慢SQL场景。

3.6.2.2 查询索引类型和状态
  • 分区表索引分为LOCAL索引与GLOBAL索引：LOCAL索引与某个具体分区绑定，而GLOBAL索引则对应整个分区表。
  • 唯一约束和主键约束的约束键包含所有分区键则创建LOCAL索引，否则创建GLOBAL索引。

Local索引会在每个分区创建一个索引，Global索引会在整个分区表上创建一个全局索引。

可以通过系统表pg_index查询分区表的所有索引，并获取索引是否可用的状态，其索引类型在pg_class中给出。下面给出了查询分区表t1的全部索引状态及索引类型的命令参考：

CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';

CREATE TABLE web_returns_p2
(
    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),
    ca_city character varying(60),
    ca_county character varying(30),
    ca_state character(2),
    ca_zip character(10),
    ca_country character varying(20),
    ca_gmt_offset numeric(5,2),
    ca_location_type character(20))
TABLESPACE example1 PARTITION BY RANGE (ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(5000),
        PARTITION P2 VALUES LESS THAN(10000),
        PARTITION P3 VALUES LESS THAN(15000),
        PARTITION P4 VALUES LESS THAN(20000),
        PARTITION P5 VALUES LESS THAN(25000),
        PARTITION P6 VALUES LESS THAN(30000),
        PARTITION P7 VALUES LESS THAN(40000),
        PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;

CREATE INDEX tpcds_web_returns_p2_index1 ON web_returns_p2 (ca_address_id) LOCAL;

CREATE INDEX tpcds_web_returns_p2_index2 ON web_returns_p2 (ca_address_sk) LOCAL
(
    PARTITION web_returns_p2_P1_index,
    PARTITION web_returns_p2_P2_index TABLESPACE example3,
    PARTITION web_returns_p2_P3_index TABLESPACE example4,
    PARTITION web_returns_p2_P4_index,
    PARTITION web_returns_p2_P5_index,
    PARTITION web_returns_p2_P6_index,
    PARTITION web_returns_p2_P7_index,
    PARTITION web_returns_p2_P8_index
) TABLESPACE example2;

CREATE INDEX tpcds_web_returns_p2_global_index ON web_returns_p2 (ca_street_number) GLOBAL;

select c.relname, i.indisusable, c.relkind 
from pg_class c 
join pg_index i on c.oid = i.indexrelid 
join pg_class r on i.indrelid = r.oid 
where r.relname = 'web_returns_p2';
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.

基于开发者空间GaussDB数据库的分区表项目实践_分区表_34

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_35

返回结果：其中indisusable字段为't'表示该索引可用，为'f'表示索引已经失效，在查询业务中该索引无法使用;relkind为'i'表示该索引为Local索引，为'I'表示该索引为Global索引。

可以看到表t1的两条索引t1_c1_idx、t1_c2_tableoid_idx被列出。其中t1_c1_idx带有LOCAL关键字，表示该索引为Local索引，t1_c2_tableoid_idx没有LOCAL关键字，表示该索引为Global索引。同时t1_c2_tableoid_idx标记了UNUSABLE，表示该索引不可用。

3.6.2.3 查询索引分区状态

对于Local索引，还需要确认目标查询分区的索引状态，这个信息可以从系统表pg_partition中查询。

select p.relname,p.indisusable from pg_partition p join pg_class c on p.parentid=c.oid where c.relname='web_returns_p2';
  • 1.

基于开发者空间GaussDB数据库的分区表项目实践_华为开发者空间_36

3.6.2.4 重建异常的索引/索引分区

如果分区表的索引/索引分区状态异常，会导致查询SQL无法走索引扫描，需要重建索引/索引分区。重建索引的命令如下：

ALTER INDEX t1_c2_tableoid_idx REBUILD;
  • 1.

t1_c2_tableoid_idx 为分区表索引名，需要根据实际情况变更

重建索引分区的命令如下：

ALTER INDEX t1_c1_idx REBUILD PARTITION p2_c1_idx;
  • 1.

t1_c1_idx 为分区表索引名，需要根据实际情况变更

3.6.2.4.1 分区表索引重建/不可用

用户可以通过命令使得一个分区表索引或者一个索引分区不可用，此时该索引/索引分区不再维护。使用重建索引命令可以重建分区表索引，恢复索引的正常功能。

此外，部分分区级DDL操作也会使得Global索引失效，包括删除drop、交换exchange、清空truncate、分割split、合并merge。如果在DDL操作中带UPDATE GLOBAL INDEX子句，则会同步更新Global索引，否则需要用户自行重建索引。

3.6.2.4.2 local索引分区重建/不可用
  • 使用ALTER INDEX PARTITION可以设置Local索引分区是否可用。
  • 使用ALTER TABLE MODIFY PARTITION可以设置分区表上指定分区的所有索引分区是否可用。

例如，假设分区表range_sales上存在两张Local索引range_sales_idx1和range_sales_idx2，假设其在分区date_202001上对应的索引分区名分别为range_sales_idx1_part1和range_sales_idx2_part1。

下面给出了维护分区表分区索引的语法：

  • 可以通过如下命令设置分区date_202001上的所有索引分区均不可用。
  • ALTER TABLE range_sales MODIFY PARTITION date_202001 UNUSABLE LOCAL INDEXES;
  • 或者通过如下命令单独设置分区date_202001上的索引分区range_sales_idx1_part1不可用。
  • ALTER INDEX range_sales_idx1 MODIFY PARTITION range_sales_idx1_part1 UNUSABLE;
  • 可以通过如下命令重建分区date_202001上的所有索引分区。
  • ALTER TABLE range_sales MODIFY PARTITION date_202001 REBUILD UNUSABLE LOCAL INDEXES;
  • 或者通过如下命令单独重建分区date_202001上的索引分区range_sales_idx1_part1。
  • ALTER INDEX range_sales_idx1 REBUILD PARTITION range_sales_idx1_part1;
3.6.2.5 判断分区表是否存在剪枝异常的场景

当分区表的分区键所在列存在条件时，可以触发分区剪枝。剪枝是被动触发的，当查询业务满足分区剪枝条件时，会自动触发分区剪枝。

3.6.2.5.1 判断是否触发了分区剪枝

可以通过查询计划来判断是否触发了分区剪枝。

下面的业务触发了分区静态剪枝，计划中的'Iterations: 1'表示扫描了1个分区，'Selected Partitions: 1'表示扫描的目标分区下标是1。

explain select * from t1 where c1 < 100;
  • 1.

下面的业务没有触发分区剪枝，计划中的'Iterations: 2'表示扫描了2个分区，'Selected Partitions: 1..2'表示扫描的目标分区下标是1和2。可以看到扫描的分区数等于分区表的总分区数，表示未进行任何分区剪枝。

explain select * from t1;
  • 1.

下面的业务触发了分区动态剪枝，计划中的'Iterations: PART'和'Selected Partitions: PART'表示优化器识别到分区表可以进行分区剪枝。

prepare p1 as select * from t1 where c1 < $1;
explain execute p1(100);
  • 1.
  • 2.
3.6.2.5.2 支持分区剪枝的场景

当分区键所在列存在条件时，可以进行分区剪枝。条件剪枝支持场景为：比较表达式(\<，\<=，=，\>=，\>)、逻辑表达式(AND、OR)、数组表达式。需要注意的是，列表和哈希分区不支持除等号外的其他比较表达式的剪枝。如果条件全为常量，则进行静态剪枝；如果条件带有参数、部分隐式转换、immutable函数，则进行动态剪枝。

当分区表作为参数化路径计划的内表且分区键所在列为索引检索条件时，支持动态剪枝。

3.6.2.5.3 不支持分区剪枝的场景

分区键所在列的条件为子查询表达式、无法直接强转的类型转换、stable/volatile函数时，不支持分区剪枝。

分区表在生成除参数化路径外的其他JOIN计划时，不支持动态剪枝。

3.6.2.5.4 业务改写适配分区剪枝

当业务设计不合理，导致原逻辑上可以走分区剪枝的计划，最终未走分区剪枝时，可以改写业务以适配分区剪枝，从而提升分区表查询业务性能。比如使用HINT固定计划、在客户端修改绑参类型等方法。

3.6.2.5.5 判断分区表是否是因为SQL计划选择非最优

通过执行计划分析判断即可，这里不再阐述。