冗余表系列文章（二）冗余表的实现方案之异步写

冗余表系列文章（一）冗余表的实现方案之同步双写
冗余表系列文章（二）冗余表的实现方案之异步写

1 冗余表

1.1为什么要有冗余表

当t_order表达到500万条或2GB时需要考虑水平分表，进行水平分表需要根据某个列进行分割，假设根据userId分割。用户查询自己的订单携带着userId，因此能够定位到具体哪张表。而商家查询者自己店铺的订单，没办法确定userId，只能访问一遍所有的分表再合并结果，效率非常低。为了加快商家端的查询，可以冗余一份订单表，这份冗余表根据merchantId切分，商家访问冗余表，效率就很好。这是引入冗余表的好处，坏处是我们要维护普通表和冗余表的数据一致。

1.2冗余表的两种实现方案

1.2.1 同步双写

更新t_order的操作要执行两次，一次更新普通表，一次更新冗余表，写两次。
优点：

• 实现简单，由一次写变为两次写

• 容易维护数据的一致性

缺点：

• 代码冗余，第二次写跟第一次写的代码类似，而且每个更新的地方都要写两次

• 请求处理时间变长

1.2.2 异步写

更新请求过来，写一次数据库，再发送一条消息到消息中间件，返回响应。消费者拉取消息进行写操作。
优点：

• 处理时间是单次写

缺点

• 较复杂，引入了消息中间件
• 不容易维护数据的一致性

2 异步写

项目源码

进入async-write文件夹

2.1数据库规划

mysql3上存储订单数据，主要用于用户端访问。t_order、t_order_item表都按user_id进行水平切分为两个表，分片规则保持一致。mysq5上冗余一份订单数据，主要用于商家端访问，根据merchat_id分片。
mysql3的order_db0库上有t_order_0、t_order_1、t_order_item_0、t_order_item_1。
mysql5的order_db1库上有t_order_0、t_order_1、t_order_item_0、t_order_item_1。

2.2创建表

见源码中的order_db0、order_db1文件

2.3maven pom文件

比较主要的几个依赖。完整pom见源码。

<!--shardingsphere-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.0</version>
</dependency>
<!--        mybatis-plus-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.2</version>
</dependency>
<!--        kafka-->
<dependency>
    <groupId>org.springframework.kafka</groupId>
    <artifactId>spring-kafka</artifactId>
</dependency>

2.4创建实体类

t_order是普通表，给用户端访问的。

@TableName("t_order")
 @Data
 @AllArgsConstructor
 @NoArgsConstructor
 public class Order {
   @TableId(type = IdType.ASSIGN_ID)
   private Long orderId;
   private Long userId;
   private Long merchantId;
   private BigDecimal amount;
   private Integer orderStatusId;
 }

t_order_2是冗余表，给商家端访问的。

@TableName("t_order_2")
 @Data
 @AllArgsConstructor
 @NoArgsConstructor
 public class Order2 {
   @TableId(type = IdType.INPUT)
   private Long orderId;
   private Long userId;
   private Long merchantId;
   private BigDecimal amount;
   private Integer orderStatusId;
 }

t_order_item、t_order_item_2同理。见源码。

@Data
 public class OrderDTO {
   private Order order;
   List<OrderItem> items;
 }

2.5创建mapper

OrderMapper有个根据userId查询订单详情的方法。

public interface OrderMapper extends BaseMapper<Order> {
   List<OrderDTO> getOrderListByUserId(@Param("userId") Long userId);
 }

OrderMapper2有个根据merchantId查询订单详情的方法。

public interface OrderMapper2 extends BaseMapper<Order2> {
   List<OrderDTO> getOrderListByMerchantId(@Param("merchantId") Long merchantId);
 }

OrderItemMapper、OrderItemMapper2见源码。

2.5.1 xml

OrderMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.asyncwrite.mapper.OrderMapper">


<resultMap id="order_dto_resultmap" type="com.example.asyncwrite.model.OrderDTO">
    <association property="order" javaType="com.example.asyncwrite.model.Order">
        <id column="order_id" property="orderId" jdbcType="BIGINT" javaType="java.lang.Long"/>
        <result column="amount" property="amount"  jdbcType="DECIMAL" javaType="java.math.BigDecimal"/>
        <result column="user_id" property="userId"  jdbcType="BIGINT" javaType="java.lang.Long"/>
        <result column="merchant_id" property="merchantId"  jdbcType="BIGINT" javaType="java.lang.Long"/>
        <result column="order_status_id" property="orderStatusId"  jdbcType="INTEGER" javaType="java.lang.Integer"/>
    </association>
    <collection property="items" ofType="com.example.asyncwrite.model.OrderItem">
        <id column="order_item_id" property="orderItemId" jdbcType="BIGINT" javaType="java.lang.Long"/>
        <result column="item_amount" property="amount"  jdbcType="DECIMAL" javaType="java.math.BigDecimal"/>
        <result column="user_id" property="userId"  jdbcType="BIGINT" javaType="java.lang.Long"/>
        <result column="merchant_id" property="merchantId"  jdbcType="BIGINT" javaType="java.lang.Long"/>
    </collection>
</resultMap>

<select id="getOrderListByUserId" resultMap="order_dto_resultmap" parameterType="long">
    select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,
           oi.amount as item_amount, oi.order_item_id
    from t_order as o join t_order_item as oi on o.order_id = oi.order_id
    where o.user_id = #{userId,jdbcType=BIGINT}
</select>

</mapper>

OrderMapper2.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.asyncwrite.mapper.OrderMapper2">

    <resultMap id="order_dto_resultmap" type="com.example.asyncwrite.model.OrderDTO">
        <association property="order" javaType="com.example.asyncwrite.model.Order">
            <id column="order_id" property="orderId" jdbcType="BIGINT" javaType="java.lang.Long"/>
            <result column="amount" property="amount"  jdbcType="DECIMAL" javaType="java.math.BigDecimal"/>
            <result column="user_id" property="userId"  jdbcType="BIGINT" javaType="java.lang.Long"/>
            <result column="merchant_id" property="merchantId"  jdbcType="BIGINT" javaType="java.lang.Long"/>
            <result column="order_status_id" property="orderStatusId"  jdbcType="INTEGER" javaType="java.lang.Integer"/>
        </association>
        <collection property="items" ofType="com.example.asyncwrite.model.OrderItem">
            <id column="order_item_id" property="orderItemId" jdbcType="BIGINT" javaType="java.lang.Long"/>
            <result column="item_amount" property="amount"  jdbcType="DECIMAL" javaType="java.math.BigDecimal"/>
            <result column="user_id" property="userId"  jdbcType="BIGINT" javaType="java.lang.Long"/>
            <result column="merchant_id" property="merchantId"  jdbcType="BIGINT" javaType="java.lang.Long"/>
        </collection>
    </resultMap>


    <select id="getOrderListByMerchantId" resultMap="order_dto_resultmap">
        select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,
               oi.amount as item_amount, oi.order_item_id
        from t_order_2 as o join t_order_item_2 as oi on o.order_id = oi.order_id
        where o.merchant_id = #{merchantId,jdbcType=BIGINT}
    </select>
</mapper>

2.6配置文件

完整配置见源码application.properties。

2.6.1 运行模式、持久化仓库

# 运行模式: 单机模式
 spring.shardingsphere.mode.type=Standalone
 # 持久化仓库类型
 spring.shardingsphere.mode.repository.type=JDBC
 # 持久化仓库所需属性
 spring.shardingsphere.mode.repository.props.provider=H2
 spring.shardingsphere.mode.repository.props.jdbc_url=jdbc:h2:mem:config;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MYSQL
 spring.shardingsphere.mode.repository.props.username=root
 spring.shardingsphere.mode.repository.props.password=

2.6.2 数据源

# 配置真实数据源
 spring.shardingsphere.datasource.names=ds1,ds2
 # 配置第 1 个数据源
 spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
 spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
 spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://mysql3:3306/order_db0
 spring.shardingsphere.datasource.ds1.username=root
 spring.shardingsphere.datasource.ds1.password= 
 # 配置第 2 个数据源
 spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource
 spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
 spring.shardingsphere.datasource.ds2.jdbc-url=jdbc:mysql://mysql5:3306/order_db1
 spring.shardingsphere.datasource.ds2.username=root
 spring.shardingsphere.datasource.ds2.password=

2.6.3 t_order、t_order_item（用户端）分片

根据userId分片

# 用户端
 # 标准分片表配置
 # 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
 spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=ds1.t_order_$->{0..1}
 spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=ds1.t_order_item_$->{0..1}
 # 分库策略，缺省表示使用默认分库策略，以下的分片策略只能选其一
 # 用于单分片键的标准分片场景
 # 分片列名称
 spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=user_id
 # 分片算法名称
 spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=my_mod
 spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-column=user_id
 spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-name=my_mod

2.6.4 t_order_2、t_order_item_2（商家端）分片

根据merchantId分片

# 商家端
 spring.shardingsphere.rules.sharding.tables.t_order_2.actual-data-nodes=ds2.t_order_$->{0..1}
 spring.shardingsphere.rules.sharding.tables.t_order_item_2.actual-data-nodes=ds2.t_order_item_$->{0..1}
 spring.shardingsphere.rules.sharding.tables.t_order_2.table-strategy.standard.sharding-column=merchant_id
 spring.shardingsphere.rules.sharding.tables.t_order_2.table-strategy.standard.sharding-algorithm-name=my_mod
 spring.shardingsphere.rules.sharding.tables.t_order_item_2.table-strategy.standard.sharding-column=merchant_id
 spring.shardingsphere.rules.sharding.tables.t_order_item_2.table-strategy.standard.sharding-algorithm-name=my_mod

2.6.5 绑定表、分片算法

# 分片算法配置
 # 分片算法类型
 spring.shardingsphere.rules.sharding.sharding-algorithms.my_mod.type=MOD
 # 分片算法属性配置
 spring.shardingsphere.rules.sharding.sharding-algorithms.my_mod.props.sharding-count=2
 # 绑定表规则列表
 spring.shardingsphere.rules.sharding.binding-tables[0]=t_order,t_order_item
 spring.shardingsphere.rules.sharding.binding-tables[1]=t_order_2,t_order_item_2
 spring.shardingsphere.props.sql-show=true

2.6.6 kafka

# kafka
 spring.kafka.bootstrap-servers[0]=kafka9:9092
 spring.kafka.client-id=user_order
 spring.kafka.consumer.auto-offset-reset=latest

 spring.kafka.consumer.group-id=order_new_consumer_group_1
 spring.kafka.consumer.key-deserializer=org.apache.kafka.common.serialization.StringDeserializer
 spring.kafka.consumer.value-deserializer=org.apache.kafka.common.serialization.StringDeserializer

 spring.kafka.producer.key-serializer=org.apache.kafka.common.serialization.StringSerializer
 spring.kafka.producer.value-serializer=org.apache.kafka.common.serialization.StringSerializer

2.7服务

2.7.1 用户服务新增订单

使用OrderMapper、OrderItemMapper新增订单，写到普通表，发送订单信息到”order_new”主题。

@Service
 public class UserOrderService {

   @Autowired
   private OrderMapper orderMapper;
   @Autowired
   private OrderItemMapper itemMapper;
   @Autowired
   private KafkaTemplate<String, String> kafkaTemplate;

   public void createOrder() {
     for (long i = 0; i < 12; i++) {
       Order order = new Order(null, i + 10000, i + 20001, new BigDecimal(0), 1);
       List<OrderItem> list = new ArrayList<>();
       for (long j = 0; j < 3; j++) {
         OrderItem orderItem = new OrderItem(null, order.getOrderId(), order.getUserId(), order.getMerchantId(), new BigDecimal(2));
         list.add(orderItem);
       }
       for (OrderItem orderItem : list) {
         order.setAmount(order.getAmount().add(orderItem.getAmount()));
       }
       orderMapper.insert(order);
       for (OrderItem orderItem : list) {
         orderItem.setOrderId(order.getOrderId());
         itemMapper.insert(orderItem);
       }
       OrderDTO orderDTO = new OrderDTO();
       orderDTO.setOrder(order);
       orderDTO.setItems(list);
       kafkaTemplate.send("order_new", JSON.toJSONString(orderDTO));
     }

   }


 }

2.7.2 异步写到冗余表

消费者监听”order_new”主题，当有新消息时，使用OrderMapper2、OrderItemMapper2新增订单，写到冗余表

@Configuration
 @Slf4j
 public class WriteConsumer {

   @Bean
   public NewTopic topic() {
     return TopicBuilder.name("order_new")
         .partitions(3)
         .replicas(1)
         .build();
   }

   @Autowired
   private OrderMapper2 orderMapper222;

   @Autowired
   private OrderItemMapper2 itemMapper222;

   @KafkaListener(id = "orderWew", topics = "order_new")
   public void orderWew(String in) {
     OrderDTO orderDTO = JSON.parseObject(in, OrderDTO.class);
     log.info(orderDTO.toString());
     Order2 order2 = new Order2();
     BeanUtils.copyProperties(orderDTO.getOrder(), order2);
     orderMapper222.insert(order2);
     for (OrderItem item : orderDTO.getItems()) {
       OrderItem2 orderItem2 = new OrderItem2();
       BeanUtils.copyProperties(item, orderItem2);
       itemMapper222.insert(orderItem2);
     }

   }


 }

2.8用户查询

@Service
 public class UserOrderService {

   @Autowired
   private OrderMapper orderMapper;
   @Autowired
   private OrderItemMapper itemMapper;
   @Autowired
   private KafkaTemplate<String, String> kafkaTemplate;

   public List<OrderDTO> getOrderListByUserId(Long userId) {
     return orderMapper.getOrderListByUserId(userId);
   }

 }

2.8.1 商家查询

@Service
 public class MerchantOrderService {

   @Autowired
   private OrderMapper2 orderMapper2;

   public List<OrderDTO> getOrderListByMerchantId(@Param("merchantId") Long merchantId) {
     return orderMapper2.getOrderListByMerchantId(merchantId);
   }

 }

2.9控制器

@RestController
 public class OrderController {
   @Autowired
   private UserOrderService userOrderService;
   @Autowired
   private MerchantOrderService merchantOrderService;

   @GetMapping("/user/create")
   public void createOrder() {
     userOrderService.createOrder();
   }

   @GetMapping("/user/order-list/{userId}")
   public Object getOrderListByUserId(@PathVariable("userId") Long userId) {
     return userOrderService.getOrderListByUserId(userId);
   }

   @GetMapping("/merchant/order-list/{merchantId}")
   public Object getOrderListByMerchantId(@PathVariable("merchantId") Long merchantId) {
     return merchantOrderService.getOrderListByMerchantId(merchantId);
   }

2.10 测试

2.10.1 创建订单

http://localhost:8080/user/create

2.10.2 用户查询

2.10.2.1 userId为奇数

http://localhost:8080/user/order-list/10001

结果正确

[{"order":{"orderId":1595244847462182914,"userId":10001,"merchantId":20002,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847462182915,"orderId":null,"userId":10001,"merchantId":20002,"amount":2.00}]},{"order":{"orderId":1595244847462182914,"userId":10001,"merchantId":20002,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847525097473,"orderId":null,"userId":10001,"merchantId":20002,"amount":2.00}]},{"order":{"orderId":1595244847462182914,"userId":10001,"merchantId":20002,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847588012033,"orderId":null,"userId":10001,"merchantId":20002,"amount":2.00}]}]

只有一个Actual SQL。入参userId为奇数，选择的是ds1数据源（即mysql3-order_db0），关联的表是t_order_1、t_order_item_1。

2022-11-23 12:15:57.746 INFO 12992 --- [nio-8080-exec-1] ShardingSphere-SQL            : Logic SQL: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​    from t_order as o join t_order_item as oi on o.order_id = oi.order_id

​    where o.user_id = ?

2022-11-23 12:15:57.748 INFO 12992 --- [nio-8080-exec-1] ShardingSphere-SQL            : Actual SQL: ds1 ::: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​    from t_order_1 as o join t_order_item_1 as oi on o.order_id = oi.order_id

​    where o.user_id = ? ::: [10001]

2.10.2.2 userId为偶数

http://localhost:8080/user/order-list/10002

[{"order":{"orderId":1595244847667703810,"userId":10002,"merchantId":20003,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847718035457,"orderId":null,"userId":10002,"merchantId":20003,"amount":2.00}]},{"order":{"orderId":1595244847667703810,"userId":10002,"merchantId":20003,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847718035458,"orderId":null,"userId":10002,"merchantId":20003,"amount":2.00}]},{"order":{"orderId":1595244847667703810,"userId":10002,"merchantId":20003,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847780950017,"orderId":null,"userId":10002,"merchantId":20003,"amount":2.00}]}]

只有一个Actual SQL。入参userId为偶数，选择的是ds1数据源（即mysql3-order_db0），关联的表是t_order_0、t_order_item_0。

2022-11-23 12:21:12.638 INFO 12992 --- [nio-8080-exec-4] ShardingSphere-SQL            : Logic SQL: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​    from t_order as o join t_order_item as oi on o.order_id = oi.order_id

​    where o.user_id = ?

2022-11-23 12:21:12.639 INFO 12992 --- [nio-8080-exec-4] ShardingSphere-SQL            : Actual SQL: ds1 ::: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​    from t_order_0 as o join t_order_item_0 as oi on o.order_id = oi.order_id

​    where o.user_id = ? ::: [10002]

2.10.3 商家查询

2.10.3.1 merchantId为奇数

http://localhost:8080/merchant/order-list/20001

[{"order":{"orderId":1595244840621273089,"userId":10000,"merchantId":20001,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244846082256897,"orderId":null,"userId":10000,"merchantId":20001,"amount":2.00}]},{"order":{"orderId":1595244840621273089,"userId":10000,"merchantId":20001,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244846145171457,"orderId":null,"userId":10000,"merchantId":20001,"amount":2.00}]},{"order":{"orderId":1595244840621273089,"userId":10000,"merchantId":20001,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244846203891713,"orderId":null,"userId":10000,"merchantId":20001,"amount":2.00}]}]

只有一个Actual SQL。入参merchantId为奇数，选择的是ds2数据源（即mysql5-order_db1），关联的表是t_order_1、t_order_item_1。

2022-11-23 12:23:40.547 INFO 12992 --- [nio-8080-exec-6] ShardingSphere-SQL            : Logic SQL: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​    from t_order_2 as o join t_order_item_2 as oi on o.order_id = oi.order_id

​    where o.merchant_id = ?

2022-11-23 12:23:40.547 INFO 12992 --- [nio-8080-exec-6] ShardingSphere-SQL            : Actual SQL: ds2 ::: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​    from t_order_1 as o join t_order_item_1 as oi on o.order_id = oi.order_id

​    where o.merchant_id = ? ::: [20001]

2.10.3.2 merchantId为偶数

http://localhost:8080/merchant/order-list/20002

[{"order":{"orderId":1595244847462182914,"userId":10001,"merchantId":20002,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847462182915,"orderId":null,"userId":10001,"merchantId":20002,"amount":2.00}]},{"order":{"orderId":1595244847462182914,"userId":10001,"merchantId":20002,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847525097473,"orderId":null,"userId":10001,"merchantId":20002,"amount":2.00}]},{"order":{"orderId":1595244847462182914,"userId":10001,"merchantId":20002,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847588012033,"orderId":null,"userId":10001,"merchantId":20002,"amount":2.00}]}]

只有一个Actual SQL。入参merchantId为偶数，选择的是ds2数据源（即mysql5-order_db1），关联的表是t_order_0、t_order_item_0。

2022-11-23 12:25:45.910 INFO 12992 --- [io-8080-exec-10] ShardingSphere-SQL            : Logic SQL: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​    from t_order_2 as o join t_order_item_2 as oi on o.order_id = oi.order_id

​    where o.merchant_id = ?

2022-11-23 12:25:45.911 INFO 12992 --- [io-8080-exec-10] ShardingSphere-SQL            : Actual SQL: ds2 ::: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​     from t_order_0 as o join t_order_item_0 as oi on o.order_id = oi.order_id

​    where o.merchant_id = ? ::: [20002]

2.10.4 删除绑定表配置

注释掉application.properties中的

## 绑定表规则列表
#spring.shardingsphere.rules.sharding.binding-tables[0]=t_order,t_order_item
#spring.shardingsphere.rules.sharding.binding-tables[1]=t_order_2,t_order_item_2

访问http://localhost:8080/user/order-list/10002

结果依然正确

[{"order":{"orderId":1595244847667703810,"userId":10002,"merchantId":20003,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847718035457,"orderId":null,"userId":10002,"merchantId":20003,"amount":2.00}]},{"order":{"orderId":1595244847667703810,"userId":10002,"merchantId":20003,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847718035458,"orderId":null,"userId":10002,"merchantId":20003,"amount":2.00}]},{"order":{"orderId":1595244847667703810,"userId":10002,"merchantId":20003,"amount":6.00,"orderStatusId":1},"items":[{"orderItemId":1595244847780950017,"orderId":null,"userId":10002,"merchantId":20003,"amount":2.00}]}]

有两个Actual SQL。入参userId为偶数，选择的是ds1数据源（即mysql3-order_db0）。
第一个sql关联的表是t_order_0、t_order_item_0。
第一个sql关联的表是t_order_0、t_order_item_1。
说明sharding-jdbc没有对t_order_item使用分片，因为我们并没有给出t_order_item的user_id条件

2022-11-23 12:31:10.563 INFO 18036 --- [nio-8080-exec-1] ShardingSphere-SQL            : Logic SQL: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​    from t_order as o join t_order_item as oi on o.order_id = oi.order_id

​    where o.user_id = ?

2022-11-23 12:31:10.564 INFO 18036 --- [nio-8080-exec-1] ShardingSphere-SQL            : Actual SQL: ds1 ::: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​    from t_order_0 as o join t_order_item_0 as oi on o.order_id = oi.order_id

​    where o.user_id = ? ::: [10002]

2022-11-23 12:31:10.564 INFO 18036 --- [nio-8080-exec-1] ShardingSphere-SQL            : Actual SQL: ds1 ::: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

​        oi.amount as item_amount, oi.order_item_id

​    from t_order_0 as o join t_order_item_1 as oi on o.order_id = oi.order_id

​    where o.user_id = ? ::: [10002]

2.10.4.1 修改sql，增加t_order_item的user_id条件

<select id="getOrderListByUserId" resultMap="order_dto_resultmap" parameterType="long">
   select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,
      oi.amount as item_amount, oi.order_item_id
   from t_order as o join t_order_item as oi on o.order_id = oi.order_id
   where o.user_id = #{userId,jdbcType=BIGINT} and oi.user_id = #{userId,jdbcType=BIGINT}
 </select>

结果跟未删除绑定表之前一样。绑定表就是分片规则一致的作为一组。这说明配置了绑定表，在关联查询时我们只需要给出一个表的条件即可。

2022-11-23 12:37:21.429 INFO 19420 --- [nio-8080-exec-1] ShardingSphere-SQL            : Logic SQL: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

        oi.amount as item_amount, oi.order_item_id

    from t_order as o join t_order_item as oi on o.order_id = oi.order_id

    where o.user_id = ? and oi.user_id = ?

2022-11-23 12:37:21.430 INFO 19420 --- [nio-8080-exec-1] ShardingSphere-SQL            : Actual SQL: ds1 ::: select o.order_id, o.amount, o.user_id, o.merchant_id, o.order_status_id,

        oi.amount as item_amount, oi.order_item_id

    from t_order_0 as o join t_order_item_0 as oi on o.order_id = oi.order_id

    where o.user_id = ? and oi.user_id = ? ::: [10002, 10002]