postgresql分分区表改分区表 pgsql 分区表

转载

mob64ca140f9cec 2024-04-05 08:14:57

文章标签 postgresql分分区表改分区表触发器分区表数据 文章分类 云原生云计算

一、分区表

在10版本前通过继承加上触发器实现分区表，步骤繁琐，10版本增加了内置分区，支持范围分区和列表分区。

1.1 分区表的意义

降低大表管理成本和某些场景的性能提升。

1.2 传统分区表

传统分区表通过继承和触发器方式实现，其实现过程步骤多，非常复杂，需要定义父表、定义子表、定义子表约束、创建子表索引、创建分区插入、删除、修改函数和触发器等

-- 创建父表
CREATE TABLE tb1_log(
	id int4,
	create_date date,
	log_type text
);

-- 子表继承父表。多增加一个字段sql
CREATE TABLE tb1_log_sql(sql text) inherits (tb1_log);

--父表和子表都可以插入数据

INSERT INTO tb1_log VALUES (1,'2018-11-22',null);

INSERT INTO tb1_log_sql VALUES(2,'2018-11-23',null,'select 2');

-- 这时如果查询父表tb1_log会显示两表的记录
SELECT * FROM tb1_log;
/*
id	create_date	log_type
1	2018-11-22	
2	2018-11-23	
*/

-- 如果想确定来源于哪张表，可以通过隐藏字段 tableoid。 获取
SELECT tableoid, * FROM tb1_log;

-- 通过连表查询
SELECT p.relname,c.*
FROM tb1_log c,pg_class p
WHERE c.tableoid = p.oid;
/*
relname	id	create_date	 log_type
tb1_log	1	  2018-11-22	
tb1_log_sql	2	2018-11-23	
*/


-- 如果只想查询父表的数据，需要在父表名称前加上关键字 ONLY
SELECT * FROM ONLY tb1_log;
/*
id	create_date	log_type
1	2018-11-22	
*/

--对于UPDATE、 DELETE 、SELECT 等，如果父表名称前没有加入ONLY、则会对父表和所有子表进行DML操作。

DELETE FROM tb1_log;

SELECT COUNT(*) FROM tb1_log;
-- 查询结果为0

对于使用了继承表的场景，对父表的UPDATE 、DELETE 的操作需要谨慎，因为会对父表和所有子表的数据进行DML操作。

1.3 创建分区表

通过继承加上触发器实现分区表。

步骤如下：

创建父表，如果父表定义约束，子表会继承，因此除非是全局约束，否则不应该在父表上定义约束；另外，父表不应该写入数据
通过 INHERITS(inherits) 方式创建继承表，也称之为子表或分区，子表的字段定义应该和父表保持一致。
给所有子表创建约束，只有满足约束条件的数据才能写入对应分区，注意分区约束值范围不要有重复。
在父表上定义insert、delete、update触发器，将SQL分发到对应分区，这步可选，因为应用可以根据分区规则定位到应用分区进行DML操作
启用constraint_exclusion参数，如果这个参数设置成off，则父表上的SQL性能会降低。

案例：创建一张范围分区表，并且定义年月子表存储月数据。

----分区表案例-----

--1.创建父表

CREATE TABLE log_ins(
	id serial,
	user_id int,
	create_time TIMESTAMP(0) WITHOUT time zone
);

--2.创建13张子表
CREATE TABLE log_ins_history(CHECK(create_time < '2017-01-01')) INHERITS(log_ins);
--(1-12)
CREATE TABLE log_ins_201701(CHECK(create_time >= '2017-01-01' AND create_time < '2017-02-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201702(CHECK(create_time >= '2017-02-01' AND create_time < '2017-03-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201703(CHECK(create_time >= '2017-03-01' AND create_time < '2017-04-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201704(CHECK(create_time >= '2017-04-01' AND create_time < '2017-05-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201705(CHECK(create_time >= '2017-05-01' AND create_time < '2017-06-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201706(CHECK(create_time >= '2017-06-01' AND create_time < '2017-07-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201707(CHECK(create_time >= '2017-07-01' AND create_time < '2017-08-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201708(CHECK(create_time >= '2017-08-01' AND create_time < '2017-09-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201709(CHECK(create_time >= '2017-09-01' AND create_time < '2017-10-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201710(CHECK(create_time >= '2017-10-01' AND create_time < '2017-11-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201711(CHECK(create_time >= '2017-11-01' AND create_time < '2017-12-01')) INHERITS(log_ins);
CREATE TABLE log_ins_201712(CHECK(create_time >= '2017-12-01' AND create_time < '2018-01-01')) INHERITS(log_ins);


--3.给子表创建索引
CREATE INDEX idx_log_ins_history_ctime ON log_ins_history USING btree(create_time);
CREATE INDEX idx_log_ins_201701_ctime ON log_ins_201701 USING btree(create_time);
CREATE INDEX idx_log_ins_201702_ctime ON log_ins_201702 USING btree(create_time);
CREATE INDEX idx_log_ins_201703_ctime ON log_ins_201703 USING btree(create_time);
CREATE INDEX idx_log_ins_201704_ctime ON log_ins_201704 USING btree(create_time);
CREATE INDEX idx_log_ins_201705_ctime ON log_ins_201705 USING btree(create_time);
CREATE INDEX idx_log_ins_201706_ctime ON log_ins_201706 USING btree(create_time);
CREATE INDEX idx_log_ins_201707_ctime ON log_ins_201707 USING btree(create_time);
CREATE INDEX idx_log_ins_201708_ctime ON log_ins_201708 USING btree(create_time);
CREATE INDEX idx_log_ins_201709_ctime ON log_ins_201709 USING btree(create_time);
CREATE INDEX idx_log_ins_201710_ctime ON log_ins_201710 USING btree(create_time);
CREATE INDEX idx_log_ins_201711_ctime ON log_ins_201711 USING btree(create_time);
CREATE INDEX idx_log_ins_201712_ctime ON log_ins_201712 USING btree(create_time);


--由于父表不存储数据，可以不用在父表上创建索引。

--4.创建触发器函数，设置数据插入父表时路由规则。如下所示：
CREATE OR REPLACE FUNCTION log_ins_insert_trigger() RETURNS TRIGGER	LANGUAGE PLPGSQL
AS $function$
BEGIN 
IF (NEW.create_time < '2017-01-01') THEN	
		INSERT INTO log_ins_history VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-01-01' AND NEW.create_time < '2017-02-01') THEN	
		INSERT INTO log_ins_201701 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-02-01' AND NEW.create_time < '2017-03-01') THEN	
		INSERT INTO log_ins_201702 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-03-01' AND NEW.create_time < '2017-04-01') THEN	
		INSERT INTO log_ins_201703 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-04-01' AND NEW.create_time < '2017-05-01') THEN	
		INSERT INTO log_ins_201704 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-05-01' AND NEW.create_time < '2017-06-01') THEN	
		INSERT INTO log_ins_201705 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-06-01' AND NEW.create_time < '2017-07-01') THEN	
		INSERT INTO log_ins_201706 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-07-01' AND NEW.create_time < '2017-08-01') THEN	
		INSERT INTO log_ins_201707 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-08-01' AND NEW.create_time < '2017-09-01') THEN	
		INSERT INTO log_ins_201708 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-09-01' AND NEW.create_time < '2017-10-01') THEN	
		INSERT INTO log_ins_201709 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-10-01' AND NEW.create_time < '2017-11-01') THEN	
		INSERT INTO log_ins_201710 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-11-01' AND NEW.create_time < '2017-12-01') THEN	
		INSERT INTO log_ins_201711 VALUES(NEW.*);
ELSEIF(NEW.create_time >= '2017-12-01' AND NEW.create_time < '2018-01-01') THEN	
		INSERT INTO log_ins_201712 VALUES(NEW.*);

ELSE 
	RAISE EXCEPTION 'create_time out of range. Fix the log_ins_insert_trigger() function!';

END IF;
RETURN NULL;

END;
$function$;

函数中 NEW.*是指要插入的数据行，在父表上定义插入触发器：

-- 在父表上定义插入触发器
CREATE TRIGGER insert_ins_trigger BEFORE INSERT ON log_ins FOR EACH ROW 
EXECUTE PROCEDURE log_ins_insert_trigger();

触发器创建完成后，往父表log_ins插入数据时，会执行触发器并触发器函数log_ins_insert_trigger() 将表数据插入到相应分区中。DELETE、 UPDATE触发器和函数创建过程和insert方法类似。

1.4 使用分区表

-- 往log_ins 插入数据测试，并验证是否插入对应分区。

INSERT INTO log_ins(user_id,create_time)
SELECT round(100000000*random()),generate_series('2016-12-01'::date,'2017-12-01'::date,'1 minute');

-- 通过round(100000000*random())随机生成8位整数，generate_series函数生成时间数据。


-- 查询
SELECT * FROM log_ins LIMIT 2;

-- 只查看父表

SELECT COUNT(*) FROM ONLY log_ins;
-- 大小为0

SELECT COUNT(*) FROM log_ins;
--525601

--- 查看子表数据
SELECT min(create_time),max(create_time) FROM log_ins_201701;

1.5 查询父表还是子表

假如检索 2017-01-01 这天的数据，是查询父表，还是查询子表。

--1.查询父表
EXPLAIN ANALYSE SELECT * FROM log_ins WHERE create_time > '2017-01-01' AND create_time < '2017-01-02';

Append  (cost=0.00..60.19 rows=1446 width=16) (actual time=0.052..0.546 rows=1439 loops=1)
  ->  Seq Scan on log_ins  (cost=0.00..0.00 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=1)
        Filter: ((create_time > '2017-01-01 00:00:00'::timestamp without time zone) AND (create_time < '2017-01-02 00:00:00'::timestamp without time zone))
  ->  Index Scan using idx_log_ins_201701_ctime on log_ins_201701  (cost=0.29..60.19 rows=1445 width=16) (actual time=0.037..0.403 rows=1439 loops=1)
        Index Cond: ((create_time > '2017-01-01 00:00:00'::timestamp without time zone) AND (create_time < '2017-01-02 00:00:00'::timestamp without time zone))
Planning time: 0.939 ms
Execution time: 0.652 ms

--2.查询子表
EXPLAIN ANALYSE SELECT * FROM log_ins_201701 WHERE create_time > '2017-01-01' AND create_time < '2017-01-02';

Index Scan using idx_log_ins_201701_ctime on log_ins_201701  (cost=0.29..60.19 rows=1445 width=16) (actual time=0.071..0.395 rows=1439 loops=1)
  Index Cond: ((create_time > '2017-01-01 00:00:00'::timestamp without time zone) AND (create_time < '2017-01-02 00:00:00'::timestamp without time zone))
Planning time: 0.149 ms
Execution time: 0.500 ms

其实，在实际生产过程中，对于传统分区表分区方式，不建议应用访问父表，而是直接访问子表。

1.6 constraint_exclusion 参数

constraint_exclusion

on 所有表都通过约束优化查询
off: 关闭
partition :只对继承表和UNION ALL子查询通过检查约束来优化查询；

如果设置成on或partition,查询父表时优化器会根据子表上的约束判断检索那些子表，而不需要扫描所有子表，从而提升查询性能。可以通过 set constraint_exclusion = off 查询执行计划便知。

set constraint_exclusion  = off;
EXPLAIN ANALYSE SELECT * FROM log_ins WHERE create_time > '2017-01-01' AND create_time < '2017-01-02';

Append  (cost=0.00..152.62 rows=1458 width=16) (actual time=0.058..0.917 rows=1439 loops=1)
  ->  Seq Scan on log_ins  (cost=0.00..0.00 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=1)
        Filter: ((create_time > '2017-01-01 00:00:00'::timestamp without time zone) AND (create_time < '2017-01-02 00:00:00'::timestamp without time zone))
  ->  Index Scan using idx_log_ins_history_ctime on log_ins_history  (cost=0.29..8.31 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=1)
        Index Cond: ((create_time > '2017-01-01 00:00:00'::timestamp without time zone) AND (create_time < '2017-01-02 00:00:00'::timestamp without time zone))
  ->  Index Scan using idx_log_ins_201701_ctime on log_ins_201701  (cost=0.29..60.19 rows=1445 width=16) (actual time=0.032..0.404 rows=1439 loops=1)
	……
        Index Cond: ((create_time > '2017-01-01 00:00:00'::timestamp without time zone) AND (create_time < '2017-01-02 00:00:00'::timestamp without time zone))
  ->  Seq Scan on log_ins_201712  (cost=0.00..1.01 rows=1 width=16) (actual time=0.027..0.027 rows=0 loops=1)
        Filter: ((create_time > '2017-01-01 00:00:00'::timestamp without time zone) AND (create_time < '2017-01-02 00:00:00'::timestamp without time zone))
        Rows Removed by Filter: 1
Planning time: 1.876 ms
Execution time: 1.149 ms

关闭时，查询父表时扫描了所有分区。所以不建议设置其值为off。当然也不建议设置成on，因为优化器通过检查约束来优化查询的方式本身就带来一定开销。如果所有表启用这个特性，将加重优化器的负担。所以建议用partition。

1.7 添加分区

添加分区术语分区表维护的常规操作之一，比如历史表范围分区到期之前需要扩分区。

-- 扩展分区
CREATE TABLE log_ins_201801(check(create_time >= '2018-01-01' AND create_time < '2018-02-01')) INHERITS(log_ins);

……

-- 创建索引

CREATE INDEX idx_log_ins_201801_ctime ON log_ins_201801 USING btree(create_time);
……

-- 修改触发器中的代码，添加路由规则

1.8 删除分区

-- 方式一：直接删除分区
DROP TABLE log_ins_201801;

-- 方式二：删除继承关系（推荐做法）
ALTER TABLE log_ins_201801 NO INHERIT log_ins;
-- 去掉继承关系，只要没有删除这个子表，还可以重新继承。

1.9 小结

分区表根据非分区键查询相比普通表性能差距较大，因为这种场景分区表的执行计划会扫描所有分区；
分区表根据分区键查询相比普通表性能有小幅降低，而查询分区表子表性能比普通表略有提升。

出于性能考虑对生产环境业务表做分区表时需要谨慎，使用分区表不一定提升性能，如果业务大部分（90%左右）操作都能基于分区键操作，并且SQL可以定位到子表，这时建议使用分区表。

1.10 传统分区表注意事项

当往父表上插入数据时，需事先在父表上创建路由函数和触发器。
分区表上的索引、约束需要使用单独的创建命令。
父表和子表允许单独定义主键，因此父表和子表可能存在重复的主键记录；所以最好设置范围。
update时不建议更新分区键数据，特别是会使数据从一个分区移动到另一个分区的场景。

二、内置分区表

PostgreSQL10 提供了内置分区表，因此不再需要在INSERT、DELETE、UPDATE上预先定义触发器了。对父表的DML操作会自动路由到相应分区表。相比传统分区表大幅度降低维护成本，目前仅支持范围分区和列表分区。

2.1 创建分区表

创建分区表的主要语法包含了两部分：创建主表和创建分区。

创建主表语法

create table table_name(...)
[partition by {range | list } ({column_name | (expression)})

创建主表时须指定分区方式，可选的分区方式为range 范围分区或list列表分区，并指定字段或表达式作为分区键。

创建分区的语法

create table table_name
	partition of parent_table [(
	)] for values partition_bound_spec

创建分区时必须指定是哪张表的分区，同时指定分区策略 partition_bound_spec. 如果是范围分区，partition_bound_spec 须指定每个分区分区键的取值范围，如果是列表分区，partition_bound_spec须指定每个分区的分区键值。

创建内置分区的步骤：

创建父表，指定分区键和分区策略
创建分区，创建分区时须指定分区表的父表和分区键的取值范围。注意分区键的范围不要有重叠，否则会出错。
在分区上创建相应索引，通常情况下分区键上的索引是必须的，非分区键的索引可以根据实际应用场景选择是否创建。

2.1 案例演示

--内置分区表创建演示过程

--1.创建父表，指定分区键和分区策略
CREATE TABLE log_par (
	id serial,
	user_id int4,
	create_time TIMESTAMP(0) WITHOUT time zone
) partition by range (create_time);

-- 表log_par 指定了分区策略为范围分区，分区键为create_time字段。

--2.创建分区，并设置分区的分区键取值范围
CREATE TABLE log_par_his PARTITION OF log_par FOR VALUES FROM ('2015-01-01') TO ('2017-01-01'); 
--  不支持UNBOUNDED
CREATE TABLE log_par_201701 PARTITION OF log_par FOR VALUES FROM ('2017-01-01') TO ('2017-02-01');
CREATE TABLE log_par_201702 PARTITION OF log_par FOR VALUES FROM ('2017-02-01') TO ('2017-03-01');
CREATE TABLE log_par_201703 PARTITION OF log_par FOR VALUES FROM ('2017-03-01') TO ('2017-04-01');
CREATE TABLE log_par_201704 PARTITION OF log_par FOR VALUES FROM ('2017-04-01') TO ('2017-05-01');
CREATE TABLE log_par_201705 PARTITION OF log_par FOR VALUES FROM ('2017-05-01') TO ('2017-06-01');
CREATE TABLE log_par_201706 PARTITION OF log_par FOR VALUES FROM ('2017-06-01') TO ('2017-07-01');
CREATE TABLE log_par_201707 PARTITION OF log_par FOR VALUES FROM ('2017-07-01') TO ('2017-08-01');
CREATE TABLE log_par_201708 PARTITION OF log_par FOR VALUES FROM ('2017-08-01') TO ('2017-09-01');
CREATE TABLE log_par_201709 PARTITION OF log_par FOR VALUES FROM ('2017-09-01') TO ('2017-10-01');
CREATE TABLE log_par_201710 PARTITION OF log_par FOR VALUES FROM ('2017-10-01') TO ('2017-11-01');
CREATE TABLE log_par_201711 PARTITION OF log_par FOR VALUES FROM ('2017-11-01') TO ('2017-12-01');
CREATE TABLE log_par_201712 PARTITION OF log_par FOR VALUES FROM ('2017-12-01') TO ('2018-01-01');

--注意分区表的分区键范围不要有重叠，定义分区键范围实质上给分区创建约束。

-- 3. 给所有分区的分区键创建索引

CREATE INDEX idx_log_par_his_ctime ON log_par_his USING btree(create_time);
CREATE INDEX idx_log_par_201701_ctime ON log_par_201701 USING btree(create_time);
CREATE INDEX idx_log_par_201702_ctime ON log_par_201702 USING btree(create_time);
CREATE INDEX idx_log_par_201703_ctime ON log_par_201703 USING btree(create_time);
CREATE INDEX idx_log_par_201704_ctime ON log_par_201704 USING btree(create_time);
CREATE INDEX idx_log_par_201705_ctime ON log_par_201705 USING btree(create_time);
CREATE INDEX idx_log_par_201706_ctime ON log_par_201706 USING btree(create_time);
CREATE INDEX idx_log_par_201707_ctime ON log_par_201707 USING btree(create_time);
CREATE INDEX idx_log_par_201708_ctime ON log_par_201708 USING btree(create_time);
CREATE INDEX idx_log_par_201709_ctime ON log_par_201709 USING btree(create_time);
CREATE INDEX idx_log_par_201710_ctime ON log_par_201710 USING btree(create_time);
CREATE INDEX idx_log_par_201711_ctime ON log_par_201711 USING btree(create_time);
CREATE INDEX idx_log_par_201712_ctime ON log_par_201712 USING btree(create_time);

2.3 使用分区

-- 向分区表插入数据
INSERT INTO log_par(user_id,create_time) 
SELECT round(100000000*random()),generate_series('2016-12-01'::date,'2017-12-01'::date,'1 minute');

--查看表数据

SELECT COUNT(*) FROM log_par;

-- 525601

SELECT COUNT(*) FROM ONLY log_par;
-- 0

-- 父表log_par 没有存储任何数据，数据存储在分区中

2.4 添加分区和删除分区

-- 给log_par增加一个分区
CREATE TABLE log_par_201801 PARTITION OF log_par FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');

-- 创建索引
CREATE INDEX idx_log_par_201801_ctime ON log_par_201801 USING btree(create_time);

-- 删除分区
--方法一：通过DROP(直接将分区和分区数据删除)
DROP TABLE log_par_201801;

--方法二：解绑分区
ALTER TABLE log_par DETACH PARTITION log_par_201801;

2.5 小结

传统分区表和内置分区表在相同的查询场景上性能表现一致。

内置分区表根据非分区键查询比普通表性能差距较大，这种场景因为要扫描所有分区
内置分区表根据分区键查询相比普通表性能小幅度降低，而查询分区表子表性能比普通表略有提升。

2.6 constraint_exclusion参数

内置分区执行计划依然受constraint_exclusion参数影响。

2.7 更新分区数据

内置分区表 UPDATE 操作目前不支持新新记录跨分区的情况。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：springboot 的selectimports的执行 springboot sleuth

下一篇：unity ik 复杂度 unity forge

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯