分库分表-shardingsphere+springboot+mybatis-plus+druid之绑定表

绑定表
是指分片规则一致的主表和子表，从而可以避免绑定表之间关联查询出现笛卡尔积关联，对关联查询有巨大的效率提升。

设置分片规则

t_order和t_order_item作为绑定表，都以order_id为分片键。

image.png

shradingsphere配置

image.png

关联查询

由于mybatis-plus对多表关联不支持，所以我们还是采用mybatis xml方式

<mapper namespace="com.cmx.dbasy.sharding.mapper.OrderItemMapper">
    <select id="selectOrderAndItem" resultType="com.cmx.dbasy.sharding.entity.OrderItem">
        SELECT o.order_name as order_name,i.*
        FROM t_order o
        LEFT JOIN t_order_item i
        ON o.id=i.order_id
        WHERE o.id in
        <foreach item="item" index="index" collection="ids"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
</mapper>

测试

 @Test
    public void testBindingTableJoin() {
        //模拟查询2个用户，在不同数据节点查询
        orderItemService.selectOrderAndItem(new Long[]{1423561765295300610L, 1423561764427079682L});

    }

测试结果

2021-08-09 14:04:47.737  INFO 6442 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT o.order_name as order_name,i.*
        FROM t_order o
        LEFT JOIN t_order_item i
        ON o.id=i.order_id
        WHERE o.id in
         (  
            ?
         , 
            ?
         )

2021-08-09 14:04:47.738  INFO 6442 --- [           main] ShardingSphere-SQL                       : Actual SQL: test0 ::: SELECT o.order_name as order_name,i.*
        FROM t_order0 o
        LEFT JOIN t_order_item0 i
        ON o.id=i.order_id
        WHERE o.id in
         (  
            ?
         , 
            ?
         ) ::: [1423561765295300610, 1423561764427079682]
2021-08-09 14:04:47.738  INFO 6442 --- [           main] ShardingSphere-SQL                       : Actual SQL: test1 ::: SELECT o.order_name as order_name,i.*
        FROM t_order0 o
        LEFT JOIN t_order_item0 i
        ON o.id=i.order_id
        WHERE o.id in
         (  
            ?
         , 
            ?
         ) ::: [1423561765295300610, 1423561764427079682]

结果分析，显然中间件通过根据分片路由到了正确的分库和对应的分表