1、mysql中查看各表的大小

这里用到一个表, information_schema.tables；对应主要字段含义如下

ABLE_SCHEMA : 数据库名
TABLE_NAME：表名
ENGINE：所使用的存储引擎
TABLES_ROWS：记录数
DATA_LENGTH：数据大小
INDEX_LENGTH：索引大小

按记录数据统计：

select table_schema,table_name,table_rows from tables order by table_rows desc;

2、查询所有数据库占用磁盘空间大小的SQL语句

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,  
concat(truncate(sum(index_length)/1024/1024,2),' MB') as index_size  
from information_schema.tables  
group by TABLE_SCHEMA  
order by data_length desc;

3、查询单个库中所有表磁盘占用大小的SQL语句

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 = 'sor'  
group by TABLE_NAME  
order by data_length desc;

4、查看一个库中的使用情况

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