根据不同的表“过滤”巨大的 MariaDB/Mysql 表

Question

在我的 mariaDB 数据库中处理大型数据集。我有两个表，其中表 A 包含 5700 万行，表 B 包含大约 500 行。表 B 是与表 A 中的列相关的 ids 的子集。我想删除 A 中没有相应 ID 的所有行表B.

示例表 A：

classification_id	姓名
20	梅赛德斯
30	川崎
80	徕兹
70	惠普

示例表B：

classification_id	类型
20	汽车
30	自行车
40	公交车
50	船

因此，在此示例中，表 A 中的最后两行将被删除（或者将创建一个仅包含前两行的镜像表，这也可以）。

我尝试使用内部联接执行第二个查询，但此查询花了几分钟才给出内存不足异常。

关于如何解决这个问题有什么建议吗？

Answer 1

由于您说过滤表包含相对较少的行数，因此最好的选择是创建一个单独的表，其中包含与原始表 A 相同的列以及与您的匹配的行criteria，然后替换原始表并删除它。另外，对于如此数量的 ID，您可能希望使用 WHERE IN () 而不是联接 - 只要您在那里使用的字段已建立索引，它通常会方式 更快。将所有内容放在一起：

CREATE TABLE new_A AS
  SELECT A.* FROM A 
  WHERE classification_id IN (SELECT classification_id FROM B);
RENAME TABLE A TO old_A, new_A to A;
DROP TABLE old_A;

需要注意的事项：

• 备份您的数据！并在运行 DROP TABLE 之前彻底测试查询。您不想因为 StackOverflow 上的随机答案而丢失 57M 行数据。
• 如果 A 有任何索引或外键，这些将不会被复制 - 因此您必须手动重新创建它们。我建议首先运行 SHOW CREATE TABLE A 并记下其结构。或者，您可以考虑使用 SHOW CREATE TABLE A 的输出作为模板显式创建表 new_A，然后执行 INSERT INTO new_A SELECT ... 而不是 CREATE TABLE new_A AS SELECT ... 并在此之后使用相同的查询。

Answer 2

试试这个：

delete from "table A" where classification_id not in (select classification_id from "table B");