知识篇 | MySQL如何整理碎片？

1. 查看数据库中表、索引和碎片大小的大小：

select round(sum(data_length/1024/1024),2) as data_length_MB,
round(sum(index_length/1024/1024),2) as index_length_MB  ,
round(sum(data_free/1024/1024),2) as data_free_MB  ,table_name
from information_schema.tables where TABLE_SCHEMA= ‘schema_name’ group by table_name order by 3 desc;

2. 根据查询的结果进行整理。

查看表的碎片情况：DATA_FREE
show TABLE status like ‘table_name’;

或：
select * from information_schema.tables where table_name= ‘table_name’;

3. 生成批量脚本：

select CONCAT('alter table ‘,table_name , ’ ENGINE=INNODB;’) from information_schema.tables where TABLE_SCHEMA = ‘freeticket_ft’ and table_name like ‘schema_name%’;

4. 整理data_free大于100M的表信息：
   select round(sum(data_length/1024/1024),2) as data_length_MB,
   round(sum(index_length/1024/1024),2) as index_length_MB  ,
   round(sum(data_free/1024/1024),2) as data_free_MB,
   CONCAT('alter table ‘,table_name , ’ ENGINE=INNODB;’) dd
   from information_schema.tables
   where table_schema= ‘schema_name’
   group by dd
   having data_free_MB>100 order by 3 desc;

5. 进行碎片整理：

根据步骤3和4，执行碎片整理
alter table t_app_user ENGINE=INNODB;

6. 碎片整理前后空间释放对比
   整理前：
   mysql> show TABLE status like ‘table_name’ \G;

Data_free: 201046622208 --200G碎片左右

整理后：

mysql> show TABLE status like ‘table_name’ \G;

Data_free: 4194304 --4M整理后