当前位置: 首页 > news >正文

[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 分库分表

什么是分库分表 分库分表是一种数据库架构设计的方法&#xff0c;用于应对大规模数据的存储和查询。当单个数据库的存储容量或查询性能无法满足需求时&#xff0c;可以通过将数据分散存储在多个数据库服务器上&#xff0c;以提高系统的可扩展性和性能。 分库分表通常包…...

【VScode】Remote-SSH XHR failed无法访问远程服务器

问题概述 当使用VScode连接远程服务器时&#xff0c;往往需要使用Remote-SSH这个插件。而该插件有一个小bug&#xff0c;当远程服务器网络不佳时容易出现。 在控制台会出现下述语句&#xff1a; Resolver error: Error: XHR failed at y.onerror (vscode-file://vscode-app/…...

pycharm打开terminal报错

Pycharm打开终端报错如何解决&#xff1f;估计是终端启动conda不顺利&#xff0c;需要重新设置路径。参考以下文章的做法即可。 Windows下Pycharm中Terminal无法进入conda环境和Python Console 不能使用 给pycharm中Terminal 添加新的shell&#xff0c;才可以使用conda环境 W…...

C#与C/C++交互(1)——需要了解的基础知识

【前言】 C#中用于实现调用C/C的方案是P/Invoke&#xff08;Platform Invoke&#xff09;&#xff0c;让托管代码可以调用库中的函数。类似的功能&#xff0c;JAVA中叫JNI&#xff0c;Python中叫Ctypes。 常见的代码用法如下&#xff1a; [DllImport("Test.dll", E…...

LeetCode笔记:Weekly Contest 356

LeetCode笔记&#xff1a;Weekly Contest 356 1. 题目一 1. 解题思路2. 代码实现 2. 题目二 1. 解题思路2. 代码实现 3. 题目三 1. 解题思路2. 代码实现 4. 题目四 1. 解题思路2. 代码实现 比赛链接&#xff1a;https://leetcode.com/contest/weekly-contest-356/ 1. 题目一…...

2 Python的基础语法

概述 在上一节的内容中&#xff0c;我们介绍了Python的诞生、发展历程、特色、缺点和应用领域。从本节开始&#xff0c;我们将正式学习Python。Python是一门简洁和优雅的语言&#xff0c;有自己特殊的一些语法规则。因此&#xff0c;在介绍Python编程的有关知识之前&#xff0c…...

抖音seo矩阵系统源代码开发搭建技术分享

抖音SEO矩阵系统是一个较为复杂的系统&#xff0c;其开发和搭建需要掌握一定的技术。以下是一些技术分享&#xff1a; 技术分享 抖音SEO矩阵系统的源代码可以使用JAVA、Python、PHP等多种语言进行开发。其中&#xff0c;JAVA语言的应用较为广泛&#xff0c;因为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的代码&#xff0c;此问已经被修复。 在4.0.3版本中的rt_timer_start函数源码如下&#xff1a; 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 拷贝构造函数特征&#xff1a; 2. 默认拷贝构造函数 2.1 未显式定义&#xff0c;编译器会生成默认的拷贝构造函数。 默认的拷贝构造函数对象按内存存储按字节序完成拷贝&#xff0c;这种拷贝叫做浅拷贝&#xff0c;或者值拷贝 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的“云浏览器”&#xff0c;集成了Vue,React,Angular等知名前端框架的特点。 资源推荐 微软官方文档 Blazor入门基础视频合集 …...

原码的乘法运算 补码乘法运算

补码乘法 对比...

找不到d3dx9_43.dll丢失怎么解决(分享几种解决方法)

为什么我们打开电脑软件或许游戏时候&#xff0c;电脑会报错出现d3dx9_43.dll丢失&#xff0c;或许找不到d3dx9_43.dll等等的提示。下面来详细介绍一下d3dx9_43.dll详细解决方法跟d3dx9_43.dll是什么。 如果你的系统中没有安装或安装不完整的d3dx9_43.dll运行时&#xff0c;应…...

篇四:建造者模式:逐步构造复杂对象

篇四&#xff1a;“建造者模式&#xff1a;逐步构造复杂对象” 设计模式是软件开发中的重要组成部分&#xff0c;建造者模式是创建型设计模式中的一种。建造者模式旨在逐步构造复杂对象&#xff0c;将对象的构造与其表示分离&#xff0c;从而使得同样的构建过程可以创建不同的…...

vs导出和导入动态库和静态库

1. 动态库和导出和导入 1.1 动态库的导出 1. 创建新项目 新建新项目&#xff0c;选择动态链接库&#xff08;DLL&#xff09;。 填写项目名称&#xff0c;并选择项目保存的路径&#xff0c;然后点击创建。 创建完成后&#xff0c;会自动生成如下所示文件&#xff0c;可以根据…...

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之后的续讲&#xff0c; 由于在上一篇讲的排序的基本…...

反弹shell的N种姿势

预备知识1. 关于反弹shell 就是控制端监听在某TCP/UDP端口&#xff0c;被控端发起请求到该端口&#xff0c;并将其命令行的输入输出转到控制端。reverse shell与telnet&#xff0c;ssh等标准shell对应&#xff0c;本质上是网络概念的客户端与服务端的角色反转。2. 反弹shel…...

创意视频剪辑教程:快速合并视频并标题,让你的作品更吸睛!

想要让你的视频作品脱颖而出&#xff0c;引人注目&#xff1f;不再担心&#xff0c;我们为你带来了一款创意视频剪辑教程&#xff0c;教你如何快速合并视频并添加令人惊艳的标题效果&#xff01;让你的作品在分钟内变得酷炫而精彩&#xff0c;向世界展示你的创意&#xff01; …...

智慧医疗能源事业线深度画像分析(上)

引言 医疗行业作为现代社会的关键基础设施,其能源消耗与环境影响正日益受到关注。随着全球"双碳"目标的推进和可持续发展理念的深入,智慧医疗能源事业线应运而生,致力于通过创新技术与管理方案,重构医疗领域的能源使用模式。这一事业线融合了能源管理、可持续发…...

C++:std::is_convertible

C++标志库中提供is_convertible,可以测试一种类型是否可以转换为另一只类型: template <class From, class To> struct is_convertible; 使用举例: #include <iostream> #include <string>using namespace std;struct A { }; struct B : A { };int main…...

基于ASP.NET+ SQL Server实现(Web)医院信息管理系统

医院信息管理系统 1. 课程设计内容 在 visual studio 2017 平台上&#xff0c;开发一个“医院信息管理系统”Web 程序。 2. 课程设计目的 综合运用 c#.net 知识&#xff0c;在 vs 2017 平台上&#xff0c;进行 ASP.NET 应用程序和简易网站的开发&#xff1b;初步熟悉开发一…...

高危文件识别的常用算法:原理、应用与企业场景

高危文件识别的常用算法&#xff1a;原理、应用与企业场景 高危文件识别旨在检测可能导致安全威胁的文件&#xff0c;如包含恶意代码、敏感数据或欺诈内容的文档&#xff0c;在企业协同办公环境中&#xff08;如Teams、Google Workspace&#xff09;尤为重要。结合大模型技术&…...

土地利用/土地覆盖遥感解译与基于CLUE模型未来变化情景预测;从基础到高级,涵盖ArcGIS数据处理、ENVI遥感解译与CLUE模型情景模拟等

&#x1f50d; 土地利用/土地覆盖数据是生态、环境和气象等诸多领域模型的关键输入参数。通过遥感影像解译技术&#xff0c;可以精准获取历史或当前任何一个区域的土地利用/土地覆盖情况。这些数据不仅能够用于评估区域生态环境的变化趋势&#xff0c;还能有效评价重大生态工程…...

uniapp中使用aixos 报错

问题&#xff1a; 在uniapp中使用aixos&#xff0c;运行后报如下错误&#xff1a; AxiosError: There is no suitable adapter to dispatch the request since : - adapter xhr is not supported by the environment - adapter http is not available in the build 解决方案&…...

智能仓储的未来:自动化、AI与数据分析如何重塑物流中心

当仓库学会“思考”&#xff0c;物流的终极形态正在诞生 想象这样的场景&#xff1a; 凌晨3点&#xff0c;某物流中心灯火通明却空无一人。AGV机器人集群根据实时订单动态规划路径&#xff1b;AI视觉系统在0.1秒内扫描包裹信息&#xff1b;数字孪生平台正模拟次日峰值流量压力…...

select、poll、epoll 与 Reactor 模式

在高并发网络编程领域&#xff0c;高效处理大量连接和 I/O 事件是系统性能的关键。select、poll、epoll 作为 I/O 多路复用技术的代表&#xff0c;以及基于它们实现的 Reactor 模式&#xff0c;为开发者提供了强大的工具。本文将深入探讨这些技术的底层原理、优缺点。​ 一、I…...

【Java学习笔记】BigInteger 和 BigDecimal 类

BigInteger 和 BigDecimal 类 二者共有的常见方法 方法功能add加subtract减multiply乘divide除 注意点&#xff1a;传参类型必须是类对象 一、BigInteger 1. 作用&#xff1a;适合保存比较大的整型数 2. 使用说明 创建BigInteger对象 传入字符串 3. 代码示例 import j…...

基于Java Swing的电子通讯录设计与实现:附系统托盘功能代码详解

JAVASQL电子通讯录带系统托盘 一、系统概述 本电子通讯录系统采用Java Swing开发桌面应用&#xff0c;结合SQLite数据库实现联系人管理功能&#xff0c;并集成系统托盘功能提升用户体验。系统支持联系人的增删改查、分组管理、搜索过滤等功能&#xff0c;同时可以最小化到系统…...