access数据库网站,随州市住房和城乡建设部网站,企业二级域名自助建站平台,wordpress百度分享插件下载一、什么是表分区#xff1f;它和分库分表有什么区别#xff1f;1.1 什么是表分区#xff08;Table Partitioning#xff09;#xff1f;表分区是数据库的一种物理设计技术#xff0c;它将一个大表从逻辑上视为一个整体#xff0c;但从物理上拆分成多个子表#xff08;…一、什么是表分区它和分库分表有什么区别1.1 什么是表分区Table Partitioning表分区是数据库的一种物理设计技术它将一个大表从逻辑上视为一个整体但从物理上拆分成多个子表分区每个分区存储一部分数据。逻辑上你仍然像操作一张表一样查询它物理上数据分散在多个子表中按规则存储✅ 举个例子把 jgpt_jzd_test 按年份拆成 p2023、p2024、p2025 三个分区查询时仍用 SELECT * FROM jgpt_jzd_test但数据库只扫描相关分区。1.2 表分区 vs 分库分表关键区别对比项表分区分库分表实现层级数据库内部单库应用层或中间件跨库透明性高应用无感知低需改代码管理复杂度低自动路由高需路由规则事务支持完整支持跨库事务复杂适用场景单表过大百万~亿级数据量极大TB级技术栈PostgreSQL、MySQL 8.0ShardingSphere、MyCat
✅ 简单说表分区是“数据库帮你拆”分库分表是“你自己写代码拆”
二、表分区的优缺点与使用场景✅ 优点优势说明查询性能提升分区剪枝Partition Pruning自动跳过无关分区数据管理高效删除旧数据从 DELETE 变为 DROP PARTITION秒级维护更方便可对单个分区做 VACUUM、ANALYZE、备份I/O 分散不同分区可分布到不同磁盘高级用法❌ 缺点缺点说明全表扫描变慢需扫描所有分区元数据开销增加分区键固定一旦选定如 gmt_create不能更改管理复杂度上升需定期创建新分区不支持主键跨分区主键必须包含分区键三、PostgreSQL 表分区的三种方式PostgreSQL 支持三种分区策略1. Range 分区按范围适用时间、数值范围示例按 gmt_create 按年/月分区语法
PARTITION BY RANGE (gmt_create)2. List 分区按枚举值适用固定分类如省份、状态示例按 province 分区语法
PARTITION BY LIST (province)3. Hash 分区按哈希值适用数据均匀分布无明显查询模式示例按 id 哈希分 4 份语法
PARTITION BY HASH (id)四、实战jgpt_jzd_test 表分区操作全流程将 3000 万 的 jgpt_jzd_test 表改造为按年分区的分区表。步骤 1创建分区主表
-- 创建主表逻辑表不存数据
CREATE TABLE jgpt_jzd_test_partitioned (id varchar(32),jzdbh varchar(255),xzb varchar(255),yzb varchar(255),htxxid varchar(255),gmt_create timestamp(6) NOT NULL, -- 必须 NOT NULLgmt_modified timestamp(6),del_flag varchar,created_user_id varchar(255),created_user varchar(255),last_modified_user_id varchar(255),last_modified_user varchar(255),dkh varchar(255),dkms varchar(255),batchnum varchar(255)
) PARTITION BY RANGE (gmt_create);步骤 2创建子分区按年
-- 2023 年分区
CREATE TABLE jgpt_jzd_test_p2023 PARTITION OF jgpt_jzd_test_partitionedFOR VALUES FROM (2023-01-01) TO (2024-01-01);-- 2024 年分区
CREATE TABLE jgpt_jzd_test_p2024 PARTITION OF jgpt_jzd_test_partitionedFOR VALUES FROM (2024-01-01) TO (2025-01-01);-- 2025 年分区
CREATE TABLE jgpt_jzd_test_p2025 PARTITION OF jgpt_jzd_test_partitionedFOR VALUES FROM (2025-01-01) TO (2026-01-01);步骤 3迁移数据
-- 从旧表插入到新分区表自动路由
INSERT INTO jgpt_jzd_test_partitioned
SELECT * FROM jgpt_jzd_test;步骤 4创建索引
-- 在主表创建索引所有分区自动继承
CREATE INDEX idx_jgpt_jzd_test_htxxid ON jgpt_jzd_test_partitioned (htxxid);
步骤 5切换表名原子操作
-- 1. 备份原表
ALTER TABLE jgpt_jzd_test RENAME TO jgpt_jzd_test_backup;-- 2. 新表启用原名
ALTER TABLE jgpt_jzd_test_partitioned RENAME TO jgpt_jzd_test;✅ 至此jgpt_jzd_test 已是分区表五、验证表分区是否成功1. 查看分区结构-- 查询系统表
SELECT inhrelid::regclass AS child_table,inhparent::regclass AS parent_table
FROM pg_inherits
WHERE inhparent jgpt_jzd_test::regclass;2. 验证分区剪枝是否生效
explain SELECT COUNT(*) FROM jgpt_jzd_test WHERE gmt_create 2025-01-01 AND gmt_create 2026-01-01;
可以看到只查询了jgpt_jzd_test_p2025一张分区表六、安全删除分区DETACH vs DROP关键区别在表分区的日常维护中删除历史数据是一个高频操作。PostgreSQL 提供了两种方式来“移除”分区但它们的安全性、可逆性和使用场景完全不同。我们以 jgpt_jzd_test_p2024 分区为例对比两种操作1. DETACH PARTITION —— 安全的“解绑”操作
ALTER TABLE jgpt_jzd_test_partitionedDETACH PARTITION jgpt_jzd_test_p2024;✅ 操作特点
数据不会丢失jgpt_jzd_test_p2024 表变成一个独立的普通表主表 jgpt_jzd_test_partitioned 不再包含该分区的数据可随时对 jgpt_jzd_test_p2024 进行查询、导出、备份或重新挂载适用场景
需要归档数据删除前做审计或备份不确定是否永久删除-- 确认无误后再删除
DROP TABLE jgpt_jzd_test_p2024;✅ 推荐做法先 DETACH再 DROP避免误删。2. DROP PARTITION —— 永久删除
ALTER TABLE jgpt_jzd_test_partitionedDROP PARTITION jgpt_jzd_test_p2024;❌ 操作特点
数据立即永久丢失无法通过 DROP 回滚相当于执行了 DROP TABLE文件被物理删除无法恢复除非有数据库备份
⚠️ 适用场景
确认数据不再需要紧急释放磁盘空间自动化脚本中已确认安全
对比总结操作数据是否保留是否可逆安全性推荐使用场景DETACH PARTITION✅ 保留✅ 可逆高所有删除操作的首选DROP PARTITION❌ 丢失❌ 不可逆低确认永久删除七、自己的理解1.表分区之后,你在datagrip或navicat里面看到的还是一张表数据也都在这张表里但实际上这张表是主表没有存储数据。2.数据实际存储在分区表里例如jgpt_jzd_test_p2024因此代码里面是可以直接调用这张表的3.平时基本不用管分区表正常使用主表就行了比如你插入数据直接往jgpt_jzd_test插入数据库会根据你的gmt字段自动插入到相应的分区表里面平时使用基本是无感的✅ 总结我的认知升级旧认知新认知分区就是“拆表”分区是“逻辑统一物理分离”数据存在主表主表是“空壳”数据在分区只能查主表可直查分区性能更优分区很复杂日常使用完全无感
