开发者学堂课程【PostgreSQL快速入门：16PostgreSQL本地分区表的用法和优化】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/16/detail/75
16PostgreSQL本地分区表的用法和优化
内容介绍：
一、数据分区
二、本地分区的实现
三、分区的好处
四、本地分区的方法
五、分区与继承表
六、结合分区表规则优化 DML
本章节主要包括四个部分的内容，分别是数据的分区、事物的并发控制与处理、监控数据库活动以及数据库的日常维护和检查。本节课主要介绍数据分区中的本地分区。
一、数据分区
数据的分区分为两个部分，一个部分是本地的分区，本地分区是指是在一个数据库下的分区，另一部分是异地分区，在数据库不能满足产品的性能要求或者数据容量要求时，会涉及到异地的分区，也就是说数据会放到不同的物理节点。关于异地分区，我们会学习关于数据合并和拆分的方法。
二、本地分区的实现
1、PostgreSQL 与 Oracle分区表的区别
在 PostgreSQL 中，它的数据分表（分区表）和 Oracle 中的分区表略有差异。Oracle 在语法上就已经支持了数据的分区表的创建，而在 PostgreSQL 里目前并没有与数据分区表创建相关的语法，帆软 PostgreSQL 数据仓库的数据库软件，它是一个商业软件，在语法上支持数据库的分区创建。在 PostgreSQL 中，可以通过继承的概念来管理数据的分区表，后面也会具体学习继承的概念。
2、继承表
关于继承表，这里可简单理解为：一个表可以被多个表继承，一个表也可以继承多个表，在分区表的体系中，使用继承表其实是为了利用其方便管理的特性。继承表是指继承自上层，称为主表，而继承表处于下一层，称为子表。
继承表的主表之间的是完全独立的存储结构，这与其他数据库的分区表的概念相同。
如在 Oracle 中，各个分区之间有各自的 segment，在 PostgreSQL 中 同样如此，主表有其自身的存储结构，继承表也有其自身的的存储结构，甚至于继承表可以扩展字段，换言之，如果主表有3个字段，但是继承表除了继承主表的3个字段之外，还可以可以扩展几个字段，使其具有自己的属性。
当查询主表时，如果要连同继承表一起查询，则只能查到继承表和主表相同的字段。但如果只查询继承表，则可以查到继承表拥有的所有字段。
三、分区的好处
1、分区后，可以将数据量较大的数据分成多个表，因此分区后的单个分区表的数据量都会变小，都可以保存在内存中。这适用于将热数据从大表里面拆分出来的场景。
2、对于大范围的查询，数据量较大的表可以通过索引避免全表扫描，但如果分区之后，则可以使用分区的全表扫描，相对于在数据量较大的表中使用索引扫描，分区可以提高扫描的效率。因为当要查询的数据不在内存中时，如果利用索引扫描则相当于先扫描索引处的数据块，再扫描索引对应的数据块，这样会伴随部分随机扫描，效率则会大打折扣。
当然如果使用 bitmap index scan 可以减少离散扫描的概率，但是仍会有离散扫描以及 bitmap 排序的过程。这适用于经常需要做大范围查询的场景，即在该场景下，按照范围分区，采用全表扫描可以减少带来的种随机 BLOCK 扫描。
3、大批的数据导入或者删除，对于大表来说，如对于一个存储量为1000万的大的数据表，我们需要定期清理100万的数据，如果使用 DELETE 清理，会为后期带来大量 VACUUM 操作的 I/O 负担及写入 xlog 的负担。如果按照规则分区，要清理数据只要 DROP 分区，或者脱离子表和主表的继承关系，这样就可以避免后期的 VACUUM 操作，也无需在 DELETE 时再次进行扫描，减小负担。
4、使用分区表还可以把不常用的分区放到比较便宜的存储中。因为分区表的存储结构和主表存储结构实际上是分开的，换言之，数据存储量大的表在经过分区之后，部分分区不会被经常查询，如相对于当前的流水记录，旧的流水记录的查询则不会被频繁查询，那么这些旧的流水记录分表就可以存储到较便宜的存储中，只用作数据保留。
5、对于单表太大的情况，其可能会受到表空间大小的限制。因为表只能放在表空间中，而在PostgreSQL中，一个表空间对应一个目录，而目录大小则会受到文件系统的限制，若要扩展表空房间，则相当于要从底层扩展分区的大小，而非简单地把表放到不同的表空间，因为一个表只能放到一个表空间。因此，对于数据存储量大的表，将其拆分成数据量小的表之后，就可以很灵活地将其调分区。如，一个表空间占用达80%，那之后的分取表就不放在该表空间里，而是将其放在新的表空间里，这样就无需从底层扩展文件系统。
四、本地分区的方法
对于表，分区的方法有多种：
1、范围分区
按照字段存储值的取值范围进行分区，利用日志表进行分区，如流水日志可以按照时间字段来做种分区，对于用户表可以按照用户 ID 的范围分区，如对10用户，可以根据这10亿用户的ID范围进行切分，如1-1000万、1000万-2000万分区等等。
2、哈希分区
是指根据字段存储值的 HASH 和分区数做比特运算得到唯一的分区 ID。
需要注意的是 HASH 的算法一定要保持一致，如当数据库要备份并且还原到另外一个节点时，如使用 pg_dump 与pg_store 的方式可以使数据库从9.1升级到9.3，且在 PostgreSQL 中所有的版本升级都应使用这种方式，整个过程相当于把原来9.1的数据备份，并将其导入到9.3的版本。
因此，哈希算法一定要保持一致，否则得到的哈希值会发生变化，简单来说，在原本9.1的版本中，得到的哈希值和分区数作比特运算得到的唯一ID 为1，而升级到9.3之后，哈希算法发生了变化，假设经过运算之后的ID为2，就会导致后期无法寻找到该记录，或者使该记录写入错误的分区表，导致数据库运行异常。
因此，对于版本升级以及其他的软件升级，一定要确保哈希算法的一致性。
以上是关于与分区数作比特笔运算的案例，此外也可以运用取模的方式 mod（hashtext(name), 16），其中 name 是用户名，并设置分区数为16，系统即根据“16”取模，进而得到0-15这样的16个 ID，就相当于16个分区表。前文中提到 hashtext 即为一个哈希函数，用来把某些值转化为int类型，该算法在不同的版本之间也一定要一致，否则就会导致运行异常。
3、list 分区
与哈希分区类似，只是可以直接使用字段值作为分区条件，在以 KEY 为分区条件的场景中，其适合 KEY 值较少且分布比较均匀的产品。
如按性别来对表作分区，男性与女性各一个分区，即分为两个区，分区的条件即为字段 sexy 的值。注意这种分区适用于 KEY 值分布均匀的情况，否则分区结果会是有的区很小，而有的区很大，则没有明显的分区的意义，则可能要做两层或三层的分局区。
五、分区与继承表
1、继承表
在 PostgreSQL 中，之所以可以创建分区表，其实是利用了继承的概念。例如：
digoal=#create table p1(id int primary key, info text unique，c1 int check(c1>0),c2 int not null, c3 int unique);
//创建p1表为父表，其中 id 与 info 存在索引
CREATE TABLE
digoal=# create table c1(like p1)inherits(p1)；
//c1是 inherits，表示其继承于p1表。like p1是 PostgreSQL 中的一个语法，表示创建 c1表时，其字段与 p1表中相应的字段一致，其中包含字段类型、字段顺序、字段名称等。同时，还支持 including，including 指的是子表将父表的包括存储、索引、约束在内的信息全部继承，如 including text 或者 including construct 表示约束或所有信息都继承自父表，均与 p1类似。
NOTICE:merging column "id"with inherited definition
NOTICE: merging column "info"with inherited definition
//继承来自 p1表 id 和 info 的索引NOTICE:merging column"c1"with inherited dcfinition NOTICE: merging column"c2"with inherited definition NOTICE: merging column"c3" with inherited definition CREATE TABLE
继承表不关注 like，而是要关注 inherits，其自动继承父表的约束，且为非空约束，但是不自动继承唯一键值uk、pk、Y键fk、索引、存储参数等，而仅是继承非空的约束和父表的约束。因此，在创建c1表时，如果不添加 like p1，那么继承到的会是值类型、约束，如 check（c1>0），但不会继承唯一键值、主键等，此外还会继承非空约束，但是不会继承 pk、uk、fk、索引、存储参数等。
如创建主表、继承表，观察主表的结构，如下：
主表 p1有三个索引，包括两个是唯一约束和一个组件，主表中还有一个 check 约束，c2中存在非空约束。
观察继承表 c1表的结构：
其id继承了非空约束，也继承了 c2的 not null，以及普通的约束。可以发现c1表中继承的类型与父表 p1完全相同，且 pk、uk等没有继承。
2、继承关系的调整
（1）including all
若要继承索引、存储结构等，可以在其中加 including all,即digoal=# create table c1(like p1 including all)inherits(p1)，
运行该命令后，即使后面不加 inherits 仍旧会创建一个相同的表，其结构相同，仅是名字不同，若不指定，默认表空间与其一致。观察其结构：
Inherits：p1表示该表继承自 p1表的。由于命令中添加了 including all，此时的c1表中包含了来自 p1表的索引、主键以及约束等的全部继承。
（2）alter table
如果要解除表的继承关系，或者在原本加入没有继承某项内容的继承表上加入基层，如何添加？或如何解除？可以使用 alter table。
例如：
digoal=# alter table cl no inherit pl；
//c1表不再继承自 p1表，即解除继承关系
ALTER TABLE
digoal=# alter table c1 drop constraint pl_cl_check;
//删除 pl_cl_check 约束，即 CHECK（c1>0）；
ALTER TABLE
digoal=# alter table cl inherit pl;
ERROR: child table is missing constraint"pl_cl_check"
//当此时再将该表加入继承时，系统报错。因为在加入继承时，系统会对该表的非空约束以及普通约束进行检查，检查其是否与父表一致，若不一致，则不可以为该表加入继承关系。
//由于上一步操作删除了 c1表的 check 约束，因此，在为c1表添加继承时，系统提示子表缺少了 pl_cl_check 约束，继承表默认继承的约束和非空约束等都在添加继承时系统的检查范围内，若不一致，则不允许继承。
digoal=# alter table cl add constraint pl_cl_check check(ch
eck>0);
ALTER TABLE
digoal=# alter table cl inherit pl;
ALTER TABLE
//将删除的约束 pl_cl_check check(check>0)重新添加到c1表，且重新添加的约束与主表要完全一致一模一样。此时，可以成功为 c1表添加继承关系，使其继承自p1表。因为在添加继承关系时，系统会对 not null 和 check 约束进行检测，检测其是否与主表一致。
此外，一个表可以同时继承多个父表，一个父表也可以被多个子表继承。在创建分区表时，实际上就是多个子表继承同一父表，每个分区即为一个子表，而父表则是一个路口，并不单指在该表中进行dml操作，后期的部分操作还要在其中写入触发器或者规则等。但一个表同时继承多个主表时，必须要注意在共有字段上，所有父表的约束（包括not null 的定义）都必须继承，同样继承的内容不包括pk、uk、fk等。
查询主表时，默认情况下会同时查询所有的子表，包括更深层次的子表（子表的子表），一直到子表后再无其他的任何子表。例如：select *from p1;（默认查询 p1表及 p1表的所有子表）
digoal=# explain select *from p1;
//查询其执行计划
结果显示该过程中查询了 p1表本身及其继承表 c1表，再将查询
结果合并（Append）。而该场景下 p1表的继承表 c1表无继承
表，因此只查询了 p1表和c1表，若 c1表有子表，则还会继续
查询，直到再没有子表。
digoal=# alter table cl add constraint pl_cl_check check(ch
eck>0);
ALTER TABLE
only或修改sql inheritance
使用 only 或者修改 sql inheritance 可以指定查询的表。
①修改 sql inheritance
//查询 sql inheritance 参数
su -pg93
cd $PGDATA
less postgresql.conf
psql
-s show sql_inheritance
//显示结果为 on，该参数默认处于打开状态
digoal=# set sql_inheritance=false;
//将其改为 off 或 false
SET
digoal=# explain select * from pl;
//查询其执行计划
//--false 时不会自动在表后面加*
//根据查询结果，可以发现本次操仅查询看父表 p1表，而没有查
询其继承表c1表作没有会不会去查，因此是否查询所有继承表与
参数有关。如果此处将参数 sql_inheritance 换为 all，则会查询
主表及其所有的继承表。
②only
digoal=# set sql_inheritance=true;
//将sql_inheritance改为true
SET
digoal=# explain select * from only p1;
//查询其执行计划
结果显示仅查询了主表p1表。
//“only p1”表示仅查询主表 p1 表，而不查询其他子表。反之，
则会查询主表及所有继承表。因为如果没有“only”，则前一个语
句中sql_inheritance=true默认会使得 查找语句加一个*，即
select * from p1*，即查询 p1表及 p1表的继承表。
在前面创建 p1表的基础上，输入：
set sql_inheritance=off;
//关闭 sql_inheritance 参数
digoal=# explain select * from p1*;
//查询执行计划：
结果显示即使关闭 sql_inheritance 参数，p1后加*仍旧可以查询 p1表及其继承表
digoal=# explain select * from p1；
结果显示仅查询 p1表。
说明 only 相当于为 p1加*，查询主表自身的同时也查询子表。
除了 SELECT之外，UPDATE、DELETE、TRUNCATE、DROP 等的操作也都一定要谨慎，因为这些操作都会影响到子表。例如在以上操作中，当 sql_inheritance 参数打开（on）时，select影响了子表。其他几个命令与此相同，都会影响子表。只有 INSERT 和 COPY 两个命令只会对当前的表进行操作，而不会影响子表。例如：
digoal=# explain update p1 set c2=c2;
结果显示如下：
查询结果显示该操作中对主表和继承表都进行了查询。
digoal=# explain update only p1 set c2=c2;
结果显示如下：
查询结果显示，执行 update only 操作仅查询主表。
或者关闭 sql_inheritance 参数，不加only也可以仅查询主表：
digoal=#set sql_inheritance=off;
digoal=# explain update p1 set c2=c2;
该查询结果显示仅查询主表，这是因为关闭 sql_inheritance 参数后，update 操作将会为p1表加*，而以下为加*时的查询过程：
digoal=# explain update p1* set c2=c2;
结果显示即使是在 sql_inheritance 参数关闭的情况下，系统也会查询主表和子表。
总之，在解析时，关闭 sql_inheritance 参数的情况下，加*在于更新所操作的表本身，并操作其继承表。再如：
digoal=# explain insert p1(id,c2) value (1,2);
结果显示，执行 insert 操作时，其查询的仅为 p1表，因为 insert 没有 p1*的语法，一旦输入digoal=# explain insert p1*(id,c2) value (1,2)，系统报错。因为执行 insert 时，只会将相关数据插入到 p1表，简单来说就是insert解释p1*，但在以上提到的各种操作中，除 insert 之外其他都解释 p1*，因此执行其他操作都会会影响到子表。但是对于 insert、copy，这两种操作都只影响当前表，不会扩展到子表。
六、结合分区表规则优化 DML
如查询优化，前面提到，在查询时，很多操作会对所有的子表都产生影响，如加*，或者在sql_inheritance=on，但没有使用 only 的情况下，很多操作都会影响到子表，也包括主表本身。那么，在创建分区表之后，如何只对某分区进行操作，进行缩小查询的范围？可以通过约束和参数 constraint_exclusion 进行优化。The allowed values of constraint_exclusion are on (examine constraints for all tables), off (never examine constraints), and partition (examine constraints only for inheritance child tables and UNION ALL subqueries). partition is the default setting.
//当参数 constraint_exclusion 参数处于 on 时，则会检测所有的表的所有约束；当参数 constraint_exclusion 参数处于 off 时，不检测任何约束；当参数 constraint_exclusion 处于 partition 时，若查询时带来一个带有约束字段的条件，则会检测这些约束，若约束可以明显排除，则不会进行检测。
例如:
//创建测试表，包括父表和子表
digoal-# show constraint exclusion;
partition
digoal-# create table p(id int,info text crt time timestamp); digoal-# create table cl(like p)inherits(p)；
digoal=# create table c2(like p)inherits(p)；
digoal=# create table c3(like p)inherits(p)；
digoal=# create table c4(like p)inherits(p)；
//添加约束
digoal=# alter tablec1 add constraint ck check(crt_time>=2013
-01-01 and crt_time<2013-02-01');
//限制 c1表 crt_time 字段在2013年1月
digoal=# alter table c2 add constraint ck check(crt _time> =2013-02-01 and crt_time<2013-03-01);
//限制 c2表 crt_time 字段在2013年2月
digoal=# alter table c3 add constraint ck check(crt _time>=201
3-03-01and crt time<2013-04-01');
//限制 c3表 crt_time 字段在2013年3月
digoal=# alter table c4 add constraint ck check(crt_ti me>=2013-04-01 and crt_time<2013-05-01);
//限制c4表 crt_time 字段在2013年4月
digoal=# explain select*from p where crt_time2013-01-01;
//查询 sql 语句 crt_time 2013-01-01，如果不加where条件则会查询所有子表，而加上 where 条件且参数处于partition 或 on，则会检查些约束。当查询时，由于字段上有约束，就会检查所有表的约束，而2013年1月1日显然只落在c1表中，而其他表显然不包含，也就是从约束条件就可以将之排除，则在查询的时候就会优化执行计划，只查询c1表和 p表，因为p表是父表。而父表没有做相关的约束，如果父表也添加了约束，如
digoal=# alter table p add constraint ck check(crt_ti me>=1970-01-01 and crt_time<1970-01-01);
而该约束显然不存在。
digoal=# explain select*from p where crt_time2013-01-01;
从执行计划看，如果检测到p表不满足，就直接不再进行查询。修改 partition：
set constrain_exclusion partition
注意加约束时，c1也会加入该约束全。
可以在其中插入数据，如插入1月1号的数据
而此处的目的在于仅修改p表，而不修改子表，输入：
alter table only p
然后去掉之前添加的错误约束check(crt_ti me>=1970-01-01 and crt_time<1970-01-01)
值得注意的是p表原先没有 check 约束，是后期在子表中添加的，如果要在主表中加入约束，则无法直接进行。我们在之前的内容中学习过有关主表和子表的关系，即子表可以比主表多一些约束，但主表有的约束，继承表也要有（除了uk、pk、fk 之外）。
因此，无法使得子表上没有主表上的某些约束。因此，在做这类型的查询时，一定会对主表进行查询，没办法消除对主表的查询。因此，在做分区时，主表中不存储任何数据，而是都放在子表中，这样就不会影响查询。
该优化案例则是使用参数和约束去完成优化，约束相当于是每个继承的where条件字段做出约束，如对时间范围的查询，可以把时间范围作为 where 条件，进而匹配该字段的约束，如 c2、c3、c4等。
