5

PG基础篇--逻辑结构管理（表继承、分区表）

 1 year ago
source link: https://blog.51cto.com/u_13874232/5429305
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

PG基础篇--逻辑结构管理（表继承、分区表）

原创

进击的CJR 2022-06-30 09:56:17 博主文章分类：PostgreSQL ©著作权

文章标签 分区表 字段 数据 文章分类 其它 数据库 yyds干货盘点 阅读数175

什么是表继承

创建父表person，子表studen 

postgres=# create table persons(
postgres(# name text,
postgres(# age int,
postgres(# sex boolean);
CREATE TABLE
postgres=# create table students(
postgres(# class_no int
postgres(# )inherits(persons);
CREATE TABLE
postgres=# insert into students values ('张三',15,true,1);
INSERT 0 1
postgres=# select  * from persons;
 name | age | sex 
------+-----+-----
 张三 |  15 | t
(1 row)

postgres=# select * from students;
 name | age | sex | class_no 
------+-----+-----+----------
 张三 |  15 | t   |        1
(1 row)
可以看到子表插入数据，父表也被插入对应行。
同时子表更新数据，父表同时也被更新。
postgres=# update students set age=13 where name='张三';
UPDATE 1
postgres=# select  * from persons;                      
 name | age | sex 
------+-----+-----
 张三 |  13 | t
(1 row)
如果向persons表插入数据，则studens表看不到
表继承规则
（1）查询父表时会把父表中字表的数据也查询出来，反之则不行，只想把父表本身的数据查询出来，只需要在查询的表名前面加ONLY关键字。
（2）所有父表的检查约束和非空约束都会自动被所有子表继承，其他类型的约束（唯一，主键，外键）不会被继承
（3）一个子表可以从多个父表继承，这种情况下它将拥有所有父表字段的总和，如果同一个字段名出线在多个父表中，那么将融合，融合的字段将会拥有其父字段的所有检查约束。
（4）使用DML和DDL操作父表，也会同时操作到子表，但REINDEX，VACUUM命令不会影响到子表。
postgresql内部是通过表继承来实现分区表的。表分区就是把逻辑上的一个大表分割成物理上的几块。
分区表的好处
（1）历史数据删除更快，例如按时间分区，直接删除历史分区即可，没有分区则可能导致VACUUM超载
（2）某些类型的查询性能可以得到极大提升，各个分区表均有各自的索引，使用率高的分区表的索引就可能完全缓存在内存中，这样查下效率就会高很多。
什么时候分区
表的大小超过了数据库服务器的物理内存大小时使用。
建分区表步骤
（1）创建父表，所有分区都从它继承。该表中没有数据，不要在其上定义任何检查约束，除非你希望约束所有的分区。
（2）创建几个子表，每个都是从主表上继承，通常这些表不会增加任何字段，
（3）给分区表增加约束，定义每个分区允许的键值
（4）对于每个分区，在关键字段上创建一个索引
（5）定义一个规则或者触发器，把对主表的数据插入重定向到合适的分区表中。
（6）确保constraint_exclusion中的配置参数postgresql.conf是打开的，目的是，打开后，如果查询中where子句的过滤条件与分区的约束条件匹配，那么该查询会智能的只查询此分区，不会查询其他分区。
create table sales_detail(
product_id int not null,
price numeric(12,2),
  amount int not null,
  sale_date date not null,
  buyer varchar(40),
  buyer_countact text
);

create table sales_detail_y2022m01(
  check (sale_date > DATE '2022-01-01' AND sale_date < DATE '2022-02-01')
)INHERITS(sales_detail);

create table sales_detail_y2022m02(
  check (sale_date > DATE '2022-02-01' AND sale_date < DATE '2022-03-01')
)INHERITS(sales_detail);

create table sales_detail_y2022m03(
  check (sale_date > DATE '2022-03-01' AND sale_date < DATE '2022-04-01')
)INHERITS(sales_detail);

create index sale_detail_y2022m01_sales_date on sales_detail_y2022m01 (sale_date);
create index sale_detail_y2022m02_sales_date on sales_detail_y2022m02 (sale_date);
create index sale_detail_y2022m03_sales_date on sales_detail_y2022m03 (sale_date);


create or replace function sale_detail_insert_trigger()
returns trigger as $$
begin
........
end;
$$
分区表是使用触发器来把插入的数据重新定位到相应的分区中的。
constraint_exclusion跳过不需要扫描的分区
设置为off，则会扫描每张分区字表
声明式分区
不要创建主表，子表的方式直接用DDL方式创建
create table sales_detail(
product_id int not null,
price numeric(12,2),
  amount int not null,
  sale_date date not null,
  buyer varchar(40),
  buyer_countact text
)PRATITION BY RANGE(sale_date);

create table sales_detail_y2022m01 PARTITION OF sales_detail
FOR VALUES FROM ('2022-01-01') TO ('2022-02-01')

create table sales_detail_y2022m02 PARTITION OF sales_detail
FOR VALUES FROM ('2022-02-01') TO ('2022-03-01')
注意：分区表的分区仍然是一个单独的表，可以直接查询。

            

		
        
        
    

    

        
  • 
                
                    
                    赞
                
            
    • 
            
  • 
                
                    
                    收藏
                
            
    • 
            
  • 
                
                    
                    评论
                
            
    • 
            
  • 
                
                    
                    分享
                
                
            
    • 
                        
  • 
                
                    
                    举报
                
            
    • 
                    

    
    


        

        

            

                report
            

        

    







        
Recommend

        



                

    
About Joyk

    
 





Aggregate valuable and interesting links.

Joyk means Joy of geeK