获取MySQL数据库表大小的方法

技术背景

在数据库管理中，了解数据库中各个表的大小是非常重要的。这有助于数据库管理员进行容量规划、性能优化以及资源分配等工作。MySQL 作为一种广泛使用的关系型数据库管理系统，提供了多种方法来获取表的大小信息。

实现步骤

使用 SHOW TABLE STATUS 命令

可以使用以下命令获取指定数据库中所有表的状态信息：

SHOW TABLE STATUS FROM myDatabaseName;

要找出最大的表，可关注 Data_length（数据大小）和 Index_length（索引大小）列，它们以字节为单位显示数据和索引的大小。

使用 information_schema.TABLES 表查询

查询指定表的大小

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";

将 $DB_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;

查询当前所选数据库中所有表的大小并排序

SELECT table_name ,
  round(((data_length + index_length) / 1024 / 1024), 2) as SIZE_MB
FROM information_schema.TABLES
WHERE table_schema = DATABASE() ORDER BY SIZE_MB DESC;

使用 MySQL Workbench

1. 右键单击模式名称，然后点击 “Schema inspector”。
2. 在弹出的窗口中，第一个 “Info” 标签页显示数据库大小的大致估计（以 MB 为单位）。
3. 第二个 “Tables” 标签页显示每个表的数据长度和其他详细信息。

使用命令行工具

从 shell 执行查询

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

将 DB_NAME 替换为数据库名。

使用 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

使用 phpMyAdmin

打开表结构页面，在 “Space usage” 部分可以看到数据、索引和总大小的信息。

核心代码

以下是一个通用的查询指定数据库中所有表大小并按大小降序排序的 SQL 代码：

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC;

最佳实践

• 定期监控表的大小，以便及时发现数据增长异常。
• 对于大表，可以考虑进行分区或优化索引，以提高查询性能。
• 在使用 information_schema.TABLES 时，注意其数据可能存在一定的延迟，尤其是在高并发环境下。

常见问题

数据长度和索引长度之和与磁盘上的实际文件大小不一致

这可能是由于存储引擎的特性、数据碎片或大对象（如 BLOB）的存储方式导致的。对于 InnoDB 存储引擎，可以使用 INNODB_SYS_TABLESPACES 表（MySQL 8.0 为 INNODB_TABLESPACES）来获取更准确的磁盘大小：

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

但需要注意，查询此表可能需要 PROCESS 权限。

SHOW TABLE STATUS 不显示某些表的准确信息

对于某些存储引擎或特殊表，SHOW TABLE STATUS 可能无法提供准确的信息。建议使用 information_schema.TABLES 进行查询。