数据库分表分库的原则

裹著新的光

于 2023-10-28 23:59:46 发布

阅读量128

点赞数 1

文章标签：数据库

本文链接：https://blog.csdn.net/qq_36896711/article/details/134098253

版权

数据库分表分库的原则

一、引言

随着业务量的增长，数据量也会随之增加，当单表的数据量达到千万行以后，数据库性能显著降低，并且优化索引、添加从库等方案可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担，缩短查询的时间。

二、分表方案

垂直分表

按字段进行分表，一般将使用频率高的字段放到一张表里，剩下使用频率低的字段放到另一张表里

分表前

id column1 column2 column3 column4
1
2
3
分表后

id column1 column2
1
2
3

id column3 column4
1
2
3
优点：
- 行记录变小，数据页可以存放更多的记录。
- 只需要查询有所需字段的分表，在查询时减少了IO次数，可提高查询性能。
缺点：
- 主键出现冗余，需要管理冗余列。
- 当需要进行关联查询时，由于数据被分散到不同的子表中，需要进行多次查询和数据合并，可能会影响查询性能。
- 依然存在单表数据量过大的问题。
水平分表

按照一定规则，例如主键id或时间等来进行数据的拆分。水平分表下每个表的结构是相同的。

分表前

id column1 column2 column3 column4
1
2
3
4
分表后

id column1 column2 column3 column4
1
2

id column1 column2 column3 column4
3
4
优点：
- 单表数据量减少，提高了查询性能。
- 可以同时对多个表进行查询和操作，提高了系统的并行处理能力。
- 当需要扩展系统的存储容量时，通过水平分表，可以将新数据分散到新的表中，实现系统的可扩展性。
缺点：
- 分布式事务和数据同步难以解决。
- 当需要进行数据迁移或数据备份时，需要额外的工作量。
- 跨表查询时，需要额外的处理和操作，可能会增加开发和维护的复杂性。
- 关联查询时需要进行多次查询和数据合并，可能会影响查询性能。

分区表

分区是把一张表的数据分成N多个区块。分区表是一个独立的逻辑表，但是底层由多个物理子表组成。当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。

mysql支持多种类型的分区表，包括范围分区、列表分区、哈希分区和复合分区。

范围分区表：根据一个范围值将数据分区，通常用于按时间或数字范围进行分区。

CREATE TABLE sales (
    id INT,
    date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE(YEAR(date)) (
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN MAXVALUE
);

上述例子将sales表按照date列的年份进行范围分区，分为2018年、2019年、2020年和2021年以及之后的分区。

列表分区表：根据一个离散的值列表将数据分区，通常用于按照特定列的值进行分区。

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50)
)
PARTITION BY LIST(department) (
    PARTITION p_sales VALUES IN ('Sales'),
    PARTITION p_marketing VALUES IN ('Marketing'),
    PARTITION p_hr VALUES IN ('HR'),
    PARTITION p_it VALUES IN ('IT'),
    PARTITION p_others VALUES IN (DEFAULT)
);

上述例子将employees表按照department列的值进行列表分区，分为销售部门、市场部门、人力资源部门、IT部门以及其他部门的分区。

哈希分区表：根据哈希算法将数据分区，通常用于均匀地分散数据。
```
CREATE TABLE customers (
    id INT,
    name VARCHAR(50),
    email VARCHAR(50)
)
PARTITION BY HASH(id) PARTITIONS 4;
```
上述例子将customers表使用哈希分区，根据id列的哈希值将数据分散到4个分区中。

复合分区表：结合两种或多种分区方式，根据多个列的值进行分区。

CREATE TABLE orders (
    id INT,
    date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE(YEAR(date))
SUBPARTITION BY HASH(customer_id)
SUBPARTITIONS 4 (
    PARTITION p2018 VALUES LESS THAN (2019) (
        SUBPARTITION s1,
        SUBPARTITION s2,
        SUBPARTITION s3,
        SUBPARTITION s4
    ),
    PARTITION p2019 VALUES LESS THAN (2020) (
        SUBPARTITION s1,
        SUBPARTITION s2,
        SUBPARTITION s3,
        SUBPARTITION s4
    ),
    PARTITION p2020 VALUES LESS THAN (2021) (
        SUBPARTITION s1,
        SUBPARTITION s2,
        SUBPARTITION s3,
        SUBPARTITION s4
    ),
    PARTITION p2021 VALUES LESS THAN MAXVALUE (
        SUBPARTITION s1,
        SUBPARTITION s2,
        SUBPARTITION s3,
        SUBPARTITION s4
    )
);

上述例子将orders表按照date列的年份进行范围分区，并在每个年份分区中使用哈希分区，将数据分散到4个子分区中。

如果说分表是因为单表数据量过大，那么整个数据库的数据量过大时就应该考虑分库了。

三、分库方案

分库是将数据按照一定的规则分散存储在多个数据库中，可以提高数据库的扩展性和性能。以下是一些常见的分库方案：

垂直分库：按照业务功能将表拆分到不同的数据库中。例如，将用户信息表、订单表和产品表分别存储在不同的数据库中。这种方案适用于业务功能之间的数据关联较少的情况。
水平分库：按照某个字段的值将数据划分到不同的数据库中。例如，按照用户ID的哈希值将用户数据分散存储在多个数据库中。
分片分库：将数据按照某个规则进行分片，将每个分片存储在不同的数据库中。例如，按照用户ID的范围将用户数据分片存储在多个数据库中。
分区分库：将数据按照某个字段的值进行分区，并将每个分区存储在不同的数据库中。例如，按照时间将数据分区存储在不同的数据库中。

需要根据具体的业务需求和数据库性能考虑，选择适合的分库方案。同时，还需要考虑数据一致性、跨库事务等问题，并合理设计分库的数据访问策略。

裹著新的光

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
数据库分表分库的原则

在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。随着业务量的增长，数据量也会随之增加，当单表的数据量达到千万行以后，数据库性能显著降低，并且优化索引、添加从库等方案可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。上述例子将orders表按照date列的年份进行范围分区，并在每个年份分区中使用哈希分区，将数据分散到4个子分区中。列表分区表：根据一个离散的值列表将数据分区，通常用于按照特定列的值进行分区。
复制链接

扫一扫