作為DBA肯定要經(jīng)常了解數(shù)據(jù)庫(kù)的空間剩余大小和表空間使用情況。
使用navicat直接查看，不能直觀(guān)的顯示所有的表記錄數(shù)和空間大小使用情況，一般文本記錄數(shù)多，但是占用空間比較大的還是圖片和文件的。
如何查詢(xún)可以顯示所有的表空間記錄數(shù)和表占用空間大小的排名。可以使用下面的語(yǔ)句：
查看所有數(shù)據(jù)庫(kù)容量大小
select
table_schema as '數(shù)據(jù)庫(kù)',
sum(table_rows) as '記錄數(shù)',
sum(truncate(data_length/1024/1024, 2)) as '數(shù)據(jù)容量(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;
查看所有數(shù)據(jù)庫(kù)各表容量大小
select
table_schema as '數(shù)據(jù)庫(kù)',
table_name as '表名',
table_rows as '記錄數(shù)',
truncate(data_length/1024/1024, 2) as '數(shù)據(jù)容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
如果你有多個(gè)數(shù)據(jù)庫(kù)，想要查指定某個(gè)數(shù)據(jù)庫(kù)的大小，可以使用下面的語(yǔ)句：
查看指定數(shù)據(jù)庫(kù)容量大小
例：查看mydb庫(kù)容量大小
select
table_schema as '數(shù)據(jù)庫(kù)',
sum(table_rows) as '記錄數(shù)',
sum(truncate(data_length/1024/1024, 2)) as '數(shù)據(jù)容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mydb';
查看指定數(shù)據(jù)庫(kù)各表容量大小
例：查看mydb庫(kù)各表容量大小
select
table_schema as '數(shù)據(jù)庫(kù)',
table_name as '表名',
table_rows as '記錄數(shù)',
truncate(data_length/1024/1024, 2) as '數(shù)據(jù)容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mydb'
order by data_length desc, index_length desc;
________________END______________
