如何确定特定 MySQL 表占用了多少磁盘空间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6474591/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can you determine how much disk space a particular MySQL table is taking up?
提问by dan
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。
回答by RolandoMySQLDBA
For a table mydb.mytablerun 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 (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;
Give it a Try !!!
试一试 ！！！
回答by Ben
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!
希望这对某人有用！
回答by hs76
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 的此链接。
https://www.percona.com/blog/2008/12/16/how-much-space-does-empty-innodb-table-take/
https://www.percona.com/blog/2008/12/16/how-much-space-does-empty-innodb-table-take/
回答by geekymartian
In linux with mysql installed by default:
在默认安装 mysql 的 linux 中：
[you@yourbox]$ ls -lha /var/lib/mysql/<databasename>
based on NIXCRAFT's mysql db location
回答by Arthur Felipe
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!
真的很喜欢！
回答by Rahul
Taken from How do I check how much disk space my database is using?
You can check MySQL table size either by looking at
phpMyAdminin 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
下面的查询也将有助于获取相同的信息 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';
回答by Luke Chadwick
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 .*' 来获取磁盘上表的大小。
回答by Natouriano
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
