關鍵欄位:
TABLE_SCHEMA:資料庫名TABLE_NAME:表名ENGINE:存儲引擎TABLE_ROWS:估算行數DATA_LENGTH:數據大小(Bytes)INDEX_LENGTH:索引大小(Bytes)DATA_FREE:碎片空間UPDATE_TIME:最後修改時間
SELECT
CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2), ' MB') AS total_size
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name';
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';
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;
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 |
+-----------+------------+
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 視圖中查看各分區大小innodb_tablespaces 獲取表空間級別資訊