ShardingSphere第9篇：表关联-Java充电社【公众号：Java充电社】

## 9.1、是什么？

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

```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条，它们呈现为笛卡尔积：

```sql
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条：

```sql
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`的条件。故绑定表之间的分区键要完全相同。

## 9.2、案例

### 1）需求

- ds_order库有4张表，2张订单表，2张订单明细表
- 2张订单表：t_order_0（存放id为奇数的数据）、t_order_1（存放id为偶数的数据）
- 2张订单明细表：t_order_0（存放order_id为奇数的数据，和t_order_0分片规则一致）、t_order_1（存放order_id为偶数的数据，和t_order_1分片规则一致）

### 2）sql脚本

```sql
drop database if exists ds_order;
create database ds_order;
use ds_order;

drop table if exists t_order_0;
create table t_order_0(
	order_id bigint not null primary key,
	price int not null
);

drop table if exists t_order_1;
create table t_order_1(
	order_id bigint not null primary key,
	price int not null
);

drop table if exists t_order_item_0;
create table t_order_item_0(
	id bigint not null primary key,
  order_id bigint not null,
	price int not null
);

create index idx_order_id on t_order_item_0(order_id);

drop table if exists t_order_item_1;
create table t_order_item_1(
	id bigint not null primary key,
	order_id bigint not null,
	price int not null
);

create index idx_order_id on t_order_item_1(order_id);

insert into t_order_0 values (1,20);
insert into t_order_1 values (2,30);

insert into t_order_item_0 values (1,1,5),(2,1,15);
insert into t_order_item_1 values (3,2,10),(4,2,20);
```

### 3）java代码：BindingTableGroupsTest

> 代码如下，关键代码就是配置了2张表(t_order、t_order_item)的分片策略

```java
@Slf4j
public class BindingTableGroupsTest {
    private static DataSource dataSource;

@BeforeAll
    public static void init() throws SQLException {
        HikariDataSource ds = new HikariDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/ds_order?characterEncoding=UTF-8");
        ds.setUsername("root");
        ds.setPassword("root123");

/**
         * 1.配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds", ds);

/**
         * 2、配置2个表的分片规则
         */
        //t_order分片规则
        TableRuleConfiguration orderRuleConfiguration =
                new TableRuleConfiguration("t_order", "ds.t_order_$->{0..1}");
        InlineShardingStrategyConfiguration orderTableShardingStrategy =
                new InlineShardingStrategyConfiguration("order_id", "t_order_$->{(order_id + 1) % 2}");
        orderRuleConfiguration.setTableShardingStrategyConfig(orderTableShardingStrategy);

//t_order_item分片规则
        TableRuleConfiguration orderItemRuleConfiguration =
                new TableRuleConfiguration("t_order_item", "ds.t_order_item_$->{0..1}");
        InlineShardingStrategyConfiguration orderItemTableShardingStrategy =
                new InlineShardingStrategyConfiguration("order_id", "t_order_item_$->{(order_id + 1) % 2}");
        orderItemRuleConfiguration.setTableShardingStrategyConfig(orderItemTableShardingStrategy);

/**
         * 3、加入表的分片规则
         */
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderRuleConfiguration);
        shardingRuleConfig.getTableRuleConfigs().add(orderItemRuleConfiguration);

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
    }

}
```

### 4）重点代码

上面代码中重点代码就下面这些，不要搞错了

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/413/aaffeac5-a535-45a7-a4e5-54c81196cb3a.png)

### 5）测试：查询

BindingTableGroupsTest中添加下面，用来查询order_id为1的订单明细，order_id为1的数据，实际上是在t_order_0和t_order_item_0表中，稍后大家注意看实际的sql

```java
@Test
public void test1() throws SQLException {
    String sql = "select a.order_id,b.id as order_item_id,b.price " +
        "from t_order a,t_order_item b " +
        "where a.order_id = b.order_id and a.order_id = 1";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            Long order_id = rs.getLong("order_id");
            Long order_item_id = rs.getLong("order_item_id");
            Integer price = rs.getInt("price");
            System.out.println(String.format("order_id：%s，order_item_id：%s, price：%s", order_id, order_item_id, price));
        }
    }
}
```

运行输出，如下，产生了2条sql，路由了3张表：t_order_0、t_order_item_0、t_order_item_1，实际上t_order_item_1表中是没有数据的，没必要路由，如何解决这个问题？设置表关联。

```sql
Logic SQL: select a.order_id,b.id as order_item_id,b.price from t_order a,t_order_item b where a.order_id = b.order_id and a.order_id = 1
Actual SQL: ds ::: select a.order_id,b.id as order_item_id,b.price from t_order_0 a,t_order_item_1 b where a.order_id = b.order_id and a.order_id = 1
Actual SQL: ds ::: select a.order_id,b.id as order_item_id,b.price from t_order_0 a,t_order_item_0 b where a.order_id = b.order_id and a.order_id = 1
order_id：1，order_item_id：1, price：5
order_id：1，order_item_id：2, price：15
```

### 6）设置2个表关联

当2个表的路由字段相同的时候，可以设置表关联，可以避免笛卡尔积查询，下面设置t_order和t_order_item关联

```java
shardingRuleConfig.setBindingTableGroups(Arrays.asList("t_order","t_order_item"));
```

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/413/faa7514f-88b9-4ba7-a877-be1d5ab54d1f.png)

### 7）再次运行测试用例

输出如下，这次只有一条sql了，查询被路由到t_order_0和t_order_item_1了，符合预期。

```sql
Logic SQL: select a.order_id,b.id as order_item_id,b.price from t_order a,t_order_item b where a.order_id = b.order_id and a.order_id = 1
Actual SQL: ds ::: select a.order_id,b.id as order_item_id,b.price from t_order_0 a,t_order_item_0 b where a.order_id = b.order_id and a.order_id = 1
order_id：1，order_item_id：1, price：5
order_id：1，order_item_id：2, price：15
```

## 9.3、本章案例完整代码

```java
https://gitee.com/javacode2018/shardingsphere-demo
```

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/413/6a922707-f6eb-482e-a313-c9795a56e33a.png)

<a style="display:none" target="_blank" href="https://mp.weixin.qq.com/s/_S1DD2JADnXvpexxaBwLLg" style="color:red; font-size:20px; font-weight:bold">继续收门徒，亲手带，月薪 4W 以下的可以来找我</a>

## 最新资料

1. <a href="https://mp.weixin.qq.com/s?__biz=MzkzOTI3Nzc0Mg==&mid=2247484964&idx=2&sn=c81bce2f26015ee0f9632ddc6c67df03&scene=21#wechat_redirect" target="_blank">尚硅谷 Java 学科全套教程（总 207.77GB）</a>
2. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484192&idx=1&sn=505f2faaa4cc911f553850667749bcbb&scene=21#wechat_redirect" target="_blank">2021 最新版 Java 微服务学习线路图 + 视频</a>
3. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484573&idx=1&sn=7f3d83892186c16c57bc0b99f03f1ffd&scene=21#wechat_redirect" target="_blank">阿里技术大佬整理的《Spring 学习笔记.pdf》</a>
4. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484544&idx=2&sn=c1dfe907cfaa5b9ae8e66fc247ccbe84&scene=21#wechat_redirect" target="_blank">阿里大佬的《MySQL 学习笔记高清.pdf》</a>
5. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485167&idx=1&sn=48d75c8e93e748235a3547f34921dfb7&scene=21#wechat_redirect" target="_blank">2021 版 java 高并发常见面试题汇总.pdf</a>
6. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485664&idx=1&sn=435f9f515a8f881642820d7790ad20ce&scene=21#wechat_redirect" target="_blank">Idea 快捷键大全.pdf</a>

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/1/2883e86e-3eff-404a-8943-0066e5e2b454.png)