一、背景:因为项目需要,要求对两个大表进行分表,每个月分一个表,保证单表数据不会特别大,后续查询限定时间段,保证整体查询和统计性能。作为搞java的农民工,自然选择了java契合度比较高的分库中间件:shardingjdbc,使用也肯定使用最新版本5.0.0-beta,经过一顿折腾,终于分库成功,直接上代码。
二、开发环境:springboot(2.3.5.RELEASE)+mybatis-plus(3.4.3)+mysql(8.0.26)+shardingSphere-jdbc(5.0.0-beta)
三、依赖引入
<!-- druid 数据源,一定不能用druid-spring-boot-starter -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<!--一定不能引入dynamic-datasource-spring-boot-starter包-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0-beta</version>
</dependency>
说明:sharding不同版本很容易产生包冲突,sharding引入的自启动包就不能引入druid-spring-boot-starter和dynamic-datasource-spring-boot-starter依赖
四、application.yml文件配置
server:
port: 8080
tomcat:
max-swallow-size: -1
error:
include-exception: true
include-stacktrace: ALWAYS
include-message: ALWAYS
compression:
enabled: true
min-response-size: 1024
mime-types: application/javascript,application/json,application/xml,text/html,text/xml,text/plain,text/css,image/*
spring:
shardingsphere:
enabled: true
datasource:
#指定数据源 名称可以自定义,注意:名称要跟后面的配置一致
names: ds0
#配置数据源的连接信息
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
username: root
password: root
rules:
sharding:
default-data-source-name: ds0
props:
#是否输出sql
sql-show: true
#绑定表规则列表,多个以逗号分割
binding-tables[0]: device_pass_record,device_alarm_record
tables:
#设置device_pass_record表分表策略
device_pass_record:
actual-data-nodes: ds0.device_pass_record20$->{21..99}$->{1..12}
table-strategy:
standard:
sharding-column: pass_month
sharding-algorithm-name: sharding-pass-by-month
#设置device_pass_record表分表策略
device_alarm_record:
actual-data-nodes: ds0.device_alarm_record20$->{21..99}$->{1..12}
table-strategy:
standard:
sharding-column: pass_month
sharding-algorithm-name: sharding-alarm-by-month
#分库或者分表算法配置
sharding-algorithms:
#device_pass_record分表算法配置
sharding-pass-by-month:
type: INLINE
props:
algorithm-expression: device_pass_record$->{pass_month}
# device_alarm_record分表策略配置
sharding-alarm-by-month:
type: INLINE
props:
algorithm-expression: device_alarm_record$->{pass_month}
#mybatis plus 设置
mybatis-plus:
mapper-locations: classpath*:/org/wfl/community/mapper/xml/*Mapper.xml
global-config:
# 关闭MP3.0自带的banner
banner: false
db-config:
#主键类型 0:"数据库ID自增",1:"该类型为未设置主键类型", 2:"用户输入ID",3:"全局唯一ID (数字类型唯一ID)", 4:"全局唯一ID UUID",5:"字符串全局唯一ID (idWorker 的字符串表示)";
id-type: ASSIGN_ID
# 默认数据库表下划线命名
table-underline: true
logic-delete-field: delFlag
logic-delete-value: 1
logic-not-delete-value: 0
configuration:
# 这个配置会将执行的sql打印出来,在开发或测试的时候可以用
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# 返回类型为Map,显示null对应的字段
call-setters-on-nulls: true
说明:本配置实现了 device_pass_record,device_alarm_record两个表,以pass_month字段作为分片健进行分表。ds0库其他表没有配置分片算法,默认不进行分表
五、扩展:后续可以添加多个数据源,实现横向分库分表,除了以上配置,需要配置分库策略 database-strategy,如下:
spring:
shardingsphere:
enabled: false
datasource:
#指定数据源 名称可以自定义,注意:名称要跟后面的配置一致
names: ds0,ds1
#配置数据源的连接信息
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding1?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
username: root
password: root
#配置数据源的连接信息
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding2?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
username: root
password: root
rules:
sharding:
props:
#是否输出sql
sql-show: true
tables:
#设置device_pass_record表分库分表策略
device_pass_record:
#需要进行分片的库中实际存在的表
actual-data-nodes: ds$->{0..1}.device_pass_record20$->{21..30}$->{1..12}
#分库策略
database-strategy:
standard:
#根据id分库
sharding-column: id
sharding-algorithm-name: sharding-pass-database
#分表策略
table-strategy:
standard:
#根据pass_month分表
sharding-column: pass_month
sharding-algorithm-name: sharding-pass-by-month
#分库或者分表算法配置
sharding-algorithms:
#分库策略配置
sharding-pass-database:
type: INLINE
props:
algorithm-expression: ds$->{id % 2}
#分表策略配置
sharding-pass-by-month:
type: INLINE
props:
algorithm-expression: device_pass_record$->{pass_month}