mysql与postgresql删除大表的方法

注意：mysql表命名主键自增不受影响
        postgresql的索引名称会变化，如果在代码里映射，需要在表命名之前把之前的索引记下来，后续做索引复原处理，这个代码就不用变更
   ALTER INDEX old_table_name_idx RENAME TO new_table_name_idx;

 

MySQL在InnoDB引擎下主要使用行级锁，但处理不当（如事务太大、WHERE条件无索引）仍可能锁表。

1. 循环分批删除（最常用）
在循环中每次只删除一小批数据，通过LIMIT控制批次大小，并在每批操作后提交事务。建议配合ORDER BY id利用主键索引提升效率。

sql

-- 伪代码示例：在存储过程或脚本中循环执行
DELETE FROM your_table
WHERE your_condition
ORDER BY id
LIMIT 1000; -- 每批删除1000行

• 优点：精确控制，风险低。

• 缺点：速度相对较慢，需编写脚本或存储过程。

2. 表重建法（适合删除大部分数据）
当需要删除表中大部分（如超过50%） 数据时，与其逐行删除，不如“移花接木”。

1. 创建新表：CREATE TABLE new_table LIKE your_table;

2. 插入保留数据：INSERT INTO new_table SELECT * FROM your_table WHERE keep_condition;

3. 重命名切换：RENAME TABLE your_table TO old_table, new_table TO your_table;

RENAME操作仅需极短暂的元数据锁，对业务影响极小。此方法需要额外的磁盘空间。

3. 利用分区表（最优解）
如果表是分区表且删除条件与分区键匹配，直接删除整个分区（DROP PARTITION） 是最佳方案。该操作几乎瞬间完成，且不产生大量日志。

sql

ALTER TABLE your_partitioned_table DROP PARTITION p_202301;

此方案需要提前规划好分区策略。

4. 借助专业工具
Percona Toolkit中的pt-archiver是专门为这类场景设计的，能安全、高效地归档或删除数据。

bash

pt-archiver --source h=localhost,D=your_db,t=your_table \
            --where "your_condition" \
            --purge \
            --limit 1000 \
            --commit-each

它能自动处理批处理和提交，减少人工脚本的复杂性。

5. 清空全表：TRUNCATE TABLE
如果需要删除全表所有数据，应直接使用TRUNCATE TABLE。它是DDL操作，通过释放数据页来清空，速度极快且锁表时间极短。注意此操作无法回滚。

---

🐘 PostgreSQL：策略与MySQL相通

PostgreSQL的MVCC机制下，DELETE不会立即回收空间，而是产生死元组，需要通过VACUUM清理。因此，删除大数据的策略重点略有不同。

1. 循环分批删除（同样适用）
与MySQL类似，通过循环执行带LIMIT的DELETE语句是控制锁影响最直接的方法。

sql

-- 在循环中执行
DELETE FROM your_table
WHERE your_condition
LIMIT 1000;

此方法可有效避免长事务和锁竞争。

2. 表重建法（更推荐）
对于删除大量数据的场景，PostgreSQL社区更推荐此方法。

• 优点：能有效整理表空间，避免大量死元组产生。

• 缺点：需要额外存储空间，且处理外键等依赖关系较麻烦。

3. TRUNCATE TABLE（清空全表）
同样，如需清空全表，TRUNCATE TABLE是首选。它会获取ACCESS EXCLUSIVE级别的锁，会阻塞所有并发操作，但操作本身非常快速。

4. 临时关闭索引与约束（谨慎使用）
删除操作会同步维护索引，增加开销。如果删除数据量巨大，可考虑先删除索引和外键，完成删除和VACUUM后再重建。此操作有风险，需在维护窗口进行。

5. 利用第三方扩展
pg_squeeze等扩展可以在线清理表空间，减少对业务的锁影响