另一种显示所占用的行数和空间并按其排序的方法。

SELECT
     table_schema as `Database`,
     table_name AS `Table`,
     table_rows AS "Quant of Rows",
     round(((data_length + index_length) / 1024 / 1024/ 1024), 2) `Size in GB`
FROM information_schema.TABLES
WHERE table_schema = 'yourDatabaseName'
ORDER BY (data_length + index_length) DESC;  

在这个查询中唯一需要替换的字符串是“yourDatabaseName”。

Size of all tables: Suppose your database or TABLE_SCHEMA name 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_NAME is "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)

有一种使用Workbench获取许多信息的简单方法:

右键单击模式名并单击“模式检查器”。 在生成的窗口中有许多选项卡。第一个标签 “信息”显示了数据库大小的粗略估计(以MB为单位)。 第二个选项卡“表”显示每个表的数据长度和其他详细信息。

下面是另一种使用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

SELECT TABLE_NAME AS table_name, 
table_rows AS QuantofRows, 
ROUND((data_length + index_length) /1024, 2 ) AS total_size_kb 
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'db'
ORDER BY (data_length + index_length) DESC; 

以上2个都是在mysql上测试的

尝试以下shell命令(将DB_NAME替换为您的数据库名称):

mysql -uroot <<<"SELECT table_name AS 'Tables'， round((data_length + index_length) / 1024 / 1024)， 2)Size in MB FROM information_schematable WHERE table_schema = \"DB_NAME\" ORDER BY (data_length + index_length) DESC;"|头

对于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