MySQL查询新增/更新行时偶现遗漏问题排查求助

你的猜测完全正确，问题出在两个核心点上：

1. CURRENT_TIMESTAMP的生成时机：MySQL中CURRENT_TIMESTAMP(3)是在语句执行启动时计算的，和事务提交时间无关。哪怕某个操作因为锁等待、IO延迟等原因迟迟未提交，它的updatedatetime依然是语句刚启动时的时间。
2. 监控逻辑的致命缺陷：你的监控仅把下一次查询的起始时间设为本次查到的最大updatedatetime。如果某行的updatedatetime落在当前查询的时间范围内，但因提交延迟没被本次查询捕获，后续查询的起始时间已经跳到了更大的值，就会永远漏掉该行。

举你遇到的实际场景为例：

• 操作A（id=163127167）先启动，生成updatedatetime=09:51:13.050，但因延迟未提交；
• 操作B（id=163127168）后启动，生成updatedatetime=09:51:13.109，快速提交；
• 监控查询到操作B的行，将sinceDateTime更新为13.109；
• 之后操作A提交，但它的updatedatetime=13.050小于13.109，后续查询不会再覆盖这个时间范围，直接导致遗漏。

方案1：利用复合主键特性，跟踪(updatedatetime, id)最大值 #

你的表主键是(updatedatetime, id)，这是天然有序的复合索引。修改监控逻辑，同时记录上次处理的最大updatedatetime和对应最大id，用复合条件查询避免同时间戳下的遗漏：

// 初始化跟踪值
DateTime lastUpdated = DateTime.MinValue;
long lastId = 0;

while(true) {
  string sql = $@"
      SELECT *
      FROM mytable
      WHERE (updatedatetime > @lastUpdated) 
         OR (updatedatetime = @lastUpdated AND id > @lastId)
      ORDER BY updatedatetime ASC, id ASC;";

  using (var conn = new MySqlConnection(...))
  {
      var results = await conn.QueryAsync(sql, new
      {
          lastUpdated,
          lastId
      });

      if(results.Any()) {
          // 更新跟踪的最大值
          var maxRow = results.OrderByDescending(r => r.updatedatetime).ThenByDescending(r => r.id).First();
          lastUpdated = maxRow.updatedatetime;
          lastId = maxRow.id;

          Console.WriteLine($"New tracked: {lastUpdated:yyyy-MM-dd HH:mm:ss.fff} / {lastId}");
          Console.WriteLine($"Ids:\n  {string.Join("  \n", results.Select(r => r.Id))}");
      }
  }
  await Task.Delay(100);
}

注意：这个方案仅能解决同updatedatetime下提交延迟的行，无法解决updatedatetime更小但提交晚的情况。

方案2：扩大查询范围+去重，彻底解决提交延迟问题 #

每次查询从上次查询的开始时间（而非结果的最大时间）起，同时维护已处理行的集合去重，确保所有提交延迟的行都会被后续查询捕获：

DateTime queryStartTime = DateTime.Now;
// 用哈希集合记录已处理的(id, updatedatetime)组合，避免重复处理
HashSet<Tuple<long, DateTime>> processedRows = new HashSet<Tuple<long, DateTime>>();

while(true) {
  var currentQueryStartTime = DateTime.Now;

  string sql = $@"
      SELECT *
      FROM mytable
      WHERE updatedatetime >= @queryStartTime
      ORDER BY updatedatetime ASC, id ASC;";

  using (var conn = new MySqlConnection(...))
  {
      var results = await conn.QueryAsync(sql, new
      {
          queryStartTime
      });

      if(results.Any()) {
          // 过滤已处理过的行
          var newRows = results.Where(r => !processedRows.Contains(Tuple.Create(r.Id, r.updatedatetime))).ToList();
          
          if(newRows.Any()) {
              Console.WriteLine($"Query from: {queryStartTime:yyyy-MM-dd HH:mm:ss.fff}");
              Console.WriteLine($"New Ids:\n  {string.Join("  \n", newRows.Select(r => r.Id))}");
              
              // 将新行加入已处理集合
              foreach(var row in newRows) {
                  processedRows.Add(Tuple.Create(row.Id, row.updatedatetime));
              }
          }
      }
  }
  // 更新下次查询的起始时间
  queryStartTime = currentQueryStartTime;
  // 定期清理过期记录（比如保留最近1小时的，根据实际情况调整）
  processedRows.RemoveWhere(t => t.Item2 < DateTime.Now.AddHours(-1));
  await Task.Delay(100);
}

这个方案能彻底解决提交延迟导致的遗漏问题，通过定期清理已处理集合，内存占用也能得到控制。

方案3：MySQL 8.0.26+专属：利用提交时间追踪 #

如果你的MySQL版本是8.0.26及以上，可以开启innodb_commit_timestamp_enable参数，通过INFORMATION_SCHEMA.INNODB_COMMIT_TIMESTAMP表查询行的实际提交时间，用提交时间作为查询条件。但该表仅保留最近的提交记录，有时间限制，且需要额外权限。

优先选择方案2，它能覆盖所有提交延迟场景，实现成本低且稳定。如果对性能有极高要求，可以结合方案1，日常用方案1快速查询，每隔1分钟执行一次方案2的全量补查，兼顾效率和准确性。

内容的提问来源于stack exchange，提问作者Matthew Conradie