在开发过程中,当项目投入使用时,经常会遇到单表数据库过大,导致查询性能变慢,应用程序的性能变下降等等。可以使用表分区的方法来进行优化;
分区是在物理层面将一个表按照某种方式分成多块,逻辑上还是一张表;多个分区可以单独管理,甚至存放在不同的磁盘/文件系统上,提升效率。
这里我们使用的是pgstgresql,不同的版本针对表分区的功能不同。
-
pg10.x 以前只能通过继承+触发器的方式创建分区表
-
postgresql版本在10.x之后提供了内置分区表,只支持range和list分区,11.x版本支持hash分区
-
pg11之前只能单独为每个分区表建立索引,且不能在父表上建立主键,索引等。pg11后可以对父表建立索引,分区子表自动创建。
-
分区表不允许其他表作为外键引用
-
分区表的数据是通过操作父表进行插入操作的
range 范围分区
通过值的范围来进行分区, 例如时间, 数字等. 不过要注意数据范围是左闭右开;
根据id进行分表
create table test(
id int not null primary key,
name varchar not null
) partition by range (id);
创建子分区
create table test1 partition of test for values from (1) to (5);
create table test2 partition of test for values from (5) to (10);
create table test3 partition of test for values from (10) to (15);
insert into test(id, name) values (5, '张三');
查询主表能查询到所有的数据,而查询字表就只能查询到该字表的数据;因为插入的 id = 5 ,只有在子表test2中有数据,test1和test3 是没有的;
select * from test2
-
分区表的数据,只需要对父表进行操作处理即可。
-
子分区表会自动继承父表,添加索引等信息的时候,无需单独分别对分区表进行操作。
-
通过DDL信息查看分区表权限授予也是和父表的一样。
list 列表分区
通过固定列表里的值进行分区
create table test(
id int not null primary key,
name varchar not null
) partition by list (id);
create table test1 partition of test for values in(1,2,3,4);
create table test2 partition of test for values in(5,6,7,8);
create table test3 partition of test for values in(9,10,11,12);
insert into test(id, name) VALUES (9, '李四');
select * from test3;
hash 哈希分区
根据 hash 算法进行分区,字段取哈希值后再分区;
create table test(
id int not null primary key,
name varchar not null
) partition by hash(id);
create table test1 partition of test for values with (modulus 3, remainder 0);
create table test2 partition of test for values with (modulus 3, remainder 1);
create table test3 partition of test for values with (modulus 3, remainder 2);
insert into test(id, name) VALUES (3, '李四');
select * from test2;
Multi-level 多级分区
分区的分区, 可以将上述 3种分区方式混合使用. 例如: 先对表按照时间分区, 再对子表按照状态再次分区
创建主表,以range根据id分区
create table test(
id int not null primary key,
name varchar not null
) partition by range (id);
创建子表,并以list根据id再次分区
create table test1 partition of test for values from (1) to (5) partition by list(id);
创建子子表
create table test1_1 partition of test1 for values in (1,2);
create table test1_2 partition of test1 for values in (3,4,5);
如果数据量大,手动分区比较麻烦,我们可以使用插件进行分区;
pg_pathman
pg_partman
当我们创建分区后,数据量比较大,甚至超过了我们定义的字表范围时,在插入数据时,就会报错,因为并没有对应的字表,这时就需要自动创建表;
-
触发器:为主表创建触发器。
-
定时任务:每隔一段时间创建字表,可以使用pgsql中的pg_cron 定时任务。
也支持主从复制(异步和半同步)和组复制(My
SQL
5.7以后的多节点强一致复制),可通过My
SQL
Cluster、My
SQL
Fabric等实现高可用和分布式存储。但这个观点基本是在 My
SQL
4.x /
PostgreSQL
7.x 的事情,现在情况已经变了,
PostgreSQL
在9.x版本速度上有了很大的改进,而My
SQL
特性也在增加。My
SQL
是开源的。:在早期版本中,My
SQL
对
SQL
标准的支持较为有限,但随着时间推移,My
SQL
也在逐步增加对更多
SQL
特性的支持。
分区表
指的是将逻辑上的一个大表分成一些物理上的小文件。
一个表何种情况下需要设计成
分区表
,取决于应用,一个经验法则是当表的尺寸超过了
数据库
服务器物理内存时,设计为
分区表
是有利的。
二、优势:
1)查询性能提升,当那些访问频繁的数据在一个分区或者少数几个分区时,只需访问少数几个分区,不需要访问全表。
2)更新性能提升,当更新一个分区的大部分行时,可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问。
3)批量插入删除数据性能提升,可通过加载,删除
分区表
实现,可以避免批量 DELETE
一、关于
分区表
表分区是在大数据优化中的一种常见的分表方案,通过将大数据按照一定的规则(最常见的是按照时间)进行分表处理,将逻辑上的一个大表分割成物理上的几块表,插入数据时,数据会自动插入到不同的
分区表
中,从而实现查询或者其它操作的性能优化。相比于一个大表,
分区表
具有以下优点:
(1)当查询或者更新一个分区的大部分记录时,采用顺序扫描而不是随机扫描,可以获得巨大的性能提升。
(2)使用不频繁的历史数据可以转移到一些低廉的存储介质上,而热数据放到性能较好的存储介质上,可以最大限度的减少成本...
分区是指将一个大表拆分成更小的物理块,这些块可以根据其用途存储在不同的存储介质中。分区有助于提高
数据库
服务器的性能,因为需要读取、处理和返回的行数明显减少。涉及将不同的行放入不同的表中。例如,您将 18 岁以上学生的详细信息存储在一个分区中,将 18 岁以下的学生详细信息存储在另一个分区中。包括创建列较少的表,并使用其他表来存储剩余的列。规范化还涉及到跨表的列分割,但是垂直分区不仅限于此,甚至在已经规范化的情况下还会对列进行分区。在垂直分区中,我们按列划分,在水平分区中,我们按行划分。
点击下方名片,设为星标!回复“1024”获取2TB学习资源!前面介绍了
PostgreSQL
常用函数、锁操作、执行计划、视图与触发器、存储过程、索引相关的知识点,今天我将详细的为大家介绍
PostgreSQL
分区分表相关知识,希望大家能够从中收获多多!如有帮助,请点在看、转发支持一波!!!概述在组件开发迭代的过程中,随着使用时间的增加,
数据库
中的数据量也不断增加,因此
数据库
查询越来越慢。通常加...
参考:https://www.xmmup.com/
pg
zhongdefenqubiao.html#
PG
_11xin_te_xing
PostgreSQL
分区的意思是把逻辑上的一个大表分割成物理上的几块。分区不仅能带来访问速度的提升,关键的是,它能带来管理和维护上的方便。
分区表
的具体好处:
某些类型的查询性能可以得到极大提升
更新的性能也可以得到提升,因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里,那么在索引上的读和写都会产生更多的磁盘访问
PostgreSQL
14:自动hash和list分区?
PG
10中引入了声明式分区,自此随着各个版本的发布,此项功能逐渐完善。以下功能
PG
14之前版本已支持:1)您可以按照range、li...