MySQL 磁盘快满了,如何低扰动清理大 JSON 字段?
从热榜历史快照中的大 JSON 字段出发,拆解服务端删除、主键游标、小事务、负载退避和断点续跑,并说明逻辑瘦身与物理空间回收的区别。
排查线上 MySQL 磁盘占用时,我遇到一类容易被忽略的增长来源。热榜快照一直在保存原始 JSON,其中的 ai_search 内容很大,而历史记录仍在持续累积。
清理目标很明确,保留历史快照,删除其中一个已经决定清理的字段。执行这件事,需要同时控制扫描、更新和恢复成本。本文整理的是这套脚本的处理方法,实际节省空间需要根据执行后的同口径统计确认。
一、先分清要减少的是哪一种空间
首先查看库和表的容量分布,再对候选字段做有限范围抽样。只看表的行数,很容易低估大 JSON 的影响。一条记录很大,即使更新二十条,也可能带来明显的日志与磁盘开销。
排查时应区分几类数值。
- JSON 内容及字段大小,用于估算数据组成。
- 表与索引已分配空间,用于了解存储布局。
- 文件系统可用空间,用于判断机器是否还能承受维护操作。
- binlog、undo 等维护期间的额外增长。
JSON_STORAGE_SIZE 可以辅助检查 JSON 存储量,不能把字段抽样大小直接等同于最终可释放的文件字节。函数计算及读取大值本身也有成本,抽样范围应当受控。MySQL JSON 工具函数
清理时,需要分别检查“历史字段是否瘦身”和“表文件空间是否归还给操作系统”。如果只看磁盘百分比,容易在字段已经删掉后,因为空间变化不明显而反复运行脚本。
二、在数据库当前值上删除字段
最初需要检查的问题,是脚本有没有把完整 ai_search 拉回程序,再修改 JSON 写回。这样的流程会额外传输大字段,还可能用旧 JSON 覆盖业务刚写入的新内容。
更新可以直接在服务端完成。下面展示单个已确认主键的参数化语句,主键由调用方传入。
UPDATE hot_topic_snapshots
SET raw_json = JSON_REMOVE(raw_json, '$.ai_search'),
updated_at = updated_at
WHERE id = %s
AND JSON_CONTAINS_PATH(raw_json, 'one', '$.ai_search') = 1;
JSON_REMOVE 表达的是删除路径。设置成空字符串或 JSON null 会保留字段,业务语义不同。路径不存在时,删除函数也不会因此报错。实现仍应针对预期 JSON 结构验证。MySQL JSON 修改函数
保留 updated_at 是本次维护的业务选择,用于避免把历史清理伪装成内容更新。这个选择要结合表的使用方式确认,不能直接套到所有表。
当前脚本读取主键、存在标记和大小信息,网络返回中没有完整 ai_search。它仍会在数据库内用 JSON_EXTRACT 计算字段大小,因此“减少网络传输”不能解释为“数据库完全不读取大字段”。UPDATE 也仍然需要锁和日志。
三、扫描预算和写事务预算分别控制
扫描采用主键游标,开始时固定 upper_id,后续沿着 last_id 向前推进。这样可以限定本轮范围,避免一边清理一边追赶线上新增数据。
脚本分别限制扫描行数、逻辑 JSON 字节数、单条大小和写事务行数。读到超大单条时停止,保留游标给人工评估。按行数拆批解决不了单条异常大的问题。
写入按小事务提交,批次和事务之间留出间隔。检测到数据库繁忙或读取过慢时,冷却并减小扫描批次。事务失败则回滚本批,已成功提交的前批无需重新处理。
这些参数是保护预算,实际取值需要结合业务延迟与数据库负载逐步调整。逻辑字节限制也无法承诺物理 I/O 永远低于某个数值。
扫描游标应记录已经检查过的行。没有目标字段的记录也要正常推进,否则可能反复扫描相同范围。记录被并发删除时,同样需要按已检查的主键继续。
四、提交和进度文件之间,留住可重复执行的边界
进度必须在事务提交后保存。先写游标再提交,一旦提交失败,就可能永久越过没有清理的记录。
反过来,提交成功后进程退出,游标还没保存,下次会重复检查这一小段。由于更新基于当前值删除固定路径,已清理记录可以自然跳过。
进度文件还需要包含实例身份、数据库、预览或执行模式,以及最初扫描上限。正式执行不能误用预览游标,也不能把另一套数据库的进度拿来继续。
本地文件使用临时文件加原子替换,减少写到一半的损坏窗口。这个机制保护本机进度文件,跨主机并行清理仍需要额外协调。
上线执行前应确认可恢复备份,观察磁盘、复制延迟、IOPS 和业务接口延迟。脚本里的活跃线程与行锁检测只覆盖其中一部分。条件恶化时,停止本轮,再从已提交进度继续。
五、验收字段清理,再评估物理回收
验收先检查目标范围中字段是否按计划消失,历史快照是否保留,以及并发业务写入有没有异常。随后再看表空间、日志和文件系统趋势。
InnoDB 删除数据以后,空闲空间可能留在表空间内部复用。需要回收表文件时,还要单独评估表空间类型、重建方式、临时空间和维护窗口。磁盘已经紧张时,贸然执行表重建可能增加压力。MySQL 表空间整理说明
这次脚本已经形成了服务端删除、限速、小事务与续跑的完整路径。它能帮助控制维护节奏,效果仍要通过执行窗口内的监控和清理后的数据验证。容量治理最终还需要回到写入端,确认未来快照是否继续保存同类冗余内容。
记录过程,也留下判断。
← 继续阅读其他文章