TypechoJoeTheme

Weclome to TTP

统计

MySQL统计数据库容量大小

TTP博主
2022-07-12
/
0 评论
/
295 阅读
/
126 个字
/
百度已收录
07/12
本文最后更新于2022年07月12日,已超过146天没有更新。如果文章内容或图片资源失效,请留言反馈,我会及时处理,谢谢!

MySQL统计数据库容量大小


查询所有数据库的总容量大小

use information_schema;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;

+-----------+
| data      |
+-----------+
| 3052.76MB |
+-----------+
1 row in set (0.02 sec)

统计每个库的大小

select table_schema,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb from information_schema.TABLES group by table_schema;

查看指定数据库大小

  • 示例:查看test数据库大小

    use information_schema;
    select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='test';
    +----------+
    | data     |
    +----------+
    | 142.84MB |
    +----------+
    1 row in set (0.00 sec)
    
    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='test';

查看所有数据库各表的大小

select table_schema as '数据库',table_name as '表名',table_row 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;

查看指定数据库的各表大小

  • 比如查看mysql各表大小

    select table_schema as '数据库',table_name as '表名',table_row 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;
mysql
朗读
赞(0)
版权属于:

Weclome to TTP

本文链接:

https://ttpc.asia/archives/179/(转载时请注明本文出处及文章链接)

评论 (0)
本篇文章评论功能已关闭
IP信息

标签云

最新回复

暂无回复