分区表
📄字数 7.4K
👁️阅读量 加载中...
虚谷数据库支持将数据按照一定条件进行集中划分隔离，即为分区表，以提升大数据量场景下，仅查询部分集中数据的查询性能。在逻辑上，分区表与非分区表具有相同的特性，插入、更改、删除与选择等操作的命令与非分区表完全相同。对于数据量较大的表（如单表超过1亿行），建议创建为分区表。虚谷数据库提供了两级三种分区方式：
虚谷数据库按分区细粒度分为两级：
- 一级分区：对数据按指定分区条件进行初次分割
- 二级分区：在一级分区的基础上，再次进行分割
分区分割方式分为列表分区、范围分区与哈希分区三种：
- 范围分区
按照数据范围进行分割，数据将存储至所属范围分区。若分区键字段的取值是连续的，则不适合使用列表分区，此种情况的最佳分区模式是范围分区，即按条件将键的取值划分成若干个范围，每个范围对应一个分区，在向表插入记录前，先测试记录满足哪个分区的条件，再将记录插入至相应的分区中。 - 列表分区
按照数据离散值进行分割，数据将存储至与其值相等的分区。列表分区是指在分区时将分区键的各种取值罗列出来，指定每个取值或几个取值对应一个分区，各个分区中的记录有一个共性，那就是其分区键字段的取值都是分区的条件值或是条件值之一，列表分区主要针对分区键的取值为有限的离散值的情况。 - 哈希分区
按照数据哈希值进行分割，数据将存储至与其（哈希值%分区数）值相等的分区。哈希分区主要用于确保数据在预先确定数目的分区中平均分布。在范围分区与列表分区中，必须明确指定列值或列值集合作为判定依据，确定数据应该保存在哪个分区中；而在哈希分区中，数据库系统自动完成这些工作，用户仅需指定分区键、分区数量以及可选的分区。
一、表一级分区
分区有助于提高大数据量的表的查询性能。
1.1 语法格式
1.2 参数说明
- column_name：指定作为分区键的列名，将以此列数据作为分区依据
- opt_parti_interval：指定是否创建为自动拓展分区，请参阅自动拓展分区
- range_parti_item：定义范围分区项
(parti_values)：分区条件值列表，值个数必须与分区键个数相等，分区名称将自动生成
分区条件值列表含义：值大于或等于上一分区条件值列表，且小于此分区条件值列表的数据将存储于此分区( MAXVALUES )：最大分区条件值，将始终包含其他分区未包含的值，分区名称将自动生成- parti_name
VALUES LESS THAN (parti_values)：分区条件值列表，值个数必须与分区键个数相等，分区名称为 parti_name - parti_name
VALUES LESS THAN ( MAXVALUES )：最大分区条件值，将始终包含其他分区未包含的值，分区名称为 parti_name
- list_parti_item：定义列表分区项
(parti_values)：分区条件值列表，值个数必须与分区键个数相等，分区名称将自动生成
分区条件值列表含义：值等于此分区条件值列表的数据将存储于此分区( OTHERVALUES )：其他分区条件值，将始终包含其他分区未包含的值，分区名称将自动生成- parti_name
VALUES (parti_values)：分区条件值列表，值个数必须与分区键个数相等，分区名称为 parti_name - parti_name
VALUES ( OTHERVALUES )：其他分区条件值，将始终包含其他分区未包含的值，分区名称为 parti_name
- parti_num：一级哈希分区数目
- parti_name：一级哈希分区名称
注意
当MAXVALUES或OTHERVALUES分区存在时，将不再允许创建新分区，但可以删除分区，并且当分区仅剩一个唯一分区时，也不再允许删除唯一分区。
1.3 示例
示例1
创建一个列表分区表，分区键为city，根据分区键值划分4个分区，当分区键值为四川、云南、贵州时，数据分别存储于分区键对应分区中，若为其他值则存储在OTHERVALUES分区。sqlSQL> CREATE TABLE tab_parti_1( id INT, name VARCHAR(20), city CHAR(20) ) PARTITION BY LIST (city) PARTITIONS(('四川'),('云南'),('贵州'),(OTHERVALUES)); -- 查询分区表结构 SQL> SELECT ut.table_name,uc.col_name,uc.not_null,uc.def_val,uc.on_null,ut.parti_key FROM SYS_COLUMNS uc JOIN SYS_TABLES ut ON uc.table_id=ut.table_id WHERE ut.table_name='tab_parti_1'; +-------------+----------+----------+---------+---------+-----------+ | TABLE_NAME | COL_NAME | NOT_NULL | DEF_VAL | ON_NULL | PARTI_KEY | +-------------+----------+----------+---------+---------+-----------+ | TAB_PARTI_1 | ID | F | <NULL> | 0 | "CITY" | | TAB_PARTI_1 | NAME | F | <NULL> | 0 | "CITY" | | TAB_PARTI_1 | CITY | F | <NULL> | 0 | "CITY" | +-------------+----------+----------+---------+---------+-----------+示例2
创建一个范围分区表，分区键为id，将id<1的数据划分在part1，将1<=id<1000的数据划分在part2，依次类推，最后id>=2000的数据划分在MAXVALUES分区。sqlSQL> CREATE TABLE tab_parti_2( id INTEGER IDENTITY(1,2), name VARCHAR, city VARCHAR ) PARTITION BY RANGE (id) PARTITIONS ( part1 VALUES LESS THAN (1), part2 VALUES LESS THAN (1000), part3 VALUES LESS THAN (1500), part4 VALUES LESS THAN (2000), part5 VALUES LESS THAN (MAXVALUES) );示例3
创建一个哈希分区表，分区键为id，划分5个分区，分区名由数据库自动生成。sqlSQL> CREATE TABLE tab_parti_3( id INTEGER IDENTITY(1,2), name VARCHAR, city VARCHAR ) PARTITION BY HASH (id) PARTITIONS 5;示例4
分区管理，删除固定分区报告错误。sql-- 创建包含固定分区的自动扩展分区表 SQL> CREATE TABLE tab_parti_4 ( c1 INTEGER, c2 DATETIME, c3 VARCHAR ) PARTITION BY RANGE(c2) INTERVAL 1 DAY PARTITIONS ( part1 VALUES LESS THAN('2000-01-01 00:00:00'), part2 VALUES LESS THAN('2023-09-30 00:00:00'), part3 VALUES LESS THAN('2023-10-01 00:00:00') ); -- 查看表分区信息 SQL> SELECT p.* from sys_partis p INNER JOIN sys_tables t USING(table_id) WHERE t.table_name='tab_parti_4'; +-------+----------+------------+-----------------------+----------+--------+-----------+-----------+ | DB_ID | PARTI_NO | PARTI_NAME | PARTI_VAL | GSTO_NOS | ONLINE | RESERVED1 | RESERVED2 | +-------+----------+------------+-----------------------+----------+--------+-----------+-----------+ | 1 | 0 | PART1 | '2000-01-01 00:00:00' | 222 | T | <NULL> | <NULL> | | 1 | 1 | PART2 | '2023-09-30 00:00:00' | 223 | T | <NULL> | <NULL> | | 1 | 2 | PART3 | '2023-10-01 00:00:00' | 224 | T | <NULL> | <NULL> | +-------+----------+------------+-----------------------+----------+--------+-----------+-----------+ -- 删除自动扩展分区表固定分区返回错误 SQL> ALTER TABLE tab_parti_4 DROP PARTITION part1; Error: [E21093] 不允许删除自动扩展分区表的固定分区 -- 删除除固定分区以外的其他分区 SQL> ALTER TABLE tab_parti_4 DROP PARTITION part2; SQL> ALTER TABLE tab_parti_4 DROP PARTITION part3; -- 删除part1、part2分区后查看表分区信息 SQL> SELECT p.* from sys_partis p INNER JOIN sys_tables t USING(table_id) WHERE t.table_name='tab_parti_4'; +-------+----------+------------+-----------------------+----------+--------+-----------+-----------+ | DB_ID | PARTI_NO | PARTI_NAME | PARTI_VAL | GSTO_NOS | ONLINE | RESERVED1 | RESERVED2 | +-------+----------+------------+-----------------------+----------+--------+-----------+-----------+ | 1 | 0 | PART1 | '2000-01-01 00:00:00' | 224 | T | <NULL> | <NULL> | +-------+----------+------------+-----------------------+----------+--------+-----------+-----------+
二、表二级分区
二级分区是对一级分区的再次分割。
注意
- 虚谷数据库系统中定义二级分区即默认所有一级分区按照指定二级分区进行再分割，不能针对一级分区进行不同的二级分区划分
- 若一级分区为哈希分区，则无法创建二级分区
2.1 语法格式
2.2 参数说明
- column_name：指定作为分区键的列名，将以此列数据作为分区依据
- range_parti_item：定义范围分区项，请参阅表一级分区中此子句说明
- list_parti_item：定义列表分区项，请参阅表一级分区中此子句说明
- parti_num：二级哈希分区数目
- parti_name：二级哈希分区名称
2.3 示例
示例1
创建一个分区表，一级列表分区分区键为city，二级列表子分区分区键为addr。sqlSQL> CREATE TABLE tab_subparti_1 ( id INT, name VARCHAR, city VARCHAR, addr VARCHAR ) PARTITION BY LIST (city) PARTITIONS ( part1 VALUES ('重庆'), part2 VALUES ('北京'), part3 VALUES ('上海'), part4 VALUES (OTHERVALUES) ) SUBPARTITION BY LIST (addr) SUBPARTITIONS ( subpart1 VALUES ('青羊'), subpart2 VALUES ('武侯'), subpart3 VALUES ('金牛'), subpart4 VALUES ('高新'), subpart5 VALUES ('锦江') );示例2
创建一个分区表，一级列表分区分区键为city，二级范围子分区分区键为id。sqlSQL> CREATE TABLE tab_subparti_2 ( id INT, name VARCHAR, city VARCHAR, addr VARCHAR ) PARTITION BY LIST (city) PARTITIONS ( part1 VALUES ('成都'), part2 VALUES ('重庆'), part3 VALUES ('北京'), part4 VALUES (OTHERVALUES) ) SUBPARTITION BY RANGE (id) SUBPARTITIONS ( subpart1 VALUES LESS THAN (1), subpart2 VALUES LESS THAN (10), subpart3 VALUES LESS THAN (23), subpart4 VALUES LESS THAN (MAXVALUES) );示例3
创建一个分区表，一级列表分区分区键为address，二级哈希子分区分区键为sex。sqlSQL> CREATE TABLE tab_subparti_3 ( id INT IDENTITY(1,1) NOT NULL, name CHAR(8) NOT NULL, sex VARCHAR(5) NOT NULL, birthday DATETIME NOT NULL, address VARCHAR NOT NULL ) PARTITION BY LIST (address) PARTITIONS ( ('中国'), (OTHERVALUES) ) SUBPARTITION BY HASH (sex) SUBPARTITIONS 2;
三、自动拓展分区
自动拓展分区可按分区键时间间隔自动拓展新分区，适用于按时间较为均匀分布的数据，如运行日志，采集数据等。
注意
自动拓展分区仅支持一级分区，且分区键必须为时间数据类型。
3.1 语法格式
3.2 参数说明
- 未指定：创建为手动分区表，需手动拓展分区
INTERVALICONSTYEAR：创建为自动拓展分区表，分区间隔时间为 ICONST 年INTERVALICONSTMONTH：创建为自动拓展分区表，分区间隔时间为 ICONST 月INTERVALICONSTDAY：创建为自动拓展分区表，分区间隔时间为 ICONST 天INTERVALICONSTHOUR：创建为自动拓展分区表，分区间隔时间为 ICONST 小时
3.3 示例
示例1
创建一个自动拓展分区表，分区键为create_time，首分区条件值为1970-01-01 00:00:00，小于此时间的数据均会存储于此分区，大于或等于此时间的数据，以5天为间隔分区。sql-- 创建包含固定分区的自动扩展分区表 SQL> CREATE TABLE tab_subparti_4( id INT, create_time DATETIME ) PARTITION BY RANGE (create_time) INTERVAL 5 DAY PARTITIONS (('1970-01-01 00:00:00')); -- 查看表分区信息 SQL> SELECT p.* from sys_partis p INNER JOIN sys_tables t USING(table_id) WHERE t.table_name='tab_subparti_4'; +-------+----------+------------+-----------------------+----------+--------+-----------+-----------+ | DB_ID | PARTI_NO | PARTI_NAME | PARTI_VAL | GSTO_NOS | ONLINE | RESERVED1 | RESERVED2 | +-------+----------+------------+-----------------------+----------+--------+-----------+-----------+ | 1 | 0 | PART1 | '1970-01-01 00:00:00' | 221 | T | <NULL> | <NULL> | +-------+----------+------------+-----------------------+----------+--------+-----------+-----------+
四、按分区访问数据
虚谷数据库针对分区表提供了按分区访问数据的功能，用户可以在查询语句中指定访问任意分区的数据。
访问方式为在需要按分区访问的表名后附加 opt_parti_clip_clause 子句即可。
4.1 语法格式
4.2 参数说明
- 未指定：不按分区访问数据
- parti_key_name：要访问分区的分区键名称
注意
通过PARTITION和SUBPARTITION关键字指定分区查找语法和PostgreSQL指定表别名以及输出字段名语法冲突，设计上表现为不抛出语法错误。若要查询不同分区数据，应严格参照以上语法进行查询。
4.3 示例
示例1
分别指定查询分区的数据。sql-- 创建一级分区表 SQL> CREATE TABLE tab_parti_query_1 ( id INT, name VARCHAR, city VARCHAR ) PARTITION BY LIST(city) PARTITIONS( part1 VALUES('成都'), part2 VALUES('重庆'), part3 VALUES('北京'), part4 VALUES(OTHERVALUES) ); -- 插入数据 SQL> INSERT INTO tab_parti_query_1 VALUES(1,'路人1','成都'); SQL> INSERT INTO tab_parti_query_1 VALUES(2,'路人2','重庆'); SQL> INSERT INTO tab_parti_query_1 VALUES(3,'路人3','重庆'); SQL> INSERT INTO tab_parti_query_1 VALUES(4,'路人4','成都'); SQL> INSERT INTO tab_parti_query_1 VALUES(5,'路人5','成都'); SQL> INSERT INTO tab_parti_query_1 VALUES(6,'路人6','北京'); -- 查询part1分区的数据并按id排序 SQL> SELECT id,name,city FROM tab_parti_query_1 PARTITION(part1) ORDER BY id; +----+-------+------+ | ID | NAME | CITY | +----+-------+------+ | 1 | 路人1 | 成都 | | 4 | 路人4 | 成都 | | 5 | 路人5 | 成都 | +----+-------+------+ -- 查询part1分区和part3分区的数据并按id排列 SQL> SELECT id,name,city FROM tab_parti_query_1 PARTITION(part1,part3) ORDER BY id; +----+-------+------+ | ID | NAME | CITY | +----+-------+------+ | 1 | 路人1 | 成都 | | 4 | 路人4 | 成都 | | 5 | 路人5 | 成都 | | 6 | 路人6 | 北京 | +----+-------+------+示例2
分别指定查询二级分区的数据。sql-- 创建两级分区表 SQL> CREATE TABLE tab_parti_query_2(id INT,name VARCHAR,city VARCHAR,addr VARCHAR) PARTITION BY LIST(city) PARTITIONS( part1 VALUES('成都'), part2 VALUES('北京'), part3 VALUES(OTHERVALUES) ) SUBPARTITION BY LIST(addr) SUBPARTITIONS( subpart1 VALUES ('高新区'), subpart2 VALUES ('锦江区'), subpart3 VALUES ('西城区'), subpart4 VALUES ('东城区') ); -- 插入数据 SQL> INSERT INTO tab_parti_query_2 VALUES(1,'路人1','成都','高新区'); SQL> INSERT INTO tab_parti_query_2 VALUES(2,'路人2','成都','锦江区'); SQL> INSERT INTO tab_parti_query_2 VALUES(3,'路人3','成都','锦江区'); SQL> INSERT INTO tab_parti_query_2 VALUES(4,'路人4','北京','西城区'); SQL> INSERT INTO tab_parti_query_2 VALUES(5,'路人5','北京','西城区'); SQL> INSERT INTO tab_parti_query_2 VALUES(6,'路人6','北京','东城区'); -- 查询subpart2的数据并按id排列 SQL> SELECT id,name,city,addr FROM tab_parti_query_2 SUBPARTITION(subpart2) ORDER BY id; +----+-------+------+--------+ | ID | NAME | CITY | ADDR | +----+-------+------+--------+ | 2 | 路人2 | 成都 | 锦江区 | | 3 | 路人3 | 成都 | 锦江区 | +----+-------+------+--------+ -- 查询subpart2和subpart3的数据并按id排列 SQL> SELECT id,name,city,addr FROM tab_parti_query_2 SUBPARTITION(subpart2,subpart3) ORDER BY id; +----+-------+------+--------+ | ID | NAME | CITY | ADDR | +----+-------+------+--------+ | 2 | 路人2 | 成都 | 锦江区 | | 3 | 路人3 | 成都 | 锦江区 | | 4 | 路人4 | 北京 | 西城区 | | 5 | 路人5 | 北京 | 西城区 | +----+-------+------+--------+