我只想保留5天的数据，如果数据少于保留日期，则删除其余数据.需要记住每天2000000行生成

Question

DELIMITER $$
CREATE PROCEDURE sp_delete_data()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE loop_counter INT DEFAULT 0;
DECLARE retain_days datetime;
DECLARE delete_days datetime;
SET loop_counter=(SELECT ROUND(count(*)/100,0) FROM data2 WHERE datetime<(SELECT  DATE_ADD(min(datetime),INTERVAL 1 DAY) FROM data2));
SET retain_days=(SELECT DATE_SUB(now(),INTERVAL 5 DAY)); -- 5 days data will keep
SET delete_days =(SELECT  DATE_ADD(min(datetime),INTERVAL 1 DAY) FROM data2); -- check old data from table data2
WHILE i <= loop_counter DO
IF retain_days>delete_days THEN
DELETE FROM data2 where datetime<delete_days LIMIT 1000;
END IF;
SET i = i + 1;
END WHILE;  
END$$
DELIMITER ;

我只想保留5天的数据，如果数据少于保留日期，则删除其余数据。由于每天数据生成近2000000行，这就是为什么很难一次性删除整个数据的原因。这就是为什么我想在每个循环中删除100000个数据。在这里loop_counter变量，用于查找今天的数据应该使用多少个循环。retain_days变量定义以查找保留日期delete_days变量定义为查找已删除的日期。基于retain_days和delete_days，可变数据将保留和删除。最后，此过程将每1天通过事件调用一次。

我的代码循环没有按预期工作。需要专家解决方案。如果删除这样的数据有任何性能问题，请告诉我。提前感谢

Answer 1

只需创建一个每天运行一次的事件：

CREATE EVENT purge_old_data
ON SCHEDULE EVERY '1' DAY
STARTS CURRENT_TIMESTAMP()
ON COMPLETION PRESERVE
COMMENT 'Delete rows older than 5 days'
DO 
BEGIN
DELETE
FROM data2
WHERE `datetime` < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 DAY);
END;

您不应该使用循环来删除较小的块。在SQL数据库中，循环实际上会使性能变差。如果你必须在第一次运行中删除数以百万计的100行，这对MariaDB来说真的不是问题。

Answer 2

PARTITION BY RANGE，每个分区都有2小时的数据。那么CCD_ 2将非常快速地丢弃数据——比CCD_ 3好得多。

有关使用分区的详细信息：http://mysql.rjweb.org/doc.php/partitionmaint

备选方案：http://mysql.rjweb.org/doc.php/deletebig

特别是，第二个链接显示了如何通过PRIMARY KEY"连续"运行数据，一次删除1000行。完成后重复。

注意：以下是有问题的：

DELETE FROM data2
where datetime<delete_days LIMIT 1000;

如果没有INDEX(datetime)，它将花费大量时间查找要删除的行。使用这样的索引，仍然存在在索引和数据之间跳1000次的开销。在任何一种情况下，都必须将1000行放入重做日志中，以减少崩溃的可能性。