我可以运行这个查询来获得MySQL数据库中所有表的大小:
show table status from myDatabaseName;
我希望有人能帮助我理解结果。我在找尺寸最大的桌子。
我应该看哪一列?
我可以运行这个查询来获得MySQL数据库中所有表的大小:
show table status from myDatabaseName;
我希望有人能帮助我理解结果。我在找尺寸最大的桌子。
我应该看哪一列?
当前回答
最后计算数据库的总大小:
(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"
)
其他回答
另一种显示所占用的行数和空间并按其排序的方法。
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”。
如果您正在使用phpmyadmin,那么只需转到表结构
e.g.
Space usage
Data 1.5 MiB
Index 0 B
Total 1.5 Mi
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;
你可以使用这个查询来显示表的大小(尽管你需要先替换变量):
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;
如果你有ssh访问权限,你可能想简单地尝试du -hc /var/lib/mysql(或不同的datadir,在my.cnf中设置)。