Spring Boot+MyBatis+MySql+Sharding-JDBC实现分库分表学习Demo

Sharding-JDBC 简介

官网介绍：Sharding-JDBC是一个开源的分布式数据库中间件，它无需额外部署和依赖，完全兼容JDBC和各种ORM框架。Sharding-JDBC作为面向开发的微服务云原生基础类库，完整实现了分库分表、读写分离和分布式主键功能，并初步实现了柔性事务。
本文参考官方Demo采用Spring Boot+MyBatis+MySql+Sharding-JDBC实现分库分表示例。

建库、建表sql

-- 两个库 demo_ds_0、demo_ds_1, 包含两个表：t_order_0、t_order_1

-- 建库SQL如下：
DROP SCHEMA IF EXISTS demo_ds_0;
DROP SCHEMA IF EXISTS demo_ds_1;
CREATE SCHEMA IF NOT EXISTS demo_ds_0;
CREATE SCHEMA IF NOT EXISTS demo_ds_1;

-- 建表SQL如下：
CREATE TABLE IF NOT EXISTS t_order_0 (order_id BIGINT AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));  
CREATE TABLE IF NOT EXISTS t_order_item_0 (order_item_id BIGINT AUTO_INCREMENT, order_id BIGINT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));    
  
CREATE TABLE IF NOT EXISTS t_order_1 (order_id BIGINT AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));  
CREATE TABLE IF NOT EXISTS t_order_item_1 (order_item_id BIGINT AUTO_INCREMENT, order_id BIGINT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));

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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>io.shardingjdbc.example.spring.boot.mybatis</groupId>
	<artifactId>sharding-jdbc-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>sharding-jdbc-demo</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.13.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

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

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
			<version>1.5.7.RELEASE</version>
		</dependency>

		<dependency>
			<groupId>io.shardingjdbc</groupId>
			<artifactId>sharding-jdbc-core-spring-boot-starter</artifactId>
			<version>2.0.3</version>
		</dependency>

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.0</version>
		</dependency>

		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>

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

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
	
</project>

分库分表最主要有几个配置：
1.有多少个数据源
2.每张表的逻辑表名和所有物理表名
3.用什么列进行分库以及分库算法
4.用什么列进行分表以及分表算法
分为两个库：demo_ds_0、demo_ds_1
每个库都包含四个表：to_order_0, t_order_1, t_order_item_0, t_order_item_1
使用user_id作为分库列；
使用order_id作为分表列；

配置文件 application.properties

sharding.jdbc.datasource.names=ds_0,ds_1

sharding.jdbc.datasource.ds_0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_0.url=jdbc:mysql://localhost:3306/demo_ds_0
sharding.jdbc.datasource.ds_0.username=root
sharding.jdbc.datasource.ds_0.password=123456

sharding.jdbc.datasource.ds_1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_1.url=jdbc:mysql://localhost:3306/demo_ds_1
sharding.jdbc.datasource.ds_1.username=root
sharding.jdbc.datasource.ds_1.password=123456

sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds_${user_id % 2}

sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds_${0..1}.t_order_${0..1}
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_${order_id % 2}
sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_id
sharding.jdbc.config.sharding.tables.t_order_item.actual-data-nodes=ds_${0..1}.t_order_item_${0..1}
sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_${order_id % 2}
sharding.jdbc.config.sharding.tables.t_order_item.key-generator-column-name=order_item_id


mybatis.config-location=classpath:META-INF/mybatis-config.xml

logging.level.org.springframework=WARN
logging.level.com.spring.ibatis.UserMapper=DEBUG
logging.file=logs/spring-boot-logging.log

上面配置的分库分表规则如下：
demo_ds_0
├── t_order_0 user_id为偶数 order_id为偶数
├── t_order_1 user_id为偶数 order_id为奇数
├── t_order_item_0 user_id为偶数 order_id为偶数
└── t_order_item_1 user_id为偶数 order_id为奇数
demo_ds_1
├── t_order_0 user_id为奇数 order_id为偶数
├── t_order_1 user_id为奇数 order_id为奇数
├── t_order_item_0 user_id为奇数 order_id为偶数
└── t_order_item_1 user_id为奇数 order_id为奇数

Application

@SpringBootApplication
public class ShardingJdbcDemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(ShardingJdbcDemoApplication.class, args);
	}
}

Entity实体类定义

package io.shardingjdbc.example.spring.boot.mybatis.entity;

public final class Order {
    
    private long orderId;
    
    private int userId;
    
    private String status;
    
    public long getOrderId() {
        return orderId;
    }
    
    public void setOrderId(final long orderId) {
        this.orderId = orderId;
    }
    
    public int getUserId() {
        return userId;
    }
    
    public void setUserId(final int userId) {
        this.userId = userId;
    }
    
    public String getStatus() {
        return status;
    }
    
    public void setStatus(final String status) {
        this.status = status;
    }
    
    @Override
    public String toString() {
        return String.format("order_id: %s, user_id: %s, status: %s", orderId, userId, status);
    }
}

package io.shardingjdbc.example.spring.boot.mybatis.entity;

public final class OrderItem {
    
    private long orderItemId;
    
    private long orderId;
    
    private int userId;
    
    private String status;
    
    public long getOrderItemId() {
        return orderItemId;
    }
    
    public void setOrderItemId(final long orderItemId) {
        this.orderItemId = orderItemId;
    }
    
    public long getOrderId() {
        return orderId;
    }
    
    public void setOrderId(final long orderId) {
        this.orderId = orderId;
    }
    
    public int getUserId() {
        return userId;
    }
    
    public void setUserId(final int userId) {
        this.userId = userId;
    }
    
    public String getStatus() {
        return status;
    }
    
    public void setStatus(final String status) {
        this.status = status;
    }
    
    @Override
    public String toString() {
        return String.format("item_id:%s, order_id: %s, user_id: %s, status: %s", orderItemId, orderId, userId, status);
    }
}

Mapper层

package io.shardingjdbc.example.spring.boot.mybatis.repository;

import io.shardingjdbc.example.spring.boot.mybatis.entity.Order;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface OrderRepository {
    
    Long insert(Order model);
    
}

package io.shardingjdbc.example.spring.boot.mybatis.repository;

import io.shardingjdbc.example.spring.boot.mybatis.entity.OrderItem;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface OrderItemRepository {
    
    Long insert(OrderItem model);
 
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="io.shardingjdbc.example.spring.boot.mybatis.repository.OrderRepository">
   
    <insert id="insert" useGeneratedKeys="true" keyProperty="orderId">
        INSERT INTO t_order (
          user_id, status
        )
        VALUES (
        #{userId,jdbcType=INTEGER},
        #{status,jdbcType=VARCHAR}
        )
    </insert>
    
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="io.shardingjdbc.example.spring.boot.mybatis.repository.OrderItemRepository">
   
    <insert id="insert" useGeneratedKeys="true" keyProperty="orderItemId">
        INSERT INTO t_order_item (
          order_id, user_id, status
        )
        VALUES (
        #{orderId,jdbcType=INTEGER},
        #{userId,jdbcType=INTEGER},
        #{status,jdbcType=VARCHAR}
        )
    </insert>
    
</mapper>

MyBatis 配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <package name="io.shardingjdbc.example.spring.boot.mybatis.entity"/>
    </typeAliases>
    <mappers>
        <mapper resource="META-INF/mappers/OrderMapper.xml"/>
        <mapper resource="META-INF/mappers/OrderItemMapper.xml"/>
    </mappers>
</configuration>

service 层

package io.shardingjdbc.example.spring.boot.mybatis.service;

import io.shardingjdbc.example.spring.boot.mybatis.entity.Order;
import io.shardingjdbc.example.spring.boot.mybatis.entity.OrderItem;
import io.shardingjdbc.example.spring.boot.mybatis.repository.OrderItemRepository;
import io.shardingjdbc.example.spring.boot.mybatis.repository.OrderRepository;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

@Service
public class DemoService {
    
    @Resource
    private OrderRepository orderRepository;
    
    @Resource
    private OrderItemRepository orderItemRepository;

    public String insert(Integer userId) {
        Order order = new Order();
        order.setUserId(userId);
        order.setStatus("INSERT_TEST");
        orderRepository.insert(order);

        long orderId = order.getOrderId();
        OrderItem item = new OrderItem();
        item.setOrderId(orderId);
        item.setUserId(userId);
        item.setStatus("INSERT_TEST");
        orderItemRepository.insert(item);

        return orderId + "|" + item.getOrderItemId();
    }
}

返回生成的order_id和order_item_id

Controller层

@RestController
@RequestMapping(value = "/order")
public class TestController {

    @Autowired
    private DemoService demoService;

    @PostMapping
    public String insertOrder(Integer userId) {
        return demoService.insert(userId);
    }
}

使用Postman进行测试

启动服务，使用Postman进行测试：

userId从1到9依次插入9条记录，执行结果如下：

可以看到数据是按照规定的规则写到不同的库和表里了：

demo_ds_0
├── t_order_0 user_id为偶数 order_id为偶数
├── t_order_1 user_id为偶数 order_id为奇数
├── t_order_item_0 user_id为偶数 order_id为偶数
└── t_order_item_1 user_id为偶数 order_id为奇数
demo_ds_1
├── t_order_0 user_id为奇数 order_id为偶数
├── t_order_1 user_id为奇数 order_id为奇数
├── t_order_item_0 user_id为奇数 order_id为偶数
└── t_order_item_1 user_id为奇数 order_id为奇数

这里有个小问题，就是两个库的 t_order_1,t_order_item_1 表中均没有数据，数据分配并不均匀。这是因为Sharding-JDBC采用的默认的分布式自增主键是使用的snowflake算法实现的，说明可以参考sharding-jdbc官网：

http://shardingjdbc.io/docs_cn/01-start/faq/

关于这个问题，考虑修改分表策略为hash分片，或者稍微改造下snowflake 随机初始？ 待研究完善…