Spring boot整合Sharding-JDBC实现分库分表

写在前面的话
为什么要分库分表，这是基于mysql的b+树的特性来的，当单表的数据量大了之后要维护一棵庞大的树，这样会使得增删改查的性能大大降低。

本文基于一个普通Spring boot项目改造添加分库分表依赖而来，具体可以看这一篇Spring boot整合mybatis generator实现自动生成代码

从shardingsphere官网https://shardingsphere.apache.org/index_zh.html简介来看好像改造分库分表相当简单，只需要引入maven依赖即可

可是它并没有告诉你不同spring-boot版本要跟不同版本的sharding配合使用，比如我的demo初始化的时候spring-boot-starter-parent是2.2.6，shardingsphere是下面这个版本

        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>

一启动就报如下异常

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-04-21 05:16:09.869 ERROR 11072 --- [           main] o.s.b.d.LoggingFailureAnalysisReporter   : 

***************************
APPLICATION FAILED TO START
***************************

Description:

The bean 'dataSource', defined in class path resource [io/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class], could not be registered. A bean with that name has already been defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class] and overriding is disabled.

Action:

Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true

Disconnected from the target VM, address: '127.0.0.1:65495', transport: 'socket'

Process finished with exit code 1

即使在yml文件添加了如下的配置也没用

main:
  allow-bean-definition-overriding: true

踩坑完毕，正文开始

org.springframework.boot使用2.0.3，sharding-jdbc使用3.0.0

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
        <relativePath/> 
    </parent>

        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>

pom文件结构如下：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>mybatis-generator</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis-generator</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>

        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.11</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>5.1.35</scope>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>
        <!--分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <!--自动生成代码插件-->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.2</version>
                <configuration>
                    <configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
                    <overwrite>true</overwrite>
                    <verbose>true</verbose>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

application.yml

server:
  port: 8080
  
sharding:
  jdbc:
    datasource:
      names: test
      # 数据库test
      test:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/test
        username: root
        password: 888888
    config:
      sharding:
        props:
          sql.show: true
        tables:
          user:  #user表名
            key-generator-column-name: id  #主键
            actual-data-nodes: test.user${0..1}    #数据节点,均匀分布
            table-strategy:  #分表策略
              inline: #行内表达式
                sharding-column: id
                algorithm-expression: user${id % 2}  #按模运算分配

mybatis:
  mapper-locations: classpath:mapping/*.xml
  type-aliases-package: com.example.mybatisgenerator.model

# 打印sql
logging:
  level:
    com.example.mybatisgenerator.dao : debug

需要注意的是，分库分表的主键id要在代码里面生成，一般可以用雪花算法生成主键id，可参考旧文提及的分布式ID生成方式之雪花算法 ，如果采用数据库自增策略的话那么不同的表它们的id肯定会冲突

建表语句

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行测试

结果如下：
user0跟user1都有数据，user表是空的，可以理解为user表是逻辑表，

以上这种是属于单库分表，当然也可以配置多个数据源，主要是yml文件上的写法稍有差异。