我要评论适用背景
交付项目时，客户需要统计数据库和表占用情况，来评估后续磁盘使用计划。
以下sql对应库的版本，mysql为5.7 pgsql为14.x
mysql
每个库的大小,单位mb
use information_schema; select table_schema,round(sum(data_length/1024/1024),2) as data from tables group by table_schema order by data desc;
具体库下的单表大小,单位mb
use information_schema; select table_name,round(sum(data_length/1024/1024),2) as data from tables where table_schema='db_name' group by table_name order by data desc;
pgsql
每个库的大小,单位mb
select
nspname as table_schema,
round(sum(pg_total_relation_size(c.oid)) / 1024.0 / 1024.0, 2) as total_mb
from
pg_class c
join
pg_namespace n on c.relnamespace = n.oid
where
c.relkind = 'r'
group by
nspname
order by
total_mb desc;
统计schema下单表大小,单位mb
select
relname as table_name,
round(pg_relation_size(c.oid) / 1024.0 / 1024.0, 2) as data_mb -- 表数据大小（mb）
from
pg_class c
join
pg_namespace n on c.relnamespace = n.oid
where
n.nspname = 'schema_name' -- 指定schema名称
and c.relkind = 'r' -- 只统计普通表
order by
data_mb desc;
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论