MySQL 實戰腳本：統計指定 Schema 所有表的資料與索引大小

應用場景

DBA 需快速掌握特定資料庫（Schema）下所有表的儲存佔用，包含資料大小、索引大小、總大小與行數，支援單表查詢與全庫掃描兩種模式。

核心 SQL 查詢

SELECT 
  table_schema,
  table_name,
  table_rows,
  ROUND(SUM(data_length + index_length) / 1024 / 1024) AS total_MB,
  ROUND(SUM(data_length) / 1024 / 1024) AS data_MB,
  ROUND(SUM(index_length) / 1024 / 1024) AS index_MB
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema = 'your_schema'
  [AND table_name = 'your_table']  -- 可選：單表模式
GROUP BY table_schema, table_name, table_rows;

完整 Shell 腳本

#!/bin/sh
# tablesize.sh
# 作者: ocpyang@126.com

# 參數檢查
if [ $# -gt 2 ]; then
  echo "Usage: $0 schema_name [table_name]"
  exit 1
fi

source /usr/local/mysql/scripts/mysql_env.ini
logfile=tmp.$(date +%Y%m%d%H%M%S).txt

SCHEMA_NAME=$1
TABLE_NAME=$2

# 動態建構 SQL
if [ $# -eq 2 ]; then
  SQL_CMD="SELECT table_schema, table_name, table_rows,
    ROUND(SUM(data_length+index_length)/1024/1024) AS total_MB,
    ROUND(SUM(data_length)/1024/1024) AS data_MB,
    ROUND(SUM(index_length)/1024/1024) AS index_MB
    FROM information_schema.tables 
    WHERE table_type='BASE TABLE' 
      AND table_schema='${SCHEMA_NAME}'
      AND table_name='${TABLE_NAME}'
    GROUP BY table_schema, table_name, table_rows;"
else
  SQL_CMD="SELECT table_schema, table_name, table_rows,
    ROUND(SUM(data_length+index_length)/1024/1024) AS total_MB,
    ROUND(SUM(data_length)/1024/1024) AS data_MB,
    ROUND(SUM(index_length)/1024/1024) AS index_MB
    FROM information_schema.tables 
    WHERE table_type='BASE TABLE' 
      AND table_schema='${SCHEMA_NAME}'
    GROUP BY table_schema, table_name, table_rows;"
fi

# 驗證 Schema 存在
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} \
  -e "SELECT * FROM information_schema.schemata WHERE schema_name='${SCHEMA_NAME}';" > ${logfile}

if [ -e "${logfile}" ] && [ ! -s "${logfile}" ]; then
  echo "Schema '${SCHEMA_NAME}' 不存在，請確認資料庫名稱"
  rm -f ${logfile}
  exit 1
fi

echo "查詢結果："
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e "${SQL_CMD}"
rm -f ${logfile}

關鍵欄位說明

進階應用

• 加入 data_free 監控碎片率：ROUND(data_free/1024/1024,2) AS frag_MB
• 結合 engine 欄位分引擎統計
• 定時寫入監控表，趨勢分析成長曲線
• 設定閾值告警（如單表超 100GB）

注意事項

• InnoDB table_rows 為統計估算，非精確值
• 分區表會有多行記錄，SUM 可正確合併
• 需具備 SELECT 權限於 information_schema