MySql查看表空间占用并优化碎片

SELECT
	table_name AS '表名',
	table_schema AS '数据库',
	SUM(table_rows) AS '记录数',
	SUM(TRUNCATE(data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
	SUM(TRUNCATE(index_length / 1024 / 1024, 2 )) AS '索引容量(MB)',
	SUM(TRUNCATE(DATA_FREE / 1024 / 1024, 2 )) AS '碎片占用(MB)' 
FROM information_schema.TABLES 
GROUP BY table_schema, table_name
ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;


OPTIMIZE TABLE tk_new_user_card;
或
ALTER TABLE tk_new_user_card ENGINE='InnoDB';