目录
一、背景
有一个产品库由于业务量增长,单数据库已经无法满足生产需要。为了提高产品系统性能,对产品库里的产品表和产品详情表做水平分库,水平分表。
1.1 表结构
- 产品表(product_info)已经根据数据的访问频次做了垂直拆分,分为了产品信息表(product_info)和产品详情表(product_desc)。
- 城市表(t_city)是配置表。
表结构:
1.2 水平分库水平分表情况
- 产品库中的产品表(product_info)和产品描述表(product_desc)根据店铺id取模的方式水平分库,分为product_db_1和product_db_2两个库。
- 产品表(product_info)和产品描述表(product_desc)又根据产品id取模的方式进行水平分表。product_info分为product_info_1和product_info_2,product_desc分为product_desc_1和product_desc_2。
- 产品表(product_info)和产品描述表(product_desc)水平分库和水平分表的方式都相同,因此可以作为绑定表。
- 城市表(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。