MySQL EVENT定时插入数据时偶发SELECT查询遗漏记录问题求助

记录遗漏核心来自两个常见问题：

1. 初始时间区间逻辑错误：原写法将区间结束值设为<= 1分钟前的整分时间，导致目标区间最后1分钟的1-59秒数据完全被排除，这是初期大量漏数的核心原因。
2. 写入延迟与快照可见性问题：调整后仍存在漏数，是因为事件执行时，部分落在目标区间的业务数据还未完成事务提交，InnoDB的读提交隔离级别下，未提交的数据对查询不可见；等手动排查时事务已经提交，所以能查到完整数据。

• 确认CUSTOMER表TIMESTAMP字段类型：如果是字符串类型，需排查是否存在写入格式不统一、跨时区写入导致的字符串匹配错误；优先改为DATETIME(3)类型支持毫秒精度，同时可正常走索引。
• 增加执行埋点日志：在存储过程开头新增日志写入，记录事件实际执行时间、查询的时间区间、查询返回的行数、汇总表插入行数，确认漏数发生在查询阶段还是插入阶段。
• 排查事务提交延迟：统计目标区间内数据的实际事务提交时间，确认是否存在大于事件执行时间的记录，即可验证写入延迟问题。
• 检查事件调度状态：查询information_schema.EVENTS表确认事件的实际执行间隔、是否存在调度延迟，避免事件漂移导致时间区间计算错误。
• 排查汇总表写入逻辑：确认汇总表是否存在唯一键冲突，若插入逻辑未做冲突处理（如ON DUPLICATE KEY UPDATE），冲突记录会静默丢弃。

避免使用SUBSTRING_INDEX这类字符串处理函数，会导致TIMESTAMP字段索引失效，查询性能下降同时提升延迟概率，推荐直接使用时间函数计算区间：

SELECT ID, NAME, COUNT(*) AS TOT_COUNT
FROM CUSTOMER 
WHERE 
  -- 取当前分钟整分，往前推2分钟作为区间左边界
  TIMESTAMP >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00'), INTERVAL 2 MINUTE)
  -- 当前分钟整分作为区间右边界，左闭右开覆盖前2个完整分钟的所有数据
  AND TIMESTAMP < DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00')
GROUP BY ID, NAME;

如果要彻底解决事件延迟、写入延迟导致的漏数问题，建议引入水位表机制，不再依赖当前执行时间计算区间：

1. 先创建水位表记录上次处理的最大时间：

CREATE TABLE SYNC_WATERMARK (
  TABLE_NAME VARCHAR(64) PRIMARY KEY COMMENT '要同步的源表名',
  LAST_PROCESSED_TS DATETIME NOT NULL COMMENT '上次处理到的时间点'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 初始化水位
INSERT INTO SYNC_WATERMARK VALUES ('CUSTOMER', '2024-01-01 00:00:00');

2. 存储过程内改用水位驱动查询：

-- 加行锁避免并发执行问题
SELECT @last_processed_ts := LAST_PROCESSED_TS FROM SYNC_WATERMARK WHERE TABLE_NAME = 'CUSTOMER' FOR UPDATE;
-- 本次处理到当前分钟整分
SET @current_end_ts := DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00');

-- 按水位区间查询
SELECT ID, NAME, COUNT(*) AS TOT_COUNT
FROM CUSTOMER 
WHERE TIMESTAMP >= @last_processed_ts
  AND TIMESTAMP < @current_end_ts
GROUP BY ID, NAME;

-- 写入汇总表逻辑...

-- 更新水位
UPDATE SYNC_WATERMARK SET LAST_PROCESSED_TS = @current_end_ts WHERE TABLE_NAME = 'CUSTOMER';

该方案下哪怕事件调度延迟数分钟，也会自动补全中间未处理的区间，不会漏数。

• 调整事件执行时机：如果写入延迟普遍在10秒内，可以将事件执行时间改为每个分钟的第10秒执行，给写入留足提交窗口。
• 调整事务隔离级别：如果业务允许，可以将存储过程内的查询隔离级别调整为读未提交，不过要注意可能出现脏读。
• 增加兜底校验逻辑：每日凌晨跑一次日终校验任务，对比主表当日汇总和Summary表当日汇总的差值，补全遗漏数据。

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