清理超1亿行Logs表中同IMEI间隔10分钟内冗余数据

我有两张MySQL表：

1. Controllers表：存储带有唯一IMEI编号的设备记录，表结构如下：

2. Logs表：存储各设备的日志数据，每台设备每10秒插入一条日志，当前该表已有1亿+行数据，表结构如下：

我的需求是删除同一IMEI编号下，时间间隔小于10分钟的日志行，尝试了以下两种方案均存在问题：

方案一：简单DELETE语句 #

注：代码中表名存在笔误，已统一修正为Logs

DELETE t1
FROM Logs t1
JOIN Logs t2 ON t1.id = t2.id + 1
WHERE TIMESTAMPDIFF(MINUTE, t2.created_at, t1.created_at) < 10;

执行后仅处理了约8500行数据，耗时约2分钟。问题在于：

• 仅对比了ID相邻的日志，但日志的ID和时间不一定严格对应（比如存在插入延迟）；
• 同一IMEI的日志可能被其他设备的日志穿插，导致大量符合条件的记录未被识别。

方案二：存储过程 #

注：代码中表名存在笔误，已统一修正为Controllers和Logs

DELIMITER //

CREATE PROCEDURE DeleteLogsLessThan10Minutes()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE imei_number VARCHAR(255);
    DECLARE curr_timestamp DATETIME;
    DECLARE previous_timestamp DATETIME;
    
    -- 声明游标获取Controllers表中所有唯一IMEI
    DECLARE cur CURSOR FOR 
        SELECT imei_number FROM Controllers;
    
    -- 游标结束处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO imei_number;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 创建临时表存储当前IMEI的日志时间
        CREATE TEMPORARY TABLE IF NOT EXISTS Temp_Logs (
            created_at DATETIME
        );
        
        -- 将当前IMEI的日志时间插入临时表并排序
        INSERT INTO Temp_Logs (created_at)
        SELECT created_at
        FROM Logs
        WHERE imei_number = imei_number
        ORDER BY created_at;
        
        -- 初始化时间对比变量
        SET previous_timestamp = NULL;
        
        -- 遍历临时表中的日志时间
        WHILE (SELECT COUNT(*) FROM Temp_Logs) > 0 DO
            SELECT MIN(created_at) INTO curr_timestamp FROM Temp_Logs;
            
            IF previous_timestamp IS NOT NULL AND TIMESTAMPDIFF(MINUTE, previous_timestamp, curr_timestamp) < 10 THEN
                -- 删除时间间隔小于10分钟的日志
                DELETE FROM Logs
                WHERE imei_number = imei_number AND created_at = curr_timestamp;
            END IF;
            
            SET previous_timestamp = curr_timestamp;
            
            -- 从临时表移除已处理的时间
            DELETE FROM Temp_Logs WHERE created_at = curr_timestamp;
        END WHILE;
        
        -- 删除临时表
        DROP TEMPORARY TABLE IF EXISTS Temp_Logs;
        
    END LOOP;
    
    CLOSE cur;
    
END //

DELIMITER ;

执行时出现超时问题，核心原因：

• 临时表无索引，每次查询和删除都是全表扫描，效率极低；
• 单条删除操作产生大量IO，1亿+行数据下耗时极长；
• 游标+嵌套循环的逻辑本身性能低下，无法处理大数据量。

优化解决方案 #

针对1亿+行的大数据量，必须采用高效批量处理+索引优化的方式：

1. 先添加关键索引 #

给Logs表添加复合索引，让数据库能快速定位同一IMEI的日志并按时间排序，避免全表扫描：

ALTER TABLE Logs ADD INDEX idx_imei_created (imei_number, created_at);

2. 用窗口函数标记待删除记录 #

利用LAG()窗口函数，按IMEI分组、时间排序，计算每条日志与上一条的时间差，筛选出需要删除的记录：

WITH LogsWithPrevTime AS (
    SELECT 
        id,
        LAG(created_at) OVER (PARTITION BY imei_number ORDER BY created_at) AS prev_created_at
    FROM Logs
)
SELECT id 
FROM LogsWithPrevTime
WHERE TIMESTAMPDIFF(MINUTE, prev_created_at, created_at) < 10;

先执行这个查询确认待删除记录数量，避免误操作。

3. 批量删除（推荐两种方式） #

方式一：分批次删除待标记记录

适合待删除记录数量较多的场景：

SET @batch_size = 10000; -- 可根据服务器性能调整批次大小

REPEAT
    DELETE l
    FROM Logs l
    JOIN (
        SELECT id
        FROM (
            SELECT 
                id,
                LAG(created_at) OVER (PARTITION BY imei_number ORDER BY created_at) AS prev_created_at
            FROM Logs
            LIMIT @batch_size
        ) AS sub
        WHERE TIMESTAMPDIFF(MINUTE, prev_created_at, created_at) < 10
    ) AS to_delete ON l.id = to_delete.id;
UNTIL ROW_COUNT() = 0 END REPEAT;

方式二：保留需要的记录，删除其余

适合保留记录远少于删除记录的场景（比如每10分钟只留一条）：

-- 创建临时表存储需要保留的日志ID（每个IMEI每10分钟保留最早的一条）
CREATE TEMPORARY TABLE IF NOT EXISTS Keep_Logs (
    id INT PRIMARY KEY
);

INSERT INTO Keep_Logs (id)
SELECT id
FROM (
    SELECT 
        id,
        imei_number,
        FLOOR(UNIX_TIMESTAMP(created_at) / (10*60)) AS time_window -- 按10分钟分片
    FROM Logs
) AS sub
GROUP BY imei_number, time_window
HAVING id = MIN(id);

-- 分批次删除不需要保留的日志
SET @batch_size = 10000;

REPEAT
    DELETE l
    FROM Logs l
    LEFT JOIN Keep_Logs kl ON l.id = kl.id
    WHERE kl.id IS NULL
    LIMIT @batch_size;
UNTIL ROW_COUNT() = 0 END REPEAT;

-- 清理临时表
DROP TEMPORARY TABLE Keep_Logs;

额外注意事项 #

• 务必先备份数据，避免误删导致数据丢失；
• 尽量在业务低峰期执行操作，减少对线上服务的影响；
• 若Logs表持续增长，建议改为按时间分区的分区表，后续清理旧数据时直接删除分区，效率提升数倍；
• 可调整MySQL参数（如innodb_buffer_pool_size），提升大数据操作的性能。

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