分区表探索

xiaogang_987654

已于 2022-12-09 10:31:50 修改

阅读量198

点赞数

分类专栏：数据库管理文章标签：数据库

于 2022-11-17 22:21:39 首次发布

本文链接：https://blog.csdn.net/weixin_45571513/article/details/127912452

版权

数据库管理专栏收录该内容

11 篇文章 0 订阅

订阅专栏

什么时候考虑使用分区？

一张表的查询速度已经慢到影响使用的时候。
sql经过优化
数据量大
表中的数据是分段的
对数据的操作往往只涉及一部分数据，而不是所有的数据

分区解决的问题

主要可以提升查询效率

分区表类型

四种类型range，list，key，hash

CREATE TABLE range_table (
    id             int not null,
    user_name      varchar(50) not null,
    created_date   date not null
) partition by range (year(created_date))
(partition p01 values less than (2020),
partition p02 values less than (2021),
partition p03 values less than (2022),
partition p04 values less than (2023),
partition p05 values less than (MAXVALUE)
);

CREATE TABLE range_table (
    id             int not null,
    user_name      varchar(50) not null,
    created_date   date not null
) partition by list (year(created_date))
(partition p01 values in (2019,2020),
partition p02 values in (2021,2022),
partition p03 values in (2023,2024),
partition p04 values in (2025),
partition p05 values in (MAXVALUE)
);

CREATE TABLE range_table (
    id             int not null,
    user_name      varchar(50) not null,
    created_date   date not null
) partition by hash(year(created_date))
(partition 3;
);

自动分区（推荐）

场景：数据库中有张表要进行分区

1.新建新表，进行分区

2.把表数据导入导新分区表

指定起始值、间隔、分区个数
create_range_partitions(relation       REGCLASS,  -- 主表OID
                        attribute      TEXT,      -- 分区列名
                        start_value    ANYELEMENT,  -- 开始值
                        p_interval     INTERVAL,    -- 间隔；interval 类型，用于时间分区表
                        p_count        INTEGER DEFAULT NULL,   --  分多少个区
                        partition_data BOOLEAN DEFAULT TRUE)   --  是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )

指定起始值、终值、间隔
create_partitions_from_range(relation       REGCLASS,  -- 主表OID
                             attribute      TEXT,      -- 分区列名
                             start_value    ANYELEMENT,  -- 开始值
                             end_value      ANYELEMENT,  -- 结束值
                             p_interval     INTERVAL,    -- 间隔；interval 类型，用于时间分区表
                             partition_data BOOLEAN DEFAULT TRUE)   --  是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )

注意:  
1. 分区列必须有not null约束  
2. 分区个数必须能覆盖已有的所有记录

举例1：
创建分区，每个分区包含1个月的跨度数据  
select create_range_partitions('part_test'::regclass,             -- 主表OID
                               'crt_time',                        -- 分区列名
                               '2016-10-25 00:00:00'::timestamp,  -- 开始值
                               interval '1 month',                -- 间隔；interval 类型，用于时间分区表
                               24,                                -- 分多少个区
                               false) ;                           -- 不迁移数据

举例2：（实践过）
select create_range_partitions('part_test'::regclass,             -- 主表OID
                               'crt_time',                        -- 分区列名
                               '2020-09-01'::date,                -- 开始值
                               '1 month'::interval ,              -- 间隔；interval 类型，用于时间分区表
                               1,                                -- 分多少个区
                               false) ;                           -- 不迁移数据

转：PostgreSQL分区表实现——pg_pathman分区表管理 - Aiden郭祥跃 - 博客园

删除分区表

drop table tableName cascade