Mysql----查看数据库,表占用磁盘大小

素颜马尾好姑娘i 2024-02-18 23:00 86阅读 0赞

1、查询所有数据库占用磁盘空间大小  
select  
TABLE\_SCHEMA,  
concat(truncate(sum(data\_length)/1024/1024,2),' MB') as data\_size,  
concat(truncate(sum(index\_length)/1024/1024,2),'MB') as index\_size  
from information\_schema.tables  
group by TABLE\_SCHEMA  
ORDER BY data\_size desc;

![70][]

2、查询单个库中所有表磁盘占用大小  
select  
TABLE\_NAME,  
concat(truncate(data\_length/1024/1024,2),' MB') as data\_size,  
concat(truncate(index\_length/1024/1024,2),' MB') as index\_size  
from information\_schema.tables  
where TABLE\_SCHEMA = 'mysql'  
group by TABLE\_NAME  
order by data\_length desc;

![70 1][]

3、information\_schema 中有数个只读表。它们实际上是视图 ，而不是基本表，因此，你将无法看到与之相关的任何文件

4. 查看该数据库实例下所有库大小，得到的结果是以MB为单位  
select table\_schema,sum(data\_length)/1024/1024 as data\_length,sum(index\_length)/1024/1024 \\  
as index\_length,sum(data\_length+index\_length)/1024/1024 as sum from information\_schema.tables;

5、查看该实例下各个库大小  
select table\_schema, sum(data\_length+index\_length)/1024/1024 as total\_mb, \\  
sum(data\_length)/1024/1024 as data\_mb, sum(index\_length)/1024/1024 as index\_mb, \\  
count(\*) as tables, curdate() as today from information\_schema.tables group by table\_schema order by 2 desc;

![70 2][]  
6、查看单个库的大小  
select concat(truncate(sum(data\_length)/1024/1024,2),'mb') as data\_size, \\  
concat(truncate(sum(max\_data\_length)/1024/1024,2),'mb') as max\_data\_size, \\  
concat(truncate(sum(data\_free)/1024/1024,2),'mb') as data\_free, \\  
concat(truncate(sum(index\_length)/1024/1024,2),'mb') as index\_size\\  
from information\_schema.tables where table\_schema = 'erongtu\_tyb2014';

[70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/60a609289ef44efa9e1d2aed7a959fa7.png
[70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/78953c840c3c46c6a89702f287fbf247.png
[70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/bded1ff5ba794c9c8c3bbf2476003c7d.png