MySQL思考-分库分表(Sharding-JDBC)

2022-08-27 14:15:40

一 分库分表基本概念

1.1 分库

将一个数据库，在节点上部署多台。因为当很多客户端连接客户端，并发量高，需要进行分库。

1.2 分表

将一个表，分成多个表。因为在MySQL中一个表数据达到5千万条，数据库性能严重下降。

1.3 分区

  1. 概述
分区技术是数据库内部技术，对于我们开发者来说，一张表分区后仍然是一张表，我们开发操作表名没有变化。仅仅是将表分成若干物理区。
  1. 为什么不使用分区
受单机因素限制(大量连接、高吞吐)，虽然每个区是独立的，但是分区表的总入口就一个。

二 分库分表的中间件

我们进行分库分表为什么引入中间件，因为我们之前操作单体数据库时候，我们使用jdbc连接给定的一个数据库和给定的一个表。但是
我们进行分库分表后，我们程序操作数据库由原来的一个变成两个或者多个，表变成两张或者多张。我们使用原先的jdbc技术没法相同
的库相同的表。所以引入类似别人写好的工具，我们业务代码和之前的一样，工具类对多个库多个表进行操作汇总。从而达到业务要求。

2.1 常用中间件

  1. 常用中间件分为两个类型
client 模式
proxy 模式
  1. sharding-jdbc(client)

MySQL思考-分库分表(Sharding-JDBC)
备注

引入sharding-jdbc的一个jar，就引入sharding-jdbc这个插件。就能代替jdbc操作数据库(多数据库多表)。
  1. mycat(proxy)
    MySQL思考-分库分表(Sharding-JDBC)

备注

因为mycat是单独部署的，单机模式安全性不高，因此需要多个机器部署。

四 分库分表需要注意问题&产生的问题剖析

4.1 分库分表原则

  1. 能不分库分表就不分库分表。因为分库分表导致业务逻辑复杂，代码书写复杂。
  2. 解除耦合性，需要将一些使用频率高的表和使用频率低的表分开。
  3. 数据量太大无法满足正常的业务访问，进行分表分库。

4.2 分库分表架构方案

  1. 垂直分库
数据库并发量上来了，数据量不是很大，进行垂直分库。分库后的数据库结构不一样数据不一样。

MySQL思考-分库分表(Sharding-JDBC)
2. 水平分库分表

并发量不大，数据量不大。但是表中字段多，一部分字段就经常被查询的，另外一部分不怎么被查询。所以需要将字段拆分。降低查询时候io时间。

MySQL思考-分库分表(Sharding-JDBC)
3. 水平分库

并发量大，数据量大，采用水平分库。将一个数据库，拆分若干个数据库，数据库的结构一样，数据不一样。

MySQL思考-分库分表(Sharding-JDBC)
4. 水平分表
MySQL思考-分库分表(Sharding-JDBC)

并发量不大，数据库中表的数据量大。采用水平分表。将一个表分成若干个表，表的数据结构一样，每个表数据不一样。

4.3 分库分表产生问题剖析

  1. 产生分布式事务问题
以前数据操作是在一个数据库操作，可以利用数据库事务解决。现在操作可能面临多个数据库，从而数据库的事务不能起作用。
采用分段式提交事务，最终一致性解决。
  1. 跨节点join问题
在一个数据库中进行多表关联查询，直接使用join查询。进行分库分表后，将多表分布到多个节点上就不能只用join关联查询了。
采用字段雍余方案，或者调用别的服务，结果归并。
  1. 主键重复问题
以前采用主键自增。在进行水平分表后，表中数据的主键重复，采用uuid或者雪花算法来生成。
  1. 多节点的排序分页问题
之前对某个表进行分页和排序，直接使用limit、order by执行可以完成。现在一个表中的数据分成多个表之后，需要将多个表数据取出后，
然后才能进行分页和排序。

例如分页：要取去1000-1010条。在所有的表都取出前1010条数据，然后将所有数据进行归并再分页。

五 使用Sharding-JDBC对分库分表进行处理

5.1 Sharding-JDBC概述

  1. 概述
Sharding-JDBC的使用，仅仅引入一个jar包就可以直接使用，可以理解为JDBC增强版。Sharding-JDBC还支持各种ORM框架和第三方的连接池。

使用Sharding-JDBC操作多个数据源，业务代码和之前jdbc操作单个数据库单表一样。不用关心数据源有几个在什么地方，交给Sharding-JDBC。
  1. 基本概念

逻辑表

水平将一个表分成若干表，对若干表提取出一个表名，供业务代码调用。这个提取的表名就是逻辑表。

实际表

分表后在硬盘中存在的表。

动态表

逻辑表和物理表不一定在配置中静态配置。比如按照日期进行分片场景，物理表名随着时间进行改变。

数据节点

库名.物理表名

绑定表

主表和从表使用相同的分片键进行分片。比如order_base(订单表)和order_detail(订单详情表)都是根据订单id进行分片。

分片键

根据表中的某个字段值，操作不同的表和库的数据。

分片算法

按照一种策略按照分片键将数据分到不同的表和库。支持大  >   <  >=   <=   between和in分片。

分片策略

分片键+分片算法

5.2 水平分表实现

  1. 导入sharding-jdbc依赖

  2. sharding-jdbc配置

导入依赖

<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>4.0.0-RC1</version>
</dependency>

配置数据源

spring.shardingsphere.datasoure.names=m1
spring.shardingsphere.datasoure.m1.type=com.alibaba.druid.pool.DruidDatasource
spring.shardingsphere.datasoure.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasoure.m1.url=jdbc:mysql://127.0.0.1:3306/order_db
spring.shardingsphere.datasoure.m1.usename=root
spring.shardingsphere.datasoure.m1.password=root

配置主键生成方式

spring.shardingsphere.sharding.tables.t_order.keyGenerator.column=order_id
spring.shardingsphere.sharding.tables.t_order.keyGenerator.type=SNOWFLAKE

配置数据节点(定义逻辑表和物理表)

spring.shardingsphere.sharding.tables.t_order.actualDataNodes=m1.t_order_$->{1..2}

配置分片策略(分片键、分片算法)

spring.shardingsphere.sharding.tables.t_order.tableStrategy.inline.shardingColumn=order_id
spring.shardingsphere.sharding.tables.t_order.tableStrategy.inline.algorithmExpression=t_order_${order_id % 2 + 1}
  1. 查看sql执行结果

插入多条数据执行sql
MySQL思考-分库分表(Sharding-JDBC)
查询数据sql
MySQL思考-分库分表(Sharding-JDBC)
4. 对sharding-jdbc拿到用户的sql做的事情

解析sql，获取分片值；
根据分片值按照分片算法插入到对应表中，如上方分片算法t_order_${order_id_% 2 + 1}，order_id为奇数时插到order_id_2表中，当order_id为偶数时候插到order_id_1表中。
改变分片值改变sql,然后执行改变后的sql。
对执行的结果进行归并。

5.3 水平分库

  1. 配置文件
// 定义数据源名称
spring.shardingsphere.datasource.names=m1,m2
// 定义数具体据源
spring.shardingsphere.datasource.m1.type=com.alibaba.pool.DruidDatasorce
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/order_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/order_db_2?useUnicode=true
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
//配置数据节点
spring.shardingsphere.sharding.tables.t_order.actualDataNodes=m$->{1..2}.t_order$->{1...2}
// 定义主键生成策略
spring.shardingsphere.sharding.tables.t_order.keyGenerator.column=order_id
spring.shardingsphere.sharding.tables.t_order.keyGenerator.type=SNOWFLAKE
//定义分片策略(分片键+分片算法)
//分表
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=order_id
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algorithmExpression=t_order_$->{order_id % 2 + 1}
//分库
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=user_id
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algorithmExpression=m$->{user_id % 2 + 1}
  1. 查看执行结果
    MySQL思考-分库分表(Sharding-JDBC)
    MySQL思考-分库分表(Sharding-JDBC)

5.4 垂直分库实现

  1. 概述
一般是按照业务将一个库分成若干库，每个库的表结构和数据不一样。

在工作中可以用sharding-jdbc操作多数据源(不一样数据库)。但很多用分布式处理。
  1. 配置文件
//配置数据库名称
spring.shardingsphere.datasource.names=m1,s1
//配置数据源
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/order_db
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.url=jdbc:mysql://127.0.0.1:3306/user_db
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=root
//配置数据节点
spring.shardingsphere.sharding.tables.t_user.actualDataNodes=s1.t_user
spring.shardingsphere.sharding.tables.t_order.actualDataNodes=m1.t_order
//配置主键生成方式
spring.shardingsphere.sharding.tables.t_user.keyGenerator.column=user_id
spring.shardingsphere.sharding.tables.t_user.keyGenerator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.keyGenerator.column=order_id
spring.shardingsphere.sharding.tables.t_order.keyGenerator.type=SNOWFLAKE
//配置分片策略
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=order_id
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algorithmExpression=t_order
spring.shardingsphere.sharding.tables.t_user.databaseStrategy.inline.shardingColumn=user_id
spring.shardingsphere.sharding.tables.t_user.databaseStrategy.inline.algorithmExpression=t_user
  1. 查看执行结果

5.5 读写分离

  1. 什么是读写分离
程序写入数据是一个数据库，读取数据是另外一个相同是数据库。利用mysql的数据同步功能，将写库的数据同步到读数据库。

MySQL思考-分库分表(Sharding-JDBC)
2. 读写分离的好处

有大量数据写入数据库，会导致行锁，导致查询性能降低。所以将读写分离能提高数据库的性能。
  1. 解决方案
    MySQL思考-分库分表(Sharding-JDBC)

备注

实现读写分离还是挺简单的，分两步。第一步，实现数据库数据同步-更改MySQL的配置文件实现。
第二步，实现多数据源的调用，按照前面分库分表的sharding-jdbc的配置。
  1. 设置数据同步
    //更改主库的配置文件 
#开启日志
log-bin=mysql-bin
#设置服务id,主从服务不能一致
server-id=1
#设置需要同步的数据库
binlog-do-db=user_db
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

//更改从库的配置文件

#开启日志
log-bin=mysql-bin
#设置服务id，主从不能一致
server-id=2
#设置需要同步的数据库
replicate_wild_do_table=user_db.%
#屏蔽系统库同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
#完成配置，主从 MySQL下的数据(data)目录下有个文件auto.cnf。载要重启服务。

//在主库中执行命令

#授权主备复制专用账号
GRANT replication SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED by 'db_sync';
#刷新权限
FLUSH PRIVILEGES;
#确认位点 记录下文件名以及位点
SHOW MASTER STATUS;

MySQL思考-分库分表(Sharding-JDBC)

//从库执行命令

#先停止同步
STOP SLAVE;
#修改从库指向主库，使用上一步记录的文件名以及位点
CHANGE MASTER TO
	master_host='localhost',
	master_user='db_sync',
	master_password='db_sync',
	master_log_file='mysql-bin.000001',
	master_log_pos=592;
#启动同步
START SLAVE;
#查看从库状态Slave_IO_Running和Slave_SQL_Runing都为yes说明同步成功，如不为yes,请检查error_Log，然后排查相关异常
SHOW SLAVE STATUS
  1. 主从复制实现配置文件
// 定义数据源
spring.shardingsphere.datasource.names=m0,m1,m2,s0
// 数据源s0
spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name =com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://localhost:3307/user_db?useUnicode=true
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=root
// 数据源0
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name =com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?useUnicode=true
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
// 数据源1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name =com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://118.31.18.203:3306/order_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
// 数据源2
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name =com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://118.31.18.203:3306/order_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
// 配置数据节点
spring.shardingsphere.sharding.tables.t_order.DataNodes=m$->{1..2}.t_order_${1..2}

spring.shardingsphere.sharding.master-slave-rules.ds0.masterDataSourceName=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slaveDataSourceNames=s0
spring.shardingsphere.sharding.tables.t_user.actualDataNodes=ds0.t_user
//配置主键生成策略
spring.shardingsphere.sharding.tables.t_order.keyGenerator.column=order_id
spring.shardingsphere.sharding.tables.t_order.keyGenerator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.t_user.keyGenerator.column=user_id
spring.shardingsphere.sharding.tables.t_user.keyGenerator.type=SNOWFLAKE
//配置分片策略
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=order_id
spring.shardingsphere.sharding.tables.t_user.databaseStrategy.inline.algorithmExpression=t_order_$->{order_id%2+1}
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=user_id
spring.shardingsphere.sharding.tables.t_user.databaseStrategy.inline.algorithmExpression=m$->{user_id%2+1}

spring.shardingsphere.sharding.tables.t_user.tableStrategy.inline.shardingColumn=user_id
spring.shardingsphere.sharding.tables.t_user.tableStrategy.inline.algorithmExpression=t_user

5.6 水平分库分表加主从复制实战

  1. 准备资源
    MySQL思考-分库分表(Sharding-JDBC) 
在一个数据库，有两个水平分库order_db_1和order_db_2。以及复制数据库。
  1. 配置文件书写
#配置数据源
spring.shardingsphere.datasource.names=m1,m2,s1,s2

#配置真实数据源
spring.shardingsphere.datasource.m1.type=com.alibaba.pool.DruidDataSource
spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/order_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jbdc.Driver
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

spring.shardingsphere.datasource.s1.type=com.alibaba.pool.DruidDataSource
spring.shardingsphere.datasource.s1.url=jdbc:mysql://127.0.0.1:3307/order_db_1?useUnicode=true
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.jbdc.Driver
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=root

spring.shardingsphere.datasource.m2.type=com.alibaba.pool.DruidDataSource
spring.shardingsphere.datasource.m2.url=jdbc:mysql://127.0.0.1:3306/order_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.jbdc.Driver
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root

spring.shardingsphere.datasource.s2.type=com.alibaba.pool.DruidDataSource
spring.shardingsphere.datasource.s2.url=jdbc:mysql://127.0.0.1:3307/order_db_2?useUnicode=true
spring.shardingsphere.datasource.s2.driver-class-name=com.mysql.jbdc.Driver
spring.shardingsphere.datasource.s2.username=root
spring.shardingsphere.datasource.s2.password=root

#主从绑定
spring.shardingsphere.sharding.master-slave-rules.ds1.masterDataSourceName=m1
spring.shardingsphere.sharding.master-slave-rules.ds1.slaveDataSourceName=s1

spring.shardingsphere.sharding.master-slave-rules.ds2.masterDataSourceName=m2
spring.shardingsphere.sharding.master-slave-rules.ds2.slaveDataSourceName=s2

#设置数据节点(数据库中表几个，配置几个数据节点)
spring.shardingsphere.sharding.tables.t_order.actualDataNodes=ds$->{1..2}.t_order_$->{1..2}

# 主键生成
spring.shardingsphere.sharding.tables.t_order.keyGenerator.column=order_id
spring.shardingsphere.sharding.tables.t_order.keyGenerator.type=SNOWFLAKE

#设置分片策略
//分表
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=order_id
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algorithmExpression=t_order_$->{order_id % 2 + 1}
//分库
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=user_id
spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algorithmExpression=ds$->{user_id % 2 + 1}
上一篇：SpringBoot2.0.3.RELEASE+sharding-jdbc4.1.0+mybatis-plus3.4.1+druid1.1.22 快速搭建分库分表


下一篇：springcloud2.x整合mybatis3+shardingsphere4.1.0&4.1.1 主从分离+服务治理