MySQL JSON列实际存储远超预期的原因及解决办法

核心原因 #

• JSON版本残留：MySQL的JSON列在更新时不会直接覆盖旧数据，而是保留旧版本快照以支持事务回滚、MVCC机制。如果该JSON字段被多次修改，这些未被清理的旧版本数据会不断累积，导致存储量大幅膨胀。
• 表空间碎片：InnoDB引擎下，可变长度的JSON字段更新后，原空间可能无法被即时复用，形成碎片。JSON_STORAGE_SIZE会统计这些未被释放的碎片空间，导致数值虚高。
• 冗余元数据累积：JSON列会存储辅助查询的元数据（如路径索引、结构映射），多次修改JSON结构后，这些元数据可能产生冗余，额外占用存储空间。

解决方法 #

• 整理表空间碎片：执行OPTIMIZE TABLE your_table_name;，该命令会重建表，清理旧版本JSON数据和碎片空间。注意：操作会锁表，需在业务低峰期进行。
• 精准更新JSON字段：避免全量替换JSON列，改用JSON_SET/JSON_REPLACE等函数仅修改需要更新的节点。例如：

  UPDATE your_table SET json_column = JSON_SET(json_column, '$.target_key', 'new_value') WHERE id = 1;
  

• 重置JSON数据：将JSON字段导出为文本后重新导入，或使用CAST(json_column AS CHAR)再转回JSON类型，清除冗余元数据。
• 配置独立表空间：确保innodb_file_per_table参数开启（默认开启），让每个表拥有独立表空间，提升碎片整理效率，避免跨表空间的影响。

内容的提问来源于stack exchange，提问作者Preetham K V