# 创建和管理DWS分区表 分区表就是把逻辑上的一张表根据分区策略分成几张物理块库进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。当进行条件查询时，系统只会扫描满足条件的分区，避免全表扫描，从而提升查询性能。 关于分区表维护的详细语法介绍请参见[ALTER TABLE PARTITION](https://support.huaweicloud.com/sqlreference-dws/dws_06_0143.html)。 分区表的优势： - 改善查询性能。对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。 - 增强可用性。如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。 - 提升可维护性。对于需要周期性删除的过期历史数据，可以通过drop/truncate分区的方式快速高效处理。 #### 支持的表分区类型 - 范围分区（Range Partitioning），基于一个数值型范围划分数据，例如按日期或价格区间定义。 - 列表分区（List Partitioning），基于一个值列表划分数据，例如按销售范围或产品属性定义。仅8.1.3及以上集群版本支持。 #### 分区策略选择 当表有以下特征时，可以考虑使用表分区策略： - 数据具有明显区间性的字段。 分区表需要根据有明显区间性字段进行表分区。比如按照日期、区域、数值等字段进行分区，时间字段是最常见的分区字段。 - 业务查询有明显的区间范围特征。 查询数据可落到区间范围指定的分区内，这样才能通过分区剪枝，只扫描查询需要的分区，从而提升数据扫描效率，降低数据扫描的IO开销。 - 表数据量比较大。 小表扫描本身耗时不大，分区表的性能收益不明显，因此只建议对大表采取分区策略。列存储模式下因为每个列是单独的文件存储，且最小的存储单元CU可存储6w行数据，因此对于列存分区表，建议每个分区的数据不小于DN个数\*6w。 #### 创建范围(range)分区表 示例：创建一个按wr_returned_date_sk范围分区的表web_returns_p1。 ``` CREATE TABLE web_returns_p1 ( wr_returned_date_sk integer, wr_returned_time_sk integer, wr_item_sk integer NOT NULL, wr_refunded_customer_sk integer ) WITH (orientation = column) DISTRIBUTE BY HASH (wr_item_sk) PARTITION BY RANGE (wr_returned_date_sk) ( PARTITION p2016 VALUES LESS THAN(20161231), PARTITION p2017 VALUES LESS THAN(20171231), PARTITION p2018 VALUES LESS THAN(20181231), PARTITION p2019 VALUES LESS THAN(20191231), PARTITION pxxxx VALUES LESS THAN(maxvalue) ); ``` 对于分区间隔固定、批量创建分区的场景。可使用如下示例： ``` CREATE TABLE web_returns_p2 ( wr_returned_date_sk integer, wr_returned_time_sk integer, wr_item_sk integer NOT NULL, wr_refunded_customer_sk integer ) WITH (orientation = column) DISTRIBUTE BY HASH (wr_item_sk) PARTITION BY RANGE(wr_returned_date_sk) ( PARTITION p2016 START(20161231) END(20191231) EVERY(10000), PARTITION p0 END(maxvalue) ); ``` 示例：创建一个按时间日期作为分区的表web_returns_p2，其中time作为分区键。 ``` CREATE TABLE web_returns_p2 ( id integer, idle numeric, IO numeric, scope text, IP text, time timestamp ) WITH (TTL='7 days',PERIOD='1 day') PARTITION BY RANGE(time) ( PARTITION P1 VALUES LESS THAN('2022-01-05 16:32:45'), PARTITION P2 VALUES LESS THAN('2022-01-06 16:56:12') ); ``` #### 创建列表(list)分区表 LIST分区表可以使用任意允许值比较的列作为分区键列。创建LIST分区表时，必须要为每一个分区声明每一个值分区。 示例：创建LIST分区表sales_info。 ``` CREATE TABLE sales_info ( sale_time timestamptz, period int, city text, price numeric(10,2), remark varchar2(100) ) DISTRIBUTE BY HASH(sale_time) PARTITION BY LIST (period, city) ( PARTITION province1_202201 VALUES (('202201', 'city1'), ('202201', 'city2')), PARTITION province2_202201 VALUES (('202201', 'city3'), ('202201', 'city4'), ('202201', 'city5')) ); ``` #### 对已有的表进行分区 表只能在创建时被分区。 如果用户有一个表想要分区，用户必须创建一个分过区的表，把原始表的数据载入到新表，再删除原始表并且把分过区的表重命名为原始表的名称。 用户还必须重新授权表上的权限。例如： ``` CREATE TABLE web_returns_p2 ( wr_returned_date_sk integer, wr_returned_time_sk integer, wr_item_sk integer NOT NULL, wr_refunded_customer_sk integer ) WITH (orientation = column) DISTRIBUTE BY HASH (wr_item_sk) PARTITION BY RANGE(wr_returned_date_sk) ( PARTITION p2016 START(20161231) END(20191231) EVERY(10000), PARTITION p0 END(maxvalue) ); ``` ``` INSERT INTO web_returns_p2 SELECT * FROM web_returns_p1; DROP TABLE web_returns_p1; ALTER TABLE web_returns_p2 RENAME TO web_returns_p1; GRANT ALL PRIVILEGES ON web_returns_p1 TO dbadmin; GRANT SELECT ON web_returns_p1 TO jack; ``` #### 增加一个分区 使用ALTER TABLE语句为范围分区表增加一个分区。例如，为表web_returns_p1增加分区P2020。 ``` ALTER TABLE web_returns_p1 ADD PARTITION P2020 VALUES LESS THAN (20201231); ``` 为列表分区增加一个分区。例如，为表sales_info增加分区province3_202201。 ``` ALTER TABLE sales_info ADD PARTITION province3_202201 VALUES (('202201', 'city6'), ('202201', 'city7')); ``` #### 分割一个分区 范围分区表和列表分区表分割分语法有所区别： - 使用ALTER TABLE语句为范围分区表分割一个分区。例如，将表*web_returns_p1* 分区*pxxxx* 以20201231为分割点分割为p2020和p20xx两个分区。 ``` ALTER TABLE web_returns_p1 SPLIT PARTITION pxxxx AT(20201231) INTO (PARTITION p2020,PARTITION p20xx); ``` - 使用ALTER TABLE语句为列表分区表分割一个分区。例如，将表*sales_info* 分区*province2_202201* 分割为*province3_202201* 和*province4_202201* 两个分区。 ``` ALTER TABLE sales_info SPLIT PARTITION province2_202201 VALUES(('202201', 'city5')) INTO (PARTITION province3_202201,PARTITION province4_202201); ``` #### 合并一个分区 使用ALTER TABLE语句为分区表合并一个分区。例如，将表*web_returns_p1* 分区p2016和p2017合并为一个分区p20162017。 ``` ALTER TABLE web_returns_p1 MERGE PARTITIONS p2016,p2017 INTO PARTITION p20162017; ``` #### 删除一个分区 使用ALTER TABLE语句从分区表中删除一个分区。例如，删除表web_returns_p1的分区P2020。 ``` ALTER TABLE web_returns_p1 DROP PARTITION P2020; ``` #### 查询分区 - 查询分区p2019。 ``` SELECT * FROM web_returns_p1 PARTITION (p2019); SELECT * FROM web_returns_p1 PARTITION FOR (20201231); ``` - 查看分区表信息，可使用系统表dba_tab_partitions。 ``` SELECT * FROM dba_tab_partitions where table_name='web_returns_p1'; ``` #### 删除分区表 使用DROP TABLE语句删除一个分区表。 ``` DROP TABLE web_returns_p1; ``` #### 设置分区索引是否可用 给分区表customer_address创建LOCAL索引student_grade_index，并指定分区的索引名称。 ``` CREATE INDEX customer_address_index ON customer_address(ca_address_id) LOCAL ( PARTITION P1_index, PARTITION P2_index, PARTITION P3_index ); ``` 重建分区表customer_address中分区P1上的所有索引。 ``` ALTER TABLE customer_address MODIFY PARTITION P1 REBUILD UNUSABLE LOCAL INDEXES; ``` 设置分区表customer_address的分区P3上的所有索引不可用。 ``` ALTER TABLE customer_address MODIFY PARTITION P3 UNUSABLE LOCAL INDEXES; ``` #### 重建分区索引 长期运行的分区表，索引可能产生碎片，重建索引，可提升查询效率，以下演示分区插入数据后重建分区索引。 1. 创建分区表。 ``` DROP TABLE IF EXISTS sales; CREATE TABLE sales ( id INT, sale_date DATE, amount DECIMAL(10,2) ) PARTITION BY RANGE (sale_date) ( PARTITION p202310 VALUES LESS THAN ('2023-11-01'), PARTITION p202311 VALUES LESS THAN ('2023-12-01') ); ``` 2. 创建索引。 ``` CREATE INDEX idx_sale_date ON sales (sale_date) LOCAL; ``` 3. 插入10月、11月分区数据。 ``` INSERT INTO sales PARTITION (p202310) VALUES (1, '2023-10-05', 100.50), (2, '2023-10-10', 200.75); INSERT INTO sales PARTITION (p202311) VALUES (3, '2023-11-15', 300.00); ``` 4. 重建特定分区p202310的索引。 ``` ALTER TABLE sales REBUILD PARTITION p202310 WITHOUT UNUSABLE; ``` #### 交换分区（EXCHANGE PARTITION） 如下示例将演示一个普通表math_grade数据迁移到分区表student_grade中分区（math）的操作。 1. 创建分区表student_grade。 ``` DROP TABLE IF EXISTS student_grade; CREATE TABLE student_grade ( stu_name char(5), stu_no integer, grade integer, subject varchar(30) ) PARTITION BY LIST(subject) ( PARTITION gym VALUES('gymnastics'), PARTITION phys VALUES('physics'), PARTITION history VALUES('history'), PARTITION math VALUES('math') ); ``` 2. 插入数据到分区表student_grade中。 ``` INSERT INTO student_grade VALUES ('Ann', 20220101, 75, 'gymnastics'), ('Jack', 20220103, 60, 'math'), ('Anna', 20220108, 56, 'history'), ('John', 20220107, 82, 'physics'), ('Molly', 20220104, 91, 'physics'), ('Sam', 20220105, 72, 'math'); ``` 3. 查询分区表student_grade的math分区记录。 ``` SELECT * FROM student_grade PARTITION (math); ``` 查询结果如下： ![](https://support.huaweicloud.com/devg-dws/figure/zh-cn_image_0000002568591613.png "点击放大") 4. 创建一个与分区表student_grade定义匹配的普通表math_grade。 ``` DROP TABLE IF EXISTS math_grade; CREATE TABLE math_grade ( stu_name char(5), stu_no integer, grade integer, subject varchar(30) ); ``` 5. 插入数据到表math_grade中。数据与分区表student_grade的math分区的分区规则一致。 ``` INSERT INTO math_grade VALUES ('Ann', 20220101, 75, 'math'), ('Jack', 20220103, 60, 'math'), ('Anna', 20220108, 56, 'math'), ('John', 20220107, 82, 'math'); ``` 6. 将普通表math_grade数据迁移到分区表student_grade的分区（math）。 ``` ALTER TABLE student_grade EXCHANGE PARTITION (math) WITH TABLE math_grade; ``` 7. 查询分区表student_grade，结果显示表math_grade中的数据已和分区表student_grade的分区math中的数据交换。 ``` SELECT * FROM student_grade PARTITION (math); ``` ![](https://support.huaweicloud.com/devg-dws/figure/zh-cn_image_0000002537513428.png) 8. 查询表math_grade，显示之前存储在分区表student_grade的分区math中的数据已交换到表math_grade中。 ``` SELECT * FROM math_grade; ``` ![](https://support.huaweicloud.com/devg-dws/figure/zh-cn_image_0000002537673762.png) #### 分区表业务锁 分区表的不同业务最终都是作用于目标分区上，数据库会给分区表和目标分区施加不同级别的表锁+分区锁，来控制并发行为。 表1锁模式间的冲突关系 | 锁模式名称 | 锁等级 | 锁用途 | 冲突关系 | |:---|:---|:---|:---| | 访问共享锁（AccessShareLock） | 1 | SELECT语句，允许其他事务读取数据。 | 8 | | 行共享锁（RowShareLock） | 2 | SELECT FOR UPDATE或FOR SHARE，允许其他事务读取但阻止写入。 | 7\|8 | | 行排他锁（RowExclusiveLock） | 3 | INSERT、UPDATE、DELETE语句。 | 5\|6\|7\|8 | | 共享更新排他锁（ShareUpdateExclusiveLock） | 4 | VACUUM（非FULL）、ANALYZE、CREATE INDEX CONCURRENTLY、COMMENT ON，允许其他事务读取但阻止写入。 | 4\|5\|6\|7\|8 | | 共享锁（ShareLock） | 5 | CREATE INDEX（非CONCURRENTLY），允许其他事务读取但阻止写入。 | 3\|4\|6\|7\|8 | | 共享行排他锁（ShareRowExclusiveLock） | 6 | ROW SELECT...FOR UPDATE，类似RowExclusiveLock，但允许RowShareLock。 | 3\|4\|5\|6\|7\|8 | | 排他锁（ExclusiveLock） | 7 | 阻止RowShareLock或SELECT ... FOR UPDATE操作。 | 2\|3\|4\|5\|6\|7\|8 | | 访问排他锁（AccessExclusiveLock） | 8 | ALTER TABLE、DROP TABLE、VACUUM FULL，完全阻止其他事务读取和写入。 | 1\|2\|3\|4\|5\|6\|7\|8 | 分区表通过表锁+分区锁两重设计，在表和分区上分别施加不同级别的常规锁，来保证DQL、DML、DDL并发过程中的行为控制。有关分区表锁粒度参考下表： 表2分区表业务锁粒度 | 执行语句 | 表锁+分区锁 | |:---|:---| | SELECT全表 | 执行CN主表1级锁+所有分区子表1级锁（列存不会加子表锁） | | SELECT全表 | DN主表1级锁+所有分区子表1级锁 | | SELECT子分区 | 执行CN主表1级锁+分区子表1级锁 | | SELECT子分区 | DN主表1级锁+分区子表1级锁 | | INSERT/UPDATE/DELETE主表 | 执行CN主表3级锁 | | INSERT/UPDATE/DELETE主表 | 主表3级锁+分区子表3级锁 | | INSERT/UPDATE/DELETE子表 | 执行CN主表3级锁+分区子表3级锁 | | INSERT/UPDATE/DELETE子表 | 主表3级锁+分区子表3级锁 | | 访问分区子表触发的light runtime analyze | 执行CN主表1级锁+分区子表1级锁 | | 访问分区子表触发的light runtime analyze | 主表1级锁+所有分区子表1级锁（其他子分区及锁用完即放） | | 访问分区子表触发的normal runtime analyze | 执行CN主表4级锁 | | 访问分区子表触发的normal runtime analyze | 主表4级锁+所有分区子表4级锁 | | ADD PARTITION | CN主表3级锁+分区子表8级锁+集群锁 | | ADD PARTITION | 主表3级锁+分区子表8级锁 | | SPLIT/MERGE/DROP PARTITION | 主表8级锁+分区子表8级锁+集群锁 | | SPLIT/MERGE/DROP PARTITION | 主表8级锁+分区子表8级锁 | | TRUNCATE PARTITION | CN主表3级锁+分区子表8级锁 | | TRUNCATE PARTITION | 主表3级锁+分区子表8级锁 | | EXCHANGE PARTITION | CN主表3级锁+分区子表8级锁 | | EXCHANGE PARTITION | 主表3级锁+分区子表8级锁 |