Sharding-JDBC笔记03-分库分表代码示例
文章目录
- 一、水平分库
- 1. 将原有order_db库拆分为order_db_1、order_db_2
- 2. 分片规则修改
- 分片策略
- standard
- complex
- inline
- hint
- none
- 3. 插入测试
- 4. 查询测试
- 5. 使用分库分片键查询测试
- 总结
- 二、公共表
- 1. 创建数据库
- 2. 在Sharding-JDBC规则中修改
- 3. 数据操作
- 4. 字典操作测试
- 5. 字典关联查询测试
- 总结分库分表配置大概流程：
- 三、读写分离
- 1.理解读写分离
- 2.实现sharding-jdbc读写分离
- 1. 在Sharding-JDBC规则中修改
一、水平分库
前面已经介绍过，水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。并且在前面的文章中(快速入门)，我们已经对水平分库进行代码实现，这里不再重复介绍。
水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。接下来看一下如何使用Sharding-JDBC实现水平分库，咱们继续对快速入门中的例子进行完善。
1. 将原有order_db库拆分为order_db_1、order_db_2
数据库表
CREATE DATABASE `order_db_1` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `order_db_2` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
`order_id` bigint(20) NOT NULL COMMENT '订单id',
`price` decimal(10, 2) NOT NULL COMMENT '订单价格',
`user_id` bigint(20) NOT NULL COMMENT '下单用户id',
`status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
`order_id` bigint(20) NOT NULL COMMENT '订单id',
`price` decimal(10, 2) NOT NULL COMMENT '订单价格',
`user_id` bigint(20) NOT NULL COMMENT '下单用户id',
`status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
引入maven依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding‐jdbc‐spring‐boot‐starter</artifactId>
<version>4.0.0‐RC1</version>
</dependency>
2. 分片规则修改
由于数据库拆分了两个，这里需要配置两个数据源。
分库需要配置分库的策略，和分表策略的意义类似，通过分库策略实现数据操作针对分库的数据库进行操作。
# 定义多个数据源
server.port=8889
spring.application.name = sharding-jdbc-simple-demo
server.servlet.context-path = /sharding-jdbc-simple-demo
spring.http.encoding.enabled = true
spring.http.encoding.charset = UTF-8
spring.http.encoding.force = true
spring.main.allow-bean-definition-overriding = true
mybatis.configuration.map-underscore-to-camel-case = true
#sharding-jdbc分片规则配置
#数据源
spring.shardingsphere.datasource.names = m1,m2
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3308/order_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = 123456
spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3308/order_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = 123456
# 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作m1数据源，否则操作m2。
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression = m$->{user_id % 2 + 1}
# 指定t_order表的数据分布情况，配置数据节点 m.t_order_1,m1.t_order_2,m2.t_order_1,m2.t_order_21
# 如果这里配置如m1.t_order_$->{1..2}，则查询时只会查询m1库的表
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m$->{1..2}.t_order_$->{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{order_id % 2 + 1}
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true
swagger.enable = true
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding = debug
logging.level.druid.sql = debug
分库策略定义方式如下：
#分库策略，如何将一个逻辑表映射到多个数据源
spring.shardingsphere.sharding.tables.<逻辑表名称>.database‐strategy.<分片策略>.<分片策略属性名> = #分片策略属性值
#分表策略，如何将一个逻辑表映射为多个实际表
spring.shardingsphere.sharding.tables.<逻辑表名称>.table‐strategy.<分片策略>.<分片策略属性名> = #分片策略属性值
分片策略
Sharding-JDBC支持以下几种分片策略：
不管理分库还是分表，策略基本一样。
standard
standard：标准分片策略，对应StandardShardingStrategy。
提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。
StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。
PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。
RangeShardingAlgorithm是可选的，用于处理BETWEEN AND分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理。
complex
complex：符合分片策略，对应ComplexShardingStrategy。复合分片策略。
提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。
ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。
inline
inline：行表达式分片策略，对应InlineShardingStrategy。
使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。
对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为 t_user_0 到t_user_7 。
hint
hint：Hint分片策略，对应HintShardingStrategy。
通过Hint而非SQL解析的方式分片的策略。对于分片字段非SQL决定，而由其他外置条件决定的场景，可使用SQL Hint灵活的注入分片字段。
例：内部系统，按照员工登录主键分库，而数据库中并无此字段。SQL Hint支持通过Java API和SQL注释(待实现)两种方式使用。
none
none：不分片策略，对应NoneShardingStrategy。不分片的策略。
目前例子中都使用inline分片策略，若对其他分片策略细节若感兴趣，请查阅官方文档：
https://shardingsphere.apache.org
3. 插入测试
修改testInsertOrder方法，插入数据中包含不同的user_id
@Test
public void testInsertOrder(){
for(int i=1;i<20;i++){
//插入时根据分库策略，userId为奇数时插入到sharding_db_2中(偶数则sharding_db_1)
orderDao.insertOrder(new BigDecimal(i),2L,"SUCCESS");
}
}
执行testInsertOrder输出:
...
2024-09-02 21:21:50.473 INFO 25308 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-02 21:21:50.473 INFO 25308 --- [ main] ShardingSphere-SQL : Logic SQL: insert into t_order(price,user_id,status)values(?,?,?)
2024-09-02 21:21:50.473 INFO 25308 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_order, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=t_order), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=19)], parametersIndex=3, logicSQL=insert into t_order(price,user_id,status)values(?,?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[price, user_id, status], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@518ddd3b, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@939ff41, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6e0e5dec])])
2024-09-02 21:21:50.474 INFO 25308 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: insert into t_order_1 (price, user_id, status, order_id) VALUES (?, ?, ?, ?) ::: [18, 1, SUCCESS, 1037476657251745792]
2024-09-02 21:21:50.476 DEBUG 25308 --- [ main] c.i.d.simple.dao.OrderDao.insertOrder : <== Updates: 1
2024-09-02 21:21:50.476 DEBUG 25308 --- [ main] c.i.d.simple.dao.OrderDao.insertOrder : ==> Preparing: insert into t_order(price,user_id,status)values(?,?,?)
2024-09-02 21:21:50.476 DEBUG 25308 --- [ main] c.i.d.simple.dao.OrderDao.insertOrder : ==> Parameters: 19(BigDecimal), 1(Long), SUCCESS(String)
2024-09-02 21:21:50.476 INFO 25308 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-02 21:21:50.476 INFO 25308 --- [ main] ShardingSphere-SQL : Logic SQL: insert into t_order(price,user_id,status)values(?,?,?)
2024-09-02 21:21:50.476 INFO 25308 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_order, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=t_order), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=19)], parametersIndex=3, logicSQL=insert into t_order(price,user_id,status)values(?,?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[price, user_id, status], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@518ddd3b, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@939ff41, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6e0e5dec])])
2024-09-02 21:21:50.476 INFO 25308 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: insert into t_order_2 (price, user_id, status, order_id) VALUES (?, ?, ?, ?) ::: [19, 1, SUCCESS, 1037476657264328705]
2024-09-02 21:21:50.478 DEBUG 25308 --- [ main] c.i.d.simple.dao.OrderDao.insertOrder : <== Updates: 1
通过日志可以看出，根据user_id的奇偶不同，数据分别落在了不同数据源，达到目标。
4. 查询测试
调用快速入门的查询接口进行测试：
@Test
public void testSelectOrderbyIds(){
List<Long> ids = new ArrayList<>();
ids.add(1037479531180457984L);
ids.add(1037479530777804801L);
List<Map> maps = orderDao.selectOrderbyIds(ids);
System.out.println(maps);
}
通过日志发现，sharding-jdbc将sql路由到m1和m2：
2024-09-02 21:34:14.292 DEBUG 29104 --- [ main] c.i.d.s.dao.OrderDao.selectOrderbyIds : ==> Preparing: select * from t_order t where t.order_id in ( ? , ? )
2024-09-02 21:34:14.308 DEBUG 29104 --- [ main] c.i.d.s.dao.OrderDao.selectOrderbyIds : ==> Parameters: 1037479531180457984(Long), 1037479530777804801(Long)
2024-09-02 21:34:14.678 INFO 29104 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-02 21:34:14.679 INFO 29104 --- [ main] ShardingSphere-SQL : Logic SQL: select * from t_order t where t.order_id in ( ? , ? )
2024-09-02 21:34:14.679 INFO 29104 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_order, alias=Optional.of(t))]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=order_id, tableName=t_order), operator=IN, compareOperator=null, positionValueMap={}, positionIndexMap={0=0, 1=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=select * from t_order t where t.order_id in ( ? , ? ))), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2024-09-02 21:34:14.680 INFO 29104 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: select * from t_order_1 t where t.order_id in ( ? , ? ) ::: [1037479531180457984, 1037479530777804801]
2024-09-02 21:34:14.680 INFO 29104 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: select * from t_order_2 t where t.order_id in ( ? , ? ) ::: [1037479531180457984, 1037479530777804801]
2024-09-02 21:34:14.680 INFO 29104 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: select * from t_order_1 t where t.order_id in ( ? , ? ) ::: [1037479531180457984, 1037479530777804801]
2024-09-02 21:34:14.680 INFO 29104 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: select * from t_order_2 t where t.order_id in ( ? , ? ) ::: [1037479531180457984, 1037479530777804801]
2024-09-02 21:34:14.721 DEBUG 29104 --- [ main] c.i.d.s.dao.OrderDao.selectOrderbyIds : <== Total: 2
当只查询偶数结尾的id时
@Test
public void testSelectOrderbyId(){
//观察查询一个id和多个id的区别
List<Long> ids = new ArrayList<>();
ids.add(1037479531180457984L);
// ids.add(1037479530777804801L);
List<Map> maps = orderDao.selectOrderbyIds(ids);
System.out.println(maps);
}
通过日志发现，sharding-jdbc将sql路由到m1和m2，并且只查询两个库中的t_order_1表：
2024-09-02 21:35:15.602 DEBUG 35388 --- [ main] c.i.d.s.dao.OrderDao.selectOrderbyIds : ==> Preparing: select * from t_order t where t.order_id in ( ? )
2024-09-02 21:35:15.616 DEBUG 35388 --- [ main] c.i.d.s.dao.OrderDao.selectOrderbyIds : ==> Parameters: 1037479531180457984(Long)
2024-09-02 21:35:15.989 INFO 35388 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-02 21:35:15.990 INFO 35388 --- [ main] ShardingSphere-SQL : Logic SQL: select * from t_order t where t.order_id in ( ? )
2024-09-02 21:35:15.991 INFO 35388 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_order, alias=Optional.of(t))]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=order_id, tableName=t_order), operator=IN, compareOperator=null, positionValueMap={}, positionIndexMap={0=0})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select * from t_order t where t.order_id in ( ? ))), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2024-09-02 21:35:15.991 INFO 35388 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: select * from t_order_1 t where t.order_id in ( ? ) ::: [1037479531180457984]
2024-09-02 21:35:15.991 INFO 35388 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: select * from t_order_1 t where t.order_id in ( ? ) ::: [1037479531180457984]
2024-09-02 21:35:16.073 DEBUG 35388 --- [ main] c.i.d.s.dao.OrderDao.selectOrderbyIds : <== Total: 1
5. 使用分库分片键查询测试
由于查询语句中并没有使用分片键user_id，所以sharding-jdbc将广播路由到每个数据结点。
下边我们在sql中添加分片键进行查询。
在OrderDao中定义接口：
@Mapper
@Component
public interface OrderDao {
/**
* 根据id列表和用户id查询订单
* @param orderIds
* @return
*/
@Select("<script>" +
"select" +
" * " +
" from t_order t " +
" where t.order_id in " +
" <foreach collection='orderIds' open='(' separator=',' close=')' item='id'>" +
" #{id} " +
" </foreach>" +
" and user_id = #{userId} " +
"</script>")
List<Map> selectOrderbyUserAndIds(@Param("userId") Long userId,@Param("orderIds") List<Long> orderIds);
}
编写测试方法：
@Test
public void testSelectOrderbyUserAndIds(){
List<Long> ids = new ArrayList<>();
ids.add(1037479531180457984L);
// ids.add(1037479530777804801L);
List<Map> maps = orderDao.selectOrderbyUserAndIds(2L,ids);
System.out.println(maps);
}
查看输出日志：
2024-09-02 21:42:30.440 DEBUG 16652 --- [ main] c.i.d.s.d.O.selectOrderbyUserAndIds : ==> Preparing: select * from t_order t where t.order_id in ( ? ) and user_id = ?
2024-09-02 21:42:30.455 DEBUG 16652 --- [ main] c.i.d.s.d.O.selectOrderbyUserAndIds : ==> Parameters: 1037479531180457984(Long), 2(Long)
2024-09-02 21:42:30.867 INFO 16652 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-02 21:42:30.869 INFO 16652 --- [ main] ShardingSphere-SQL : Logic SQL: select * from t_order t where t.order_id in ( ? ) and user_id = ?
2024-09-02 21:42:30.869 INFO 16652 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_order, alias=Optional.of(t))]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=order_id, tableName=t_order), operator=IN, compareOperator=null, positionValueMap={}, positionIndexMap={0=0}), Condition(column=Column(name=user_id, tableName=t_order), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=select * from t_order t where t.order_id in ( ? ) and user_id = ?)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2024-09-02 21:42:30.869 INFO 16652 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: select * from t_order_1 t where t.order_id in ( ? ) and user_id = ? ::: [1037479531180457984, 2]
2024-09-02 21:42:30.895 DEBUG 16652 --- [ main] c.i.d.s.d.O.selectOrderbyUserAndIds : <== Total: 1
查询条件user_id为2，根据分片策略m$->{user_id % 2 + 1}计算得出m1，此sharding-jdbc将sql路由到m1。
总结
当使用user_id查询时，因为分库策略中以user_id为分片键，所以会在user_id为偶数操作m1数据源，又根据当查询偶数结尾的id时，根据t_order表的分片策略使用t_order_1表。所以结果为使用m1库中t_order_1表查询。
在分库分表中查条件查询时，能加上分片的字段就一定要加上查询，没有分片键时会广播路由，查询所有的数据源。
二、公共表
公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。接下来看一下如何使用Sharding-JDBC实现公共表。
1. 创建数据库
分别在user_db、order_db_1、order_db_2中创建t_dict表：
CREATE TABLE `t_user` (
`user_id` bigint NOT NULL COMMENT '用户id',
`fullname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
`user_type` char(1) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t_dict` (
`dict_id` bigint(20) NOT NULL COMMENT '字典id',
`type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典类型',
`code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典编码',
`value` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典值',
PRIMARY KEY (`dict_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2. 在Sharding-JDBC规则中修改
# 指定t_dict为公共表
spring.shardingsphere.sharding.broadcast‐tables=t_dict
完整配置
server.port=8889
spring.application.name = sharding-jdbc-simple-demo
server.servlet.context-path = /sharding-jdbc-simple-demo
spring.http.encoding.enabled = true
spring.http.encoding.charset = UTF-8
spring.http.encoding.force = true
spring.main.allow-bean-definition-overriding = true
mybatis.configuration.map-underscore-to-camel-case = true
#sharding-jdbc分片规则配置
#数据源
spring.shardingsphere.datasource.names = m1,m2,m0
spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url = jdbc:mysql://localhost:3308/user_db?useUnicode=true
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = 123456
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3308/order_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = 123456
spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3308/order_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = 123456
# 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作m1数据源，否则操作m2。
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression = m$->{user_id % 2 + 1}
# 指定t_order表的数据分布情况，配置数据节点 m.t_order_1,m1.t_order_2,m2.t_order_1,m2.t_order_21
# 如果这里配置如m1.t_order_$->{1..2}，则查询时只会查询m1库的表
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m$->{1..2}.t_order_$->{1..2}
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = m$->{0}.t_user
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{order_id % 2 + 1}
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression = t_user
# 指定t_dict为公共表
spring.shardingsphere.sharding.broadcast‐tables=t_dict
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true
swagger.enable = true
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding = debug
logging.level.druid.sql = debug
3. 数据操作
新增DictDao：
@Mapper
@Component
public interface DictDao {
/**
* 新增字典
* @param type 字典类型
* @param code 字典编码
* @param value 字典值
* @return
*/
@Insert("insert into t_dict(dict_id,type,code,value) value(#{dictId},#{type},#{code},#{value})")
int insertDict(@Param("dictId") Long dictId,@Param("type") String type, @Param("code")String code, @Param("value")String value);
/**
* 删除字典
* @param dictId 字典id
* @return
*/
@Delete("delete from t_dict where dict_id = #{dictId}")
int deleteDict(@Param("dictId") Long dictId);
}
4. 字典操作测试
新增单元测试方法：
@Test
public void testInsertDict(){
//t_dict设置为公共表后，插入数据时会同时插入所有数据源
dictDao.insertDict(1L,"user_type","1","超级管理员");
dictDao.insertDict(2L,"user_type","2","二级管理员");
}
输出
2024-09-02 22:19:45.760 DEBUG 8620 --- [ main] c.i.d.simple.dao.DictDao.insertDict : ==> Preparing: insert into t_dict(dict_id,type,code,value) value(?,?,?,?)
2024-09-02 22:19:45.774 DEBUG 8620 --- [ main] c.i.d.simple.dao.DictDao.insertDict : ==> Parameters: 1(Long), user_type(String), 1(String), 超级管理员(String)
2024-09-02 22:19:46.148 INFO 8620 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-02 22:19:46.149 INFO 8620 --- [ main] ShardingSphere-SQL : Logic SQL: insert into t_dict(dict_id,type,code,value) value(?,?,?,?)
2024-09-02 22:19:46.149 INFO 8620 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_dict, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_dict, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=18)], parametersIndex=4, logicSQL=insert into t_dict(dict_id,type,code,value) value(?,?,?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[dict_id, type, code, value], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@74d6736, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@52a33c3f, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@668625f5, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@19a20bb2])])
2024-09-02 22:19:46.150 INFO 8620 --- [ main] ShardingSphere-SQL : Actual SQL: m0 ::: insert into t_dict (dict_id, type, code, value) VALUES (?, ?, ?, ?) ::: [1, user_type, 1, 超级管理员]
2024-09-02 22:19:46.150 INFO 8620 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: insert into t_dict (dict_id, type, code, value) VALUES (?, ?, ?, ?) ::: [1, user_type, 1, 超级管理员]
2024-09-02 22:19:46.150 INFO 8620 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: insert into t_dict (dict_id, type, code, value) VALUES (?, ?, ?, ?) ::: [1, user_type, 1, 超级管理员]
2024-09-02 22:19:46.220 DEBUG 8620 --- [ main] c.i.d.simple.dao.DictDao.insertDict : <== Updates: 1
2024-09-02 22:19:46.225 DEBUG 8620 --- [ main] c.i.d.simple.dao.DictDao.insertDict : ==> Preparing: insert into t_dict(dict_id,type,code,value) value(?,?,?,?)
2024-09-02 22:19:46.225 DEBUG 8620 --- [ main] c.i.d.simple.dao.DictDao.insertDict : ==> Parameters: 2(Long), user_type(String), 2(String), 二级管理员(String)
2024-09-02 22:19:46.226 INFO 8620 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-02 22:19:46.226 INFO 8620 --- [ main] ShardingSphere-SQL : Logic SQL: insert into t_dict(dict_id,type,code,value) value(?,?,?,?)
2024-09-02 22:19:46.226 INFO 8620 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_dict, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_dict, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=18)], parametersIndex=4, logicSQL=insert into t_dict(dict_id,type,code,value) value(?,?,?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[dict_id, type, code, value], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@74d6736, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@52a33c3f, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@668625f5, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@19a20bb2])])
2024-09-02 22:19:46.226 INFO 8620 --- [ main] ShardingSphere-SQL : Actual SQL: m0 ::: insert into t_dict (dict_id, type, code, value) VALUES (?, ?, ?, ?) ::: [2, user_type, 2, 二级管理员]
2024-09-02 22:19:46.226 INFO 8620 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: insert into t_dict (dict_id, type, code, value) VALUES (?, ?, ?, ?) ::: [2, user_type, 2, 二级管理员]
2024-09-02 22:19:46.226 INFO 8620 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: insert into t_dict (dict_id, type, code, value) VALUES (?, ?, ?, ?) ::: [2, user_type, 2, 二级管理员]
2024-09-02 22:19:46.232 DEBUG 8620 --- [ main] c.i.d.simple.dao.DictDao.insertDict : <== Updates: 1
通过日志可以看出，对t_dict的表的操作被广播至所有数据源。
测试删除字典，观察是否把所有数据源中该 公共表的记录删除。
@Test
public void testDeleteDict(){
//删除公共表同理
dictDao.deleteDict(2L);
}
输出
2024-09-02 22:21:37.006 DEBUG 8464 --- [ main] c.i.d.simple.dao.DictDao.deleteDict : ==> Preparing: delete from t_dict where dict_id = ?
2024-09-02 22:21:37.026 DEBUG 8464 --- [ main] c.i.d.simple.dao.DictDao.deleteDict : ==> Parameters: 2(Long)
2024-09-02 22:21:37.366 INFO 8464 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-02 22:21:37.367 INFO 8464 --- [ main] ShardingSphere-SQL : Logic SQL: delete from t_dict where dict_id = ?
2024-09-02 22:21:37.367 INFO 8464 --- [ main] ShardingSphere-SQL : SQLStatement: DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_dict, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_dict, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=delete from t_dict where dict_id = ?), deleteStatement=true, updateTableAlias={t_dict=t_dict}, updateColumnValues={}, whereStartIndex=19, whereStopIndex=35, whereParameterStartIndex=0, whereParameterEndIndex=0)
2024-09-02 22:21:37.367 INFO 8464 --- [ main] ShardingSphere-SQL : Actual SQL: m0 ::: delete from t_dict where dict_id = ? ::: [2]
2024-09-02 22:21:37.367 INFO 8464 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: delete from t_dict where dict_id = ? ::: [2]
2024-09-02 22:21:37.367 INFO 8464 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: delete from t_dict where dict_id = ? ::: [2]
2024-09-02 22:21:37.425 DEBUG 8464 --- [ main] c.i.d.simple.dao.DictDao.deleteDict : <== Updates: 1
5. 字典关联查询测试
字典表已在各各分库存在，各业务表即可和字典表关联查询。
定义用户关联查询dao
在UserDao中定义:
/**
* 根据id列表查询多个用户
* @param userIds 用户id列表
* @return
*/
@Select({"<script>",
" select",
" * ",
" from t_user t ,t_dict b",
" where t.user_type = b.code and t.user_id in",
"<foreach collection='userIds' item='id' open='(' separator=',' close=')'>",
"#{id}",
"</foreach>",
"</script>"
})
List<Map> selectUserInfobyIds(@Param("userIds") List<Long> userIds);
测试方法：
@Test
public void testSelectUserInfobyIds(){
//分库的表可以直接在库中关联查询公共表
List<Long> userIds = new ArrayList<>();
userIds.add(11L);
userIds.add(12L);
List<Map> users = userDao.selectUserInfobyIds(userIds);
System.out.println(users);
}
输出
2024-09-02 22:22:53.729 DEBUG 22356 --- [ main] c.i.d.s.dao.UserDao.selectUserInfobyIds : ==> Preparing: select * from t_user t ,t_dict b where t.user_type = b.code and t.user_id in ( ? , ? )
2024-09-02 22:22:53.743 DEBUG 22356 --- [ main] c.i.d.s.dao.UserDao.selectUserInfobyIds : ==> Parameters: 11(Long), 12(Long)
2024-09-02 22:22:54.081 INFO 22356 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-02 22:22:54.083 INFO 22356 --- [ main] ShardingSphere-SQL : Logic SQL: select * from t_user t ,t_dict b where t.user_type = b.code and t.user_id in ( ? , ? )
2024-09-02 22:22:54.083 INFO 22356 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_user, alias=Optional.of(t)), Table(name=t_dict, alias=Optional.of(b))]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=t_user), operator=IN, compareOperator=null, positionValueMap={}, positionIndexMap={0=0, 1=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0), TableToken(tableName=t_dict, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=select * from t_user t ,t_dict b where t.user_type = b.code and t.user_id in ( ? , ? ))), containStar=true, firstSelectItemStartIndex=8, selectListStopIndex=8, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2024-09-02 22:22:54.083 INFO 22356 --- [ main] ShardingSphere-SQL : Actual SQL: m0 ::: select * from t_user t ,t_dict b where t.user_type = b.code and t.user_id in ( ? , ? ) ::: [11, 12]
2024-09-02 22:22:54.120 DEBUG 22356 --- [ main] c.i.d.s.dao.UserDao.selectUserInfobyIds : <== Total: 2
[{dict_id=1, user_type=1, code=1, user_id=11, fullname=姓名11, type=user_type, value=超级管理员}, {dict_id=1, user_type=1, code=1, user_id=12, fullname=姓名12, type=user_type, value=超级管理员}]
总结分库分表配置大概流程：
- 先配置数据源
- 是否分库，是则配置分库策略
- 必须配置数据节点
- 是否要配置主键生成策略
- 配置每个表的分片策略
三、读写分离
1.理解读写分离
MySQL的读写分离是一种常见的数据库架构模式，用于提高数据库的可用性和扩展性。通过将读取请求分散到多个从服务器（slave servers），可以减轻主服务器（master server）的负载，从而提高读取性能。同时，写入请求仍然集中在一个主服务器上执行，保证了数据的一致性和完整性。
面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。
通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力。 使用多主多从的方式，不但能够提升系统的吞吐量，还能够提升系统的可用性，可以达到在任何一个数据库宕机，甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。
读写分离的数据节点中的数据内容是一致的，而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读写分离联合使用，能够更加有效的提升系统的性能。
Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。它提供透明化读写分离，让使用方尽量像使用一个数据库一样使用主从数据库集群。
Sharding-JDBC提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用，同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性。Sharding-JDBC不提供主从数据库的数据同步功能，需要采用其他机制支持。
接下来，咱们对上面例子中user_db进行读写分离实现。为了实现Sharding-JDBC的读写分离，首先，要进行mysql的主从同步配置。
配置mysql主从同步可以看这篇文章：Windows环境下搭建MySQL主从同步实现读写分离
2.实现sharding-jdbc读写分离
1. 在Sharding-JDBC规则中修改
server.port=56081
spring.application.name = sharding-jdbc-simple-demo
server.servlet.context-path = /sharding-jdbc-simple-demo
spring.http.encoding.enabled = true
spring.http.encoding.charset = UTF-8
spring.http.encoding.force = true
spring.main.allow-bean-definition-overriding = true
mybatis.configuration.map-underscore-to-camel-case = true
#sharding-jdbc分片规则配置
#数据源
spring.shardingsphere.datasource.names = m1,m2,m0,s0
spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url = jdbc:mysql://localhost:3308/user_db?useUnicode=true
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = 123456
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3308/order_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = 123456
spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3308/order_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = 123456
spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.url = jdbc:mysql://localhost:3309/user_db?useUnicode=true
spring.shardingsphere.datasource.s0.username = root
spring.shardingsphere.datasource.s0.password = 123456
# 主库从库逻辑数据源定义 ds0为user_db
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0
# 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作m1数据源，否则操作m2。
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression = m$->{user_id % 2 + 1}
# 指定t_order表的数据分布情况，配置数据节点 m.t_order_1,m1.t_order_2,m2.t_order_1,m2.t_order_21
# 如果这里配置如m1.t_order_$->{1..2}，则查询时只会查询m1库的表
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m$->{1..2}.t_order_$->{1..2}
#spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = m$->{0}.t_user
# t_user分表策略，固定分配至ds0的t_user真实表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = ds0.t_user
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{order_id % 2 + 1}
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression = t_user
# 指定t_dict为公共表
spring.shardingsphere.sharding.broadcast‐tables=t_dict
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true
swagger.enable = true
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding = debug
logging.level.druid.sql = debug
...
- 测试
执行testInsertUser单元测试：
@Test
public void testInsertUser2() {
userDao.insertUser(16L, "姓名" + 16);
//插入时在m0(主库)中
}
输出
2024-09-24 07:27:54.672 DEBUG 25836 --- [ main] c.i.d.simple.dao.UserDao.insertUser : ==> Preparing: insert into t_user(user_id, fullname) value(?,?)
2024-09-24 07:27:54.690 DEBUG 25836 --- [ main] c.i.d.simple.dao.UserDao.insertUser : ==> Parameters: 16(Long), 姓名16(String)
2024-09-24 07:27:55.055 INFO 25836 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-24 07:27:55.056 INFO 25836 --- [ main] ShardingSphere-SQL : Logic SQL: insert into t_user(user_id, fullname) value(?,?)
2024-09-24 07:27:55.056 INFO 25836 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=0})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=18)], parametersIndex=2, logicSQL=insert into t_user(user_id, fullname) value(?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[user_id, fullname], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3e36b7a0, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@60c1663c])])
2024-09-24 07:27:55.056 INFO 25836 --- [ main] ShardingSphere-SQL : Actual SQL: m0 ::: insert into t_user (user_id, fullname) VALUES (?, ?) ::: [16, 姓名16]
2024-09-24 07:27:55.080 DEBUG 25836 --- [ main] c.i.d.simple.dao.UserDao.insertUser : <== Updates: 1
可以发现插入时在m0(主库)中
通过日志可以看出，所有写操作落入m0数据源。
执行testSelectUserbyIds单元测试：
@Test
public void testSelectUserInfobyIds2() {
//分库的表可以直接在库中关联查询公共表
List<Long> userIds = new ArrayList<>();
userIds.add(16L);
List<Map> users = userDao.selectUserInfobyIds(userIds);
System.out.println(users);
//查询时在s0(从库)中
}
输出
2024-09-24 07:29:46.292 DEBUG 4776 --- [ main] c.i.d.s.dao.UserDao.selectUserInfobyIds : ==> Preparing: select * from t_user t ,t_dict b where t.user_type = b.code and t.user_id in ( ? )
2024-09-24 07:29:46.310 DEBUG 4776 --- [ main] c.i.d.s.dao.UserDao.selectUserInfobyIds : ==> Parameters: 16(Long)
2024-09-24 07:29:46.653 INFO 4776 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2024-09-24 07:29:46.656 INFO 4776 --- [ main] ShardingSphere-SQL : Logic SQL: select * from t_user t ,t_dict b where t.user_type = b.code and t.user_id in ( ? )
2024-09-24 07:29:46.656 INFO 4776 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_user, alias=Optional.of(t)), Table(name=t_dict, alias=Optional.of(b))]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=t_user), operator=IN, compareOperator=null, positionValueMap={}, positionIndexMap={0=0})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0), TableToken(tableName=t_dict, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select * from t_user t ,t_dict b where t.user_type = b.code and t.user_id in ( ? ))), containStar=true, firstSelectItemStartIndex=8, selectListStopIndex=8, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2024-09-24 07:29:46.656 INFO 4776 --- [ main] ShardingSphere-SQL : Actual SQL: s0 ::: select * from t_user t ,t_dict b where t.user_type = b.code and t.user_id in ( ? ) ::: [16]
2024-09-24 07:29:46.694 DEBUG 4776 --- [ main] c.i.d.s.dao.UserDao.selectUserInfobyIds : <== Total: 1
[{dict_id=2, user_type=1, code=1, user_id=16, fullname=姓名16, type=order_type, value=1}]
通过日志可以看出，所有查询操作落入s0数据源，达到目标。