【Sharding-JDBC】SpringBoot2 + Sharding-jdbc实现分库分表

一、背景

有一个产品库由于业务量增长，单数据库已经无法满足生产需要。为了提高产品系统性能，对产品库里的产品表和产品详情表做水平分库，水平分表。

1.1 表结构

1. 产品表（product_info）已经根据数据的访问频次做了垂直拆分，分为了产品信息表（product_info）和产品详情表(product_desc)。
2. 城市表（t_city）是配置表。

表结构：

1.2 水平分库水平分表情况

1. 产品库中的产品表（product_info）和产品描述表(product_desc)根据店铺id取模的方式水平分库，分为product_db_1和product_db_2两个库。
2. 产品表（product_info）和产品描述表(product_desc)又根据产品id取模的方式进行水平分表。product_info分为product_info_1和product_info_2，product_desc分为product_desc_1和product_desc_2。
3. 产品表（product_info）和产品描述表(product_desc)水平分库和水平分表的方式都相同，因此可以作为绑定表。
4. 城市表（t_city）是配置表，数据量小。是作为和产品表关联查询产品信息的表。作为广播表。

水平分库分表情况：

1.3 开发环境

组件	版本
windows	10
mysql	Mysql 8.0.25
springboot	2.4.10
sharding-jdbc-spring-boot-starter	4.0.0-RC1

二、代码实现

源代码地址

2.1 pom.xml

	<dependencies>
		<!-- web依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
		<!-- lombok插件 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        <!--pageHelper分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>
        <!-- 指定使用5.1.44版本驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>

        <!--druid数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--工具包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.6.5</version>
        </dependency>
        <!--sharding-jdbc-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
    </dependencies>

2.2 application.properties

server.port=8081
spring.profiles.active=fkfb
spring.main.allow-bean-definition-overriding=true
############## mybatis Configuration ###########
mybatis.mapper-locations=classpath:mapper/*Mapper.xml
mybatis.type-aliases-package=com.lh.boot.sharding.jdbc.entity
############## mybatis Configuration ###########
############ PageHelper Configuration ########
#数据库的方言
pagehelper.helper-dialect=mysql
#启用合理化，如果pageNum < 1会查询第一页，如果pageNum > pages会查询最后一页
pagehelper.reasonable=true
#是否将参数offset作为PageNum使用
pagehelper.offset-as-page-num=true
#是否进行count查询
pagehelper.row-bounds-with-count=true

2.3 application-fkfb.properties

# ======================================================
# sharding-jdbc公共配置
# ======================================================
datasource.type=com.alibaba.druid.pool.DruidDataSource
datasource.driver-class-name=com.mysql.cj.jdbc.Driver
datasource.filters=stat
datasource.maxActive=100
datasource.initialSize=40
datasource.maxWait=10000
datasource.minIdle=40
datasource.timeBetweenEvictionRunsMillis=60000
datasource.minEvictableIdleTimeMillis=300000
datasource.validationQuery=SELECT 1
datasource.testWhileIdle=true
datasource.testOnBorrow=false
datasource.testOnReturn=false
datasource.poolPreparedStatements=true
datasource.maxOpenPreparedStatements=20
# ======================================================
# sharding-jdbc配置，数据源名称
# ======================================================
spring.shardingsphere.datasource.names=m1,m2
# ======================================================
# 数据源m1配置信息
# ======================================================
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/product_db_1?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true&noAccessToProcedureBodies=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456
spring.shardingsphere.datasource.m1.type=${datasource.type}
spring.shardingsphere.datasource.m1.driver-class-name=${datasource.driver-class-name}
spring.shardingsphere.datasource.m1.filters=${datasource.filters}
spring.shardingsphere.datasource.m1.maxActive=${datasource.maxActive}
spring.shardingsphere.datasource.m1.initialSize=${datasource.initialSize}
spring.shardingsphere.datasource.m1.maxWait=${datasource.maxWait}
spring.shardingsphere.datasource.m1.minIdle=${datasource.minIdle}
spring.shardingsphere.datasource.m1.timeBetweenEvictionRunsMillis=${datasource.timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.m1.minEvictableIdleTimeMillis=${datasource.minEvictableIdleTimeMillis}
spring.shardingsphere.datasource.m1.validationQuery=${datasource.validationQuery}
spring.shardingsphere.datasource.m1.testWhileIdle=${datasource.testWhileIdle}
spring.shardingsphere.datasource.m1.testOnBorrow=${datasource.testOnBorrow}
spring.shardingsphere.datasource.m1.testOnReturn=${datasource.testOnReturn}
spring.shardingsphere.datasource.m1.poolPreparedStatements=${datasource.poolPreparedStatements}
spring.shardingsphere.datasource.m1.maxOpenPreparedStatements=${datasource.maxOpenPreparedStatements}
#======================================================
# 数据源m2配置信息
#======================================================
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/product_db_2?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true&noAccessToProcedureBodies=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456
spring.shardingsphere.datasource.m2.type=${datasource.type}
spring.shardingsphere.datasource.m2.driver-class-name=${datasource.driver-class-name}
spring.shardingsphere.datasource.m2.filters=${datasource.filters}
spring.shardingsphere.datasource.m2.maxActive=${datasource.maxActive}
spring.shardingsphere.datasource.m2.initialSize=${datasource.initialSize}
spring.shardingsphere.datasource.m2.maxWait=${datasource.maxWait}
spring.shardingsphere.datasource.m2.minIdle=${datasource.minIdle}
spring.shardingsphere.datasource.m2.timeBetweenEvictionRunsMillis=${datasource.timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.m2.minEvictableIdleTimeMillis=${datasource.minEvictableIdleTimeMillis}
spring.shardingsphere.datasource.m2.validationQuery=${datasource.validationQuery}
spring.shardingsphere.datasource.m2.testWhileIdle=${datasource.testWhileIdle}
spring.shardingsphere.datasource.m2.testOnBorrow=${datasource.testOnBorrow}
spring.shardingsphere.datasource.m2.testOnReturn=${datasource.testOnReturn}
spring.shardingsphere.datasource.m2.poolPreparedStatements=${datasource.poolPreparedStatements}
spring.shardingsphere.datasource.m2.maxOpenPreparedStatements=${datasource.maxOpenPreparedStatements}
#======================================================
# product_info 产品信息表分库分表配置
#======================================================
# 指定 product_info 表的数据分布情况，配置数据节点 m1.product_info_1,m1.product_info_2,m2.product_info_1,m2.product_info_2
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes=m$->{1..2}.product_info_$->{1..2}
# 指定 product_info 表的主键生成策略 (sharding-jdbc生成时打开，自己在代码里生成时注释掉)
#spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_id
#spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE
# 配置分库策略
spring.shardingsphere.sharding.tables.product_info.database-strategy.inline.sharding-column=store_id
spring.shardingsphere.sharding.tables.product_info.database-strategy.inline.algorithm-expression=m$->{store_id % 2 + 1}
# 指定product_info表的分片策略，分片策略包含分片键和分片算法
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column=product_id
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression=product_info_$->{product_id % 2 +1}
#======================================================
# product_desc 分库分表配置
#======================================================
# 指定 product_desc 表的数据分布情况，配置数据节点 m1.product_desc_1,m1.product_desc_2,m2.t_product_desc_1,m2.t_product_desc_2
spring.shardingsphere.sharding.tables.product_desc.actual-data-nodes=m$->{1..2}.product_desc_$->{1..2}
# 指定 product_desc 表的主键生成策略
#spring.shardingsphere.sharding.tables.product_desc.key-generator.column=product_id
#spring.shardingsphere.sharding.tables.product_desc.key-generator.type=SNOWFLAKE
# 配置分库策略
spring.shardingsphere.sharding.tables.product_desc.database-strategy.inline.sharding-column=store_id
spring.shardingsphere.sharding.tables.product_desc.database-strategy.inline.algorithm-expression=m$->{store_id % 2 + 1}
# 指定 product_desc 表的分片策略，分片策略包含分片键和分片算法
spring.shardingsphere.sharding.tables.product_desc.table-strategy.inline.sharding-column=product_id
spring.shardingsphere.sharding.tables.product_desc.table-strategy.inline.algorithm-expression=product_desc_$->{product_id % 2 +1}
#======================================================
# 绑定表配置
#======================================================
# 设置 product_info,product_desc 为绑定表
spring.shardingsphere.sharding.binding-tables[0]=product_info,product_desc
#======================================================
# 广播表配置
#======================================================
# 设置t_city为广播表(公共表)，每次更新操作会发送至所有数据源
spring.shardingsphere.sharding.broadcast-tables=t_city
#======================================================
# 日志配置
#======================================================
spring.shardingsphere.props.sql.show=true
logging.level.root=info
logging.level.org.springframework.web=info
logging.level.com.lh.boot.sharding.jdbc.mapper=debug
logging.level.druid.sql=debug

2.4 ProductInfoMapper.xml

<?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.lh.boot.sharding.jdbc.mapper.ProductInfoMapper">
    <resultMap id="BaseResultMap" type="com.lh.boot.sharding.jdbc.entity.ProductInfo">
        <constructor>
            <idArg column="product_id" javaType="java.lang.Long" jdbcType="BIGINT"/>
            <arg column="store_id" javaType="java.lang.Long" jdbcType="BIGINT"/>
            <arg column="price" javaType="java.math.BigDecimal" jdbcType="DECIMAL"/>
            <arg column="product_name" javaType="java.lang.String" jdbcType="VARCHAR"/>
            <arg column="city" javaType="java.lang.String" jdbcType="VARCHAR"/>
            <arg column="status" javaType="java.lang.String" jdbcType="VARCHAR"/>
        </constructor>
    </resultMap>
    <sql id="Base_Column_List">
        product_id, store_id, price, product_name, city, status
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from product_info
        where product_id = #{productId,jdbcType=BIGINT}
    </select>

    <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
        delete from product_info
        where product_id = #{productId,jdbcType=BIGINT}
    </delete>
    <insert id="insert" parameterType="productInfo">
        insert into product_info (product_id, store_id, price,
        product_name, city, status
        )
        values (#{productId,jdbcType=BIGINT}, #{storeId,jdbcType=BIGINT}, #{price,jdbcType=DECIMAL},
        #{productName,jdbcType=VARCHAR}, #{city,jdbcType=VARCHAR}, #{status,jdbcType=VARCHAR}
        )
    </insert>
    <insert id="insertSelective" parameterType="productInfo">
        insert into product_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="productId != null">
                product_id,
            </if>
            <if test="storeId != null">
                store_id,
            </if>
            <if test="price != null">
                price,
            </if>
            <if test="productName != null">
                product_name,
            </if>
            <if test="city != null">
                city,
            </if>
            <if test="status != null">
                status,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="productId != null">
                #{productId,jdbcType=BIGINT},
            </if>
            <if test="storeId != null">
                #{storeId,jdbcType=BIGINT},
            </if>
            <if test="price != null">
                #{price,jdbcType=DECIMAL},
            </if>
            <if test="productName != null">
                #{productName,jdbcType=VARCHAR},
            </if>
            <if test="city != null">
                #{city,jdbcType=VARCHAR},
            </if>
            <if test="status != null">
                #{status,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="productInfo">
        update product_info
        <set>
            <if test="storeId != null">
                store_id = #{storeId,jdbcType=BIGINT},
            </if>
            <if test="price != null">
                price = #{price,jdbcType=DECIMAL},
            </if>
            <if test="productName != null">
                product_name = #{productName,jdbcType=VARCHAR},
            </if>
            <if test="city != null">
                city = #{city,jdbcType=VARCHAR},
            </if>
            <if test="status != null">
                status = #{status,jdbcType=VARCHAR},
            </if>
        </set>
        where product_id = #{productId,jdbcType=BIGINT}
    </update>
    <update id="updateByPrimaryKey" parameterType="productInfo">
        update product_info
        set store_id = #{storeId,jdbcType=BIGINT},
        price = #{price,jdbcType=DECIMAL},
        product_name = #{productName,jdbcType=VARCHAR},
        city = #{city,jdbcType=VARCHAR},
        status = #{status,jdbcType=VARCHAR}
        where product_id = #{productId,jdbcType=BIGINT}
    </update>
    <resultMap type="com.lh.boot.sharding.jdbc.entity.vo.ProductInfoVO" id="ProductInfoResultMap">
        <id property="productId" column="product_id"/>
        <result property="storeId" column="store_id"/>
        <result property="price" column="price"/>
        <result property="productName" column="product_name"/>
        <result property="city" column="city"/>
        <result property="cityName" column="city_name"/>
        <result property="status" column="status"/>
        <result property="productSize" column="product_size"/>
        <result property="stock" column="stock"/>
        <result property="descInfo" column="desc_info"/>
    </resultMap>
    <select id="selectProductInfoVOList" resultMap="ProductInfoResultMap">
        select
            pri.product_id ,
            pri.product_name ,
            pri.price ,
            pri.status ,
            pri.store_id ,
            pri.city ,
            pd.desc_info ,
            pd.product_size ,
            pd.stock ,
            tc.city_name
        from
            product_info pri
        left join product_desc pd on
            pd.product_id = pri.product_id
        left join t_city tc on
            tc.city_code = pri.city
        order by pri.product_id
    </select>
</mapper>

2.5 ProductInfoMapper.java

package com.lh.boot.sharding.jdbc.mapper;


import com.lh.boot.sharding.jdbc.entity.ProductInfo;
import com.lh.boot.sharding.jdbc.entity.vo.ProductInfoVO;

import java.util.List;

public interface ProductInfoMapper {
    int deleteByPrimaryKey(Long productId);

    int insert(ProductInfo record);

    int insertSelective(ProductInfo record);

    ProductInfo selectByPrimaryKey(Long productId);

    int updateByPrimaryKeySelective(ProductInfo record);

    int updateByPrimaryKey(ProductInfo record);

    List<ProductInfoVO> selectProductInfoVOList();
}

2.6 ProductInfo.java

package com.lh.boot.sharding.jdbc.entity;

import java.math.BigDecimal;

public class ProductInfo {
    private Long productId;

    private Long storeId;

    private BigDecimal price;

    private String productName;

    private String city;

    private String status;

    public ProductInfo(Long productId, Long storeId, BigDecimal price, String productName, String city, String status) {
        this.productId = productId;
        this.storeId = storeId;
        this.price = price;
        this.productName = productName;
        this.city = city;
        this.status = status;
    }

    public ProductInfo() {
        super();
    }

    public Long getProductId() {
        return productId;
    }

    public void setProductId(Long productId) {
        this.productId = productId;
    }

    public Long getStoreId() {
        return storeId;
    }

    public void setStoreId(Long storeId) {
        this.storeId = storeId;
    }

    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName == null ? null : productName.trim();
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city == null ? null : city.trim();
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status == null ? null : status.trim();
    }
}

2.7 ProductInfoService.java

package com.lh.boot.sharding.jdbc.service;

import com.lh.boot.sharding.jdbc.entity.ProductInfo;
import com.lh.boot.sharding.jdbc.entity.Result;
import com.lh.boot.sharding.jdbc.entity.vo.ProductInfoVO;

import java.util.List;

/**
 * @author: StarrySky
 * @createDate: 2021/8/24 13:49
 * @version: 1.0
 * @description:
 */
public interface ProductInfoService {

    int deleteByPrimaryKey(Long productId);

    int insert(ProductInfo record);

    int insertSelective(ProductInfo record);

    ProductInfo selectByPrimaryKey(Long productId);

    int updateByPrimaryKeySelective(ProductInfo record);

    int updateByPrimaryKey(ProductInfo record);

    Result<List<ProductInfoVO>> selectProductInfoVOList(Integer pageSize, Integer pageIndex);
}

2.8 ProductInfoServiceImpl.java

package com.lh.boot.sharding.jdbc.service.impl;

import com.lh.boot.sharding.jdbc.entity.ProductInfo;
import com.lh.boot.sharding.jdbc.entity.Result;
import com.lh.boot.sharding.jdbc.entity.vo.ProductInfoVO;
import com.lh.boot.sharding.jdbc.mapper.ProductInfoMapper;
import com.lh.boot.sharding.jdbc.service.ProductInfoService;
import com.lh.boot.sharding.jdbc.utils.PageUtil;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author: StarrySky
 * @createDate: 2021/8/24 13:49
 * @version: 1.0
 * @description:
 */
@Slf4j
@Service
@AllArgsConstructor
public class ProductInfoServiceImpl implements ProductInfoService {
    private final ProductInfoMapper productInfoMapper;

    @Override
    public int deleteByPrimaryKey(Long productId) {
        return productInfoMapper.deleteByPrimaryKey(productId);
    }

    @Override
    public int insert(ProductInfo record) {
        return productInfoMapper.insert(record);
    }

    @Override
    public int insertSelective(ProductInfo record) {
        return productInfoMapper.insertSelective(record);
    }

    @Override
    public ProductInfo selectByPrimaryKey(Long productId) {
        return productInfoMapper.selectByPrimaryKey(productId);
    }

    @Override
    public int updateByPrimaryKeySelective(ProductInfo record) {
        return productInfoMapper.updateByPrimaryKeySelective(record);
    }

    @Override
    public int updateByPrimaryKey(ProductInfo record) {
        return productInfoMapper.updateByPrimaryKey(record);
    }

    @Override
    public Result<List<ProductInfoVO>> selectProductInfoVOList(Integer pageSize, Integer pageIndex) {
        return PageUtil.queryServiceResult(pageIndex, pageSize, productInfoMapper::selectProductInfoVOList);
    }
}

三、个人总结

• sharding-jdbc帮我们实现了从逻辑sql到真是sql的转换过程，使我们在开发中操作分库分表更方便简单。
• 全部基于配置即可完成分库分表管理，对代码侵入很小。
• 完全兼容mybatis和druid。