[SQL系列] 从头开始学PostgreSQL 分库分表
什么是分库分表
分库分表是一种数据库架构设计的方法,用于应对大规模数据的存储和查询。当单个数据库的存储容量或查询性能无法满足需求时,可以通过将数据分散存储在多个数据库服务器上,以提高系统的可扩展性和性能。
分库分表通常包括两个步骤:分库和分表。
分库
分库是指将单个数据库按照一定规则划分为多个数据库,每个数据库可以存储一部分数据。这样可以减少单个数据库的数据量,提高查询效率。常见的分库方式包括垂直分库和水平分库。
垂直分库是指按照功能模块或业务领域将数据分成多个数据库。例如,可以将订单数据、用户数据、商品数据分别存储在不同的数据库中。
水平分库是指按照数据特征将数据分成多个数据库,例如按照时间、地理位置等。例如,可以将订单数据按照月份分别存储在不同的数据库中。
分表
分表是指将单个表按照一定规则划分为多个表,每个表可以存储一部分数据。这样可以减少单个表的数据量,提高查询效率。常见的分表方式包括垂直分表和水平分表。
垂直分表是指按照功能模块或业务领域将表分成多个部分。例如,可以将订单表按照订单状态分成多个部分。
水平分表是指按照数据特征将表分成多个部分,例如按照时间、地理位置等。例如,可以将订单表按照月份分别存储在不同的表中。
从PostgreSQL 11开始,就有三种表分区:
1. 范围分区(Range Partition)
范围分区是将表按照某个列的值划分成一段或多段。每个分区的端点值存储在 pg_partition_range 系统表中。范围分区支持基于时间戳的自动分区,例如根据日期列自动创建每天、每月、每年等分区。
2. 列表分区(List Partition)
列表分区是将表按照某个列的值存储在数组中,每个分区的值存储在 pg_partition_list 系统表中。列表分区的支持比较灵活,可以自定义分区值,也可以使用预先定义好的列表进行分区。
3. 哈希分区(Hash Partition)
哈希分区是将表按照某个列的值进行哈希运算,将结果映射到不同的分区。哈希分区可以使用任何哈希函数,例如 MD5、SHA1 等。哈希分区的优点是可以平均分布数据,避免某个分区存储过多数据,提高查询效率。
示例
1. 创建主表
首先,我们需要创建一个主表,用于存储所有分表的公共字段和索引。在示例中,我们创建一个名为 customers 的表,其中包含 id、name、age 和 address 列。
testdb=# CREATE TABLE customers ( id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT NOT NULL, address VARCHAR(100) NOT NULL
);
2. 创建分表
接下来,我们需要创建多个分表,每个分表都包含主表的所有字段和额外的特定字段。在示例中,我们创建年龄分区表
user=# create table customers_10 () inherits (customers);
CREATE TABLE
user=# create table customers_20 () inherits (customers);
CREATE TABLE
user=# create table customers_30 () inherits (customers);
CREATE TABLE
user=#user=# \dList of relationsSchema | Name | Type | Owner
--------+-----------------------+----------+-------public | customers | table | userpublic | customers_10 | table | userpublic | customers_20 | table | userpublic | customers_30 | table | user
3. 定义分表规则
使用 PostgreSQL 提供的分表规则(partitioning)功能,定义如何将数据分配到不同的分表中。在示例中,我们使用 AGE 列作为分表规则,将数据分配到 customers_age 分表中。
首先创建一个function,年龄为 (0,10), [10,20), [20, ...)分别插入三张不同的表里。
然后创建一个trigger,在插入到customers之前开始执行这个function。
这样子当我们向这个customers表插入数据的时候
user=# create or replace function customers_partition_trigger()
returns trigger as $$
begin
if NEW.age < 10 then
insert into customers_10 values (NEW.*);
elseif NEW.age < 20 then
insert into customers_20 values (NEW.*);
else insert into customers_30 values (NEW.*);
end if;
return null;
end;
$$
language plpgsql;
CREATE FUNCTIONuser=# create trigger insert_customers_partition_trigger
user-# before insert on customers
user-# for each row execute procedure customers_partition_trigger();
CREATE TRIGGER
4. 向表中插入数据,这里数据仍会显示在父表中,但是实际上父表仅仅作为整个分区表结构的展示,实际插入的记录是保存在子表中。
user=# INSERT INTO customers VALUES (1, 'Alice', 25, 'New York');
INSERT 0 0
user=# INSERT INTO customers VALUES (2, 'Bob', 35, 'San Francisco');
INSERT 0 0
user=# INSERT INTO customers VALUES (3, 'Charlie', 18, 'Chicago');
INSERT 0 0
user=# INSERT INTO customers VALUES (3, 'Charlie', 18, 'Chicago');
INSERT 0 0
user=# select * from customers;id | name | age | address
----+---------+-----+---------------3 | Charlie | 18 | Chicago3 | Charlie | 18 | Chicago1 | Alice | 25 | New York2 | Bob | 35 | San Francisco
(4 rows)user=# select * from customers_10;id | name | age | address
----+------+-----+---------
(0 rows)user=# select * from customers_20;id | name | age | address
----+---------+-----+---------3 | Charlie | 18 | Chicago3 | Charlie | 18 | Chicago
(2 rows)user=# select * from customers_30;id | name | age | address
----+-------+-----+---------------1 | Alice | 25 | New York2 | Bob | 35 | San Francisco
(2 rows)
5. 设置分表约束,加快查询效率。因为如果查询主表的话,会直接扫描所有的子表来查询,但是如果加上constraint的话,会允许规划器根据条件查询对应的子分区,在数据很多的情况下可以加快查询速度。
user=# alter table customers_10
user-# add constraint customers_10_check_age_key
user-# check (age < 10);
ALTER TABLEuser=# alter table customers_20
user-# add constraint customers_20_check_age_key
user-# check (age < 20);
ALTER TABLEuser=# alter table customers_30
user-# add constraint customers_30_check_age_key
user-# check (age < 30);
ALTER TABLE
优缺点
分库分表都有 一定的优缺点,下面来盘点下。
优点
- 提高系统可扩展性:通过将数据分散存储在多个数据库服务器上,可以提高系统的可扩展性,方便扩展存储容量和处理能力。
- 提高系统性能:通过将数据分散存储在多个数据库服务器上,可以提高系统的性能,减少单个数据库的压力。
- 降低数据冗余:通过将数据分散存储在多个数据库服务器上,可以降低数据冗余,减少数据丢失的风险。
缺点
- 复杂性:分库分表需要对数据进行划分和维护,增加了系统的复杂性和维护成本。
- 数据一致性:分库分表可能导致数据不一致,需要额外的机制来保证数据的一致性。
- 事务处理:分库分表可能会影响事务的处理,需要额外的机制来支持跨库的事务处理。
相关文章:
[SQL系列] 从头开始学PostgreSQL 分库分表
什么是分库分表 分库分表是一种数据库架构设计的方法,用于应对大规模数据的存储和查询。当单个数据库的存储容量或查询性能无法满足需求时,可以通过将数据分散存储在多个数据库服务器上,以提高系统的可扩展性和性能。 分库分表通常包…...
【VScode】Remote-SSH XHR failed无法访问远程服务器
问题概述 当使用VScode连接远程服务器时,往往需要使用Remote-SSH这个插件。而该插件有一个小bug,当远程服务器网络不佳时容易出现。 在控制台会出现下述语句: Resolver error: Error: XHR failed at y.onerror (vscode-file://vscode-app/…...
pycharm打开terminal报错
Pycharm打开终端报错如何解决?估计是终端启动conda不顺利,需要重新设置路径。参考以下文章的做法即可。 Windows下Pycharm中Terminal无法进入conda环境和Python Console 不能使用 给pycharm中Terminal 添加新的shell,才可以使用conda环境 W…...
C#与C/C++交互(1)——需要了解的基础知识
【前言】 C#中用于实现调用C/C的方案是P/Invoke(Platform Invoke),让托管代码可以调用库中的函数。类似的功能,JAVA中叫JNI,Python中叫Ctypes。 常见的代码用法如下: [DllImport("Test.dll", E…...
LeetCode笔记:Weekly Contest 356
LeetCode笔记:Weekly Contest 356 1. 题目一 1. 解题思路2. 代码实现 2. 题目二 1. 解题思路2. 代码实现 3. 题目三 1. 解题思路2. 代码实现 4. 题目四 1. 解题思路2. 代码实现 比赛链接:https://leetcode.com/contest/weekly-contest-356/ 1. 题目一…...
2 Python的基础语法
概述 在上一节的内容中,我们介绍了Python的诞生、发展历程、特色、缺点和应用领域。从本节开始,我们将正式学习Python。Python是一门简洁和优雅的语言,有自己特殊的一些语法规则。因此,在介绍Python编程的有关知识之前,…...
抖音seo矩阵系统源代码开发搭建技术分享
抖音SEO矩阵系统是一个较为复杂的系统,其开发和搭建需要掌握一定的技术。以下是一些技术分享: 技术分享 抖音SEO矩阵系统的源代码可以使用JAVA、Python、PHP等多种语言进行开发。其中,JAVA语言的应用较为广泛,因为JAVA语言有良好…...
python#django数据库一对一/一对多/多对多
一对一OneToOneField 用户和用户信息 搭建 # 一对一 class TestUser(models.Model): usernamemodels.CharField(max_length32) password models.CharField(max_length32) class TestInfo(models.Model): mick_namemodels.CharField(max_length32) usermode…...
记RT-Thread rt_timer_start函数的问题
我使用的RT-Thread版本为4.0.3。 我看了5.0.1的代码,此问已经被修复。 在4.0.3版本中的rt_timer_start函数源码如下: rt_err_t rt_timer_start(rt_timer_t timer) {unsigned int row_lvl;rt_list_t *timer_list;register rt_base_t level;rt_list_t *r…...
C++初阶——拷贝构造和运算符重载(const成员)
目录 1. 拷贝构造函数 1.2 拷贝构造函数特征: 2. 默认拷贝构造函数 2.1 未显式定义,编译器会生成默认的拷贝构造函数。 默认的拷贝构造函数对象按内存存储按字节序完成拷贝,这种拷贝叫做浅拷贝,或者值拷贝 3. 运算符重载 3.1…...
go练习 day01
DTO: note_dto.go package dtoimport "king/model"type NoteAddDTO struct {ID uintTitle string json:"title" form:"title" binding:"required" message:"标题不能为空"Content string json:"conten…...
C# Blazor 学习笔记(0.1):如何开始Blazor和vs基本设置
文章目录 前言资源推荐环境如何开始Blazor个人推荐设置注释快捷键热重载设置 前言 Blazor简单来说就是微软提供的.NET 前端框架。使用 WebAssembly的“云浏览器”,集成了Vue,React,Angular等知名前端框架的特点。 资源推荐 微软官方文档 Blazor入门基础视频合集 …...
原码的乘法运算 补码乘法运算
补码乘法 对比...
找不到d3dx9_43.dll丢失怎么解决(分享几种解决方法)
为什么我们打开电脑软件或许游戏时候,电脑会报错出现d3dx9_43.dll丢失,或许找不到d3dx9_43.dll等等的提示。下面来详细介绍一下d3dx9_43.dll详细解决方法跟d3dx9_43.dll是什么。 如果你的系统中没有安装或安装不完整的d3dx9_43.dll运行时,应…...
篇四:建造者模式:逐步构造复杂对象
篇四:“建造者模式:逐步构造复杂对象” 设计模式是软件开发中的重要组成部分,建造者模式是创建型设计模式中的一种。建造者模式旨在逐步构造复杂对象,将对象的构造与其表示分离,从而使得同样的构建过程可以创建不同的…...
vs导出和导入动态库和静态库
1. 动态库和导出和导入 1.1 动态库的导出 1. 创建新项目 新建新项目,选择动态链接库(DLL)。 填写项目名称,并选择项目保存的路径,然后点击创建。 创建完成后,会自动生成如下所示文件,可以根据…...
30 使用easyExcel依赖生成Excel
30.1 导入依赖 <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId&…...
排序进行曲-v2.0
文章目录 小程一言直接插入排序步骤举例复杂度分析应用场景实际举例代码实现 希尔排序步骤举例复杂度分析应用场景实际举例代码实现 堆排序步骤举例复杂度分析应用场景实际举例代码实现 小程一言 这篇文章是在排序进行曲1.0之后的续讲, 由于在上一篇讲的排序的基本…...
反弹shell的N种姿势
预备知识1. 关于反弹shell 就是控制端监听在某TCP/UDP端口,被控端发起请求到该端口,并将其命令行的输入输出转到控制端。reverse shell与telnet,ssh等标准shell对应,本质上是网络概念的客户端与服务端的角色反转。2. 反弹shel…...
创意视频剪辑教程:快速合并视频并标题,让你的作品更吸睛!
想要让你的视频作品脱颖而出,引人注目?不再担心,我们为你带来了一款创意视频剪辑教程,教你如何快速合并视频并添加令人惊艳的标题效果!让你的作品在分钟内变得酷炫而精彩,向世界展示你的创意! …...
聊聊 Pulsar:Producer 源码解析
一、前言 Apache Pulsar 是一个企业级的开源分布式消息传递平台,以其高性能、可扩展性和存储计算分离架构在消息队列和流处理领域独树一帜。在 Pulsar 的核心架构中,Producer(生产者) 是连接客户端应用与消息队列的第一步。生产者…...
【机器视觉】单目测距——运动结构恢复
ps:图是随便找的,为了凑个封面 前言 在前面对光流法进行进一步改进,希望将2D光流推广至3D场景流时,发现2D转3D过程中存在尺度歧义问题,需要补全摄像头拍摄图像中缺失的深度信息,否则解空间不收敛…...
服务器硬防的应用场景都有哪些?
服务器硬防是指一种通过硬件设备层面的安全措施来防御服务器系统受到网络攻击的方式,避免服务器受到各种恶意攻击和网络威胁,那么,服务器硬防通常都会应用在哪些场景当中呢? 硬防服务器中一般会配备入侵检测系统和预防系统&#x…...
Python实现prophet 理论及参数优化
文章目录 Prophet理论及模型参数介绍Python代码完整实现prophet 添加外部数据进行模型优化 之前初步学习prophet的时候,写过一篇简单实现,后期随着对该模型的深入研究,本次记录涉及到prophet 的公式以及参数调优,从公式可以更直观…...
土地利用/土地覆盖遥感解译与基于CLUE模型未来变化情景预测;从基础到高级,涵盖ArcGIS数据处理、ENVI遥感解译与CLUE模型情景模拟等
🔍 土地利用/土地覆盖数据是生态、环境和气象等诸多领域模型的关键输入参数。通过遥感影像解译技术,可以精准获取历史或当前任何一个区域的土地利用/土地覆盖情况。这些数据不仅能够用于评估区域生态环境的变化趋势,还能有效评价重大生态工程…...
Rust 异步编程
Rust 异步编程 引言 Rust 是一种系统编程语言,以其高性能、安全性以及零成本抽象而著称。在多核处理器成为主流的今天,异步编程成为了一种提高应用性能、优化资源利用的有效手段。本文将深入探讨 Rust 异步编程的核心概念、常用库以及最佳实践。 异步编程基础 什么是异步…...
【JavaSE】多线程基础学习笔记
多线程基础 -线程相关概念 程序(Program) 是为完成特定任务、用某种语言编写的一组指令的集合简单的说:就是我们写的代码 进程 进程是指运行中的程序,比如我们使用QQ,就启动了一个进程,操作系统就会为该进程分配内存…...
Leetcode33( 搜索旋转排序数组)
题目表述 整数数组 nums 按升序排列,数组中的值 互不相同 。 在传递给函数之前,nums 在预先未知的某个下标 k(0 < k < nums.length)上进行了 旋转,使数组变为 [nums[k], nums[k1], …, nums[n-1], nums[0], nu…...
ubuntu22.04有线网络无法连接,图标也没了
今天突然无法有线网络无法连接任何设备,并且图标都没了 错误案例 往上一顿搜索,试了很多博客都不行,比如 Ubuntu22.04右上角网络图标消失 最后解决的办法 下载网卡驱动,重新安装 操作步骤 查看自己网卡的型号 lspci | gre…...
数据结构:递归的种类(Types of Recursion)
目录 尾递归(Tail Recursion) 什么是 Loop(循环)? 复杂度分析 头递归(Head Recursion) 树形递归(Tree Recursion) 线性递归(Linear Recursion)…...
