查看此文章前先参考上篇文章,数据分表和读写分离
今天开始分享分库分表+读写分离,相关jar包参考以前文章,数据源也大概相同,核心在配置文件,废话少说,开始:
1、此次采用4个数据库user0,user1,user2,user3,三张表 tbl_user0、tbl_user1、tbl_user2,两主两从配置策略。
2、数据库表/mapper/xml配置
2.1、user0 、user1 数据库表结构
CREATE TABLE `tbl_user0` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL COMMENT '名称',
`city_id` int(12) DEFAULT NULL COMMENT '城市',
`sex` tinyint(1) DEFAULT NULL COMMENT '性别',
`phone` varchar(32) DEFAULT NULL COMMENT '电话',
`email` varchar(32) DEFAULT NULL COMMENT '邮箱',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`password` varchar(32) DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;
CREATE TABLE `tbl_user1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL COMMENT '名称',
`city_id` int(12) DEFAULT NULL COMMENT '城市',
`sex` tinyint(1) DEFAULT NULL COMMENT '性别',
`phone` varchar(32) DEFAULT NULL COMMENT '电话',
`email` varchar(32) DEFAULT NULL COMMENT '邮箱',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`password` varchar(32) DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;
CREATE TABLE `tbl_user2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL COMMENT '名称',
`city_id` int(12) DEFAULT NULL COMMENT '城市',
`sex` tinyint(1) DEFAULT NULL COMMENT '性别',
`phone` varchar(32) DEFAULT NULL COMMENT '电话',
`email` varchar(32) DEFAULT NULL COMMENT '邮箱',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`password` varchar(32) DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;
2.2、实体类
import java.io.Serializable;
import java.util.Date;
public class TblUser implements Serializable {
private Long id;
private String name;
private Integer cityId;
private Boolean sex;
private String phone;
private String email;
private Date createTime;
private String password;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
public Integer getCityId() {
return cityId;
}
public void setCityId(Integer cityId) {
this.cityId = cityId;
}
public Boolean getSex() {
return sex;
}
public void setSex(Boolean sex) {
this.sex = sex;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone == null ? null : phone.trim();
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email == null ? null : email.trim();
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password == null ? null : password.trim();
}
}
2.3、mapper和xml配置 参考上篇文章
3、分库分表和读写分离配置策略
sharding:
jdbc:
datasource:
names: user0,user1,user2,user3
user0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sys_db?useAffectedRows=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=CTT&zeroDateTimeBehavior=convertToNull
username: root
password: 123456
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
stat-view-servlet:
enabled: true
url-pattern: /druid/*
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
user1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/u_db?useAffectedRows=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=CTT&zeroDateTimeBehavior=convertToNull
username: root
password: 123456
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
stat-view-servlet:
enabled: true
url-pattern: /druid/*
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
user2:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/t_sys?useAffectedRows=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=CTT&zeroDateTimeBehavior=convertToNull
username: root
password: 123456
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
stat-view-servlet:
enabled: true
url-pattern: /druid/*
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
user3:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/a_db?useAffectedRows=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=CTT&zeroDateTimeBehavior=convertToNull
username: root
password: 123456
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
stat-view-servlet:
enabled: true
url-pattern: /druid/*
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
config:
sharding:
default-database-strategy:
inline:
sharding-column: id #分库的字段,本案例是根据id进行分
algorithm-expression: ds_$->{id % 2} #分库策略
tables:
tbl_user:
actual-data-nodes: ds_$->{0..1}.tbl_user$->{0..2} #分库分表的策略
table-strategy:
inline:
sharding-column: id #分表的字段,本案例是根据id进行分
#precise-algorithm-class-name: # 可以是分表算法的实现类
algorithm-expression: tbl_user$->{id % 3} #分表的算法
key-generator-column-name: id #自动生成id
#default-data-source-name: ds_0
#binding-tables: tbl_user #绑定表规则列表
master-slave-rules:
ds_0: #数据库分区
master-data-source-name: user0 #ds_0区的主库
slave-data-source-names: user1 #ds_0区的从库
ds_1:
master-data-source-name: user2 #ds_1区的主库
slave-data-source-names: user3 #ds_1区的从库
props:
sql:
show: true #是否开启SQL显示,默认值: false
executor:
size: 20 #工作线程数量,默认值: CPU核数
check:
table:
metadata:
enabled: true #是否在启动时检查分表元数据一致性,默认值: false
4、测试API
for(Long i=10L;i<15;i++) {
TblUser user = new TblUser();
user.setId(i);
user.setName("nandao"+i+"");
user.setCityId(1 % 2 == 0 ? 1 : 2);
user.setCreateTime(new Date());
user.setSex(true);
user.setPhone("11111111");
user.setEmail("xxxxx");
user.setCreateTime(new Date());
user.setPassword("eeeeeeeeeeee");
tblUserMapper.insertOne(user);
}
5、执行的部分日志:
[http-nio-8088-exec-1] INFO ShardingSphere-SQL - Rule Type: sharding
[http-nio-8088-exec-1] INFO ShardingSphere-SQL - Logic SQL: insert into tbl_user (id, name, city_id,
sex, phone, email, create_time,
password)
values (?, ?, ?,
?, ?, ?, ?,
?)
[http-nio-8088-exec-1] INFO ShardingSphere-SQL - SQLStatement: InsertStatement(super=DMLStatement(super=io.shardingsphere.core.parsing.parser.sql.dml.insert.InsertStatement@57b9717e), columns=[Column(name=id, tableName=tbl_user), Column(name=name, tableName=tbl_user), Column(name=city_id, tableName=tbl_user), Column(name=sex, tableName=tbl_user), Column(name=phone, tableName=tbl_user), Column(name=email, tableName=tbl_user), Column(name=create_time, tableName=tbl_user), Column(name=password, tableName=tbl_user)], generatedKeyConditions=[GeneratedKeyCondition(column=Column(name=id, tableName=tbl_user), index=0, value=null)], insertValues=InsertValues(insertValues=[InsertValue(type=VALUES, expression=(?, ?, ?,
?, ?, ?, ?,
?), parametersCount=8)]), columnsListLastPosition=94, generateKeyColumnIndex=0, insertValuesListLastPosition=144)
[http-nio-8088-exec-1] INFO ShardingSphere-SQL - Actual SQL: ds_1 ::: insert into tbl_user1 (id, name, city_id,
sex, phone, email, create_time,
password)
values (?, ?, ?,
?, ?, ?, ?,
?) ::: [[13, nandao13, 2, true, 11111111, xxxxx, 2022-01-26 20:43:30.694, eeeeeeeeeeee]]
[http-nio-8088-exec-1] DEBUG c.h.e.db.mapper.TblUserMapper.insertOne - <== Updates: 1
[http-nio-8088-exec-1] DEBUG org.mybatis.spring.SqlSessionUtils - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@13e589fa]
[http-nio-8088-exec-1] DEBUG org.mybatis.spring.SqlSessionUtils - Creating a new SqlSession
[http-nio-8088-exec-1] DEBUG org.mybatis.spring.SqlSessionUtils - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1706af65] was not registered for synchronization because synchronization is not active
[http-nio-8088-exec-1] DEBUG o.m.spring.transaction.SpringManagedTransaction - JDBC Connection [io.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@3fca1fc0] will not be managed by Spring
[http-nio-8088-exec-1] DEBUG c.h.e.db.mapper.TblUserMapper.insertOne - ==> Preparing: insert into tbl_user (id, name, city_id, sex, phone, email, create_time, password) values (?, ?, ?, ?, ?, ?, ?, ?)
[http-nio-8088-exec-1] DEBUG c.h.e.db.mapper.TblUserMapper.insertOne - ==> Parameters: 14(Long), nandao14(String), 2(Integer), true(Boolean), 11111111(String), xxxxx(String), 2022-01-26 20:43:30.721(Timestamp), eeeeeeeeeeee(String)
[http-nio-8088-exec-1] INFO ShardingSphere-SQL - Rule Type: sharding
[http-nio-8088-exec-1] INFO ShardingSphere-SQL - Logic SQL: insert into tbl_user (id, name, city_id,
sex, phone, email, create_time,
password)
values (?, ?, ?,
?, ?, ?, ?,
?)
[http-nio-8088-exec-1] INFO ShardingSphere-SQL - SQLStatement: InsertStatement(super=DMLStatement(super=io.shardingsphere.core.parsing.parser.sql.dml.insert.InsertStatement@57b9717e), columns=[Column(name=id, tableName=tbl_user), Column(name=name, tableName=tbl_user), Column(name=city_id, tableName=tbl_user), Column(name=sex, tableName=tbl_user), Column(name=phone, tableName=tbl_user), Column(name=email, tableName=tbl_user), Column(name=create_time, tableName=tbl_user), Column(name=password, tableName=tbl_user)], generatedKeyConditions=[GeneratedKeyCondition(column=Column(name=id, tableName=tbl_user), index=0, value=null)], insertValues=InsertValues(insertValues=[InsertValue(type=VALUES, expression=(?, ?, ?,
?, ?, ?, ?,
?), parametersCount=8)]), columnsListLastPosition=94, generateKeyColumnIndex=0, insertValuesListLastPosition=144)
[http-nio-8088-exec-1] INFO ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into tbl_user2 (id, name, city_id,
sex, phone, email, create_time,
password)
values (?, ?, ?,
?, ?, ?, ?,
?) ::: [[14, nandao14, 2, true, 11111111, xxxxx, 2022-01-26 20:43:30.721, eeeeeeeeeeee]]
[http-nio-8088-exec-1] DEBUG c.h.e.db.mapper.TblUserMapper.insertOne - <== Updates: 1
[http-nio-8088-exec-1] DEBUG org.mybatis.spring.SqlSessionUtils - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1706af65]
6、执行后数据库两个主库三张表的数据显示:
user0:
tbl_user0:
tbl_user1:
tbl_user2:
user2:
tbl_user0:
无数据
tbl_user1:
tbl_user2
到此、分库分表、读写分离分享完毕,下篇分享主从复制,敬请期待!