MySQL-统计信息&整理碎片

1.查找碎片

SELECT t.TABLE_SCHEMA,
       t.TABLE_NAME,
       t.TABLE_ROWS,
	   concat(round(t.DATA_LENGTH / 1024 / 1024, 2), 'M') AS size,
       t.INDEX_LENGTH,
       concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafree
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA = 'zabbix' order by DATA_LENGTH desc;

2.优化整理碎片

alter table t engine = InnoDB; /*重建表，整理表中碎片*/
analyze table t;  /*不是重建表，只是对表的索引信息做重新统计，没有修改数据，过程中加了MDL读锁*/
optimize table t; /*等于 recreate + analyze*/