有没有一种快速的方法来确定一个特定的MySQL表占用了多少磁盘空间?该表可以是MyISAM或Innodb。
发布于 2011-06-25 07:12:36
对于表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;
试一试吧!
发布于 2016-06-29 18:35:31
使用SQL快速获取前20个最大的表( MB )。
SELECT table_schema, table_name,
ROUND((data_length+index_length)/POWER(1024,2),2) AS tablesize_mb
FROM information_schema.tables
ORDER BY tablesize_mb DESC LIMIT 20;
希望这对某些人有用!
发布于 2014-04-25 23:58:21
这对于InnoDB表来说并不准确。磁盘大小实际大于查询上报的大小。
有关更多信息,请参阅Percona的此链接。
https://www.percona.com/blog/2008/12/16/how-much-space-does-empty-innodb-table-take/
https://stackoverflow.com/questions/6474591
复制相似问题