MySQL高效查询需求：时间戳取整至最近半小时并补全缺失数据

问题背景 #

• 场景：有一个每30分钟执行一次的脚本，会将当前时间戳及数据插入MySQL表statuses，但脚本偶尔漏执行，导致时间序列存在数据间隙。
• 目标：生成包含所有半小时整时刻的序列，将表中时间戳取整到最近半小时后关联，无数据的行返回NULL。
• 限制：不得修改表结构、现有数据或脚本。
• 现状：原递归CTE方案在5万行数据的表上执行耗时超15分钟，无法扩展。

原方案的核心问题：递归CTE生成时间序列时迭代开销大，且关联阶段对每行数据的时间戳都做动态计算，完全无法利用索引，导致性能极差。

优化方案 #

核心思路：

1. 用非递归方式生成时间序列（利用数字笛卡尔积生成连续数字，避免递归迭代的性能损耗）
2. 预处理statuses表的时间戳，提前计算出每个数据对应的半小时整点，再与时间序列做等值关联，充分利用索引加速查询。

具体实现 #

-- 生成数字辅助集，这里生成0到10000的数字，足够覆盖多年的半小时间隔（1年约105120个间隔，可按需调整）
WITH nums AS (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
nums_expanded AS (
    SELECT a.n + b.n*10 + c.n*100 + d.n*1000 + e.n*10000 AS n
    FROM nums a, nums b, nums c, nums d, nums e
),
-- 计算时间范围的起始和结束半小时整点
time_range AS (
    SELECT
        FROM_UNIXTIME(UNIX_TIMESTAMP(MIN(`timestamp`)) - MOD(UNIX_TIMESTAMP(MIN(`timestamp`)), 1800)) AS start_ts,
        FROM_UNIXTIME(UNIX_TIMESTAMP(MAX(`timestamp`)) - MOD(UNIX_TIMESTAMP(MAX(`timestamp`)), 1800)) AS end_ts
    FROM statuses
),
-- 生成完整的半小时时间序列
timestamps AS (
    SELECT
        DATE_ADD(start_ts, INTERVAL n*30 MINUTE) AS `timestamp`
    FROM nums_expanded, time_range
    WHERE DATE_ADD(start_ts, INTERVAL n*30 MINUTE) <= end_ts
),
-- 预处理statuses表，提前计算每个数据对应的半小时整点
statuses_rounded AS (
    SELECT
        FROM_UNIXTIME(UNIX_TIMESTAMP(`timestamp`) - MOD(UNIX_TIMESTAMP(`timestamp`), 1800)) AS rounded_ts,
        `status`
    FROM statuses
)
SELECT
    t.`timestamp`,
    s.`status`
FROM timestamps t
LEFT JOIN statuses_rounded s ON t.`timestamp` = s.rounded_ts
ORDER BY t.`timestamp` ASC;

性能提升原因 #

1. 消除递归开销：用数字笛卡尔积生成连续数字，比递归CTE的迭代方式高效数倍，尤其在时间范围较大时优势明显。
2. 预处理减少计算量：提前计算statuses表的取整时间戳，避免关联阶段逐行重复计算，同时利用主键索引快速获取时间范围的起止点。
3. 等值关联高效匹配：时间序列与预处理后的表通过等值条件关联，MySQL可快速完成匹配，避免全表扫描的性能损耗。

示例验证 #

使用你提供的测试数据：

CREATE TABLE IF NOT EXISTS `statuses` (
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `status` INT NOT NULL DEFAULT '0',
    PRIMARY KEY (`timestamp`)
);

INSERT IGNORE INTO `statuses` (`timestamp`, `status`)
VALUES
    ('2023-01-01 00:03:34', '164850'),
    ('2023-01-01 00:31:23', '794088'),
    ('2023-01-01 03:31:28', '686754'),
    ('2023-01-01 04:01:15', '684711'),
    ('2023-01-01 05:31:35', '116777'),
    ('2023-01-01 06:01:52', '469332'),
    ('2023-01-01 06:31:55', '816300'),
    ('2023-01-01 08:33:53', '309583'),
    ('2023-01-01 09:03:54', '847976'),
    ('2023-01-01 09:31:33', '812517');

执行优化后的查询，会返回从2023-01-01 00:00:00到2023-01-01 09:30:00的所有半小时整点，缺失数据的行status为NULL，且执行速度远快于原递归方案。

内容的提问来源于stack exchange，提问作者Oliver M.