1 概述
之前笔者写过两篇文章:
- ShardingSphere 读写拆散
- ShardingSphere 分库分表
这里将两者联合起来,实现读写拆散 + 分库分表的性能。对于环境的配置本文将进行简化叙述,具体能够参考前两篇文章。
2 环境
MySQL 8.0.25
(Docker
)MyBatis Plus 3.4.3.1
MyBatis Plus Generator 3.5.0
Druid 1.2.6
ShardingSphere 4.1.1
Yitter 1.0.6
(一个雪花id
生成器)
3 数据库环境筹备
因为环境筹备不是本文的重点,一主一从的主从复制环境能够参考此处搭建。
筹备好环境,本地启动两个MySQL
,主节点环境:
- 名字:
master
- 端口:
3306
- 数据库:两个库(
test0
、test1
) - 数据表:六个表,每个库三个(
test0.user0
、test0.user1
、test0.user2
、test1.user0
、test1.user1
、test1.user2
)
从节点环境:
- 名字:
slave
- 端口:
3307
- 数据库:两个库(
test0
、test1
) - 数据表:六个表,每个库三个(
test0.user0
、test0.user1
、test0.user2
、test1.user0
、test1.user1
、test1.user2
)
主库配置文件:
[mysqld]
server-id=1
binlog-do-db=test0
binlog-do-db=test1
从库配置文件:
[mysqld]
server-id=2
replicate-do-db=test0
replicate-do-db=test1
残缺的数据库脚本和 MySQL
配置文件放在文末的源码链接中。
4 新建我的项目
新建我的项目并引入如下依赖:
Druid
MyBatis Plus starter
MyBaits Plus Generator
Velocity core
ShardingSphere
Yitter
Maven
如下:
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.0</version>
</dependency>
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
<version>2.3</version>
</dependency>
<dependency>
<groupId>org.realityforge.org.jetbrains.annotations</groupId>
<artifactId>org.jetbrains.annotations</artifactId>
<version>1.7.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.6</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.github.yitter</groupId>
<artifactId>yitter-idgenerator</artifactId>
<version>1.0.6</version>
</dependency>
Gradle
如下:
implementation 'com.baomidou:mybatis-plus-boot-starter:3.4.3.1'
implementation 'org.apache.velocity:velocity-engine-core:2.3'
implementation 'org.realityforge.org.jetbrains.annotations:org.jetbrains.annotations:1.7.0'
implementation 'com.alibaba:druid:1.2.6'
implementation 'org.apache.shardingsphere:sharding-jdbc-spring-boot-starter:4.1.1'
implementation 'com.github.yitter:yitter-idgenerator:1.0.6'
5 配置文件
spring:
shardingsphere:
datasource:
names: master-test0,master-test1,slave-test0,slave-test1 # 数据源节点名字
# master-test0 示意主节点的 test0 库,master-test1 示意主节点的 test1 库
# slave-test0 示意从节点的 test0 库,slave-test1 示意从节点的 test1 库
master-test0:
type: com.alibaba.druid.pool.DruidDataSource # 连接池
url: jdbc:mysql://127.0.0.1:3306/test0 # 主节点的 test0 库
username: root
password: 123456
master-test1:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/test1 # 主节点的 test1 库
username: root
password: 123456
slave-test0:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3307/test0 # 从节点的 test0 库,端口 3307
username: root
password: 123456
slave-test1:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3307/test1 # 从节点的 test1 库,端口 3307
username: root
password: 123456
sharding:
default-database-strategy:
inline:
sharding-column: age # 依照哪一列分库
algorithm-expression: master-test$->{age % 2} # 分库规定为对年龄取模
tables:
user:
actual-data-nodes: master-test$->{0..1}.user$->{0..2} # 分表的节点,格局为 [数据源. 表名]
table-strategy:
inline:
sharding-column: id # 依照哪一列分表
algorithm-expression: user$->{id%3} # 分表规定,对 id 取模
master-slave-rules: # 读写拆散的规定
master-test0: # 哪一个主节点
master-datasource-name: master-test0 # 指定主节点名字
slave-data-source-names: slave-test0 # 指定从节点名字
master-test1:
master-datasource-name: master-test1
slave-data-source-names: slave-test1
props:
sql:
show:
true # 打印 SQL
6 筹备测试代码
应用 MyBatis Plus Generator
生成器类生成代码:
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.*;
public class MyBatisPlusGenerator {public static void main(String[] args) {DataSourceConfig dataSourceConfig = new DataSourceConfig.Builder("jdbc:mysql://localhost:3306/test0", "root", "123456").build();
String projectPath = System.getProperty("user.dir");
StrategyConfig strategyConfig = new StrategyConfig.Builder().addInclude("user").build();
GlobalConfig globalConfig = new GlobalConfig.Builder().outputDir(projectPath + "/src/main/java").openDir(false).build();
PackageConfig packageConfig = new PackageConfig.Builder().moduleName("user").parent("com.example.demo").serviceImpl("service").build();
new AutoGenerator(dataSourceConfig).global(globalConfig).packageInfo(packageConfig).strategy(strategyConfig).execute();}
}
实体类加上 @Builder
,同时设置id
类型为IdType.ASSIGN_ID
:
@Builder
public class User implements Serializable {@TableId(type = IdType.ASSIGN_ID)
private Long id;
//...
}
批改 Controller
类:
@RestController
@RequestMapping("/user")
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class UserController {private final Random random = new Random();
private final UserServiceImpl service;
@GetMapping("/select")
public List<User> select(){return service.list();
}
@GetMapping("/insert")
public boolean insert(){return service.save(User.builder().age(random.nextInt(80)+20).name("test name").email("test@test.com").build());
}
}
同时新增一个雪花 id
生成器类(具体配置办法能够参考 MyBatis Plus
官网文档):
import com.baomidou.mybatisplus.core.incrementer.IdentifierGenerator;
import com.github.yitter.contract.IdGeneratorOptions;
import com.github.yitter.idgen.YitIdHelper;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
@Component
public class IdGenerator implements IdentifierGenerator {final IdGeneratorOptions options = new IdGeneratorOptions((short) 1);
@PostConstruct
public void init() {YitIdHelper.setIdGenerator(options);
}
@Override
public Long nextId(Object entity) {return YitIdHelper.nextId();
}
}
7 测试
刷新几次插入页面:
http://localhost:8080/user/insert
从输入能够看到插入都是在主节点中进行的:
而查问的时候:
http://localhost:8080/user/select
输入如下:
是在从节点查问的。
8 参考代码
Java
版:
- Github
- 码云
- CODE CHINA
Kotlin
版:
- Github
- 码云
- CODE CHINA