【藏经阁】查看MySQL库表大小
查询库大小[预估值]
select table_schema,
round((sum(DATA_LENGTH)+sum(INDEX_LENGTH)+sum(DATA_FREE))/1024/1024/1024,2) 表空间_GB,
round(sum(DATA_LENGTH)/1024/1024/1024,2) 数据空间_GB,
round(sum(INDEX_LENGTH)/1024/1024/1024,2) 索引空间_GB,
round(sum(DATA_FREE)/1024/1024/1024,2) 碎片空间_GB,
concat(round(sum(DATA_FREE)/(sum(DATA_LENGTH)+sum(INDEX_LENGTH)+sum(DATA_FREE))*100,2),'%') 碎片率
from information_schema.tables
group by table_schema
order by 表空间_GB desc;
查询库下的指定的各表大小[预估值]
select table_schema,table_name,sum(table_rows) 表行数,
round((sum(DATA_LENGTH)+sum(INDEX_LENGTH)+sum(DATA_FREE))/1024/1024/1024,2) 表空间_GB,
round(sum(DATA_LENGTH)/1024/1024/1024,2) 数据空间_GB,
round(sum(INDEX_LENGTH)/1024/1024/1024,2) 索引空间_GB,
round(sum(DATA_FREE)/1024/1024/1024,2) 碎片空间_GB,
concat(round(sum(DATA_FREE)/(sum(DATA_LENGTH)+sum(INDEX_LENGTH)+sum(DATA_FREE))*100,2),'%') 碎片率
from information_schema.tables
where table_schema='pp_tran'
group by table_schema,table_name
order by 表空间_GB desc;
查询分区表大小[预估值]
select table_name,(sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 GB from information_schema.partitions
where table_schema='pp_tran' AND table_name = 'a_chk_file_dtl'
and PARTITION_name < 'p20190409'
group by table_name
查看表在磁盘上实际大小[精确值]
需要 5.7+版本才有 INFORMATION_SCHEMA.FILES 表
mysql> SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC;
+----------------------+-----------+
| FILE_NAME | FIle_size |
+----------------------+-----------+
| ./ibdata1 | 100M |
| ./yqtest/sbtest1.ibd | 72M |
| ./sync/sbtest1.ibd | 72M |
| ./yqtest/loada.ibd | 48M |
| ./mysql.ibd | 24M |
| ./undo_002 | 16M |
| ./undo_001 | 16M |
| ./ibtmp1 | 12M |
| ./yqtest/a.ibd | 9M |
| ./test02/a.ibd | 0M |
| ./test02/b.ibd | 0M |
| ./test01/b.ibd | 0M |
| ./test01/a.ibd | 0M |
| ./sys/sys_config.ibd | 0M |
| ./sync/products.ibd | 0M |
| ./mytest/b.ibd | 0M |
| ./mytest/a.ibd | 0M |
+----------------------+-----------+
17 rows in set (0.14 sec)
SELECT file_name, concat(TOTAL_EXTENTS/1024,'G') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS desc