自動目錄
法一
到mysql的目錄，以Centos為例：
# cd /var/lib/mysql
由於mysql是一個資料庫占一個目錄，所以下指令：
# du -Sh dbname
3.1M dbname
法二
進到你的mysql console，選擇你的資料庫：
mysql> use dbname;
mysql> SHOW TABLE STATUS;
或直接下指令
# mysqlshow --status note
列出的
Data_length + Index_length 就是大小，單位是bytes.
這個方法的好處是可以列出每一個table所占的大小，但比較麻煩是沒有加總，您得自己加總
吃飯前禱告一下, 你先別跑...
法三
在[1]下方有kevingo網友提供一個複雜的語法來查看，我略為修改：
SELECT concat(table_schema,'.',table_name), concat(round(table_rows/1000,2),'K') rows, concat(round(data_length/(1024*1024),2),'M') DATA, concat(round(index_length/(1024*1024),2),'M') idx, concat(round((data_length+index_length)/(1024*1024),2),'M') total_size, round(index_length/data_length,2) idxfrac FROM information_schema.TABLES WHERE table_schema='note' ORDER BY data_length+index_length ;
請修改第8行的"note"為您的資料庫名
執行結果：(idxfrac:索引/資料的比值)
mysql> SELECT concat(table_schema,'.',table_name),
-> concat(round(table_rows/1000,2),'K') rows,
-> concat(round(data_length/(1024*1024),2),'M') DATA,
-> concat(round(index_length/(1024*1024),2),'M') idx,
-> concat(round((data_length+index_length)/(1024*1024),2),'M') total_size,
-> round(index_length/data_length,2) idxfrac
-> FROM information_schema.TABLES where table_schema='note'
-> ORDER BY data_length+index_length ;
+-------------------------------------+-------+-------+-------+------------+---------+
| concat(table_schema,'.',table_name) | rows | DATA | idx | total_size | idxfrac |
+-------------------------------------+-------+-------+-------+------------+---------+
| note.contact | 0.00K | 0.00M | 0.00M | 0.00M | 2.23 |
| note.month_user | 0.04K | 0.00M | 0.00M | 0.00M | 2.12 |
| note.log_nobody | 0.02K | 0.00M | 0.00M | 0.00M | 2.04 |
| note.media_extra | 0.01K | 0.00M | 0.00M | 0.00M | 13.13 |
... 略 ...
| note.test | 0.00K | 1.60M | 0.00M | 1.60M | 0.00 |
| note.temp | 5.81K | 2.86M | 0.34M | 3.20M | 0.12 |
+-------------------------------------+-------+-------+-------+------------+---------+
25 rows in set (0.00 sec)
上面的缺點還是沒有算加總，所以再改一下
SELECT round(sum(data_length)/(1024*1024),2) DATA_SUM, round(sum(index_length)/(1024*1024),2) INDEX_SUM, round(sum(data_length+index_length)/(1024*1024),2) TOTAL FROM information_schema.TABLES where table_schema='note';
執行結果(單位是M)
mysql> SELECT round(sum(data_length)/(1024*1024),2) DATA_SUM,
-> round(sum(index_length)/(1024*1024),2) INDEX_SUM,
-> round(sum(data_length)+sum(index_length),2) TOTAL
-> FROM information_schema.TABLES
-> where table_schema='note';
+----------+-----------+-------+
| DATA_SUM | INDEX_SUM | TOTAL |
+----------+-----------+-------+
| 6.35 | 0.64 | 6.99 |
+----------+-----------+-------+
1 row in set (0.00 sec)
參考資料
[1] Tsung's blog http://blog.longwin.com.tw/2009/02/view-mysql-table-size-2009/
原文 2013-09-10 14:42:11
