目录
一、概述
二、案例演示-水平分表
(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框架。
- 适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
- 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
- 支持任意实现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)、创建数据库和数据表
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从节点。
- 满足分库规则：user_id为偶数，插入到master0库，user_id为奇数，插入到master1库。
- 满足分表规则：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: #是否在启动时检查分表元数据一致性，默认值: false6.2、读写分离
dataSources: #省略数据源配置，与数据分片一致
masterSlaveRule:
name: #读写分离数据源名称
masterDataSourceName: #主库数据源名称
slaveDataSourceNames: #从库数据源名称列表
- <data_source_name1>
- <data_source_name2>
- <data_source_name_x>
loadBalanceAlgorithmType: #从库负载均衡算法类型，可选值：ROUND_ROBIN，RANDOM。若`loadBalanceAlgorithmClassName`存在则忽略该配置
props: #读写分离负载算法的属性配置
<property-name>: #属性值
