mysql 查看表大小的方法實踐
1.查看所有數據庫容量大小
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 order by sum(data_length) desc, sum(index_length) desc;
2.查看所有數據庫各表容量大小
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;
3.查看指定數據庫容量大小
例:查看mysql庫容量大小:代碼如下:
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 where table_schema='mysql';
4.查看指定數據庫各表容量大小*
例:查看mysql庫各表容量大小
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 where table_schema='mysql' order by data_length desc, index_length desc;
PS:查看MySql數據空間使用情況:
information_schema是MySQL的系統數據庫,information_schema裡的tables表存放瞭整個數據庫各個表的使用情況。
可以使用sql來統計出數據庫的空間使用情況,相關字段:
- table_schema:數據庫名
- table_name:表名
- table_rows:記錄數
- data_length:數據大小
- index_length:索引大小
使用空間
1、統計表使用空間
select concat(round(sum(data_length/1024/1024),2),'mb') as data from tables where table_schema='mydb' and table_name='mytable';
| data |
| 0.02mb |
1 row in set (0.00 sec)
2、統計數據庫使用空間
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='mydb';
| data |
| 6.64MB |
1 row in set (0.00 sec)
3、統計所有數據使用空間
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
| data |
| 6.64MB |
1 row in set (0.01 sec)
到此這篇關於mysql 查看表大小的方法實踐的文章就介紹到這瞭,更多相關mysql 查看表大小內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- MySQL 表空間碎片的概念及相關問題解決
- Mysql查詢所有表和字段信息的方法
- MySQL中limit對查詢語句性能的影響
- 一次SQL查詢優化原理分析(900W+數據從17s到300ms)
- MySQL 用 limit 為什麼會影響性能