SpringBoot+Sharding-jdbc 分库分表+读写分离（踩坑经历）

<!-- 先导包 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
	<version>8.0.21</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
	<version>1.1.16</version>
</dependency>
<!-- springboot-sharding包 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
	<version>2.1.3</version>
</dependency>
<!-- mybatis-plus包，单纯的不想写mapper.xml -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
	<version>3.3.2</version>
</dependency>
复制代码

代码编写部分和普通的 crud 一样，直接上 sharding 配置文件, 可能有点长

主库和从库中数据库名和表名都一样，mysql主从搭建可以参考我得上一篇文章
其中两个数据库名字：m_s_demo0, m_s_demo1
每个数据库中都是两张表：user_0, user_1

# 两个主库，两个从库，每个从库对应起数字的主库
spring.shardingsphere.datasource.names=master0,master1,slave0,slave1
# 两个主库数据源配置
spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master0.url=jdbc:mysql://192.168.56.102:3306/m_s_demo0?characterEncoding=utf-8
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=ratil123

spring.shardingsphere.datasource.master1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master1.url=jdbc:mysql://192.168.56.102:3306/m_s_demo1?characterEncoding=utf-8
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=ratil123

# 两个从库数据源配置
spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://192.168.56.101:3306/m_s_demo0?characterEncoding=utf-8
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=ratil123

spring.shardingsphere.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://192.168.56.101:3306/m_s_demo1?characterEncoding=utf-8
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=ratil123

# 分表, 这里是数据库里的实际表名称
spring.shardingsphere.sharding.tables.user.actual-data-nodes=m_s_demo$->{0..1}.user_$->{0..1}

# inline
# 分表的字段（user 表）,通过 id 分，其中的 user 是表名
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
# 分表策略，这是直接简单点取模
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 2}
# 这里自动生成主键的，我项目中没用到
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
# 路由数据应该到哪个库中，grovy 表达式的 '/' 会是浮点数，得用 intdiv 来计算
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=m_s_demo$->{id.intdiv(2) % 2}
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=id

# 从库负载均衡算法：round_rabin 轮询，random 随机
# 主从分离配置, 每个数据库对应在哪个主从中，其中的 m_s_demo 是数据库名
spring.shardingsphere.sharding.master-slave-rules.m_s_demo0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.m_s_demo0.slave-data-source-names=slave0

spring.shardingsphere.sharding.master-slave-rules.m_s_demo1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.m_s_demo1.slave-data-source-names=slave1

# 配置默认的标准分库，写了第一个第二个就必填，否则会 nullPointException
#spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=id
#spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=
# log 打印 sql
spring.shardingsphere.props.sql.show=true
复制代码

学习过程中遇到的一些问题：

找不到表（找不到m_s_demo0.user_0 这种）：

一开始可能是 spring.shardingsphere.sharding.tables.user.actual-data-nodes 配置错了
然后是检查一下主从分离配置

查询不走从库，都走主库

一开始我用的 spring.shardingsphere.masterslave 配置（百度有的教程看到的），后面发现要用 master-slave-rules 来配置