我可以运行这个查询来获得MySQL数据库中所有表的大小:
show table status from myDatabaseName;
我希望有人能帮助我理解结果。我在找尺寸最大的桌子。
我应该看哪一列?
当前回答
select x.dbname as db_name, x.table_name as table_name, x.bytesize as the_size from
(select
table_schema as dbname,
sum(index_length+data_length) as bytesize,
table_name
from
information_schema.tables
group by table_schema
) x
where
x.bytesize > 999999
order by x.bytesize desc;
其他回答
这只是一个供以后参考的说明。所有答案都依赖于I_S.TABLES。例如，如果你在表中有blob字段，它不会告诉你正确的大小。LOB页存储在外部页中，因此不计入聚集索引。 事实上有一个注释:
对于NDB表，该语句的输出显示适当的值 AVG_ROW_LENGTH和DATA_LENGTH列，例外 没有考虑到BLOB列。
我发现InnoDB也是如此。
我已经创建了社区Bug相同。
尝试以下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
select x.dbname as db_name, x.table_name as table_name, x.bytesize as the_size from
(select
table_schema as dbname,
sum(index_length+data_length) as bytesize,
table_name
from
information_schema.tables
group by table_schema
) x
where
x.bytesize > 999999
order by x.bytesize desc;
如果您正在使用phpmyadmin，那么只需转到表结构
e.g.
Space usage
Data 1.5 MiB
Index 0 B
Total 1.5 Mi
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为单位)。
