PostgreSQL分区表的灵活应用
通常情况下，扫描一个大表会很慢,需要扫描整张表格，如果能够把大表分拆成小表，查询数据的时猴，只扫描数据所属的小表，就能大大降低扫描时间，提高查询速度。
1、简介
PostgreSQL10之前的版本不支持内置分区表，若要实现分区功能，需通过继承的方式实现。
PostgreSQL 10.x 之前的版本提供了一种“手动”方式使用分区表的方式，需要使用继承 + 触发器的来实现分区表，步骤较为繁琐，需要定义附表、子表、子表的约束、创建子表索引，创建分区删除、修改，触发器等。
PostgreSQL 10.x 开始提供了内置分区表（内置是相对于 10.x 之前的手动方式）。内置分区简化了操作，将部分操作内置，最终简单三步就能够创建分区表。但是只支持范围分区（RANGE）和列表分区（LIST），11.x 版本添加了对 HASH 分区。
如果要充分使用分区表的查询优势，必须使用分区时的字段作为过滤条件。
除了在查询上的优势，分区表的使用也可提高删除数据的性能，因为删除一个分区要比删除分区上的所有数据要快的多。这些命令也完全避免了由批量DELETE造成的VACUUM负载。
概念：分区表就是根据分区策略，将数据数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上的分区。
文章目录
2、列表分区
--创建主表
CREATE TABLE info_list (
id bigint NOT NULL,
protocol varchar(16),
ip varchar(50),
create_time timestamp
) partition by list(create_time);
--创建分区表
create table info_list20200801 partition of info_list for values in ('2020-08-01');
create table info_list20200802 partition of info_list for values in ('2020-08-02');
create table info_list20200803 partition of info_list for values in ('2020-08-03');
--分区表建索引
CREATE INDEX idx_info_list20200801 ON info_list20200801 (create_time);
CREATE INDEX idx_info_list20200802 ON info_list20200802 (create_time);
CREATE INDEX idx_info_list20200803 ON info_list20200803 (create_time);
3、范围分区
注意:如分表的范围为2020-08-01至2020-08-02，则包含前者，不包含后者。相当于时a<=create_time<b。
--1、创建主表(根据create_time进行范围分区)
CREATE TABLE info_range (
id bigint NOT NULL,
protocol varchar(16),
ip varchar(50),
create_time timestamp
) partition by range(create_time);
--2、创建分表（根据下面表范围，如果插入2020-08-04,则会报错;如范围为2020-08-01至2020-08-02，则包含前者，不包含后者。相当于时a<=create_time<b;）
create table info_range20200801 partition of info_range for values from ('2020-08-01') to ('2020-08-02');
create table info_range20200802 partition of info_range for values from ('2020-08-02') to ('2020-08-03');
create table info_range20200803 partition of info_range for values from ('2020-08-03') to ('2020-08-04');
--3、创建索引
CREATE INDEX idx_info_range20200801 ON info_range20200801 (create_time);
CREATE INDEX idx_info_range20200802 ON info_range20200802 (create_time);
CREATE INDEX idx_info_range20200803 ON info_range20200803 (create_time);
4、分区管理
--断开分区
alter table info_range detach partition info_range20200803;
--连接分区
alter table info_range attach partition info_range20200803 for values from ('2020-08-03') to ('2020-08-04');
--删除分区
drop table info_range20200803;
PG11以上
哈希分区
通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足：分区键的值除以为其指定的模数将产生为其指定的余数。
5、常用的分表方式，范围分区（包括分区表嵌套，添加、删除分区）
如给大表分区后的某个分区还是特别大，可以弄个二级分区，就像结构树那样嵌套下去（具体做法请参考5.5~5.8）
5.1、创建主表
CREATE TABLE sales_orders (
order_id SERIAL, -- 订单ID，自动递增
customer_id INT NOT NULL, -- 客户ID，不允许为空
order_date DATE NOT NULL, -- 订单日期，不允许为空
amount NUMERIC(10, 2) NOT NULL, -- 订单金额，精确到小数点后两位，不允许为空
status VARCHAR(20), -- 订单状态，例如 'completed' 或 'pending'
CONSTRAINT sales_orders_pkey PRIMARY KEY (order_id, order_date) -- 设定主键约束，唯一标识每一条订单
)
PARTITION BY RANGE (order_date); -- 根据订单日期进行范围分区
5.2、创建分区表
CREATE TABLE sales_orders_2021
PARTITION OF sales_orders
FOR VALUES FROM ('2021-01-01') TO ('2021-12-31'); -- 2021年的订单分区
CREATE TABLE sales_orders_2022
PARTITION OF sales_orders
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31'); -- 2022年的订单分区
CREATE TABLE sales_orders_2023_q1_q2
PARTITION OF sales_orders
FOR VALUES FROM ('2023-01-01') TO ('2023-06-30'); -- 2023年第一季度和第二季度的订单分区
CREATE TABLE sales_orders_2023_q3_q4
PARTITION OF sales_orders
FOR VALUES FROM ('2023-07-01') TO ('2023-12-31'); -- 2023年第三季度和第四季度的订单分区
CREATE TABLE sales_orders_2024
PARTITION OF sales_orders
FOR VALUES FROM ('2024-01-01') TO ('2024-12-31'); -- 2024年的订单分区
5.3、创建生成数据的函数
CREATE OR REPLACE FUNCTION insert_random_data(
start_date DATE, -- 起始日期
end_date DATE, -- 结束日期
num_rows INT -- 生成的记录数量
) RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO sales_orders (
customer_id, -- 客户ID
order_date, -- 订单日期
amount, -- 订单金额
status -- 订单状态
)
SELECT
(random() * 1000)::int AS customer_id, -- 随机生成客户ID
generate_series(start_date, end_date, '1 day'::interval)::date AS order_date, -- 从起始日期到结束日期生成日期系列
(random() * 500 + 50)::numeric(10, 2) AS amount, -- 随机生成订单金额，范围在50到550之间
CASE WHEN random() > 0.5 THEN 'completed' ELSE 'pending' END AS status -- 随机生成订单状态
FROM generate_series(start_date, end_date, '1 day'::interval) -- 生成日期系列
LIMIT num_rows; -- 限制插入的记录数
END;
$$;
5.4、插入数据到表格
SELECT insert_random_data('2021-01-01', '2021-12-30', 50000);
SELECT insert_random_data('2022-01-01', '2022-12-30', 50000);
test=# select count(*) from sales_orders;
count
--------
100000
(1 row)
test=#
5.5、断开分区
alter table sales_orders detach partition sales_orders_2021;
test=# select count(*) from sales_orders;
count
-------
50000
(1 row)
5.6、再创建与原来那个分区表一样的表结构,添加两个分区
这种情况一般应用于，如给大表分区后的某个分区还是特别大，可以弄个二级分区，就像结构树那样嵌套下去
-- 创建2021年订单分区表
CREATE TABLE sales_orders_2021_p (
order_id SERIAL, -- 订单ID，自动递增
customer_id INT NOT NULL, -- 客户ID，不允许为空
order_date DATE NOT NULL, -- 订单日期，不允许为空
amount NUMERIC(10, 2) NOT NULL, -- 订单金额，精确到小数点后两位，不允许为空
status VARCHAR(20), -- 订单状态，例如 'completed' 或 'pending'
CONSTRAINT sales_orders_2021_p_pkey PRIMARY KEY (order_id, order_date) -- 主键约束
)
PARTITION BY RANGE (order_date); -- 根据订单日期进行范围分区
--新建两个分区
create table sales_orders_2021_p_1 partition of sales_orders_2021_p for VALUES FROM ('2021-01-01') TO ('2021-5-31');
create table sales_orders_2021_p_2 partition of sales_orders_2021_p for VALUES FROM ('2021-5-31') TO ('2021-12-31');
5.7、把原先那个总表的2021分区表数据导入现在分区表
insert into sales_orders_2021_p select * from sales_orders_2021;
5.8、把这个分区表加入到到之前的分区表分区中
test=# select count(*) from sales_orders;
count
-------
50000
(1 row)
alter table sales_orders ATTACH PARTITION sales_orders_2021_p FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');
test=# select count(*) from sales_orders;
count
--------
100000
(1 row)
5.9、查看当前的分区表信息
--以下就是带有二级分区的分区表
test=# \d+ sales_orders;
Partitioned table "public.sales_orders"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+-----------------------+-----------+----------+------------------------------------------------+----------+-------------+--------------+-------------
order_id | integer | | not null | nextval('sales_orders_order_id_seq'::regclass) | plain | | |
customer_id | integer | | not null | | plain | | |
order_date | date | | not null | | plain | | |
amount | numeric(10,2) | | not null | | main | | |
status | character varying(20) | | | | extended | | |
Partition key: RANGE (order_date)
Indexes:
"sales_orders_pkey" PRIMARY KEY, btree (order_id, order_date)
Partitions: sales_orders_2021_p FOR VALUES FROM ('2021-01-01') TO ('2021-12-31'), PARTITIONED,
sales_orders_2022 FOR VALUES FROM ('2022-01-01') TO ('2022-12-31'),
sales_orders_2023_q1_q2 FOR VALUES FROM ('2023-01-01') TO ('2023-06-30'),
sales_orders_2023_q3_q4 FOR VALUES FROM ('2023-07-01') TO ('2023-12-31'),
sales_orders_2024 FOR VALUES FROM ('2024-01-01') TO ('2024-12-31')
test=# \d+;
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------------------------------+-------------------+-------+-------------+---------------+------------+-------------
public | sales_orders | partitioned table | fbase | permanent | | 0 bytes |
public | sales_orders_2021 | table | fbase | permanent | heap | 3048 kB |
public | sales_orders_2021_p | partitioned table | fbase | permanent | | 0 bytes |
public | sales_orders_2021_p_1 | table | fbase | permanent | heap | 1248 kB |
public | sales_orders_2021_p_2 | table | fbase | permanent | heap | 1768 kB |
public | sales_orders_2021_p_order_id_seq | sequence | fbase | permanent | | 8192 bytes |
public | sales_orders_2022 | table | fbase | permanent | heap | 2976 kB |
public | sales_orders_2023_q1_q2 | table | fbase | permanent | heap | 16 kB |
public | sales_orders_2023_q3_q4 | table | fbase | permanent | heap | 16 kB |
public | sales_orders_2024 | table | fbase | permanent | heap | 16 kB |
public | sales_orders_order_id_seq | sequence | fbase | permanent | | 8192 bytes |
(11 rows)
5393
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
