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

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,把数据源换成德鲁伊:

 

<bean id="ds0" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://192.168.195.11:3306/tmp_order?serverTimezone=UTC"/>
    <property name="username" value="root"/>
    <property name="password" value="123456"/>
    <property name = "filters" value = "stat" />
</bean>

<bean id="ds1" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://192.168.195.11:3307/tmp_order?serverTimezone=UTC"/>
    <property name="username" value="root"/>
    <property name="password" value="123456"/>
    <property name = "filters" value = "stat" />
</bean>

 

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

 

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

 

在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}" />

 

这样问题就解决了。

 

总结一下:

 

使用shardingSphere 做分表分库的时候,一定要注意取模条件,要使用表的关联条件做分表取模的条件,不然在多表查询的时候,如果数据不在一个库中是查不出来的。

以上是 使用shardingSphere做mysql分库分表(2)之多表联查遇到的问题 的全部内容, 来源链接: utcz.com/z/531625.html

回到顶部