干货分享｜UXDB优炫数据库分区表创建和使用

优炫软件 2021-08-13

1227

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

UXDB支持两种分区表，传统分区表与内置分区表。分区表的优势主要体现在降低大表管理成本和某些场景的性能提升。本次我们将与大家分享内置分区表。

内置分区表

支持内置分区表是UXDB重量级新特性，用户不需要预先在父表上定义INSERT、 DELETE、 UPDATE触发器，对父表的DML操作会自动路由到相应分区，相比传统分区表大幅度降低了维护成本，目前仅支持范围分区和列表分区。

创建分区表

创建分区表的主要语法包含两部分：创建主表和创建分区。创建主表时须指定分区方式，可选的分区方式为RANGE范围分区或LIST列表分区，并指定宇段或表达式作为分区键。创建分区时必须指定是哪张表的分区，同时指定分区策略partition_bound_ spec，如果是范围分区，partition_bound_spec须指定每个分区分区键的取值范围，如果是列表分区partition_ bound_ spec，需指定每个分区的分区键值。

1 创建父表，指定分区键和分区策略。

CREATE TABLE log_par( id serial , user_id int4 , create_time timestamp(0) without time zone ) PARTITION BY RANGE(create_time);

2 创建分区，创建分区时须指定分区表的父表和分区键的取值范围，注意分区键的范围不要有重叠，否则会报错。

表log_par指定了分区策略为范围分区，分区键为create_time宇段。创建分区，并设置分区的分区键取值范围。

create table log_par_his partition of log_par for values from (MINVALUE) to ('2020-01-01');

create table log_par_202001 partition of log_par for values from ('2020-01-01') to (' 2020-02-01'); create table log_par_202002 partition of log_par for values from ('2020-02-01') to (' 2020-03-01');create table log_par_202003 partition of log_par for values from ('2020-03-01') to (' 2020-04-01');create table log_par_202004 partition of log_par for values from ('2020-04-01') to (' 2020-05-01');create table log_par_202005 partition of log_par for values from ('2020-05-01') to (' 2020-06-01');create table log_par_202006 partition of log_par for values from ('2020-06-01') to (' 2020-07-01');create table log_par_202007 partition of log_par for values from ('2020-07-01') to (' 2020-08-01');create table log_par_202008 partition of log_par for values from ('2020-08-01') to (' 2020-09-01');create table log_par_202009 partition of log_par for values from ('2020-09-01') to (' 2020-10-01');create table log_par_202010 partition of log_par for values from ('2020-10-01') to (' 2020-11-01');create table log_par_202011 partition of log_par for values from ('2020-11-01') to (' 2020-12-01');create table log_par_202012 partition of log_par for values from ('2020-12-01') to (' 2021-01-01');/*MINVALUE原为UNBOUNDED，含义是没有边界，该常量在本版中无效*/

3 在分区上创建相应索引

通常情况下分区键上的索引是必须的，非分区键的索引可根据实际应用场景选择是否创建。

CREATE INDEX idx_log_par_his_ctime ON log_par_his USING btree(create_time);

CREATE INDEX idx_log_par_202001_ctime ON log_par_202001 USING btree(create_time); CREATE INDEX idx_log_par_202002_ctime ON log_par_202002 USING btree(create_time);CREATE INDEX idx_log_par_202003_ctime ON log_par_202003 USING btree(create_time);CREATE INDEX idx_log_par_202004_ctime ON log_par_202004 USING btree(create_time);CREATE INDEX idx_log_par_202005_ctime ON log_par_202005 USING btree(create_time);CREATE INDEX idx_log_par_202006_ctime ON log_par_202006 USING btree(create_time);CREATE INDEX idx_log_par_202007_ctime ON log_par_202007 USING btree(create_time);CREATE INDEX idx_log_par_202008_ctime ON log_par_202008 USING btree(create_time);CREATE INDEX idx_log_par_202009_ctime ON log_par_202009 USING btree(create_time);CREATE INDEX idx_log_par_202010_ctime ON log_par_202010 USING btree(create_time);CREATE INDEX idx_log_par_202011_ctime ON log_par_202011 USING btree(create_time);CREATE INDEX idx_log_par_202012_ctime ON log_par_202012 USING btree(create_time);