mysql查询数据库和数据表占用内存大小

information_schema是mysql中一个默认的数据表，其中保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等

1、切换数据库

use information_schema;

2、查看所有数据库容量大小

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;

3、查看指定数据库使用大小

database_name 替换成 实际库名

select 
concat(round(sum(data_length/1024/1024),2),"MB") as data 
from tables 
where table_schema="database_name";

4、查看表使用大小

table_name 替换成 实际表名

select 
concat(round(sum(data_length/1024/1024),2),"MB") as data
from tables
where table_schema="database_name" and table_name="table_name";

5、查看所有数据库容量大小

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;

6、查看所有数据库各表容量大小

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;

7、查看指定数据库容量大小

table_name 替换成 实际表名

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="table_name";

8、查看指定数据库各表容量大小

table_name 替换成 实际表名

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
where table_schema="table_name"
order by data_length desc, index_length desc;