Mysql——》查看数据库表容量大小

最新推荐文章于 2024-11-17 08:30:00 发布

原创最新推荐文章于 2024-11-17 08:30:00 发布 · 384 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Mysql #data #length #大小

Mysql 专栏收录该内容

106 篇文章

订阅专栏

本文提供了一种使用SQL语句查询MySQL数据库中所有数据库及其各表的容量大小的方法，包括总容量、数据容量、索引容量及记录数，适用于数据库管理和优化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

版权声明：本文为博主原创文章，无需授权即可转载，甚至无需保留以上版权声明，转载时请务必注明作者。
https://blog.youkuaiyun.com/weixin_43453386/article/details/84570972

一、查看所有数据库容量大小

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;

二、查看所有数据库各表容量大小

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;

三、查看指定数据库容量大小

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';

四、查看指定数据库各表容量大小

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;