Linux檢視MYSQL資料庫容量大小命令

獵手家園發表於2020-07-09
LinuxMySql資料庫

首先了解一下 “information_schema” 這張表：https://www.cnblogs.com/hunttown/p/13272680.html

 

一、查詢所有資料庫的總大小

mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_mb from TABLES;

 

二、查詢每個資料庫的大小

mysql> use information_schema;
mysql> SELECT table_schema,CONCAT(ROUND(SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024,2),'MB') AS total_mb FROM TABLES GROUP BY table_schema;

 

三、查詢各資料庫容量

select
    table_schema as '資料庫',
    sum(table_rows) as '記錄數',
    sum(truncate(data_length/1024/1024, 2)) as '資料容量(MB)',
    sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema

 

四、查詢各資料表容量大小

select
    table_schema as '資料庫',
    table_name as '表名',
    table_rows as '記錄數',
    truncate(data_length/1024/1024, 2) as '資料容量(MB)',
    truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

 

相關文章