文章目录
查看数据库数据量大小,占用磁盘大小
本次测试使用
mysql
数据库
数据库名demo
1. 查看现有数据库
show databases;
2. 进入information_schema 数据库(存放了其他的数据库的信息)
use information_schema;
3. 查看 information_schema 数据库表信息
select * from information_schema.tables;
4. 查询所有数据的总大小:
select
concat(round(sum(data_length/1024/1024),2),'MB') as data
from
information_schema.tables
;
5. 查看实例下所有数据库的空间占用情况,并按从大小排序
select
table_schema
, concat(round(sum(data_length/1024/1024),2),'MB') as data
from
information_schema.tables
where
table_schema like '%%'
group by
table_schema
order by
data desc
;
6.查看指定数据库的大小:
select
concat(round(sum(data_length/1024/1024),2),'MB') as data
from
information_schema.tables
where
table_schema='demo'
;
7. 查看指定数据库下的所有表的空间占用情况,并按从大到小排序
select
table_name
, round(sum(data_length/1024/1024),2) as size
from
information_schema.tables
where
table_schema='demo'
group by
table_name
order by
size desc
;
8. 查看指定数据库的某个表的大小
select
concat(round(sum(data_length/1024/1024),2),'MB') as data
from
information_schema.tables
where
table_schema ='demo'
and table_name='t_student'
;
其他
select
table_schema
, round(sum(data_length /1024/1024),2) as data_length
, round(sum(DATA_FREE /1024/1024),2) as data_free
, round(sum(INDEX_LENGTH/1024/1024),2) as INDEX_LENGTH
from
information_schema.tables
where
table_schema='demo'
group by
table_schema
order by
data_length
;
select
TABLE_SCHEMA
, sum(DATA_LENGTH) /1024/1024/1024 as size_DATA_LENGTH_g
, sum(INDEX_LENGTH)/1024/1024/1024 as size_INDEX_LENGTH_g
, sum(DATA_FREE) /1024/1024/1024 as size_DATA_FREE_g
, sum((DATA_LENGTH+INDEX_LENGTH+DATA_FREE))/1024/1024/1024 as size_g
from
information_schema.tables
where
table_type = 'BASE TABLE'
group by
TABLE_SCHEMA
order by
size_DATA_FREE_g
;
select
TABLE_NAME
, sum(DATA_LENGTH) /1024/1024/1024 as size_DATA_LENGTH_g
, sum(INDEX_LENGTH)/1024/1024/1024 as size_INDEX_LENGTH_g
, sum(DATA_FREE) /1024/1024/1024 as size_DATA_FREE_g
, sum((DATA_LENGTH+INDEX_LENGTH+DATA_FREE))/1024/1024/1024 as size_g
from
information_schema.tables
where
table_type = 'BASE TABLE'
and table_schema = 'db_name' –
and TABLE_NAME = 'table_name'
group by
TABLE_NAME
order by
size_g desc
limit 20