当前位置：首页 > article >正文

postgresql（功能最强大的开源数据库）继承特性和分区实现

article 2024/11/19 17:54:03

PostgreSQL实现了表继承，在多重表继承下，对上亿条不同类别的数据条目进行按型号、按月份双层分区管理，既可在总表查阅所有条目的共有字段，也可在各类型字表查询附加字段，非常高效。

分区是通过继承的方式来实现的，每个分区实际上都是一个独立的表。

1，继承

我们创建两个表来说明继承的特性：创建一个人类表和leader表，全球很多人，但是只有少数人是国家leader人。我们希望能够快速地检索任何国家的leader，继承特性有助于解决这个问题。

创建人类表：

mydb=#create table human(hid int,name varchar(20));

创建leader表，继承人类表：

mydb=#create table leader(flag int default 1) inherits(human);

通过继承，leader表将继承它父表human的所有列。leader通过一个额外的flag列来表示它为leader。

mydb=# \d human

         资料表 "public.human"

 栏位 |         型别          | 修饰词 

------+-----------------------+--------

 hid  | integer               | 

 name | character varying(20) | 



mydb=# \d leader

        资料表 "public.leader"

 栏位 |         型别          | 修饰词 

------+-----------------------+--------

 hid  | integer               | 

 name | character varying(20) | 

 flag | integer               | 缺省 1

继承: human

查找所有的人

mydb=# select * from human;

 hid |  name  

-----+--------

   1 | 小花

   2 | 小明

   3 | 小华

   4 | 小丽

   5 | 小李

   7 | 朱元璋

   8 | 特兰普

查找不是领导人的人

mydb=# select * from only human;

 hid | name 

-----+------

   1 | 小花

   2 | 小明

   3 | 小华

   4 | 小丽

   5 | 小李

查找领导人

mydb=# select * from leader;

 hid |  name  | flag 

-----+--------+------

   7 | 朱元璋 |    1

   8 | 特兰普 |    1

2，分区

分区在某些情况下可以使得查询性能显著提升。

当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。

如果需求计划使用划分设计，可以通过增加或移除分区来完成批量载入和删除。 ALTER TABLE NO INHERIT 和 DROP TABLE 都远快于一个批量操作。这些命令也完全避免了由批量 DELETE 造成的 VACUUM 负载。

很少使用的数据可以被迁移到便宜且较慢的存储介质上。

通过继承实现分区：

mydb=# create table login_log(login_id int not null,user_name varchar(20),login_time date);



mydb=# create table login_log_201801(

    check(login_time>=DATE '2018-01-01' and login_time<=DATE '2018-01-31')) inherits(login_log);



mydb=# create table login_log_201802(

    check(login_time>=DATE '2018-02-01' and login_time<=DATE '2018-02-28')) inherits(login_log);



mydb=# create table login_log_201803(

    check(login_time>=DATE '2018-03-01' and login_time<=DATE '2018-03-31')) inherits(login_log);

在时间列上建立索引，确保性能：

mydb=# create index idx_login_log_201801_time on login_log_201801(login_time);



mydb=# create index idx_login_log_201802_time on login_log_201802(login_time);



mydb=# create index idx_login_log_201803_time on login_log_201803(login_time);

通过insert into login_log，利用触发器和函数，使得数据根据登陆日期重定向到不同的分区。

函数创建：

CREATE OR REPLACE FUNCTION insert_login_log()

RETURNS TRIGGER AS $$

BEGIN

  if(new.login_time>=DATE '2018-01-01' and new.login_time<=DATE '2018-01-31') then

    insert into login_log_201801 values(new.*);

  elsif(new.login_time>=DATE '2018-02-01' and new.login_time<=DATE '2018-02-28') then

    insert into login_log_201802 values(new.*);

  elsif(new.login_time>=DATE '2018-03-01' and new.login_time<=DATE '2018-03-31') then

    insert into login_log_201803 values(new.*);

  else

    RAISE EXCEPTION 'Date	out of range!';

  end if;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

触发器创建：

CREATE TRIGGER insert_log_trigger

BEFORE INSERT ON login_log

FOR EACH ROW EXECUTE PROCEDURE insert_login_log();

数据录入：

insert into login_log values(1,'小明','2018-01-10');

insert into login_log values(2,'小明','2018-02-13');

insert into login_log values(3,'小明','2018-03-20');

insert into login_log values(4,'小丽','2018-01-31');

insert into login_log values(5,'小丽','2018-02-20');

insert into login_log values(6,'小丽','2018-03-11');

查询全部数据：

mydb=# select * from login_log;

 login_id | user_name | login_time 

----------+-----------+------------

        1 | 小明      | 2018-01-10

        4 | 小丽      | 2018-01-31

        2 | 小明      | 2018-02-13

        5 | 小丽      | 2018-02-20

        3 | 小明      | 2018-03-20

        6 | 小丽      | 2018-03-11

查询分区表login_log_201801数据：

mydb=# select * from login_log_201801;

 login_id | user_name | login_time 

----------+-----------+------------

        1 | 小明      | 2018-01-10

        4 | 小丽      | 2018-01-31

查询分区表login_log_201802数据：

mydb=# select * from login_log_201802;

 login_id | user_name | login_time 

----------+-----------+------------

        2 | 小明      | 2018-02-13

        5 | 小丽      | 2018-02-20

查询分区表login_log_201803数据：

mydb=# select * from login_log_201803;

 login_id | user_name | login_time 

----------+-----------+------------

        3 | 小明      | 2018-03-20

        6 | 小丽      | 2018-03-11