您如何确定特定的MySQL表占用多少磁盘空间? [英] How can you determine how much disk space a particular MySQL table is taking up?

问题描述

有没有一种快速的方法来确定特定的MySQL表占用了多少磁盘空间?该表可以是MyISAM或Innodb.

Is there a quick way to determine how much disk space a particular MySQL table is taking up? The table may be MyISAM or Innodb.

推荐答案

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

For a table mydb.mytable run this for:

SELECT (data_length+index_length) tablesize
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

KILOBYTES

SELECT (data_length+index_length)/power(1024,1) tablesize_kb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

MEGABYTES

SELECT (data_length+index_length)/power(1024,2) tablesize_mb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

GIGABYTES

SELECT (data_length+index_length)/power(1024,3) tablesize_gb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

GENERIC

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

GENERIC

Here is a generic query where the maximum unit display is 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;

尝试一下！

这篇关于您如何确定特定的MySQL表占用多少磁盘空间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！