PostgreSQL子表和分区表比较

57 阅读 0 评论 38 点赞

我是靠谱客的博主诚心牛排，最近开发中收集的这篇文章主要介绍PostgreSQL子表和分区表比较，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

从整体上来看，子表和分区表有相同的地方也有差别，因为都使用了继承的特性，所以本质上是一样的。下面看一下二者的区别：

1.父子表：

--创建父表
CREATE TABLE cities (   
        name        text,
        population float,
        altitude     int
);

--创建子表
CREATE TABLE capitals ( 
        state      char(2)
) INHERITS (cities);

INSERT INTO cities values('Las Vegas', 1.53, 2174);  --插入父表
INSERT INTO cities values('Mariposa',3.30,1953);     --插入父表
INSERT INTO capitals values('Madison',4.34,845,'WI');--插入子表

--在父表上做查询，父表和子表的数据均被取出。
SELECT name, altitude FROM cities WHERE altitude > 500; 
Las Vegas	2174
Mariposa	1953
Madison	        845

--查看执行计划，实际上查询从扫描了两个表的数据，组合出想要的结果
explain analyze SELECT name,altitude FROM  cities WHERE altitude > 500;
Append  (cost=0.00..24.54 rows=361 width=36) (actual time=0.008..0.012 rows=3 loops=1)
  ->  Seq Scan on cities  (cost=0.00..2.41 rows=38 width=36) (actual time=0.007..0.008 rows=2 loops=1)
        Filter: (altitude > 500)
  ->  Seq Scan on capitals  (cost=0.00..22.12 rows=323 width=36) (actual time=0.001..0.001 rows=1 loops=1)
        Filter: (altitude > 500)
Planning time: 0.096 ms
Execution time: 0.044 ms

--在子表上做查询，只能查出子表上的数据。
SELECT name, altitude FROM capitals WHERE altitude > 500;

--如果只想从父表中取数据，则需要在SQL中加入ONLY关键字，如：
SELECT name,altitude FROM ONLY cities WHERE altitude > 500;

从这个例子看一看出，父子表使用了继承的特性，子表可以增加字断，另外子表可以继承于多个父表。但是仍然有个疑问，有什么卵用呢？？

2.分区表：

分区表也是使用继承的特性，在逻辑上把一个大表分成几块数据，分区的字断和主表字断一致，不会再另行增加字断，另外可以人为定义约束，来约束每个表上的数据不重复。

--创建主表示例：
CREATE TABLE measurement (           
        city_id      int    NOT NULL,
        logdate     date  NOT NULL,
        peaktemp int
);

--创建几个子表，继承父表所有字断：
CREATE TABLE measurement_yy04mm02 (
        CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01')
    ) INHERITS (measurement);
    CREATE TABLE measurement_yy04mm03 (
        CHECK (logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01')
    ) INHERITS (measurement);
    ...
    CREATE TABLE measurement_yy05mm11 (
        CHECK (logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01')
    ) INHERITS (measurement);
    CREATE TABLE measurement_yy05mm12 (
        CHECK (logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01')
    ) INHERITS (measurement);
    CREATE TABLE measurement_yy06mm01 (
        CHECK (logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01')
    ) INHERITS (measurement);

从这个例子看出，分区表完全继承主表字断，并且不另外增加字断，每个分区表上定义好约束，保证数据不要发生重叠。分区表在生产上还是很有用的，可以提高查询效率。