如何获得以 GB 为单位的 mysql 表大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15715487/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get mysql table size in GB
提问by Parthi04
Just ended up with calculating the size of MySQL table in GB with the following query.
刚刚结束使用以下查询计算 MySQL 表的大小（以 GB 为单位）。
SELECT (data_length+index_length)/power(1024,3) tablesize_gb FROM information_schema.tables WHERE table_schema='db' and table_name='tablename'
SELECT (data_length+index_length)/power(1024,3) tablesize_gb FROM information_schema.tables WHERE table_schema='db' and table_name='tablename'
Is it possible to get the size of a MySQL row in GB.
是否可以以 GB 为单位获取 MySQL 行的大小。
Or how to get the avg row size for the table in GB.
或者如何获得表的平均行大小（以 GB 为单位）。
回答by nneonneo
To get the average row length (including overhead), use the AVG_ROW_LENGTHcolumn in information_schema.tables.
要获得平均行长度（包括开销），请使用AVG_ROW_LENGTHinformation_schema.tables 中的列。
As far as I'm aware, there's no way to calculate the exact actual size of a single, specific row in MySQL.
据我所知，无法计算 MySQL 中单个特定行的确切实际大小。
回答by Ranil Wijeyratne
Hi this might do the trick, we had a similar issue and had to find out which types of rows take up the most space. That's why here with a group by...
嗨，这可能会奏效，我们遇到了类似的问题，必须找出哪种类型的行占用最多的空间。这就是为什么在这里与一群...
SELECT groupval, (sum(length(somefield) + length(someotherfield)) / 1024) / 1024 as "fields_size_mb"
FROM table
GROUP BY groupval
ORDER BY fields_size_mb desc;
回答by Entity
SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024 ), 2) as `Size in MB`,
round((AVG_ROW_LENGTH / 1024), 2) as `Avg row size in KB`
FROM information_schema.TABLES WHERE table_schema = 'your_db_name'
ORDER BY `Size in MB` DESC
回答by Django Janny
Not sure if you were looking for that, but I end up here looking for the theorical size of a row (addition of every fields' size).
不确定您是否在寻找那个，但我最终在这里寻找行的理论大小（每个字段的大小的添加）。
So I finaly came up with this request :
所以我最终提出了这个要求：
select TABLE_NAME, sum(CHARACTER_MAXIMUM_LENGTH)/power(1024,3)
from COLUMNS where TABLE_SCHEMA = 'schema' group by 1 order by 2 desc ;
回答by Ilshat Karazbayev
To calculate the size of a row, use length() method.
要计算行的大小，请使用 length() 方法。
For example:
例如：
MariaDB [db]> select id,length(row_to_calcsize) from tablename order by id desc limit 2\G
*************************** 1. row ***************************
id: 501
length(row_to_calcsize): 2192911
*************************** 2. row ***************************
id: 500
length(row_to_calcsize): 51657
2 rows in set (0.00 sec)
MariaDB [db]>
To calculate size in GB, just divide it 3 times per 1024
要以 GB 为单位计算大小，只需将其每 1024 次除以 3
length(row_to_calcsize)/1024/1024/1024
回答by Masood Alam
To find table size we can use something like this..
要找到表格大小，我们可以使用这样的东西..
SELECT count(*) tables,
concat(round(sum(table_rows)/1000000,2),'M') rows,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES
WHERE table_name like "%table-name%"
Find the largest table in MYSQL Databases we can use something like this
在 MYSQL 数据库中找到最大的表，我们可以使用这样的东西
SELECT CONCAT(table_schema, '.', table_name),
CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10
