建站网站那个好,站外推广渠道有哪些,江西seo推广方案,没有网站域名备案信息什么是分库分表 分库分表是一种数据库架构设计的方法#xff0c;用于应对大规模数据的存储和查询。当单个数据库的存储容量或查询性能无法满足需求时#xff0c;可以通过将数据分散存储在多个数据库服务器上#xff0c;以提高系统的可扩展性和性能。 分库分表通常包…什么是分库分表 分库分表是一种数据库架构设计的方法用于应对大规模数据的存储和查询。当单个数据库的存储容量或查询性能无法满足需求时可以通过将数据分散存储在多个数据库服务器上以提高系统的可扩展性和性能。 分库分表通常包括两个步骤分库和分表。
分库 分库是指将单个数据库按照一定规则划分为多个数据库每个数据库可以存储一部分数据。这样可以减少单个数据库的数据量提高查询效率。常见的分库方式包括垂直分库和水平分库。 垂直分库是指按照功能模块或业务领域将数据分成多个数据库。例如可以将订单数据、用户数据、商品数据分别存储在不同的数据库中。 水平分库是指按照数据特征将数据分成多个数据库例如按照时间、地理位置等。例如可以将订单数据按照月份分别存储在不同的数据库中。
分表 分表是指将单个表按照一定规则划分为多个表每个表可以存储一部分数据。这样可以减少单个表的数据量提高查询效率。常见的分表方式包括垂直分表和水平分表。 垂直分表是指按照功能模块或业务领域将表分成多个部分。例如可以将订单表按照订单状态分成多个部分。 水平分表是指按照数据特征将表分成多个部分例如按照时间、地理位置等。例如可以将订单表按照月份分别存储在不同的表中。
从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年龄为 010 [1020, [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 优缺点
分库分表都有 一定的优缺点下面来盘点下。
优点
提高系统可扩展性通过将数据分散存储在多个数据库服务器上可以提高系统的可扩展性方便扩展存储容量和处理能力。提高系统性能通过将数据分散存储在多个数据库服务器上可以提高系统的性能减少单个数据库的压力。降低数据冗余通过将数据分散存储在多个数据库服务器上可以降低数据冗余减少数据丢失的风险。
缺点
复杂性分库分表需要对数据进行划分和维护增加了系统的复杂性和维护成本。数据一致性分库分表可能导致数据不一致需要额外的机制来保证数据的一致性。事务处理分库分表可能会影响事务的处理需要额外的机制来支持跨库的事务处理。
