查看表的碎片情况
select concat(table_schema,'.',table_name),data_free,engine from information_schema.tables where data_free>0 and engine !='MEMORY';
查看指定表的碎片情况
show table status like 'table_name'
Data_free: 代表碎片的byte数,如果该字段不为0,则说明产生了碎片。
碎片化查询排序
select table_schema, table_name, concat(data_free/1024/1024, 'M') from information_schema.tables where engine = 'innodb' order by data_free desc
4、碎片清理
alter table tb_test engine=innodb; (本质上是 recreate 重新整理表)
alter table tb_test engine=innodb,ALGORITHM=inplace;
alter table tb_test engine=innodb,ALGORITHM=copy;
optimize table tb_test; (本质上是 recreate 重新整理会锁表,很重量级和耗时的过程)
alter table tb_test force (InnoDB表中等价于 alter table tb_test engine=innodb )
mysqlcheck 批量表空间优化
gh-ost/pt-osc
pt-online-schema-change (本质上也是先备份旧表数据,然后 truncate 旧表)