MySQL数据表产生大量碎片原因

今日发现数据库空间占用每天增长20G，并且和实际空间占用差好多，500G左右的数据吃了900多G的空间。

产生碎片的原因有两个。

一个是运行delete命令删除数据，空间没回收，产生碎片。

另一个在我遇到的情况是json列，可能不定长 TEXT 类的列也有，大量行这些不定长列数据增加数据量，可能会导致数据占用空间再申请，原空间没释放导致碎片。

下面一段代码能看到数据库内物理文件的大小。

SELECT file_name,concat(TOTAL_EXTENTS,'MB') as 'FileSize' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS;  

下面一段代码能看到数据库内表数据大小。

select 
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'from information_schema.tables
where table_schema='这里填写你的数据库名称'order by data_length desc, index_length desc;

对比文件大小和数据大小可以看到哪些表有碎片，执行碎片整理命令。

OPTIMIZE TABLE `待整理数据表名`

然后排查不定长列长度大量变化的原因，优化代码。