如何获得 MySQL 数据库的真实大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14714750/
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 to Get True Size of MySQL Database?
提问by marvin
I would like to know how much space does my MySQL database use, in order to select a web host.
I found the command SHOW TABLE STATUS LIKE 'table_name'so when I do the query, I get something like this:
我想知道我的 MySQL 数据库使用了多少空间，以便选择 Web 主机。我找到了命令，SHOW TABLE STATUS LIKE 'table_name'所以当我执行查询时，我得到如下信息：
Name | Rows | Avg. Row Length | Data_Length | Index Length
---------- ---- --------------- ----------- ------------
table_name 400 55 362000 66560
- numbers are rounded.
- 数字四舍五入。
So do I have 362000or 400*362000 = 144800000bytes of data for this table? And what does Index Length mean? Thanks !
那么我有362000或 400*362000 = 144800000字节的数据用于该表吗？索引长度是什么意思？谢谢 ！
回答by ron_dobley
From S. Prakash, found at the MySQL forum:
来自 S. Prakash，在MySQL 论坛上找到：
SELECT table_schema "database name",
sum( data_length + index_length ) / 1024 / 1024 "database size in MB",
sum( data_free )/ 1024 / 1024 "free space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;
Or in a single line for easier copy-pasting:
或者在一行中更容易复制粘贴：
SELECT table_schema "database name", sum( data_length + index_length ) / 1024 / 1024 "database size in MB", sum( data_free )/ 1024 / 1024 "free space in MB" FROM information_schema.TABLES GROUP BY table_schema;
回答by minhas23
You can get the size of your Mysql database by running the following command in Mysql client
您可以通过在 Mysql 客户端运行以下命令来获取您的 Mysql 数据库的大小
SELECT sum(round(((data_length + index_length) / 1024 / 1024 / 1024), 2)) as "Size in GB"
FROM information_schema.TABLES
WHERE table_schema = "<database_name>"
回答by Radu Murzea
If you use phpMyAdmin, it can tell you this information.
如果您使用 phpMyAdmin，它可以告诉您这些信息。
Just go to "Databases" (menu on top) and click "Enable Statistics".
只需转到“数据库”（顶部菜单）并单击“启用统计”。
You will see something like this:
你会看到这样的事情：
This will probably lose some accuracy as the sizes go up, but it should be accurate enough for your purposes.
随着尺寸的增加，这可能会失去一些准确性，但它应该足以满足您的目的。
回答by jakson
if you want to find it in MB do this
如果您想在 MB 中找到它，请执行此操作
SELECT table_schema "DB Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;
回答by MiGro
Basically there are two ways: query DB (data length + index length) or check files size. Index length is related to data stored in indexes.
基本上有两种方法：查询DB（数据长度+索引长度）或检查文件大小。索引长度与索引中存储的数据有关。
Everything is described here:
一切都在这里描述：
http://www.mkyong.com/mysql/how-to-calculate-the-mysql-database-size/
http://www.mkyong.com/mysql/how-to-calculate-the-mysql-database-size/
回答by Semra
None of the answers include the overhead size and the metadata sizes of tables.
没有一个答案包括表的开销大小和元数据大小。
Here is a more accurate estimation of the "disk space" allocated by a database.
这是对数据库分配的“磁盘空间”的更准确估计。
SELECT ROUND((SUM(data_length+index_length+data_free) + (COUNT(*) * 300 * 1024))/1048576+150, 2) AS MegaBytes FROM information_schema.TABLES WHERE table_schema = 'DATABASE-NAME'
回答by Rick James
回答by ofrommel
MySQL Utilities by Oracle have a command called mysqldiskusage that displays the disk usage of every database: https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqldiskusage.html
Oracle 的 MySQL Utilities 有一个名为 mysqldiskusage 的命令，它显示每个数据库的磁盘使用情况：https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqldiskusage.html
回答by Viraj Dhamal
If you are using MySql Workbench, its very easy to get all details of Database size, each table size, index size etc.
如果您使用的是 MySql Workbench，则很容易获得数据库大小、每个表大小、索引大小等的所有详细信息。
- Right Click on Schema
Select Schema Inspectoroption
It Shows all details of Schema size
Select TablesTab to see size of each table.
Table size diplayed in Data Lenght column
- 右键单击架构
选择架构检查器选项
它显示架构大小的所有详细信息
选择表格选项卡以查看每个表格的大小。
数据长度列中显示的表大小
回答by Arbab Nazar
If you want to find the size of all MySQL databases, us this command, it will show their respective sizes in megabytes;
如果要查找所有 MySQL 数据库的大小，使用这个命令，它会显示它们各自的大小（以兆字节为单位）；
SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema;
If you have large databases, you can use the following command to show the result in gigabytes;
如果您有大型数据库，可以使用以下命令以 GB 为单位显示结果；
SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES GROUP BY table_schema;
If you want to show the size of only a specific database, for example YOUR_DATABASE_NAME, you could use the following query;
例如，如果您只想显示特定数据库的大小YOUR_DATABASE_NAME，则可以使用以下查询；
SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES WHERE table_schema='YOUR_DATABASE_NAME' GROUP BY table_schema;
