萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 查看資料庫與表空間佔用大小:information_schema 實戰查詢

核心視圖:information_schema.TABLES

關鍵欄位:

  • TABLE_SCHEMA:資料庫名
  • TABLE_NAME:表名
  • ENGINE:存儲引擎
  • TABLE_ROWS:估算行數
  • DATA_LENGTH:數據大小(Bytes)
  • INDEX_LENGTH:索引大小(Bytes)
  • DATA_FREE:碎片空間
  • UPDATE_TIME:最後修改時間

常用查詢模板

1. 單庫總大小(數據+索引,MB)


SELECT
    CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2), ' MB') AS total_size
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name';

2. 單表大小與行數


SELECT
    TABLE_NAME,
    TABLE_ROWS,
    CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2), ' MB') AS total_size,
    CONCAT(ROUND(DATA_LENGTH / 1024 / 1024, 2), ' MB') AS data_size,
    CONCAT(ROUND(INDEX_LENGTH / 1024 / 1024, 2), ' MB') AS index_size,
    UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name'
  AND TABLE_NAME = 'table_name';

3. 庫內所有表大小排序(Top 20)


SELECT
    TABLE_NAME,
    TABLE_ROWS,
    CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2), ' MB') AS total_size,
    ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 20;

4. 實例:t_carmodelparamvalue 表


mysql> USE information_schema;
mysql> SELECT
    CONCAT(ROUND(SUM(DATA_LENGTH)/1024/1024,2),' MB') AS data_MB,
    CONCAT(ROUND(SUM(INDEX_LENGTH)/1024/1024,2),' MB') AS index_MB
FROM TABLES
WHERE TABLE_SCHEMA='dbwww58com_kuchecarlib'
  AND TABLE_NAME='t_carmodelparamvalue';
+-----------+------------+
| data_MB   | index_MB   |
+-----------+------------+
| 158.67 MB | 224.86 MB  |
+-----------+------------+

進階:碎片率評估與 OPTIMIZE 判斷


SELECT
    TABLE_NAME,
    CONCAT(ROUND(DATA_FREE/1024/1024,2),' MB') AS free_MB,
    ROUND(DATA_FREE/(DATA_LENGTH+INDEX_LENGTH)*100,2) AS frag_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='db_name'
  AND DATA_FREE > 0
  AND ENGINE='InnoDB'
ORDER BY frag_pct DESC;

frag_pct > 20% 且表大於 1GB 時,考慮 OPTIMIZE TABLE 或 pt-online-schema-change 整理碎片。

注意事項

  • TABLE_ROWS 為估算值(InnoDB 抽樣統計),精確行數需 SELECT COUNT(*)
  • 分區表在 PARTITIONS 視圖中查看各分區大小
  • 5.7+ 可查 innodb_tablespaces 獲取表空間級別資訊
copyright © 萬盛學電腦網 all rights reserved