如何获取MySQL数据库表的大小？

我可以运行此查询来获取MySQL数据库中所有表的大小：

show table status from myDatabaseName;

我希望在理解结果方面得到一些帮助。我正在寻找最大尺寸的表格。

我应该看哪一栏？

817182 次浏览
小开
最佳答案

您可以使用此查询来显示表的大小（尽管您需要先替换变量）：

SELECTtable_name AS `Table`,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`FROM information_schema.TABLESWHERE table_schema = "$DB_NAME"AND table_name = "$TABLE_NAME";

或者这个查询列出每个数据库中每个表的大小，最大的第一：

SELECTtable_schema as `Database`,table_name AS `Table`,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`FROM information_schema.TABLESORDER BY (data_length + index_length) DESC;
小开 
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.TABLESWHERE 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.tablesGROUP  BY table_schemaORDER BY `DB Size in MB` DESC;

结果

DB Name              |      DB Size in MB
mydatabase_wrdp             39.1information_schema          0.0

你可以在这里获取更多细节。

小开 
SELECTtable_name AS "Table",round(((data_length + index_length) / 1024 / 1024), 2) as sizeFROM information_schema.TABLESWHERE table_schema = "YOUR_DATABASE_NAME"ORDER BY size DESC;

这会对大小进行排序（以MB为单位的DB大小）。

小开

尝试以下shell命令（将DB_NAME替换为您的库名）：

mysql -uroot <<<"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

对于Drupal/drush解决方案，请检查以下示例脚本，该脚本将显示正在使用的最大表：

#!/bin/shDB_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
小开

这是使用bash命令行解决此问题的另一种方法。

for i in `mysql -NB -e 'show databases'`; do echo $i; mysql -e "SELECT table_name AS 'Tables', round(((data_length+index_length)/1024/1024),2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema =\"$i\" ORDER BY (data_length + index_length) DESC" ; done
小开

有一个简单的方法来获得许多信息使用工作台：

  • 右键单击架构名称并单击“架构检查器”。

  • 在生成的窗口中，您有许多选项卡。第一个选项卡“Info”显示了以MB为单位的数据库大小的粗略估计。

  • 第二个选项卡“表”显示每个表的数据长度和其他详细信息。

小开

如果您使用的是phpmyadmin，那么只需转到表结构

e. g.

Space usageData    1.5 MiBIndex   0   BTotal   1.5 Mi
小开

另一种显示它占用的行数和空间以及排序的方式。 

SELECTtable_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.TABLESWHERE table_schema = 'yourDatabaseName'ORDER BY (data_length + index_length) DESC;

在此查询中，您必须替换的唯一字符串是“您的数据库名称”。

小开

如果您希望查询使用当前选择的数据库。只需复制粘贴此查询。（无需修改）

SELECT table_name ,round(((data_length + index_length) / 1024 / 1024), 2) as SIZE_MBFROM information_schema.TABLESWHERE table_schema = DATABASE() ORDER BY SIZE_MB DESC;
小开

根据ChapMic的回答改编，以满足我的特殊需求。

仅指定您的库名，然后按降序对所选数据库中的所有表进行排序-从LARGEST到SMALLEST表。只需要替换1个变量=您的库名。

SELECTtable_name AS `Table`,round(((data_length + index_length) / 1024 / 1024), 2) AS `size`FROM information_schema.TABLESWHERE table_schema = "YOUR_DATABASE_NAME_HERE"ORDER BY size DESC;
小开

如果您有ssh访问权限，您可能希望简单地尝试du -hc /var/lib/mysql（或不同的datadir，如您的my.cnf中设置的）。

小开

  • 所有表格的大小：

    假设您的数据库或TABLE_SCHEMA名称为"news_alert"，则此查询将显示数据库中所有表的大小。

    SELECTTABLE_NAME AS `Table`,ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)`FROMinformation_schema.TABLESWHERETABLE_SCHEMA = "news_alert"ORDER BY(DATA_LENGTH + INDEX_LENGTH)DESC;

    输出：

        +---------+-----------+| 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-

    SELECTTABLE_NAME AS `Table`,ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)`FROMinformation_schema.TABLESWHERETABLE_SCHEMA = "news_alert"ANDTABLE_NAME = "news"ORDER BY(DATA_LENGTH + INDEX_LENGTH)DESC;

    输出：

    +-------+-----------+| Table | Size (MB) |+-------+-----------+| news  |      0.08 |+-------+-----------+1 row in set (0.00 sec)
小开

最后计算数据库的总大小：

(SELECTtable_name AS `Table`,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`FROM information_schema.TABLESWHERE table_schema = "$DB_NAME")UNION ALL(SELECT'TOTAL:',SUM(round(((data_length + index_length) / 1024 / 1024), 2) )FROM information_schema.TABLESWHERE table_schema = "$DB_NAME")
小开

这应该在mysql中测试，而不是postgresql：

SELECT table_schema, # "DB Name",Round(Sum(data_length + index_length) / 1024 / 1024, 1) # "DB Size in MB"FROM   information_schema.tablesGROUP  BY table_schema;
小开 
SELECT TABLE_NAME AS table_name,table_rows AS QuantofRows,ROUND((data_length + index_length) /1024, 2 ) AS total_size_kbFROM information_schema.TABLESWHERE information_schema.TABLES.table_schema = 'db'ORDER BY (data_length + index_length) DESC;

以上所有2个都在mysql上测试

小开

我发现现有的答案实际上并没有给出磁盘上表的大小，这更有帮助。与基于data_length的表大小相比，此查询提供更准确的磁盘估计&index。我不得不将其用于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_GBfrom INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES sjoin INFORMATION_SCHEMA.TABLES ton NAME = Concat(table_schema,'/',table_name)order by FILE_SIZE desc
小开 
select x.dbname as db_name, x.table_name as table_name, x.bytesize as the_size from(selecttable_schema as dbname,sum(index_length+data_length) as bytesize,table_namefrominformation_schema.tablesgroup by table_schema) xwherex.bytesize > 999999order by x.bytesize desc;
小开

我制作了这个外壳脚本来跟踪表的大小（以字节和行数为单位）

#!/bin/sh
export MYSQL_PWD=XXXXXXXXTABLES="table1 table2 table3"
for TABLE in $TABLES;doFILEPATH=/var/lib/mysql/DBNAME/$TABLE.ibdTABLESIZE=`wc -c $FILEPATH | awk '{print $1}'`#Size in Bytesmysql -D scarprd_self -e "INSERT INTO tables_sizes (table_name,table_size,measurement_type) VALUES ('$TABLE', '$TABLESIZE', 'BYTES');"#Size in rowsROWSCOUNT=$(mysql -D scarprd_self -e "SELECT COUNT(*) AS ROWSCOUNT FROM $TABLE;")ROWSCOUNT=${ROWSCOUNT//ROWSCOUNT/}mysql -D scarprd_self -e "INSERT INTO tables_sizes (table_name,table_size,measurement_type) VALUES ('$TABLE', '$ROWSCOUNT', 'ROWSCOUNT');"mysql -D scarprd_self -e "DELETE FROM tables_sizes WHERE measurement_datetime < TIMESTAMP(DATE_SUB(NOW(), INTERVAL 365 DAY));"done

它预先假设有这个MySQL表

CREATE TABLE `tables_sizes` (`table_name` VARCHAR(128) NOT NULL,`table_size` VARCHAR(25) NOT NULL,`measurement_type` VARCHAR(10) NOT NULL CHECK (measurement_type IN ('BYTES','ROWSCOUNT')),`measurement_datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()) ENGINE=INNODB DEFAULT CHARSET=utf8
小开

这只是供将来参考的注释。所有答案都依赖于I_S.TABLES。例如，如果表中有blob字段，它不会告诉正确的大小。LOB页面存储在外部页面中，因此它们不会计入聚集索引。实际上有一个注意：

对于NDB表，此语句的输出显示适当的值对于AVG_ROW_LENGTH和DATA_LENGTH列，例外不考虑BLOB列。

我发现InnoDB也是如此。

我已经创建了社区Bug相同。


提交答案