pgsql分区表创建索引 pgsql 分区

转载

网络安全卫士 2024-04-24 22:18:06

文章标签 pgsql分区表创建索引 postgres分区表分区表 sql 数据 文章分类 数据仓库大数据

一、简介

　　在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，PostgreSQL也不例外。

　　PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。

　　分区的具体好处是：

某些类型的查询性能可以得到极大提升。
更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
批量删除可以用简单的删除某个分区来实现。
可以将很少用的数据移动到便宜的、转速慢的存储介质上。

　　在PG里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。无论何时，都应保证主表里面是空的。

　　小表分区不实际，表在多大情况下才考虑分区呢？PostgresSQL官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。

　　PG目前（9.2.2）仅支持范围分区和列表分区，尚未支持散列分区。

特别说明：

1.继承，权限和约束不会继承。

2.继承，对父表的操作(select丶update丶delete),默认会引用子表行，若不想引用子表行可用关键字 ONLY，只对父表操作。

二、环境

系统环境：CentOS release 6.3 (Final)

PostgreSQL版本：PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

三、实现分区

3.1 创建主表

CREATE TABLE partition_main
(
  id integer,
  insert_date date
)

3.2 创建分区表

--2014年01月分区表
CREATE TABLE partition_201401
(
  CONSTRAINT partition_201402_insert_date_check CHECK (insert_date >= '2014-01-01'::date AND insert_date < '2014-02-01'::date)
)
INHERITS (partition_main);

--2014年02月分区表
CREATE TABLE partition_201402
(
  CONSTRAINT partition_201402_insert_date_check CHECK (insert_date >= '2014-02-01'::date AND insert_date < '2014-03-01'::date)
)
INHERITS (partition_main);

--2014年03月分区表
CREATE TABLE partition_201403
(
  CONSTRAINT partition_201403_insert_date_check CHECK (insert_date >= '2014-03-01'::date AND insert_date < '2014-04-01'::date)
)
INHERITS (partition_main);

--2014年04月分区表
CREATE TABLE partition_201404
(
  CONSTRAINT partition_201404_insert_date_check CHECK (insert_date >= '2014-04-01'::date AND insert_date < '2014-05-01'::date)
)
INHERITS (partition_main);

3.3 分区键上建索引

--postgresql 默认索引类型btree

CREATE INDEX partition_201401_insert_date_index ON partition_201401 USING btree (insert_date);

CREATE INDEX partition_201402_insert_date_index ON partition_201402 USING btree (insert_date);

CREATE INDEX partition_201403_insert_date_index ON partition_201403 USING btree (insert_date);

CREATE INDEX partition_201404_insert_date_index ON partition_201404 USING btree (insert_date);

3.4 创建触发器函数

CREATE OR REPLACE FUNCTION tbl_partition_insert_function()
  RETURNS trigger AS
$BODY$
BEGIN 
	IF (    NEW.insert_date >= DATE '2014-01-01' AND NEW.insert_date < DATE '2014-02-01' ) THEN  
		INSERT INTO partition_201401 VALUES (NEW.*);  
	ELSIF ( NEW.insert_date >= DATE '2014-02-01' AND  NEW.insert_date < DATE '2014-03-01' ) THEN  
		INSERT INTO partition_201402 VALUES (NEW.*);  
	ELSIF ( NEW.insert_date >= DATE '2014-03-01' AND  NEW.insert_date < DATE '2014-04-01' ) THEN  
		INSERT INTO partition_201403 VALUES (NEW.*);  
	ELSIF ( NEW.insert_date >= DATE '2014-04-01' AND  NEW.insert_date < DATE '2014-05-01' ) THEN  
		INSERT INTO partition_201404 VALUES (NEW.*);  
	ELSE  
		RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';  
	END IF;  
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

说明：

如果不想丢失数据，上面的ELSE 条件可以改成 INSERT INTO partition_error_insert_date VALUES (NEW.*); 同时需要创建一张结构和partition_main 一样的表partition_error_insert_date，这样，错误的insert_date 数据就可以插入到这张表中而不是报错了。

3.5 创建触发器

CREATE TRIGGER tbl_partition_insert_trigger
  BEFORE INSERT
  ON partition_main
  FOR EACH ROW
  EXECUTE PROCEDURE tbl_partition_insert_function();

四、测试

4.1 插入数据

insert into partition_main values(1,DATE '2014-01-01');
insert into partition_main values(2,DATE '2014-02-01');
insert into partition_main values(3,DATE '2014-03-01');
insert into partition_main values(4,DATE '2014-04-01');

4.2 查看主表数据

select *,tableoid from  partition_main

id | insert_date | tableoid
---+-------------+----------
 1 | 2014-01-02  |    66658
 2 | 2014-02-01  |    66661
 3 | 2014-03-01  |    66664
 4 | 2014-04-01  |    66667

可从tableoid 看出每一行记录来自不同的表

5、管理分区

5.1 移除数据/分区

实现分区表之后，我们就可以很容易地移除不再使用的旧数据了，最简单的方法就是：

drop table tbl_partition_201304;

这样可以快速移除大量数据，而不是逐条删除数据。

另一个推荐做法是将分区从分区表中移除，但是保留访问权限。

alter table tbl_partition_201304 no inherit tbl_partition;

和直接DROP 相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等，在完成诸多例行性的操作之后，就可以考虑是直接删除该表(DROP TABLE)，还是先清空该表的数据(TRUNCATE TABLE)，之后再让该表重新继承主表。

alter table tbl_partition_201304 inherit tbl_partition;

5.2 增加分区

我们可以像之前那样增加一个分区

create table partition_201405 (
	check ( insert_date >= DATE '2014-05-01' AND insert_date < DATE '2014-06-01' )  
) INHERITS (partition_main);

create index partition_201405_insert_date_index on partition_201405 (insert_date );

同时，需要修改触发器函数，将插入条件改成相应的值。

说明：创建触发器函数时，最好把插入条件写更未来一点，比如多写十年，这样以后增加新分区时就不需要重新创建触发器函数了，也可以避免一些不必要的错误。

另外，还可以如下增加新的分区：

create table partition_201406

(LIKE partition_main INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

alter table partition_201406 add constraint partition_201406_insert_date_check

check ( insert_date >= DATE '2014-06-01' AND insert_date < DATE '2014-07-01' );

create index partition_201406_insert_date_index on partition_201406 (insert_date);

copy partition_201406 from '/tmp/partition_201406.sql';    //从文件中拷贝数据，这些数据可以是事前准备的

alter table partition_201406 inherit partition_main;

六、约束排除

约束排除（Constraint exclusion）是一种查询优化技巧，它改进了用上面方法定义的表分区的性能。

确保postgresql.conf 里的配置参数constraint_exclusion 是打开的。没有这个参数，查询不会按照需要进行优化。这里我们需要做的是确保该选项在配置文件中没有被注释掉。

constraint_exclusion = partition	# on, off, or partition

如果没有约束排除，查询会扫描tbl_partition 表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后再试图证明该分区不需要被扫描，因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。

可以使用EXPLAIN 命令显示一个规划在constraint_exclusion 关闭和打开情况下的不同：

6.1 约束排除关闭

set constraint_exclusion = off;

explain select count(*) from partition_main where insert_date >= DATE '2014-01-01';
                              QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
explain select count(*) from partition_main where insert_date >= DATE '2014-01-01';
                                           QUERY PLAN

从上面的查询计划中可以看出，PostgreSQL 扫描了所有分区。下面我们再看一下打开约束排除之后的查询计划：

6.2 约束排除开启

set constraint_exclusion = on;                                                  

explain select * from partition_main where insert_date >= DATE '2014-02-01';
                              QUERY PLAN
----------------------------------------------------------------------
 Append  (cost=0.00..2.02 rows=3 width=8)
   ->  Seq Scan on partition_main  (cost=0.00..0.00 rows=1 width=8)
         Filter: (insert_date >= '2014-03-01'::date)
   ->  Seq Scan on partition_201403  (cost=0.00..1.01 rows=1 width=8)
         Filter: (insert_date >= '2014-03-01'::date)
   ->  Seq Scan on partition_201404  (cost=0.00..1.01 rows=1 width=8)
         Filter: (insert_date >= '2014-03-01'::date)
(7 行记录)

可以看到，PostgreSQL 只扫描四月份以后的分区表。

八、可选的分区方式

还可以通过建立规则的方式进行分区。

CREATE RULE insert_tbl_partition_201401 AS
ON INSERT TO partition_main WHERE
    ( insert_date >= DATE '2014-01-01' AND insert_date < DATE '2014-02-01' )
DO INSTEAD
    INSERT INTO partition_201401 VALUES (NEW.*);

CREATE RULE insert_tbl_partition_201402 AS
ON INSERT TO partition_main WHERE
    ( insert_date >= DATE '2014-02-01' AND insert_date < DATE '2014-03-01' )
DO INSTEAD
    INSERT INTO partition_201402 VALUES (NEW.*);

....省略

八、注意事项

VACUUM 或 ANALYZE tbl_partition 只会对主表起作用，要想分析表，需要分别分析每个分区表。

九、分区表之自动建表，

上述方法，建表等工作由人工完成。

下面的触发器，将会自动完成数据的插入，建表等工作。

CREATE OR REPLACE FUNCTION tbl_partition_insert_function()
  RETURNS trigger AS
$BODY$
DECLARE tbl_name CHARACTER VARYING;
	tbl_name_main CHARACTER VARYING='partition';
	tbl_name_salve CHARACTER VARYING;
	tbl_year INTEGER;
	tbl_month INTEGER;
	create_tbl_sql CHARACTER VARYING;
	insert_tbl_sql CHARACTER VARYING;
	index_sql CHARACTER VARYING;
BEGIN 
	SELECT date_part('year',NEW.insert_date::date)  INTO tbl_year;
	SELECT date_part('month',NEW.insert_date::date) INTO tbl_month;
	IF(tbl_month<10)THEN
		tbl_name_salve=tbl_year||'0'||tbl_month;
	ELSE 
		tbl_name_salve=tbl_year||tbl_month;
	END IF;
	tbl_name=tbl_name_main||'_'||tbl_name_salve;
	
	--判断表 时间分段表是否存在
	IF ( (SELECT count(relname) FROM pg_class WHERE relname=tbl_name)>0) THEN
		insert_tbl_sql='INSERT INTO '||tbl_name ||' VALUES('||NEW.id||','''||NEW.insert_date||''')';
		EXECUTE insert_tbl_sql;
	ELSE 
		create_tbl_sql='CREATE TABLE '||tbl_name
		||'('
		||	'CONSTRAINT '||tbl_name||'insert_date_check'||' CHECK (insert_date >='''||tbl_year||'-'||tbl_month||'-01''' ||' AND insert_date <''' ||tbl_year||'-'||tbl_month+1||'-01'')'
		||')INHERITS (partition_main)';
		EXECUTE create_tbl_sql;
		
		index_sql='CREATE INDEX '||tbl_name||'insert_date_index ON '||tbl_name||'(insert_date)';
		EXECUTE index_sql;
		
		insert_tbl_sql='INSERT INTO '||tbl_name ||' VALUES('||NEW.id||','''||NEW.insert_date||''')';
		EXECUTE insert_tbl_sql;
	END IF;
	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

比较：第一种人工建表的方法，触发器函数内无动态执行指令，故

只用prepare一次。

第二种自动建表的方法，触发器函数内包含数条动态执行指令，动态命令每次都需要prepare,带来性能上的折损。

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