MySQL information_schema 查詢表行數、空間佔用與元數據：實用 SQL 片段合集

核心表：TABLES

`information_schema.TABLES` 包含所有表的統計元數據 (近似值，非實時精確)。

1. 庫級總空間佔用

-- 所有庫總數據大小 (MB)
SELECT CONCAT(ROUND(SUM(DATA_LENGTH)/1024/1024, 2), ' MB') AS total_data_size
FROM information_schema.TABLES;

-- 指定庫總數據大小
SELECT CONCAT(ROUND(SUM(DATA_LENGTH)/1024/1024, 2), ' MB') AS db_data_size
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_db_name';

2. 表級詳情：行數、數據大小、索引大小、總大小

SELECT 
    TABLE_SCHEMA AS `Database`,
    TABLE_NAME AS `Table`,
    TABLE_ROWS AS `Rows (Est.)`,
    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)`,
    ROUND(DATA_FREE/1024/1024, 2) AS `Fragment (MB)`,
    ENGINE,
    TABLE_COLLATION,
    CREATE_TIME,
    UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db_name'
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC;

3. 找出最大的 10 張表

SELECT TABLE_SCHEMA, TABLE_NAME, 
       ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2) AS `Size_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;

4. 碎片率分析 (優化候選)

SELECT TABLE_SCHEMA, TABLE_NAME,
       ROUND(DATA_FREE/1024/1024, 2) AS `Free_MB`,
       ROUND(DATA_FREE/(DATA_LENGTH+INDEX_LENGTH)*100, 2) AS `Frag_%`
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db_name'
  AND DATA_FREE > 100*1024*1024  -- 碎片 > 100MB
  AND ENGINE = 'InnoDB'
ORDER BY DATA_FREE DESC;
-- Frag_% > 20% 考慮 OPTIMIZE TABLE / ALTER TABLE ... ENGINE=InnoDB

注意事項

• TABLE_ROWS 是估算值 (InnoDB 抽樣統計)，精確計數需 `SELECT COUNT(*)`。
• DATA_LENGTH/INDEX_LENGTH 為頁面分配空間，含碎片，非緊湊數據大小。
• 5.7+ 推薦使用 `innodb_table_stats` / `innodb_index_stats`** 持久統計信息表，更準確。
• 權限： 需 `PROCESS` 或 `SELECT ON information_schema.*` 權限。

#!/bin/bash
# check_table_size.sh
mysql -uroot -p'pass' -e "
SELECT TABLE_SCHEMA, TABLE_NAME, ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) AS Size_MB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='prod_db' ORDER BY Size_MB DESC LIMIT 20;
" | mail -s "Daily Top 20 Tables Size" dba@company.com