mysql查询索引大小和数据库大小

mysql查询索引大小和数据库大小
记录一下,方便以后自己查询

#所有库索引 所有库大小
SELECT CONCAT( ROUND(SUM(INDEX_LENGTH)/1024/1024 ,2),'MB' ) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MYISAM';
SELECT CONCAT( ROUND(SUM(DATA_LENGTH)/1024/1024 ,2),'MB' ) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MYISAM';

#单库索引 单库大小
SELECT CONCAT( ROUND(SUM(INDEX_LENGTH)/1024/1024 ,2),'MB' ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'databasename' AND ENGINE='MYISAM';
SELECT CONCAT( ROUND(SUM(DATA_LENGTH)/1024/1024 ,2),'MB' ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'databasename' AND  ENGINE='MYISAM';

#单表索引 单表大小
SELECT CONCAT( ROUND(SUM(INDEX_LENGTH)/1024/1024 ,2),'MB' ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME  = 'tablename' AND ENGINE='MYISAM';
SELECT CONCAT( ROUND(SUM(DATA_LENGTH)/1024/1024 ,2),'MB' ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME  = 'tablename' AND  ENGINE='MYISAM';



发表评论