Mysql统计空间增量实现方式_Mysql

1、统计【所有库】每月新增空间总量

select
    date_format(create_time, '%y-%m') as 月份,
    round(sum(data_length + index_length) / 1024 / 1024, 2) as 新增总空间_mb,
    round(sum(data_length + index_length) / 1024 / 1024 / 1024, 2) as 新增总空间_gb
from
    information_schema.tables
where
    table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys') -- 排除系统库
    and create_time is not null -- 只统计有创建时间的表
group by
    date_format(create_time, '%y-%m')
order by
    月份 desc;

2、统计【指定库】每月新增空间

把 your_database 换成你的数据库名即可：

select
    date_format(create_time, '%y-%m') as 月份,
    round(sum(data_length + index_length) / 1024 / 1024, 2) as 新增空间_mb,
    round(sum(data_length + index_length) / 1024 / 1024 / 1024, 2) as 新增空间_gb
from
    information_schema.tables
where
    table_schema = 'your_database' -- 替换成你的库名
    and create_time is not null
group by
    date_format(create_time, '%y-%m')
order by
    月份 desc;

3、更精准：按【修改时间】统计每月增量（推荐）

create_time 是表创建时间，实际业务中更应该用 update_time（表最后修改时间） 代表每月数据增长：

select
    date_format(update_time, '%y-%m') as 月份,
    round(sum(data_length + index_length) / 1024 / 1024, 2) as 当月总占用_mb,
    round(sum(data_length + index_length) / 1024 / 1024 / 1024, 2) as 当月总占用_gb
from
    information_schema.tables
where
    table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
    and update_time is not null
group by
    date_format(update_time, '%y-%m')
order by
    月份 desc;