MySQL间隔与孤岛查询优化：避免临时表与文件排序

问题背景 #

现有messages表按Arrival字段做日历对齐的按月分区，主键为(SenderID, Arrival, ID)，需关联Senders表筛选活跃发送者，找出消息发送间隔超过指定时长（如5天）的间隙。当前用LAG/LEAD窗口函数的CTE查询在小数据集正常，但面对每月1亿条消息、1.2万活跃发送者的场景，执行耗时超1小时甚至导致MySQL崩溃。EXPLAIN显示存在临时表、文件排序及不可缓存派生表，重写查询仍未解决。

表结构 #

CREATE TABLE `messages` (
    `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `Arrival` TIMESTAMP NOT NULL,
    `SenderID` INT UNSIGNED NOT NULL,
    -- 消息描述字段省略
    PRIMARY KEY (`SenderID`, `Arrival`, `ID`) USING BTREE,
    INDEX `ID` (`ID`) USING BTREE,
    INDEX `Arrival_SenderID` (`Arrival`, `SenderID`) USING BTREE
)

表按PARTITION BY RANGE(UNIX_TIMESTAMP(Arrival))按月分区。

当前查询（参数说明：@aFrom和@aTo为2024年12月，@aDays=5） #

WITH t AS (
    SELECT `Messages`.`SenderID`, `Messages`.`Arrival`,
        LAG(`Arrival`) OVER(PARTITION BY `Messages`.`SenderID` ORDER BY `Messages`.`Arrival`) AS `Prev`,
        LEAD(`Arrival`) OVER(PARTITION BY `Messages`.`SenderID` ORDER BY `Messages`.`Arrival`) AS `Next`
    FROM `Messages`
        INNER JOIN `Senders` ON `Senders`.`SenderID` = `Messages`.`SenderID` AND `Senders`.`Active` IS TRUE
    WHERE `Arrival` BETWEEN @aFrom AND @aTo
)
SELECT `SenderID`, IFNULL(`Prev`, @aFrom) AS `From`, `Arrival` AS `To` FROM t
WHERE TIMESTAMPDIFF(SECOND, IFNULL(`Prev`, @aFrom), `Arrival`) > @aDays * 24 * 3600
    UNION
SELECT `SenderID`, `Arrival` AS `From`, IFNULL(`Next`, @aTo) AS `To` FROM t
WHERE TIMESTAMPDIFF(SECOND, `Arrival`, IFNULL(`Next`, @aTo)) > @aDays * 24 * 3600
ORDER BY `SenderID`, `From`;

现有问题 #

EXPLAIN输出显示存在Using temporary; Using filesort及UNCACHEABLE DERIVED，Senders.SenderID为唯一索引。示例场景：时间范围2024-12-01至2024-12-31，@aDays=5时，需找出如SenderID 42月末间隙、85中间间隙等符合条件的记录。

优化方案 #

1. 提前过滤活跃发送者，缩小数据集 #

先将活跃发送者存入内存临时表，避免每次查询都扫描Senders表，提升关联效率：

-- 创建内存临时表存储活跃发送者
CREATE TEMPORARY TABLE active_senders ENGINE=MEMORY
SELECT SenderID FROM Senders WHERE Active IS TRUE;
ALTER TABLE active_senders ADD PRIMARY KEY (SenderID);

-- 基于临时表执行查询
WITH t AS (
    SELECT m.SenderID, m.Arrival,
        LAG(m.Arrival) OVER(PARTITION BY m.SenderID ORDER BY m.Arrival) AS Prev,
        LEAD(m.Arrival) OVER(PARTITION BY m.SenderID ORDER BY m.Arrival) AS Next
    FROM messages m
    JOIN active_senders s ON m.SenderID = s.SenderID
    WHERE m.Arrival BETWEEN @aFrom AND @aTo
)
SELECT SenderID, IFNULL(Prev, @aFrom) AS `From`, Arrival AS `To` FROM t
WHERE TIMESTAMPDIFF(SECOND, IFNULL(Prev, @aFrom), Arrival) > @aDays * 24 * 3600
UNION ALL
SELECT SenderID, Arrival AS `From`, IFNULL(Next, @aTo) AS `To` FROM t
WHERE TIMESTAMPDIFF(SECOND, Arrival, IFNULL(Next, @aTo)) > @aDays * 24 * 3600
ORDER BY SenderID, `From`;

2. 替换窗口函数为自连接，避免不可缓存派生表 #

利用主键(SenderID, Arrival, ID)的有序性，用自连接计算前后消息时间，减少窗口函数带来的临时表开销：

-- 计算每个消息的前一条消息时间
WITH prev_messages AS (
    SELECT 
        m1.SenderID,
        m1.Arrival,
        MAX(m2.Arrival) AS Prev
    FROM messages m1
    JOIN active_senders s ON m1.SenderID = s.SenderID
    LEFT JOIN messages m2 
        ON m1.SenderID = m2.SenderID 
        AND m2.Arrival < m1.Arrival
        AND m2.Arrival BETWEEN @aFrom AND @aTo
    WHERE m1.Arrival BETWEEN @aFrom AND @aTo
    GROUP BY m1.SenderID, m1.Arrival
),
-- 计算每个消息的后一条消息时间
next_messages AS (
    SELECT 
        m1.SenderID,
        m1.Arrival,
        MIN(m2.Arrival) AS Next
    FROM messages m1
    JOIN active_senders s ON m1.SenderID = s.SenderID
    LEFT JOIN messages m2 
        ON m1.SenderID = m2.SenderID 
        AND m2.Arrival > m1.Arrival
        AND m2.Arrival BETWEEN @aFrom AND @aTo
    WHERE m1.Arrival BETWEEN @aFrom AND @aTo
    GROUP BY m1.SenderID, m1.Arrival
),
-- 合并前后时间数据
combined AS (
    SELECT 
        p.SenderID,
        p.Arrival,
        p.Prev,
        n.Next
    FROM prev_messages p
    JOIN next_messages n ON p.SenderID = n.SenderID AND p.Arrival = n.Arrival
)
-- 筛选符合条件的间隙
SELECT SenderID, IFNULL(Prev, @aFrom) AS `From`, Arrival AS `To` FROM combined
WHERE TIMESTAMPDIFF(SECOND, IFNULL(Prev, @aFrom), Arrival) > @aDays * 24 * 3600
UNION ALL
SELECT SenderID, Arrival AS `From`, IFNULL(Next, @aTo) AS `To` FROM combined
WHERE TIMESTAMPDIFF(SECOND, Arrival, IFNULL(Next, @aTo)) > @aDays * 24 * 3600
ORDER BY SenderID, `From`;

3. 利用分区特性，直接指定目标分区 #

由于表是按月分区，手动指定查询分区，避免扫描无关分区：

-- 假设2024年12月对应的分区名为p202412
WITH t AS (
    SELECT m.SenderID, m.Arrival,
        LAG(m.Arrival) OVER(PARTITION BY m.SenderID ORDER BY m.Arrival) AS Prev,
        LEAD(m.Arrival) OVER(PARTITION BY m.SenderID ORDER BY m.Arrival) AS Next
    FROM messages m PARTITION (p202412)
    JOIN active_senders s ON m.SenderID = s.SenderID
    WHERE m.Arrival BETWEEN @aFrom AND @aTo
)
-- 后续查询逻辑同前

4. 替换UNION为UNION ALL，减少额外开销 #

原查询用UNION会自动去重排序，改用UNION ALL后手动排序，避免不必要的临时表操作：

WITH t AS (
    SELECT m.SenderID, m.Arrival,
        LAG(m.Arrival) OVER(PARTITION BY m.SenderID ORDER BY m.Arrival) AS Prev,
        LEAD(m.Arrival) OVER(PARTITION BY m.SenderID ORDER BY m.Arrival) AS Next
    FROM messages m
    JOIN active_senders s ON m.SenderID = s.SenderID
    WHERE m.Arrival BETWEEN @aFrom AND @aTo
)
SELECT SenderID, IFNULL(Prev, @aFrom) AS `From`, Arrival AS `To` FROM t
WHERE TIMESTAMPDIFF(SECOND, IFNULL(Prev, @aFrom), Arrival) > @aDays * 24 * 3600
UNION ALL
SELECT SenderID, Arrival AS `From`, IFNULL(Next, @aTo) AS `To` FROM t
WHERE TIMESTAMPDIFF(SECOND, Arrival, IFNULL(Next, @aTo)) > @aDays * 24 * 3600
ORDER BY SenderID, `From`;

5. 调整MySQL配置参数 #

针对大查询场景，调大以下参数减少磁盘IO和排序开销：

• tmp_table_size和max_heap_table_size：设置为足够容纳临时数据的大小，避免使用磁盘临时表
• sort_buffer_size：提升排序缓冲区大小，减少文件排序次数
• join_buffer_size：增大连接缓冲区，优化自连接性能

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