MySQL查看表

概述

今天主要介绍MySQL查看数据库表容量大小的几个方法，仅供参考。

1、查看所有数据库容量大小ｓｅｌｅｃｔtable_schema AS ‘数据库‘,sum( table_rows ) AS ‘记录数‘,sum( ｔｒｕｎｃａｔｅ ( data_length / 1024 / 1024, 2 ) ) AS ‘数据容量(MB)‘,sum( ｔｒｕｎｃａｔｅ ( index_length / 1024 / 1024, 2 ) ) AS ‘索引容量(MB)‘ FROMinformation_schema.TABLES GROUP BYtable_schema ORDER BYsum( data_length ) DESC,sum( index_length ) DESC;

2、查看所有数据库各表容量大小ｓｅｌｅｃｔtable_schema AS ‘数据库‘,table_name AS ‘表名‘,table_rows AS ‘记录数‘,ｔｒｕｎｃａｔｅ ( data_length / 1024 / 1024, 2 ) AS ‘数据容量(MB)‘,ｔｒｕｎｃａｔｅ ( index_length / 1024 / 1024, 2 ) AS ‘索引容量(MB)‘ FROMinformation_schema.TABLES ORDER BYdata_length DESC,index_length DESC;

3、查看指定数据库容量大小ｓｅｌｅｃｔtable_schema AS ‘数据库‘,sum( table_rows ) AS ‘记录数‘,sum( ｔｒｕｎｃａｔｅ ( data_length / 1024 / 1024, 2 ) ) AS ‘数据容量(MB)‘,sum( ｔｒｕｎｃａｔｅ ( index_length / 1024 / 1024, 2 ) ) AS ‘索引容量(MB)‘ FROMinformation_schema.TABLES WHEREtable_schema = ‘mysql‘;

4、查看指定数据库各表容量大小ｓｅｌｅｃｔtable_schema AS ‘数据库‘,table_name AS ‘表名‘,table_rows AS ‘记录数‘,ｔｒｕｎｃａｔｅ ( data_length / 1024 / 1024, 2 ) AS ‘数据容量(MB)‘,ｔｒｕｎｃａｔｅ ( index_length / 1024 / 1024, 2 ) AS ‘索引容量(MB)‘ FROMinformation_schema.TABLES WHEREtable_schema = ‘mysql‘ ORDER BYdata_length DESC,index_length DESC;

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下！