目录
- 前言
- 前文
- 什么是绑定表?
- 创建子表
- boot 配置编写
- 功能测试
- 父子表关联查询测试
前言
主子表关联在我们的开发业务中是及其常见的,本文我们就来看下如何在分库分表的情况下,使用Sharding-JDBC
来完成主子关联。
前文
《Sharding-JDBC简单使用》
什么是绑定表?
官方文档
指分片规则一致的主表和子表。例如:t_order 表和 t_order_item 表,均按照 order_id 分片,则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联,关联查询效率将大大提升。
在不配置绑定表关系时,假设分片键 order_id 将数值 10 路由至第 0 片,将数值 11 路由至第 1 片,那么路由后的 SQL 应该为 4 条,它们呈现为笛卡尔积:
1
2
3
4
5SELECT 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 条:
1
2
3SELECT 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 的条件。故绑定表之间的分区键要完全相同。
上面的例子简单理解是:
- 一共有 2 个分片,一共是 4 张表:t_order_0、t_order_1、t_order_item_0、t_order_item_1 ,当查询的条件 order_id 在 2 个分片中的时候,会生成 4 条语句查询,因为 t_order_item 表有 4 个
- 如果是绑定表关系的话:t_order_item 的分片规则跟着主表的分片规则走,所以只会生成 2 条 SQL 查询
创建子表
分别在 sharding-order 和 shard-order 数据库中创建 t_order_item_1、t_order_item_2 的子表
1
2
3
4
5
6
7
8CREATE TABLE `t_order_item_1` ( `id` int(11) NOT NULL, `order_id` int(11) NOT NULL COMMENT '订单 ID', `product_name` varchar(255) DEFAULT NULL COMMENT '商品名称', `user_id` int(11) DEFAULT NULL COMMENT '用户 ID', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
boot 配置编写
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59spring: shardingsphere: datasource: names: ds0,ds1 ds0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://localhost:3307/sharding-order?serverTimezone=Asia/Shanghai&useSSL=true&characterEncoding=utf8 username: root password: root ds1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://localhost:3308/shard-order?serverTimezone=Asia/Shanghai&useSSL=true&characterEncoding=utf8 username: root password: root sharding: broadcast-tables: - area # 分片表配置 tables: # 逻辑表名 # 下面的没有自动提示,可以点击配置属性,会跳转到具体的自动配置文件中 # 然后就会看到源码对应的配置类是什么,就知道有哪些属性可以配置了 # 比如 org.apache.shardingsphere.core.yaml.config.sharding.YamlTableRuleConfiguration t_order: # 实际节点 actual-data-nodes: ds$->{0..1}.t_order_$->{1..2} # 数据库分片策略 database-strategy: inline: sharding-column: user_id algorithm-expression: ds$->{user_id % 2} # 表分片策略 table-strategy: inline: sharding-column: id algorithm-expression: t_order_$->{id % 2 + 1} # 订单子表的分片规则,设置为和 订单表一样的,表分片字段设置为 order_id t_order_item: # 实际节点 actual-data-nodes: ds$->{0..1}.t_order_item_$->{1..2} # 数据库分片策略 database-strategy: inline: sharding-column: user_id algorithm-expression: ds$->{user_id % 2} # 表分片策略 table-strategy: inline: sharding-column: order_id algorithm-expression: t_order_item_$->{order_id % 2 + 1} # 设置绑定表,左边的为主表,右边的为子表 binding-tables: - t_order,t_order_item # 打印 sharding 的 sql 信息 props: sql.show: true
功能测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17/** * 绑定表的子表插入数据 */ @Test public void testBindingTable() { OrderItem order = new OrderItem(); // 数据库:userId 偶数分到 sharding-order,奇数分到 shard-order order.setUserId(19); // 表:orderId 偶数分到 t_order_item_1, 奇数分到 t_order_item_2 order.setId(1); order.setOrderId(1); order.setProductName("商品 1"); orderItemMapper.insertSelective(order); // 那么这条语句期望是插入到:shard-order.t_order_item_2 中 }
控制台的配置输出信息对比
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28bindingTables: - t_order,t_order_item broadcastTables: - area tables: t_order: actualDataNodes: ds$->{0..1}.t_order_$->{1..2} databaseStrategy: inline: algorithmExpression: ds$->{user_id % 2} shardingColumn: user_id logicTable: t_order tableStrategy: inline: algorithmExpression: t_order_$->{id % 2 + 1} shardingColumn: id t_order_item: actualDataNodes: ds$->{0..1}.t_order_item_$->{1..2} databaseStrategy: inline: algorithmExpression: ds$->{user_id % 2} shardingColumn: user_id logicTable: t_order_item tableStrategy: inline: algorithmExpression: t_order_item_$->{order_id % 2 + 1} shardingColumn: order_id
父子表关联查询测试
1
2
3
4
5
6
7
8
9@Autowired private OrderItemxMapper orderItemxMapper; @Test public void testBingdingTables() { List<OrderDetail> details = orderItemxMapper.selectOrder(); System.out.println(details); }
控制台打印
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5b1f5fcc] was not registered for synchronization because synchronization is not active JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@21688427] will not be managed by Spring ==> Preparing: SELECT o.id AS orderId, o.user_id AS userId, item.id AS orderItemId, item.product_name AS productName FROM t_order AS o LEFT JOIN t_order_item AS item ON o.id = item.order_id WHERE o.id IN (2) ==> Parameters: 2022-09-23 21:12:28.422 INFO 17184 --- [ main] ShardingSphere-SQL : Rule Type: sharding 2022-09-23 21:12:28.422 INFO 17184 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT o.id AS orderId, o.user_id AS userId, item.id AS orderItemId, item.product_name AS productName FROM t_order AS o LEFT JOIN t_order_item AS item ON o.id = item.order_id WHERE o.id IN (2) 2022-09-23 21:12:28.422 INFO 17184 --- [ main] ShardingSphere-SQL : SQLStatement: SelectSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@cc91fe3, tablesContext=TablesContext(tables=[Table(name=t_order, alias=Optional.of(o)), Table(name=t_order_item, alias=Optional.of(item))], schema=Optional.absent())), projectionsContext=ProjectionsContext(startIndex=7, stopIndex=176, distinctRow=false, projections=[ColumnProjection(owner=o, name=id, alias=Optional.of(orderId)), ColumnProjection(owner=o, name=user_id, alias=Optional.of(userId)), ColumnProjection(owner=item, name=id, alias=Optional.of(orderItemId)), ColumnProjection(owner=item, name=product_name, alias=Optional.of(productName))], columnLabels=[orderId, userId, orderItemId, productName]), groupByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.groupby.GroupByContext@481c1e92, orderByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.orderby.OrderByContext@66f223fa, paginationContext=org.apache.shardingsphere.sql.parser.relation.segment.select.pagination.PaginationContext@7ac47f14, containsSubquery=false) 2022-09-23 21:12:28.422 INFO 17184 --- [ main] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT o.id AS orderId, o.user_id AS userId, item.id AS orderItemId, item.product_name AS productName FROM t_order_1 AS o LEFT JOIN t_order_item_1 AS item ON o.id = item.order_id WHERE o.id IN (2) 2022-09-23 21:12:28.422 INFO 17184 --- [ main] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT o.id AS orderId, o.user_id AS userId, item.id AS orderItemId, item.product_name AS productName FROM t_order_1 AS o LEFT JOIN t_order_item_2 AS item ON o.id = item.order_id WHERE o.id IN (2) 2022-09-23 21:12:28.422 INFO 17184 --- [ main] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT o.id AS orderId, o.user_id AS userId, item.id AS orderItemId, item.product_name AS productName FROM t_order_1 AS o LEFT JOIN t_order_item_1 AS item ON o.id = item.order_id WHERE o.id IN (2) 2022-09-23 21:12:28.422 INFO 17184 --- [ main] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT o.id AS orderId, o.user_id AS userId, item.id AS orderItemId, item.product_name AS productName FROM t_order_1 AS o LEFT JOIN t_order_item_2 AS item ON o.id = item.order_id WHERE o.id IN (2) <== Columns: orderId, userId, orderItemId, productName <== Row: 2, 19, 1, 商品 2 <== Row: 2, 19, null, null <== Total: 2 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5b1f5fcc] [OrderDetail(orderId=2, userId=19, orderItemId=1, productName=商品 2), OrderDetail(orderId=2, userId=19, orderItemId=null, productName=null)]
t_order 和 t_order_item 都定位到了 2 ,这个是正确的
这里感谢茶佬的博客,在实战时真的一堆幺蛾子,但是都是特别低级的错误,有茶佬在旁边指导,真的非常感谢~
未完,下面将研究下读写分离~
码云传送门
最后
以上就是冷静大叔最近收集整理的关于Sharding-JDBC主子表(绑定表)关联的全部内容,更多相关Sharding-JDBC主子表(绑定表)关联内容请搜索靠谱客的其他文章。
发表评论 取消回复