分页查询引起的线上错误

1. 业务需求背景

开发了一个统计的系统，数据同步工作使用定时任务完成。在数据同步的过程中有一个步骤，需要同步更新一个字段。

简化举例：

已有的产品表及生产数据（good_type存在null的情况）：

CREATE TABLE `good` (
  `good_id` varchar(128) NOT NULL COMMENT '主键id',
  `good_name` varchar(128) NOT NULL COMMENT '产品名称',
  `good_type` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '产品类型（01 | 02 | 03）',
  PRIMARY KEY (`good_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='产品信息表'

统计表：

CREATE TABLE `goodstat` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `good_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '产品名称',
  `good_type` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '产品类型（01 | 02 | 03 |...）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='产品信息统计表'

前期需求设计，需要将表good中数据同步至goodStat。因中间存在业务逻辑处理，good表中的数据分两步同步到goodStat中：

1. 同步产品的基本信息（good_type除外）至goodStat中；
2. 统一更新goodStat中的good_type字段。

2. 功能初步实现

因数据量巨大，第一步一开始使用的是以下sql语句，结果锁表差点跑路。。。

Insert into Table2(field1,field2,...) select value1,value2,... from Table1

第二步也为了快速使用了批量更新语句。。。同样会锁表

-- 对统计表goodStat中good_type字段为null的数据进行关联update
update goodStat
left join good on goodStat.good_id = good.good_id
set goodStat.good_type = good.good_type
where goodStat.good_type is null;

这里贴个链接：同事埋了个坑：Insert into select 语句把生产服务器炸了！

3. 实现方法改造

既然不能一次性同步数据，那就只能分批进行处理，虽然效率下降了不少，对存量数据的同步处理时间增加了2倍左右，但对增量数据却无太大影响。

1. 批量查询，批量插入（这里不展开叙述，重点是后面）

2. 批量查询good_type，批量更新

   -- 批量查询
   <select id="selectBatch" parameterType="java.util.Map">
       select 
           goodStat.id, good.good_type
       from good
       inner join goodStat on good.good_id = goodStat.good_id
       where goodStat.good_type is null
       limit offset, rows;
   </select>
   

   -- 批量更新
   <update id="updateBatch" parameterType="java.util.List">
       update goodStat set good_type =
       <foreach collection="list" item="item" index="index" 
           separator=" " open="case id" close="end">
           when #{item.id} then #{item.good_type}
       </foreach>
       where id in
       <foreach collection="list" index="index" item="item" 
           separator="," open="(" close=")">
           #{item.id}
       </foreach>
    </update>
   

   sql的改造基本就是如此，但在生产上却出现了统计数据错误的问题（测试居然没有测出来。。），通过排查生产数据库，发现goodStat表中部分产品的good_type为null，于是在dev环境进行了模拟重现。

   结果是：

   1. good表数据量2w+，goodStat表数据量2w+，基本一致（存在脏数据清洗的操作）

   2. good表中good_type有值数据量为1.8w+，而goodStat表中只有2k+数据的good_type字段有值

4. 原因查找分析

通过debug以及对sql日志的查看，发现update语句更新数据量确实只有2000+，往前查找，发现问题出在了select语句的查找条件上。

...
where goodStat.good_type is null
...

之前使用一次性更新操作是没有问题，增加这个条件也是为了提高效率，对good_type有值的数据不进行更新操作（业务场景下good_type确定后不会改变）。

但分批查询然后更新的这种操作则不可以使用这个条件，这会导致部分数据被分页条件跳过。

先放示意图：

本来期望的是，先取出第1-1000条数据，更新good_type字段，然后取第1001-2000条数据，直至结束。

但每一次批量查询更新操作并不是所有数据都的good_type都有值，存在null的情况。

因此，如上图，在第一次操作后，有700条数据更新了good_type，但有300条数据依旧为null，那么在第二查询时需要跳过（offset）1000条good_type为null的数据，就会把本应该是在第二次查询结果中的700条数据也跳过去，并且会存在累加的情况。

或者简单讲：每一次查询的总数量是变动的，因为每update一次后，部分数据的good_type已经不是null了。

-- 批量查询总数量是变动的
select count(*) from (
    select
    	goodStat.id, good.good_type
    from good
    inner join goodStat on good.good_id = goodStat.good_id
    where goodStat.good_type is null
    /*limit offset, rows;*/
)

5. 问题解决

修改查询条件，根据上一次定时任务开始的时间查询需要更新的数据，不管good_type是否为null，对增量数据统一处理（首次上线上一次定时任务开始时间被设定为1970-1-1 00:00:00）。

-- 批量查询（lastStatTime - 上次定时任务开始时间）
select goodStat.id, good.good_type
from good inner join goodStat on good.good_id = goodStat.good_id
where 
<![CDATA[ goodStat.created_time >= lastStatTime ]]>
<![CDATA[ or goodStat.modify_time >= lastStatTime]]>
limit offset, rows;