1 介绍
Sharding-JDBC定位为轻量级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标准的数据库。
2 安装MySQL
- 准备至少两台机器安装MySQL
# 配置扩展源
rpm -ivh http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
# yum安装MySQL
yum -y install mysql-community-server
# 若报错 mysql-community-client-xxxxxx.rpm 的公钥尚未安装，则执行
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
# 开机启动MySQL
systemctl enable mysqld
# 启动MySQL
systemctl start mysqld
- 修改初始密码
# 查看初始密码
grep "password" /var/log/mysqld.log
# 使用初始密码登录
mysql -uroot -p
# 修改密码级别
mysql> set global validate_password_policy=0;
# 修改密码最小长度
mysql> set global validate_password_length=1;
# 修改root用户密码
mysql> alter user `root`@`localhost` identified by '123456';
# 赋权让root用户可以在其他机器访问
mysql> grant all on *.* to `root`@`%` identified by '123456';
mysql> flush privileges;
3 读写分离
3.1 相关概念
读写分离的大致实现效果图如下所示：
MySQL主从同步的原理：
Master数据库把所有增删改的命令写入二进制日志 Binary log 中，通过TCP连接把这些日志信息传送到Slave库的IO线程。Slave库的IO线程再把这些内容写入中继日志 Relay log 中，最后Slave库的SQL线程读取中继日志的内容进行重现，以此达到与Master库同步。
3.2 MySQL主从同步
- Master节点配置
/etc/my.cnf
vim /etc/my.cnf
[mysqld]
# 唯一ID
server-id=101
# 开启二进制日志功能，后面的是名字，可任意取
log-bin=mysql-master-bin
# 过滤不需要复制的库
binlog-ignore-db=mysql,sys
# 存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式
binlog_format=mixed
- Slave节点配置
/etc/my.cnf
vim /etc/my.cnf
[mysqld]
# 唯一ID
server-id=102
# 中继日志
relay_log=mysql-relay-bin
# 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true
# 开启二进制日志功能
log-bin=mysql-slave-bin
# 过滤不需要复制的库
binlog-ignore-db=mysql,sys
# 存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式
binlog_format=mixed
# 跳过主从复制过程中的错误类型，1062是主键重复
slave_skip_errors=1062
- 在Master节点赋予Slave可以同步的权限。在Master节点执行
# 先登录主节点MySQL
mysql -uroot -p
# 授权
mysql> grant replication slave, replication client on *.* to `root`@`Slave节点的IP` identified by 'Slave节点的密码';
mysql> flush privileges;
- 查看Master节点复制的位置
mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-master-bin.000001 | 771 | | mysql,sys | |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
- 在Slave节点执行
mysql> change master to master_host='Master节点的IP', master_user='root', master_password='123456', master_log_file='mysql-master-bin.000001', master_log_pos=771;
mysql> start slave;
# 查看主从复制的状态，下列两项为Yes才表示正常
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
mysql> show slave status\G;
- 在Master进行创建库、表等操作后，Slave也能同步
3.3 功能实现
- 在Master节点创建表
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
`username` varchar(20) DEFAULT NULL COMMENT '用户名',
`password` varchar(20) DEFAULT NULL COMMENT '密码',
`name` varchar(10) DEFAULT NULL COMMENT '姓名',
`birthday` timestamp NULL DEFAULT NULL COMMENT '出生日期',
`sex` int(11) DEFAULT NULL COMMENT '性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';
- 添加依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.6.5</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.datatype</groupId>
<artifactId>jackson-datatype-jsr310</artifactId>
<version>2.10.0</version>
</dependency>
</dependencies>
- 添加配置文件 application.yml
server:
port: 8088
spring:
shardingsphere:
props:
sql:
show: true
# 配置数据源
datasource:
# 名字任意取
names: ds00,ds01
ds00:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.168.6.233:3306/testdb?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8
username: root
password: 123456
maxPoolSize: 100
minPoolSize: 5
ds01:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.168.6.69:3306/testdb?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8
username: root
password: 123456
maxPoolSize: 100
minPoolSize: 5
sharding:
# 默认数据源，即主库，主要用于写操作。
# 若不配置，就会把所有节点都当作slave节点，此时执行写操作会报错
default-data-source-name: ds00
# 下面的内容可以不配置，主要是为了重配负载均衡策略
masterslave:
# 主从名称，随意取
name: ms
master-data-source-name: ds00
slave-data-source-names: ds01
# slave节点的负载均衡策略：轮询（默认也是轮询）
load-balance-algorithm-type: round_robin
mybatis:
mapper-locations: classpath:/mapper/*Mapper.xml
type-aliases-package: pers.ljc.learn.shardingjdbc.model
configuration:
map-underscore-to-camel-case: true
- 编写相应的Controlelr、Mapper等
@RestController
@RequestMapping("/user")
public class UserController {
private final UserMapper userMapper;
public UserController(UserMapper userMapper) {
this.userMapper = userMapper;
}
@PostMapping("/add")
public String addUser(@RequestBody User user) {
long userId = IdUtil.getSnowflake(1, 1).nextId();
user.setId(null);
user.setUserId(userId);
int result = userMapper.insertUser(user);
return result > 0 ? "success" : "fail";
}
@GetMapping("/list")
public List<User> listUser() {
return userMapper.listUser();
}
}
- 执行写操作和读操作，有以下日志信息
# 写操作时：实际都发生在ds01数据源
Actual SQL: ds00 ::: insert into t_user...
# 读操作时：实际都发生在slave数据源，这里只配置了一个
Actual SQL: ds01 ::: select...
4 分库分表
4.1 相关概念
- 垂直拆分：把一个表的字段拆到不同库的不同表中。每个表的都包含原来的一部分字段，一般根据业务维度拆分或者根据冷热程度拆分。拆分后每个表的结构保持不同
- 水平拆分：把一个表的数据拆到不同库的不同表中。可以根据时间、地区、hash值等信息拆分。拆分后每个表的结构保持一致
- 逻辑表：水平拆分之前的表明。如拆分之前是t_user表，拆分后是t_user0、t_user1、t_user2等等，那么逻辑表就是t_user
4.2 功能实现
- 在Slave节点执行以下操作，关闭之前开启的MySQL主从同步
mysql> stop slave;
QueryOK, 0 rowsaffected (0,00 sec)
mysql> reset slave all;
QueryOK, 0 rowsaffected (0,04 sec)
mysql> show slave status\G
Emptyset (0,00 sec)
- 分别在Master节点和Slave节点创建两张表（t_user0、t_user1），此时共有四张物理表
- 配置application.yml
server:
port: 8088
spring:
shardingsphere:
props:
sql:
show: true
# 配置数据源
datasource:
# 名字任意取
names: ds00,ds01
ds00:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.168.6.233:3306/testdb?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8
username: root
password: 123456
maxPoolSize: 100
minPoolSize: 5
ds01:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.168.6.69:3306/testdb?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8
username: root
password: 123456
maxPoolSize: 100
minPoolSize: 5
sharding:
default-data-source-name: ds00
tables:
# 逻辑表名
t_user:
# 配置数据源和表
# 如下表示 有两个源（ds00、ds01），每个源都有两个物理表（t_user0、t_user1），所以实际共4张表
actual-data-nodes: ds0$->{0..1}.t_user$->{0..1}
# 分库策略
database-strategy:
inline:
# 分片键
sharding-column: user_id
# 逻辑：即根据user_id模2的结果进行分库
algorithm-expression: ds0$->{user_id%2}
# 分表策略
tableStrategy:
inline:
sharding-column: sex
# 逻辑：即根据sex模2的结果进行分表
algorithm-expression: t_user$->{sex%2}
mybatis:
mapper-locations: classpath:/mapper/*Mapper.xml
type-aliases-package: pers.ljc.learn.shardingjdbc.model
configuration:
map-underscore-to-camel-case: true
# 指定自定义的typeHandler所在包，避免类型转换错误
type-handlers-package: pers.ljc.learn.shardingjdbc.config
- 效果如下：
执行插入操作时
# 当生成的user_id是偶数时，会入库到ds00；此时sex是1时，插入到表t_user1中
Actual SQL: ds00 ::: insert into t_user1...
# 当生成的user_id是偶数时，会入库到ds00；此时sex是0时，插入到表t_user0中
Actual SQL: ds00 ::: insert into t_user0...
执行查询操作时，实际会查询四个物理表
2022-10-08 14:37:07.495 INFO 27176 --- [nio-8088-exec-5] ShardingSphere-SQL : Actual SQL: ds00 ::: select
id, user_id, username, password, name, birthday, sex
from t_user0
2022-10-08 14:37:07.495 INFO 27176 --- [nio-8088-exec-5] ShardingSphere-SQL : Actual SQL: ds00 ::: select
id, user_id, username, password, name, birthday, sex
from t_user1
2022-10-08 14:37:07.495 INFO 27176 --- [nio-8088-exec-5] ShardingSphere-SQL : Actual SQL: ds01 ::: select
id, user_id, username, password, name, birthday, sex
from t_user0
2022-10-08 14:37:07.495 INFO 27176 --- [nio-8088-exec-5] ShardingSphere-SQL : Actual SQL: ds01 ::: select
id, user_id, username, password, name, birthday, sex
from t_user1
4.3 标准分片
在4.2章节中，使用的是Inline分片，可以通过某种运算逻辑来达到分库分表的效果。但有时根据业务需求需要实现更加灵活的分片策略，下面介绍另一种分片策略：标准分片。
比如需求为：2000年之前出生的用户存在第一个库中，2000年之后出生的存在第二个库中
- 编写分库逻辑类
package pers.ljc.learn.shardingjdbc.algorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
/**
* 分库逻辑类
* 由于配置了TypeHandler，在落库之前把LocalDateTime转成了String类型，所以这里实现接口的泛型是String
*/
public class BirthdayAlgorithm implements PreciseShardingAlgorithm<String> {
private static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
private static final List<LocalDateTime> LIST = Arrays.asList(
LocalDateTime.of(2000, 1, 1, 0, 0, 0),
LocalDateTime.of(9999, 1, 1, 0, 0, 0)
);
/**
* 分库逻辑
*
* @param collection 数据源集合，本案例的ds00和ds01
* @param preciseShardingValue 字段值
* @return 返回对应数据源
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
String stringValue = preciseShardingValue.getValue();
LocalDateTime value = LocalDateTime.parse(stringValue, FORMATTER);
String ret = null;
int i = 0;
// 实现效果：2000年之前出生的存在第一个库中，之后出生的存在第二个库中
for (String dataSource : collection) {
ret = dataSource;
if (value.isBefore(LIST.get(i))) {
break;
} else {
i++;
}
}
return ret;
}
}
- 修改application.yml的分库配置
spring:
shardingsphere:
sharding:
default-data-source-name: ds00
tables:
# 逻辑表名
t_user:
# 配置数据源和表
# 如下表示 有两个源（ds00、ds01），每个源都有两个物理表（t_user0、t_user1），所以实际共4张表
actual-data-nodes: ds0$->{0..1}.t_user$->{0..1}
# 分库策略
database-strategy:
# 标准分片
standard:
# 分片键
sharding-column: birthday
# 按照指定类的逻辑进行分库
precise-algorithm-class-name: pers.ljc.learn.shardingjdbc.algorithm.BirthdayAlgorithm
# 分表策略
tableStrategy:
inline:
sharding-column: sex
algorithm-expression: t_user$->{sex%2}
