一、查看所有数据库容量大小
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.tables WHERE 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
