MYSQL查看所有数据库容量大小/表占用

方法一：

SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH)
FROM information_schema.TABLES<br>&nbsp;where TABLE_SCHEMA='oa_api';

方法二：

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;

指定表占用空间

SELECT table_name AS `Table`,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`
FROM information_schema.TABLES
WHERE table_schema = 'abc'
AND table_name = 'tp_test';
或者：
SELECT table_name AS `Table`,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size(MB)`
FROM information_schema.TABLES
WHERE table_schema = 'oa_api'
order by `Size(MB)` desc;

查询指定数据库的所有表的容量占用大小：

开启profiles
mysql> set profiling=1;
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH as '表大小',TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA='djjf_api' order by 表大小 desc;

查询剖析工具：show profile;
查询语句分析工具(包括索引):explain select * from artist