下面演示**在 MySQL（5.6 ~ 8.0）中计算数据库与数据表“物理尺寸”与“逻辑尺寸”**的常用方法，均基于官方 information_schema & performance_schema 视图，不依赖第三方插件。所有示例可直接在 MySQL Shell / CLI (mysql) 中执行；若用 MariaDB 同样适用（列名相同）。

---

1.基础概念

列	说明
DATA_LENGTH	表数据文件大小（字节）
INDEX_LENGTH	索引占用大小（字节）
DATA_FREE	已分配但未使用的空间（字节，InnoDB “碎片”）
总大小	DATA_LENGTH + INDEX_LENGTH

源自 information_schema.TABLES 视图。

---

2.计算 单张表 大小

SELECT  
  table_schema                                          AS db,
  table_name,
  ROUND(data_length/1024/1024, 2)  AS data_mb,
  ROUND(index_length/1024/1024, 2) AS index_mb,
  ROUND((data_length+index_length)/1024/1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'your_db' AND table_name = 'your_table';

---

3.计算 整个数据库（Schema）大小

SELECT  
  table_schema                                          AS db,
  ROUND(SUM(data_length)/1024/1024, 2)  AS data_mb,
  ROUND(SUM(index_length)/1024/1024, 2) AS index_mb,
  ROUND(SUM(data_length+index_length)/1024/1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'your_db'
GROUP BY table_schema;

---

4.一次列出 所有数据库 大小

SELECT
  table_schema                                          AS db,
  ROUND(SUM(data_length)/1024/1024, 2)  AS data_mb,
  ROUND(SUM(index_length)/1024/1024, 2) AS index_mb,
  ROUND(SUM(data_length+index_length)/1024/1024, 2) AS total_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY total_mb DESC;

跳过系统库：
… WHERE table_schema NOT IN ('mysql','sys','performance_schema','information_schema') …

---

5.建立 视图 便于日常查询

CREATE OR REPLACE VIEW v_db_size AS
SELECT
  table_schema,
  ROUND(SUM(data_length+index_length)/1024/1024,2) AS total_mb
FROM information_schema.tables
GROUP BY table_schema;

以后直接：

SELECT * FROM v_db_size ORDER BY total_mb DESC;

---

6.查询 分区表 各分区大小（8.0+）

SELECT
  table_schema,
  table_name,
  partition_name,
  ROUND(data_length/1024/1024,2) AS data_mb,
  ROUND(index_length/1024/1024,2) AS index_mb
FROM information_schema.partitions
WHERE table_schema = 'your_db' AND table_name = 'your_partitioned_tbl';

---

7.使用 performance_schema（8.0 推荐）

file_summary_by_event_name 统计物理文件读写、字节数：

SELECT
  SUBSTRING_INDEX(event_name,'/',-1) AS file,
  ROUND(SUM(number_of_bytes_read+number_of_bytes_write)/1024/1024,2) AS io_mb
FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE 'wait/io/file/innodb/%'
GROUP BY file
ORDER BY io_mb DESC;

---

8.CLI 快捷命令

• 查看表状态：

  mysql -e "USE your_db; SHOW TABLE STATUS\G"

• MySQL 自带工具：

  mysqlshow --status your_db        # 汇总
  mysqlshow --status your_db your_table

---

9.脚本化监控示例（Bash）

#!/usr/bin/env bash
mysql -Nse "
  SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,2) AS MB
  FROM information_schema.tables
  WHERE table_schema NOT IN ('mysql','sys','performance_schema','information_schema')
  GROUP BY table_schema;
"

可将输出推送到 Prometheus Pushgateway、Zabbix Sender 或记录日志。

---

10.注意事项 & 最佳实践

项	建议
InnoDB innodb_file_per_table=ON	便于单表查看/收缩
长期碎片回收	OPTIMIZE TABLE 或 ALTER TABLE … ENGINE=InnoDB;
监控阈值告警	对数据库或磁盘分区设置使用率告警
备份 & 清理	大体量表分区归档，读写分离

---

📚 官方参考

• MySQL Ref Manual – The information_schema Tables
  https://dev.mysql.com/doc/refman/8.0/en/information-schema.html
• MySQL Ref Manual – SHOW TABLE STATUS
  https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html
• MySQL Ref Manual – performance_schema File Summary Tables
  https://dev.mysql.com/doc/refman/8.0/en/performance-schema-file-summary-tables.html

---

更多详细内容请关注其他相关文章！