最近收到rds警告说数据库大小预警,想了解下具体mysql数据库的情况,比如各数据库表的容量。准备对表的数据进行详细分析
对数据库进行一次瘦身,其中用到的SQL语句如下,从网上摘录下来的。
1、查看数据库大小
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;
2、查看数据库表大小
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;