Mysql之统计函数

-- 查看所有表的大小
select TABLE_NAME,
concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as ALL_DB_DATA_SIZE,
concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as ALL_DB_INDEX_SIZE,
concat(round(sum((DATA_LENGTH + INDEX_LENGTH)/1024/1024),2),'MB') as ALL_DB_TOTAL_SIZE
from information_schema.TABLES
group by TABLE_NAME

-- 查看所有库的大小的排名
select
table_schema as DB_NAME,
concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as DB_DATA_SIZE,
concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as DB_INDEX_SIZE,
concat(round(sum((DATA_LENGTH + INDEX_LENGTH)/1024/1024),2),'MB') as DB_TOTAL_SIZE
from information_schema.TABLES
group by table_schema
order by sum(DATA_LENGTH + INDEX_LENGTH) desc ;

-- 查看指定库的表的大小排名
select
table_name as TABLE_NAME,
table_rows AS ROW_NUM,
concat(round(DATA_LENGTH/1024/1024,2),'MB') as TABLE_DATA_SIZE,
concat(round(INDEX_LENGTH/1024/1024,2),'MB') as TABLE_INDEX_SIZE,
concat(round((DATA_LENGTH + INDEX_LENGTH)/1024/1024,2),'MB') as TABLE_TOTAL_SIZE
from information_schema.TABLES
where table_schema='cmf_cdh'
order by (DATA_LENGTH + INDEX_LENGTH) desc;
 

统计结果如下：

结果二：

结果三：