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

数据库分库分表的介绍

news 来源：原创 2024/9/19 13:36:20

为什么要分库分表

把存于一个库的数据分散到多个库中，把存于一个表的数据分散到多个表中。如果说读写分离是为了分散数据库读写操作压力，分库分表就是为了分散存储压力，一般情况下，单表数据量到达千万级别，就可以考虑分库分表了。

分库分表的原则：能不分就不分：优先MySQL调优，能不分就不分。

分库分表基本概念

分表

比如你单表都几千万数据了，单表数据量太大，会极大影响你的 sql执行的性能，到了后面你的 sql 可能就跑的很慢了。一般来说单表到几百万的时候，性能就会相对差一些了，你就得分表了。

分表是啥意思？就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。比如按照用户 id 来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在 200 万以内。

分库

分库是啥意思？就是你一个库一般我们经验而言，最多支撑到并发 2000，一定要扩容了，而且一个健康的单库并发值你最好保持在每秒 1000 左右，不要太大。那么你可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

分库分表实现

分库分表旨在，通过将大表、或大数据库的数据，切分为多个较小的部分，从而提升性能。

分库分表的方式

垂直切分

垂直分表：操作数据库中的某张表，把这张表中的一部分字段数据保存到一张新表里面，再把另一部分字段放另一张表，如：我们电商项目中的member表和member_info表

垂直分库：把单一数据库按照业务划分，（专库专用）

水平切分

水平分库：例如数据库，数据量持续增加数据量大的问题依然存在，这样可以把一个数据库拆分为多个相同的数据库，来分担数据量多的问题

水平分表：单表数据量过大

分表字段（sharding_key）选择

选择最佳的分表字段是一个需要仔细考虑的问题。最佳的分表字段应该是能够让数据分布均匀、频繁查询的字段以及不可变的字段。通过选择最佳的分表字段，可以提高系统的性能和查询效率

常用字段：

主键ID：频繁查询并且唯一，非常适合作分表字段。例如，在用户表中，用户ID作为分表字段是一个不错的选择，因为用户ID是唯一的，而且在查询用户信息时经常会用到。

时间字段：如果业务需要按时间范围查询数据，那么选择时间字段作为分表字段是合理的。例如，在日志表中，可以选择时间戳字段作为分表字段，以便按天、按月或按年分割数据，方便查询和维护。

地理信息字段：如果业务需要按地区查询数据，那么选择地理信息字段作为分表字段是合适的。例如，在订单表中，可以选择订单地区字段作为分表字段，以便将订单数据按地区进行拆分，方便查询和扩展。

关联字段：如果业务需要频繁进行关联查询，那么选择订单号等关联字段作为分表字段。例如，在订单表中，可以选择订单号作为分表字段，因为订单号唯一且包含业务信息，并且日常查询、关联查询都是根据订单号查询的，很少根据id查询，方便查询和维护。

选择分表字段的原则：

1. 数据分布均匀：最佳的分表字段应该是能够让数据分布均匀的字段，这样可以避免某个表的数据过多，导致查询效率降低。在用户表中，如果以地区作为分表字段，可能会导致某些地区的数据过多，而某些地区的数据过少。

2. 频繁查询的字段：尽量选择查询频率最高的字段（例如主键id），然后根据表拆分方式选择字段。在一个订单表中，如果经常需要根据用户ID查询订单信息，那么以用户ID作为分表字段是一个不错的选择。

3. 不可变字段：最佳的分表字段还应该是不可变的字段，这样可以避免在数据迁移时出现问题。在一个商品表中，如果选择以商品名称作为分表字段，那么当商品名称发生变化时，就需要将数据移动到不同的表中，这样会增加系统的复杂度。

ShardingSphere介绍

概览 :: ShardingSphere (apache.org)

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。

Sharding-JDBC是ShardingSphere的第一个产品，也是ShardingSphere的前身。它定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

配置是整个Sharding-JDBC的核心，是Sharding-JDBC中唯一与应用开发者打交道的模块。配置模块也是Sharding-JDBC的门户，通过它可以快速清晰的理解Sharding-JDBC所提供的功能。Sharding-JDBC提供了4种配置方式，用于不同的使用场景。通过配置，应用开发者可以灵活的使用分库分表、读写分离以及分库分表 + 读写分离共用。

java配置
yaml配置
springboot配置
spirng命名空间配置

分库分表并不是由 ShardingSphere-JDBC 来做，它是用来负责操作已经分完之后的 CRUD 操作。

分库分表是由数据库中间件来实现的。数据库中间件是位于应用程序和数据库之间的一层软件，它负责将数据库的访问请求进行拦截和解析，然后将请求分发到不同的数据库节点上进行处理。中间件会根据事先定义的规则将数据进行分片（分库分表），将数据分散存储在多个数据库节点上，从而提高数据库的扩展性和性能。

常见的数据库中间件有MySQL的MyCAT、阿里巴巴的TDDL、蚂蚁金服的OceanBase等。这些中间件通过内置的路由规则和分片算法，可以将数据的读写请求合理地路由到对应的数据库节点上，实现透明的分库分表操作。

因此，分库分表不是由ShardingSphere-JDBC这样的JDBC框架来完成的，而是由专门的数据库中间件来实现的。ShardingSphere-JDBC主要负责提供对分库分表后的数据进行操作的功能。

代码实例：

创建两个数据库edu_db_1,edu_db_2,每个库中创建两张表course_1,course_2

CREATE TABLE `product1` (`id` bigint NOT NULL,`cname` varchar(50) NOT NULL,`shelf_id` bigint NOT NULL,`status` varchar(10) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `product2` (`id` bigint NOT NULL,`cname` varchar(50) NOT NULL,`shelf_id` bigint NOT NULL,`status` varchar(10) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

导入相关依赖

1.分表不分库

配置对应实体类以及 Mapper

Spring Boot配置 :: ShardingSphere (apache.org)

# sharding-jdbc 水平分表策略
# 配置数据源，给数据源起别名
spring.shardingsphere.datasource.names=m1# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true# 配置数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/product_db_1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&tinyInt1isBit=falsespring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456# 指定product表分布的情况，配置表在哪个数据库里，表的名称都是什么 m1.product_1,m1.product_2
spring.shardingsphere.sharding.tables.product.actual-data-nodes=m1.product$->{1..2}# 指定 product 表里面主键 cid 的生成策略 SNOWFLAKE
#key-generator属性配置了他的主键列以及主键生成策略。
#ShardingJDBC默认提供了UUID和SNOWFLAKE两种分布式主键生成策略。
#spring.shardingsphere.sharding.tables.product.key-generator.column=id
#spring.shardingsphere.sharding.tables.product.key-generator.type=SNOWFLAKE# 配置分表策略  约定 cid 值偶数添加到 product 1表，如果 cid 是奇数添加到 product2表
spring.shardingsphere.sharding.tables.product.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product.table-strategy.inline.algorithm-expression=product$->{id % 2 + 1}# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true

测试代码运行

@Test
public void addProduct() {for (int i = 1; i < 50; i++) {int randomInt = RandomUtil.randomInt(1, 100);Product product = new Product();product.setId(Long.valueOf(i));product.setCname("小米su7");product.setShelf_id(randomInt+1l);product.setStatus("小米");productMapper.insert(product);}}
@Test
public void select(){QueryWrapper<Product> queryWrapper = new QueryWrapper();List<Product> products = productMapper.selectList(queryWrapper);System.out.println(products);
}

2.分库分表

配置对应实体类以及 Mapper

# sharding-jdbc 水平分库分表策略
# 配置数据源，给数据源起别名
# 水平分库需要配置多个数据库
spring.shardingsphere.datasource.names=m1,m2# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true# 配置第一个数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/product_db_1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&tinyInt1isBit=falsespring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456# 配置第二个数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/product_db_2?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&tinyInt1isBit=falsespring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456# 指定数据库分布的情况和数据表分布的情况
# m1 m2   product_1 product_2
spring.shardingsphere.sharding.tables.product.actual-data-nodes=m$->{1..2}.product$->{1..2}# 指定 course 表里面主键 id 的生成策略 SNOWFLAKE# 指定分库策略    约定 shelf_id 值偶数添加到 m1 库，如果 shelf_id 是奇数添加到 m2 库spring.shardingsphere.sharding.tables.product.database-strategy.inline.sharding-column=shelf_id
spring.shardingsphere.sharding.tables.product.database-strategy.inline.algorithm-expression=m$->{shelf_id % 2 + 1}# 指定分表策略    约定 id 值偶数添加到 product_1 表，如果 id 是奇数添加到 product_2 表
spring.shardingsphere.sharding.tables.product.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product.table-strategy.inline.algorithm-expression=product$->{id % 2 + 1}# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true

测试代码运行

@Test
public void addProduct() {for (int i = 1; i < 50; i++) {int randomInt = RandomUtil.randomInt(1, 100);Product product = new Product();product.setId(Long.valueOf(i));product.setCname("小米su7");product.setShelf_id(randomInt+1l);product.setStatus("小米");productMapper.insert(product);}}
@Test
public void select(){QueryWrapper<Product> queryWrapper = new QueryWrapper();List<Product> products = productMapper.selectList(queryWrapper);System.out.println(products);
}