MariaDB比较两个表并删除不在第一个表中的表(大型数据集)

Question

作为uni项目的一部分，我正在使用MariaDB通过算法清理一些大型的CSV，由于其大小，我使用的是MariaDB 10.5.9。

数据是5列与日期，时间，PlaceID，ID(不唯一和重复的)，位置它是一个大型数据集，每天大约50+百万条记录，总计超过1周3.86亿条记录。

我开始每天单独运行算法，效果很好，整个过程需要11到15分钟。

当我试着跑完这7天的总和时，我对性能有一些显着的影响。大多数元素都可以工作，但我有一个查询，它将ID中的值与已知良好id的列表进行比较，并删除任何不在已知良好中的值。

DELETE quick FROM merged WHERE ID NOT IN (SELECT ID FROM knownID) ;

在一个日常表上，这个查询大约需要2分钟(对比5000万个与1.25亿个已知良好的表，两个表都有索引来加快每个表的ID列的处理速度。

合并数据的表大小为24.5 is，已知的大小为4.7 is

当运行整个星期时，我预计查询花费的时间大约是查询时间的7倍(加一点)，不到2个小时？如何提高此性能？我在执行工作时将两个表都加载到内存表中，然后在完成后复制回基于磁盘的表，以尝试并加快过程，服务器有256 on的RAM，所以有足够的空间。是否有其他我可以更改/调整的设置？

my.ini如下所示：

innodb_buffer_pool_size=18G
max_heap_table_size=192G
key_buffer_size=18G
tmp_memory_table_size=64G

非常感谢

Answer 1

innodb_buffer_pool_size=18G -- too low; raise to 200G
max_heap_table_size=192G    -- dangerously high; set to 2G
key_buffer_size=18G         -- used only by MyISQM; set to 50M
tmp_memory_table_size=64G   -- dangerously high; set to 2G

这会删除多少行？

DELETE quick FROM merged
     WHERE ID NOT IN (SELECT ID FROM knownID) ;

更改为DELETE的“多表”语法并使用LEFT JOIN ... IS NULL

如果要删除的行超过1000行，请按块删除。请参阅http://mysql.rjweb.org/doc.php/deletebig

正如该链接中所讨论的，构建一个仅包含您想要保留的行的新表可能会更快。

DELETE必须保留旧行，直到语句结束；然后(在后台)执行实际的delete。这是一个很大的开销。

如需进一步讨论，请提供两个表的SHOW CREATE TABLE。