使用shardingSphere做mysql分库分表（2）之多表联查遇到的问题

Z时代
2024-01-10
分类：综合

database

2019年11月20日星期三

试试基于shardingSphere能不能多表联查

分表在两个tmp_order库中创建test_order_item0和test_order_item1

create database if not exists tmp_order_item CHARACTER SET "utf8" COLLATE "utf8_general_ci";

USE tmp_order;

DROP TABLE IF EXISTS `test_order_item1`;

CREATE TABLE `test_order_item1` (

`id` varchar(255) NOT NULL COMMENT "主键",

`orderNo` varchar(255) DEFAULT NULL COMMENT "订单编号",

`goodsName` varchar(255) DEFAULT NULL,

`createdTime` datetime DEFAULT NULL,

`updatedTime` datetime DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后把分表策略设置一下：

<sharding:inline-strategy id="orderDatabaseStrategy" sharding-column="id" algorithm-expression="ds$->{Math.abs(id.hashCode()) % 2}" />

<sharding:inline-strategy id="orderTableStrategy" sharding-column="orderNo" algorithm-expression="test_order$->{Math.abs(orderNo.hashCode()) % 2}" />

<sharding:inline-strategy id="orderItemTableStrategy" sharding-column="orderNo" algorithm-expression="test_order_item$->{Math.abs(orderNo.hashCode()) % 2}" />

<sharding:data-source id="shardingDataSource">
    <sharding:sharding-rule data-source-names="ds0,ds1">
        <sharding:table-rules>
            <sharding:table-rule logic-table="test_order" actual-data-nodes="ds$->{0..1}.test_order$->{0..1}" database-strategy-ref="orderDatabaseStrategy" table-strategy-ref="orderTableStrategy" />
            <sharding:table-rule logic-table="test_order_item" actual-data-nodes="ds$->{0..1}.test_order_item$->{0..1}" database-strategy-ref="orderDatabaseStrategy" table-strategy-ref="orderItemTableStrategy" />
        </sharding:table-rules>
    </sharding:sharding-rule>
</sharding:data-source>

分库策略改成根据id取模

分表策略使用orderNo 取模

之前的分库策略是根据userId取模，但是userId字段只在test_order表中又，在test_order_item表中没有，所以出了错误，再向test_order_item表插入数据的时候，只插入到了一个库中的两张表中，另一个库中的两张表插入了上一个库中的全量数据做了备份，这块需要找时间再记录一下，这里就不再记录了。

为了监控sql，把数据源换成德鲁伊：

德鲁伊的配置不是重点就不帖了。

因为分库策略调整了，把所有数据清空重新插入。

在test_order表中插入10条数据，再在test_order_item中以test_order表中的orderNo为关联字段，每个orderNo 再插入3条数据，所以就是10*3=30条数据。代码

两段主要代码：

@Test
public void insert() {
    for (int i = 0; i < 10; i++) {
        Order order = new Order();
        order.setId(IdWorker.getId());
        order.setUserId(RandomUtils.getRandomString(6));
        order.setAliTradeNo(RandomUtils.getRandomString(8));
        order.setAmount(new BigDecimal(100));
        order.setCreatedTime(new Date());
        order.setGoodsId("1");
        order.setNum(100);
        order.setOrderNo(RandomUtils.getRandomString2(8));
        order.setUpdatedTime(new Date());
        order.setStatus(1);
        order.setStockId("20");
        System.out.println(orderService.insert(order));
    }

@Test
public void insert() {
    List<Order> orders = orderService.findAllOrders();
    for (Order order : orders) {
        for (int i = 0; i < 3; i++) {
            saveOrderItem(order.getOrderNo());
        }
    }
}

/**
* TODO 根据订单号插入订单项
*
* @param orderNo :
* @return void
* @author 侨居
* @time 2019/11/19 15:11
* @version v1.0
*/
private void saveOrderItem(String orderNo) {
    OrderItem orderItem = new OrderItem();
    orderItem.setCreatedTime(new Date());
    orderItem.setUpdatedTime(new Date());
    orderItem.setGoodsName(RandomNameUtils.getRandomJianHan(5));
    orderItem.setId(IdWorker.getId());
    orderItem.setOrderNo(orderNo);
    orderItemService.insert(orderItem);
}

现在配置信息和数据都有了，开始编写查询语句：

先做个普通的根据test_order_item表中的orderNo 字段查出 test_order_表中的userId和test_order_item表中的goodsName 和 orderNo;

Sql:

<select id="findOrderInfo" resultType="com.sunliang.sharding.bean.OrderVo">
    select o.userId as userId,oi.orderNo as orderNo,oi.goodsName as goodsName
    from test_order o inner join test_order_item oi ON o.orderNo = oi.orderNo
</select>

可以查出来。数据的都是随机生成的。所以看着有点闹眼睛，呵呵。

再看下德鲁伊监控的sql。

生成了8条sql语句。

再看看着8条sql都查了什么：

下面主要看剩下的8条sql中的上图的两个红框内容：

数据再数据库中是这么存的：

Mysql01 中两个test_order * 两个test_order_item 一共4次查询

Mysql02 中两个test_order * 两个test_order_item 一共4次查询

两个库一共查询8次。所以产生8条sql语句。

再用测试类查一下，发现使用inner join 查出的数据少一条

单表查询

OrderItem{id="40776371588304896", orderNo="9F079y3U", goodsName="建邦筹奴层", createdTime=Tue Nov 19 21:53:11 CST 2019, updatedTime=Tue Nov 19 21:53:11 CST 2019}

OrderItem{id="40776372116787200", orderNo="9F079y3U", goodsName="雹懦悯庭轿", createdTime=Tue Nov 19 21:53:11 CST 2019, updatedTime=Tue Nov 19 21:53:11 CST 2019}

OrderItem{id="40776372183896064", orderNo="9F079y3U", goodsName="这雪模吹契", createdTime=Tue Nov 19 21:53:11 CST 2019, updatedTime=Tue Nov 19 21:53:11 CST 2019}

Inner join 查询

order size is :2

OrderVo{userId="kBwPcO", orderNo="9F079y3U", goodsName="建邦筹奴层"}

OrderVo{userId="kBwPcO", orderNo="9F079y3U", goodsName="雹懦悯庭轿"}

发现少一条 goodsName="这雪模吹契",的数据，把sql改一下，就用inner join 查一下这个条件看能不能查到。

order size is :0

结果，查不到。

分析一下，原因可能是这条数据来自两个表，但是这两个表又再两个库中，所以上面生成的8条sql都是再自己的库内查询，没有跨库查询，所以查不到，验证一下。

OrderItem{id="40776372183896064", orderNo="9F079y3U", goodsName="这雪模吹契", createdTime=Tue Nov 19 21:53:11 CST 2019, updatedTime=Tue Nov 19 21:53:11 CST 2019}

现在一个库中查询

select * from test_order0 where orderNo = "9F079y3U";

select * from test_order1 where orderNo = "9F079y3U";

select * from test_order_item0 where orderNo = "9F079y3U";

select * from test_order_item1 where orderNo = "9F079y3U";

有2条数据，果然如此，第三条不在这个库。

在去第二库中查：

只有结果3里有一条数据。验证了猜测。

这就是不仔细看官方文档的事，但是也锻炼了自己的分析能力，哈哈。

问题找到了，如何解决呢。继续分析。

….5分钟之后….

这种问题还是得去看官网的说明，如果sharding不支持这种操作，怎么分析也白搭。

这个不错，不出现笛卡儿积，提升了查询效率。

这个刚才也验证过了。

但是。。我好像配置了绑定关系了。我的test_order和test_order_item 都是按照orderNo分片。为什么还是4条sql….先跳过，找重点。

OMG！OMG！OMG！

OMG！OMG！OMG！ again！

怎么办？

上图参考文章：https://www.jianshu.com/p/32b3e91aa22c

9天之后，突然想起这篇文章中留下的问题。

然后一下就想明白了，就是这么神奇。。。

的查询没问题，只是数据插入的时候的分库分表规则有问题。

我可以让关联条件比如orderNo相同的数据都在一个库中就可以了。

所以sharding配置文改动一下。

<sharding:inline-strategy id="orderTableStrategy" sharding-column="id" algorithm-expression="test_order$->{Math.abs(id.hashCode()) % 2}" />

<sharding:inline-strategy id="databaseStrategy" sharding-column="orderNo" algorithm-expression="ds$->{Math.abs(orderNo.hashCode()) % 2}" />

<sharding:inline-strategy id="orderItemTableStrategy" sharding-column="id" algorithm-expression="test_order_item$->{Math.abs(id.hashCode()) % 2}" />

<sharding:inline-strategy id="orderItemDatabaseStrategy" sharding-column="orderNo" algorithm-expression="ds$->{Math.abs(orderNo.hashCode()) % 2}" />