MySQL 查詢資料庫/表磁碟佔用大小 SQL：information_schema 實戰

MySQL 查詢資料庫/表磁碟佔用大小 SQL：information_schema 實戰

利用 information_schema.TABLES 視圖，可快速掌握各庫、各表的資料與索引空間佔用，支援容量規劃、分區決策、清理優化。

1. 查詢所有資料庫總大小 (資料 + 索引)

SELECT
  TABLE_SCHEMA AS `Database`,
  CONCAT(TRUNCATE(SUM(data_length) / 1024 / 1024, 2), ' MB') AS `Data Size`,
  CONCAT(TRUNCATE(SUM(index_length) / 1024 / 1024, 2), ' MB') AS `Index Size`,
  CONCAT(TRUNCATE(SUM(data_length + index_length) / 1024 / 1024, 2), ' MB') AS `Total Size`
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY SUM(data_length + index_length) DESC;

輸出範例：

+------------+-----------+------------+------------+
| Database   | Data Size | Index Size | Total Size |
+------------+-----------+------------+------------+
| app_prod   | 12500.50 MB | 3200.10 MB | 15700.60 MB |
| app_test   |  1200.30 MB |  300.20 MB |  1500.50 MB |
+------------+-----------+------------+------------+

2. 查詢單一資料庫內所有表大小

SELECT
  TABLE_NAME AS `Table`,
  CONCAT(TRUNCATE(data_length / 1024 / 1024, 2), ' MB') AS `Data Size`,
  CONCAT(TRUNCATE(index_length / 1024 / 1024, 2), ' MB') AS `Index Size`,
  CONCAT(TRUNCATE((data_length + index_length) / 1024 / 1024, 2), ' MB') AS `Total Size`,
  TABLE_ROWS AS `Rows`,
  ENGINE AS `Engine`
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app_prod'   -- 替換目標資料庫
GROUP BY TABLE_NAME
ORDER BY data_length DESC;

3. 進階：含碎片率、平均行長度、自增 ID 最大值

SELECT
  TABLE_NAME,
  CONCAT(TRUNCATE(data_length/1024/1024,2), ' MB') AS Data_MB,
  CONCAT(TRUNCATE(index_length/1024/1024,2), ' MB') AS Index_MB,
  CONCAT(TRUNCATE(data_free/1024/1024,2), ' MB') AS Fragment_MB,
  TABLE_ROWS,
  AVG_ROW_LENGTH,
  AUTO_INCREMENT,
  ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app_prod'
ORDER BY data_length DESC;

關鍵指標**：

• data_free：InnoDB 表碎片空間，超過 100MB 或佔比 > 20% 建議 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 整理。
• AVG_ROW_LENGTH：評估行大小，配合 TABLE_ROWS 估算容量增長。
• AUTO_INCREMENT：監控自增 ID 接近上限 (INT 21 億、BIGINT 9 穰) 風險。

4. 快速定位大表 Top 10 (全實例)

SELECT
  TABLE_SCHEMA, TABLE_NAME,
  CONCAT(TRUNCATE((data_length+index_length)/1024/1024,2), ' MB') AS Total_MB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY (data_length+index_length) DESC
LIMIT 10;

5. 監控腳本整合建議 (Prometheus + Grafana)

# 定時任務每 5 分鐘執行，推送至 Pushgateway
mysql -h localhost -u monitor -p'Pass' -B -N -e "
SELECT CONCAT('mysql_table_size_bytes{database=\"',TABLE_SCHEMA,'\",table=\"',TABLE_NAME,'\"} ', (data_length+index_length))
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys');" | curl --data-binary @- https://pushgateway:9091/metrics/job/mysql_table_size

注意事項

• data_length/index_length 為邏輯大小，InnoDB 含未清理的標記刪除空間，實際磁碟檔案可能更大 (含碎片、雙寫緩衝、Redo Log 等)。
• 分區表每個分區為一行，需 SUM() 聚合得到邏輯表總大小。
• TABLE_ROWS 為估算值 (InnoDB 採樣統計)，精確行數請 SELECT COUNT(*)。