我有 7 个相关表，其中一个表中有一个时间戳列，我想删除所有超过 30 天的行。然而，这些是非常大的删除。我说的是数千万条记录。如果我从主表中删除所有这些记录，我必须查看其他 6 个表并从这些表中删除相关记录。

我的问题是优化它的最佳方法是什么？

我正在考虑使用 PARTITION 但只有一个表具有时间戳列。我担心如果删除主表中的旧分区，相关记录仍然存在于其他6个表中。相关记录由字段(sid，cid)关联。

对于上下文，我使用的是 IDS 处理器 snort 和 barnyard。

我正在使用 MySQL 5.1.73，MyISAM 表

这是清理日志中的一个片段:

StartTime,EndTime,TimeElapsed,AffectedRows
Wed Jan 6 01:00:01 EST 2016,Wed Jan 6 01:45:11 EST 2016,45:10,2911807
Thu Jan 7 01:00:02 EST 2016,Thu Jan 7 01:25:29 EST 2016,25:27,2230255
Fri Jan 8 01:00:01 EST 2016,Fri Jan 8 01:24:18 EST 2016,24:17,1400470
Sat Jan 9 01:00:02 EST 2016,Sat Jan 9 05:47:10 EST 2016,287:8,23360088
Sun Jan 10 01:00:01 EST 2016,Sun Jan 10 10:06:16 EST 2016,546:15,44970072
Mon Jan 11 01:00:01 EST 2016,Mon Jan 11 09:40:39 EST 2016,520:38,43948091

这是我的旧清理脚本:

/usr/bin/mysql --defaults-extra-file=/old/.my.cnf snort_db >> /root/snortcleaner.log 2>&1 <<EOF
use snort_db;

DROP TRIGGER IF EXISTS delete_old;

DELIMITER //
CREATE TRIGGER delete_old AFTER DELETE ON event
FOR EACH ROW
BEGIN
DELETE FROM data WHERE data.cid = old.cid AND data.sid = old.sid;
DELETE FROM iphdr WHERE iphdr.cid = old.cid AND iphdr.sid = old.sid;
DELETE FROM icmphdr WHERE icmphdr.cid = old.cid AND icmphdr.sid = old.sid;
DELETE FROM tcphdr WHERE tcphdr.cid = old.cid AND tcphdr.sid = old.sid;
DELETE FROM udphdr WHERE udphdr.cid = old.cid AND udphdr.sid = old.sid;
DELETE FROM opt WHERE opt.cid = old.cid AND opt.sid = old.sid;
END //
DELIMITER ;

EOF

# Send the main MySQL command: Deletes all records betweeen the oldest     timestamp and 31 days from now()
# Gets the oldest timestamp and ranges a deletion from that to 31 days before now(). If the oldest timestamp is more recent than 31 days, the following command returns 0 anyway. If it is older than 31 days, it will return them
OLDEST_TIMESTAMP=$(mysql --defaults-extra-file=/old/.my.cnf -Dsnort_db -se "SELECT timestamp FROM event ORDER BY timestamp ASC LIMIT 1;")
NUM_AFFECTED=$(mysql --defaults-extra-file=/old/.my.cnf -Dsnort_db -se "DELETE FROM event WHERE timestamp BETWEEN DATE_SUB('${OLDEST_TIMESTAMP}', INTERVAL 1 HOUR) AND DATE_SUB(NOW(), INTERVAL 31 DAY); SELECT ROW_COUNT();")

这是我当前的清理脚本:

DELETE FROM event WHERE timestamp BETWEEN DATE_SUB('${OLDEST_TIMESTAMP}', INTERVAL 1 HOUR) AND DATE_SUB(NOW(), INTERVAL 31 DAY);

DELETE FROM data USING data LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM iphdr USING iphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM icmphdr USING icmphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM tcphdr USING tcphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM udphdr USING udphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM opt USING opt LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;

我在两者之间来回切换，因为我不知道哪个更快，但事实是两者都太慢了。

最佳答案

尝试将外键设置为级联删除，这样您就无需创建触发器并手动加入和删除相关记录。

下面的例子展示了如何创建级联删除的关系

CREATE TABLE parent ( id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;

示例来自 Mysql website

关于MySQL - 多个表上的大删除，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34727114/