ShardingJDBC的分库分表实践

写代码的强哥
3,233 阅读3分钟

前言

上篇文章讲了ShardingJDBC分库分表的原理，它是作为一个jar包整合到工程中，对JDBC进行增强。执行SQL时主要是通过解析引擎、改写引擎、执行引擎、排序引擎和归并引擎等对SQL进行执行和优化以及结果处理。我们来实际操作下，观察日志就可以明白大致的过程。

准备环境

在本地新建两个数据库ds0和ds1，然后在两个数据库执行建表语句

CREATE TABLE `t_order2021` (
  `id` bigint(32) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `cloumn` varchar(45) DEFAULT NULL,
  `day_date` char(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `t_order2022` (
  `id` bigint(32) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `cloumn` varchar(45) DEFAULT NULL,
  `day_date` char(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后使用IDEA创建一个SpringBoot工程，整合mybatis和ShardingJDBC（就是在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.5.1</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>shardingjdbc</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>shardingjdbc</name>
	<description>sharding jdbc demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
		<sharding-sphere.version>4.1.1</sharding-sphere.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

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

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

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<scope>provided</scope>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.12.0</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP -->
		<dependency>
			<groupId>com.zaxxer</groupId>
			<artifactId>HikariCP</artifactId>
			<version>4.0.3</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.25</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.4</version>
		</dependency>
	</dependencies>

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

</project>

核心配置

现在我们想要实现这种效果，按照t_order表的主键id的奇偶性进行分库，id为偶数的记录路由到ds0库，id为奇数的记录路由到ds1库。按照t_order表的day_date的数值是2021还是2022进行分表。话不多说，直接上配置

配置文件

server.port=10080

spring.shardingsphere.datasource.names=ds0,ds1

# 配置第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0
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.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

# 配置t_order表的分库策略

spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=id
# 自定义分库策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbc.config.MyDbPreciseShardingAlgorithm

# 配置t_order的分表策略
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{2021..2022}
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=day_date
# 自定义分表策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbc.config.MyTablePreciseShardingAlgorithm

# 添加t_order表的id生成策略
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

# mybatis配置
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.example.shardingjdbc.po

# 配置日志级别
logging.level.com.echo.shardingjdbc.dao=DEBUG

这是在application.properties的配置内容，如果使用yaml的配置方式，如下

tables:
  t_order:
    actualDataNodes: ds$->{0..1}.t_order$->{2021..2022}
    databaseStrategy:
      standard:
        preciseAlgorithmClassName: com.example.shardingjdbc.config.MyDbPreciseShardingAlgorithm
        shardingColumn: id
    keyGenerator:
      column: id
      type: SNOWFLAKE
    logicTable: t_order
    tableStrategy:
      standard:
        preciseAlgorithmClassName: com.example.shardingjdbc.config.MyTablePreciseShardingAlgorithm
        shardingColumn: day_date

自定义分库规则类/分表规则类


package com.example.shardingjdbc.config;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;
@Slf4j
public class MyDbPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long>{

    /**
     * 分片策略
     * @param availableTargetNames 所有的数据源
     * @param preciseShardingValue SQL执行时传入的分片值
     * @return 返回
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> preciseShardingValue) {
        //真实节点
        availableTargetNames.forEach(a -> log.info("actual node db:{}", a));

        log.info("logic table name:{}, route column:{}" , preciseShardingValue.getLogicTableName(), preciseShardingValue.getColumnName());

        //精确分片
        log.info("column name:{}", preciseShardingValue.getValue());

        for (String availableTargetName : availableTargetNames) {
            Long value = preciseShardingValue.getValue();
            if (("ds"+value%2).equals(availableTargetName)) {
                return availableTargetName;
            }
        }
        return null;
    }
}

package com.example.shardingjdbc.config;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

@Slf4j
public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<String>{

    /**
     * 自定义分表规则
     * @param availableTargetNames
     * @param preciseShardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> preciseShardingValue) {
        //真实节点
        availableTargetNames.forEach(a -> log.info("actual node table:{}", a));

        log.info("logic table name:{}, route column:{}", preciseShardingValue.getLogicTableName(), preciseShardingValue.getColumnName());
        //精确分片
        log.info("column value:{}", preciseShardingValue.getValue());

        for (String availableTargetName : availableTargetNames) {
            if (("t_order"+preciseShardingValue.getValue()).equals(availableTargetName)) {
                return availableTargetName;
            }
        }
        return null;
    }
}

测试验证

从下往上我们依次要定义mapper.xml，mapper接口，service类，controller类

<?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="com.example.shardingjdbc.dao.TOrderDao">
    <resultMap id="BaseResultMap" type="com.example.shardingjdbc.po.TOrder">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="user_id" jdbcType="INTEGER" property="userId"/>
        <result column="order_id" jdbcType="INTEGER" property="orderId"/>
        <result column="cloumn" jdbcType="VARCHAR" property="cloumn"/>
        <result column="day_date" jdbcType="CHAR" property="dayDate"/>
    </resultMap>
    <sql id="Base_Column_List">
        id, user_id, order_id, cloumn, day_date
    </sql>

    <insert id="insert" parameterType="com.example.shardingjdbc.po.TOrder">
        insert into t_order (user_id, order_id, cloumn, day_date) value (#{userId}, #{orderId}, #{cloumn}, #{dayDate})
    </insert>

    <select id="getList" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from t_order
    </select>

    <delete id="delete" parameterType="java.lang.Long">
        delete from t_order
        where id = #{id,jdbcType=BIGINT}
    </delete>

    <update id="update" parameterType="com.example.shardingjdbc.po.TOrder">
        update t_order
        set
        cloumn = #{cloumn,jdbcType=VARCHAR},
        order_id = #{orderId,jdbcType=INTEGER},
        user_id = #{userId,jdbcType=INTEGER}
        where id = #{id,jdbcType=BIGINT}
    </update>
</mapper>

package com.example.shardingjdbc.dao;

import com.example.shardingjdbc.po.TOrder;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface TOrderDao {

    void insert(TOrder tOrder);

    List<TOrder> getList();

    void delete(Integer id);

    int update(TOrder tOrder);
}

package com.example.shardingjdbc.service;

import com.example.shardingjdbc.dao.TOrderDao;
import com.example.shardingjdbc.po.TOrder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class TOrderService {

    @Autowired
    private TOrderDao tOrderDao;


    public void save(TOrder tOrder) {
        tOrderDao.insert(tOrder);
    }

    public void delete(Integer id) {
        tOrderDao.delete(id);
    }

    public int update(TOrder tOrder) {
        return tOrderDao.update(tOrder);
    }

    public List<TOrder> getList() {
        return tOrderDao.getList();
    }
}

package com.example.shardingjdbc.controller;

import com.example.shardingjdbc.po.TOrder;
import com.example.shardingjdbc.service.TOrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/order")
public class TOrderController {

    @Autowired
    private TOrderService tOrderService;

    @PostMapping("/save")
    public String save(@RequestBody TOrder tOrder) {
        tOrderService.save(tOrder);
        return "success";
    }

    @PostMapping("/delete")
    public String delete(@RequestParam(value = "id") Integer id) {
        tOrderService.delete(id);
        return "success";
    }

    @PostMapping("/update")
    public int update(@RequestBody TOrder tOrder) {
        return tOrderService.update(tOrder);
    }

    @GetMapping("/getList")
    public List<TOrder> getList() {
        return tOrderService.getList();
    }
}

然后我们启动项目，在postman调接口测试

image.png

看日志是插入到了ds0库的t_order2022表了

image.png

打开数据库看看

image.png

然后我们再插入一个

image.png

看日志是插入到了ds1库的t_order2021表 image.png

当调用查询接口时

image.png

在没有分片键的情况下，shardingJDBC会从各个数据源取数据

image.png

结语

使用shardingJDBC进行分库分表还是非常简单的，配置正确即可，本文是用了自定义的分片规则，需要自行实现自定义分片类，如果分片规则比较简单，那么就可以直接在配置里定义分片规则，那就更简单了。

avatar
文章
阅读
粉丝