一、查看所有数据库容量大小

SELECTtable_schema AS '数据库',sum( table_rows ) AS '记录数',sum(TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',sum(TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' FROMinformation_schema.TABLES GROUP BYtable_schema ORDER BYsum( data_length ) DESC,sum( index_length ) DESC;

二、查看所有数据库各表容量大小

SELECTtable_schema AS '数据库',table_name AS '表名',table_rows AS '记录数',TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' FROMinformation_schema.TABLES ORDER BYdata_length DESC,index_length DESC;

三、查看指定数据库容量大小

SELECTtable_schema AS '数据库',sum( table_rows ) AS '记录数',sum(TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',sum(TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' FROMinformation_schema.TABLES WHEREtable_schema = 'osale_im';

四、查看指定数据库各表容量大小

SELECTtable_schema AS '数据库',table_name AS '表名',table_rows AS '记录数',TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' FROMinformation_schema.TABLES WHEREtable_schema = 'osale_im' ORDER BYdata_length DESC,index_length DESC;

五：查看指定数据库指定表容量大小

六.查看所有产生碎片的表

SELECT table_schema db,table_name,data_free,engine FROM information_schema.tablesWHERE table_schema NOT IN ('information_schema', 'mysql')AND data_free > 0 ORDER BY DATA_FREE DESC;

查看某个表的碎片大小

SHOW TABLE STATUS LIKE ‘表名’;

查询结果中的’Data_free’字段的值就是碎片大小。

7. 清理表碎片

/*1. MyISAM表*/ OPTIMIZE TABLE 表名 /*2. InnoDB表*/ ALTER TABLE 表名 engine = InnoDB