瀚高数据库4.0分区表介绍

你的名字 2022-01-25 18:41 422阅读 0赞

**HGDB4.0** **分区表使用介绍**

**分区表概述**

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据分类存放到多个表空间的物理文件上，这样查询数据时，不至于每次都扫描整张表。

事实上，分区表的实现是在继承表的基础上实现的，所以分区表的子分区实质上是作为主表的子表对象存在的。所以继承表的特性，分区表一般都会有继承。比如分区表的子分区也会继承主表的非空约束和检查约束等。

分区表作为数据库的一项重要功能，不仅能带来访问速度上的提高，还能带来管理和维护上的方便。其具体优点表现为：

1.  分区表能够提高检索性能，特别是当一些频繁检索的记录存在同一个分区中时，这时检索速度能够大幅度提高。
2.  便于管理，分区表中的各个分区是相互独立的，可以根据需要增加或删除分区；当某个分区出现故障或删除，不会影响其他分区。
3.  可以根据需要，把不同访问频率的数据放到不同的访问速度介子中。

**分区表特性**

1.  **分区表的****KEY**

分区表的KEY的概念是表中一列或多个列组成的列的集合，该集合用于设定分区表的分区规则。所谓分区规则就是为分区表中的每个分区定义不同的约束条件，当向分区表中插入数据时，分区规则就会根据这些约束条件决定向哪个分区中插入。

1.  **分区表的分类**

目前HGDB4.0可以支持范围分区（range partition）、列表分区（list partition）和哈希分区（hash partition）三种分区。创建分区表时，具体使用哪种分区，具体要看建表的使用场景或要设置为KEY的字段属性来决定。下面具体介绍三种分区。

1.  1.  **范围分区（range partition****）**

在范围分区中，分区规则根据KEY按照不同的范围映射到不同的分区中，每个分区的范围都是由一个上限值和下限值来决定，这两个值是在创建分区表时定义的。

范围分区的KEY可由多个列（最多可由32个列）组成。值得一提的是，当KEY由

多个列组成时，除了第一列的各分区约束范围之间不能重叠外，其他列的分区范围可以         有重叠。

范围分区适用于组成KEY是连续的，可以根据需要设定上下限的列。实际上，在分区表的应用中，范围分区是最普遍，最常用的分区类型。

HGDB4.0分区表的创建实行主表和子分区单独创建。

创建主表语法为：

*CREATE TABLE* *表名 ( \[\{* *列名称**数据\_**类型\} \[, ... \] \] )*

*PARTITION BY RANGE ( \[\{* *列名称 \} \[, ...\] \] );*

语法参数：

1.  PARTITION BY RANGE 关键字，表示进行范围分区
2.  关键字后面跟的多个列名称是组成的KEY

创建子分区的语法：

*CREATE TABLE* *表名 PARTITION OF* *父表 FOR VALUES*

*FROM\{ (* *表达式 \[, ...\] ) | UNBOUNDED \} \[ INCLUSIVE | EXCLUSIVE \]*

*TO \{ (* *表达式 \[, ...\] ) | UNBOUNDED \} \[ INCLUSIVE | EXCLUSIVE \]*

*\[ TABLESPACE* *表空间名 \];*

语法参数：

1.  1.  1.  1.  1.  FROM...TO ... 表示范围分区约束的起始值和结束值。
                2.  UNBOUNDED关键字表示无限大或无限小。
                3.  INCLUSIVE关键字表示约束范围包括上下限值，EXCLUSIVE表示不包括。创建分区时，FROM默认是INCLUSIVE，TO默认是EXCLUSIVE。

范围分区示例 2-1

*CREATE TABLE stu (stu\_id int, stu\_name name, stu\_score int) PARTITION BY RANGE(stu\_score);*

*CREATE TABLE stu\_1 PARTITION OF stu FOR VALUES FROM (60) * TO *(70);*

*CREATE TABLE stu\_2 PARTITION OF stu FOR VALUES FROM (70) * TO *(80);*

*CREATE TABLE stu\_3 PARTITION OF stu FOR VALUES FROM (80) * TO *(90);*

*CREATE TABLE stu\_4 PARTITION OF stu FOR VALUES FROM (90) * TO *(100) INCLUSIVE;*

范围分区示例2-2

*CREATE TABLE stu (stu\_id int, stu\_name name, stu\_score int) PARTITION BY RANGE(stu\_id, stu\_score);*

*CREATE TABLE stu\_1 PARTITION OF stu FOR VALUES FROM(1, 60) TO (10, 70);*

*CREATE TABLE stu\_2 PARTITION OF stu FOR VALUES FROM(10, 65) TO (20, 75);*

*CREATE TABLE stu\_3 PARTITION OF stu FOR VALUES FROM(20, 70) TO (30, 80);*

*CREATE TABLE stu\_4 PARTITION OF stu FOR VALUES FROM(30, 80) TO (40, 90);*

*示例2-2**的分区表的KEY**是由两个列组成的，分区stu\_1**和stu\_2**的stu\_score**字段的范围约束有重叠，这种情况是允许的，只要第一个字段的分区范围没有重叠即可。*

1.  1.  **列表分区（list partition****）**

列表分区的KEY只能包含一个。

在列表分区中，分区规则是由KEY的一系列离散值组成。也就是向列表分区插入数据时，只有等于这些离散值时才会执行插入。

很明显，当KEY值是非连续的，离散的时候，创建分区表比较适合列表分区。

创建主表语法：

*CREATE TABLE* *表名 ( \[\{* *列名称**数据\_**类型\} \[, ... \] \] )*

*PARTITION BY LIST( \{* *列名称 \} );*

语法参数：

1.  PARTITION BY LIST关键字，表示进行列表分区
    
    1.  1.  1.  列表分区的KEY只能由一列组成

创建子分区语法：

*CREATE TABLE* *表名 PARTITION OF* *父表 FOR VALUES*

*IN (* *表达式 \[, ...\] ) \[ TABLESPACE* *表空间名 \];*

语法参数：

1.  IN 表示列表分区约束的离散值。

列表分区示例2-3

*CREATE TABLE sales (product\_id int, saleroom int, province text) PARTITION BY LIST(province);*

*CREATE TABLE sales\_east PARTITION OF sales FOR VALUES IN ('**山东','**江苏','**上海');*

*CREATE TABLE sales\_west PARTITION OF sales FOR VALUES IN ('**山西','**陕西','**四川');*

*CREATE TABLE sales\_north PARTITION OF sales FOR VALUES IN ('**北京','**河北','**辽宁');*

*CREATE TABLE sales\_south PARTITION OF sales FOR VALUES IN ('**广东','**福建');*

1.  1.  **哈希分区（hash partition****）**

所谓哈希分区就是分区规则将分区创建的KEY根据内部的哈希运算结果然后映射到相应的分区中去。具体的映射关系为，插入数据的KEY值在进行哈希运算等算法后会返回一个序号值，然后拿该序号值和创建分区时给分区分配的序号值比较，相等则插入相应的分区。

哈希分区的KEY目前仅支持一个字段。

HASH分区适用于无法有效划分分区范围，即不适用于范围分区和列表分区的场景，比如由很长的字符串组成的ID号，并且查询时以ID做为查询条件之一，那么此时就比较合适对其进行哈希分区。哈希分区还有一个用途就是起到对数据进行平均分配到各个分区中的效果，特别是在大数据量且KEY值没有重复的情况下。

创建主表语法：

*CREATE TABLE* *表名 ( \[\{* *列名称**数据\_**类型\} \[, ... \] \] )*

*PARTITION BY HASH( \{* *列名称 \} );*

语法参数：

1.  PARTITION BY HASH关键字，表示进行哈希分区

创建子分区语法：

*CREATE TABLE* *表名 PARTITION OF* *父表 \[ TABLESPACE* *表空间名 \]**；*

哈希分区示例2-4

*CREATE TABLE employee(employee\_id text, employee\_name name, department int) PARTITION BY HASH(employee\_id);*

*CREATE TABLE employee\_1 PARTITION OF employee;*

*CREATE TABLE employee\_2 PARTITION OF employee;*

*CREATE TABLE employee\_3 PARTITION OF employee;*

*CREATE TABLE employee\_4 PARTITION OF employee;*

1.  **分区表的系统表**
    
    1.  **系统表pg\_partitioned**

系统表pg\_partitioned用于记录分区表主表信息，详细信息参考*表**3-1*

*表3-1 pg\_partitioned**系统表*

<table style="margin-left:47.7pt;width:0px;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:92.25pt;"> 字段名 </td> 
 <td style="vertical-align:top;width:91.5pt;"> 数据类型 </td> 
 <td style="vertical-align:top;width:174pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:92.25pt;"> partedrelid </td> 
 <td style="vertical-align:top;width:91.5pt;"> oid </td> 
 <td style="vertical-align:top;width:174pt;"> 主表OID </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:92.25pt;"> partstrat </td> 
 <td style="vertical-align:top;width:91.5pt;"> char </td> 
 <td style="vertical-align:top;width:174pt;"> 分区表类型 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:92.25pt;"> partnatts </td> 
 <td style="vertical-align:top;width:91.5pt;"> int2 </td> 
 <td style="vertical-align:top;width:174pt;"> 分区表KEY值个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:92.25pt;"> partattrs </td> 
 <td style="vertical-align:top;width:91.5pt;"> int2vector </td> 
 <td style="vertical-align:top;width:174pt;"> 组成KEY的字段序列号 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:92.25pt;"> partclass </td> 
 <td style="vertical-align:top;width:91.5pt;"> oidvector </td> 
 <td style="vertical-align:top;width:174pt;"> KEY值的数据类型操作符 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:92.25pt;"> partexprs </td> 
 <td style="vertical-align:top;width:91.5pt;"> pg_node_tree </td> 
 <td style="vertical-align:top;width:174pt;"> 一般函数计算KEY时的信息描述 </td> 
 </tr> 
 </tbody> 
</table>

*说明：在分区表类型中，r* *表示范围分区，l* *表示列表分区， h* *表示哈希分区。*

1.  1.  **系统表pg\_partition**

系统表pg\_partition用于记录分区表子分区信息，详细信息参考*表**3-2*

*表3-2 pg\_partition**系统表*

<table style="margin-left:48.45pt;width:0px;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:90pt;"> 字段名 </td> 
 <td style="vertical-align:top;width:93.75pt;"> 数据类型 </td> 
 <td style="vertical-align:top;width:174pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:90pt;"> partrelid </td> 
 <td style="vertical-align:top;width:93.75pt;"> oid </td> 
 <td style="vertical-align:top;width:174pt;"> 子分区OID </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:90pt;"> partbound </td> 
 <td style="vertical-align:top;width:93.75pt;"> pg_node_tree </td> 
 <td style="vertical-align:top;width:174pt;"> 分区KEY的描述信息 </td> 
 </tr> 
 </tbody> 
</table>

1.  **分区表的其他操作**
    
    1.  **增加分区**

由于分区表中创建主表和分区的语法是独立的，这里增加分区实际上就是再创建一个新的分区，语法就是上面创建子分区的语法。对于范围分区和列表分区这里不再多说，对于哈希分区，有一点需要注意的是，如果哈希分区表中原来有数据，那么再增加新的分区后，原有数据会有一个重新分配的过程。这种重分配过程是在增加分区时自动完成的。当增加分区时，表对象的写操作，如INSERT,UPDATE,DELETE,VACUUM,CREATE INDEX等会被阻塞；读操作，如SELECT则没有影响。鉴于这种情况，建议在创建哈希分区时，就要确定好分区个数，后面不要频繁的增加分区，否则会影响数据库的性能和操作。

为什么会有重分配过程？ 因为前面提到的哈希分区的分区规则除了和内部哈希算法有关外，还和分区数有关。而检索数据时，具体定位到哪个分区进行检索，内部算法是和插入时一样的。如果说增加分区后没有数据的重分配，则在对原有数据进行检索时，检索分区和数据所在的分区可能会对应不上。

1.  1.  **ATTACH** **普通表**

ATTACH操作就是把一个普通表变为分区表的一个子分区，当然前提是该普通表和分区表有着相同的表结构。

目前范围分区和列表分区支持该操作，哈希分区还不支持。

范围分区ATTACH语法：

*ALTER TABLE* *分区表名 ATTACH PARTITION* *表名 FOR VALUES*

*FROM\{ (* *表达式 \[, ...\] ) | UNBOUNDED \} \[ INCLUSIVE | EXCLUSIVE \]*

*TO \{ (* *表达式 \[, ...\] ) | UNBOUNDED \} \[ INCLUSIVE | EXCLUSIVE \]*

*\[ VALIDATE | NO VALIDATE \];*

语法参数：

1.  VALIDATE关键字表示是否校验ATTACH普通表中的数据。NO VALIDATE是不校验。默认操作是VALIDATE。

范围分区ATTACH操作示例4-1

*CREATE TABLE stu\_attach(stu\_id int, stu\_name name, stu\_score int) ;*

*ALTER TABLE stu ATTACH PARTITION stu\_attach FOR VALUES FROM (50) TO (60);*

列表分区ATTACH语法：

*ALTER TABLE* *分区表名 ATTACH PARTITION* *表名 FOR VALUES*

*IN (* *表达式 \[, ...\] )*

*\[ VALIDATE | NO VALIDATE \];*

*                * 列表分区ATTACH操作示例4-2

*CREATE TABLE sales\_foreign (product\_id int, saleroom int, province text) ;*

*ALTER TABLE sales ATTACH PARTITION sales\_foreign FOR VALUES IN('**美国','**日本');*

1.  1.  **DETACH** **分区**

DETACH是ATTACH的反操作。起作用就是把分区表的子分区变成一个普通表。

同样范围分区和列表分区支持该操作，哈希不支持。

其语法为：

*ALTER TABLE* *分区表名 DETACH PARTITION* *分区名;*

DETACH操作示例4-3

*ALTER TABLE stu DETACH PARTITION stu\_attach;*

*ALTER TABLE sales DETACH PARTITION sales\_foreign;*

1.  1.  **删除分区**

目前分区表不支持直接删除分区的操作。对于范围分区和列表分区，需要先进行DETACH操作把要删除的子分区变为一个普通表的形式，然后再以普通表的形式删除。哈希分区不支持删除分区的操作，只能对整个分区表删除。

删除分区示例4-4

*ALTER TABLE stu DETACH PARTITION stu\_4;*

*DROP TABLE stu\_4;*

**5.** **分区表的性能**

HGDB4.0分区表摒弃了之前版本插入数据时采用的内置触发器，而是在内核中采用直接插入数据，节省了语法分析和优化的过程，因而比内置触发器更高效。

此外HGDB4.0分区表的配置信息都是写在缓存中的，而不是再从磁盘中读取，读数据的时候直接从缓冲中读取进行判断，节省了I/O时间，因而HGDB4.0分区表在数据写入方面效率更高。

在有些应用场景下，我们可以根据需要来提高性能。例如在创建哈希分区时，如果数据频繁的检索，而写的频率不大，那么在一定量的数据情况下，分区数越多，检索效率越大。因为条件检索消耗的时间都是在某个分区上的数据遍历。因此可以创建更多的分区来提高效率。

1.  **分区表的使用限制**
    
    1.  目前HGDB4.0分区表还不支持子分区能继承主表的索引，这一点是继承了继承表的特性。所以要想在分区表上使用索引，需要对各个子分区分别创建索引。
    2.  分区表在插入数据时，范围分区和哈希分区，KEY值不能为NULL。
    3.  分区表不支持全局主键约束、唯一约束、外键约束及排他约束，可在子分区上单独创建相关约束及索引。
    4.  分区表不支持复合分区。
    5.  分区表不支持垂直分区。
    6.  不支持把普通表变成分区表；反之亦然。
    7.  分区表不支持被继承。