分表实践
原创大约 4 分钟
使用ShardingSphere实现将t_order的数据划分到两张表里。
首先创建三个数据库itechthink_order_0、itechthink_order_1和itechthink_order_2。
-- 创建数据库
> DROP DATABASE IF EXISTS itechthink_order_0;
> CREATE DATABASE IF NOT EXISTS itechthink_order_0 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
> DROP DATABASE IF EXISTS itechthink_order_1;
> CREATE DATABASE IF NOT EXISTS itechthink_order_1 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
> DROP DATABASE IF EXISTS itechthink_order_2;
> CREATE DATABASE IF NOT EXISTS itechthink_order_2 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;然后分别在每一个对应的数据库中创建对应的t_order_0表和t_order_1表。
-- 依次在`itechthink_order_0`、`itechthink_order_1`和`itechthink_order_2`中执行以下建表语句
> DROP TABLE IF EXISTS t_order_0;
> CREATE TABLE t_order_0 (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '订单编码',
userid BIGINT(20) DEFAULT NULL COMMENT '用户编码',
tradeno VARCHAR(64) DEFAULT NULL COMMENT '订单唯一标识',
state TINYINT(1) DEFAULT NULL COMMENT '订单状态',
money decimal(16, 2) DEFAULT NULL COMMENT '实际支付金额',
createtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '订单表0';
> DROP TABLE IF EXISTS t_order_1;
> CREATE TABLE t_order_1 (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '订单编码',
userid BIGINT(20) DEFAULT NULL COMMENT '用户编码',
tradeno VARCHAR(64) DEFAULT NULL COMMENT '订单唯一标识',
state TINYINT(1) DEFAULT NULL COMMENT '订单状态',
money decimal(16, 2) DEFAULT NULL COMMENT '实际支付金额',
createtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '订单表1';这样执行完成后，就得到了准备填充数据的3库6表。
首先是pom.xml文件。
<?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>com.itechthink</groupId>
<artifactId>mysql-sharding-jdbc</artifactId>
<version>1.0-SNAPSHOT</version>
<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>
<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>
<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.27</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</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>
</project>然后再创建实体类对象Order。
package com.itechthink.model;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.util.Date;
import java.util.Objects;
// 指定逻辑表名
@TableName("t_order")
public class Order {
// ID自增
@TableId(value = "id", type = IdType.AUTO)
private Long id;
private Long userid;
private String tradeno;
private Integer state;
private Double money;
private Date createtime;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getUserid() {
return userid;
}
public void setUserid(Long userid) {
this.userid = userid;
}
public String getTradeno() {
return tradeno;
}
public void setTradeno(String tradeno) {
this.tradeno = tradeno;
}
public Integer getState() {
return state;
}
public void setState(Integer state) {
this.state = state;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Order order = (Order) o;
return Objects.equals(id, order.id) && Objects.equals(userid, order.userid) && Objects.equals(tradeno, order.tradeno) && Objects.equals(state, order.state) && Objects.equals(money, order.money) && Objects.equals(createtime, order.createtime);
}
@Override
public int hashCode() {
return Objects.hash(id, userid, tradeno, state, money, createtime);
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", userid=" + userid +
", tradeno='" + tradeno + '\'' +
", state='" + state + '\'' +
", money=" + money +
", createtime=" + createtime +
'}';
}
}因为使用的是MyBatis Plus，所以还需要最基本的mapper类。
package com.itechthink.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itechthink.model.Order;
public interface OrderMapper extends BaseMapper<Order> {
}配置文件application.properties的内容如下。
spring.application.name=mysql-sharding-jdbc
server.port=8080
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true
# 数据源
spring.shardingsphere.datasource.names=ds0,ds1,ds2
# 第一个数据源
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://172.16.185.176:3306/itechthink_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
# 第二个数据源
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://172.16.185.176:3306/itechthink_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
# 第三个数据源
spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.jdbc-url=jdbc:mysql://172.16.185.176:3306/itechthink_order_2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123456
# 指定 t_order 表的数据节点
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order_$->{0..1},ds1.t_order_$->{0..1},ds2.t_order_$->{0..1}
# 指定 t_order 表的分片键和分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=userid
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{userid % 2}通过ShardingJDBCTest测试类来运行分库分表的功能测试。
package com.itechthink;
import com.itechthink.mapper.OrderMapper;
import com.itechthink.model.Order;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.Date;
import java.util.UUID;
@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingJDBCApplication.class)
public class ShardingJDBCTest {
@Autowired
private OrderMapper orderMapper;
@Test
public void testSaveOrder() {
for (int i = 0; i < 10; i++) {
Order order = new Order();
// 分片键
order.setUserid(Long.valueOf(i));
order.setTradeno(UUID.randomUUID().toString().substring(0, 32).replaceAll("-", ""));
order.setState(1);
order.setMoney(168.00);
order.setCreatetime(new Date());
orderMapper.insert(order);
}
}
}执行测试之后可以在itechthink_order_0、itechthink_order_1和itechthink_order_2这三个数据库中看到下面的事实。
t_order_0表和t_order_1表各插入了5条记录。t_order_0表中的userid都是偶数，也就是0、2、4、6、8。t_order_1表中的userid都是奇数，也就是1、3、5、7、9。三个数据库中所有
t_order表的id都相同，这肯定是不行的。
> SELECT * FROM t_order_0;
+----+--------+------------------------------+-------+--------+---------------------+
| id | userid | tradeno | state | money | createtime |
+----+--------+------------------------------+-------+--------+---------------------+
| 1 | 0 | a352be4bc32d463b886189ad5046 | 1 | 168.00 | 2023-06-07 20:12:13 |
| 2 | 2 | f6a157979b554da8be124ae13324 | 1 | 168.00 | 2023-06-07 20:12:14 |
| 3 | 4 | bce010fca07f4be2b7800e0c02df | 1 | 168.00 | 2023-06-07 20:12:14 |
| 4 | 6 | 2f43b36c330e4be1aa4d10727540 | 1 | 168.00 | 2023-06-07 20:12:14 |
| 5 | 8 | d1a8080592734163a17c9a5fef1a | 1 | 168.00 | 2023-06-07 20:12:14 |
+----+--------+------------------------------+-------+--------+---------------------+
5 rows in set (0.00 sec)
> SELECT * FROM t_order_1;
+----+--------+------------------------------+-------+--------+---------------------+
| id | userid | tradeno | state | money | createtime |
+----+--------+------------------------------+-------+--------+---------------------+
| 1 | 1 | de5bc98474bc4d9b998f3e4fdc45 | 1 | 168.00 | 2023-06-07 20:12:14 |
| 2 | 3 | cf664de4089848a1877c461d291f | 1 | 168.00 | 2023-06-07 20:12:14 |
| 3 | 5 | c15376dadeee4baaa9609866bbb8 | 1 | 168.00 | 2023-06-07 20:12:14 |
| 4 | 7 | 1c6787dd55a8476288649663b8fd | 1 | 168.00 | 2023-06-07 20:12:14 |
| 5 | 9 | f5e1fb4d8ede47699fe48e594e67 | 1 | 168.00 | 2023-06-07 20:12:14 |
+----+--------+------------------------------+-------+--------+---------------------+
5 rows in set (0.00 sec)清除测试数据。
> USE itechthink_order_0;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;
> USE itechthink_order_1;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;
> USE itechthink_order_2;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;感谢支持
更多内容，请移步《超级个体》。