本文ShardingJDBC相关知识主要参考自ShardingJDBC官方文档，更多的用法建议到官网文档查看。

前言

传统的业务系统都是将数据集中存储至单一数据节点的解决方案，如今随着互联网数据的海量增长，单点存储早已无法满足性能、可用性和运维成本上的要求了，分库分表变成了目前数据库存储问题的主流解决方案。
目前最常见的MySQL分库分表选择主要为ShardingJDBC(基于JDBC驱动的轻量级Java框架)与MyCat(基于Proxy)。在这两个分库分表方案中个人更倾向于ShardingJDBC，主要原因如下：

官方文档详细优雅，入门容易
轻量级服务，搭建简便
相关产品与架构孵化演进都让人感到该开源项目未来可期

下图为ShardingSphere官网中的Sharding-JDBC+Sharding-Proxy架构图，通过混合使用Sharding-JDBC和Sharding-Proxy，并采用同一注册中心统一配置分片策略，灵活的搭建适用于各种场景的应用系统，更加自由的调整适合于当前业务的最佳系统架构。
sharding-mix-architecture

项目环境

本文例子主要基于Spring Boot+Mybatis plus+ShardingJDBC框架集成搭建项目，通过Docker配置运行Mysql主从容器，Spring Boot项目运行连接Docker Mysql容器进行实现完整的分库分表与读写分离，主要的搭建流程如下：

Docker与Docker Compose安装
Docker Compose启动容器
Mysql从机容器同步主机配置
启动Spring Boot项目

由于该项目主要是为了记录与展现分库分表与读写分离的实现搭建细节，所以Mysql的主从配置也是以双Master同时各配单Slave最低需求搭建的，具体架构图如(project-architecture)下：

搭建流程

数据表order_info_x

create table order_info
(
    id          bigint                              not null
        primary key,
    user_id     bigint                              not null comment '用户id',
    username    varchar(50)                         not null comment '用户名',
    note        varchar(300)                        not null default '' comment '备注',
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP
)
    comment '订单表';

该项目中实际的操作数据表为order_info_x，order_info表仅用于代码文件逆向生成。当生成的order.id%2==0时，则存到Master1数据库上，若order.id%==1时，则存到Master2数据库上；当确认存到哪台机器上时，再根据user_id%2判断是存到order_info_0还是order_info_1。
每当Master有数据库创建或数据更新时，Slave会通过日志将主机数据库信息同步。

项目搭建

项目文件树

Maven依赖

<properties>
    <shardingsphere.version>4.0.0</shardingsphere.version>
</properties>

<dependencies>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- 个人参数校验框架，可忽略 -->
    <dependency>
        <groupId>io.github.wilson-he</groupId>
        <artifactId>spring-boot-common</artifactId>
        <version>0.0.2</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>${shardingsphere.version}</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>com.google.guava</groupId>
        <artifactId>guava</artifactId>
    </dependency>
</dependencies>

<build>
    <finalName>${project.artifactId}</finalName>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
        <!-- 个人开源mybatis-plus代码生成器插件 -->
        <plugin>
            <groupId>io.github.wilson-he</groupId>
            <artifactId>generator-maven-plugin</artifactId>
            <version>0.2.4</version>
            <configuration>
                <basePackage>io.wilson.sharding</basePackage>
                <dataSource>
                    <url>jdbc:mysql://localhost/order_1</url>
                    <username>root</username>
                    <password>tiger</password>
                </dataSource>
                <templates>
                    <excludeController>false</excludeController>
                </templates>
                <crudIdType>Long</crudIdType>
                <!-- 只生成表order_info的实体类 -->
                <inclusions>order_info</inclusions>
            </configuration>
        </plugin>
    </plugins>
</build>

Mybatis Plus语句打印优化配置：ExtMybatisXMLLanguageDriver.java

/**
 * 解决Mybatis Plus原生Mapper语句的格式问题
 *
 * @author Wilson
 * @since 2020/1/13
 **/
public class ExtMybatisXMLLanguageDriver extends MybatisXMLLanguageDriver {
    /**
     * 缓存反射字段，减少反射带来的性能损耗
     */
    private final Field sqlField;

    public ExtMybatisXMLLanguageDriver() throws NoSuchFieldException {
        Class boundSqlClass = BoundSql.class;
       sqlField = boundSqlClass.getDeclaredField("sql");
       try {
           sqlField.setAccessible(true);
       } catch (Exception e) {
           e.printStackTrace();
       }
   }

    @Override
    public MybatisDefaultParameterHandler createParameterHandler(MappedStatement mappedStatement,
                                                                 Object parameterObject, BoundSql boundSql) {
        String sql = boundSql.getSql();
        try {
            sqlField.set(boundSql, sql.replaceAll("\r\n|\n", " ")
                    .replaceAll("\\s{2,}", " "));
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return new MybatisDefaultParameterHandler(mappedStatement, parameterObject, boundSql);
    }
}

该配置类主要用于优化在使用Sharding JDBC时Mybatis Plus的语句格式问题，下图为去除当前类配置后ShardingJDBC打印MybtisPlus原生Mapper语句的格式问题图：
在这里插入图片描述

OrderController.java

@RestController
@RequestMapping("/orderInfo")
public class OrderInfoController {
    @Resource
    private OrderInfoService orderInfoService;

    @PostMapping("/")
    public ServerResponse add(@Validated @RequestBody OrderVO vo) {
        return ServerResponse.success(orderInfoService.insert(vo.orderInfo()));
    }

    @PutMapping("/")
    public ServerResponse update(@Validated @RequestBody OrderVO vo) {
        return ServerResponse.success(orderInfoService.updateById(vo.orderInfo()));
    }

    @GetMapping("/")
    public ServerResponse get(@RequestParam Long id) {
        return ServerResponse.success(orderInfoService.findById(id));
    }

    @GetMapping("/list")
    public ServerResponse list() {
        return ServerResponse.success(orderInfoService.list());
    }
}

主程序ShardingApplication.java

@SpringBootApplication
@MapperScan("io.wilson.sharding.mapper")
public class ShardingApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingApplication.class, args);
    }
}

配置文件application-sharding.yml，分片规则根据以上project-architecture图

spring:
  shardingsphere:
  # 根据个人实际配置，该实例中配置的是个人的ecs ip
    host: localhost
    datasource:
      ds0:
        username: root
        password: tiger
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://${spring.shardingsphere.host}:3306/order_0
      ds0-slave:
        username: root
        password: tiger
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://${spring.shardingsphere.host}:3307/order_0
      ds1:
        username: root
        password: tiger
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://${spring.shardingsphere.host}:4306/order_0
      ds1-slave:
        username: root
        password: tiger
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://${spring.shardingsphere.host}:4306/order_0
      # 配置数据源名称
      names: ds0,ds0-slave,ds1,ds1-slave
    sharding:
      default-database-strategy:
        inline:
          # 根据id%2取模判断当前数据存取是在ds0还是ds1对应的数据库
          algorithm-expression: ds$->{id % 2}
          # 根据user_id分片存取数据
          sharding-column: id
      tables:
        order_info:
          # 根据sharding生成的id%2是0还是1分配到数据库_0还是_1
          actual-data-nodes: ds$->{0..1}.order_info_$->{0..1}
          # 雪花算法生成id
          keyGenerator:
            type: SNOWFLAKE
            column: id
          # 根据user_id%2取模确定分配到数据表_0还是_1
          table-strategy:
            inline:
              # 根据user_id%2取模判断当前数据存取是在表order_info_0还是order_info_1
              algorithm-expression: order_info_$->{user_id % 2}
              # 根据user_id分片存取数据
              sharding-column: user_id
      # 多主从配置
      master-slave-rules:
        ds0:
          masterDataSourceName: ds0
          slave-data-source-names: [ds0-slave]
        ds1:
            masterDataSourceName: ds1
            slaveDataSourceNames: [ds1-slave]
    # 单主从配置
#    masterslave:
#      name: ds0_ms
#      masterDataSourceName: ds0
#      slaveDataSourceNames: [ds0-slave]
   props:
     # 打印分片信息与SQL语句
     sql.show: true

配置文件application.yml

spring:
  common:
    validation:
      msg-locale: zh_CN
    serialization:
      longToString: true
  profiles:
    include: sharding
swagger:
  docket:
    base-package: io.wilson.sharding.controller
mybatis-plus:
  configuration:
    default-scripting-language: io.wilson.sharding.mybatis.ExtMybatisXMLLanguageDriver

Docker容器配置

该例子中为了简便Docker只用于部署Mysql集群，若想更全面的使用Docker将Mysql与SpringBoot项目整合可参考 Docker+Jenkins+Nginx+SpringBoot自动化部署项目，然后在docker-compose.yml中将Spring Boot容器depends_on Mysql容器。

配置文件docker-compose.yml

version: "3.7"
services:
  db-master:
    image: mysql:5.7.28
    container_name: mysql-master1
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      # root用户密码
      MYSQL_ROOT_PASSWORD: tiger
      TZ: Asia/Shanghai
    # 设置容器hostname，使从机可通过hostname连接主机而不必使用ip
    hostname: mysql-master1
    ports:
      - 3306:3306
    volumes:
      - ./mysql/master1/data:/var/lib/mysql
      - ./mysql/master1/log:/var/log/mysql
      - ./mysql/master1/conf:/etc/mysql
      # mysql初始化执行文件，用于创建同步用户
      - ./mysql/init.sql:/docker-entrypoint-initdb.d/init.sql
  db-slave:
    image: mysql:5.7.28
    container_name: mysql-master1-slave1
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    hostname: mysql-master1-slave1
    environment:
      MYSQL_ROOT_PASSWORD: tiger
      TZ: Asia/Shanghai
    ports:
      - 3307:3306
    volumes:
      - ./mysql/slave1/data:/var/lib/mysql
      - ./mysql/slave1/log:/var/log/mysql
      - ./mysql/slave1/conf:/etc/mysql
  db-master2:
    image: mysql:5.7.28
    container_name: mysql-master2
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      # root用户密码
      MYSQL_ROOT_PASSWORD: tiger
      TZ: Asia/Shanghai
    # 设置容器hostname，使从机可通过hostname连接主机而不必使用ip
    hostname: mysql-master2
    ports:
      - 4306:3306
    volumes:
      - ./mysql/master2/data:/var/lib/mysql
      - ./mysql/master2/log:/var/log/mysql
      - ./mysql/master2/conf:/etc/mysql
      # mysql初始化执行文件，用于创建同步用户slave
      - ./mysql/init.sql:/docker-entrypoint-initdb.d/init.sql
  db-master2-slave1:
    image: mysql:5.7.28
    container_name: mysql-master2-slave1
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    hostname: mysql-master2-slave1
    environment:
      MYSQL_ROOT_PASSWORD: tiger
      TZ: Asia/Shanghai
    ports:
      - 4307:3306
    volumes:
      - ./mysql/slave2/data:/var/lib/mysql
      - ./mysql/slave2/log:/var/log/mysql
      - ./mysql/slave2/conf:/etc/mysql

在docker-compose.yml目录下执行docker-compose up -d指令，查看mysql master容器mysql binary log文件(File)与位置(Position)：
在这里插入图片描述

1
2
3

docker exec -it mysql-master1 /bin/bash
mysql -uroot -ptiger
show master status;

slave容器mysql master连接配置并进行同步：

docker exec -it mysql-master1-slave1 /bin/bash
mysql -uroot -ptiger
# master_host可设为ip或容器hostname，使用用户为slave连接主机并通过log同步
change master to master_host='mysql-master1', master_user='slave', master_password='123456', master_port=3306, master_log_file='master1-bin.000005', master_log_pos=154, master_connect_retry=30;
# 开启同步
start slave;

Slave1根据Master1配置binlog，Slave2根据Master2配置binlog，更具体的主从同步可参考Docker搭建基于binlog的Mysql主从同步，此处便省略一些配置文件讲解了，更详细的项目信息可到文末查看项目地址，当完成配置后即可启动本地Spring Boot项目，可以看到2主2从都已被初始化。
在这里插入图片描述

运行测试

插入数据根据id与user_id是否成功写入到相应的库与数据表，并查看从库数据是否同步

插入数据的id%2=0,user_id%2=1，根据前面定义的规则会放入master1的order_0.order_info_0表，查看控制台与master-slave1数据库数据校验数据是否正确、数据是否同步，如下图：
id与user_id都为基数时，根据规则数据放入master2的order_1.order_info_1，以下为测试数据与校验图：
读分离测试
1中已经成功验证了只会写Master库并从库通过binlog同步数据，读分离验证则只需获取OrderInfo列表(OrderController.list接口)查看JDBC从哪查即可：

从上图可以看出ShardingJDBC已成功从所有从库中获取数据

耀阳居士

Docker配置SpringBoot+ShardingJDBC+MybatisPlus项目实现分库分表与读写分离