数据库必知必会系列：数据库分区与分表策略

发布于：2023-09-28 ⋅ 阅读:(82) ⋅ 点赞:(0)

作者：禅与计算机程序设计艺术

1.简介

随着互联网网站的发展，数据量越来越大，单个数据库已经无法承载如此之多的数据。为了解决这一问题，大型公司通常都会将数据库进行拆分，将一个大的数据库按照业务逻辑拆分成多个小的、更易管理的数据库集合。而分库、分表是一个比较常用的技术手段。

分库与分表并不是独立的两个技术，而是在一起使用的，即使没有考虑到性能、安全等问题，只要按需增加节点，就可以实现数据库水平扩展。

本文中，作者从零开始，系统性地介绍了数据库分区与分表技术的概念、术语、原理及应用。

2.背景介绍

2.1 大型网站的数据量问题

随着互联网网站的发展，网站用户的数量和数据量不断增加，这给网站开发者带来巨大的压力。目前已有的网站已经超负荷运转，响应时间明显延迟，甚至严重超时。根据一些分析，大概率是由于网站的数据量太大，导致数据库查询效率下降，进而影响用户体验和销售额。因此，如何对数据库进行合理的设计是网站运营者面临的一大难题。

2.2 分库分表的目的

通过把大量数据划分成多个小的、易于管理的数据库，可以有效解决网站数据量过大的问题。对于一个大型网站来说，通常都需要采用分库分表的方式来应对快速增长的数据量。通过分库分表，可以将数据分布到不同的物理服务器上，从而提高数据库查询效率和处理能力。这样做还可以降低各台服务器之间的硬件资源占用，提高网站的整体运行速度。

分库分表最主要的三个作用如下：

  1. 数据集中存储：通过分库分表，可以将相同数据类型的数据放在同一个库或者表中，减少索引的数量，降低磁盘空间占用，方便数据检索。
  2. 读写分离：通过设置多个库，每个库部署多个服务器，可以将读写操作分摊到不同服务器上，增加吞吐量。同时也便于应对单个服务器的性能瓶颈。
  3. 水平扩展：当网站访问量增加时，可以通过添加新的库或表来横向扩展数据库，达到数据库水平扩展的效果。

2.3 分库分表的原则

对数据库进行分库分表，除了应对数据量过大的问题外，还有一个重要的原则就是数据库切割原则。一般情况下，建议按照时间、地域、业务功能、业务类型进行切割，将数据按照切割依据进行分类管理。如下图所示，通过分库分表，可以帮助网站更好地进行数据管理和优化。

按照数据库切割原则，分库分表后的数据结构如下所示：

数据库名称：库名_业务类型（如：crm、order）
表名称：库名_业务类型_表名（如：crm_customer、order_item）
字段名称：库名_业务类型_表名_字段名（如：crm_customer_name、order_item_price）

3.基本概念术语说明

3.1 分区

在传统的关系型数据库中，每一个数据库只能存放一个表，也就是说，所有的记录都在同一个地方。虽然这种做法简单直观，但是在大规模数据量的场景下，如果表越来越大，那么就会越来越慢。

为了解决这个问题，大部分数据库支持将表分成多个碎片，称为分区。一个表可以由多个分区组成，每个分区可以存储在不同的文件或磁盘上，从而实现“分布式”的概念。这种方式虽然能够有效地解决性能问题，但同时也引入了复杂性、维护成本和运维难度。

通常情况下，数据库会自动将数据分成多个分区，每个分区可以容纳一定范围内的数据。比如，MySQL 中通过定义分区字段来实现分区功能。通过分区功能，数据库可以轻松应对大量数据的读写需求，并且提升了数据库的性能。

3.2 分表

随着数据量的增加，一个数据库中的数据也变得越来越大。为了避免性能瓶颈和数据库管理上的困扰，大部分数据库提供了分表功能。分表是一种将大表中的数据划分为多个较小表的方法。

分表的优点如下：

  1. 提升性能：分表可以将一个大的表中的数据分散到不同的物理文件中，从而提升数据库的查询和更新性能。
  2. 便于管理：通过拆分表，可以方便地进行数据删除、备份、归档等操作。
  3. 更加灵活：如果业务发生变化，或者某些查询需要特殊的处理，可以将这些表拆分出来单独管理，从而提升系统的稳定性和灵活性。

3.3 MySQL中的分区和分表机制

MySQL 中的分区和分表都是利用数据库服务器自身的功能特性来实现的，并不需要使用第三方工具。

MySQL 的分区是一个非常强大的功能，它可以在创建表的时候就指定分区方案，而无需用户再次执行任何操作。分区的好处在于可以直接解决性能问题，并且分区不会影响到其他的工作负载。

MySQL 中的分表机制也是基于表的，它的好处在于可以快速地进行数据切割，而且不需要调整现有的 SQL 查询语句。但是，分表会影响数据库服务器的负载均衡、备份和恢复等工作，所以在大规模集群环境下，可能会出现问题。

4.核心算法原理和具体操作步骤以及数学公式讲解

4.1 创建分区表

创建分区表的方式和创建一个普通的表类似，只是在 CREATE TABLE 命令中增加分区字段，例如：

CREATE TABLE tablename (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    PRIMARY KEY (`id`)
) PARTITION BY RANGE (age)(
    PARTITION p0 VALUES LESS THAN (18),
    PARTITION p1 VALUES LESS THAN (30),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

这里，我们创建了一个 tablename 表，其中包括 id、name 和 age 三个字段。PARTITION BY RANGE (age) 表示分区方式为范围分区，这里的 RANGE 可以换成其他的分区方式，比如 LIST 分区和 KEY 分区等。PARTITIONS 3 指定分区的数量为3。

4.2 插入数据

插入数据的方式与插入普通表一样。比如，在 p1 分区中插入一条记录：

INSERT INTO tablename (name, age) VALUES ('Tom', 25);

当然，也可以通过 PARTITION 关键字指定插入的分区：

INSERT INTO tablename PARTITION (p1) (name, age) VALUES ('Jack', 26);

这里，PARTITION (p1) 指定插入的分区为 p1。

4.3 删除数据

删除数据的方式与删除普通表一样。比如，删除年龄大于等于20岁的所有记录：

DELETE FROM tablename WHERE age >= 20;

当然，也可以通过 PARTITION 关键字指定删除的分区：

DELETE FROM tablename PARTITION (p2) WHERE age < 18;

这里，PARTITION (p2) 指定删除的分区为 p2。

4.4 更新数据

更新数据的方式与更新普通表一样。比如，将年龄大于等于30岁的记录设置为NULL：

UPDATE tablename SET age = NULL WHERE age >= 30;

当然，也可以通过 PARTITION 关键字指定更新的分区：

UPDATE tablename PARTITION (p2) SET age = NULL WHERE age < 20;

这里，PARTITION (p2) 指定更新的分区为 p2。

4.5 查询数据

查询数据的方式与查询普通表一样。比如，查询所有数据：

SELECT * FROM tablename;

当然，也可以通过 PARTITION 关键字指定查询的分区：

SELECT * FROM tablename PARTITION (p1);

这里，PARTITION (p1) 指定查询的分区为 p1。

4.6 执行计划

执行计划（explain plan）是查看 MySQL 执行器采用哪种索引来优化查询的过程。执行计划通常包括表扫描和索引扫描两种方式。

4.6.1 表扫描

当没有满足条件的索引时，MySQL 会选择全表扫描。

4.6.2 索引扫描

当存在满足条件的索引时，MySQL 会通过索引查找匹配的数据行。

4.7 分区的优缺点

4.7.1 优点

  • 提升性能：通过分区可以将数据分布到不同的物理文件中，从而提升数据库的查询和更新性能。
  • 便于管理：通过拆分表，可以方便地进行数据删除、备份、归档等操作。
  • 更加灵活：如果业务发生变化，或者某些查询需要特殊的处理，可以将这些表拆分出来单独管理，从而提升系统的稳定性和灵活性。

4.7.2 缺点

  • 运维难度：分区是一个非常复杂的操作，需要对数据进行物理切割，而这一切都需要自己亲自实施。
  • 分布式事务：由于采用分区之后，一个表分散到了不同的物理位置，因此分布式事务的实现和应用就可能遇到很多问题。
  • 主键回填：分区后的空白数据，可能会因为分区键的分布不均匀而产生数据倾斜问题。

4.8 分表的优缺点

4.8.1 优点

  • 提升性能：通过分表可以将大表中的数据分散到不同的物理表中，从而提升数据库的查询和更新性能。
  • 便于管理：通过拆分表，可以方便地进行数据删除、备份、归档等操作。
  • 更加灵活：如果业务发生变化，或者某些查询需要特殊的处理，可以将这些表拆分出来单独管理，从而提升系统的稳定性和灵活性。

4.8.2 缺点

  • 维护难度：每张表都需要单独创建，增加了数据库维护的难度。
  • 垂直拆分：对于有关联关系的表，不能将其拆分到不同表中，造成了跨业务的耦合性。

4.9 分库分表的注意事项

  • 拆分粒度：选择合适的拆分粒度，不要将不同业务的表分配到同一个分区。
  • 数据一致性：由于分表导致数据不再落入同一台服务器中，因此对于事务要求比较高的场景，仍然需要通过中间件来保证数据一致性。
  • 避免热点问题：分表后，热点问题可能会发生，因此需要根据业务特点设置合适的分区规则。
  • 分区扩容：当分区的数量或者大小发生变化时，需要手动或自动地进行扩容操作。

5.具体代码实例和解释说明

5.1 Spring Boot项目中集成MyBatis-Plus

Spring Boot项目中集成MyBatis-Plus，使用分库分表功能来降低数据库压力。

首先，我们需要配置两个yml配置文件，分别为：

application.yml：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    username: root
    password: root
    driver-class-name: com.mysql.jdbc.Driver
    type: com.baomidou.mybatisplus.HikariDataSourceFactory

bootstrap.yml：

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true #驼峰命名
    cache-enabled: false #全局缓存开关，默认false
    call-setters-on-nulls: true #参数为null时，是否调用setter方法，默认值false
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #日志打印类型，默认为控制台输出
  mapper-locations: classpath*:mapper/*.xml
  global-config:
    db-config:
      id-type: auto
      logic-delete-value: -1
      logic-not-delete-value: 0
  plugins:
    paginationInterceptor: #分页插件
      enable: true
      pagehelper:
        reasonable: false
        support-methods-arguments: false
        params: count=countSql

然后，在工程目录下创建resources文件夹，并创建mapper和mapperXml文件夹。

在 mapper 文件夹下创建 UserMapper.java 接口：

public interface UserMapper extends BaseMapper<User> {

}

在 mapperXml 文件夹下创建 UserMapper.xml 配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">

  <!-- 根据 id 字段进行主键查询 -->
  <select id="selectById" parameterType="int" resultType="User">
    SELECT * FROM user WHERE id = #{id} AND deleted = 0 LIMIT 1
  </select>

  <!-- 插入数据 -->
  <insert id="insertUser" parameterType="User">
    INSERT INTO user (username, password, email)
    VALUES (#{username}, #{password}, #{email})
  </insert>

</mapper>

最后，在 Spring Boot 配置类中注入 Mapper 即可：

@Configuration
@MapperScan("com.example.demo.mapper")
public class DemoApplication implements WebMvcConfigurer {

    @Autowired
    private UserMapper userMapper;

    //...

    public static void main(String[] args) throws Exception {
        ConfigurableApplicationContext applicationContext = SpringApplication.run(DemoApplication.class, args);

        // 测试分库分表
        List<User> users = Arrays.asList(new User().setId(1).setUsername("user1").setPassword("<PASSWORD>"),
                new User().setId(2).setUsername("user2").setPassword("<PASSWORD>"));

        for (User user : users) {
            userMapper.insert(user);
        }

        System.out.println(userMapper.selectList(Wrappers.<User>lambdaQuery().eq(User::getUsername, "user1")));
    }

}

在这里，我们测试了通过 MyBatis-Plus 实现分库分表功能，同时也演示了简单的查询和插入操作。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布