Sharding-JDBC + MyBatisPlus 实现分库分表
前言
随着业务数据的增加，单个数据库服务器已经难以满足业务需要，必须考虑数据库集群的方式来提升性能。高性能数据库集群的第一种方式是“读写分离”，第二种方式是“数据库分片”。
Sharding-jdbc 作为成熟的分库分表技术框架，在国内应用广泛。本文将介绍基于 Sharding-JDBC + MyBatisPlus 的分库分表。
准备数据库
在一个或两个 MySQL 服务上创建两个数据库（test），执行下面的脚本创建三个订单表（t_order、t_order_0、t_order_1）。
t_order 只是为了 MybatisPlus 逆向生成 CRUD 代码（Mapper、Service），生成完代码可以删掉。
t_order_0 和 t_order_1 是保存订单数据的两个分表。
-- test.t_order_0 definition
CREATE TABLE `t_order_0` (
`order_id` bigint NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`description` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=986675951494496257 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `t_order_1` (
`order_id` bigint NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`description` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=986675951494496257 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
引入 Maven 依赖
Maven 核心依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.1</version>
</dependency>
demo 工程使用到的 Maven 依赖
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.phixlin</groupId>
<artifactId>sharding-sphere-demo</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.1</version>
<relativePath/>
</parent>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
<version>8.3.0</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
SpringBoot 配置文件
spring:
shardingsphere:
datasource:
# 数据源
names: m0,m1
m0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
m1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test-node?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
rules:
sharding:
# 分片算法
sharding-algorithms:
# t-order 数据库
t-order-database-inline:
# 分片算法类型
type: INLINE
props:
# 分片算法的行表达式
algorithm-expression: m$->{ user_id % 2}
# t-order 表
t-order-inline:
type: INLINE
props:
algorithm-expression: t_order_$->{order_id % 2}
# 主键生成策略
key-generators:
t-order-id-snowflake:
type: SNOWFLAKE
props:
worker-id: 001
tables:
t_order:
# 由数据源名 + 表名组成（参考 Inline 语法规则）
actualDataNodes: m$->{0..1}.t_order_$->{0..1}
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: t-order-database-inline
# 表策略
tableStrategy:
# 用于单分片键的标准分片场景
standard:
# 分片列名称
shardingColumn: order_id
# 分片算法名称
shardingAlgorithmName: t-order-inline
# 主键策略
keyGenerateStrategy:
column: order_id
keyGeneratorName: t-order-id-snowflake
props:
sql-show: true
实体类
package cn.phixlin.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import lombok.*;
@Data
@ToString
@Builder
@TableName("t_order")
public class Order implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "order_id", type = IdType.AUTO)
private Long orderId;
private Integer userId;
private String description;
}
测试接口
package cn.phixlin;
import cn.phixlin.entity.Order;
import cn.phixlin.service.impl.OrderServiceImpl;
import lombok.SneakyThrows;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
@SpringBootTest
public class SampleTest {
@Autowired
private OrderServiceImpl orderService;
@Test
public void testAutoId() {
// 自动生成 order_id
Order order = Order.builder().userId(664).description("分库测试").build();
System.out.println(order);
orderService.save(order);
}
@Test
public void testSplit() {
Order order = Order.builder().orderId(888L).userId(666).description("分库测试").build();
System.out.println(order);
orderService.saveOrUpdate(order);
}
}
License:
CC BY 4.0