sharding-jdbc分片方式介绍

像素化创新

已于 2024-02-10 22:44:13 修改

阅读量1.1k

点赞数 24

文章标签： java 数据库大数据

于 2024-02-10 15:31:45 首次发布

本文链接：https://blog.csdn.net/weixin_46520767/article/details/136090963

版权

sharding-jdbc实现数据分片有4种策略：

inline模式
- 使用最简单，开发成本比较低；
- 只能使用单个字段作为分片键；
- 基于行表达式定义分片规则；

通过groovy表达式来表示分库分表的策略；
db0
├── t_order0
└── t_order1
db1
├── t_order0
└── t_order1
表达式：db${0..1}.t_order${0..1}
t_order${orderId % 2}

standard标准分片模式
- 用户可通过代码自定义复杂的分片策略；
- 同样只能使用单个字段作为分片键；
complex复合分片模式
- 用于多分片键的复合分片策略（多片键）
Hint强制分片模式
- 不指定片键，通过代码动态指定路由规则
- 强制分片策略（强制路由）

基于inline模式实现水平分表

order_db_1
├── t_order_1
└── t_order_2

#创建数据库
CREATE DATABASE `order_db_1` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
#建表
USE order_db_1;
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
    `order_id` BIGINT (20) NOT NULL COMMENT '订单id',
    `price` DECIMAL (10, 2) NOT NULL COMMENT '订单价格',
    `user_id` BIGINT (20) NOT NULL COMMENT '下单用户id',
    `status` VARCHAR (50) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
 PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
    `order_id` BIGINT (20) NOT NULL COMMENT '订单id',
    `price` DECIMAL (10, 2) NOT NULL COMMENT '订单价格',
    `user_id` BIGINT (20) NOT NULL COMMENT '下单用户id',
    `status` VARCHAR (50) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
 PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

工程集成sharding-jdbc

<dependencies>
  <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.4</version>
  </dependency>
  <!--引入sharding依赖-->
  <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
  </dependency>

  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
  </dependency>
</dependencies>

1) 主配置 定义sharding相关配置

#下面这些内容是为了让MyBatis映射
#指定Mybatis的Mapper文件
mybatis.mapper-locations=classpath:mappers/*xml
#指定Mybatis的实体目录
mybatis.type-aliases-package=com.itheima.sharding.entity
## 数据库驱动：
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
## 数据库连接地址
#spring.datasource.url=jdbc:mysql://192.168.175.129:3306/order_db_1?serverTimezone=Asia/Shanghai&useSSL=false
## 数据库用户名&密码：
#spring.datasource.username=root
#spring.datasource.password=123456

#spring.profiles.active=test1
spring.profiles.active=test8

2) 环境application-test1.properties配置

#1.配置数据源
#数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=ds1
#数据库连接池类名称
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.175.129:3306/order_db_1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds1.username=root
#数据库密码
spring.shardingsphere.datasource.ds1.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=


#2.配置指定表的数据结点
#由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式。
# 缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds1.t_order_${1..2}
#3.指定库的分片策略
#分片列名称
#spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.inline.sharding-column=
##分片算法行表达式，需符合groovy语法
#spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.inline.algorithm-expression=

#4.指定表的分片策略根据分片条件找到对应真实表
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id

spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_${order_id % 2 +1}

3) 定义mapper接口和xml

package com.itheima.shardingjdbc.mapper;

import com.itheima.shardingjdbc.pojo.TOrder;
import org.apache.ibatis.annotations.Mapper;

/**
 * @Entity com.itheima.shardingjdbc.pojo.TOrder
 */
@Mapper
public interface TOrderMapper {

    int deleteByPrimaryKey(Long id);

    int insert(TOrder record);

    int insertSelective(TOrder record);

    TOrder selectByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(TOrder record);

    int updateByPrimaryKey(TOrder record);

}

定义与接口绑定的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.itheima.shardingjdbc.mapper.TOrderMapper">

    <resultMap id="BaseResultMap" type="com.itheima.shardingjdbc.pojo.TOrder">
            <id property="orderId" column="order_id" jdbcType="BIGINT"/>
            <result property="price" column="price" jdbcType="DECIMAL"/>
            <result property="userId" column="user_id" jdbcType="BIGINT"/>
            <result property="status" column="status" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        order_id,price,user_id,
        status
    </sql>

    <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from t_order
        where  order_id = #{orderId,jdbcType=BIGINT} 
    </select>

    <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
        delete from t_order
        where  order_id = #{orderId,jdbcType=BIGINT} 
    </delete>
    <insert id="insert" parameterType="com.itheima.shardingjdbc.pojo.TOrder">
        insert into t_order
        ( order_id,price,user_id
        ,status)
        values (#{orderId,jdbcType=BIGINT},#{price,jdbcType=DECIMAL},#{userId,jdbcType=BIGINT}
        ,#{status,jdbcType=VARCHAR})
    </insert>
    <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.itheima.shardingjdbc.pojo.TOrder" useGeneratedKeys="true">
        insert into t_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="orderId != null">orderId,</if>
                <if test="price != null">price,</if>
                <if test="userId != null">userId,</if>
                <if test="status != null">status,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="orderId != null">order_id = #{orderId,jdbcType=BIGINT},</if>
                <if test="price != null">price = #{price,jdbcType=DECIMAL},</if>
                <if test="userId != null">user_id = #{userId,jdbcType=BIGINT},</if>
                <if test="status != null">status = #{status,jdbcType=VARCHAR},</if>
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="com.itheima.shardingjdbc.pojo.TOrder">
        update t_order
        <set>
                <if test="price != null">
                    price = #{price,jdbcType=DECIMAL},
                </if>
                <if test="userId != null">
                    user_id = #{userId,jdbcType=BIGINT},
                </if>
                <if test="status != null">
                    status = #{status,jdbcType=VARCHAR},
                </if>
        </set>
        where   order_id = #{orderId,jdbcType=BIGINT} 
    </update>
    <update id="updateByPrimaryKey" parameterType="com.itheima.shardingjdbc.pojo.TOrder">
        update t_order
        set 
            price =  #{price,jdbcType=DECIMAL},
            user_id =  #{userId,jdbcType=BIGINT},
            status =  #{status,jdbcType=VARCHAR}
        where   order_id = #{orderId,jdbcType=BIGINT} 
    </update>
</mapper>

注意：xml中使用的逻辑表t_order，而不是t_order1、t_order2

4) 测试

@SpringBootTest
public class TestAll {

    @Autowired
    private TOrderMapper tOrderMapper;
    @Test
   public void test1() {
        int orderId=0;
        Random random = new Random();
        for (int i = 0; i < 20; i++) {
            //保证随机生成奇数或者偶数
            orderId+=random.nextInt(2)+1;
            TOrder order = TOrder.builder().orderId(Long.valueOf(orderId))
                    .userId(Long.valueOf(i))
                    .status("1")
                    .price(new BigDecimal(300))
                    .build();
            tOrderMapper.insert(order);
        }
    }
}

sharding-jdbc广播表

广播表介绍

广播表属于数据库中数据量较小和变动较少，且存在高频联合查询的表，比如：数据字典表等属于此广播表。
可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。

order_db_1
├── t_order_1
└── t_order_2
└── t_dict
order_db_2
├── t_order_1
└── t_order_2
└── t_dict

#在数据库 user_db、order_db_1、order_db_2中均要建表
CREATE TABLE `t_dict` (
 `dict_id` BIGINT (20) NOT NULL COMMENT '字典id',
 `type` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典类型',
 `code` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典编码',
 `value` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典值',
 PRIMARY KEY (`dict_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

将配置表更改

#1.配置数据源
#数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=ds1,ds2
#数据库连接池类名称
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.175.129:3306/order_db_1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds1.username=root
#数据库密码
spring.shardingsphere.datasource.ds1.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=


#数据库连接池类名称
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.175.129:3306/order_db_2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds2.username=root
#数据库密码
spring.shardingsphere.datasource.ds2.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=
#----------------------------------------------------------------------------------------------------------------

#2.配置指定表的数据结点
#由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式。
# 缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds${1..2}.t_order_${1..2}

#----------------------------------------------------------------------------------------------------------------
#3.指定库的分片策略
#分片列名称   使用uer_id 防止数据倾斜（如果是order_id 那只会有两个表有数据）
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
#分片算法行表达式，需符合groovy语法
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds${user_id%2+1}
#----------------------------------------------------------------------------------------------------------------
#4.指定表的分片策略根据分片条件找到对应真实表
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id

spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_${order_id % 2 +1}
#----------------------------------------------------------------------------------------------------------------
#是否开启SQL显示
spring.shardingsphere.props.sql.show=true

#----------------------------------------------------------------------------------------------------------------
# 指定t_dict为公共表，多个公共表以逗号间隔
spring.shardingsphere.sharding.broadcast‐tables=t_dict

测试广播表

    @Autowired
    private TDictMapper tDictMapper;
    
	@Test
    public void commonTable(){
        TDict build = TDict.builder().dictId(1l).code("666").type("1").value("888")
                .build();
        tDictMapper.insert(build);
    }

基于inline模式实现垂直分库

表结构

order_db_1
├── t_order_1
└── t_order_2
└── t_dict
order_db_2
├── t_order_1
└── t_order_2
└── t_dict
user_db
└── t_user

#创建数据库
CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
#建表
USE user_db;
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
 `user_id` BIGINT (20) NOT NULL COMMENT '用户id',
 `fullname` VARCHAR (255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
 `user_type` CHAR (1) DEFAULT NULL COMMENT '用户类型',
 PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

将配置表更改

#1.配置数据源
#数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=ds1,ds2,udb
#数据库连接池类名称
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.175.129:3306/order_db_1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds1.username=root
#数据库密码
spring.shardingsphere.datasource.ds1.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=


#数据库连接池类名称
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.175.129:3306/order_db_2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds2.username=root
#数据库密码
spring.shardingsphere.datasource.ds2.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=

#数据库连接池类名称
spring.shardingsphere.datasource.udb.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.udb.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.udb.url=jdbc:mysql://192.168.175.129:3306/user_db?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.udb.username=root
#数据库密码
spring.shardingsphere.datasource.udb.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=
#----------------------------------------------------------------------------------------------------------------

#2.配置指定表的数据结点
#由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式。
# 缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds${1..2}.t_order_${1..2}
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=udb.t_user
#----------------------------------------------------------------------------------------------------------------
#3.指定库的分片策略
#分片列名称   使用uer_id 防止数据倾斜（如果是order_id 那只会有两个表有数据）
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
#分片算法行表达式，需符合groovy语法
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds${user_id%2+1}
#----------------------------------------------------------------------------------------------------------------
#4.指定表的分片策略根据分片条件找到对应真实表
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id

spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_${order_id % 2 +1}
#----------------------------------------------------------------------------------------------------------------
#是否开启SQL显示
spring.shardingsphere.props.sql.show=true

#----------------------------------------------------------------------------------------------------------------
# 指定t_dict为公共表，多个公共表以逗号间隔
spring.shardingsphere.sharding.broadcast‐tables=t_dict

测试

    @Test
    public void test04(){
        TUser user = TUser.builder().userId(133l).userType("1")
                .fullname("laozhang").build();
        tUserMapper.insert(user);
    }

默认数据源模式

如果没有配置分片策略，则可指定默认访问的数据源，也就是说只需指定数据源，无需指定数据节点、库和表的分片策略也无需执行；

如果在进行相关操作时，发现逻辑表没有对应的数据节点、库表的分片配置，则走默认指定的数据源；

-- 构建数据
create database default_db character set utf8;
use default_db;
-- 构建表
create table tb_log (
    id bigint primary key ,
    info varchar(30)
);

#1.配置数据源
#数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=defdb
#数据库连接池类名称
spring.shardingsphere.datasource.defdb.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.defdb.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.defdb.url=jdbc:mysql://192.168.175.129:3306/default_db?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.defdb.username=root
#数据库密码
spring.shardingsphere.datasource.defdb.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=


#配置默认数据源 当大量的表值存在单个库中 且库内没有水平分表处理 可使用默认数据源
#未配置分片规则的表将通过默认数据源定位
spring.shardingsphere.sharding.default-data-source-name=defdb

测试

	/**
     * 测试默认数据源
     *  对于没有做分片处理的操作，则会直接访问默认数据源处理
     */
    @Test
    public void test5(){
        TbLog log = TbLog.builder().id(1l).info("这是一个测试").build();
        tbLogMapper.insert(log);
    }

inline模式小结

优点：
- 配置简单，开发成本低；
- 便于理解和维护；
缺点：
- 复杂的分片策略支持不友好；
- 对范围查询支持不太友好，动辄全节点查询，查询效率低下；

Standard模式实现分库分表

标准模式分片API介绍

# 用于单分片键的标准分片场景
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.standard.sharding-column= # 分片列名称
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.standard.precise-algorithm-class-name= # 精确分片算法类名称，用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.standard.range-algorithm-class-name= # 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器

PreciseShardingAlgorithm ：精准查询库或者表（sql使用使用了= 、in）
RangeShardingAlgorithm ：范围查询库或者表（between and 、 > 、 <）
所以，使用sharding-jdbc时尽量让分片键去查询，且遵循使用规范；

标准分片模式实现

定义标准分库策略

package com.itheima.sharding.alg;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;
import java.util.Optional;

/**
 * ClassName: MyPreciseShardingAlgorithmDb
 * Package: com.itheima.sharding.alg
 * Description:
 *
 * @Author R
 * @Create 2024/2/10 14:23
 * @Version 1.0
 */
public class MyPreciseShardingAlgorithmDb implements PreciseShardingAlgorithm<Long> {
    /**
     *
     * @param dsNames  所有数据源的名称集合
     * @param shardingValue 片键信息的封装
     * @return 当前数据库查询名称的数据库集合
     */
    @Override
    public String doSharding(Collection<String> dsNames, PreciseShardingValue<Long> shardingValue) {
        //获取数据库的分片键名称：user_id
        String dbColumnName = shardingValue.getColumnName();
        //获取此时查询时的片键值
        Long value = shardingValue.getValue();
        //获取逻辑表名称 t_user
        String logicTableName = shardingValue.getLogicTableName();
        Optional<String> result = dsNames.stream().filter(dbName -> dbName.endsWith((value % 2 + 1) + "")).findFirst();
        if(result.isPresent()){
            return result.get();
        }
        return null;
    }
}

package com.itheima.sharding.alg;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Collection;

/**
 * ClassName: MyRangeShardingAlgorithmDb
 * Package: com.itheima.sharding.alg
 * Description:
 *
 * @Author R
 * @Create 2024/2/10 14:30
 * @Version 1.0
 */
public class MyRangeShardingAlgorithmDb implements RangeShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(Collection<String> dsNames, RangeShardingValue<Long> shardingValue) {
        //获取分片键名称
        String columnName = shardingValue.getColumnName();
        //获取逻辑表
        String logicTableName = shardingValue.getLogicTableName();
        //对范围数据的封装
        Range<Long> valueRange = shardingValue.getValueRange();
        //判断是否有范围查询的起始值
        if (valueRange.hasLowerBound()) {
            //获取起始值
            Long start = valueRange.lowerEndpoint();//10
        }
        //判断是否有上限值
        if (valueRange.hasUpperBound()) {
            Long end = valueRange.upperEndpoint();
        }
        return dsNames;
    }
}

配置标准分库：

#1.配置数据源
#数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=ds1,ds2
#数据库连接池类名称
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.175.129:3306/order_db_1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds1.username=root
#数据库密码
spring.shardingsphere.datasource.ds1.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=


#数据库连接池类名称
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.175.129:3306/order_db_2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds2.username=root
#数据库密码
spring.shardingsphere.datasource.ds2.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=
#-----------------------------------------------------------------------------------------------------------
# 配置数据节点：datanode,数据源.表 t_order是逻辑表，属于t_order_1和t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds${1..2}.t_order_${1..2}

#-----------------------------------------------------------------------------------------------------------
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=user_id
#精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.itheima.sharding.alg.MyPreciseShardingAlgorithmDb
#范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.range-algorithm-class-name=com.itheima.sharding.alg.MyRangeShardingAlgorithmDb

#-----------------------------------------------------------------------------------------------------------
#分片列名称
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_${order_id % 2 +1}
#-----------------------------------------------------------------------------------------------------------

# 是否开启 SQL 显示，默认值: false
spring.shardingsphere.props.sql.show=true

    /**
     * @Description 测试数据库标准精准查询=
     */
    @Test
    public void test2(){
        List<TOrder> tOrder = tOrderMapper.findByUserId(18l);
        System.out.println(tOrder);
    }

    /**
     * @Description 测试标准查询：范围匹配 between
     */
    @Test
    public void test3(){
        List<TOrder> tOrder = tOrderMapper.findByUserIdBetween(18l,70l);
        System.out.println(tOrder);
    }

定义标准分库策略集合

package com.itheima.sharding.alg;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Arrays;
import java.util.Collection;

/**
 * ClassName: Common4Tb
 * Package: com.itheima.sharding.alg
 * Description:
 *
 * @Author R
 * @Create 2024/2/10 14:44
 * @Version 1.0
 */
public class Common4Tb implements PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> dsNames, PreciseShardingValue<Long> shardingValue) {
        //获取数据库分片的字段名称 user_id = in
        String columnName = shardingValue.getColumnName();
        //获取逻辑表名称
        String logicTableName = shardingValue.getLogicTableName();
        //获取片键对应的值 select * from t_order where user_id=10,这里的value就等于10
        Long value = shardingValue.getValue();
        //一般是根据片键值获取对应的数据源，并返回
        String sufix=String.valueOf(value % 2 +1);
        String dsName = dsNames.stream().filter(ds -> ds.endsWith(sufix)).findFirst().get();
        return dsName;
    }

    @Override
    public Collection<String> doSharding(Collection<String> dsNames, RangeShardingValue<Long> shardingValue) {
        //获取分片字段
        String columnName = shardingValue.getColumnName();
        //获取逻辑表
        String logicTableName = shardingValue.getLogicTableName();
        //获取范围数据
        Range<Long> valueRange = shardingValue.getValueRange();
        //select * from t_order where user_id between 1 and 20;
        //判断是否有上限值
        if (valueRange.hasUpperBound()) {
            //获取上限值 20
            Long uppper = valueRange.upperEndpoint();
            System.out.println(uppper);
        }
        if (valueRange.hasLowerBound()){
            Long lower = valueRange.lowerEndpoint();
            System.out.println(lower);
        }
        return dsNames;
    }
}

#1.配置数据源
#数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=ds1,ds2
#数据库连接池类名称
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.175.129:3306/order_db_1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds1.username=root
#数据库密码
spring.shardingsphere.datasource.ds1.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=


#数据库连接池类名称
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.175.129:3306/order_db_2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds2.username=root
#数据库密码
spring.shardingsphere.datasource.ds2.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=
#-----------------------------------------------------------------------------------------------------------
# 配置数据节点：datanode,数据源.表 t_order是逻辑表，属于t_order_1和t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds${1..2}.t_order_${1..2}

#抽取变量
common.shard4db=com.itheima.sharding.alg.Common4Tb

#-----------------------------------------------------------------------------------------------------------
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=user_id
#精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=${common.shard4db}
#范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.range-algorithm-class-name=${common.shard4db}

#-----------------------------------------------------------------------------------------------------------
#分片列名称
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_${order_id % 2 +1}
#-----------------------------------------------------------------------------------------------------------

# 是否开启 SQL 显示，默认值: false
spring.shardingsphere.props.sql.show=true

定义标准分表策略

package com.itheima.sharding.alg;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Arrays;
import java.util.Collection;

/**
 * @author by itheima
 * @Date 2022/6/11
 * @Description 定义精准查询表的算法类 接口中的泛型与数据库的片键类型一致
 * 保证片键中使用= in
 */
public class CommonAlgorithm4Tb implements PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> {

    /**
     * 定义精准匹配表的方法
     * @param tbNames 所有配置的物理表的集合t_order_${1..2} 包含t_order_1 t_order_2封装到该集合下
     *                说白了就是sharinding把所有的数据源都给你，然后让你根据片键值选择
     * @param shardingValue 封装分片相关信息
     * @return 返回匹配的数据源
     */
    @Override
    public String doSharding(Collection<String> tbNames, PreciseShardingValue<Long> shardingValue) {
        //获取数据库分片的字段名称 user_id = in
        String columnName = shardingValue.getColumnName();
        //获取逻辑表名称
        String logicTableName = shardingValue.getLogicTableName();
        //获取片键对应的值 select * from t_order where order_id=10,这里的value就等于10
        Long value = shardingValue.getValue();
        //一般是根据片键值获取对应的数据源，并返回
        String sufix=String.valueOf(value % 2 +1);
        String dsName = tbNames.stream().filter(ds -> ds.endsWith(sufix)).findFirst().get();
        return dsName;
    }

    /**
     * @param tbNames 数据源集合
     * @param shardingValue 范围查询信息封装
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> tbNames, RangeShardingValue<Long> shardingValue) {
        //获取分片字段
        String columnName = shardingValue.getColumnName();
        //获取逻辑表
        String logicTableName = shardingValue.getLogicTableName();
        //获取范围数据
        Range<Long> valueRange = shardingValue.getValueRange();
        //select * from t_order where user_id between 1 and 20;
        //判断是否有上限值
        if (valueRange.hasUpperBound()) {
            //获取上限值 20
            Long uppper = valueRange.upperEndpoint();
            System.out.println(uppper);
        }
        if (valueRange.hasLowerBound()){
            Long lower = valueRange.lowerEndpoint();
            System.out.println(lower);
        }
        //理论上要根据上限值和下限值获取满足条件的数据源集合
        return Arrays.asList("t_order_1","t_order_2");
    }
}

#1.配置数据源
#数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=ds1,ds2
#数据库连接池类名称
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.175.129:3306/order_db_1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds1.username=root
#数据库密码
spring.shardingsphere.datasource.ds1.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=


#数据库连接池类名称
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.175.129:3306/order_db_2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.ds2.username=root
#数据库密码
spring.shardingsphere.datasource.ds2.password=123456
#数据库连接池的其它属性
#spring.shardingsphere.datasource.<data-source-name>.xxx=
#-----------------------------------------------------------------------------------------------------------
# 配置数据节点：datanode,数据源.表 t_order是逻辑表，属于t_order_1和t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds${1..2}.t_order_${1..2}

#抽取变量
common.shard4db=com.itheima.sharding.alg.Common4Tb

#-----------------------------------------------------------------------------------------------------------
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=user_id
#精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=${common.shard4db}
#范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.range-algorithm-class-name=${common.shard4db}

#-----------------------------------------------------------------------------------------------------------

#抽取变量
common.shard4tb=com.itheima.sharding.alg.CommonAlgorithm4Tb

spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
#精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=${common.shard4tb}
#范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.range-algorithm-class-name=${common.shard4tb}

#-----------------------------------------------------------------------------------------------------------

# 是否开启 SQL 显示，默认值: false
spring.shardingsphere.props.sql.show=true

    /**
     * @Description 测试精准匹配表的范围查询
     */
    @Test
    public void testRangeTable(){
        List<TOrder> tOrders = tOrderMapper.selectByRange(1l, 40l);
        System.out.println(tOrders);
    }
    /**
     * @Description
     */
    @Test
    public void testRangeTable2(){
        List<TOrder> tOrders = tOrderMapper.selectByRange2(2l,1l, 40l);
        System.out.println(tOrders);
    }

像素化创新

关注

24
点赞
踩
21

收藏

觉得还不错? 一键收藏
1
评论
sharding-jdbc分片方式介绍

广播表属于数据库中数据量较小和变动较少，且存在高频联合查询的表，比如：数据字典表等属于此广播表。可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。order_db_1└── t_dictorder_db_2└── t_dict#在数据库 user_db、order_db_1、order_db_2中均要建表`dict_id` BIGINT (20) NOT NULL COMMENT '字典id',
复制链接

扫一扫