MariaDB 比较 2 个表并删除不在第一个表中的位置（大数据集）

关键词：

【中文标题】MariaDB 比较 2 个表并删除不在第一个表中的位置（大数据集）【英文标题】：MariaDB Compare 2 tables and delete where not in 1st (Large Dataset) 【发布时间】：2021-07-24 03:24:27 【问题描述】：

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

数据为 5 列，包含日期、时间、PlaceID、ID（不唯一且重复）、位置 它是一个大型数据集，每天大约有 50+ 百万条记录，在 1 周内总共有 3.86 亿条记录。

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

当尝试运行 7 天的总和时，我会对性能产生一些重大影响。 大多数元素都有效，但我有 1 个查询将 ID 中的值与已知良好 id 的列表进行比较，并删除任何不属于已知良好的。

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

在每日表上，此查询大约需要 2 分钟（比较 5000 万和 1.25 亿已知良好，两个表都有索引以加快每个表的 ID 列的处理速度。

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

在整个一周内运行时，我预计查询时间大约是 7 倍（加上一点），查询时间不到 2 小时？我怎样才能提高这种性能？我在执行工作时将两个表加载到内存表中，然后在完成后复制回基于磁盘的表以尝试加快进程，服务器有 256GB 内存，所以那里有足够的空间。我可以更改/调整任何其他设置吗？

my.ini 如下：

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

非常感谢

【问题讨论】：

【参考方案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

如果您要删除的行数超过一千行，请分块进行。见http://mysql.rjweb.org/doc.php/deletebig

正如该链接中所讨论的，可能只使用您想要保留的行来构建一个新表。

DELETE 必须保留旧行直到语句结束；然后（在后台）进行实际删除。这是很多开销。

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

【讨论】：

mysql查询加入，比较两个表并返回第一个表中的所有记录

】mysql查询加入，比较两个表并返回第一个表中的所有记录【英文标题】：mysqlquerytojoin,comparetwotablesandreturnallrecordsinfirsttable【发布时间】：2019-07-1821:10:37【问题描述】：我正在尝试比较两个不同表中的数据，方法是返回第一个表... 查看详情

MySQL JOIN 2 个表并分别获取两个表的总和

】MySQLJOIN2个表并分别获取两个表的总和【英文标题】：MySQLJOIN2tablesandgetsumforbothtablesseparately【发布时间】：2019-07-2202:52:03【问题描述】：我有2张桌子。我需要从两个表中获得收入-将具有相同日期和相同user_id的收入相加（输出... 查看详情

比较 2 个表并返回 MySQL 中的变化

】比较2个表并返回MySQL中的变化【英文标题】：Compare2tablesandreturnthevariationsinMySQL【发布时间】：2020-04-1606:10:55【问题描述】：大家早上好，希望有人可以帮助我解决以下问题。在MySQL中，我需要比较不同员工填写的2张相同的表... 查看详情

SQL查询删除多个表并从多个表中选择

】SQL查询删除多个表并从多个表中选择【英文标题】：SQLquerytodropmultipletablesandselectfrommultipletables【发布时间】：2014-03-0314:01:44【问题描述】：我在phpMyadmin中有两个SQL数据库，db1和db2。假设db1有3个表：1_a、1_b、1_c；db2有3个表：2_... 查看详情

Access - 比较两个表并在第一个表中更新或插入数据

】Access-比较两个表并在第一个表中更新或插入数据【英文标题】：Access-comparetwotablesandupdateorinsertdatainfirsttable【发布时间】：2015-05-2919:24:24【问题描述】：在我的Access数据库中，我有两个表：表1：PersNumNameSurname2321LenoraSpringer2320D... 查看详情

Laravel 连接 2 个表，第一个表中的一个数据和第二个表中的多行

】Laravel连接2个表，第一个表中的一个数据和第二个表中的多行【英文标题】：LaravelJoin2tables,onedatafromfirsttableandmultiplerowfromsecondtable【发布时间】：2018-05-1715:38:48【问题描述】：第一个表标识|姓名|1|学生1|2|学生2|3|学生3|4|学生4|=... 查看详情

如何从一个表中选择不在其他表中的所有行？

...布时间】：2021-01-0714:43:56【问题描述】：我在mysql数据库(mariaDB)名称products和order中有2个表。在产品表中有productid列，其类型为int(11)并设置为主键以及自动增量。虽然订单表有productid列的引用名称是不相同， 查看详情

仅删除与第二个表对应的第一个表中没有条目的那些

】仅删除与第二个表对应的第一个表中没有条目的那些【英文标题】：Deleteonlythose,whichhavenoEntryinfirsttablecorrespondingtothesecondtable【发布时间】：2019-02-2814:51:59【问题描述】：varproductSchema=Schema(product_code:String,name:type:String,required:true... 查看详情

SQL Server - 使用 PIVOT 查询比较 2 个表中的字段

】SQLServer-使用PIVOT查询比较2个表中的字段【英文标题】：SQLServer-CompareFieldsin2TableswithPIVOTquery【发布时间】：2020-09-1900:59:20【问题描述】：我在两个数据库（dbSource和dbTarget）中有同一个表，我正在尝试编写一个查询来将每个表中... 查看详情

在 MariaDB 中完全连接 2 个表

】在MariaDB中完全连接2个表【英文标题】：fulljoin2tablesinmariaDB【发布时间】：2020-02-2905:08:20【问题描述】：我有两张桌子Table1Year,Month,data2017,1,22018,2,10Table2Year,Month,data22017,1,52019,2,2我正在尝试将表合并为1个表，我们从两个表中获... 查看详情

返回第一个表中的所有数据，如果存在，则返回第二个表中的 1 个数据

】返回第一个表中的所有数据，如果存在，则返回第二个表中的1个数据【英文标题】：Returnalldatafrom1stTable,andonly1datafrom2ndTableifexist【发布时间】：2018-05-1412:10:30【问题描述】：我正在使用MySql。我有2个表，一个是名称和数据的... 查看详情

MySql JOIN 3 个表并获取具有相同值的子行

】MySqlJOIN3个表并获取具有相同值的子行【英文标题】：MySqlJOIN3tablesandgetchildrowswithsamevalues【发布时间】：2022-01-1822:21:53【问题描述】：我有3个表：订单、项目和渲染。1个订单可以有多个项目，每个项目都有一个带有状态的渲染... 查看详情

SQL删除不在另一个表中的行

...表的结构相同有些字段是可为空的列和行的数量巨大（要比较更多100k行和20-30列）每一行的每个字段都需要从表A与表B进行比较。这样的要求是由于必须每天运行一个流程，因为 查看详情

删除第二个表中不包含的记录

】删除第二个表中不包含的记录【英文标题】：Removerecordswhatisnotcontaininsecondtable【发布时间】：2013-06-1522:01:54【问题描述】：我要两张桌子：lang_temp（最新数据）：sku|postid|fi|en|ru（15000条记录）完整产品：sku|postid|fi|en|ru|xx|zzz|aaa... 查看详情

Oracle - 使用第二个表中的行更新第一个表中的数据

】Oracle-使用第二个表中的行更新第一个表中的数据【英文标题】：Oracle-updatedatainfirsttablewithrowsfromsecondtable【发布时间】：2020-09-0410:40:04【问题描述】：在20/07/20时如何使用\'modfied\'(t2)更新\'date_from\'(t1)。所以在这种情况下，在t1... 查看详情

比较 2 个表中的值并生成具有差异的新表

】比较2个表中的值并生成具有差异的新表【英文标题】：Comparevaluesfrom2tablesandproduceanewtablewiththedifferences【发布时间】：2017-09-0720:56:51【问题描述】：我正在尝试在SQLServer（脚本）中找到一种方法：给定2个表，具有相同的列结构... 查看详情

SQL查询2个表，其中值不在给定日期范围内

】SQL查询2个表，其中值不在给定日期范围内【英文标题】：SQLquerying2tableswherevaluenotinbothforagivendaterange【发布时间】：2018-02-0308:57:11【问题描述】：我在一个SQL数据库中有2个表。第一个表-Equipment-保存公司使用的机械记录清单。... 查看详情

Netezza 创建不在两个原始表中的记录表

...描述】：您好，这可能是一个简单的问题，但是：我有2个表，一个是2900万个条目，另一个是4000万个，有很大的重叠。我想创建第三个表，它只包含两个表中都没有出现的记录。通常要做到这一点，我会获取原始表的横向副本， 查看详情