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上测试的

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

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”。

你可以使用这个查询来显示表的大小(尽管你需要先替换变量):

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";

或者这个查询列出每个数据库中每个表的大小，最大的先:

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;

尝试以下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

我发现现有的答案实际上并没有给出磁盘上表的大小，这更有帮助。 与基于data_length的表大小相比，此查询提供了更准确的磁盘估计 和索引。我不得不在AWS RDS实例中使用这种方法，因为您无法物理地检查磁盘和检查文件大小。

select NAME as TABLENAME,FILE_SIZE/(1024*1024*1024) as ACTUAL_FILE_SIZE_GB
, round(((data_length + index_length) / 1024 / 1024/1024), 2) as REPORTED_TABLE_SIZE_GB 
from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s
join INFORMATION_SCHEMA.TABLES t 
on NAME = Concat(table_schema,'/',table_name)
order by FILE_SIZE desc

如果您想查询使用当前选定的数据库。简单地复制粘贴这个查询。(无需修改)

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;