欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

Sharding-jdbc实现读写分离、分库分表

程序员文章站 2022-06-19 10:20:12
一、简介Sharding-jdbc官网：http://shardingsphere.apache.org/1.概述a、Sharding-jdbc是一个开源的分布式的关系型数据库中间件b、Sharding-jdbc是客户端代理模式c、定位为轻量级的Java框架，以jar包提供服务；可以理解为增强版的jdbc驱动d、完全兼容各种ORM框架，如Mybatis等架构图：2.与Mycat之间的差别a、Mycat是服务端代理，sharding-jdbc是客户端代理b、MyCat不支持同一库内的水平...

一、简介

Sharding-jdbc官网：http://shardingsphere.apache.org/
1.概述
a、Sharding-jdbc是一个开源的分布式的关系型数据库中间件
b、Sharding-jdbc是客户端代理模式
c、定位为轻量级的Java框架，以jar包提供服务；可以理解为增强版的jdbc驱动
d、完全兼容各种ORM框架，如Mybatis等
架构图：
Sharding-jdbc实现读写分离、分库分表
2.与Mycat之间的差别
a、Mycat是服务端代理，sharding-jdbc是客户端代理
b、MyCat不支持同一库内的水平切分，Sharding-jdbc支持

二、使用

准备：使用前先准备两台Mysql数据库，作为分片节点
本项目使用的两台数据库节点分别为131和132
1.新建一个spring boot项目
a、通过idea创建一个springboot项目
b、通过Maven引入依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--sharding-jdbc for spring -->
        <!--<dependency>-->
            <!--<groupId>org.apache.shardingsphere</groupId>-->
            <!--<artifactId>sharding-jdbc-spring-namespace</artifactId>-->
            <!--<version>4.0.0-RC2</version>-->
        <!--</dependency>-->

        <!--sharding-jdbc for springboot -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC2</version>
        </dependency>
    </dependencies>

2.配置Sharding-jdbc
注意： a、Sharding-jdbc的配置在spring和springboot项目中是不同的
b、同时，在spring项目和spring-boot项目中，jar的引入方式也是不同的，请注意maven中sharding-jdbc的依赖包的引入方式
（1）第一种方式，使用spring名称空间的方式进行配置
a、创建sharding-jdbc.xml文件
文件位置：
Sharding-jdbc实现读写分离、分库分表

文件内容：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
       xmlns:master-slave="http://shardingsphere.apache.org/schema/shardingsphere/masterslave"
       xmlns:bean="http://www.springframework.org/schema/util"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave
                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd
                        http://www.springframework.org/schema/context
                        http://www.springframework.org/schema/context/spring-context.xsd
                        http://www.springframework.org/schema/tx
                        http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/util https://www.springframework.org/schema/util/spring-util.xsd">
    <!--第一个数据源 主-->
    <bean name="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close" >
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="password" value="root" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.73.131/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    <!--第一个数据源 从-->
    <bean id="slave0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="root" />
        <property name="password" value="root" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.73.130/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    <!--第二个数据源-->
    <bean id="ms1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="root" />
        <property name="password" value="root" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.73.132/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>

    <!--主从之间的负载均衡策略-->
    <master-slave:load-balance-algorithm id="msStrategy" type="RANDOM"/>

    <sharding:data-source id="sharding-data-source">
        <!--
            data-source-names: 该规则表示针对哪几个数据源；
        -->
        <sharding:sharding-rule data-source-names="ds0,slave0,ms1" default-data-source-name="ms0">
            <!--主从关系,在这里主从共同构建成为一个一体的数据源-->
            <sharding:master-slave-rules>
                <sharding:master-slave-rule id="ms0" master-data-source-name="ds0" slave-data-source-names="slave0"
                                            strategy-ref="msStrategy" />
            </sharding:master-slave-rules>

            <!--针对表的规则-->
            <sharding:table-rules>
                <!--
                    logic-table: sharding-jdbc 中的逻辑表
                    actual-data-nodes: 真实的数据节点,内容格式：库名.表名
                    $->：占位符，相当于spring中的${}
                    database-strategy-ref：数据库的分片策略
                    table-strategy-ref: 表的分片策略
                -->
                <sharding:table-rule logic-table="t_order" actual-data-nodes="ms$->{0..1}.t_order_$->{1..2}"
                                     database-strategy-ref="databaseStrategy" table-strategy-ref="tableStrategy"
                                     key-generator-ref="uuid" />

                <sharding:table-rule logic-table="t_order_item" actual-data-nodes="ms$->{0..1}.t_order_item_$->{1..2}"
                                     database-strategy-ref="databaseStrategy" table-strategy-ref="tableOrderItemStrategy"
                                     key-generator-ref="uuid" />

            </sharding:table-rules>

            <!--全局表配置-->
            <sharding:broadcast-table-rules>
                <sharding:broadcast-table-rule table="area"/>
            </sharding:broadcast-table-rules>

            <!--子表(绑定表) 在4.0.0-RC2 这个版本中，存在bug，绑定表无法使用，若要使用请关注sharding-jdbc的更新-->
            <sharding:binding-table-rules>
                <!--
                父表：t_order      order_id(主键,且同一个库中，使用该字段进行分别)； user_id,入库时，使用user_id进行分库
                子表：t_order_item 关联字段：order_id(t_order的主键)，user_id，入库时使用该字段判断父表所在的库
                注意：sharding-jdbc不能指定绑定字段，因此，子表和父表必须要有相同的字段，并以该字段作为关联字段-->
                <sharding:binding-table-rule logic-tables="t_order,t_order_item"/>
            </sharding:binding-table-rules>

        </sharding:sharding-rule>
    </sharding:data-source>

    <!--key 生成策略-->
    <sharding:key-generator id="uuid" column="order_id" type="UUID"/>
    <!--<sharding:key-generator id="snowflake" column="order_id" type="SNOWFLAKE" props-ref="snow"/>-->
    <!--<bean:properties id="snow">-->
        <!--<prop key="worker.id">678</prop>-->
        <!--<prop key="max.tolerate.time.difference.milliseconds">10</prop>-->
    <!--</bean:properties>-->
    <!--
        sharding-column: 分片列
        algorithm-expression：表达式
    -->
    <sharding:inline-strategy id="databaseStrategy" sharding-column="user_id"
                              algorithm-expression="ms$->{user_id % 2}"/>

    <!--分表策略-->
    <!--<sharding:inline-strategy id="tableStrategy" sharding-column="order_id"-->
                              <!--algorithm-expression="t_order_$->{order_id % 2 + 1}"/>-->
    <sharding:standard-strategy id="tableStrategy"
                                sharding-column="order_id"
                                precise-algorithm-ref="myShard"/>
    <bean id="myShard" class="com.example.shardingjdbcdemo.sharding.MySharding"/>

    <!--<sharding:inline-strategy id="tableOrderItemStrategy" sharding-column="order_id"-->
                              <!--algorithm-expression="t_order_item_$->{order_id % 2 + 1}"/>-->
    <sharding:standard-strategy id="tableOrderItemStrategy"
                                sharding-column="order_id"
                                precise-algorithm-ref="myShard"/>

    <!--接下来配置spring的SqlSessionFactory-->
    <bean class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="sharding-data-source"/>
        <property name="mapperLocations" value="classpath*:/mybatis/*.xml"/>
    </bean>
    <!--注意：以上配置完成后，请检查mapper中被分片的表的表名，不要使用实际表明，需要使用sharding:data-source配置的逻辑表名-->
</beans>

b、springBoot中引入该配置文件
Sharding-jdbc实现读写分离、分库分表
c.整体项目结构
Sharding-jdbc实现读写分离、分库分表
Sharding-jdbc实现读写分离、分库分表
d、自定义的分片表达式处理类

package com.example.shardingjdbcdemo.sharding;

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

import java.util.Collection;

/**
 * 自定义的处理分片表达式的类
 * 本次用例中，需要处理order_id 的分片规则
 * order_id 做为库内分片的字段，它既是t_order表的主键，同时也是子表t_order_item中的字段
 * order_id 使用了全局唯一主键 UUID
 */
public class MySharding implements PreciseShardingAlgorithm<String> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        String id = shardingValue.getValue();

        int mode = id.hashCode() % availableTargetNames.size();
        String[] strings = availableTargetNames.toArray(new String[0]);
        //取绝对值
        mode = Math.abs(mode);

        System.out.println(strings[0]+"---------"+strings[1]);
        System.out.println("mode="+mode);
        return strings[mode];
    }
}

e.分布式id解决方案之雪花算法
概述：
snowFlake 时Twitter提出的分布式ID算法
一个64bit的long型数字
引入了时间戳，保持自增

基本概念
Sharding-jdbc实现读写分离、分库分表
基本保持全局唯一，毫秒内并发最大4096个ID
时间回调可能会引起ID重复
可设置最大容忍回调时间
应用
配置：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
       xmlns:master-slave="http://shardingsphere.apache.org/schema/shardingsphere/masterslave"
       xmlns:bean="http://www.springframework.org/schema/util"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave
                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd
                        http://www.springframework.org/schema/context
                        http://www.springframework.org/schema/context/spring-context.xsd
                        http://www.springframework.org/schema/tx
                        http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/util https://www.springframework.org/schema/util/spring-util.xsd">
    <bean id="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="imooc" />
        <property name="password" value="Imooc@123456" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.73.131/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    <bean id="slave0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="imooc" />
        <property name="password" value="Imooc@123456" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.73.130/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    <bean id="ms1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="imooc" />
        <property name="password" value="Imooc@123456" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.73.132/shard_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>

    <master-slave:load-balance-algorithm id="msStrategy" type="RANDOM"/>

    <sharding:data-source id="sharding-data-source">
        <sharding:sharding-rule data-source-names="ds0,slave0,ms1" >
            <sharding:master-slave-rules>
                <sharding:master-slave-rule id="ms0" master-data-source-name="ds0" slave-data-source-names="slave0"
                    strategy-ref="msStrategy"
                />
            </sharding:master-slave-rules>
            <sharding:table-rules>
                <sharding:table-rule logic-table="t_order" actual-data-nodes="ms$->{0..1}.t_order_$->{1..2}"
                    database-strategy-ref="databaseStrategy" table-strategy-ref="standard"
                                     key-generator-ref="snowflake"
                />
            </sharding:table-rules>
            <sharding:broadcast-table-rules>
                <sharding:broadcast-table-rule table="area"/>
            </sharding:broadcast-table-rules>
            <!--<sharding:binding-table-rules>-->
                <!--<sharding:binding-table-rule logic-tables="t_order,t_order_item" />-->
            <!--</sharding:binding-table-rules>-->
        </sharding:sharding-rule>
    </sharding:data-source>

    <sharding:key-generator id="snowflake" column="order_id" type="SNOWFLAKE" props-ref="snow"/>

    <bean:properties id="snow">
        <prop key="worker.id">678</prop>
        <prop key="max.tolerate.time.difference.milliseconds">10</prop>
    </bean:properties>

    <sharding:inline-strategy id="databaseStrategy" sharding-column="user_id"
                              algorithm-expression="ms$->{user_id % 2}" />

    <bean id="myShard" class="com.example.shardingjdbcdemo.sharding.MySharding"/>

    <sharding:standard-strategy id="standard" sharding-column="order_id" precise-algorithm-ref="myShard"/>

    <sharding:inline-strategy id="tableStrategy" sharding-column="order_id"
                              algorithm-expression="t_order_$->{order_id % 2 +1}" />


    <bean class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="sharding-data-source"/>
        <property name="mapperLocations" value="classpath*:/mybatis/*.xml"/>
    </bean>

</beans>

对应的自定义分片处理逻辑类

package com.example.shardingjdbcdemo.sharding;

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

import java.util.Collection;

/**
 * 自定义的处理分片表达式的类
 * 本次用例中，需要处理order_id 的分片规则
 * order_id 做为库内分片的字段，它既是t_order表的主键，同时也是子表t_order_item中的字段
 * order_id 使用了全局唯一主键 雪花算法
 */
public class MySharding implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        Long id = shardingValue.getValue();

        long mode = id % availableTargetNames.size();
        String[] strings = availableTargetNames.toArray(new String[0]);
        //取绝对值
        mode = Math.abs(mode);

        System.out.println(strings[0]+"---------"+strings[1]);
        System.out.println("mode="+mode);
        return strings[(int) mode];
    }
}

（2）第二种方式，使用springboot starter 的配置方式
a、注释关于spring名称空间的引用
Sharding-jdbc实现读写分离、分库分表
b、修改maven依赖
Sharding-jdbc实现读写分离、分库分表
c、修改application.properties文件如下

# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,ms1,slave0

# 配置第 1 个数据源 -主库（131与130构成主从关系）
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://192.168.73.131/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root

#从库
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbcUrl=jdbc:mysql://192.168.73.130/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=root

# 配置第 2 个数据源
spring.shardingsphere.datasource.ms1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ms1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ms1.jdbcUrl=jdbc:mysql://192.168.73.132/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.ms1.username=root
spring.shardingsphere.datasource.ms1.password=root

#读写分离配置
spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=ds0
spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-names=slave0
spring.shardingsphere.sharding.master-slave-rules.ms0.load-balance-algorithm-type=RANDOM

# 配置 t_order 表规则
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ms$->{0..1}.t_order_$->{0..1}

# 配置分库策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
#相应的分片算法
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ms$->{user_id % 2}

# 配置分表策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=user_id
#自定义的分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbcdemo.sharding.MySharding
#配置t_order的主键生成策略
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=UUID


#全局表
spring.shardingsphere.sharding.broadcast-tables=area

#mybatis mapper 位置
mybatis.mapper-locations=/mybatis/*.xml

d、自定义的分片表达式处理类

package com.example.shardingjdbcdemo.sharding;

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

import java.util.Collection;

/**
 * 自定义的处理分片表达式的类
 * 本次用例中，需要处理order_id 的分片规则
 * order_id 做为库内分片的字段，它既是t_order表的主键，同时也是子表t_order_item中的字段
 * order_id 使用了全局唯一主键 UUID
 */
public class MySharding implements PreciseShardingAlgorithm<String> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        String id = shardingValue.getValue();

        int mode = id.hashCode() % availableTargetNames.size();
        String[] strings = availableTargetNames.toArray(new String[0]);
        //取绝对值
        mode = Math.abs(mode);

        System.out.println(strings[0]+"---------"+strings[1]);
        System.out.println("mode="+mode);
        return strings[mode];
    }
}

e.分布式id解决方案之雪花算法
概述：
snowFlake 时Twitter提出的分布式ID算法
一个64bit的long型数字
引入了时间戳，保持自增

基本概念
Sharding-jdbc实现读写分离、分库分表
基本保持全局唯一，毫秒内并发最大4096个ID
时间回调可能会引起ID重复
可设置最大容忍回调时间
应用
配置：

# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,ms1,slave0

# 配置第 1 个数据源 -主库（131与130构成主从关系）
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://192.168.73.131/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root

#从库
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbcUrl=jdbc:mysql://192.168.73.130/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=root

# 配置第 2 个数据源
spring.shardingsphere.datasource.ms1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ms1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ms1.jdbcUrl=jdbc:mysql://192.168.73.132/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.ms1.username=root
spring.shardingsphere.datasource.ms1.password=root

#读写分离配置
spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=ds0
spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-names=slave0
spring.shardingsphere.sharding.master-slave-rules.ms0.load-balance-algorithm-type=RANDOM

# 配置 t_order 表规则
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ms$->{0..1}.t_order_$->{0..1}

# 配置分库策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
#相应的分片算法
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ms$->{user_id % 2}

# 配置分表策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=user_id
#自定义的分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbcdemo.sharding.MySharding
#配置t_order的主键生成策略
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id

#spring.shardingsphere.sharding.tables.t_order.key-generator.type=UUID  全局id生成策略 UUID

#全局ID生成策略之雪花算法相关配置
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=345
spring.shardingsphere.sharding.tables.t_order.key-generator.props.max.tolerate.time.difference.milliseconds=10


#全局表
spring.shardingsphere.sharding.broadcast-tables=area

#mybatis mapper 位置
mybatis.mapper-locations=/mybatis/*.xml

对应的自定义分片处理逻辑类：

package com.example.shardingjdbcdemo.sharding;

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

import java.util.Collection;

/**
 * 自定义的处理分片表达式的类
 * 本次用例中，需要处理order_id 的分片规则
 * order_id 做为库内分片的字段，它既是t_order表的主键，同时也是子表t_order_item中的字段
 * order_id 使用了全局唯一主键 雪花算法
 */
public class MySharding implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        Long id = shardingValue.getValue();

        long mode = id % availableTargetNames.size();
        String[] strings = availableTargetNames.toArray(new String[0]);
        //取绝对值
        mode = Math.abs(mode);

        System.out.println(strings[0]+"---------"+strings[1]);
        System.out.println("mode="+mode);
        return strings[(int) mode];
    }
}

本文地址：https://blog.csdn.net/qq_42282792/article/details/112431165

相关标签： 分库分表 sharding-jdbc sharding

上一篇： 消息队列全面了解

下一篇： Express--中间件

推荐阅读