进阶数据库系列（十三）：PostgreSQL 分区分表

概述

在组件开发迭代的过程中，随着使用时间的增加，数据库中的数据量也不断增加，因此数据库查询越来越慢。

通常加速数据库的方法很多，如添加特定的索引，将日志目录换到单独的磁盘分区，调整数据库引擎的参数等。这些方法都能将数据库的查询性能提高到一定程度。

对于许多应用数据库来说，许多数据是历史数据并且随着时间的推移它们的重要性逐渐降低。如果能找到一个办法将这些可能不太重要的数据隐藏，数据库查询速度将会大幅提高。可以通过DELETE来达到此目的，但同时这些数据就永远不可用了。

分区表是关系型数据库提供的一个亮点特性，比如Oracle对分区表的支持已经非常成熟，广泛使用于生产系统，PostgreSQL也支持分区表，只是道路有些曲折，早在10版本之前PostgreSQL分区表一般通过继承加触发器方式实现，这种分区方式不能算是内置分区表，而且步骤非常烦琐，PostgreSQL10版本一个重量级的新特性是支持内置分区表，在分区表方面前进了一大步，目前支持范围分区和列表分区。

表分区

表分区是指在逻辑上将一个大表拆分为较小的物理部分。分区可以带来几个好处：

  • 在某些情况下，查询性能可以显著提高，尤其是当表的大多数大量访问的行都放在单个分区或少量分区中时。分区取代了索引的前导列，减小了索引大小，使索引中大量使用的部分更可能适合内存。
  • 当查询或更新访问单个分区的很大一部分时，可以通过利用该分区的顺序扫描来提高性能，而不是使用分散在整个表中的索引和随机访问读取。
  • 如果分区设计中计划了分区，则可以通过添加或删除分区来完成批量加载和删除。使用执行或删除单个分区比批量操作快得多。
  • 很少使用的数据可以迁移到更便宜、更慢的存储介质。

只有当一个表会很大时，这些好处通常才是值得的。表将从分区中受益的确切点取决于应用程序，尽管经验法则是表的大小应超过数据库服务器的物理内存。

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

随着使用时间的增加，数据库中的数据量也不断增加，因此数据库查询越来越慢。

加速数据库的方法很多，如添加特定的索引，将日志目录换到单独的磁盘分区，调整数据库引擎的参数等。这些方法都能将数据库的查询性能提高到一定程度。

对于许多应用数据库来说，许多数据是历史数据并且随着时间的推移它们的重要性逐渐降低。如果能找到一个办法将这些可能不太重要的数据隐藏，数据库查询速度将会大幅提高。可以通过DELETE来达到此目的，但同时这些数据就永远不可用了。

PostgreSQL 11 内置分区分表

PostgreSQL 中的分区支持

PostgreSQL从10.0版本开始，开始引入内置分区机制partition。

Partition数据库表分区把一个大的物理表分成若干个小的物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。

  • 主表/父表/Master Table　该表是创建子表的模板。它是一个正常的普通表，但通常情况下它应该并不储存任何数据，而是将所有记录重定向到子表中进行存储。
  • 子表/分区表/Child Table/Partition Table　这些表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系，也就是说，一个主表包含多个分区表，而一个分区表只从属于一个主表
数据库表分区的优势
  • 在特定场景下，查询性能可以极大提高，尤其是当大部分经常访问的数据记录在一个或少数几个分区表上时。表分区减小了索引的大小，并使得常访问的分区表的索引更容易保存于内存中。
  • 当查询或者更新访问一个或少数几个分区表中的大部分数据时，可以通过顺序扫描该分区表而非使用大表索引来提高性能。
  • 可通过添加或移除分区表来高效的批量增删数据。如可使用ALTER TABLE NO INHERIT可将特定分区从主逻辑表中移除（该表依然存在，并可单独使用，只是与主表不再有继承关系并无法再通过主表访问该分区表），或使用DROP TABLE直接将该分区表删除。这两种方式完全避免了使用DELETE时所需的VACUUM额外代价。
  • 很少使用的数据可被迁移到便宜些的慢些的存储介质中

以上优势只有当表非常大的时候才能体现出来。一般来说，当表的大小超过数据库服务器的物理内存时以上优势才能体现出来。

PostgreSQL 11 的新特性

PostgreSQL从10版本支持通过表继承来实现表的分区。父表是普通表并且正常情况下并不存储任何数据，它的存在只是为了代表整个数据集。

从11版本开始PostgreSQL可实现如下3种表分区。

  • 范围分区　每个分区表包含一个或多个字段组合的一部分，并且每个分区表的范围互不重叠。比如可近日期范围分区
  • 列表分区　分区表显示列出其所包含的列值
  • 哈希分区 PostgreSQL11版本引入，可以根据自定义的hash规则，通过为每个分区指定模数和余数来对表进行分区。每个分区将保存分区键的哈希值除以指定的模数将生成指定余数的行。

如果项目组件的数据表需要使用上面未列出的表分区形式，可以使用替代方法（如基于10版本的继承和视图）。这些方法通常更具有灵活性，但可能部分特性没有内置的分区优化，所幸的是，目前PostgreSQL 11 版本已经对此做了大量优化。

PostgreSQL 内置分区表使用

PostgreSQL 10 一个重量级新特性是支持内置分区表，用户不需要预先在父表上定义INSERT、DELETE、UPDATE 触发器，对父表的DML操作会自动路由到相应分区，相比传统分区表大幅度降低了维护成本，目前仅支持范围分区和列表分区，本小节将以创建范围分区表为例，演示 PostgreSQL 10 内置分区表的创建、使用与性能测试。

创建分区表

创建分区表的主要语法包含两部分:创建主表和创建分区。创建主表语法如下:

代码语言：javascript

复制

CREATE TABLE table_name ( ... ){ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) }

创建主表时须指定分区方式，可选的分区方式为RANGE范围分区或LIST列表分区,并指定字段或表达式作为分区键。 创建分区的语法如下:

代码语言：javascript

复制

CREATE TABLE table_name
PARTITION OF parent_table [ (
) ] FOR VALUES partition_bound_spec

创建分区时必须指定是哪张表的分区，同时指定分区策略partition_bound_spec，如果是范围分区，partition_bound_spec须指定每个分区分区键的取值范围，如果是列表分区partition_bound_spec，需指定每个分区的分区键值。

PostgreSQL10创建内置分区表主要分为以下几个步骤：

  • 创建父表，指定分区键和分区策略。
  • 创建分区，创建分区时须指定分区表的父表和分区键的取值范围，注意分区键的范围不要有重叠，否则会报错。
  • 在分区上创建相应索引，通常情况下分区键上的索引是必须的，非分区键的索引可根据实际应用场景选择是否创建。
内置分区表注意事项

使用内置分区表有以下注意事项:

  • 1.当往父表上插入数据时，数据会自动根据分区键路由规则插入到分区中，目前仅支持范围分区和列表分区。
  • 2.分区表上的索引、约束需使用单独的命令创建，目前没有办法一次性自动在所有分区上创建索引、约束。
  • 3.内置分区表不支持定义（全局）主键，在分区表的分区上创建主键是可以的。
  • 4.内置分区表的内部实现使用了继承。
  • 5.如果UPDATE语句的新记录违反当前分区键的约束则会报错，UPDAET语句的新记录目前不支持跨分区的情况。
  • 6.性能方面:根据本节的测试场景，内置分区表根据非分区键查询相比普通表性能差距较大，因为这种场景分区表的执行计划会扫描所有分区;根据分区键查询相比普通表性能有小幅降低，而查询分区表子表性能相比普通表略有提升。
  • 继承式表分区的使用

PostgreSQL从10版本开始，引入了基于继承的分区机制。

创建主表/父表

不用为该表定义任何检查限制，除非需要将该限制应用到所有的分区表中。同样也无需为该表创建任何索引和唯一限制。这里我们以项目开发中常用到的告警查询为例，创建一张tb_test_alarm表。

代码语言：javascript

复制

CREATE TABLE public.tb_test_alarm (id varchar(64) NOT NULL,alarm_type varchar(10) NOT NULL,happen_time timestamptz NOT NULL,create_time timestamptz NULL,update_time timestamptz NULL,"desc" text NULL,device_id varchar(64) NOT NULL,CONSTRAINT tb_test_pk PRIMARY KEY (id)
);

创建的表结构如下图所示

创建子分区表

每个分区表必须继承自主表，并且正常情况下都不要为这些分区表添加任何新的列。子表尽量保持和父表一致的字段。

此处以每月分区表为例

代码语言：javascript

复制

create table tb_test_alarm_2020_12 () inherits (tb_test_alarm);
create table tb_test_alarm_2020_11 () inherits (tb_test_alarm);
create table tb_test_alarm_2020_10 () inherits (tb_test_alarm);
create table tb_test_alarm_2020_09 () inherits (tb_test_alarm);
创建分区表路由函数

代码语言：javascript

复制

        --创建分区函数
CREATE OR REPLACE FUNCTION alarm_partition_trigger()
RETURNS TRIGGER AS $$
BEGINIF NEW.happen_time >= '2020-09-01 00:00:00' and NEW.happen_time <= '2020-09-30 23:59:59'THENINSERT INTO tb_test_alarm_2020_09 VALUES (NEW.*);ELSIF NEW.happen_time >= '2020-10-01 00:00:00' and NEW.happen_time <= '2020-10-31 23:59:59'THENINSERT INTO tb_test_alarm_2020_10 VALUES (NEW.*);ELSIF NEW.happen_time >= '2020-11-01 00:00:00' and NEW.happen_time <= '2020-11-30 23:59:59'THENINSERT INTO tb_test_alarm_2020_11 VALUES (NEW.*);ELSIF NEW.happen_time >= '2020-12-01 00:00:00' and NEW.happen_time <= '2020-12-31 23:59:59'THENINSERT INTO tb_test_alarm_2020_12 VALUES (NEW.*);END IF;RETURN NULL;
END;
$$
LANGUAGE plpgsql;
--挂载分区Trigger
CREATE TRIGGER insert_almart_partition_trigger
BEFORE INSERT ON tb_test_alarm
FOR EACH ROW EXECUTE PROCEDURE alarm_partition_trigger();

插入成功后，可以看到100万条数据成功执行了插入，且由于我们前面编写的分区路由函数生效，数据会根据happen_time自动的插入到子表中。这里数据仍会显示在父表中，但是实际上父表仅仅作为整个分区表结构的展示，实际插入的记录是保存在子表中。如下图所示。

设置分表约束前，查询效率。执行查询语句

代码语言：javascript

复制

explain analyze select * from tb_test_alarm tta where happen_time < '2020-12-01 00:00:00'

结果如下：

需要消耗 474.307ms，扫描主表下所有子表来查询。

在执行查询时，PostgreSQL默认将会把查询条件应用到该表结构的所有分区上，因为PosgreSQL不知道这些分区表表名和表内容的关联性。于是需要添加表约束，它会告诉数据库这些表的内容，并允许规划器根据条件去查询对应的子分区，这样在很多情况下，能极大地加快查询速度。

应用分区表约束的语法为ADD CHECK(CONDITION)

代码语言：javascript

复制

ALTER TABLE tb_test_alarm_2020_12
ADD CONSTRAINT tb_test_alarm_2020_12_check_time_key
CHECK (happen_time>='2020-12-01 00:00:00' and happen_time <= '2020-12-31 23:59:59');
ALTER TABLE tb_test_alarm_2020_11
ADD CONSTRAINT tb_test_alarm_2020_11_check_time_key
CHECK (happen_time>='2020-11-01 00:00:00' and happen_time <= '2020-11-30 23:59:59');
ALTER TABLE tb_test_alarm_2020_10
ADD CONSTRAINT tb_test_alarm_2020_10_check_time_key
CHECK (happen_time>='2020-10-01 00:00:00' and happen_time <= '2020-10-31 23:59:59');
ALTER TABLE tb_test_alarm_2020_09
ADD CONSTRAINT tb_test_alarm_2020_09_check_time_key
CHECK (happen_time>='2020-09-01 00:00:00' and happen_time <= '2020-09-30 23:59:59');

建议对每个分区表增加一个特定的约束，以防止全表查询扫描查询时间过长。

并且在PostgreSQL中，这些表约束是可以重叠的，但一般来说创建非重叠的表约束会更好。重叠的表约束只有在一定特定场景下有意义。

在创建好上述告警信息表及分区表后，我们可以执行一次插入操作和查询，并分析其查询计划来查看分区是否生效以及效果如何。

再次执行查询操作，会发现，sql没有去查询表4的内容，时间也有所缩短：

参考文章：https://blog.csdn.net/Auspicious_air/article/ details/130058329 https://blog.csdn.net/qq_36125072/ article/details/125523756

原文引用：进阶数据库系列（十三）：PostgreSQL 分区分表-腾讯云开发者社区-腾讯云

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/437635.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

无人化焦炉四大车系统 武汉正向科技 工业机车无人远程控制系统

无人化焦炉四大车系统 武汉正向科技 工业机车无人远程控制系统

焦炉四大车无人化系统介绍 采用格雷母线光编码尺双冗余定位技术&#xff0c;炉门视觉定位自学习技术&#xff0c;wifi5G无线通讯技术&#xff0c;激光雷达安全识别技术&#xff0c;焦化智慧调度&#xff0c;手机APP监控功能。 焦炉四大车无人化系统功能 该系统能自动生成生产…
阅读更多...
IDTL：茶叶病害识别数据集（猫脸码客 第205期）

IDTL：茶叶病害识别数据集（猫脸码客 第205期）

Identifying Disease in Tea Leaves茶叶病害识别数据集 一、引言 在农业领域&#xff0c;茶叶作为一种重要的经济作物&#xff0c;其生产过程中的病害防治是确保茶叶质量和产量的关键环节。然而&#xff0c;传统的病害识别方法主要依赖于人工观察和经验判断&#xff0c;这不仅…
阅读更多...
Nature Machine Intelligence 基于强化学习的扑翼无人机机翼应变飞行控制

Nature Machine Intelligence 基于强化学习的扑翼无人机机翼应变飞行控制

尽管无人机技术发展迅速&#xff0c;但复制生物飞行的动态控制和风力感应能力&#xff0c;仍然遥不可及。生物学研究表明&#xff0c;昆虫翅膀上有机械感受器&#xff0c;即钟形感受器campaniform sensilla&#xff0c;探测飞行敏捷性至关重要的复杂气动载荷。 近日&#xff0…
阅读更多...
STM32引脚PB3、PB4、PA15作为输入输出的特殊配置

STM32引脚PB3、PB4、PA15作为输入输出的特殊配置

一、问题描述 简单描述&#xff1a; 最近做的一个项目中&#xff0c;PB3端口配置为输入&#xff0c;不管外部输入是高电平还是低电平&#xff0c;一直读取到的是低电平。 调试过程&#xff1a;在撰写代码过程中&#xff0c;又发现新的问题&#xff0c;Enter按键无法控制屏幕数…
阅读更多...
【Python】ftfy 使用指南：修复 Unicode 编码问题

【Python】ftfy 使用指南：修复 Unicode 编码问题

ftfy&#xff08;fixes text for you&#xff09;是一个专为修复各种文本编码错误而设计的 Python 工具。它的主要目标是将损坏的 Unicode 文本恢复为正确的 Unicode 格式。ftfy 并非用于处理非 Unicode 编码&#xff0c;而是旨在修复因为编码不一致、解码错误或混合编码导致的…
阅读更多...
物流行业中的AI平台架构与智能化应用

物流行业中的AI平台架构与智能化应用

随着物流行业的迅速发展&#xff0c;尤其是电商、仓储、运输的需求日益增多&#xff0c;AI技术逐渐成为推动物流企业高效运营、提升服务水平的关键力量。AI平台架构为物流行业的各个环节提供了智能化解决方案&#xff0c;助力物流企业在仓储管理、运输调度、客户服务等方面实现…
阅读更多...
Redis: Sentinel工作原理和故障迁移流程

Redis: Sentinel工作原理和故障迁移流程

Sentinel 哨兵几个核心概念 1 ) 定时任务 Sentinel 它是如何工作的&#xff0c;是如何感知到其他的 Sentinel 节点以及 Master/Slave节点的就是通过它的一系列定时任务来做到的&#xff0c;它内部有三个定时任务 第一个就是每一秒每个 Sentinel 对其他 Sentinel 和 Redis 节点…
阅读更多...
【2023工业3D异常检测文献】Shape-Guided: 基于形状引导和双记忆库的异常检测方法

【2023工业3D异常检测文献】Shape-Guided: 基于形状引导和双记忆库的异常检测方法

Shape-Guided Dual-Memory Learning for 3D Anomaly Detection 1、Background 提出了一个以形状为指导的专家学习框架&#xff0c;用于解决无监督3D异常检测的问题。 该方法建立在两个专门的专家模型及其协同作用的基础上&#xff0c;以从颜色和形状模态中定位异常区域。 第…
阅读更多...
基于单片机跑步机控制系统设计

基于单片机跑步机控制系统设计

** 文章目录 前言概要功能设计设计思路 软件设计效果图 程序文章目录 前言 &#x1f497;博主介绍&#xff1a;✌全网粉丝10W,CSDN特邀作者、博客专家、CSDN新星计划导师&#xff0c;一名热衷于单片机技术探索与分享的博主、专注于 精通51/STM32/MSP430/AVR等单片机设计 主要对…
阅读更多...
详细分析BigDecimal基本知识（附Demo）

详细分析BigDecimal基本知识（附Demo）

目录 前言1. 基本知识2. Demo 前言 之所以深入了解这个函数的用法&#xff0c;发现还可这么使用 基本的Java知识推荐阅读&#xff1a; java框架 零基础从入门到精通的学习路线 附开源项目面经等&#xff08;超全&#xff09;【Java项目】实战CRUD的功能整理&#xff08;持续更…
阅读更多...
多区域OSPF路由协议

多区域OSPF路由协议

前言 之前也有过关于OSPF路由协议的博客&#xff0c;但都不是很满意&#xff0c;不是很完整。现在也是听老师讲解完OSPF路由协议&#xff0c;感触良多&#xff0c;所以这里重新整理一遍。这次应该是会满意的 一些相关概念 链路状态 链路指路由器上的一个接口&#xff0c;链路状…
阅读更多...
ChatGPT实时语音将于本周向免费用户推出：OpenAI DevDay 2024详细解读

ChatGPT实时语音将于本周向免费用户推出：OpenAI DevDay 2024详细解读

大家好&#xff0c;我是木易&#xff0c;一个持续关注AI领域的互联网技术产品经理&#xff0c;国内Top2本科&#xff0c;美国Top10 CS研究生&#xff0c;MBA。我坚信AI是普通人变强的“外挂”&#xff0c;专注于分享AI全维度知识&#xff0c;包括但不限于AI科普&#xff0c;AI工…
阅读更多...
Windows 环境搭建 CUDA 和 cuDNN 详细教程

Windows 环境搭建 CUDA 和 cuDNN 详细教程

CUDA CUDA&#xff08;Compute Unified Device Architecture&#xff09;是由NVIDIA公司推出的一个并行计算平台和编程模型&#xff0c;它允许开发者使用NVIDIA GPU进行通用计算&#xff08;即GPGPU&#xff09;&#xff0c;从而加速各种计算密集型任务。CUDA提供了一套基于C/C…
阅读更多...
深度学习：cGAN和pix2pix图像转换

深度学习：cGAN和pix2pix图像转换

cGAN和pix2pix的基础概念 cGAN cGAN是条件生成对抗网络&#xff08;Conditional Generative Adversarial Networks&#xff09;的简称。 它是一种基于基础GAN&#xff08;Generative Adversarial Networks&#xff09;架构的变体&#xff0c;通过给GAN模型引入额外的信息或条…
阅读更多...
【零基础入门产品经理】学习准备篇 | 需要学一些什么呢？

【零基础入门产品经理】学习准备篇 | 需要学一些什么呢？

前言&#xff1a; 零实习转行产品经理经验分享01-学习准备篇_哔哩哔哩_bilibili 该篇内容主要是对bilibili这个视频的观后笔记~谢谢美丽滴up主友情分享。 全文摘要&#xff1a;如何在0实习且没有任何产品相关经验下&#xff0c;如何上岸产品经理~ 目录 一、想清楚为什么…
阅读更多...
uniapp 上了原生的 echarts 图表插件了 兼容性还行

uniapp 上了原生的 echarts 图表插件了 兼容性还行

插件地址&#xff1a;echarts - DCloud 插件市场 兼容性这块儿不知道后期会不会支持其他浏览器 H5 的话建议可以用原生的不用这个插件
阅读更多...
沐风老师3DMAX快速嵌板修改器插件Panelizer使用方法

沐风老师3DMAX快速嵌板修改器插件Panelizer使用方法

3DMAX快速嵌板修改器插件Panelizer&#xff0c;一键快速几何体分布工具&#xff0c;基于3dsMax的拓扑修改器&#xff0c;将几何模型散布并对齐到四边形面上的参数化建模工具。 【系统要求】 3dsMax 2018.1 - 2025 【安装方法】 1.将插件安装文件&#xff08;.mcg&#xff09;…
阅读更多...
Llama 系列简介与 Llama3 预训练模型推理

Llama 系列简介与 Llama3 预训练模型推理

1. Llama 系列简介 1.1 Llama1 由 Meta AI 发布&#xff0c;包含 7B、13B、33B 和 65B 四种参数规模的开源基座语言模型 数据集&#xff1a;模型训练数据集使用的都是开源的数据集&#xff0c;总共 1.4T token 模型结构&#xff1a;原始的 Transformer 由编码器&#xff08…
阅读更多...
基于元神操作系统实现NTFS文件操作（二）

基于元神操作系统实现NTFS文件操作（二）

1. 背景 本文主要介绍MBR的读取和解析&#xff0c;并提供了基于元神操作系统的实现代码。由于解析MBR的目的是定位到NTFS磁盘分区进行文件操作&#xff0c;所以只解析了MBR的分区表部分&#xff0c;至于MBR的其它部分&#xff0c;可以参考相关文档进行理解。 2. 方法 &#…
阅读更多...
Python使用matplotlib绘制五星红旗

Python使用matplotlib绘制五星红旗

安装包 pip install matplotlib pip install numpy完整代码 import matplotlib.pyplot as plt import numpy as np# 设置画布尺寸 fig plt.figure(figsize(10, 7)) ax fig.add_subplot(111)# 绘制红色背景 ax.set_facecolor(red)# 五星红旗的尺寸定义 flag_width 30 flag_…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023