我可以运行这个查询来获得MySQL数据库中所有表的大小:

show table status from myDatabaseName;

我希望有人能帮助我理解结果。我在找尺寸最大的桌子。

我应该看哪一列?


当前回答

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

您可以从“information_schema”-> SCHEMATA表->“SCHEMA_NAME”列中获取模式名称


额外的 你可以得到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
ORDER BY `DB Size in MB` DESC;

结果

DB Name              |      DB Size in MB

mydatabase_wrdp             39.1
information_schema          0.0

你可以在这里得到更多的细节。

其他回答

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

这应该在mysql中测试,而不是postgresql:

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; 

改编自ChapMic的回答,以满足我的特殊需要。

只指定数据库名称,然后按降序对所有表进行排序——在所选数据库中从最大到最小的表。只需要替换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;

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

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;

我发现现有的答案实际上并没有给出磁盘上表的大小,这更有帮助。 与基于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