如何高效处理MariaDB中5700万行大表的过滤删除问题？

方案1：分批生成镜像表（规避全量内存占用） #

直接全量内连接5700万行数据会瞬间占满内存，改成按分片分批处理：

• 先复刻表A的结构创建镜像表：

  CREATE TABLE table_a_mirror LIKE table_a;
  

• 借助表A的主键（或自增ID）分片，每次处理10万行左右，循环执行直到完成：

  INSERT INTO table_a_mirror
  SELECT a.*
  FROM table_a a
  JOIN table_b b ON a.classification_id = b.classification_id
  WHERE a.id BETWEEN 1 AND 100000; -- 替换成你的表A主键范围，每次迭代调整数值
  

  要是表A没有自增主键，尽量避免用大偏移量的LIMIT，效率太低，优先找能分片的字段。

方案2：临时表+索引优化连接效率 #

先把表B的目标ID导入带索引的临时表，再和表A连接，大幅降低内存压力：

• 创建临时表并导入数据、加索引：

  CREATE TEMPORARY TABLE temp_b_ids (classification_id INT PRIMARY KEY); -- 字段类型要和表A对应列一致
  INSERT INTO temp_b_ids SELECT classification_id FROM table_b;
  

• 用临时表生成镜像表，索引会让连接速度快很多：

  CREATE TABLE table_a_mirror AS
  SELECT a.*
  FROM table_a a
  JOIN temp_b_ids b ON a.classification_id = b.classification_id;
  

  要是还是内存不够，就把这个方法和方案1的分批插入结合起来。

方案3：分批删除不匹配行（无需新建表） #

如果不想做镜像表，直接清理表A的无效数据，同样要分批避免锁表和内存溢出：

• 先给表A的classification_id加索引（没加的话必须加，不然匹配慢到离谱）：

  CREATE INDEX idx_a_classification ON table_a(classification_id);
  

• 每次删1万行左右，循环执行直到返回影响行数为0：

  DELETE FROM table_a
  WHERE classification_id NOT IN (SELECT classification_id FROM table_b)
  LIMIT 10000;
  

  注意：如果表A的classification_id有NULL值，NOT IN会把NULL也当成无效行，要是想保留NULL，要加AND classification_id IS NOT NULL。

方案4：分区表快速清理（适合长期场景） #

如果表A可以提前按classification_id或主键分区，之后直接删除不包含目标ID的分区，效率拉满，但需要提前规划分区结构，适合频繁做这类清理的场景。

核心优化提醒 #

• 表A的classification_id一定要加索引，表B的classification_id最好设为主键或加索引（500行影响不大，但有索引更稳）。
• 所有操作都要避免一次性全量执行，分批是解决内存不足的关键。
• 生成镜像表时，先用CREATE TABLE ... LIKE复制结构再插入，比直接CREATE TABLE ... AS更可控。

内容的提问来源于stack exchange，提问作者niedem