一、单库分表
pom.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.payne</groupId>
<artifactId>sprintboot-sharding</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<java.version>1.8</java.version>
<mybatis.version>2.1.0</mybatis.version>
<sharding.jdbc.version>3.0.0</sharding.jdbc.version>
<druid.version>1.1.10</druid.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding.jdbc.version}</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
添加实体类
@Getter
@Setter
@ToString
public class User {
private Long id;
private String name;
private Long cityId;
private String sex;
}
创建Mapper
public interface UserMapper {
/**
* 保存
*/
void save(User user);
/**
* 查询
* @param id
* @return
*/
User get(Long id);
}
<?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.payne.user.mapper.UserMapper">
<insert id="save" parameterType="com.payne.user.model.User">
INSERT INTO t_user(name,city_id,sex)
VALUES
(
#{name},#{cityId},#{sex}
)
</insert>
<select id="get" parameterType="long" resultType="com.payne.user.model.User">
select * from t_user where id = #{id}
</select>
</mapper>
Controller
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserMapper userMapper;
@GetMapping("/save")
public String save() {
for (int i = 0; i <10 ; i++) {
User user=new User();
user.setName("test"+i);
user.setSex(i%2==0?"M":"F");
userMapper.save(user);
}
return "success";
}
@GetMapping("/get/{id}")
public User get(@PathVariable Long id) {
User user = userMapper.get(id);
System.out.println(user.getId());
return user;
}
}
application.properties
spring.application.name=sharding-springboot-mybatis
# mybatis
mybatis.mapper-locations=classpath*:mapper/**/*.xml
# 数据源 db0,db1,db2
sharding.jdbc.datasource.names=db0
# 数据源ds0
sharding.jdbc.datasource.db0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.db0.jdbc-url=jdbc:mysql://localhost:3306/db0?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
sharding.jdbc.datasource.db0.username=root
sharding.jdbc.datasource.db0.password=1234
sharding.jdbc.config.sharding.default-data-source-name=db0
sharding.jdbc.config.sharding.binding-tables=t_user
#数据节点,均匀分布
sharding.jdbc.config.sharding.tables.t_user.actualDataNodes=db0.t_user${0..1}
#指定主键名称,sharding-jdbc默认生成主键策略为雪花算法(SnowFlake)
sharding.jdbc.config.sharding.tables.t_user.keyGeneratorColumnName=id
#分表策略 行表达式
sharding.jdbc.config.sharding.tables.t_user.tableStrategy.inline.shardingColumn=sex
#分表策略 按条件运算 性别等于M的存入t_user0表,其它存入t_user1表
sharding.jdbc.config.sharding.tables.t_user.tableStrategy.inline.algorithmExpression=t_user$->{sex.equalsIgnoreCase("M")?0:1}
# 打印执行的数据库以及语句
sharding.jdbc.config.sharding.props.sql.show=true
初始化sql
CREATE TABLE `t_user0` (
`id` bigint(20) NOT NULL,
`name` varchar(64) DEFAULT NULL COMMENT '名称',
`sex` varchar(2) DEFAULT NULL COMMENT '性别',
`city_id` int(12) DEFAULT NULL COMMENT '城市',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `t_user1` (
`id` bigint(20) NOT NULL,
`name` varchar(64) DEFAULT NULL COMMENT '名称',
`sex` varchar(2) DEFAULT NULL COMMENT '性别',
`city_id` int(12) DEFAULT NULL COMMENT '城市',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
分析执行结果
分片策略:sharding.jdbc.config.sharding.tables.t_user.tableStrategy.inline.algorithmExpression=t_user$->{sex.equalsIgnoreCase(“M”)?0:1}
http://localhost:8080/user/save
一个简单的水平分片单库分表就完成了。进行测试就发现数据分别存储到t_user0和t_user1两个表中。这里采用的事按照字段sex类型分片存储。
二、分库分表
application.properties
# 第二个数据库
sharding.jdbc.datasource.db1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
sharding.jdbc.datasource.db1.username=root
sharding.jdbc.datasource.db1.password=1234
# 水平拆分的数据库(表) 配置分库 + 分表策略 行表达式分片策略
# 分库策略
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=db$->{id % 2}
#数据节点,均匀分布
sharding.jdbc.config.sharding.tables.t_user.actualDataNodes=db$->{0..1}.t_user${0..1}
@GetMapping("/save")
public String save() {
for (int i = 0; i <10 ; i++) {
User user=new User();
#设置id
user.setId(Long.valueOf(i));
user.setName("test"+i);
user.setSex(i%2==0?"M":"F");
userMapper.save(user);
}
return "success";
}
分析结果
根据sharding-jdbc日志,可以看出sharding-jdbc的执行步骤:
SQL解析 ⇒ sql改写 ⇒ sql路由 ⇒ sql执行 ⇒ 结果合并
集成PageHelper进行分页操作
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.12</version>
</dependency>
@GetMapping("/all")
public List<User> selectAll() {
PageHelper.startPage(2, 5);
List<User> list = userMapper.selectAll();
return list;
}
Sharding-jdbc 完全支持Pagehelp分页操作