线上问题——【面试题】Mybatis-plus 整合 ShardingSphere-JDBC，调用批量方法执行更新操作扫所有分表问题

记录下 ShardingSphere 整合 mybatis-plus 进行批量更新时扫所有分表问题的原因及解决方案。ShardingSphere 整合 mybatis-plus 与整合 mybatis 流程是一样的，一个是导入 mybatis 包，一个是导入 mybatis-plus 包，在 ShardingSphere-JDBC —— 数据分片详细讲解 介绍了 ShardingSphere 分布分表及整合 mybatis 的使用示例，这里就不在赘述整合使用过程了。

过程及问题描述

在使用 ShardingSphere 整合 mybatis-plus 并调用 saveOrUpdateBatch() 批量插入或更新方法，发现数据批量插入时，ShardingSphere 的分片规则会根据分片键的值将数据正确地分散到各个分片表中；而数据批量更新时，会扫描所有分表进行更新。这是什么原因呢？

首先看我是如何使用的。

1. 数据表的设计，表字段如下，id 自增主键、text 唯一索引。这里建 32 张分表，test_table_0 … test_table_31

   CREATE TABLE `test_table` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
     `text` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '文本内容',
     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
     PRIMARY KEY (`id`),
     UNIQUE KEY `text` (`encrypt_text`) USING BTREE,
     KEY `idx_user_id` (`user_id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='文本';
   

2. ShardingSphere 相关配置，详细配置不再降速，就看看数据库表分片配置。这里设置 text 字段作为分片键。

   @Bean(name = "textTableTableRuleConfig")
   public TableRuleConfiguration textTableTableRuleConfig() {
       String tableName = "test_table";
       String actualDataNodes = "dataSource.test_table_${0..31}";
       // 分片键
       String shardingColumns = "text";
   
   	// 表规则配置
       TableRuleConfiguration configuration = new TableRuleConfiguration();
       configuration.setLogicTable(tableName);	// 设置逻辑表名
       configuration.setActualDataNodes(actualDataNodes); // 设置实际分表节点
       // 分片策略，UserEncryptTextTableAlgorithm 为自定义分片算法
       ShardingStrategyConfiguration tableShardingStrategyConfig = new StandardShardingStrategyConfiguration(
           shardingColumns, new UserEncryptTextTableAlgorithm());
   	// 数据库没分库，不用分片策略；数据表分表，设置分片策略。
       configuration.setDatabaseShardingStrategyConfig(new NoneShardingStrategyConfiguration());
       configuration.setTableShardingStrategyConfig(tableShardingStrategyConfig);
       return configuration;
   }
   

   分片算法。使用 PreciseShardingAlgorithm，是 ShardingSphere 提供的精确分片算法接口，用于处理单一分片键的分片规则。

   @Configuration
   public class UserEncryptTextTableAlgorithm implements PreciseShardingAlgorithm<String> {
   
       /**
        * availableTargetNames：可用的分片目标名称集合，即分片规则所涉及的所有分片表的集合。
        * shardingValue：PreciseShardingValue 对象，表示单一分片键的值。
        */
       @Override
       public String doSharding(Collection<String> collection, PreciseShardingValue<String> shardingValue) {
           StringBuffer tableName = new StringBuffer();
           // shardingValue.getValue()：分片键的值
           String tableSuffix = String.valueOf(Math.abs(shardingValue.getValue().hashCode()) % 32);
           tableName.append(shardingValue.getLogicTableName()).append("_").append(tableSuffix);
           return tableName.toString();
       }
   }
   

3. 业务代码操作数据表，这里调用 mybatis-plus 的批量方法。

   // mybatis-plus 操作数据库服务类
   @Autowired
   private ITestTableService iTestTableService;
   
   public static void main(String[] args) {
   	List<TestTable> testTableList = new ArrayList<>();
   	TestTable updateInfo· = new TestTable();
       updateInfo1·.setText("test1");
       updateInfo1·.setId(1);
       updateInfo1·.setCreateTime(new Date());
       testTableList.add(updateInfo1);
       TestTable updateInfo1 = new TestTable();
       updateInfo2.setText("test2");
       updateInfo2.setId(2);
       updateInfo2.setCreateTime(new Date());
       testTableList.add(updateInfo2);
       iTestTableService.saveOrUpdateBatch(testTableList);
   }
   

4. 运行结果

   配置 ShardingSphere 数据源时开启sql打印，方便在控制台查看输出的sql语句。

   Properties prop = new Properties();
   prop.setProperty(ShardingPropertiesConstant.SQL_SHOW.getKey(), true);
   

   运行条件：假设 updateInfo1 的数据已存在与表中，updateInfo2 的数据在表中不存在。在进行 iTestTableService.saveOrUpdateBatch(testTableList); 运行后。

   insert 语句只打印了一次。
   INSERT INTO test_table_10(id, text, create_time, update_time) VALUES (?,?, ?, ?);

   update 语句只打印了32次。
   UPDATE table_name_0 SET id = ?, text= ?, create_time = ?, update_time = ? WHERE id = ?;
   UPDATE table_name_1 SET id = ?, text= ?, create_time = ?, update_time = ? WHERE id = ?;
   …
   UPDATE table_name_32 SET id = ?, text= ?, create_time = ?, update_time = ? WHERE id = ?;

   看表中数据，32 张表中只有一张表中插入了 updateInfo2 的数据，说明只执行了一次 insert 操作，而且按分片规则落到指定的分表中；而 updateInfo1 在表中的数据已被修改了，说明更新了，是否是只执行了一次 update 操作呢？继续分析。

5. 分析及结论

   那打印了 32 次 update 语句，是否执行了 32 次 update 操作呢？首先看打印的 sql，saveOrUpdateBatch() 批量 update 操作打印的sql，where 条件是 id = ?，那我在另外一张不存在 updateInfo1 数据的表中插入一条数据，其 id 也等于1，再执行一遍更新操作，发现两张表的 updateInfo1 数据都进行了更新，说明确实执行了 32 次 update 操作。

   查看打印的 update sql 语句，可以看出 mybatis-plus 的 saveOrUpdateBatch() 操作批量更新时，是以 id 主键作为 where 条件来索引更新，而 id 主键又不是分片键，分片规则失效，所以每一次 update 都会扫所有分表。而对于 insert 操作，可以理解插入的字段包含 id 主键和 text 唯一索引，且 text 作为分片键，所以插入时会解析到包含分片键 text，会先进行分表定位到具体表，再插入。

原因及方案

针对 update 操作的问题：

1. 于是，我再使用 mybatis-plus 的 saveOrUpdate(T entity); 方法与 saveOrUpdate(T entity, Wrapper updateWrapper); 方法单独对 updateInfo1 进行更新操作。（mybatis-plus 现象）

   iTestTableService.saveOrUpdate(updateInfo1);
   与
   iTestTableService.saveOrUpdate(updateInfo1, Wrappers.<UserEncryptText>lambdaUpdate().eq(UserEncryptText::getText, updateInfo.getText()));
   

   这两种更新操作的结果为：不带条件的还是扫描所有分表，带条件能定位到具体分表进行更新。说明在进行 saveOrUpdate(updateInfo1) 操作更新时，mybatis-plus 默认是使用主键索引作为 where 条件进行索引的；使用 saveOrUpdate(updateInfo1, Wrappers) 操作更新时才会使用指定的 where 条件。

2. 其次，我更改分片键，使用多字段分片键的分片策略，id 与 text 字段都作为分片键。（ShardingSphere 现象）

   String shardingColumns = "id,text";
   ShardingStrategyConfiguration tableShardingStrategyConfig = new ComplexShardingStrategyConfiguration(
      shardingColumns, new UserEncryptTextTableAlgorithm());
   

   分片算法。使用 ComplexKeysShardingAlgorithm，是 ShardingSphere 中用于处理复合分片键的分片算法接口。该接口定义了根据多个分片键进行分片的方法。这里简单处理，同时存在 id、text 或 text 都映射到 text 作为分片键，只有 id 分片键就默认分到0表。

   public class UserEncryptTextTableAlgorithm implements ComplexKeysShardingAlgorithm {
       @Override
       public Collection<String> doSharding(Collection<String> collection, Collection<ShardingValue> collection1) {
           List<String> list = new ArrayList<>();
           Optional<ShardingValue> optional = collection1.stream()
               .filter(x -> StringUtils.equals(x.getColumnName(), "text")).findFirst();
           if (!optional.isPresent()){
           	StringBuffer tableName = new StringBuffer();
   	        tableName.append(shardingValue.getLogicTableName()).append("_").append(0);
               list.add(tableName.toString());
               return list;
           }
           ListShardingValue shardingValue = (ListShardingValue)optional.get();
           Collection values = shardingValue.getValues();
           values.forEach(value -> {
               StringBuffer tableName = new StringBuffer();
           	String tableSuffix = String.valueOf(Math.abs(shardingValue.getValue().hashCode()) % 32);
           	tableName.append(shardingValue.getLogicTableName()).append("_").append(tableSuffix);
               list.add(tableName.toString());
           });
   
           return list;
       }
   }
   

   1、当条件为 where id = ? 时，分片算法中的入参 collection1 集合中只有 id 分片键对象，只能使用 id 分片键进行分片算法，这里直接操作了 0 表；
   2、当条件为 where text = ? 时，分片算法中的入参 collection1 集合中只有 text 分片键对象，使用 text 分片键进行分片算法，锁定操作某一个分表；
   3、当条件为 where id = ? AND text = ? 时，分片算法中的入参 collection1 集合中有 id 和 text 分片键对象，使用 text 分片键进行分片算法，锁定操作某一个分表；
   4、当条件为 where create_time = ? 等其他非分片键时，直接跳过分片算法，不会调用到分片算法，然后会扫描所有分表；

   所以在进行 update 操作时，可以理解会先解析 where 条件，判断包含了哪些分片键，并以这些分片键按照分片算法逻辑进行分表定位到具体表，再插入；若一个分片键都不包含，分片算法失效，直接扫所有分表操作。

最终如何进行批量更新操作：

1. 方案一：轮询使用带条件的更新操作，如：saveOrUpdate(updateInfo1, Wrappers) 、update(updateInfo1, Wrappers)
2. 方案二：若要使用 saveOrUpdateBatch() 方法，将 id 设置为分片键，并且要保证 id 分片键与 text 分片键进行分片算法时能得到相同的分表。（上面示例并没有实现，只是为了简单演示。）