一：分区表的定义和优点
1分区表的定义
分区表就是根据分区策略，将数据数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上的分区。分区表在物理上是分开存储的，但在逻辑上仍作为一个整体呈现给用户。这一特性特别适用于处理大量数据的场景，旨在提高查询性能、管理和维护大数据集的效率。
2分区表的优点
查询性能提升：通过将数据分布到多个分区，查询可以针对单个分区进行，而不是整个表，从而减少需要扫描的数据量，提高查询性能。
方便管理:维护数据更方便快捷，不用整个表都维护，只需要单独维护某个分区，对某个分区进行增删改查等操作。
数据隔离：在实际业务场景下，有些时候要对数据进行逻辑上的隔离，分区表刚好能够满足这一业务需求。
提高并发性：分区表可以提高并发性，因为不同的查询可以并行地访问不同的分区，减少了锁的争用。
二：分区表的类型
PostgreSQL支持三种主要的分区类型：范围分区（Range Partitioning）、列表分区（List Partitioning）和哈希分区（Hash Partitioning）。
1：范围分区（Range Partitioning）：根据表中某一列的值范围来创建分区。例如，可以根据时列将数据按月、季度或年份划分到不同的分区中。
2：列表分区（List Partitioning）：根据列的特定值列表来划分分区。适合于当数据可以明确地根据某个列的枚举值进行分类的情况，如按地区或用户组划分。
3：哈希分区（Hash Partitioning）：从PostgreSQL 11版本开始支持。基于哈希算法将数据分布到不同分区中，适用于希望数据均匀分布在各个分区的场景，但不保证数据的顺序或范围。
三：在Postgresql中创建范围分区表
1：创建父表，所有子表继承该表，一般父表不存储数据，也不需要在父表中建立索引，该表包含所有分区表的共同字段，并且不包含任何数据。父表需要指定分区键和分区类型。
在创建父表的时候遇到了一个报错，我们一起来看一下是为什么？
--创建父表
CREATE TABLE father_table (
id SERIAL,
column1 TEXT,
column2 INTEGER,
partition_key INT,
primary key (id,partition_key)
) PARTITION BY RANGE (partition_key);
2:创建分区表，分区表需要创建多个，我们是按照范围分区，分区表的继承分区父表的所有属性，包括(字段个数，类型，索引，主键)另外，分区表还需要指定分区健的值的范围。
分区表的创建如下所示
--创建分区表
CREATE TABLE child_table1 PARTITION OF father_table
FOR VALUES FROM (1) TO (100);
CREATE TABLE child_table2 PARTITION OF father_table
FOR VALUES FROM (101) TO (200);
CREATE TABLE child_table3 PARTITION OF father_table
FOR VALUES FROM (201) TO (300);
CREATE TABLE child_table4 PARTITION OF father_table
FOR VALUES FROM (301) TO (400);
3：不用指定分区表名，直接给表中插入数据，数据库会自动根据分区的范围插入数据到不同的分区表中。
--插入数据
INSERT INTO father_table (column1, column2, partition_key) VALUES ('value1', 1, 50);
INSERT INTO father_table (column1, column2, partition_key) VALUES ('value1', 2, 150);
INSERT INTO father_table (column1, column2, partition_key) VALUES ('value1', 3, 250);
INSERT INTO father_table (column1, column2, partition_key) VALUES ('value1', 4, 350);
表数据
四：在Postgresql中创建列表分区表
创建学生表，需要根据学生籍贯来进行分区
--创建学生表,按照籍贯分区(列表分区例子)
CREATE TABLE students (
student_id SERIAL ,
name VARCHAR(100),
province VARCHAR(50),
PRIMARY KEY(student_id,province) --键约束或唯一约束必须包含所有分区字段
) PARTITION BY LIST (province);
--分区表
CREATE TABLE student1 PARTITION OF students
FOR VALUES IN ('shanxi');
CREATE TABLE student2 PARTITION OF students
FOR VALUES IN ('henan');
CREATE TABLE student3 PARTITION OF students
FOR VALUES IN ('gansu');
CREATE TABLE student4 PARTITION OF students
FOR VALUES IN ('beijing');
CREATE TABLE student5 PARTITION OF students
FOR VALUES IN ('shanghai');
CREATE TABLE student6 PARTITION OF students
FOR VALUES IN ('guangzhou');
-- 插入数据
INSERT INTO students (name, province) VALUES ('小明', 'shanxi');
INSERT INTO students (name, province) VALUES ('小李', 'henan');
INSERT INTO students (name, province) VALUES ('小王', 'gansu');
INSERT INTO students (name, province) VALUES ('小刚', 'beijing');
INSERT INTO students (name, province) VALUES ('小红', 'shanghai');
INSERT INTO students (name, province) VALUES ('小杨', 'guangzhou');
-- 查询数据
SELECT * FROM students WHERE province in('shanxi','henan','gansu','beijing','shanghai','guangzhou') ;
五：在Postgresql中创建哈希分区表
哈希分区表的优点如下
数据分布：哈希分区可以将数据均匀地分布到多个分区中，从而提高查询和事务的并行处理能力。
性能优化：通过将大表分割成小的分区，可以显著减少查询处理的数据量，从而提高查询效率。
管理便捷：分区表可以作为管理大量数据的手段，使得数据的维护和管理更加简单。
以下是一个创建哈希分区表的示例代码：
--创建哈希分区表
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY HASH (city_id);
CREATE TABLE measurement_p1 PARTITION OF measurement
FOR VALUES WITH (MODULUS 5, REMAINDER 1);
CREATE TABLE measurement_p2 PARTITION OF measurement
FOR VALUES WITH (MODULUS 5, REMAINDER 2);
CREATE TABLE measurement_p3 PARTITION OF measurement
FOR VALUES WITH (MODULUS 5, REMAINDER 3);
CREATE TABLE measurement_p4 PARTITION OF measurement
FOR VALUES WITH (MODULUS 5, REMAINDER 4);
CREATE TABLE measurement_p5 PARTITION OF measurement
FOR VALUES WITH (MODULUS 5, REMAINDER 0);
目录
--插入数据
INSERT INTO measurement (city_id, logdate,peaktemp,unitsales) VALUES (10, now(),2,3);
INSERT INTO measurement (city_id, logdate,peaktemp,unitsales) VALUES (13, now(),2,3);
INSERT INTO measurement (city_id, logdate,peaktemp,unitsales) VALUES (17, now(),2,3);
INSERT INTO measurement (city_id, logdate,peaktemp,unitsales) VALUES (18, now(),2,3);
评论区