一、Sharding-JDBC系列01：整合SpringBoot实现分库分表，读写分离

发布时间：2024年01月11日

目录

一、概述

二、案例演示-水平分表

(1)、创建springboot工程

(2)、创建数据库和数据表

(3)、application.yaml配置分片规则??

(4)、测试数据插入、查询操作

4.1、插入-控制台SQL日志?

4.2、查询-控制台SQL日志

?三、案例演示-水平分库

(1)、创建数据库和数据表?

(2)、application.yaml配置分片规则?

(3)、测试数据插入、查询操作

3.1、插入-控制台SQL日志

3.2、查询-控制台SQL日志?

四、案例演示-广播表

(1)、创建数据库和数据表?

(2)、application.yaml配置分片规则

(3)、测试数据插入、查询操作

3.1、插入-控制台SQL日志

3.2、查询-控制台SQL日志?

五、案例演示-绑定表

(1)、创建数据库和数据表

(2)、application.yaml配置分片规则?

(3)、测试数据插入、查询操作

3.1、插入-控制台SQL日志

3.2、查询-控制台SQL日志

六、案例演示-读写分离?

(1)、创建数据库和数据表

?(2)、application.yaml配置分片规则

(3)、测试数据插入、查询操作

3.1、插入-控制台SQL日志

3.2、查询-控制台SQL日志

六、常用配置项说明

6.1、数据分片

6.2、读写分离?

一、概述

Sharding-JDBC定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

  1. 适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
  2. 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
  3. 支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

二、案例演示-水平分表

(1)、创建springboot工程

引入相应的pom.xml依赖，包括mybatis、sharding-jdbc、druid等依赖。我们使用的sharding-jdbc-spring-boot-starter版本为4.1.1。

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
  </dependency>

  <!-- 引入Mybatis依赖-->
  <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
  </dependency>
  
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
  </dependency>
  
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>2.2.5.RELEASE</version>
  </dependency>

  <!--引入sharding-sphere-jdbc依赖-->
  <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
  </dependency>

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
  </dependency>

  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
  </dependency>
</dependencies>

(2)、创建数据库和数据表

image.png

CREATE TABLE `t_order0` (
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_name` varchar(255) DEFAULT NULL COMMENT '订单名称',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order1` (
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_name` varchar(255) DEFAULT NULL COMMENT '订单名称',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

(3)、application.yaml配置分片规则??

spring:
  shardingsphere:
    props:
      sql:
        show: true  # 打印具体的插入SQL
    datasource:
      names: ds0
      ds0: # 数据源的名称
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
    sharding:
      tables:
        t_order: # 逻辑表的名称
          actual-data-nodes: ds0.t_order$->{0..1}   # 真实的数据节点: ds0数据源下的t_order0、t_order1两个表
          table-strategy:
            inline:
              sharding-column: order_id   # 分片字段
              algorithm-expression: t_order$->{order_id % 2}   # 分片规则： order_id为偶数则插入t_order0表，order_id为奇数则插入t_order1表
          key-generator:
            column: order_id   # 主键字段名称
            type: SNOWFLAKE # 主键生成策略：雪花算法

(4)、测试数据插入、查询操作

4.1、插入-控制台SQL日志?

2023-12-19 10:48:05.921  INFO 2424 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`) values (?)
2023-12-19 10:48:05.922  INFO 2424 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@3becc950, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6b9fdbc6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6b9fdbc6, columnNames=[order_name], insertValueContexts=[InsertValueContext(parametersCount=1, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=42, stopIndex=42, parameterMarkerIndex=0), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=1))], parameters=[订单[0]])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943821100398149632])])
2023-12-19 10:48:05.922  INFO 2424 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_name`, order_id) values (?, ?) ::: [订单[0], 943821100398149632]
2023-12-19 10:48:05.997  INFO 2424 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`) values (?)
2023-12-19 10:48:05.997  INFO 2424 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@3becc950, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2a8a3ada), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2a8a3ada, columnNames=[order_name], insertValueContexts=[InsertValueContext(parametersCount=1, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=42, stopIndex=42, parameterMarkerIndex=0), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=1))], parameters=[订单[1]])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943821101710966785])])
2023-12-19 10:48:05.997  INFO 2424 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order1(`order_name`, order_id) values (?, ?) ::: [订单[1], 943821101710966785]
2023-12-19 10:48:06.008  INFO 2424 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`) values (?)
2023-12-19 10:48:06.008  INFO 2424 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@3becc950, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@373e6b9d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@373e6b9d, columnNames=[order_name], insertValueContexts=[InsertValueContext(parametersCount=1, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=42, stopIndex=42, parameterMarkerIndex=0), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=1))], parameters=[订单[2]])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943821101757104128])])
2023-12-19 10:48:06.009  INFO 2424 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_name`, order_id) values (?, ?) ::: [订单[2], 943821101757104128]
2023-12-19 10:48:06.020  INFO 2424 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`) values (?)
2023-12-19 10:48:06.020  INFO 2424 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@3becc950, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@10244722), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@10244722, columnNames=[order_name], insertValueContexts=[InsertValueContext(parametersCount=1, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=42, stopIndex=42, parameterMarkerIndex=0), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=1))], parameters=[订单[3]])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943821101807435777])])
2023-12-19 10:48:06.020  INFO 2424 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order1(`order_name`, order_id) values (?, ?) ::: [订单[3], 943821101807435777]
2023-12-19 10:48:06.032  INFO 2424 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`) values (?)
2023-12-19 10:48:06.033  INFO 2424 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@3becc950, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1946384), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1946384, columnNames=[order_name], insertValueContexts=[InsertValueContext(parametersCount=1, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=42, stopIndex=42, parameterMarkerIndex=0), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=1))], parameters=[订单[4]])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943821101857767424])])
2023-12-19 10:48:06.033  INFO 2424 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_name`, order_id) values (?, ?) ::: [订单[4], 943821101857767424]
2023-12-19 10:48:06.059  INFO 2424 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`) values (?)
2023-12-19 10:48:06.059  INFO 2424 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@3becc950, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3c952a33), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3c952a33, columnNames=[order_name], insertValueContexts=[InsertValueContext(parametersCount=1, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=42, stopIndex=42, parameterMarkerIndex=0), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=1))], parameters=[订单[5]])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943821101971013633])])
2023-12-19 10:48:06.059  INFO 2424 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order1(`order_name`, order_id) values (?, ?) ::: [订单[5], 943821101971013633]
2023-12-19 10:48:06.070  INFO 2424 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`) values (?)
2023-12-19 10:48:06.070  INFO 2424 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@3becc950, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@654e6a90), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@654e6a90, columnNames=[order_name], insertValueContexts=[InsertValueContext(parametersCount=1, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=42, stopIndex=42, parameterMarkerIndex=0), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=1))], parameters=[订单[6]])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943821102017150976])])
2023-12-19 10:48:06.070  INFO 2424 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_name`, order_id) values (?, ?) ::: [订单[6], 943821102017150976]
2023-12-19 10:48:06.082  INFO 2424 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`) values (?)
2023-12-19 10:48:06.083  INFO 2424 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@3becc950, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4be0a27d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4be0a27d, columnNames=[order_name], insertValueContexts=[InsertValueContext(parametersCount=1, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=42, stopIndex=42, parameterMarkerIndex=0), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=1))], parameters=[订单[7]])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943821102063288321])])
2023-12-19 10:48:06.083  INFO 2424 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order1(`order_name`, order_id) values (?, ?) ::: [订单[7], 943821102063288321]
2023-12-19 10:48:06.095  INFO 2424 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`) values (?)
2023-12-19 10:48:06.095  INFO 2424 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@3becc950, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@c02670f), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@c02670f, columnNames=[order_name], insertValueContexts=[InsertValueContext(parametersCount=1, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=42, stopIndex=42, parameterMarkerIndex=0), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=1))], parameters=[订单[8]])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943821102122008576])])
2023-12-19 10:48:06.095  INFO 2424 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_name`, order_id) values (?, ?) ::: [订单[8], 943821102122008576]
2023-12-19 10:48:06.106  INFO 2424 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`) values (?)
2023-12-19 10:48:06.106  INFO 2424 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@3becc950, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4dcbae55), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4dcbae55, columnNames=[order_name], insertValueContexts=[InsertValueContext(parametersCount=1, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=42, stopIndex=42, parameterMarkerIndex=0), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=1))], parameters=[订单[9]])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943821102168145921])])
2023-12-19 10:48:06.106  INFO 2424 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order1(`order_name`, order_id) values (?, ?) ::: [订单[9], 943821102168145921]

4.2、查询-控制台SQL日志

2023-12-19 11:11:27.286  INFO 23876 --- [           main] ShardingSphere-SQL                       : Logic SQL: select * from t_order
         WHERE  order_id = ?
2023-12-19 11:11:27.286  INFO 23876 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7a1b8a46), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7a1b8a46, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[ColumnProjection(owner=null, name=order_id, alias=Optional.empty), ColumnProjection(owner=null, name=order_name, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@2921199d, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@3d40a3b4, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@7f13811b, containsSubquery=false)
2023-12-19 11:11:27.287  INFO 23876 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select * from t_order1
         WHERE  order_id = ? ::: [943821102063288321]
Order{orderId=943821102063288321, orderName='订单[7]'}

?三、案例演示-水平分库


?

(1)、创建数据库和数据表?

CREATE TABLE `t_order0` (
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_name` varchar(255) DEFAULT NULL COMMENT '订单名称',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order1` (
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_name` varchar(255) DEFAULT NULL COMMENT '订单名称',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

(2)、application.yaml配置分片规则

spring:
  shardingsphere:
    props:
      sql:
        show: true  # 打印具体的插入SQL
    datasource:
      names: ds0,ds1
      ds0: # 数据源的名称
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
      ds1: # 数据源的名称
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
    sharding:
      tables:
        t_order: # 逻辑表的名称
          actual-data-nodes: ds$->{0..1}.t_order$->{0..1}   # 真实的数据节点: ds0数据源下的t_order0、t_order1两个表
          table-strategy:
            inline:
              sharding-column: order_id   # 表分片字段
              algorithm-expression: t_order$->{order_id % 2}   # 分片规则： order_id为偶数则插入t_order0表，order_id为奇数则插入t_order1表
          database-strategy:
            inline:
              sharding-column: user_id    # 分库字段
              algorithm-expression: ds$->{user_id % 2}   # 分库规则：user_id为偶数则插入ds0，user_id为奇数则插入ds1
          key-generator:
            column: order_id   # 主键字段名称
            type: SNOWFLAKE # 主键生成策略：雪花算法
      default-data-source-name: ds0  # 默认数据源
      default-table-strategy:  # 默认分表策略
        none:

(3)、测试数据插入、查询操作

3.1、插入-控制台SQL日志

2023-12-19 13:36:51.907  INFO 26216 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-19 13:36:51.907  INFO 26216 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6fa2448b, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@61bb1e4d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@61bb1e4d, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[0], 93])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943863572021116928])])
2023-12-19 13:36:51.908  INFO 26216 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order0(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[0], 93, 943863572021116928]
2023-12-19 13:36:51.975  INFO 26216 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-19 13:36:51.975  INFO 26216 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6fa2448b, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@da4cf09), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@da4cf09, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[1], 45])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943863573136801793])])
2023-12-19 13:36:51.975  INFO 26216 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order1(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[1], 45, 943863573136801793]
2023-12-19 13:36:51.988  INFO 26216 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-19 13:36:51.988  INFO 26216 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6fa2448b, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@789c3057), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@789c3057, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[2], 98])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943863573195522048])])
2023-12-19 13:36:51.988  INFO 26216 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[2], 98, 943863573195522048]
2023-12-19 13:36:51.999  INFO 26216 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-19 13:36:51.999  INFO 26216 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6fa2448b, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@679dd234), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@679dd234, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[3], 85])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943863573241659393])])
2023-12-19 13:36:51.999  INFO 26216 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order1(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[3], 85, 943863573241659393]
2023-12-19 13:36:52.010  INFO 26216 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-19 13:36:52.010  INFO 26216 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6fa2448b, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4c3de38e), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4c3de38e, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[4], 26])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943863573283602432])])
2023-12-19 13:36:52.011  INFO 26216 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[4], 26, 943863573283602432]
2023-12-19 13:36:52.023  INFO 26216 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-19 13:36:52.023  INFO 26216 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6fa2448b, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3d8d17a3), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3d8d17a3, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[5], 29])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943863573342322689])])
2023-12-19 13:36:52.023  INFO 26216 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order1(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[5], 29, 943863573342322689]
2023-12-19 13:36:52.033  INFO 26216 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-19 13:36:52.033  INFO 26216 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6fa2448b, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b5c6a30), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b5c6a30, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[6], 88])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943863573384265728])])
2023-12-19 13:36:52.034  INFO 26216 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[6], 88, 943863573384265728]
2023-12-19 13:36:52.046  INFO 26216 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-19 13:36:52.046  INFO 26216 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6fa2448b, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3d40a3b4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3d40a3b4, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[7], 45])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943863573434597377])])
2023-12-19 13:36:52.046  INFO 26216 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order1(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[7], 45, 943863573434597377]
2023-12-19 13:36:52.056  INFO 26216 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-19 13:36:52.056  INFO 26216 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6fa2448b, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@40d52be7), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@40d52be7, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[8], 31])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943863573480734720])])
2023-12-19 13:36:52.057  INFO 26216 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order0(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[8], 31, 943863573480734720]
2023-12-19 13:36:52.067  INFO 26216 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-19 13:36:52.067  INFO 26216 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6fa2448b, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b791a81), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b791a81, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[9], 48])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[943863573526872065])])
2023-12-19 13:36:52.067  INFO 26216 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order1(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[9], 48, 943863573526872065]

3.2、查询-控制台SQL日志?

2023-12-19 13:41:17.750  INFO 21876 --- [           main] ShardingSphere-SQL                       : Logic SQL: select * from t_order
         WHERE  order_id = ?
2023-12-19 13:41:17.751  INFO 21876 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@28279a49, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@384fda47), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@384fda47, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[ColumnProjection(owner=null, name=order_id, alias=Optional.empty), ColumnProjection(owner=null, name=order_name, alias=Optional.empty), ColumnProjection(owner=null, name=user_id, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@3663d0ba, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@575cabf0, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@433d9680, containsSubquery=false)
2023-12-19 13:41:17.751  INFO 21876 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select * from t_order1
         WHERE  order_id = ? ::: [943863573526872065]
2023-12-19 13:41:17.752  INFO 21876 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select * from t_order1
         WHERE  order_id = ? ::: [943863573526872065]
Order{orderId=943863573526872065, orderName='订单[9]', userId=48}

四、案例演示-广播表

广播表指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致，跟MyCat中的全局表类似。广播表适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。?

(1)、创建数据库和数据表?

CREATE TABLE `t_dictionary` (
  `id` varchar(255) NOT NULL COMMENT '主键ID',
  `code` varchar(255) DEFAULT NULL COMMENT '字典代码',
  `value` varchar(255) DEFAULT NULL COMMENT '字典值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

(2)、application.yaml配置分片规则

spring:
  shardingsphere:
    props:
      sql:
        show: true  # 打印具体的插入SQL
    datasource:
      names: ds0,ds1
      ds0: # 数据源的名称
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
      ds1: # 数据源的名称
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
    sharding:
      tables:
        t_order: # 逻辑表的名称
          actual-data-nodes: ds$->{0..1}.t_order$->{0..1}   # 真实的数据节点: ds0数据源下的t_order0、t_order1两个表
          table-strategy:
            inline:
              sharding-column: order_id   # 表分片字段
              algorithm-expression: t_order$->{order_id % 2}   # 分片规则： order_id为偶数则插入t_order0表，order_id为奇数则插入t_order1表
          database-strategy:
            inline:
              sharding-column: user_id    # 分库字段
              algorithm-expression: ds$->{user_id % 2}   # 分库规则：user_id为偶数则插入ds0，user_id为奇数则插入ds1
          key-generator:
            column: order_id   # 主键字段名称
            type: SNOWFLAKE # 主键生成策略：雪花算法
        t_dictionary:  # 全局广播表
          key-generator:
            column: id   # 主键字段名称
            type: UUID # 主键生成策略：UUID
      default-data-source-name: ds0  # 默认数据源
      default-table-strategy:  # 默认分表策略
        none:
      broadcast-tables: t_dictionary   # 广播表

(3)、测试数据插入、查询操作

3.1、插入-控制台SQL日志

2023-12-19 14:10:31.266  INFO 26564 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_dictionary(`code`,  `value`) values (?, ?)
2023-12-19 14:10:31.266  INFO 26564 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31973858, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@65514add), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@65514add, columnNames=[code, value], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=51, stopIndex=51, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[order.type, normal])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=id, generated=true, generatedValues=[b3151dcdbaef4fe5b7d0fecb4a7d7d28])])
2023-12-19 14:10:31.267  INFO 26564 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_dictionary(`code`,  `value`, id) values (?, ?, ?) ::: [order.type, normal, b3151dcdbaef4fe5b7d0fecb4a7d7d28]
2023-12-19 14:10:31.267  INFO 26564 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_dictionary(`code`,  `value`, id) values (?, ?, ?) ::: [order.type, normal, b3151dcdbaef4fe5b7d0fecb4a7d7d28]
3.2、查询-控制台SQL日志?
2023-12-19 14:16:56.928  INFO 24080 --- [           main] ShardingSphere-SQL                       : Logic SQL: select * from t_dictionary
         WHERE  id = ?
2023-12-19 14:16:56.928  INFO 24080 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@3730f716, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7730ef88), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7730ef88, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=code, alias=Optional.empty), ColumnProjection(owner=null, name=value, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@66f5a02e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4e3f2908, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@7e87ef9e, containsSubquery=false)
2023-12-19 14:16:56.929  INFO 24080 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select * from t_dictionary
         WHERE  id = ? ::: [b3151dcdbaef4fe5b7d0fecb4a7d7d28]
Dictionary{id=b3151dcdbaef4fe5b7d0fecb4a7d7d28, code='order.type', value='normal'}

五、案例演示-绑定表

绑定表指分片规则一致的主表和子表。例如：t_order表和t_order_item表，均按照order_id分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明，如果SQL为：?

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

在不配置绑定表关系时，假设分片键order_id将数值10路由至第0片，将数值11路由至第1片，那么路由后的SQL应该为4条，它们呈现为笛卡尔积：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

在配置绑定表关系后，路由的SQL应该为2条：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

其中t_order在FROM的最左侧，ShardingSphere将会以它作为整个绑定表的主表。 所有路由计算将会只使用主表的策略，那么t_order_item表的分片计算将会使用t_order的条件。故绑定表之间的分区键要完全相同。下面我们通过示例来演示如何使用绑定表。

(1)、创建数据库和数据表

CREATE TABLE `t_order0` (
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_name` varchar(255) DEFAULT NULL COMMENT '订单名称',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order1` (
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_name` varchar(255) DEFAULT NULL COMMENT '订单名称',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order_item0` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `order_id` bigint(20) DEFAULT NULL COMMENT '订单id',
  `goods_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order_item1` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `order_id` bigint(20) DEFAULT NULL COMMENT '订单id',
  `goods_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

(2)、application.yaml配置分片规则

spring:
  shardingsphere:
    props:
      sql:
        show: true  # 打印具体的插入SQL
    datasource:
      names: ds0,ds1
      ds0: # 数据源的名称
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
      ds1: # 数据源的名称
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
    sharding:
      tables:
        t_order: # 逻辑表的名称
          actual-data-nodes: ds$->{0..1}.t_order$->{0..1}   # 真实的数据节点
          table-strategy:
            inline:
              sharding-column: order_id   # 表分片字段
              algorithm-expression: t_order$->{order_id % 2}   # 分片规则
          database-strategy:
            inline:
              sharding-column: user_id    # 分库字段
              algorithm-expression: ds$->{user_id % 2}   # 分库规则
        t_order_item: # 逻辑表的名称
          actual-data-nodes: ds$->{0..1}.t_order_item$->{0..1}   # 真实的数据节点
          table-strategy:
            inline:
              sharding-column: order_id   # 表分片字段
              algorithm-expression: t_order_item$->{order_id % 2}   # 分片规则
          database-strategy:
            inline:
              sharding-column: user_id    # 分库字段
              algorithm-expression: ds$->{user_id % 2}   # 分库规则
      binding-tables: # 绑定表配置
        - t_order,t_order_item

(3)、测试数据插入、查询操作

3.1、插入-控制台SQL日志

从日志中可以看到，绑定表的两个表的数据，都是选择的同一个数据库进行插入，简单理解，就是相同order_id的t_order_item的数据以及t_order的数据是存在在同一个数据库中的，这样做关联查询时，就方便很多。?

2023-12-20 16:25:33.432  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_id`, `order_name`,  `user_id`) values (?, ?, ?)
2023-12-20 16:25:33.432  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6588b715, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@15a3b42), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@15a3b42, columnNames=[order_id, order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=2)], parameters=[89170, 订单[0], 97])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.433  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order0(`order_id`, `order_name`,  `user_id`) values (?, ?, ?) ::: [89170, 订单[0], 97]
2023-12-20 16:25:33.546  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order_item(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?,?, ?)
2023-12-20 16:25:33.547  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@483f286e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4bb147ec), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4bb147ec, columnNames=[id, order_id, user_id, goods_name], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=75, stopIndex=75, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=78, stopIndex=78, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=83, stopIndex=83, parameterMarkerIndex=3)], parameters=[11293, 89170, 97, 商品[0]])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.547  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order_item0(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?, ?, ?) ::: [11293, 89170, 97, 商品[0]]
2023-12-20 16:25:33.560  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_id`, `order_name`,  `user_id`) values (?, ?, ?)
2023-12-20 16:25:33.561  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6588b715, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@186d8a71), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@186d8a71, columnNames=[order_id, order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=2)], parameters=[24234, 订单[1], 98])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.561  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_id`, `order_name`,  `user_id`) values (?, ?, ?) ::: [24234, 订单[1], 98]
2023-12-20 16:25:33.575  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order_item(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?,?, ?)
2023-12-20 16:25:33.575  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@483f286e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@cde8c6c), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@cde8c6c, columnNames=[id, order_id, user_id, goods_name], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=75, stopIndex=75, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=78, stopIndex=78, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=83, stopIndex=83, parameterMarkerIndex=3)], parameters=[98389, 24234, 98, 商品[1]])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.576  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_item0(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?, ?, ?) ::: [98389, 24234, 98, 商品[1]]
2023-12-20 16:25:33.585  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_id`, `order_name`,  `user_id`) values (?, ?, ?)
2023-12-20 16:25:33.586  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6588b715, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4aeb0e2b), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4aeb0e2b, columnNames=[order_id, order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=2)], parameters=[92696, 订单[2], 90])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.586  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_id`, `order_name`,  `user_id`) values (?, ?, ?) ::: [92696, 订单[2], 90]
2023-12-20 16:25:33.596  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order_item(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?,?, ?)
2023-12-20 16:25:33.597  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@483f286e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@13ed066e), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@13ed066e, columnNames=[id, order_id, user_id, goods_name], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=75, stopIndex=75, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=78, stopIndex=78, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=83, stopIndex=83, parameterMarkerIndex=3)], parameters=[29372, 92696, 90, 商品[2]])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.597  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_item0(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?, ?, ?) ::: [29372, 92696, 90, 商品[2]]
2023-12-20 16:25:33.609  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_id`, `order_name`,  `user_id`) values (?, ?, ?)
2023-12-20 16:25:33.609  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6588b715, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@626d2016), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@626d2016, columnNames=[order_id, order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=2)], parameters=[66617, 订单[3], 9])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.610  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order1(`order_id`, `order_name`,  `user_id`) values (?, ?, ?) ::: [66617, 订单[3], 9]
2023-12-20 16:25:33.624  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order_item(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?,?, ?)
2023-12-20 16:25:33.624  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@483f286e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1c240cf2), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1c240cf2, columnNames=[id, order_id, user_id, goods_name], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=75, stopIndex=75, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=78, stopIndex=78, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=83, stopIndex=83, parameterMarkerIndex=3)], parameters=[75081, 66617, 9, 商品[3]])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.625  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order_item1(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?, ?, ?) ::: [75081, 66617, 9, 商品[3]]
2023-12-20 16:25:33.641  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_id`, `order_name`,  `user_id`) values (?, ?, ?)
2023-12-20 16:25:33.641  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6588b715, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@64763e49), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@64763e49, columnNames=[order_id, order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=2)], parameters=[12607, 订单[4], 59])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.641  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order1(`order_id`, `order_name`,  `user_id`) values (?, ?, ?) ::: [12607, 订单[4], 59]
2023-12-20 16:25:33.654  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order_item(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?,?, ?)
2023-12-20 16:25:33.654  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@483f286e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2b53840a), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2b53840a, columnNames=[id, order_id, user_id, goods_name], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=75, stopIndex=75, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=78, stopIndex=78, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=83, stopIndex=83, parameterMarkerIndex=3)], parameters=[97807, 12607, 59, 商品[4]])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.654  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order_item1(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?, ?, ?) ::: [97807, 12607, 59, 商品[4]]
2023-12-20 16:25:33.667  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_id`, `order_name`,  `user_id`) values (?, ?, ?)
2023-12-20 16:25:33.667  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6588b715, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@f2276c9), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@f2276c9, columnNames=[order_id, order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=2)], parameters=[63215, 订单[5], 86])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.667  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order1(`order_id`, `order_name`,  `user_id`) values (?, ?, ?) ::: [63215, 订单[5], 86]
2023-12-20 16:25:33.681  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order_item(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?,?, ?)
2023-12-20 16:25:33.681  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@483f286e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1c61eda5), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1c61eda5, columnNames=[id, order_id, user_id, goods_name], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=75, stopIndex=75, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=78, stopIndex=78, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=83, stopIndex=83, parameterMarkerIndex=3)], parameters=[13329, 63215, 86, 商品[5]])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.682  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_item1(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?, ?, ?) ::: [13329, 63215, 86, 商品[5]]
2023-12-20 16:25:33.697  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_id`, `order_name`,  `user_id`) values (?, ?, ?)
2023-12-20 16:25:33.697  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6588b715, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5ae15), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5ae15, columnNames=[order_id, order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=2)], parameters=[15330, 订单[6], 84])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.697  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_id`, `order_name`,  `user_id`) values (?, ?, ?) ::: [15330, 订单[6], 84]
2023-12-20 16:25:33.708  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order_item(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?,?, ?)
2023-12-20 16:25:33.709  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@483f286e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7f0b93b4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7f0b93b4, columnNames=[id, order_id, user_id, goods_name], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=75, stopIndex=75, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=78, stopIndex=78, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=83, stopIndex=83, parameterMarkerIndex=3)], parameters=[59338, 15330, 84, 商品[6]])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.709  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_item0(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?, ?, ?) ::: [59338, 15330, 84, 商品[6]]
2023-12-20 16:25:33.720  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_id`, `order_name`,  `user_id`) values (?, ?, ?)
2023-12-20 16:25:33.720  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6588b715, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4bc6da03), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4bc6da03, columnNames=[order_id, order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=2)], parameters=[81720, 订单[7], 24])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.721  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_id`, `order_name`,  `user_id`) values (?, ?, ?) ::: [81720, 订单[7], 24]
2023-12-20 16:25:33.732  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order_item(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?,?, ?)
2023-12-20 16:25:33.733  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@483f286e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@61cda923), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@61cda923, columnNames=[id, order_id, user_id, goods_name], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=75, stopIndex=75, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=78, stopIndex=78, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=83, stopIndex=83, parameterMarkerIndex=3)], parameters=[94637, 81720, 24, 商品[7]])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.733  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_item0(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?, ?, ?) ::: [94637, 81720, 24, 商品[7]]
2023-12-20 16:25:33.744  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_id`, `order_name`,  `user_id`) values (?, ?, ?)
2023-12-20 16:25:33.744  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6588b715, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@77cb452c), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@77cb452c, columnNames=[order_id, order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=2)], parameters=[32169, 订单[8], 68])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.745  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order1(`order_id`, `order_name`,  `user_id`) values (?, ?, ?) ::: [32169, 订单[8], 68]
2023-12-20 16:25:33.754  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order_item(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?,?, ?)
2023-12-20 16:25:33.755  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@483f286e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@42ea7565), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@42ea7565, columnNames=[id, order_id, user_id, goods_name], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=75, stopIndex=75, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=78, stopIndex=78, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=83, stopIndex=83, parameterMarkerIndex=3)], parameters=[95780, 32169, 68, 商品[8]])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.755  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_item1(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?, ?, ?) ::: [95780, 32169, 68, 商品[8]]
2023-12-20 16:25:33.764  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_id`, `order_name`,  `user_id`) values (?, ?, ?)
2023-12-20 16:25:33.765  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6588b715, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@317890ea), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@317890ea, columnNames=[order_id, order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=2)], parameters=[76732, 订单[9], 40])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.765  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order0(`order_id`, `order_name`,  `user_id`) values (?, ?, ?) ::: [76732, 订单[9], 40]
2023-12-20 16:25:33.774  INFO 23900 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order_item(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?,?, ?)
2023-12-20 16:25:33.775  INFO 23900 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@483f286e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@422ad5e2), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@422ad5e2, columnNames=[id, order_id, user_id, goods_name], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=75, stopIndex=75, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=78, stopIndex=78, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=83, stopIndex=83, parameterMarkerIndex=3)], parameters=[51080, 76732, 40, 商品[9]])], generatedKeyContext=Optional.empty)
2023-12-20 16:25:33.775  INFO 23900 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_item0(`id`,`order_id`, `user_id`, `goods_name`) values (?, ?, ?, ?) ::: [51080, 76732, 40, 商品[9]]
3.2、查询-控制台SQL日志

可以看到，绑定表在关联查询的时候避免了笛卡尔积关联。

2023-12-20 16:42:22.002  INFO 8840 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT o.order_id, o.order_name, o.user_id, i.goods_name FROM
        t_order o JOIN t_order_item i
        ON o.order_id=i.order_id
         WHERE  o.order_id in (
                        ?
                ,
                        ?
                )
2023-12-20 16:42:22.002  INFO 8840 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@38fb50f8, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7ab802f4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7ab802f4, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=55, distinctRow=false, projections=[ColumnProjection(owner=o, name=order_id, alias=Optional.empty), ColumnProjection(owner=o, name=order_name, alias=Optional.empty), ColumnProjection(owner=o, name=user_id, alias=Optional.empty), ColumnProjection(owner=i, name=goods_name, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@608cd501, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@3b97907c, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@59096b66, containsSubquery=false)
2023-12-20 16:42:22.003  INFO 8840 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT o.order_id, o.order_name, o.user_id, i.goods_name FROM
        t_order0 o JOIN t_order_item0 i
        ON o.order_id=i.order_id
         WHERE  o.order_id in (
                        ?
                ,
                        ?
                ) ::: [89170, 66617]
2023-12-20 16:42:22.003  INFO 8840 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT o.order_id, o.order_name, o.user_id, i.goods_name FROM
        t_order1 o JOIN t_order_item1 i
        ON o.order_id=i.order_id
         WHERE  o.order_id in (
                        ?
                ,
                        ?
                ) ::: [89170, 66617]
2023-12-20 16:42:22.003  INFO 8840 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT o.order_id, o.order_name, o.user_id, i.goods_name FROM
        t_order0 o JOIN t_order_item0 i
        ON o.order_id=i.order_id
         WHERE  o.order_id in (
                        ?
                ,
                        ?
                ) ::: [89170, 66617]
2023-12-20 16:42:22.004  INFO 8840 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT o.order_id, o.order_name, o.user_id, i.goods_name FROM
        t_order1 o JOIN t_order_item1 i
        ON o.order_id=i.order_id
         WHERE  o.order_id in (
                        ?
                ,
                        ?
                ) ::: [89170, 66617]
[Order{orderId=89170, orderName='订单[0]', userId=97, goodsName='商品[0]'}, Order{orderId=66617, orderName='订单[3]', userId=9, goodsName='商品[3]'}]

六、案例演示-读写分离?

(1)、创建数据库和数据表

依次创建四个数据库，并做好相应的MySQL主从关系，需要注意的是，sharding-jdbc并不负责MySQL的主从配置以及主从节点间的数据同步，这些都是需要我们事先配置好的。?

CREATE TABLE `t_order0` (
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_name` varchar(255) DEFAULT NULL COMMENT '订单名称',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order1` (
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_name` varchar(255) DEFAULT NULL COMMENT '订单名称',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

?(2)、application.yaml配置分片规则

spring:
  shardingsphere:
    props:
      sql:
        show: true  # 打印具体的插入SQL
    datasource:
      names: master0,master1,master0slave,master1slave
      master0: # 数据源的名称。配置的格式为spring.shardingsphere.datasource.${datasource-name}.type等
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
      master1: # 数据源的名称。配置的格式为spring.shardingsphere.datasource.${datasource-name}.type等
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
      master0slave: # order_db0的从库数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db0_slave?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
      master1slave: # order_db1的从库数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db1_slave?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: "0905"
    sharding:
      tables:
        t_order: # 逻辑表的名称  配置的格式为spring.shardingsphere.sharding.tables.${table-name}.actual-data-nodes等
          actual-data-nodes: ds$->{0..1}.t_order$->{0..1}   # 真实的数据节点, 注意，ds$->{0..1}对应master-slave-rules配置项的主从配置名称
          table-strategy:
            inline:
              sharding-column: order_id   # 表分片字段
              algorithm-expression: t_order$->{order_id % 2}   # 分片规则： order_id为偶数则插入t_order0表，order_id为奇数则插入t_order1表
          database-strategy:
            inline:
              sharding-column: user_id    # 分库字段
              algorithm-expression: ds$->{user_id % 2}   # 分库规则 注意，ds对应master-slave-rules配置项的主从配置名称
          key-generator: 
            column: order_id   # 主键字段名称
            type: SNOWFLAKE # 主键生成策略：雪花算法
      master-slave-rules:  # #指定谁是主库谁是从库，可以看到从库的属性是slave-data-source-names,表明支持多个从库
        ds0:
          master-data-source-name: master0
          slave-data-source-names: master0slave
          load-balance-algorithm-type: ROUND_ROBIN   # 负载均衡策略
        ds1:
          master-data-source-name: master1
          slave-data-source-names: master1slave
          load-balance-algorithm-type: ROUND_ROBIN   # 负载均衡策略

(3)、测试数据插入、查询操作

3.1、插入-控制台SQL日志

可以看到SQL路由信息，所有的插入操作都是发往master0或者master1主节点，并不会发往slave从节点。

  1. 满足分库规则：user_id为偶数，插入到master0库，user_id为奇数，插入到master1库。
  2. 满足分表规则：order_id为偶数，插入到t_order0表，order_id为奇数，插入到t_order1表。
2023-12-20 09:24:12.551  INFO 18044 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-20 09:24:12.552  INFO 18044 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7a1b8a46), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7a1b8a46, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[0], 74])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[944162376834351104])])
2023-12-20 09:24:12.553  INFO 18044 --- [           main] ShardingSphere-SQL                       : Actual SQL: master0 ::: insert into t_order0(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[0], 74, 944162376834351104]
2023-12-20 09:24:12.637  INFO 18044 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-20 09:24:12.638  INFO 18044 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1e54a6b1), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1e54a6b1, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[1], 74])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[944162378134585345])])
2023-12-20 09:24:12.638  INFO 18044 --- [           main] ShardingSphere-SQL                       : Actual SQL: master0 ::: insert into t_order1(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[1], 74, 944162378134585345]
2023-12-20 09:24:12.652  INFO 18044 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-20 09:24:12.653  INFO 18044 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4e245739), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4e245739, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[2], 84])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[944162378201694208])])
2023-12-20 09:24:12.653  INFO 18044 --- [           main] ShardingSphere-SQL                       : Actual SQL: master0 ::: insert into t_order0(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[2], 84, 944162378201694208]
2023-12-20 09:24:12.664  INFO 18044 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-20 09:24:12.664  INFO 18044 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2f20f7ad), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2f20f7ad, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[3], 69])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[944162378247831553])])
2023-12-20 09:24:12.665  INFO 18044 --- [           main] ShardingSphere-SQL                       : Actual SQL: master1 ::: insert into t_order1(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[3], 69, 944162378247831553]
2023-12-20 09:24:12.695  INFO 18044 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-20 09:24:12.695  INFO 18044 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@637791d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@637791d, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[4], 71])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[944162378377854976])])
2023-12-20 09:24:12.696  INFO 18044 --- [           main] ShardingSphere-SQL                       : Actual SQL: master1 ::: insert into t_order0(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[4], 71, 944162378377854976]
2023-12-20 09:24:12.710  INFO 18044 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-20 09:24:12.710  INFO 18044 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18b6d3c1), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18b6d3c1, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[5], 50])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[944162378440769537])])
2023-12-20 09:24:12.711  INFO 18044 --- [           main] ShardingSphere-SQL                       : Actual SQL: master0 ::: insert into t_order1(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[5], 50, 944162378440769537]
2023-12-20 09:24:12.722  INFO 18044 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-20 09:24:12.722  INFO 18044 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6dce59e), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6dce59e, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[6], 19])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[944162378491101184])])
2023-12-20 09:24:12.723  INFO 18044 --- [           main] ShardingSphere-SQL                       : Actual SQL: master1 ::: insert into t_order0(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[6], 19, 944162378491101184]
2023-12-20 09:24:12.736  INFO 18044 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-20 09:24:12.736  INFO 18044 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3ad8717d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3ad8717d, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[7], 80])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[944162378549821441])])
2023-12-20 09:24:12.736  INFO 18044 --- [           main] ShardingSphere-SQL                       : Actual SQL: master0 ::: insert into t_order1(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[7], 80, 944162378549821441]
2023-12-20 09:24:12.749  INFO 18044 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-20 09:24:12.749  INFO 18044 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5e593b08), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5e593b08, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[8], 95])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[944162378600153088])])
2023-12-20 09:24:12.749  INFO 18044 --- [           main] ShardingSphere-SQL                       : Actual SQL: master1 ::: insert into t_order0(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[8], 95, 944162378600153088]
2023-12-20 09:24:12.759  INFO 18044 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order(`order_name`,  `user_id`) values (?, ?)
2023-12-20 09:24:12.759  INFO 18044 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@43f03c23, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3d53e6f7), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3d53e6f7, columnNames=[order_name, user_id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=57, stopIndex=57, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[订单[9], 59])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=order_id, generated=true, generatedValues=[944162378646290433])])
2023-12-20 09:24:12.759  INFO 18044 --- [           main] ShardingSphere-SQL                       : Actual SQL: master1 ::: insert into t_order1(`order_name`,  `user_id`, order_id) values (?, ?, ?) ::: [订单[9], 59, 944162378646290433]
3.2、查询-控制台SQL日志

可以观察到SQL路由信息，查询操作发往的是master0slave、master1slave从库，可以分担主库压力，这就是读写分离。?

2023-12-20 09:31:19.571  INFO 17064 --- [           main] ShardingSphere-SQL                       : Logic SQL: select * from t_order
         WHERE  order_id = ?
2023-12-20 09:31:19.572  INFO 17064 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@e9474f, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1c61eda5), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1c61eda5, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[ColumnProjection(owner=null, name=order_id, alias=Optional.empty), ColumnProjection(owner=null, name=order_name, alias=Optional.empty), ColumnProjection(owner=null, name=user_id, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@1859ffda, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@59838256, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@131a7516, containsSubquery=false)
2023-12-20 09:31:19.573  INFO 17064 --- [           main] ShardingSphere-SQL                       : Actual SQL: master0slave ::: select * from t_order0
         WHERE  order_id = ? ::: [944162376834351104]
2023-12-20 09:31:19.573  INFO 17064 --- [           main] ShardingSphere-SQL                       : Actual SQL: master1slave ::: select * from t_order0
         WHERE  order_id = ? ::: [944162376834351104]

六、常用配置项说明

6.1、数据分片

dataSources: #数据源配置，可配置多个data_source_name
  <data_source_name>: #<!!数据库连接池实现类> `!!`表示实例化该类
    driverClassName: #数据库驱动类名
    url: #数据库url连接
    username: #数据库用户名
    password: #数据库密码
    # ... 数据库连接池的其它属性

shardingRule:
  tables: #数据分片规则配置，可配置多个logic_table_name
    <logic_table_name>: #逻辑表名称
      actualDataNodes: #由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
        
      databaseStrategy: #分库策略，缺省表示使用默认分库策略，以下的分片策略只能选其一
        standard: #用于单分片键的标准分片场景
          shardingColumn: #分片列名称
          preciseAlgorithmClassName: #精确分片算法类名称，用于=和IN。。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
          rangeAlgorithmClassName: #范围分片算法类名称，用于BETWEEN，可选。。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
        complex: #用于多分片键的复合分片场景
          shardingColumns: #分片列名称，多个列以逗号分隔
          algorithmClassName: #复合分片算法类名称。该类需实现ComplexKeysShardingAlgorithm接口并提供无参数的构造器
        inline: #行表达式分片策略
          shardingColumn: #分片列名称
          algorithmInlineExpression: #分片算法行表达式，需符合groovy语法
        hint: #Hint分片策略
          algorithmClassName: #Hint分片算法类名称。该类需实现HintShardingAlgorithm接口并提供无参数的构造器
        none: #不分片
      tableStrategy: #分表策略，同分库策略
      keyGenerator: 
        column: #自增列名称，缺省表示不使用自增主键生成器
        type: #自增列值生成器类型，缺省表示使用默认自增列值生成器。可使用用户自定义的列值生成器或选择内置类型：SNOWFLAKE/UUID
        props: #属性配置, 注意：使用SNOWFLAKE算法，需要配置worker.id与max.tolerate.time.difference.milliseconds属性。若使用此算法生成值作分片值，建议配置max.vibration.offset属性
          <property-name>: 属性名称
      
  bindingTables: #绑定表规则列表
  - <logic_table_name1, logic_table_name2, ...> 
  - <logic_table_name3, logic_table_name4, ...>
  - <logic_table_name_x, logic_table_name_y, ...>
  broadcastTables: #广播表规则列表
  - table_name1
  - table_name2
  - table_name_x
  
  defaultDataSourceName: #未配置分片规则的表将通过默认数据源定位  
  defaultDatabaseStrategy: #默认数据库分片策略，同分库策略
  defaultTableStrategy: #默认表分片策略，同分库策略
  defaultKeyGenerator: #默认的主键生成算法 如果没有设置,默认为SNOWFLAKE算法
    type: #默认自增列值生成器类型，缺省将使用org.apache.shardingsphere.core.keygen.generator.impl.SnowflakeKeyGenerator。可使用用户自定义的列值生成器或选择内置类型：SNOWFLAKE/UUID
    props:
      <property-name>: #自增列值生成器属性配置, 比如SNOWFLAKE算法的worker.id与max.tolerate.time.difference.milliseconds

  masterSlaveRules: #读写分离规则，详见读写分离部分
    <data_source_name>: #数据源名称，需要与真实数据源匹配，可配置多个data_source_name
      masterDataSourceName: #详见读写分离部分
      slaveDataSourceNames: #详见读写分离部分
      loadBalanceAlgorithmType: #详见读写分离部分
      props: #读写分离负载算法的属性配置
        <property-name>: #属性值
      
props: #属性配置
  sql.show: #是否开启SQL显示，默认值: false
  executor.size: #工作线程数量，默认值: CPU核数
  max.connections.size.per.query: # 每个查询可以打开的最大连接数量,默认为1
  check.table.metadata.enabled: #是否在启动时检查分表元数据一致性，默认值: false

6.2、读写分离?

dataSources: #省略数据源配置，与数据分片一致

masterSlaveRule:
  name: #读写分离数据源名称
  masterDataSourceName: #主库数据源名称
  slaveDataSourceNames: #从库数据源名称列表
    - <data_source_name1>
    - <data_source_name2>
    - <data_source_name_x>
  loadBalanceAlgorithmType: #从库负载均衡算法类型，可选值：ROUND_ROBIN，RANDOM。若`loadBalanceAlgorithmClassName`存在则忽略该配置
  props: #读写分离负载算法的属性配置
    <property-name>: #属性值

文章来源:https://blog.csdn.net/Weixiaohuai/article/details/135535185
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章