查看Mysql数据量大小
查看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)
Read More