MySQL统计数据库容量大小

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