掌握MySQL分库分表（五）SpringBoot2+MybatisPlus整合Sharding-Jdbc水平分表实现

创建Java-Maven项目

添加依赖

<properties>
        <java.version>11</java.version>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
        <spring.boot.version>2.5.5</spring.boot.version>
        <mybatisplus.boot.starter.version>3.4.0</mybatisplus.boot.starter.version>
        <lombok.version>1.18.16</lombok.version>
        <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
        <junit.version>4.12</junit.version>
        <druid.version>1.1.16</druid.version>
        <!--跳过单元测试-->
        <skipTests>true</skipTests>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring.boot.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring.boot.version}</version>
            <scope>test</scope>
        </dependency>

        <!--mybatis plus和springboot整合-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.boot.starter.version}</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <!--<scope>provided</scope>-->
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-jdbc.version}</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring.boot.version}</version>
                <configuration>
                    <fork>true</fork>
                    <addResources>true</addResources>
                </configuration>
            </plugin>
        </plugins>
    </build>

创建数据库、表

分库分表需求 : 2库2表
数据库1：xdclass_shop_order_0
表1：product_order_0
表2：product_order_1

数据库2：xdclass_shop_order_1
表1：product_order_0
表2：product_order_1

SQL脚本(x->{0…1})

CREATE TABLE `product_order_x` (
 `id` bigint NOT NULL AUTO_INCREMENT,
 `out_trade_no` varchar(64) DEFAULT NULL COMMENT '订单唯⼀标识',
 `state` varchar(11) DEFAULT NULL COMMENT 'NEW 未⽀付订单,PAY已经⽀付订单,CANCEL超时取消订单',
 `create_time` datetime DEFAULT NULL COMMENT '订单⽣成时间',
 `pay_amount` decimal(16,2) DEFAULT NULL COMMENT '订单实际⽀付价格',
 `nickname` varchar(64) DEFAULT NULL COMMENT '昵称',
 `user_id` bigint DEFAULT NULL COMMENT '⽤户id',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

创建Java实体类

model层

@Data
@TableName("product_order")
@EqualsAndHashCode(callSuper = false)
public class ProductOrderDO {

    @TableId(value = "id",type = IdType.AUTO)
    private Long id;

    private String outTradeNo;

    private String state;

    private Date createTime;

    private Double payAmount;

    private String nickname;

    private Long userId;
}

mapper层

public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {
}

配置启动类

@MapperScan("net.xdclass.mapper")
@EnableTransactionManagement
@SpringBootApplication
public class DemoApplication {
    public static void main(String [] args){
        SpringApplication.run(DemoApplication.class,args);
    }
}

水平分表配置文件配置

spring.application.name=xdclass-sharding-jdbc
server.port=8080

# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true

# 数据源，几个数据库，就是几个数据源，可以任意起名
spring.shardingsphere.datasource.names=ds0,ds1

# 第一个数据库，控制台查询本机ip地址(cmd->ipconfig)
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://10.24.201.232:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root


# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://10.24.201.232:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root


# 指定product_order表的数据分布情况，配置数据节点,行表达式标识符使用 ${...} 或 $->{...}，
# 但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}

# 指定product_order表(逻辑表)的分片策略，分片策略包括【分片键和分片算法】
# 以user_id作为分片键
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=user_id

# 将user_id模除2作为分片策略
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{user_id % 2}

测试分库分表实现

测试类

@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
@Slf4j
public class DbTest {

    @Autowired
    private ProductOrderMapper productOrderMapper;

    @Test
    public void testSaveProductOrder(){

        for(int i=0; i<10;i++){
            ProductOrderDO productOrderDO = new ProductOrderDO();
            productOrderDO.setCreateTime(new Date());
            productOrderDO.setNickname("我是i="+i+"号");
            productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0,32));
            productOrderDO.setPayAmount(100.00);
            productOrderDO.setState("PAY");
            productOrderDO.setUserId(Long.valueOf(i+""));
            productOrderMapper.insert(productOrderDO);
        }
    }
}

运行结果

分析控制台SQL

与application.properties配置的相对应

逻辑SQL

没具体到哪个数据节点，先执行一条逻辑语句，把数据插入到逻辑表

真实SQL

具体到每个数据节点的SQL，经过一层路由，执行真实sql操作，将数据按照分片策略插入到相应表中

主键重复问题

分库分表带来的六个问题之一