背景
随着业务量的增长,业务表单表记录已经达到百万级别,并且仍在不断增加,查询效率也不断下降,查询时间长,经过讨论决定单库分表。技术选型沿用公司技术栈shardingjdbc,通过看官方文档,发现最新版出到5.x,但是文档个别使用描述不甚清楚,及网上使用5.x资料颇少,所以退求其次选择4.x版本。
直接开始
pom
springboot+mybatis-plus部分maven就不贴了,网上资料大把,直接说shardingjdbc。
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
然后是YML配置
spring:
#sharding-jdbc
shardingsphere:
props:
sql:
show: true
datasource:
names: master0,slave0
master0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: 'jdbc:mysql://192.168.0.1:3306/test?characterEncoding=utf-8&allowMultiQueries=true'
username: xxx
password: 'xxx'
initialSize: 5
minIdle: 10
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 2000
minEvictableIdleTimeMillis: 600000
maxEvictableIdleTimeMillis: 900000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
asyncInit: true
filters: stat,log4j
slave0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: 'jdbc:mysql://192.168.0.2:3306/test?characterEncoding=utf-8&allowMultiQueries=true'
username: xxx
password: 'xxx'
initialSize: 5
minIdle: 10
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 2000
minEvictableIdleTimeMillis: 600000
maxEvictableIdleTimeMillis: 900000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
asyncInit: true
filters: stat,log4j
sharding:
master-slave-rules:
ds0:
master-data-source-name: master0
slave-data-source-names:
- slave0
load-balance-algorithm-type: ROUND_ROBIN
tables:
#分表名
user:
actual-data-nodes: ds0.user$->{0..3}
database-strategy:
none:
table-strategy:
#单一分片自定义算法
# standard:
# sharding-column: user_id
# precise-algorithm-class-name: com.sharding.UsrPreciseShardingAlgorithm
#复合分片键自定义算法
# complex:
# sharding-columns: user_id
# algorithm-class-name: com.sharding.UsrPreciseShardingAlgorithm
#行表达式分片策略
inline:
sharding-column: user_id
algorithm-expression: user$->{user_id % 4}
key-generator:
column: id
type: SNOWFLAKE
举例子逻辑表名就用user,分为4个表,user0,user1,user2,user3根据user_id % 4 分表。并且使用一主一从读写分离master0写入,slave0读取,如果有多从库,在datasource下增加数据源后,在slave-data-source-names配置下增加就ok。
这样user表的单库分表+读写分离就完成,补全controller,service,mapper就可以见证奇迹了,代码就不贴了。
关于join查询
经过测试发现,如果分表作为主表join其他表关联查询,where条件中带有分片键,则不会出现多表查询。
select * from user a
left join user_detail b on a.user_id = b.user_id
where a.user_id=xxx
Logic SQL: select * from user a left join user_detail b on a.user_id = b.user_id where a.user_id=100
Actual SQL: slave0 ::: select * from user0 a left join ser_detail b on a.user_id = b.user_id where a.user_id=100
如果条件不是分片键,shardingjdbc没法确定在哪个表,则会全部查询一遍笛卡尔积,比如我们改成用id
Logic SQL: select * from user a left join user_detail b on a.user_id = b.user_id where a.id=100
Actual SQL: slave0 ::: select * from user0 a left join user_detail b on a.user_id = b.user_id where a.id=100
Actual SQL: slave0 ::: select * from user1 a left join user_detail b on a.user_id = b.user_id where a.id=100
Actual SQL: slave0 ::: select * from user2 a left join user_detail b on a.user_id = b.user_id where a.id=100
Actual SQL: slave0 ::: select * from user3 a left join user_detail b on a.user_id = b.user_id where a.id=100
这样效率会低很多,所以分片键确定很重要,当然我们用的是行分片策略,大家也可以采用复合分片策略,自己实现ComplexKeysShardingAlgorithm接口。
关于日志打印
我们可以发现,shardingjdbc会详细打印出查询的逻辑,Logic SQL是xxxxx,Actual SQL是xxxx,方便我们观察及排查问题,这个日志打印是通过配置控制的
spring:
shardingsphere:
props:
sql:
show: true
网上很多资料写的是sql-show: true,坑!