postgresql 创建分表
划分指的是将逻辑上的一个大表分成一些小的物理上的片。划分有很多益处:
1、在某些情况下查询性能能够显著提升,特别是当那些访问压力大的行在一个分区或者少数几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。
2、当查询或更新访问一个分区的大部分行时,可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问,这样可以改善性能。
3、如果需求计划使用划分设计,可以通过增加或移除分区来完成批量载入和删除。ALTER TABLE NO INHERIT和DROP TABLE都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。
4、很少使用的数据可以被迁移到便宜且较慢的存储介质上。
当一个表非常大时,划分所带来的好处是非常值得的。一个表何种情况下会从划分获益取决于应用,一个经验法则是当表的尺寸超过了数据库服务器物理内存时,划分会为表带来好处。
目前,PostgreSQL支持通过表继承来进行划分。每一个分区被创建为父表的一个子表。父表本身通常是空的,它的存在仅仅为了表示整个数据集。
例如: select * from only tb_log,可以查看到主表并未存储数据。
范围划分
表被根据一个关键列或一组列划分为"范围",不同的分区的范围之间没有重叠。例如,我们可以根据日期范围划分,或者根据特定业务对象的标识符划分。
列表划分
通过显式地列出每一个分区中出现的键值来划分表。
实现划分
1、创建分表并关联主表(可以根据年份,季度和或者月份来创建分表)
例:表名为tb_log,createtime 表示tb_log的一个字段,记录时间
CREATE TABLE tb_log_2017 ( CHECK ( createtime >= DATE '2017-01-01' AND createtime < DATE '2018-01-01' ) ) INHERITS (tb_log); CREATE TABLE tb_log_2018 ( CHECK ( createtime >= DATE '2018-01-01' AND createtime < DATE '2019-01-01' ) ) INHERITS (tb_log);
-- 这里创建了tb_log_2017 和 tb_log_2018 两张分表.
** 这个语句创建的分表没有主键,手动为每张分表加上主键
2、创建触发器函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE OR REPLACE FUNCTION function_insert_tb_log() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.createtime >= DATE '2017-01-01' AND NEW.createtime < DATE '2018-01-01' ) THEN INSERT INTO tb_log_2017 VALUES (NEW.*); ELSIF ( NEW.createtime >= DATE '2018-01-01' AND NEW.createtime < DATE '2019-01-01' ) THEN INSERT INTO tb_log_2018 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the function_insert_tb_log() function!' ; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; |
3、创建触发器
CREATE TRIGGER trigger_insert_tb_log
BEFORE INSERT ON tb_log
FOR EACH ROW
EXECUTE PROCEDURE function_insert_tb_log();
备注:维护表的时候,只要执行第1步和第2步就可以了,比如到2019年的时候,再用第1步的格式去创建2019年的分表(tb_log_2019),然后修改一下第2步的逻辑,执行一下就可以了。
** 使用Hibernate的时候注意,hibernate的save方法有期待返回的保存结果,分表之后保存并不会返回保存结果,所以要在实体里加入下面这行代码:
@SQLInsert(sql = "insert into tb_log (id,column1,column2) values (?,?,?)")
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(三):用.NET IoT库
· 【非技术】说说2024年我都干了些啥