如何高效从约2000万行MySQL数据库中批量删除文本文件指定ID的行？

针对数十万条主键ID、2000万行数据的场景，最优实现方式优先考虑临时表关联删除，其次是分批次批量删除，以下是具体操作步骤和注意事项：

一、最优方案：临时表+关联删除 #

该方法利用MySQL的批量处理能力和索引优化，是效率最高的方式，适合大规模删除场景。

1. 创建临时表
   临时表仅存储待删除的主键ID，且为主键建立索引（确保关联查询效率），注意ID类型需与目标表主键类型完全一致（如INT/BIGINT/VARCHAR）：

   CREATE TEMPORARY TABLE temp_delete_ids (id INT PRIMARY KEY);
   

2. 导入文本文件到临时表
   使用LOAD DATA命令快速导入文本文件（比逐条INSERT效率高几个数量级），替换/path/to/your/id_file.txt为你的文件路径：

   LOAD DATA LOCAL INFILE '/path/to/your/id_file.txt' INTO TABLE temp_delete_ids;
   

   若文本文件有特殊格式（如分隔符、头部行），可添加参数调整，例如跳过第一行：

   LOAD DATA LOCAL INFILE '/path/to/your/id_file.txt' INTO TABLE temp_delete_ids IGNORE 1 LINES;
   

3. 执行关联删除
   通过JOIN关联目标表与临时表，一次性删除匹配行，相比WHERE id IN (...)避免了IN列表过长的性能问题：

   DELETE t FROM target_table t JOIN temp_delete_ids d ON t.id = d.id;
   

   若你的MySQL版本较低（如5.5及以前），需使用完整表名写法：

   DELETE target_table.* FROM target_table JOIN temp_delete_ids ON target_table.id = temp_delete_ids.id;
   

4. 验证与清理
   执行查询确认删除数量：

   SELECT COUNT(*) FROM temp_delete_ids; -- 待删除ID总数
   SELECT ROW_COUNT(); -- 实际删除行数
   

   临时表会在会话结束后自动销毁，无需手动删除。

二、备选方案：分批次批量删除 #

若无法使用LOAD DATA（如权限限制），可将文本文件中的ID拆分多个小批次（建议每批次1000-5000条），循环执行删除：

1. 拆分文本文件
   使用脚本（如Shell/Python）将数十万条ID拆分为多个小文件，每个文件包含1000个ID，例如：

   # Shell示例：拆分id_file.txt为每个文件1000行，前缀为batch_
   split -l 1000 id_file.txt batch_
   

2. 循环执行删除
   读取每个批次文件，拼接成IN子句执行删除，示例SQL：

   DELETE FROM target_table WHERE id IN (1001,1002,...,2000);
   

   建议在程序中实现循环读取+执行，避免手动拼接过长的IN列表（MySQL对IN列表长度有max_allowed_packet限制）。

三、关键注意事项 #

• 生产环境操作时机：尽量在业务低峰期执行删除，避免长时间锁表影响正常业务。
• 事务与提交：关闭自动提交（SET autocommit=0;），每批次删除后手动提交（COMMIT;），减少事务日志写入压力。
• 空间回收：InnoDB引擎若使用独立表空间，删除后空间会自动标记为可复用；若需立即释放空间，可执行ALTER TABLE target_table ENGINE=InnoDB;（锁表，需低峰期操作）；MyISAM引擎需执行OPTIMIZE TABLE target_table;。
• 数据备份：操作前务必备份目标表数据，避免误删：

  CREATE TABLE target_table_backup LIKE target_table;
  INSERT INTO target_table_backup SELECT * FROM target_table;
  

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