我可以运行这个查询来获得MySQL数据库中所有表的大小:
show table status from myDatabaseName;
我希望有人能帮助我理解结果。我在找尺寸最大的桌子。
我应该看哪一列?
我可以运行这个查询来获得MySQL数据库中所有表的大小:
show table status from myDatabaseName;
我希望有人能帮助我理解结果。我在找尺寸最大的桌子。
我应该看哪一列?
当前回答
改编自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;
其他回答
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)
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;
这将对大小进行排序(DB大小以MB为单位)。
这只是一个供以后参考的说明。所有答案都依赖于I_S.TABLES。例如,如果你在表中有blob字段,它不会告诉你正确的大小。LOB页存储在外部页中,因此不计入聚集索引。 事实上有一个注释:
对于NDB表,该语句的输出显示适当的值 AVG_ROW_LENGTH和DATA_LENGTH列,例外 没有考虑到BLOB列。
我发现InnoDB也是如此。
我已经创建了社区Bug相同。
我发现现有的答案实际上并没有给出磁盘上表的大小,这更有帮助。 与基于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 AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "$DB_NAME"
)
UNION ALL
(SELECT
'TOTAL:',
SUM(round(((data_length + index_length) / 1024 / 1024), 2) )
FROM information_schema.TABLES
WHERE table_schema = "$DB_NAME"
)