blocks|key|639805|text|你也许可以看看这些文件的大小...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|639806|每个表都存储在一个文件夹中的两个单独的文件中，该文件夹的名称与您对数据库的名称无关。这些文件夹存储在mysql数据目录中。|639807|从那里，您可以执行'du+-sh+.*‘来获取磁盘上表的大小。|639808|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

<p>You could perhaps look at the size of the files...</p>

<p>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.</p>

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


blocks|key|639852|text|对于表mydb.mytable，运行以下命令：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|639853|字节数|639854|SELECT+(data_length%2Bindex_length)+tablesize
FROM+information_schema.tables
WHERE+table_schema='mydb'+and+table_name='mytable';|code-block|syntax|javascript|639855|千字节|639856|SELECT+(data_length%2Bindex_length)/power(1024,1)+tablesize_kb
FROM+information_schema.tables
WHERE+table_schema='mydb'+and+table_name='mytable';|639857|兆字节|639858|SELECT+(data_length%2Bindex_length)/power(1024,2)+tablesize_mb
FROM+information_schema.tables
WHERE+table_schema='mydb'+and+table_name='mytable';|639859|千兆字节|639860|SELECT+(data_length%2Bindex_length)/power(1024,3)+tablesize_gb
FROM+information_schema.tables
WHERE+table_schema='mydb'+and+table_name='mytable';|639861|通用|639862|下面是一个通用查询，其中最大单位显示为TB+(TeraBytes)|639863|SELECT+
++++CONCAT(FORMAT(DAT/POWER(1024,pw1),2),'+',SUBSTR(units,pw1*2%2B1,2))+DATSIZE,
++++CONCAT(FORMAT(NDX/POWER(1024,pw2),2),'+',SUBSTR(units,pw2*2%2B1,2))+NDXSIZE,
++++CONCAT(FORMAT(TBL/POWER(1024,pw3),2),'+',SUBSTR(units,pw3*2%2B1,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%2Bindex_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%2Bindex_length=0,1,data_length%2Bindex_length))/LOG(1024))+pz
++++++++FROM+information_schema.tables
++++++++WHERE+table_schema='mydb'
++++++++AND+table_name='mytable'
++++)+AA
)+A,(SELECT+'B+KBMBGBTB'+units)+B;|639864|试一试吧！|639865|entityMap^0|3|C|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1B|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1C|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1D|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|1E|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1F|8|@]|D|@]|E|$]]|$1|S|3|T|5|J|7|1G|8|@]|D|@]|E|$K|L]]|$1|U|3|V|5|6|7|1H|8|@]|D|@]|E|$]]|$1|W|3|X|5|J|7|1I|8|@]|D|@]|E|$K|L]]|$1|Y|3|Z|5|6|7|1J|8|@]|D|@]|E|$]]|$1|10|3|11|5|6|7|1K|8|@]|D|@]|E|$]]|$1|12|3|13|5|J|7|1L|8|@]|D|@]|E|$K|L]]|$1|14|3|15|5|6|7|1M|8|@]|D|@]|E|$]]|$1|16|3|-4|5|6|7|1N|8|@]|D|@]|E|$]]]|17|$]]

<p>For a table <code>mydb.mytable</code> run this for:</p>

<h1>BYTES</h1>

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

<h1>KILOBYTES</h1>

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

<h1>MEGABYTES</h1>

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

<h1>GIGABYTES</h1>

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

<h1>GENERIC</h1>

<p>Here is a generic query where the maximum unit display is TB (TeraBytes)</p>

<pre><code>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&gt;4,4,px) pw1,IF(py&gt;4,4,py) pw2,IF(pz&gt;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;
</code></pre>

<p>Give it a Try !!!</p>


blocks|key|639883|text|在默认安装了mysql的linux中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|639884|[you@yourbox]$+ls+-lha+/var/lib/mysql/<databasename>|code-block|syntax|javascript|639885|基于NIXCRAFT's+mysql+db+location|offset|length|639886|entityMap|0|LINK|mutability|MUTABLE|url|http://www.cyberciti.biz/faq/mysql-datadir-files-stored-unix-linux/^0|0|0|2|S|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

<p>In linux with mysql installed by default:</p>

<pre><code>[you@yourbox]$ ls -lha /var/lib/mysql/&lt;databasename&gt;
</code></pre>

<p>based on <a href="http://www.cyberciti.biz/faq/mysql-datadir-files-stored-unix-linux/" rel="nofollow">NIXCRAFT's mysql db location</a></p>


blocks|key|639904|text|这对于InnoDB表来说并不准确。磁盘大小实际大于查询上报的大小。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|639905|有关更多信息，请参阅Percona的此链接。|639906|https://www.percona.com/blog/2008/12/16/how-much-space-does-empty-innodb-table-take/|offset|length|639907|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|2C|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@$F|R|G|S|1|T]]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|E]]]]

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

<p>Please see this link from Percona for more information.</p>

<p><a href="https://www.percona.com/blog/2008/12/16/how-much-space-does-empty-innodb-table-take/" rel="nofollow noreferrer">https://www.percona.com/blog/2008/12/16/how-much-space-does-empty-innodb-table-take/</a></p>


blocks|key|857263|text|基于RolandMySQLDBA的回答，我认为我们可以使用上面的公式来获得表中每个模式的大小：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|857264|SELECT+table_schema,+SUM((data_length%2Bindex_length)/power(1024,1))+tablesize_kb+
++++FROM+information_schema.tables+GROUP+BY+table_schema;|code-block|syntax|javascript|857265|真的很喜欢！|857266|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

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

<p>Really liked it!</p>


blocks|key|639956|text|我只会使用'mysqldiskusage‘工具，如下所示|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|639957|$+mysqldiskusage+--server=user:password@localhost+mydbname
#+Source+on+localhost:+...+connected.

#+Database+totals:
%2B------------%2B----------------%2B
%7C+db_name++++%7C+++++++++total++%7C
%2B------------%2B----------------%2B
%7C+mydbaname++%7C+5,403,033,600++%7C
%2B------------%2B----------------%2B

Total+database+disk+usage+=+5,403,033,600+bytes+or+5.03+GB|code-block|syntax|javascript|639958|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiskusage.html^0|6|E|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

<p>I would just use '<a href="https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiskusage.html" rel="nofollow">mysqldiskusage</a>' tool as follow</p>

<pre><code>$ 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
</code></pre>


blocks|key|857327|text|使用SQL快速获取前20个最大的表(+MB+)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|857328|SELECT+table_schema,+table_name,
++ROUND((data_length%2Bindex_length)/POWER(1024,2),2)+AS+tablesize_mb
FROM+information_schema.tables
ORDER+BY+tablesize_mb+DESC+LIMIT+20;|code-block|syntax|javascript|857329|希望这对某些人有用！|857330|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

<p>Quick bit of SQL to get the top 20 biggest tables in MB.</p>

<pre><code>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;
</code></pre>

<p>Hope that's useful to somebody!</p>


blocks|key|640014|text|上面的大多数答案对于InnoDB表都是不准确的。你可以很容易的复制它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|640015|向表中添加10,000条虚拟记录，然后运行上面建议的查询。然后使用delete命令(而不是truncate)删除所有10,000条记录。|640016|再次运行上面的建议查询，您将看到大小不是0。|640017|磁盘上的文件只会增长，但即使删除了数据也不会缩小。如果你想缩小，你需要截断或运行一些东西，如优化表。|640018|似乎没有可靠的方法来了解InnoDB表记录实际消耗了多少空间。即使磁盘上的文件是100MB，数据库中的实际记录也可能是0。|640019|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

<p>Most answers above are inaccurate for InnoDB tables. You can replicate this easily.</p>
<p>Add 10,000 dummy records to a table, and run the above suggested queries. Then delete all 10,000 records using delete command (not truncate).</p>
<p>Run above suggested queries again, you will see the size is not 0.</p>
<p>Files on disk only grow but dont shrink even when data is deleted. If you want to shrink, you need to truncate or run something such as OPTIMIZE TABLE.</p>
<p>There seems no reliable way to know how much space InnoDB table records are really consuming. Even if file on disk says 100MB, actual records in DB could be 0.</p>


<p>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.</p>


How can you determine how much disk space a particular MySQL table is taking up?

数据库

MySQL

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

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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