1、背景
在项目组件的开发中,统计模块使用的表数据量较大,影响查询性能,需要进行分表处理。本文将介绍PostgreSql数据库表分区的策略以及其在巡查考评组件开发中的应用。
2、术语解释
- 主表:该表是创建子表的模板,它是一个正常的普通表,但是正常情况下它并不存储任何数据。
- 子表/分表:这些表继承并属于一个主表,子表中存储所有的数据。
3、问题分析
3.1 PostgreSql如何分表
数据库表分区把一个大的物理表分成若干个小的物理表,并使得这些小物理表在逻辑上可以被当成一张表来使用。那么如何使用PostgreSql来实现这个功能呢?
- 创建主表
![302eacddc892184b1dc2bf41f6e7bd97.png](https://i-blog.csdnimg.cn/blog_migrate/f66deb79d0240c83d5835d3d396fbdfb.png)
2. 创建多个分表。每个分区表必须继承自主表,并且正常情况下都不要为这些分区表添加任何新的列
![f05bfda88f69312375479f041dceab25.png](https://i-blog.csdnimg.cn/blog_migrate/fbe0693f12510b2fbb90c329a3a6bd6a.png)
可以看到,这里我们为tb_patrol_detail_stat创建了一个2018年12月的子表。
3. 为分区表添加限制。这些限制决定了该表所能允许保存的数据集范围。这里必须保证各个分区表之间的限制不能有重叠
![51eb93ffb612b2edb1376e756175cd5c.png](https://i-blog.csdnimg.cn/blog_migrate/b1326898e1ccdeef98596c0c53772613.png)
为子表tb_patrol_detail_stat_201812添加约束,限制该字表中只能存储statistic_time在2018年12月份的数据。这里需要注意的是添加约束时,约束的字段类型应与主表的分表字段的类型一致,否则会导致全表扫描。
4. 定义一个trigger或者rule把对主表的数据插入操作重定向到对应的分区表
![c01c44ed6e12c81d640e635aa9985ffe.png](https://i-blog.csdnimg.cn/blog_migrate/17f42bba483b5f9b85b07c06bd460395.png)
添加了一个名为insert_tb_patrol_detail_stat_201812的规则,当向主表添加statistic_time在2018年12月份的数据时重定向到子表tb_patrol_detail_stat_201812中。
3.2 在应用组件中分表
上一节介绍了PostgreSql是如何实现分表的,本节将介绍如何在巡查考评组件中应用分表。
在巡查考评组件中,对数据量较大的表进行按月分表。分表策略如下:
- 在系统启动时进行一次分表
- 每月1号凌晨1点进行一次分表
- 每次分表的逻辑如下图所示
![05ed7b461ac398a7c001d9cf53cfea15.png](https://i-blog.csdnimg.cn/blog_migrate/ee49f32d2411f129ea07616605fa26a2.png)
注意:“最后一次分表时间”表示最晚的子表月份。
正常情况下,每次分表后最后分表时间都会晚于当前时间5个月,也就是会有5个月的富余子表。如果出现当前时间晚于上次分表时间,说明系统有一段时间没有进行分表了,需要把缺失的子表补上去。
4. 获取最后一次分表时间的方法
![22c06c8b1bc302f558475e998e835b74.png](https://i-blog.csdnimg.cn/blog_migrate/ab636b411599b1f9b2a5707652569701.png)
根据主表名称获取到子表插入规则,获取最晚的规则名的后缀即为最后一次分表时