问题
解决方案
- 分区表：xxx
- 分批次删：xxx
分区表删除
有一个滑动的分区窗口。假设需要清除30天之前的数据。“分区键”将是用于清除的日期时间（或时间戳），分区将是BY RANGE。每天晚上，都会出现一个cron作业，决定是否为第二天构建一个新分区，并删除最旧的分区。
删除一个分区本质上是即时的，比删除那么多行要快得多。但是，您必须设计表，以便可以删除整个分区。也就是说，不能让分区中的某些记录比其他记录存活更久。
分区表有很多限制，有些相当奇怪。表上可以没有UNIQUE（或PRIMARY）键，也可以每个UNIQUE键都必须包含分区键。在这个用例中，分区键是datetime。它不应该是PRIMARY KEY的第一部分（如果您有PRIMARY KEY）。
可以对InnoDB表进行分区。（在8.0版本之前，您还可以对MyISAM表进行分区。）
分批次删除
虽然本节讨论的是DELETE，但它可以用于任何其他“分批次”，例如UPDATE或SELECT加上一些复杂的处理。
（这个讨论适用于MyISAM和InnoDB。）
在chunks中删除时，请确保避免进行表扫描。还要确保避免使用OFFSET和LIMIT。下面的代码很擅长这个；它在任何一次查询中扫描不超过1001行。(1000是可调的。如果您的innodb_buffer_pool_size非常小，则应该向下调优。)
假设您有需要清除的数据，并且您有一个类似的模式
CREATE TABLE tbl
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ts TIMESTAMP,
...
PRIMARY KEY(id)
然后，这个伪代码是删除超过30天的行的好方法!
@a = 0
LOOP
DELETE FROM tbl
WHERE id BETWEEN @a AND @a+999
AND ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
SET @a = @a + 1000
sleep 1 -- be a nice guy
UNTIL end of table
- 间隔设置：1 秒的间隔可以缓解数据库压力，但会延长清理的总时间，可根据具体业务场景调整间隔时间。
- 索引：在ts时间字段上一定要有索引。
- 分批大小：分批大小1000是一个经验值。
- @a是一个变量，一直自增。
如果表上没有主键，并且在时间字段上有一个索引，可以考虑时间下面的方式
LOOP
DELETE FROM tbl
WHERE ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY ts -- to use the index, and to make it deterministic
LIMIT 1000
UNTIL no rows deleted
不建议使用这种技术，因为LIMIT会导致在replication（从节点）上警告它是不确定的（下面将讨论）。
limit在从节点引发数据不一致的可能
任何带有LIMIT的UPDATE， DELETE等被复制到Replicas（通过基于语句的复制）可能会导致Master和Replicas之间的不一致。这是因为在Replica上发现用于更新/删除的记录的实际顺序可能不同，从而导致修改不同的子集。为了安全起见，在这些语句中添加ORDER BY。此外，要确保ORDER BY是确定性的——也就是说，ORDER BY中的字段/表达式是唯一的。
InnoDB分批次删除的建议
- 为innodb_log_file_size设置一个“合理”的大小。
- 设置字段提交事务？
- 选择1000行，作为每批次的大小，是一个经验值。
- 向下调整每批次行数，如果异步复制导致从节点有太多的延迟。
如果删除超过表中一半的数据
- 如果删除的数据超过表数据的百分之50，建议拷贝所需数据到临时表，然后删除原表，再重命名临时表为原表
INSERT INTO New
SELECT * FROM Main
WHERE ...; -- just the rows you want to keep
RENAME TABLE main TO Old, New TO Main;
DROP TABLE Old; -- Space freed up here
- 您确实需要为两个副本提供足够的磁盘空间。
- 在这个过程中，不能直接把数据写入表。（对Main的更改可能不会反映在New中。）
- 外键和触发器可能会引发一些问题。
