使用ShardingShpere来实现读写分离跟分库分表

环境准备

两个mysql集群，一主一从

我们简单的用docker-compose来快速搭建一个

Copy
version: '3'
services:
  master1:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    ports:
      - "3307:3306"
    volumes:
      - ./master1/data:/var/lib/mysql
      - ./master1/conf/my.cnf:/etc/mysql/conf.d/my.cnf

  slave1:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    ports:
      - "3308:3306"
    volumes:
      - ./slave1/data:/var/lib/mysql
      - ./slave1/conf/my.cnf:/etc/mysql/conf.d/my.cnf

  master2:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    ports:
      - "3309:3306"
    volumes:
      - ./master2/data:/var/lib/mysql
      - ./master2/conf/my.cnf:/etc/mysql/conf.d/my.cnf

  slave2:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    ports:
      - "3310:3306"
    volumes:
      - ./slave2/data:/var/lib/mysql
      - ./slave2/conf/my.cnf:/etc/mysql/conf.d/my.cnf

数据库集群搭建完成后我们在两个集群中创建两个库daily，然后分别创建4张表t_user_0 ~ t_user_3，表结构非常简单，就一个id,姓名跟性别

Copy
create table t_user_0
(
    id       bigint       not null
        primary key,
    username varchar(255) null,
    gender   tinyint      null
);

具体搭建步骤可以参考我之前的博客 mysql主从同步

项目搭建

我们使用SpringBoot来集成shardingsphere，mybatis-plus来作为orm框架。

相关pom如下：

Copy
<dependency>
     
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.5.3</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.32</version>
    </dependency>
   <!--省略SpringBoot相关依赖-->
    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>4.1.0</version>
    </dependency>

项目目录结构如下：

模型类：#

Copy
@TableName("t_user")
class User {
    var id:Long? = null
    var username:String? = null
    // 0：女性|1：男性
    var gender:Int = 0
    override fun toString(): String {
        return "User(id=$id, username=$username, gender=$gender)"
    }

}

Mapper#

Copy
@Mapper
interface UserMapper:BaseMapper<User> {
}

Service&Impl#

Copy
interface IUserService:IService<User> {
}

@Service
class UserServiceImpl(
    var userMapper: UserMapper
):ServiceImpl<UserMapper,User>(),IUserService {
}

配置文件#

我们目标是将男性用户跟女性用户分表放在master1跟master2中，并且按照分别存储在4张表中t_user_0~t_user_3。我们先将用户按照性别划分存在哪个库中，再按照id来决定存在哪个表中。

我们这里id使用shardingsphere提供的雪花算法来自动生成，也可以用mybatis-plus的提供的，两者取一。

Copy
# 4个数据库的数据源信息 
spring.shardingsphere.datasource.names=master1,slave1,master2,slave2

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://localhost:3307/daily?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=123456

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://localhost:3308/daily?useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456


spring.shardingsphere.datasource.master2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2.jdbc-url=jdbc:mysql://localhost:3309/daily?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master2.username=root
spring.shardingsphere.datasource.master2.password=123456

spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://localhost:3310/daily?useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=123456



#id 使用雪花算法
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

#database-strategy   基于gender进行分库 
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=gender
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=master$->{gender % 2+1}
# table-strategy  基于id进行分表

spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user_$->{id%4}
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=master$->{1..2}.t_user_$->{0..3}


#master-slave   基于master1和master2主从集群实现读写分离
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave1
spring.shardingsphere.sharding.master-slave-rules.master2.master-data-source-name=master2
spring.shardingsphere.sharding.master-slave-rules.master2.slave-data-source-names=slave2

#多个从库的时候使用负载均衡
spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN

# 打印执行sql
spring.shardingsphere.props.sql.show=true

测试#

Copy
 @Test
    fun test1(){
        val list = mutableListOf<User>()
        for (i in 0..200) {
            list.add(User().apply {
                this.username = "张三$i"
                gender = i%2
            })

        }
        userService.saveBatch(list)
    }

可以看到确实已经分别插入到不同的库跟表

测试一下读写分离功能：

我们在从库里面找一条数据，然后更改一下里面的值，我们再查询一下

1659885607800840196 我们把张三100改成张三100_slave

可以看到查询的时候确实是从 从库里面查询的

我们再在slave2中修改一条数据 1659885607817617412中将张三109修改为张三109_slave