Sharding-jdbc實作讀寫分離、分庫分表

一、簡介

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實作讀寫分離、分庫分表

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="[email protected]" />
        <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="[email protected]" />
        <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="[email protected]" />
        <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];
    }
}

Sharding-jdbc實作讀寫分離、分庫分表

一、簡介

二、使用

繼續閱讀

從MySQL到HBase：分庫分表方案轉型的演進Why Not NoSQL/NewSQL?Why Not 分區?Why 分庫分表?

docker部署mycat實作mysql讀寫分離拉取鏡像mysql主從配置mysql master建立4個檔案夾（不要操縱從）建立檔案夾

ShardingSphere5入門實戰(二)之sharding-jdbc實作讀寫分離

ShardingSphere5入門實戰(三)之sharding-jdbc垂直分片

feed流系統重構-架構篇01 應用背景03 分庫分表04 Feed流05 消息隊列寫在最後

這些資深架構師平時需要解決的問題，看看你離資深架構師還有多少距離？

Sharding-JDBC（二）- Sharding-JDBC介紹一、Sharding-JDBC介紹二、Sharding-JDBC快速入門

分庫分表分庫分表如何權衡目前公司的存儲需要優化Mysql的主從MyCat資料庫分庫分表centos7安裝mysql5.7操作步驟

sharding-jdbc執行原理

springboot整合sharding-jdbc分庫分表介紹springboot整合sharding-jdbc分庫分表分析庫表資料，不難發現所配置的存儲/查詢規律結論：limit 分頁操作

sharding-jdbc核心概念記錄

【資料庫】分庫分表的難點

00.資料庫分庫分表-概述摘要

資料庫分庫分表思想

MySQL 性能調優——資料庫的分庫分表 1. 資料庫分片2、資料庫分片前的準備：

Mysql 網站大全