MySQL查看库和表容量大小方法

MySQL查看库和表容量大小方法

-- 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 '数据库',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='weiya_chat';
-- 3.查看指定数据库各表容量大小
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='weiya_chat' ORDER BY data_length desc, index_length desc;
-- 4.查看所有数据库各表容量大小
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;