查看数据库占用空间
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 '数据库名称';