Sharding-Sphere官网:Apache ShardingSphere
Sharding-Sphere官方文档:Overview :: ShardingSphere
Sharding-Sphere中文文档:概览 :: ShardingSphere
Sharding-Sphere中文文档2:概览 :: ShardingSphere
1. 核心概念
- 主库:添加、更新以及删除数据操作所使用的数据库,目前仅支持单主库。
- 从库:查询数据操作所使用的数据库,可支持多从库。
- 主从同步:将主库的数据异步的同步到从库的操作。由于主从同步的异步性,从库与主库的数据会短时间内不一致。
- 负载均衡策略:通过负载均衡策略将查询请求疏导至不同从库。
2. 核心功能
- 提供一主多从的读写分离配置,可独立使用,也可配合分库分表使用。
- 独立使用读写分离支持SQL透传。
- 同一线程且同一数据库连接内,如有写入操作,以后的读操作均从主库读取,用于保证数据一致性。
- 基于Hint的强制主库路由。
3. 不支持项
- 主库和从库的数据同步。
- 主库和从库的数据同步延迟导致的数据不一致。
- 主库双写或多写。
- 跨主库和从库之间的事务的数据不一致。主从模型中,事务中读写均用主库。
4. 配置步骤
1. 添加依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
2. 编写配置文件
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
names: master,slave1
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.88.131:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false
username: root
password: root
slave1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.88.132:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false
username: root
password: root
sharding:
master-slave-rules:
master:
name: # 读写分离数据源名称
master-data-source-name: master # 主库数据源名称
slave-data-source-names: # 从库数据源名称列表
- slave1
loadBalanceAlgorithmType: ROUND_ROBIN # 从库负载均衡算法类型,可选值:ROUND_ROBIN,RANDOM。若`loadBalanceAlgorithmClassName`存在则忽略该配置
props: # 读写分离负载算法的属性配置
props: # 属性配置
sql:
show: true #是否开启SQL显示,默认值: false
# executor.size: #工作线程数量,默认值: CPU核数
# max.connections.size.per.query: # 每个查询可以打开的最大连接数量,默认为1
# check.table.metadata.enabled: #是否在启动时检查分表元数据一致性,默认值: false
5. 解决jdk8新时间类与Sharding-Sphere兼容问题
以 LocalDate 和 LocalDateTime 为例:
1. 引入依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.17</version>
</dependency>
2. 编写 BaseTypeHandler 的实现类:
- LocalDate适配:
package com.zjp.shadingjdbcdemo.handler;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.lang.TypeReference;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.springframework.stereotype.Component;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
@Component
@MappedTypes(LocalDate.class)
@MappedJdbcTypes(value = JdbcType.DATE, includeNullJdbcType = true)
public class LocalDateTypeHandler extends BaseTypeHandler<LocalDate> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, LocalDate parameter, JdbcType jdbcType)
throws SQLException {
ps.setObject(i, parameter);
}
@Override
public LocalDate getNullableResult(ResultSet rs, String columnName) throws SQLException {
return Convert.convert(new TypeReference<LocalDate>() {
@Override
public String getTypeName() {
return super.getTypeName();
}
},rs.getObject(columnName));
}
@Override
public LocalDate getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return Convert.convert(new TypeReference<LocalDate>() {
@Override
public String getTypeName() {
return super.getTypeName();
}
},rs.getObject(columnIndex));
}
@Override
public LocalDate getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return Convert.convert(new TypeReference<LocalDate>() {
@Override
public String getTypeName() {
return super.getTypeName();
}
},cs.getObject(columnIndex));
}
}
- LocalDateTime适配:
package com.zjp.shadingjdbcdemo.handler;
import cn.hutool.core.convert.Convert;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.springframework.stereotype.Component;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
@Component
@MappedTypes(LocalDateTime.class)
@MappedJdbcTypes(value = JdbcType.DATE, includeNullJdbcType = true)
public class LocalDateTimeTypeHandler extends BaseTypeHandler<LocalDateTime> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, LocalDateTime parameter, JdbcType jdbcType)
throws SQLException {
ps.setObject(i, parameter);
}
@Override
public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
return Convert.toLocalDateTime(rs.getObject(columnName));
}
@Override
public LocalDateTime getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return Convert.toLocalDateTime(rs.getObject(columnIndex));
}
@Override
public LocalDateTime getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return Convert.toLocalDateTime(cs.getObject(columnIndex));
}
}
其他时间类可仿照上述方案仿写。
6. 测试
1. 创建数据表
CREATE DATABASE IF NOT EXISTS test;
USE test;
-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`name` varchar(255) CHARACTER SET utf8mb4 NULL DEFAULT NULL COMMENT '用户名',
`age` int(11) NULL DEFAULT NULL COMMENT '用户年龄',
`salary` double NULL DEFAULT NULL COMMENT '用户薪资',
`birthday` datetime NULL DEFAULT NULL COMMENT '用户生日',
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
2. 创建实体类
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.time.LocalDateTime;
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("t_user")
public class User implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 用户编号
*/
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
* 用户名
*/
private String name;
/**
* 用户年龄
*/
private Integer age;
/**
* 用户薪资
*/
private Double salary;
/**
* 用户生日
*/
private LocalDateTime birthday;
}
3. 编写sevice层和mapper层
import com.zjp.shardingjdbcdemo.entity.User;
import com.baomidou.mybatisplus.extension.service.IService;
public interface IUserService extends IService<User> {
}
import com.zjp.shardingjdbcdemo.entity.User;
import com.zjp.shardingjdbcdemo.mapper.UserMapper;
import com.zjp.shardingjdbcdemo.service.IUserService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}
import com.zjp.shardingjdbcdemo.entity.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
public interface UserMapper extends BaseMapper<User> {
}
4. 测试接口
import com.github.javafaker.Faker;
import com.zjp.shardingjdbcdemo.entity.User;
import com.zjp.shardingjdbcdemo.service.IUserService;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@Slf4j
@SpringBootTest
public class ShardingJdbcDemoApplicationTests {
@Autowired
private IUserService userService;
@Test
public void testClusterSave() {
Date birthday = FAKER.date().birthday(18, 100);
User user = new User()
.setName(FAKER.name().fullName())
.setAge(Year.now().getValue() - birthday.getYear() - 1900)
.setSalary((RANDOM.nextInt(500000) + 500000) / 100.0)
.setBirthday(birthday.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime());
userService.save(user);
}
@Test
public void testClusterGet() {
List<User> list = userService.list();
log.info("查询结果为:{}", list);
}
}
如下图所示,写操作在master库:
如下图所示,写操作在slave库:
7. 基于暗示(Hint)的强制主库路由
7.1. 配置步骤
1. 编写配置文件(同读写分离配置文件)
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
names: master,slave1
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.88.131:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false
username: root
password: root
slave1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.88.132:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false
username: root
password: root
sharding:
master-slave-rules:
master:
name: # 读写分离数据源名称
master-data-source-name: master # 主库数据源名称
slave-data-source-names: # 从库数据源名称列表
- slave1
loadBalanceAlgorithmType: ROUND_ROBIN # 从库负载均衡算法类型,可选值:ROUND_ROBIN,RANDOM。若`loadBalanceAlgorithmClassName`存在则忽略该配置
props: # 读写分离负载算法的属性配置
props: # 属性配置
sql:
show: true #是否开启SQL显示,默认值: false
# executor.size: #工作线程数量,默认值: CPU核数
# max.connections.size.per.query: # 每个查询可以打开的最大连接数量,默认为1
# check.table.metadata.enabled: #是否在启动时检查分表元数据一致性,默认值: false
2. 获取HintManager:与基于暗示(Hint)的数据分片相同。
3. 设置主库路由:使用hintManager.setMasterRouteOnly设置主库路由。
4. 清除分片键值:与基于暗示(Hint)的数据分片相同。
7.2. 代码示例
import com.github.javafaker.Faker;
import com.zjp.shardingjdbcdemo.entity.User;
import com.zjp.shardingjdbcdemo.service.IUserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.hint.HintManager;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@Slf4j
@SpringBootTest
public class ShardingJdbcDemoApplicationTests {
@Autowired
private IUserService userService;
@Test
public void testClusterHint() {
try (HintManager hintManager = HintManager.getInstance();) {
hintManager.setMasterRouteOnly();
List<User> list = userService.list();
log.info("查询结果为:{}", list);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
如下图所示,读操作在master库执行:
8. 自定义负载均衡算法
8.1. 源码参考
Ctrl + 左键点击 loadBalanceAlgorithmType,点击定位文件位置,如下图所示,这三个文件即为主键生成策略的配置文件。org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator文件指定了两种生成策略,分别为UUID和雪花算法。
8.2. 自定义主键生成策略步骤
1. 编写配置文件
spring:
shardingsphere:
sharding:
master-slave-rules:
master:
name: # 读写分离数据源名称
master-data-source-name: master # 主库数据源名称
slave-data-source-names: # 从库数据源名称列表
- slave1
loadBalanceAlgorithmType: MY_LOAD_BALANCE # 从库负载均衡算法类型,可选值:ROUND_ROBIN,RANDOM。若`loadBalanceAlgorithmClassName`存在则忽略该配置
props: # 读写分离负载算法的属性配置
weight.formula: "weight_random()"
2. 编写负载均衡算法
package com.zjp.shardingjdbcdemo.strategy.masterslave;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.spi.masterslave.MasterSlaveLoadBalanceAlgorithm;
import java.util.List;
import java.util.Properties;
import java.util.concurrent.atomic.AtomicInteger;
@Slf4j
@Getter
@Setter
public class MyMasterSlaveLoadBalanceAlgorithm implements MasterSlaveLoadBalanceAlgorithm {
private Properties properties = new Properties();
private static final AtomicInteger CURRENT_INDEX = new AtomicInteger(0);
@Override
public String getDataSource(String s, String s1, List<String> list) {
log.info("自定义负载均衡算法");
log.info("s:{},s1:{},list:{},properties:{}", s, s1, list, properties); // s: master,s1: master, list:[slave1, slave2],properties: prop里的参数
if (list == null || list.isEmpty()) {
log.warn("数据源列表为空");
return null;
}
// 获取当前索引的数据源
int index = CURRENT_INDEX.getAndIncrement() % list.size();
if (index < 0) {
index += list.size();
}
return list.get(index);
}
@Override
public String getType() {
return "MY_LOAD_BALANCE";
}
}
在src/main/resources/META-INF/services/org.apache.shardingsphere.spi.masterslave.MasterSlaveLoadBalanceAlgorithm 目录下编写自定义算法路径
com.zjp.shardingjdbcdemo.strategy.masterslave.MyMasterSlaveLoadBalanceAlgorithm
测试读操作:
import com.github.javafaker.Faker;
import com.zjp.shardingjdbcdemo.entity.User;
import com.zjp.shardingjdbcdemo.service.IUserService;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@Slf4j
@SpringBootTest
public class ShardingJdbcDemoApplicationTests {
@Autowired
private IUserService userService;
@Test
public void testClusterGet() {
List<User> list = userService.list();
log.info("查询结果为:{}", list);
}
}
从日志可以看出自定义负载均衡算法配置成功:
本篇文章示例代码:GitHub - kerrsixy/sharding-jdbc-demo