查看Mysql数据量大小

Paper-Dragon2023/8/13...大约 2 分钟

查看Mysql数据量大小

查询数据库大小

以MB为单位统计，查询当前全部数据库的数据量大小。

为什么（data_length：数据大小） + （index_length：索引大小）是数据大小呢？

（data_length + index_length）更准确地应该被称为“表的物理存储空间总大小”，而不仅仅是“数据大小”。

一个表的“总物理大小” = 主数据存储的大小 + 所有辅助索引的大小 + 一些内部开销

而这个公式正好对应： data_length (主数据/聚簇索引) + index_length (次要索引)

SELECT 
    table_schema AS `DataBaseName`,
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS `Data Size (MB)`,
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS `Index Size (MB)`,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `DataBase Size (MB)`
FROM 
    information_schema.TABLES
GROUP BY 
    table_schema;

ROUND的作用：ROUND(数字, 2) 函数将这个很长的数字四舍五入到小数点后两位
SUM 的作用：对于分好组的每一行数据（例如 db001 组里有2行），SUM(data_length) 会把这个组里所有行的 data_length 值加起来。

结果

mysql> SELECT 
    ->     table_schema AS `DataBaseName`,
    ->     ROUND(SUM(data_length) / 1024 / 1024, 2) AS `Data Size (MB)`,
    ->     ROUND(SUM(index_length) / 1024 / 1024, 2) AS `Index Size (MB)`,
    ->     ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `DataBase Size (MB)`
    -> FROM 
    ->     information_schema.TABLES
    -> GROUP BY 
    ->     table_schema;
+--------------------+----------------+-----------------+--------------------+
| DataBaseName       | Data Size (MB) | Index Size (MB) | DataBase Size (MB) |
+--------------------+----------------+-----------------+--------------------+
| db001              |         931.20 |         1672.05 |            2603.25 |
| information_schema |           0.00 |            0.00 |               0.00 |
| mysql              |           7.55 |            0.33 |               7.88 |
| performance_schema |           0.00 |            0.00 |               0.00 |
| sys                |           0.02 |            0.00 |               0.02 |
+--------------------+----------------+-----------------+--------------------+
5 rows in set (0.01 sec)

查询数据表大小

使用这个命令要先修改数据库名字

SELECT 
    table_name AS 'Table Name',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Total Size (MB)',
    ROUND(data_length / 1024 / 1024, 2) AS 'Data Size (MB)',
    ROUND(index_length / 1024 / 1024, 2) AS 'Index Size (MB)',
    table_rows AS 'Estimated Rows'
FROM 
    information_schema.TABLES
WHERE 
    table_schema = 'db001' -- Replace with your database name
ORDER BY 
    (data_length + index_length) DESC;

执行结果

mysql> SELECT 
    ->     table_name AS 'Table Name',
    ->     ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Total Size (MB)',
    ->     ROUND(data_length / 1024 / 1024, 2) AS 'Data Size (MB)',
    ->     ROUND(index_length / 1024 / 1024, 2) AS 'Index Size (MB)',
    ->     table_rows AS 'Estimated Rows'
    -> FROM 
    ->     information_schema.TABLES
    -> WHERE 
    ->     table_schema = 'db001' -- Replace with your database name
    -> ORDER BY 
    ->     (data_length + index_length) DESC;
+------------------------+-----------------+----------------+-----------------+----------------+
| Table Name             | Total Size (MB) | Data Size (MB) | Index Size (MB) | Estimated Rows |
+------------------------+-----------------+----------------+-----------------+----------------+
| item                   |         2537.09 |         904.02 |         1633.08 |        6345883 |
| item_backup            |           36.64 |          16.55 |           20.09 |         132004 |
| item_download          |           29.09 |          10.52 |           18.58 |         153187 |
| item_export            |            0.08 |           0.02 |            0.06 |             79 |
| device                 |            0.06 |           0.02 |            0.05 |              7 |
| item_file              |            0.06 |           0.02 |            0.05 |             21 |
| setting                |            0.06 |           0.02 |            0.05 |              0 |
| device_config          |            0.05 |           0.02 |            0.03 |              5 |
| project                |            0.05 |           0.02 |            0.03 |              6 |
| device_config_projects |            0.03 |           0.02 |            0.02 |              6 |
| user                   |            0.03 |           0.02 |            0.02 |              2 |
+------------------------+-----------------+----------------+-----------------+----------------+
11 rows in set (0.00 sec)

更新日志

2025/9/3 03:07

查看所有更新日志

de739-重构查看Mysql数据量大小.md于 2025/9/3
699fa-format index于 2025/9/3
cff30-更新和修复安全问题于 2023/10/17
392a5-Create The Worid!于 2023/8/13