<div style="font-size:16px;">
<div class="pgc-img">
<img alt="247f5585716c5264f2047db3a536ceea.png" src="https://beijingoptbbs.oss-cn-beijing.aliyuncs.com/cs/5606289-d5b1e6e3da4c40a819e256a29b6ab03d.png">
</div>
<p>作者 Iris (PG深度爱好者)</p>
<h1 class="pgc-h-arrow-right">业务背景</h1>
<p>近期在支撑应用厂商从Oracle改造到PostgreSQL的改造工作，目前大部分O库业务表已经成功转化为PG表，应用厂商提出关于Oracle分区表迁移到PG分区表的改造方法和思路问题。由于应用业务数据每天产生大几千万数据，需要对每天的数据进行分区存储。业务表需要保留30天数据，30天之前的数据删除。</p>
<p>在原Oracle分区表设计中，以时间范围分区，区域为列表分区来创建混合分区。在分布式MYSQL数据库里可以通过时间范围分片规自动生成分片规则。在PG中分区表是通过表继承来实现的，创建一个空的主表，每个分区表按时间进行创建，去继承主表。</p>
<h1 class="pgc-h-arrow-right">分区表介绍</h1>
<p>数据库表分区把一个大的物理表分成若干个小的物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。</p>
<div class="pgc-img">
<img alt="5fb0237045627a1d3f5d793d59b5e4dc.png" src="https://beijingoptbbs.oss-cn-beijing.aliyuncs.com/cs/5606289-6c89e11aa12801816b177cf79665bce6.png">
</div>
<h1 class="pgc-h-arrow-right">分区术语</h1>
<p>· 主表 / 父表 该表是创建子表的模板。可以理解为一个标准模板表，正常情况下它并不储存任何数据。</p>
<p>· 子表 / 分区表 / Child Table / Partition Table 子表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系，一个主表包含多个分区表，而一个分区表只从属于一个主表</p>
<h1 class="pgc-h-arrow-right">分区优势</h1>
<p>· 分区后, 单个分区表的索引和表都变小了, 可以保持在内存里面, 适合把热数据从大表拆分出来的场景. </p>
<p>· 对于大范围的查询, 大表可以通过索引来避免全表扫描. 但是如果分区了的话, 可以使用分区的全表扫描. 适合经常要做大 范围扫描的场景, 按照范围分区(分区后采用全表扫描), 减少索引带来的随机BLOCK扫描. </p>
<p>· 大批量的数据导入或删除, 对于大表来说, 删除大量的数据使用DELETE的话会带来大量的VACUUM操作负担.而使用分 区表的话可以直接DROP分区, 或者脱离子表和父表的继承关系. </p>
<p>· 使用分区表,可以把不常用的分区放到便宜的存储上. </p>
<p>· 因为每个表只能放在一个表空间上, 表空间和目录对应, 表的大小受到表空间大小的限制. 使用分区表则更加灵活.</p>
<h1 class="pgc-h-arrow-right">本地分区</h1>
<p><strong>范围分区</strong>：根据字段存储的值取值范围进行分区, 例如日志表的时间字段, 用户表的ID范围等等。</p>
<p><strong>哈希分区</strong>：根据字段存储值HASH再做和分区数做比特运算得到一个唯一的分区ID. 或者取模.例如mod(hashtext(name),16), 对16个分区的场景。</p>
<p><strong>list分区</strong>：与哈希分区类似, 但是直接使用字段值作为分区条件. 适合KEY值比较少并且比较均匀的场景.例如按性别字段作为分区字段. 那么就分成了2个区。</p>
<p>本地分区，子表继承表自动继承父表的约束, 非空约束. 但是不自动继承的是(uk,pk,fk,索引,存储参数等)。如果需要继承父表约束，需要添加like table_partition including all 参数。例如：CREATE TABLE tab_partition_2020_01_1 (<strong>like tab_partition including all</strong>) inherits (tab_partition);</p>
<h1 class="pgc-h-arrow-right">创建分区表</h1>
<h1 class="pgc-h-arrow-right">创建本地分区表</h1>
<h1 class="pgc-h-arrow-right">创建父表</h1>
<p>创建主标。限制应用数据1月份的数据插入到2020年1月份分区表中，也可以为主表创建约束条件和唯一键。</p>
<p>CREATE TABLE tab_partition</p>
<p>(</p>
<p>date_key date check(date_key>to_date('2020-01-01 00:00:01','yyyy-mm-dd hh24:mi:ss') and date_key</p>
<p>hour_key smallint,</p>
<p>client_key integer,</p>
<p>item_key integer ,</p>
<p>account integer,</p>
<p>expense numeric);</p>
<h1 class="pgc-h-arrow-right">创建子表</h1>
<p>创建多个分区表。每个分区表必须继承自主表，并且正常情况下都不要为这些分区表添加任何新的列。</p>
<p>CREATE TABLE tab_partition_2020_01_01 (like tab_partition including all) inherits (tab_partition);CREATE TABLE tab_partition_2020_01_02 (like tab_partition including all) inherits (tab_partition);CREATE TABLE tab_partition_2020_01_03 (like tab_partition including all) inherits (tab_partition);CREATE TABLE tab_partition_2020_01_04 (like tab_partition including all) inherits (tab_partition);</p>
<p>CREATE TABLE tab_partition_2020_01_05 (like tab_partition including all) inherits (tab_partition);</p>
<p>1、 此建表用在测试，其他建表语句省略，实际在生产中建表需要在程序代 |
|
转播
