ShardingJDBC分库分表实战指南

分库分表核心概念

alt text

虚拟库： ShardingSphere的核⼼就是提供⼀个具备分库分表功能的虚拟库，他是⼀个ShardingSphereDatasource实例。应⽤程序只需要像操作单数据源⼀样访问这个ShardingSphereDatasource即可。示例中，MyBatis框架并没有特殊指定DataSource，就是使⽤的ShardingSphere的DataSource数据源。
真实库： 实际保存数据的数据库。这些数据库都被包含在ShardingSphereDatasource实例当中，由ShardingSphere决定未来需要使⽤哪个真实库。下面示例中，m0和m1就是两个真实库。
逻辑表： 应⽤程序直接操作的逻辑表。示例中操作的course表就是⼀个逻辑表，并不需要在数据库中真实存在。
真实表： 实际保存数据的表。这些真实表与逻辑表表名不需要⼀致，但是需要有相同的表结构，可以分布在不同的真实库中。应⽤可以维护⼀个逻辑表与真实表的对应关系，所有的真实表默认也会映射成为ShardingSphere的虚拟表。下面的示例中course_1和course_2就是真实表。
分布式主键⽣成算法： 给逻辑表⽣成唯⼀主键。由于逻辑表的数据是分布在多个真实表当中的，所有，单表的索引就⽆法保证逻辑表的ID唯⼀性。因此，在做分库分表时，通常都会独⽴出⼀个⽣成分布式ID的主键⽣成算法。示例中使⽤的SNOWFLAKE雪花算法就是⼀种很常⻅的主键⽣成算法。
分⽚策略： 表示逻辑表要如何分配到真实库和真实表当中，分为分库策略和分表策略两个部分。分⽚策略由分⽚键和分⽚算法组成。分⽚键是进⾏数据⽔平拆分的关键字段。分⽚算法则表示根据分⽚键如何寻找对应的真实库和真实表。下面示例当中对cid字段取模，就是⼀种简单的分⽚算法。如果ShardingSphere匹配不到合适的分⽚策略，那就只能进⾏全分⽚路由，这是效率最差的⼀种实现⽅式。

垂直分⽚和⽔平分⽚

这也是设计分库分表⽅案时经常会提到的概念，所以也⼀并做⼀下梳理。当我们设计分库分表⽅案时，通常有两种拆分数据的维度。⼀是按照业务划分的维度，将不同的表拆分到不同的库当中。这样可以减少每个数据库的数据量以及客户端的连接数，提⾼查询效率。这种⽅案称为垂直分库。⼆是按照数据分布的维度，将原本存在同⼀张表当中的数据，拆分到多张⼦表当中。每个⼦表只存储⼀部分数据。这样可以介绍每⼀张表的数据量，提升查询效率。这种⽅案称为⽔平分表。

alt text

通常我们讲的分库分表，主要是指⽔平分⽚，因为这样才能减少数据量，从根本上解决数据量过⼤带来的存储和查询的问题。但是，这也并不意味着垂直分⽚⽅案就不重要。

分库分表案例

任务需求：需要将一批课程信息分别拆分到两个库，四个表中。

alt text

项目搭建步骤：

创建数据库表

sql

CREATE TABLE course (
`cid` bigint(0) NOT NULL,
`cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`user_id` bigint(0) NOT NULL,
`cstatus` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

搭建⼀个Maven项⽬，在pom.xml中加⼊以下的依赖。

xml

<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-dependencies</artifactId>
            <version>2.2.1.RELEASE</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
        <!-- mybatisplus依赖 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
    </dependencies>
</dependencyManagement>
<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
    </dependency>
    <!-- 数据源连接池 -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.20</version>
    </dependency>
    <!-- mysql连接驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <!-- mybatisplus依赖 -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.4.3.3</version>
    </dependency>
</dependencies>

使⽤MyBatis-plus的⽅式，直接声明Entity和Mapper，映射数据库中的course表

java

public class Course {
    private Long cid;
    private String cname;
    private Long userId;
    private String cstatus;
    //省略。getter ... setter ....
}
public interface CourseMapper extends BaseMapper<Course> {
}

增加SpringBoot启动类，扫描mapper接⼝

java

@SpringBootApplication
@MapperScan("com.roy.jdbcdemo.mapper")
public class App {
    public static void main(String[] args) {
        SpringApplication.run(App.class,args);
    }
}

在springboot的配置⽂件application.properties中增加数据库配置

xml

spring.datasource.druid.db-type=mysql
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql://192.168.65.212:3306/test?serverTimezone=UTC
spring.datasource.druid.username=root
spring.datasource.druid.password=root

写⼀个单元测试，简单的把course课程信息插⼊到数据库，以及从数据库中进⾏查询

java

@SpringBootTest
@RunWith(SpringRunner.class)
public class JDBCTest {
    @Resource
    private CourseMapper courseMapper;
    @Test
    public void addcourse() {
        for (int i = 0; i < 10; i++) {
        Course c = new Course();
        c.setCname("java");
        c.setUserId(1001L);
        c.setCstatus("1");
        courseMapper.insert(c);
        //insert into course values ....
        System.out.println(c);
        }
    }
    @Test
    public void queryCourse() {
        QueryWrapper<Course> wrapper = new QueryWrapper<Course>();
        wrapper.eq("cid",1L);
        List<Course> courses = courseMapper.selectList(wrapper);
        courses.forEach(course -> System.out.println(course));
    }
}

引⼊ShardingJDBC快速实现分库分表

在之前的简单案例基础上，快速使⽤ShardingSphere实现Course表的分库分表功能

在pom.xml中引⼊ShardingSphere

xml

<dependencies>
    <!-- shardingJDBC核⼼依赖 -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
        <version>5.2.1</version>
        <exclusions>
            <exclusion>
                <artifactId>snakeyaml</artifactId>
                <groupId>org.yaml</groupId>
            </exclusion>
        </exclusions>
    </dependency>
    <!-- 版本冲突 -->
    <dependency>
        <groupId>org.yaml</groupId>
        <artifactId>snakeyaml</artifactId>
        <version>1.33</version>
    </dependency>
    <!-- SpringBoot依赖 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
        <exclusions>
            <exclusion>
                <artifactId>snakeyaml</artifactId>
                <groupId>org.yaml</groupId>
            </exclusion>
        </exclusions>
    </dependency>
    <!-- 数据源连接池 -->
    <!--注意不要⽤这个依赖，他会创建数据源，跟上⾯ShardingJDBC的SpringBoot集成依赖有冲突 -->
    <!-- <dependency>-->
    <!-- <groupId>com.alibaba</groupId>-->
    <!-- <artifactId>druid-spring-boot-starter</artifactId>-->
    <!-- <version>1.1.20</version>-->
    <!-- </dependency>-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.20</version>
    </dependency>
    <!-- mysql连接驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <!-- mybatisplus依赖 -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.4.3.3</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
    </dependency>
</dependencies>

注： ShardingSphere⽬前最新版本5.5.0提供的官⽅依赖项是

xml

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc</artifactId>
    <version>${latest.release.version}</version>
</dependency>

这⾥并没有使⽤官⽅的这个依赖项，⽽是采⽤的SpringBoot集成ShardingSphere的⼀个依赖项，依赖的是5.2.1版本。原因有两个：

1、⼀是⽬前最新版本ShardingSphere与SpringBoot的集成，版本相对较慢，这在业务功能上倒没有造成很直接的影响，但是会造成对开发⼯作的⽀持没有那么明显。⽐如IDEA中对SpringBoot的配置⽂件没有提示。

2、⼆是经过讲课视频教师测试，官⽅提供的这个依赖项，⽬前从5.2往上的版本，还有⼀些问题，尤其跟其他组件集成时，经常会出现不兼容的情况。所以，并没有追求⽬前最新的5.5.0版本，⽽是采⽤5.2.1版本。

在对应数据库⾥创建分⽚表：按照我们之前的设计，去对应的数据库中⾃⾏创建course_1和course_2表。表结构与course表是⼀致的
增加ShardingJDBC的分库分表配置：应⽤层代码不需要做任何的修改，直接修改SpringBoot的配置⽂件application.properties，在⾥⾯添加以下配置信息。再次执⾏addCourse⽅法，添加课程信息，数据就会⾃动完成分库分表

yaml

# 打印SQL
## 显示ShardingSphere执行的真实SQL，便于调试:cite[1]
spring.shardingsphere.props.sql-show=true
## 允许覆盖Bean定义，避免在集成ShardingSphere时出现Bean冲突
spring.main.allow-bean-definition-overriding=true

# ----------------数据源配置----------------
## 定义数据源的逻辑名称，多个用逗号分隔:cite[6]
spring.shardingsphere.datasource.names=m0,m1

## 配置第一个数据源m0:cite[1]
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
## 连接字符串，指向第一个分片数据库sharding1
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/sharding1?serverTimezone=UTC
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=123456

## 配置第二个数据源m1:cite[1]
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
## 连接字符串，指向第二个分片数据库sharding2
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/sharding2?serverTimezone=UTC
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

#------------------------分布式序列算法配置------------------------
## 定义一个名为'alg_snowflake'的分布式主键生成器，类型为雪花算法:cite[3]:cite[6]
spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.type=SNOWFLAKE
## 设置雪花算法的工作节点ID。单机模式可自定义，集群中需保证唯一:cite[3]
## 注意：在properties文件中，通常建议使用`worker.id`而不是`worker-id`
spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.props.worker.id=1

## 为course表配置主键生成策略:cite[6]
## 指定主键列名
spring.shardingsphere.rules.sharding.tables.course.key-generate-strategy.column=cid
## 指定使用上面定义的'alg_snowflake'主键生成器
spring.shardingsphere.rules.sharding.tables.course.key-generate-strategy.key-generator-name=alg_snowflake

#-----------------------配置实际分片节点-----------------------
## 定义course逻辑表的实际数据节点:cite[6]:cite[8]
## 格式: 数据源名.表名
## 此处表示: 
##   - 数据分布在m0和m1两个数据源
##   - 每个数据源中有course_1和course_2两张表
##   - 共2库 × 2表 = 4个物理分片
spring.shardingsphere.rules.sharding.tables.course.actual-data-nodes=m$->{0..1}.course_$->{1..2}

#-----------------------分库策略-----------------------
## 配置course表的分库策略为标准策略:cite[6]:cite[8]
## 指定分库片键列名
spring.shardingsphere.rules.sharding.tables.course.database-strategy.standard.sharding-column=cid
## 指定分库算法名称为'course_db_alg'
spring.shardingsphere.rules.sharding.tables.course.database-strategy.standard.sharding-algorithm-name=course_db_alg

## 定义名为'course_db_alg'的分片算法:cite[6]
## 算法类型为MOD（取模）:cite[8]
spring.shardingsphere.rules.sharding.sharding-algorithms.course_db_alg.type=MOD
## MOD算法属性：分片数量为2（与数据源数量匹配）
spring.shardingsphere.rules.sharding.sharding-algorithms.course_db_alg.props.sharding-count=2

#-----------------------分表策略-----------------------
## 配置course表的分表策略为标准策略:cite[6]:cite[8]
## 指定分表片键列名（此处与分库片键相同，均为cid）
spring.shardingsphere.rules.sharding.tables.course.table-strategy.standard.sharding-column=cid
## 指定分表算法名称为'course_tbl_alg'
spring.shardingsphere.rules.sharding.tables.course.table-strategy.standard.sharding-algorithm-name=course_tbl_alg

## 定义名为'course_tbl_alg'的分片算法:cite[6]
## 算法类型为INLINE（行表达式）:cite[8]
spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.type=INLINE
## INLINE算法属性：使用Groovy表达式计算分表路由:cite[8]
## cid % 2 + 1 确保结果在1-2范围内，与actual-data-nodes中的course_$->{1..2}匹配
## 例如：cid为5 -> 5 % 2 = 1 -> 1 + 1 = 2 -> 路由到course_2表
spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.props.algorithm-expression=course_$->{cid % 2 + 1}

#-----------------------关于均匀分片的注释-----------------------
## 你提到的注释说明：
## 1. 如果cid严格递增，取模分片会很均匀
## 2. 雪花算法生成的cid不是严格递增，是趋势递增，可能导致短时内数据分布不均
## 3. 如需绝对均匀，可考虑自定义算法（如MYSNOWFLAKE）或更复杂的表达式
## 下面是一个更复杂表达式的例子，旨在将数据更均匀地分布到4个分片（2库×2表）上：
## 表达式解析: ((cid + 1) % 4).intdiv(2) + 1
##   - (cid + 1) % 4: 将cid映射到0-3的范围
##   - .intdiv(2): 整数除法除以2，将0,1映射为0；2,3映射为1 → 这可以决定库的编号（0或1，对应m0或m1）
##   - +1: 将表编号固定在1-2范围
## 注意：此表达式仅为示例，实际效果需测试，且会增加SQL解析复杂度
#spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.props.algorithm-expression=course_$->{((cid+1)%4).intdiv(2)+1}

注意：可能你将分库分表的配置加入到项目的配置文件中会出现报红的情况，可以先尝试以下项目测试用例插入数据是否成功，虽然项目配置文件报红，但是可能不会影响项目的启动（我遇到的就是这种情况）

alt text

运行插入语句的测试用例，如下显示插入数据库的结果

alt text

ShardingJDBC常⻅数据分⽚策略实战

INLINE简单分⽚（行表达式分片策略）

INLINE简单分⽚主要⽤来处理针对分⽚建的=和 in 这样的查询操作。在这些操作中，可以拿到分⽚键的精确值。例如对下⾯这样的操作：

java

@Test
public void queryCourse() {
    QueryWrapper<Course> wrapper = new QueryWrapper<Course>();
//  wrapper.eq("cid", 1961635932830556166L);
    wrapper.in("cid", 1961635932830556166L, 1961635932767641604L, 1961635932767641603L);
    List<Course> courses = courseMapper.selectList(wrapper);
    courses.forEach(course -> System.out.println(course));
}

对于这样的操作，拿到分⽚键的精确值后，都可以通过表达式计算出可能的真实库以及真实表。⽽ShardingJDBC就会将逻辑SQL转化成对应的真实SQL，并路由到真实库中去执⾏。

ShardingJDBC关注的是过滤数据的关键查询条件中是否包含了分⽚键，⽽并只是简单关注附加的条件。例如在SQL语句后⾯加上order by user_id，并不会影响ShardingJDBC的处理过程。⽽如果查询条件中不包含分⽚建，那么 ShardingJDBC就只能根据actual-nodes，到所有的真实表和真实库中查询。这也就是全分⽚路由

对于全分⽚路由，ShardingJDBC做了⼀定的优化。⽐如通过Union将同⼀库的多条语句结合起来，这样可以减少与数据库的交互次数

alt text

但是，在真实项⽬中，这种全分⽚路由是⼀定要尽⼒避免的。因为在真实项⽬中，你要⾯对的，就不是示例中少数的⼏个分⽚了，通常都是⼏⼗个甚⾄上百个分⽚。在这样⼤数据情况下，进⾏全分⽚路由，效率是⾮常低的

STANDARD标准分⽚

应⽤当中我们对于主键信息通常不只是进⾏精确查询，还需要进⾏范围查询。这时就需要⼀种能够同时⽀持精确查询和范围查询的算法出⼚了。这就是STANDARD标准分⽚。例如：

java

@Test
public void queryCourseRange() {
    QueryWrapper<Course> wrapper = new QueryWrapper<Course>();
    wrapper.between("cid", 1961635932767641600L, 1961635932767641604L);
    List<Course> courses = courseMapper.selectList(wrapper);
    courses.forEach(course -> System.out.println(course));
    }

这时，如果不修改分⽚算法，直接执⾏。由于ShardingSphere⽆法根据配置的表达式计算出可能的分⽚情况，在执⾏时就会抛出⼀个异常

alt text

报错信息明确提到需要添加⼀个allow-range-query-with-inline-sharding参数。这时，就需要给course_tbl_alg算法添加这个参数。

xml

# 允许在inline策略中使⽤范围查询。
spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.props.allow-range-query-with-inline-sharding=true

加上这个参数后，就可以进⾏查询了。但是这样就可以了吗？观察⼀下Actual SQL的执⾏⽅式，你会发现这时SQL还是按照全路由的⽅式执⾏的。之前⼀直强调过，这是效率最低的⼀种执⾏⽅式

alt text

那么有没有办法通过查询时的范围下限和范围上限⾃⼰计算出⼀个⽬标真实库和真实表呢？当然是⽀持的。只不过，很显然，这种范围查询要匹配的精确值太多了，不可能通过⼀个简单的表达式来处理

COMPLEX_INLINE复杂分⽚（复合分片策略）

除了针对单个分⽚键的查询，我们还有可能需要针对多个属性进⾏组合查询。例如

java

@Test
public void queryCourseComplexSimple(){
    QueryWrapper<Course> wrapper = new QueryWrapper<Course>();
    wrapper.orderByDesc("user_id");
    wrapper.in("cid",851198095084486657L,851198095139012609L);
    wrapper.eq("user_id",1001L);
    List<Course> course = courseMapper.selectList(wrapper);
    //select * from couse where cid in (xxx) and user_id =xxx
    System.out.println(course);
}

简单执⾏⼀下，当然是可以执⾏的

但是有⼀个⼩问题，user_id查询条件只能参与数据查询，但是并不能参与到分⽚算法当中。例如在我们的示例当中，所有的user_id都是1001L，这其实是数据⼀个⾮常明显的分⽚规律。如果user_id的查询条件不是1001L，那这时其实不需要到数据库中去查，我们也能知道是不会有结果的。有没有办法让user_id也参与到分⽚算法当中呢？

当然是可以的，不过STANDARD策略就不够⽤了。这时候就需要引⼊COMPLEX_INLINE策略。注释掉之前给course表配置的分表策略，重新分配⼀个新的分表策略：

yaml

#给course表指定分表策略 complex-按多个分⽚键进⾏组合分⽚
spring.shardingsphere.rules.sharding.tables.course.table-strategy.complex.sharding-columns=cid,user_id
spring.shardingsphere.rules.sharding.tables.course.table-strategy.complex.sharding-algorithm-name=course_tbl_alg
# 分表策略-COMPLEX：按多个分⽚键组合分表
spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.type=COMPLEX_INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.props.algorithm-expression=course_$->{(cid+user_id+1)%2+1}

在这个配置当中，就可以使⽤cid和user_id两个字段联合确定真实表。例如在查询时，将user_id条件设定为1002L，此时不管cid传什么值，就总是会路由到错误的表当中，查不出数据了

CLASS_BASED⾃定义分⽚

例如，我要进⾏下⾯这样的查询，包含对user_id的范围查询

java

@Test
public void queryCourdeComplex(){
    QueryWrapper<Course> wrapper = new QueryWrapper<Course>();
    wrapper.in("cid",799020475735871489L,799020475802980353L);
    wrapper.between("user_id",3L,8L);
    List<Course> course = courseMapper.selectList(wrapper);
    System.out.println(course);
}

我们测试数据中的user_id都是固定的1001L，那么接下来我就可以希望在对user_id进⾏范围查询时，能够提前判断⼀些不合理的查询条件。⽽具体的判断规则，⽐如在对user_id进⾏between范围查询时，要求查询的范围必须包括1001L这个值。

如果连这个简单的规则都⽆法满⾜，那么这个SQL语句明显不可能有数据。对于这样的SQL，当然是希望他不要去数据库⾥执⾏了。因为这样明显是浪费性能。那么这样的需求要怎么实现呢？

虽然对于COMPLEX_INLINE策略，也⽀持添加allow-range-query-with-inline-sharding参数让他能够⽀持分⽚键的范围查询，但是这时这种复杂的分⽚策略就明显不能再⽤⼀个简单的表达式来忽悠了

这就需要⼀个Java类来实现这样的规则。这个算法类也不⽤⾃⼰瞎设计，只要实现ShardingSphere提供的ComplexKeysShardingAlgorithm接⼝就⾏了

java

public class MyComplexAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
    private static final String SHARING_COLUMNS_KEY = "sharding-columns";
    private Properties props;
    //保留配置的分⽚键。在当前算法中其实是没有⽤的。
    private Collection<String> shardingColumns;
    @Override
    public void init(Properties props) {
        this.props = props;
        this.shardingColumns = getShardingColumns(props);
    }
    /**
    * 实现⾃定义分⽚算法
    * @param availableTargetNames 在actual-nodes中配置了的所有数据分⽚
    * @param shardingValue 组合分⽚键
    * @return ⽬标分⽚
    */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames,ComplexKeysShardingValue<Long> shardingValue) {
        //select * from cid where cid in (xxx,xxx,xxx) and user_id between {lowerEndpoint} and {upperEndpoint};
        Collection<Long> cidCol = shardingValue.getColumnNameAndShardingValuesMap().get("cid");
        Range<Long> userIdRange = shardingValue.getColumnNameAndRangeValuesMap().get("user_id");
        //拿到user_id的查询范围
        Long lowerEndpoint = userIdRange.lowerEndpoint();
        Long upperEndpoint = userIdRange.upperEndpoint();
        //如果下限 >= 上限
        if(lowerEndpoint >= upperEndpoint){
            //抛出异常，终⽌去数据库查询的操作
            throw new UnsupportedShardingOperationException("empty record query","course");
            //如果查询范围明显不包含1001
        }else if(upperEndpoint<1001L || lowerEndpoint>1001L){
            //抛出异常，终⽌去数据库查询的操作
            throw new UnsupportedShardingOperationException("error range query param","course");
            // return result;
        }else{
            List<String> result = new ArrayList<>();
            //user_id范围包含了1001后，就按照cid的奇偶分⽚
            String logicTableName = shardingValue.getLogicTableName();//操作的逻辑表 course
            for (Long cidVal : cidCol) {
                String targetTable = logicTableName+"_"+(cidVal%2+1);
                if(availableTargetNames.contains(targetTable)){
                    result.add(targetTable);
                }
            }
            return result;
        }
    }
    private Collection<String> getShardingColumns(final Properties props) {
        String shardingColumns = props.getProperty(SHARING_COLUMNS_KEY, "");
        return shardingColumns.isEmpty() ? Collections.emptyList() :
        Arrays.asList(shardingColumns.split(","));
    }
    public void setProps(Properties props) {
        this.props = props;
    }
    @Override
    public Properties getProps() {
        return this.props;
    }
}

在核⼼的dosharding⽅法当中，就可以按照我们之前的规则进⾏判断。不满⾜规则，直接抛出UnsupportedShardingOperationException异常，就可以组织ShardingSphere把SQL分配到真实数据库中执⾏

接下来，还是需要增加策略配置，让course表按照这个规则进⾏分⽚

yaml

spring.shardingsphere.rules.sharding.tables.course.table-strategy.complex.sharding-columns=cid,user_id
spring.shardingsphere.rules.sharding.tables.course.table-strategy.complex.sharding-algorithm-name=course_tbl_alg
# 使⽤CLASS_BASED分⽚算法- 不⽤配置SPI扩展⽂件
spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.type=CLASS_BASED
# 指定策略 STANDARD|COMPLEX|HINT
spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.props.strategy=COMPLEX
# 指定算法实现类。这个类必须是指定的策略对应的算法接⼝的实现类。 STANDARD-> StandardShardingAlgorithm;COMPLEX->ComplexKeysShardingAlgorithm;HINT -> HintShardingAlgorithm
spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.props.algorithmClassName=com.roy.shardingDemo.algorithm.MyComplexAlgorithm

这时，再去执⾏查询⽅法，就会得到这样的异常信息,代码会抛出我们自定义的异常

alt text

HINT_INLINE强制分⽚

需要查询所有cid为奇数的课程信息。这要怎么查呢？按照MyBatis-plus的机制，你应该很快能想到在CourseMapper中实现⼀个⾃定义的SQL语句就⾏了

sql

public interface CourseMapper extends BaseMapper<Course> {
    @Select("select * from course where MOD(cid,2)=1")
    List<Long> unsupportSql();
}

java

@Test
public void unsupportTest(){
    //select * from course where mod(cid,2)=1
    List<Long> res = courseMapper.unsupportSql();
    res.forEach(System.out::println);
}

在我们当前的这个场景下，course的信息就是按照cid的奇偶分⽚的，所以⾃然是希望只去查某⼀个真实表就可以了。这种基于虚拟列的查询语句，对于ShardingSphere来说实际上是⼀块难啃的⻣头。因为他很难解析出你是按照cid分⽚键进⾏查询的，并且不知道怎么组织对应的策略去进⾏分库分表。所以他的做法只能⼜是性能最低的全路由查询。

alt text

当你决定使用分库分表（如ShardingSphere）后，就必须在应用架构和SQL编写上做出重大改变，放弃那些在单库单表环境下常用的复杂SQL查询方式

这个cid的奇偶关系并不能通过SQL语句正常体现出来，这时，就需要⽤上ShardingSphere提供的另外⼀种分⽚算法HINT 强制路由。HINT强制路由可以⽤⼀种与SQL⽆关的⽅式进⾏分库分表。

注释掉之前给course表分配的分表算法，重新分配⼀个HINT_INLINE类型的分表算法

yaml

spring.shardingsphere.rules.sharding.tables.course.table-strategy.complex.sharding-columns=cid,user_id
spring.shardingsphere.rules.sharding.tables.course.table-strategy.complex.sharding-algorithm-name=course_tbl_alg
#给course表指定分表策略 hint-与SQL⽆关的⽅式进⾏分⽚
spring.shardingsphere.rules.sharding.tables.course.table-strategy.hint.sharding-algorithm-name=course_tbl_alg
# 分表策略-HINT：⽤于SQL⽆关的⽅式分表，使⽤value关键字。
spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.type=HINT_INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.course_tbl_alg.props.algorithm-expression=course_$->{value}

然后，在应⽤进⾏查询时，使⽤HintManager给HINT策略指定value的值

java

@Test
public void queryCourseByHint(){
    //强制只查course_1表
    HintManager hintManager = HintManager.getInstance();
    // 强制查course_1表
    hintManager.addTableShardingValue("course","1");
    //select * from course;
    List<Course> courses = courseMapper.selectList(null);
    courses.forEach(course -> System.out.println(course));
    //线程安全，所有⽤完要注意关闭。
    hintManager.close();
    //hintManager关闭的主要作⽤是清除ThreadLocal，释放内存。HintManager实现了AutoCloseable接⼝，所以建议使⽤try-resource的⽅式，⽤完⾃动关闭。
    //try(HintManager hintManager = HintManager.getInstance()){ xxxx }
}

这样就可以让SQL语句只查询course_1表，在当前场景下，也就相当于是实现了只查cid为奇数的需求。

alt text

常⽤分⽚算法总结

在之前的示例中就介绍了ShardingSphere提供的MOD、HASH-MOD这样的简单内置分⽚策略，standard、complex、hint三种典型的分⽚策略以及CLASS_BASED这种扩展分⽚策略的⽅法。为什么要有这么多的分⽚策略，其实就是以为分库分表⾯临的业务场景其实是很复杂的。即便是ShardingSphere，也⽆法真的像MySQL、Oracle这样的数据库产品⼀样，完美的兼容所有的SQL语句。因此，⼀旦开始决定⽤分库分表，那么后续业务中的每⼀个SQL语句就都需要结合分⽚策略进⾏思考，不能像操作真正数据库那样随⼼所欲了。

ShardingJDBC数据加密功能实战

ShardingSphere内置了多种加密算法，可以⽤来快速对关键数据进⾏加密。最典型的，⽐如对⽤户的密码，通常都是需要加密存储的。使⽤ShardingSphere就可以⽤应⽤⽆感知的⽅式，快速实现数据加密。并且可以灵活切换多种内置的加密算法。

sql

CREATE TABLE user (
`userid` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`password` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`password_cipher` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`userstatus` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`age` int(0) DEFAULT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'F or M',
PRIMARY KEY (`userid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

创建对应的数据实体：

java

@TableName("user")
public class User {
    private String userid;
    private String username;
    private String password;
    private String userstatus;
    private int age;
    private String sex;
    // getter ... setter ...
}

创建操作数据库的mapper

java

public interface UserCourseInfoMapper extends BaseMapper<UserCourseInfo> {
}

SpringBoot中配置逻辑表user的加密算法

yaml

# 打印SQL
spring.shardingsphere.props.sql-show = true
spring.main.allow-bean-definition-overriding = true
# ----------------数据源配置
# 指定对应的库
spring.shardingsphere.datasource.names=m0,m1
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://192.168.65.212:3306/shardingdb1?
serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://192.168.65.212:3306/shardingdb2?
serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
#------------------------分布式序列算法配置
# ⽣成字符串类型分布式主键。
spring.shardingsphere.rules.sharding.key-generators.user_keygen.type=NANOID
#spring.shardingsphere.rules.sharding.key-generators.user_keygen.type=UUID
# 指定分布式主键⽣成策略
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=userid
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=user_keygen
#-----------------------配置实际分⽚节点
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=m$->{0..1}.user_$->{1..2}
# HASH_MOD分库
spring.shardingsphere.rules.sharding.tables.user.database-strategy.standard.sharding-column=userid
spring.shardingsphere.rules.sharding.tables.user.database-strategy.standard.sharding-algorithm-name=user_db_alg
spring.shardingsphere.rules.sharding.sharding-algorithms.user_db_alg.type=HASH_MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.user_db_alg.props.sharding-count=2
# HASH_MOD分表
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=userid
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=user_tbl_alg
spring.shardingsphere.rules.sharding.sharding-algorithms.user_tbl_alg.type=INLINE
# 字符串类型要先hashcode转为long，再取模。但是Grovvy的 "xxx".hashcode%2 不知道为什么会产⽣ -1,0,1三种结果
#spring.shardingsphere.rules.sharding.sharding-algorithms.user_tbl_alg.props.algorithm-expression=user_$->{Math.abs(userid.hashCode()%2) +1}
# ⽤户信息分到四个表
spring.shardingsphere.rules.sharding.sharding-algorithms.user_tbl_alg.props.algorithm-expression=user_$->{Math.abs(userid.hashCode()%4).intdiv(2) +1}
# 数据加密:对password字段进⾏加密
# 存储明⽂的字段
spring.shardingsphere.rules.encrypt.tables.user.columns.password.plainColumn = password
# 存储密⽂的字段
spring.shardingsphere.rules.encrypt.tables.user.columns.password.cipherColumn = password_cipher
# 加密器
spring.shardingsphere.rules.encrypt.tables.user.columns.password.encryptorName = user_password_encry
# AES加密器
#spring.shardingsphere.rules.encrypt.encryptors.user_password_encry.type=AES
#spring.shardingsphere.rules.encrypt.encryptors.user_password_encry.props.aes-key-value=123456
# MD5加密器
#spring.shardingsphere.rules.encrypt.encryptors.user_password_encry.type=MD5
# SM3加密器
spring.shardingsphere.rules.encrypt.encryptors.user_password_encry.type=SM3
spring.shardingsphere.rules.encrypt.encryptors.user_password_encry.props.sm3-salt=12345678
# sm4加密器
#spring.shardingsphere.rules.encrypt.encryptors.user_password_encry.type=SM4

单元测试案例

java

@Test
public void addUser(){
    for (int i = 0; i < 10; i++) {
        User user = new User();
        // user.setUserid();
        user.setUsername("user"+i);
        user.setPassword("123qweasd");
        user.setUserstatus("NORMAL");
        user.setAge(30+i);
        user.setSex(i%2==0?"F":"M");
        userMapper.insert(user);
    }
}

在插⼊时，就会在password_cipher字段中加⼊加密后的密⽂

接下来在查询时，可以主动传⼊password_cipher查询字段，按照密⽂进⾏查询。同时，针对password字段的查询，也会转化成为密⽂查询。查询案例

java

@Test
public void queryUser() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("password","123qweasd");
    List<User> users = userMapper.selectList(queryWrapper);
    for(User user : users){
        System.out.println(user);
    }
}

🚫ShardingSphere 难以处理的查询类型:

多表关联查询（JOIN）

sql

-- 难以处理
SELECT u.name, o.order_no 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.region = 'east';

问题：关联表可能分布在不同的分片甚至不同的物理库中，无法高效执行分布式JOIN

多层嵌套子查询

sql

-- 难以处理
SELECT * FROM products 
WHERE category_id IN (
    SELECT id FROM categories 
    WHERE department_id = (
        SELECT id FROM departments WHERE name = 'Electronics'
    )
);

问题：子查询结果可能涉及多个分片，查询重写和结果合并极其复杂。

DISTINCT 查询

sql

-- 性能极差
SELECT DISTINCT category FROM products;

其他复杂查询: GROUP BY + HAVING 复杂分组、窗口函数（ROW_NUMBER, RANK等）、复杂 UNION 查询、全表扫描类查询

💡 解决方案与最佳实践

1、架构层面调整

采用命令查询职责分离（CQRS）：
- 命令端（写操作）：使用分库分表保证写入性能
- 查询端（读操作）：使用ES、ClickHouse等专门的分析型数据库
数据冗余与宽表设计

sql

-- Instead of JOIN:
SELECT u.name, o.order_no FROM users u JOIN orders o ...

-- Use wide table:
CREATE TABLE user_orders_wide (
    user_id BIGINT,
    user_name VARCHAR,
    order_no VARCHAR,
    region VARCHAR,
    -- 其他冗余字段
    PRIMARY KEY (user_id, order_no)
);

2、应用层面调整

分步查询替代关联查询：

java

// 而不是执行JOIN查询
// 1. 先查询用户ID
List<Long> userIds = userDao.findByRegion("east");
// 2. 再查询这些用户的订单
List<Order> orders = orderDao.findByUserIds(userIds);

使用缓存层：对频繁访问但不经常变化的数据使用Redis等缓存

3、查询设计原则

尽量使用分片键作为查询条件
避免跨分片的数据聚合
使用分页限制结果集大小
考虑最终一致性而不是强一致性

总结：分库分表不是透明的、无代价的技术方案。它要求你在获得扩展性的同时，必须在应用设计和开发习惯上做出相应的调整和妥协。这是一种架构上的有舍有得--放弃复杂的查询灵活性，换取系统的水平扩展能力和海量数据处理能力。

ShardingJDBC分库分表实战指南 ​

分库分表核心概念 ​

垂直分⽚和⽔平分⽚ ​

分库分表案例 ​

引⼊ShardingJDBC快速实现分库分表 ​

ShardingJDBC常⻅数据分⽚策略实战 ​

INLINE简单分⽚（行表达式分片策略） ​

STANDARD标准分⽚ ​

COMPLEX_INLINE复杂分⽚ （复合分片策略） ​

CLASS_BASED⾃定义分⽚ ​

HINT_INLINE强制分⽚ ​

常⽤分⽚算法总结 ​

ShardingJDBC数据加密功能实战 ​

ShardingJDBC分库分表实战指南

分库分表核心概念

垂直分⽚和⽔平分⽚

分库分表案例

引⼊ShardingJDBC快速实现分库分表

ShardingJDBC常⻅数据分⽚策略实战

INLINE简单分⽚（行表达式分片策略）

STANDARD标准分⽚

COMPLEX_INLINE复杂分⽚（复合分片策略）

CLASS_BASED⾃定义分⽚

HINT_INLINE强制分⽚

常⽤分⽚算法总结

ShardingJDBC数据加密功能实战