从大型MySQL innoDB表中删除记录。

Question

从大型MySQL innoDB表中删除记录。

4

我知道这个问题已经被问了很多次，但不幸的是现有的答案都没有帮到我，因为大部分都非常陈旧，所以需要重新回答一下。

我有两个表，records表有+140kk行/+24GB，extra表有+89kk行/+70GB。

每个extra行都与records表有外键关系，两个表之间的索引已正确设置。从records表中删除记录会级联删除相关的extra行。

我需要在生产过程中清除旧的记录。运行DELETE FROM records WHERE WHERE created < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) ORDER BY id LIMIT 1000;需要无限的时间（我在20分钟后将其终止，它仍处于init状态）。使用SELECT执行相同的操作只需要几毫秒。

由于SELECT操作如此快速，我尝试了一个id IN (SELECT id subquery^)子查询，但不幸的是什么也没改变，所以我在15分钟后将其终止。

由于单个记录的删除很快，我最终采用了这个解决方法：

for i in `seq 1 100000`; do
    mysql database -e "SELECT id FROM records WHERE created < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) ORDER BY id LIMIT 1000;" | sed 's;/|;;g' | awk '{if(NR>1)print "DELETE FROM records WHERE id = ",$1,";" }' | mysql database;
    now=$(date +"%Y/%m/%d %T")
    echo "[ $now ] $i.000"
done

这个程序开始时能够每秒处理一千条记录，但几次循环后，速度下降到每10-20秒才能处理一千条。由于我需要清除10万行数据，这将需要近一个月的时间来完成，而这个时间正好与需要清除的记录时间相同，因此它永远无法完成（特别是因为有多个需要进行此操作的数据库）。

数据库存储在SSD Crucial_CT500MX200SSD1上，软件是MariaDB 10.1，使用InnoDB引擎。innodb_flush_log_at_trx_commit设置为0以避免不必要的磁盘利用。

根据atop的显示，瓶颈在于磁盘，CPU几乎处于休眠状态，大部分内存都在系统缓存中。

结构转储：https://gist.github.com/Slind14/0da34e09dba91cf411db2ead5ad666ef

- user2693017

第一个查询中需要使用“ORDER BY id”吗？不使用它试试看。 - Mihai

这是为了避免复制问题所必需的。只要是ASC（默认顺序），就没有关系。 - user2693017

据我所知，ORDER BY id 无法在 WHERE 子句中的范围检查后使用索引。您尝试过不使用 ORDER BY 和 LIMIT 吗？ - Paul Spiegel

选择操作在毫秒级别内返回，因此限制和排序不是问题。由于这是生产数据库，我们无法锁定表格一整天，所以需要使用“LIMIT”来分块处理。为了使查询确定性，必须使用“ORDER BY”，否则会破坏复制。 - user2693017

这些ID的顺序与“created”相同吗？ - Paul Spiegel

我可以得到更多信息吗？请发送以下查询的输出结果：SELECT * FROM prism_extra LIMIT 1000 PROCEDURE ANALYSE(); 和 **SELECT * FROM prism_records LIMIT 1000 PROCEDURE ANALYSE();**（可能需要几分钟时间）。同时，请运行您的删除查询，如果它需要很长时间，请运行 **SHOW ENGINE INNODB STATUS;**。 - Bernd Buffen

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rick James · Accepted Answer

请参见http://mysql.rjweb.org/doc.php/deletebig，它讨论了从大表中高效删除大量行的各种方法。其中最有效的方法可能是基于主键（如果有）遍历整个表，删除符合条件的那些行。这种方法非常高效，因为它每次只查看100行左右，处理它们，然后再继续下一个100行。相比之下，使用“LIMIT”和无界“WHERE”子句可能需要多次扫描相同的行。您提到了“JOIN”，根据它的成本，100可能是一个不错的限制。如果“JOIN”不太费时，那么1000也可能很好。如果这将是一个经常性的任务，请考虑使用“PARTITIONing”，使得可以使用“DROP PARTITION”进行即时操作。