网站首页 > 技术文章 正文
在处理 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 或显式提交事务,避免长事务。
猜你喜欢
- 2025-05-02 MySQL自增ID用完了怎么办?4种解决方案!
- 2025-05-02 MySQL批量插入性能对比:100、1000、10000行,谁更胜一筹?
- 2025-05-02 牛哇!MySQL中的日志“binlog”的三种格式这么好玩
- 2025-05-02 从B+树原理到实战:MySQL索引设计的22条军规
- 2025-05-02 mysql数据库基本增删改查操作总结
- 2025-05-02 MySQL索引效率太快,1亿数据查询不到1秒
- 2025-05-02 MySQL大数据表处理策略,原来一直都用错了……
- 2025-05-02 在MySQL命令行中获取用户账户列表的方法
- 2025-05-02 MySQL分库分表设计方案:大道至简,数据不乱
- 2025-05-02 从零到亿级数据:MySQL 分库分表实战避坑指南
- 05-11CSS:前端必会的flex布局,我把布局代码全部展示出来了
- 05-11Moti:React Native 动画库的新标杆
- 05-11前端开发避坑指南:每天都能用的 CSS3/Less/Sass 实战技巧
- 05-11HarmonyOS:ArkTS 多态样式自学指南
- 05-115 分钟快速上手图形验证码,防止接口被恶意刷量!
- 05-11网页五指棋游戏
- 05-11告别长文焦虑!AI帮你“秒划重点”,文章秒变知识卡片
- 05-11鸿蒙NEXT小游戏开发:数字华容道
- 最近发表
- 标签列表
-
- cmd/c (64)
- c++中::是什么意思 (83)
- 标签用于 (65)
- 主键只能有一个吗 (66)
- c#console.writeline不显示 (75)
- pythoncase语句 (81)
- es6includes (73)
- sqlset (64)
- windowsscripthost (67)
- apt-getinstall-y (86)
- node_modules怎么生成 (76)
- chromepost (65)
- localstorage.removeitem (74)
- vector线程安全吗 (70)
- & (66)
- java (73)
- org.redisson (64)
- js数组插入 (83)
- gormwherein (64)
- linux删除一个文件夹 (65)
- mac安装java (72)
- outofmemoryerror是什么意思 (64)
- eacces (67)
- 查看mysql是否启动 (70)
- 无效的列索引 (74)