我知道这个问题已经被问了很多次,但不幸的是现有的答案都没有帮到我,因为大部分都非常陈旧,所以需要重新回答一下。
我有两个表,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
ORDER BY id
无法在 WHERE 子句中的范围检查后使用索引。您尝试过不使用 ORDER BY 和 LIMIT 吗? - Paul Spiegel