SpringBoot2.1集成Sharding-JDBC实现分库分表是每个互联网企业必备的技术栈,今天我们开始分享一系列文章来详细分析:
1、引入核心jar:
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-core</artifactId>
<version>3.1.1</version>
</dependency>
2、核心配置文件:
sharding:
jdbc:
datasource:
names: user0,user1
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: 7cz.jXR3mycw
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: 7cz.jXR3mycw
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:
tables:
user_info:
actual-data-nodes: user$->{0..1}.user_info$->{0..1}
database-strategy: #分库策略
inline: #行表达式
sharding-column: gender #列名称,多个列以逗号分隔
algorithm-expression: user$->{gender % 2} #按模运算分配
table-strategy: #分表策略
inline: #行表达式
sharding-column: user_id
algorithm-expression: user_info$->{user_id % 2}
props:
sql:
show: true #打印sql
executor:
size: 20
3、创建表结构、实体、mapper过程大概省略:
。。。自己创建普通的表
CREATE TABLE `tbl_user0` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL COMMENT '名称',
`city_id` int(12) DEFAULT NULL COMMENT '城市',
`user_id` int(12) DEFAULT NULL COMMENT '用户id',
`gender` 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`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
4、执行插入的日志:
[http-nio-23888-exec-5] INFO ShardingSphere-SQL - Actual SQL: user1 ::: insert into user_info0 ( user_id,name,id,...
)
values (?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?,
?, ?, ?,
?, ?, ?,
?,?,?,?
) ::: [[ .........]]
user1数据中的 user_info0 表插入数据
5、执行查询的日志:类似的
如果分页查就去两个数据库查(没有user_id参数);如果用user_id 就去一个库查询;
6、特别注意!1中 第二个 core(sharding-core) 包,必须加而且版本必须是3.1.1以上,否则分页查询出现问题(比如第一页10条,第二页20条,第三页30条....的错误现象)。
到此、单一的分库分表分享完毕,下期我们分享数据库中仅某张表进行分库分表,其他的不分,敬请期待。