概述
今天主要介绍MySQL查看数据库表容量大小的几个方法,仅供参考。
1、查看所有数据库容量大小
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;
2、查看所有数据库各表容量大小
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;
3、查看指定数据库容量大小
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 = 'mysql';
4、查看指定数据库各表容量大小
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 = 'mysql' ORDER BYdata_length DESC,index_length DESC;
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下!