如何确定一个特定的MySQL表占用了多少磁盘空间?

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.

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

8 个解决方案

#1

For a table mydb.mytable run this for:

表mydb。mytable运行这个:

BYTES

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

Here is a generic query where the maximum unit display is TB (TeraBytes)

下面是一个通用查询，其中最大的单元显示为TB (TB)

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;

Give it a Try !!!

试试看!!!

#2

This won't be accurate for InnoDB tables. The size on disk is actually bigger than that reported via query.

这对于InnoDB表来说并不准确。磁盘上的大小实际上比查询报告的大。

Please see this link from Percona for more information.

更多信息请参见Percona的链接。

http://www.mysqlperformanceblog.com/2008/12/16/how-much-space-does-empty-innodb-table-take/

http://www.mysqlperformanceblog.com/2008/12/16/how-much-space-does-empty-innodb-table-take/

#3

Quick bit of SQL to get the top 20 biggest tables in MB.

快速的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;

Hope that's useful to somebody!

希望对别人有用!

#4

In linux with mysql installed by default:

在默认安装mysql的linux中:

[you@yourbox]$ ls -lha /var/lib/mysql/<databasename>

based on NIXCRAFT's mysql db location

基于NIXCRAFT的mysql db位置

#5

Based on the RolandMySQLDBA's answer I think we can use the above to get the size of each schema in a table:

基于RolandMySQLDBA的答案，我认为我们可以使用上面的方法来得到表中每个模式的大小:

SELECT table_schema, SUM((data_length+index_length)/power(1024,1)) tablesize_kb 
    FROM information_schema.tables GROUP BY table_schema;

Really liked it!

真的很喜欢它!

#6

You could perhaps look at the size of the files...

你可以看看文件的大小……

Each table is stored in a couple of separate files inside a folder that is named whatever you called your database. These folders are stored within the mysql data directory.

每个表都存储在一个文件夹中的两个独立文件中，该文件夹的名称是您所调用的数据库。这些文件夹存储在mysql数据目录中。

From there you can do a 'du -sh .*' to get the size of the table on disk.

从那里，您可以做一个“du -sh .*”来获取磁盘上的表的大小。

#7

Taken from How do I check how much disk space my database is using?

从如何检查我的数据库使用了多少磁盘空间?

You can check MySQL table size either by looking at phpMyAdmin in your control panel by clicking on the database name in the left frame and reading the size for the tables in there in the right frame.

通过查看控制面板中的phpMyAdmin，您可以检查MySQL表的大小，方法是单击左侧框架中的数据库名，并读取右侧框架中的表的大小。

The below query will as well help to get the same information in bytes

下面的查询也将有助于以字节为单位获得相同的信息

select SUM(data_length) + SUM(index_length) as total_size 
from information_schema.tables 
where table_schema = 'db_name' 
and table_name='table_name';

#8

I would just use 'mysqldiskusage' tool as follow

我将使用“mysqldiskusage”工具，如下所示

$ mysqldiskusage --server=user:password@localhost mydbname
# Source on localhost: ... connected.

# Database totals:
+------------+----------------+
| db_name    |         total  |
+------------+----------------+
| mydbaname  | 5,403,033,600  |
+------------+----------------+

Total database disk usage = 5,403,033,600 bytes or 5.03 GB