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

程序员文章站 2022-03-15 13:08: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.整体项目结构

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依赖

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型数字
引入了时间戳，保持自增

# 配置真实数据源
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

上一篇：小米手机电量怎样改变显示样式?小米手机电量改变显示样式技巧

下一篇： jquery PrintArea票据的套打功能实现方法

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

一、简介

二、使用

DBA 小记 — 分库分表、主从、读写分离

MySQL+MyCat分库分表读写分离配置

SpringBoot 2.0 整合sharding-jdbc中间件，实现数据分库分表

数据库水平切分的实现原理解析－－－分库，分表，主从，集群，负载均衡器

学会数据库读写分离、分表分库——用Mycat，这一篇就够了！

完美的分表分库方案还未实现

MyCat教程五：实现分库分表

MySQL：分库、分表、排序、分页、分组、实现教程

MySQL分布式数据库架构：分库、分表、排序、分页、分组、实现教程

Docker实现Mariadb分库分表及读写分离功能

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

一、简介

二、使用

DBA 小记 — 分库分表、主从、读写分离

MySQL+MyCat分库分表 读写分离配置

SpringBoot 2.0 整合sharding-jdbc中间件，实现数据分库分表

数据库水平切分的实现原理解析－－－分库，分表，主从，集群，负载均衡器

学会数据库读写分离、分表分库——用Mycat，这一篇就够了！

完美的分表分库方案还未实现

MyCat教程五：实现分库分表

MySQL：分库、分表、排序、分页、分组、实现教程

MySQL分布式数据库架构：分库、分表、排序、分页、分组、实现教程

Docker实现Mariadb分库分表及读写分离功能

MySQL+MyCat分库分表读写分离配置