mysql分库分表-shardingJDBC学习二（分片策略）

分片策略

<properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <spring.boot.version>2.5.5</spring.boot.version>

        <mybatisplus.boot.starter.version>3.4.0</mybatisplus.boot.starter.version>
        <lombok.version>1.18.16</lombok.version>
        <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
        <junit.version>4.12</junit.version>
        <druid.version>1.1.16</druid.version>
        <!--跳过单元测试-->
        <skipTests>true</skipTests>
    </properties>
    <dependencies>

        <dependency>

            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring.boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring.boot.version}</version>
            <scope>test</scope>
        </dependency>
        <!--mybatis plus和springboot整合-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.boot.starter.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <!--<scope>provided</scope>-->
        </dependency>
        <dependency>

            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-jdbc.version}
            </version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
        </dependency>
    </dependencies>

精准分片-分库分表配置及代码

spring.application.name=xdcalss-sharding-jdbc
server.port=8080

logging.level.root=INFO
# 控制台打印sql
spring.shardingsphere.props.sql.show=true
# 数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 数据库 ds0
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.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=用户名
spring.shardingsphere.datasource.ds0.password=密码

# 数据库 ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=用户名
spring.shardingsphere.datasource.ds1.password=密码

# 配置workId
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1


#精准分片，水平分表
# 指定product_order表的数据分布情况，配置数据节点,在Spring 环境中建议使⽤ $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}
#指定精准分⽚算法(⽔平分表)
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.precise-algorithm-class-name=net.xdclass.strategy.CustomTablePreciseShardingAlgorithm
#水平分库
spring.shardingsphere.sharding.tables.product_order.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.standard.precise-algorithm-class-name=net.xdclass.strategy.CustomDBPreciseShardingAlgorithm

#id生成策略  雪花算法
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

package net.xdclass.strategy;

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

import java.util.Collection;


public class CustomDBPreciseShardingAlgorithm
        implements PreciseShardingAlgorithm<Long> {
    /**
     *
     * @param dataSourceNames 数据源集合
     * 在分库时值为所有分⽚库的集合
    databaseNames
     * 分表时为对应分⽚库中所有分⽚
    表的集合 tablesNames
     *
     * @param shardingValue 分⽚属性，包括
     *
    logicTableName 为逻辑表，
     * columnName 分
    ⽚健（字段），
     * value 为从
    SQL 中解析出的分⽚健的值
     * @return
     */
    @Override
    public String doSharding(Collection<String>
                                     dataSourceNames, PreciseShardingValue<Long>
                                     shardingValue) {
        for (String databaseName : dataSourceNames)
        {
            String value = shardingValue.getValue()
                    % dataSourceNames.size() + "";
            //value是0，则进⼊0库表，1则进⼊1库表
            if (databaseName.endsWith(value)) {
                return databaseName;
            }
        }
        throw new IllegalArgumentException();
    }
}

package net.xdclass.strategy;

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

import java.util.Collection;


public class CustomTablePreciseShardingAlgorithm
        implements PreciseShardingAlgorithm<Long> {
    /**
     *
     * @param dataSourceNames 数据源集合
     * 在分库时值为所有分⽚库的集合
    databaseNames
     * 分表时为对应分⽚库中所有分⽚
    表的集合 tablesNames
     *
     * @param shardingValue 分⽚属性，包括
     *
    logicTableName 为逻辑表，
     * columnName 分
    ⽚健（字段），
     * value 为从
    SQL 中解析出的分⽚健的值
     * @return
     */
    @Override
    public String doSharding(Collection<String>
                                     dataSourceNames, PreciseShardingValue<Long>
                                     shardingValue) {
        for (String databaseName : dataSourceNames)
        {
            String value = shardingValue.getValue()
                    % dataSourceNames.size() + "";
            //value是0，则进⼊0库表，1则进⼊1库表
            if (databaseName.endsWith(value)) {
                return databaseName;
            }
        }
        throw new IllegalArgumentException();
    }
}

范围分片-分表配置及代码

注：如果没有配置范围分片，使用between会报错

spring.application.name=xdcalss-sharding-jdbc
server.port=8080

logging.level.root=INFO
# 控制台打印sql
spring.shardingsphere.props.sql.show=true
# 数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 数据库 ds0
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.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=用户名
spring.shardingsphere.datasource.ds0.password=密码

# 数据库 ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://101.132.179.61:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=用户名
spring.shardingsphere.datasource.ds1.password=密码

# 配置workId
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1

#精准分片，水平分表
# 指定product_order表的数据分布情况，配置数据节点,在Spring 环境中建议使⽤ $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}
#指定精准分⽚算法(⽔平分表)
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.precise-algorithm-class-name=net.xdclass.strategy.CustomTablePreciseShardingAlgorithm
#水平分库
spring.shardingsphere.sharding.tables.product_order.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.standard.precise-algorithm-class-name=net.xdclass.strategy.CustomDBPreciseShardingAlgorithm

#范围分片  分表配置
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.range-algorithm-class-name=net.xdclass.strategy.CustomRangeShardingAlgorithm

#id生成策略  雪花算法
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

public class CustomRangeShardingAlgorithm implements
        RangeShardingAlgorithm<Long> {
    @Override
    public Collection<String>
    doSharding(Collection<String> dataSourceNames,
               RangeShardingValue<Long> rangeShardingValue) {
        Set<String> result = new LinkedHashSet<>();
        // between 起始值
        Long lower =
                rangeShardingValue.getValueRange().lowerEndpoint();
        // between 结束值
        Long upper =
                rangeShardingValue.getValueRange().upperEndpoint();
        // 循环范围计算分库逻辑
        for (long i = lower; i <= upper; i++) {
            for (String databaseName :
                    dataSourceNames) {
                if (databaseName.endsWith(i %
                        dataSourceNames.size() + "")) {
                    result.add(databaseName);
                }
            }
        }
        return result;
    }
}

Hint分⽚策略：分片值不再是从sql中解析，而是通过代码指定；外部手动指定分片表或分片库，让sql在指定的分库、分表中执行。Hint策略会绕过sql解析，对于比较复杂的需要分片的查询，Hint策略性能较好

spring.application.name=xdcalss-sharding-jdbc
server.port=8080

logging.level.root=INFO
# 控制台打印sql
spring.shardingsphere.props.sql.show=true
# 数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 数据库 ds0
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.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=用户名
spring.shardingsphere.datasource.ds0.password=密码

# 数据库 ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=用户名
spring.shardingsphere.datasource.ds1.password=密码

spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}
# Hint分⽚算法
spring.shardingsphere.sharding.tables.product_order.table-strategy.hint.algorithm-class-name=net.xdclass.strategy.CustomTableHintShardingAlgorithm
spring.shardingsphere.sharding.tables.product_order.database-strategy.hint.algorithm-class-name=net.xdclass.strategy.CustomDBHintShardingAlgorithm

#id生成策略  雪花算法
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

public class CustomDBHintShardingAlgorithm
        implements HintShardingAlgorithm<Long> {
    /**
     *
     * @param dataSourceNames 数据源集合
     * 在分库时值为所有分⽚库的集合 databaseNames
     * 分表时为对应分⽚库中所有分⽚表的集合 tablesNames
     *
     * @param hintShardingValue 分⽚属性，包括
     *logicTableName 为逻辑表，
     * columnName分⽚健（字段），hit策略此处为空 ""
     *
     * value 【之前】都是 从 SQL 中解析出的分⽚健的值,⽤于取模判断
     *HintShardingAlgorithm不再从SQL 解析中获取值，⽽是直接通过
     *hintManager.addTableShardingValue("product_order",1)参数进⾏指定
     * @return
     */
    @Override
    public Collection<String>
    doSharding(Collection<String> dataSourceNames, HintShardingValue<Long> hintShardingValue) {
        Collection<String> result = new ArrayList<>();
        for (String tableName : dataSourceNames) {
            for (Long shardingValue : hintShardingValue.getValues()) {
                if (tableName.endsWith(String.valueOf(shardingValue % dataSourceNames.size()))) {
                    result.add(tableName);
                }
            }
        }
        return result;
    }
}


public class CustomTableHintShardingAlgorithm
        implements HintShardingAlgorithm<Long> {
    /**
     *
     * @param dataSourceNames 数据源集合
     * 在分库时值为所有分⽚库的集合 databaseNames
     * 分表时为对应分⽚库中所有分⽚表的集合 tablesNames
     *
     * @param hintShardingValue 分⽚属性，包括
     *logicTableName 为逻辑表，
     * columnName分⽚健（字段），hit策略此处为空 ""
     *
     * value 【之前】都是 从 SQL 中解析出的分⽚健的值,⽤于取模判断
     *HintShardingAlgorithm不再从SQL 解析中获取值，⽽是直接通过
     *hintManager.addTableShardingValue("product_order",1)参数进⾏指定
     * @return
     */
    @Override
    public Collection<String>
    doSharding(Collection<String> dataSourceNames,HintShardingValue<Long> hintShardingValue) {
        Collection<String> result = new ArrayList<>();
        for (String tableName : dataSourceNames) {
            for (Long shardingValue : hintShardingValue.getValues()) {
                if (tableName.endsWith(String.valueOf(shardingValue % dataSourceNames.size()))) {
                    result.add(tableName);
                }
            }
        }
        return result;
    }
}

   @org.junit.Test
    public void testHint() {
        // 清除掉历史的规则
        HintManager.clear();
        //Hint分⽚策略必须要使⽤ HintManager⼯具类
        HintManager hintManager = HintManager.getInstance();
        // 设置库的分⽚健,value⽤于库分⽚取模，

        hintManager.addDatabaseShardingValue("product_order",3L);
        // 设置表的分⽚健,value⽤于表分⽚取模，
        hintManager.addTableShardingValue("product_order", 8L);
        // 如果在读写分离数据库中，Hint 可以强制读主库（主从复制存在⼀定延时，但在业务场景中，可能更需要保证数据的实时性）
        //hintManager.setMasterRouteOnly();
        //对应的value只做查询，不做sql解析
        productOrderMapper.selectList(new QueryWrapper<ProductOrderDO>().eq("id", 66L));
    }

Sharding-Jdbc多种分⽚策略实战总结

⾃⼰实现分⽚策略的优缺点

优点：可以根据分⽚策略代码⾥⾯⾃⼰拼装真实的数据库、真实的表，灵活控制分⽚规则

缺点：增加了编码，不规范的 sql 容易造成全库表扫描，部分 sql语法⽀持不友好

⾏表达式分⽚策略 InlineShardingStrategy

只⽀持【 单分⽚键 】使⽤ Groovy 的表达式，提供对 SQL 语句中的 = 和 IN 的分⽚操作⽀持

可以通过简单的配置使⽤，⽆需⾃定义分⽚算法，从⽽避免繁琐的Java 代码开发 prouduct_order_$->{user_id % 8}` 表示订单表根据 user_id模 8 ，⽽分成 8 张表，表名称为 `prouduct_order_0` 到`prouduct_order_7

标准分⽚策略StandardShardingStrategy

只⽀持【 单分⽚键 】，提供 PreciseShardingAlgorithm 和 RangeShardingAlgorithm两个分⽚算法

PreciseShardingAlgorithm 精准分⽚是必选的，⽤于处理 = 和IN 的分⽚

RangeShardingAlgorithm 范围分配是可选的，⽤于处理 BETWEEN AND分⽚

如果不配置 RangeShardingAlgorithm ，如果 SQL 中⽤了 BETWEEN AND语法，则将按照全库路由处理，性能下降

复合分⽚策略ComplexShardingStrategy（一般不使用）

⽀持【 多分⽚键 】，多分⽚键之间的关系复杂，由开发者⾃⼰实现，提供最⼤的灵活度

提供对 SQL 语句中的 =, IN 和 BETWEEN AND 的分⽚操作⽀持

Hint分⽚策略HintShardingStrategy

这种分⽚策略⽆需配置分⽚健，分⽚健值也不再从SQL中解析，外部⼿动指定分⽚健或分⽚库，让 SQL在指定的分库、分表中执⾏。⽤于处理使⽤Hint⾏分⽚的场景，通过Hint⽽⾮SQL解析的⽅式分⽚的策略。

Hint 策略会绕过 SQL 解析的，对于这些⽐较复杂的需要分⽚的

查询， Hint 分⽚策略性能可能会更好

分库分表问题解决

问题⼀：执⾏的SQL排序、翻⻚、函数计算问题

分库后，数据分布再不同的节点上，跨节点多库进⾏查询时，会出现limit 分⻚、 order by 排序等问题。⽽且当排序字段⾮分⽚字段时，更加复杂了，要在不同的分⽚节点中将数据进⾏排序并返回，然后将不同分⽚返回的结果集进⾏汇总和再次排序（也会带来更多的CPU/IO 资源损耗）

解决⽅式：业务上要设计合理，利⽤好PartitionKey ，查询的数据分布同个数据节点上，避免跨节点多库进⾏查询时。

sharding-jdbc 在结果合并层⾃动帮我们解决很多问题（流式归并和内存归并）

问题⼆：数据库全局主键重复问题

常规表的 id 是使⽤⾃增 id 进⾏实现，分库分表后，由于表中数据同时存在不同数据库中，如果⽤⾃增id ，则会出现冲突问题

解决⽅式： UUID ⾃研发号器 redis 雪花算法

问题三：分库分表技术选型问题

市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择

解决⽅式

开源产品：主要是 Mycat 和 ShardingJdbc 区别，也是被⾯试官问⽐较多的

两者设计理念相同，主流程都是 SQL 解析 -->SQL 路由 -- >SQL改写 --> 结果归并

sharding-jdbc （推荐）

基于 jdbc 驱动，不⽤额外的 proxy ，在本地应⽤层重写 Jdbc 原⽣的⽅法，实现数据库分⽚形式

是基于 JDBC 接⼝的扩展，是以 jar 包的形式提供轻量级服务的，性能⾼

代码有侵⼊性

Mycat

是基于 Proxy ，它复写了 MySQL 协议，将 Mycat Server 伪装成⼀个 MySQL 数据库

客户端所有的 jdbc 请求都必须要先交给 MyCat ，再有 MyCat转发到具体的真实服务器

缺点是效率偏低，中间包装了⼀层

代码⽆侵⼊性

问题四：跨节点数据库Join关联查询和多维度查询

数据库切分前，多表关联查询，可以通过 sql join 进⾏实现

分库分表后，数据可能分布在不同的节点上， sql join 带来的问题就⽐较麻烦

不同维度查看数据，利⽤的 partitionKey 是不⼀样的

解决⽅案

冗余字段

⼴播表

NOSQL 汇总

案例⼀

订单需要⽤户的基本信息，但是分布在不同库上

进⾏字段冗余，订单表冗余⽤户昵称、头像

案例⼆

订单表的 partionKey 是 user_id ，⽤户查看⾃⼰的订单列表⽅便

但商家查看⾃⼰店铺的订单列表就麻烦，分布在不同数据节点

订单冗余存储在 es 上⼀份

问题五：容量规划,分库分表后⼆次扩容问题

此问题需具体结合业务处理，主要是数据库需成倍扩容，要保证旧数据的操作不会出现问题。

分⽚数量建议可以成倍扩容策略，只需要【迁移部分数据】即可

旧节点的数据，有⼀半要迁移⾄⼀个新增节点中

问题六：分布式事务问题

使用分布式事务框架

原文链接：https://blog.csdn.net/weixin_44815682/article/details/124237880