Mysql 查看表及索引空间大小

查看数据库占用空间

SELECT CONCAT(ROUND((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024),2),'M') as 'Datebase Size' FROM information_schema.TABLES where TABLE_SCHEMA='数据库名称';

查看表占用空间

SELECT CONCAT(ROUND(SUM(DATA_LENGTH)/(1024*1024), 2), ' MB') AS 'Total Table Size' FROM information_schema.TABLES WHERE table_schema LIKE '数据库名称';

查看索引占用空间

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE '数据库名称';

查看库中表详细信息

结果包括：表名、记录数、此表占用空间、此表索引占用空间、此表总占用空间

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',

table_rows AS 'Number of Rows',

CONCAT(ROUND(data_length/(1024*1024),3),'M') AS 'Data Size',

CONCAT(ROUND(index_length/(1024*1024),3),'M') AS 'Index Size' ,

CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'M') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE '数据库名称';