mysql 磁盘多少合适,您如何确定特定的MySQL表占用多少磁盘空间？

小编典典

对于表，请mydb.mytable运行以下命令：

字节

SELECT (data_length+index_length) tablesize

FROM information_schema.tables

WHERE table_schema='mydb' and table_name='mytable';

基洛比

SELECT (data_length+index_length)/power(1024,1) tablesize_kb

FROM information_schema.tables

WHERE table_schema='mydb' and table_name='mytable';

兆字节

SELECT (data_length+index_length)/power(1024,2) tablesize_mb

FROM information_schema.tables

WHERE table_schema='mydb' and table_name='mytable';

技嘉

SELECT (data_length+index_length)/power(1024,3) tablesize_gb

FROM information_schema.tables

WHERE table_schema='mydb' and table_name='mytable';

一般

这是一个通用查询，其中最大单位显示为TB(TeraBytes)

SELECT

CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,

CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,

CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE

FROM

(

SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3

FROM

(

SELECT data_length DAT,index_length NDX,data_length+index_length TBL,

FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,

FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,

FLOOR(LOG(IF(data_length+index_length=0,1,data_length+index_length))/LOG(1024)) pz

FROM information_schema.tables

WHERE table_schema='mydb'

AND table_name='mytable'

) AA

) A,(SELECT 'B KBMBGBTB' units) B;

试试看 ！！！

2020-05-17