【mysql系】表碎片清理

当然如果生产环境环境表是做过分区的且是根据分区维度做删除的，可以使用交互分区到普通表，或者删除对应分区drop partition，也是会释放空间的

查看有碎片的表

SELECT
	table_schema db,
	table_name,
	data_free,
ENGINE 
FROM
	information_schema.TABLES 
WHERE
	table_schema NOT IN ( 'information_schema', 'mysql' ) 
	AND data_free > 0;

查询结果出来 data_free列的值就是对应的碎片大小

查看日常表空间大小大于50M的表的脚本

SELECT
	CONCAT( table_schema, '.', table_name ) AS 表名,
	ENGINE AS 表存储引擎,
	table_type AS 表类型,
	table_rows AS 行数,
	CONCAT( ROUND( data_length / ( 1024 * 1024 ), 2 ), 'M' ) AS 数据大小,
	CONCAT( ROUND( index_length / ( 1024 * 1024 ), 2 ), 'M' ) AS 索引大小,
CASE
		
		WHEN data_length = 0 THEN
		0 ELSE ROUND( index_length / data_length, 2 ) 
	END AS 索引占比,
	CONCAT( ROUND( data_free / 1024 / 1024, 2 ), 'MB' ) AS 空闲大小,
CASE
		
		WHEN ( data_length + index_length ) = 0 THEN
		0 ELSE ROUND( data_free /( data_length + index_length ), 2 ) 
	END AS 空闲占比 
FROM
	information_schema.TABLES 
WHERE
	ROUND( DATA_FREE / 1024 / 1024, 2 ) >= 50 
	and table_schema NOT IN ( 'information_schema', 'mysql' )  
ORDER BY
	data_free DESC;

对应表进行做碎片整理的sql脚本(推荐使用)

这里需要注意生产环境切记在业务高峰期操作执行该语句

alter table 表名  engine=innodb;