查看数据库表空间.md

查看数据占用空间大小：

SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ' GB') AS 'Total Data Size'  FROM information_schema.TABLES WHERE table_schema LIKE 'quizzes';

查看指定数据库schema下的空间：

select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size, concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where TABLE_SCHEMA = 'quizzes';

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',  table_rows AS 'Number of Rows',  CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size',  CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' ,  CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total' FROM information_schema.TABLES  WHERE table_schema LIKE 'aries_account' order by Total;

查看该实例下所有schema的空间：

select table_schema, sum(data_length+index_length)/1024/1024 as total_mb,   
sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb,   
count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;

select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,  
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,   
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,  
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size 
 from information_schema.tables;

转载于:https://blog.51cto.com/niuduzi/2170381