MySQL 如何获取MySQL数据库表的大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9620198/
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 the sizes of the tables of a MySQL database?
提问by JPashs
I can run this query to get the sizes of all tables in a MySQL database:
我可以运行此查询来获取 MySQL 数据库中所有表的大小：
show table status from myDatabaseName;
I would like some help in understanding the results. I am looking for tables with the largest sizes.
我想要一些帮助来理解结果。我正在寻找最大尺寸的桌子。
Which column should I look at?
我应该看哪一栏？
回答by ChapMic
You can use this query to show the size of a table (although you need to substitute the variables first):
您可以使用此查询来显示表的大小（尽管您需要先替换变量）：
SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "$DB_NAME"
AND table_name = "$TABLE_NAME";
or this query to list the size of every table in every database, largest first:
或此查询列出每个数据库中每个表的大小，首先是最大的：
SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
回答by Sumith Harshan
SELECT TABLE_NAME AS "Table Name",
table_rows AS "Quant of Rows", ROUND( (
data_length + index_length
) /1024, 2 ) AS "Total Size Kb"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'YOUR SCHEMA NAME/DATABASE NAME HERE'
LIMIT 0 , 30
You can get schema name from "information_schema" -> SCHEMATAtable -> "SCHEMA_NAME" column
您可以从“ information_schema” -> SCHEMATA表 -> “ SCHEMA_NAME”列中获取模式名称
AdditionalYou can get size of the mysql databasesas following.
附加您可以按如下方式获取mysql 数据库的大小。
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;
Result
结果
DB Name | DB Size in MB
mydatabase_wrdp 39.1
information_schema 0.0
You can get additional details in here.
您可以在此处获取更多详细信息。
回答by Gank
SELECT
table_name AS "Table",
round(((data_length + index_length) / 1024 / 1024), 2) as size
FROM information_schema.TABLES
WHERE table_schema = "YOUR_DATABASE_NAME"
ORDER BY size DESC;
This sorts the sizes (DB Size in MB).
这对大小进行排序（以 MB 为单位的 DB 大小）。
回答by zainengineer
If you want a query to use currently selected database. simply copy paste this query. (No modification required)
如果您希望查询使用当前选定的数据库。只需复制粘贴此查询。（无需修改）
SELECT table_name ,
round(((data_length + index_length) / 1024 / 1024), 2) as SIZE_MB
FROM information_schema.TABLES
WHERE table_schema = DATABASE() ORDER BY SIZE_MB DESC;
回答by Guppy
There is an easy way to get many informations using Workbench:
使用 Workbench 有一种简单的方法可以获取许多信息：
Right-click the schema name and click "Schema inspector".
In the resulting window you have a number of tabs. The first tab "Info" shows a rough estimate of the database size in MB.
The second tab, "Tables", shows Data length and other details for each table.
右键单击架构名称，然后单击“架构检查器”。
在结果窗口中，您有许多选项卡。第一个选项卡“信息”显示了以 MB 为单位的数据库大小的粗略估计。
第二个选项卡“表格”显示每个表格的数据长度和其他详细信息。
回答by Nurul Akter Towhid
Size of all tables:
Suppose your database or
TABLE_SCHEMAname is "news_alert".Then this query will show the size of all tables in the database.SELECT TABLE_NAME AS `Table`, ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "news_alert" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;Output:
+---------+-----------+ | Table | Size (MB) | +---------+-----------+ | news | 0.08 | | keyword | 0.02 | +---------+-----------+ 2 rows in set (0.00 sec)For the specific table:
Suppose your
TABLE_NAMEis "news". Then SQL query will be-SELECT TABLE_NAME AS `Table`, ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "news_alert" AND TABLE_NAME = "news" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;Output:
+-------+-----------+ | Table | Size (MB) | +-------+-----------+ | news | 0.08 | +-------+-----------+ 1 row in set (0.00 sec)
所有表的大小：
假设您的数据库或
TABLE_SCHEMA名称是“news_alert”。然后这个查询将显示数据库中所有表的大小。SELECT TABLE_NAME AS `Table`, ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "news_alert" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;输出：
+---------+-----------+ | Table | Size (MB) | +---------+-----------+ | news | 0.08 | | keyword | 0.02 | +---------+-----------+ 2 rows in set (0.00 sec)具体表：
假设您
TABLE_NAME是“新闻”。然后SQL查询将是-SELECT TABLE_NAME AS `Table`, ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "news_alert" AND TABLE_NAME = "news" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;输出：
+-------+-----------+ | Table | Size (MB) | +-------+-----------+ | news | 0.08 | +-------+-----------+ 1 row in set (0.00 sec)
回答by kenorb
Try the following shell command (replace DB_NAMEwith your database name):
尝试以下 shell 命令（替换DB_NAME为您的数据库名称）：
mysql -uroot <<<"SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"DB_NAME\" ORDER BY (data_length + index_length) DESC;" | head
mysql -uroot <<<"SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"DB_NAME\" ORDER BY (data_length + index_length) DESC;" | head
For Drupal/drush solution, check the following example script which will display the biggest tables in use:
对于 Drupal/drush 解决方案，请检查以下示例脚本，该脚本将显示正在使用的最大表：
#!/bin/sh
DB_NAME=$(drush status --fields=db-name --field-labels=0 | tr -d '\r\n ')
drush sqlq "SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"${DB_NAME}\" ORDER BY (data_length + index_length) DESC;" | head -n20
回答by user1380599
Heres another way of working this out from using the bash command line.
这是使用 bash 命令行解决此问题的另一种方法。
for i in mysql -NB -e 'show databases'; do echo $i; mysql -e "SELECT table_name AS 'Tables', round(((data_length+index_length)/1024/1024),2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema =\"$i\" ORDER BY (data_length + index_length) DESC" ; done
for i in mysql -NB -e 'show databases'; do echo $i; mysql -e "SELECT table_name AS 'Tables', round(((data_length+index_length)/1024/1024),2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema =\"$i\" ORDER BY (data_length + index_length) DESC" ; done
回答by Almis
If you are using phpmyadmin then just go to the table structure
如果您使用的是 phpmyadmin 则只需转到表结构
e.g.
例如
Space usage
Data 1.5 MiB
Index 0 B
Total 1.5 Mi
回答by dev101
Adapted from ChapMic's answer to suite my particular need.
改编自 ChapMic 的回答以满足我的特殊需求。
Only specify your database name, then sort all the tables in descending order - from LARGEST to SMALLEST table inside selected database. Needs only 1 variable to be replaced = your database name.
仅指定您的数据库名称，然后按降序对所有表进行排序 - 从所选数据库中的 LARGEST 到 SMALLEST 表。只需要替换 1 个变量 = 您的数据库名称。
SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) AS `size`
FROM information_schema.TABLES
WHERE table_schema = "YOUR_DATABASE_NAME_HERE"
ORDER BY size DESC;
