MySQL 快速查詢資料庫大小與資料表最後異動時間

1. 查詢資料庫總大小 (含索引，單位 Byte)

SELECT 
    SUM(DATA_LENGTH) + SUM(INDEX_LENGTH) AS total_bytes,
    ROUND((SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) / 1024 / 1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';

2. 查詢單一資料表大小

SELECT 
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

3. 查詢資料表最後異動時間 (判斷是否棄用)

SELECT 
    TABLE_NAME,
    UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY UPDATE_TIME DESC;

單位換算備忘

• KB = Bytes / 1024
• MB = Bytes / 1,048,576 (1024×1024)
• GB = Bytes / 1,073,741,824

實用技巧

搭配 TABLE_ROWS 欄位可快速評估資料密度；DATA_FREE 可發現碎片空間回收機會。