MySQL亿级数据下连续数值时间列的间隙高效查询SQL方案

原有查询的性能根源 #

你之前使用的自连接匹配逻辑虽然判断逻辑正确，但在近1亿行的MyISAM大表上存在本质效率问题：

• 逻辑上需要逐行取出t1.time，再对每一行的值去匹配t2.time = t1.time +1的主键记录，整体会产生1亿次左右的随机主键查找
• 哪怕查询最终只取1条结果，也会先完成全量自连接的匹配流程，无法提前终止扫描

核心优化方向 #

利用time是主键、本身按值严格有序存储的特性，放弃自连接匹配的思路，改为按主键顺序逐行读取，计算相邻两行的时间差，碰到第一个时间差大于1的位置就立刻终止查询，把全量关联的成本降到近乎顺序扫描的级别。

可直接落地的实现方案 #

MySQL 8.0及以上版本 #

使用CTE结合窗口函数的实现，执行过程中会严格按主键顺序扫描，命中第一个间隙后立刻返回，不会读取全表数据：

WITH ordered_time AS (
    SELECT 
        time,
        LAG(time) OVER (ORDER BY time) AS previous_time
    FROM candles
)
SELECT time AS gap_position
FROM ordered_time
WHERE time - previous_time > 1
ORDER BY time ASC
LIMIT 1;

如果你的MySQL版本低于8.0，不支持CTE和窗口函数，可以用自定义变量的实现达到完全一致的执行效果：

SELECT gap_position
FROM (
    SELECT
        time AS gap_position,
        @last_time AS previous_time,
        @last_time := time
    FROM candles, (SELECT @last_time := NULL) AS init_var
    ORDER BY time
) AS scan_result
WHERE gap_position - previous_time > 1
LIMIT 1;

关键执行特性：以上两种写法的执行计划都会触发主键顺序扫描，不会产生随机IO。因为表是MyISAM引擎，主键和数据文件按顺序连续存储，顺序扫描百万行数据的耗时通常也就在百毫秒级别，只要第一个间隙不是在表的最末尾位置，整体查询耗时都会远低于现有自连接方案。

极致性能优化思路 #

如果对查询延迟要求极高，可以采用二分定位的逻辑把扫描成本压到最低：

1. 先一次性获取表的时间范围：

   SELECT MIN(time) AS min_time, MAX(time) AS max_time, COUNT(*) AS row_cnt FROM candles;
   

2. 计算连续无间隙场景下的预期总行数：max_time - min_time + 1，和实际row_cnt对比，若两者相等则说明表内没有任何时间间隙
3. 若存在间隙，则对时间区间做二分排查：每次取当前区间的中点时间值，统计[区间起始值, 中点值]范围内的实际行数，和该区间的预期连续行数对比，最多只需要20-30次主键范围查询，就能精确定位到第一个间隙的位置，完全不需要做逐行扫描。

额外注意事项 #

• 不要再使用LEFT JOIN自连接的逻辑做间隙判断，1亿行规模下该写法的耗时通常会达到十分钟以上，没有任何优化空间
• 如果需要常态化监控表内的时间间隙，可以在应用层记录上次校验到的最大time值，后续查询直接从这个值开始往后扫描，基本可以做到毫秒级返回结果

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