mysql 千万级表数据删除及优化（mysql千万级数据存储方案）

在处理 MySQL 超大表（例如千万级或亿级数据）的数据删除时，直接使用 DELETE 语句可能会

导致严重的性能问题，例如锁表时间长、事务日志暴增、主从延迟甚至服务不可用。以下是针对

超大表数据删除的优化方案和注意事项：

1. 优先考虑分区表（Partitioning）

如果表已按时间或业务逻辑分区（如按月份分区），删除数据时可以直接 删除整个分区，效率极

高（仅需元数据操作）。

示例：

-- 删除指定分区
ALTER TABLE big_table DROP PARTITION p202201;

适用场景：

• 数据按时间或固定规则分布。
• 需要定期清理历史数据。

优势：

• 几乎瞬时完成，无锁表风险。
• 避免大量 I/O 操作。

2. 分批删除（Batch Delete）

直接删除全部数据会导致事务过大，建议 分批次删除，每次删除少量数据并释放事务资源。

-- 分批删除（每次删除 1000 条）
WHILE TRUE DO
		DELETE FROM big_table WHERE create_time < '2022-01-01' LIMIT 1000;
		COMMIT; -- 提交事务释放锁
		DO SLEEP(1); -- 暂停1秒减少负载
END WHILE;

优化技巧：

添加索引： 确保 WHERE 条件字段有索引（如 create_time ），否则全表扫描会极慢。

调整批次大小： 根据服务器性能调整 LIMIT 值（如 1000~10000）。

低峰期执行： 在业务低峰期运行删除任务。

3. 重建表（Recreate Table）

如果需要保留少量数据，可 创建新表 + 插入保留数据 + 重命名表。此方法通常比直接删除更

快。

-- 1. 创建新表（结构与原表相同）
CREATE TABLE new_big_table LIKE big_table;
-- 2. 插入需要保留的数据
INSERT INTO new_big_table
SELECT * FROM big_table WHERE create_time >= '2022-01-01';
-- 3. 重命名表（原子操作）
RENAME TABLE big_table TO old_big_table, new_big_table TO big_table;
-- 4. 删除旧表
DROP TABLE old_big_table;

优势：

• 避免逐行删除的 I/O 开销。
• 锁表时间极短（仅重命名瞬间）。

注意事项：

• 需要足够的磁盘空间存储新旧两份数据。
• 确保插入过程中无数据写入（建议在维护窗口操作）。

4. 使用 pt-archiver 工具

Percona Toolkit 中的 pt-archiver 是专门用于安全归档/删除大数据的工具，支持 分批处理、限

流、避免锁表。

pt-archiver \
--source h=localhost,D=test,t=big_table \
--purge \
--where "create_time < '2022-01-01'" \
--progress 1000 \
--limit 1000 \
--sleep 1

参数说明：

• --purge : 直接删除数据（不归档）。
• --limit 1000 : 每批删除 1000 行。
• --sleep 1 : 每批后休眠 1 秒。

优势：

• 避免长时间锁表（使用低锁级别）。
• 支持限流，减少对业务影响。

5. 延迟删除（Low Priority Delete）

如果允许短暂延迟，可以结合 异步任务或事件调度器 逐步删除数据

-- 启用事件调度器
SET GLOBAL event_scheduler = ON;
-- 创建每日删除任务
CREATE EVENT daily_purge
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
DELETE FROM big_table WHERE create_time < '2022-01-01' LIMIT 100000;
END;

6. 预防性优化

• 分区表设计： 在建表时提前规划分区，方便后续清理。
• 定期归档： 使用定时任务将历史数据迁移到归档表或数据仓库
• 调整 InnoDB 参数：

innodb_buffer_pool_size = 80%物理内存 # 提升缓存命中率
innodb_io_capacity = 2000 # 提高 I/O 吞吐量

注意事项

1. 备份优先： 删除前务必备份数据（如 mysqldump 或物理备份）。

2. 主从延迟： 大批量删除可能导致主从延迟，建议分批操作。

3. 监控资源： 关注 CPU、I/O、内存和锁状态（如 SHOW PROCESSLIST ）。

4. 事务隔离： 使用 AUTOCOMMIT=1 或显式提交事务，避免长事务。