[SQL] MySQL 엔진, 데이터베이스, 테이블 용량 확인

 

-- 엔진별 용량 확인
SELECT ENGINE
     , COUNT(1) AS 'TABLES'
     , CONCAT(ROUND(SUM(TABLE_ROWS) / (1024 * 1024), 2), 'M') AS 'TABLE_ROWS_SIZE'
     , CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024 * 1024), 2), 'G') AS 'DATA_SIZE'
     , CONCAT(ROUND(SUM(INDEX_LENGTH) / (1024 * 1024 * 1024), 2), 'G') AS 'IDX_SIZE'
     , CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024 * 1024), 2), 'G') AS 'TOTAL_SIZE'
     , ROUND(SUM(INDEX_LENGTH) / SUM(DATA_LENGTH), 2) AS 'IDX_FRAC'
  FROM INFORMATION_SCHEMA.TABLES
 GROUP BY ENGINE;

-- 데이터베이스별 용량 확인
SELECT TABLE_SCHEMA AS 'DATABASE_NAME'
     , CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024), 2), 'MB') AS 'TOTAL_SIZE(M)'
     , CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024 * 1024), 2), 'G') AS 'TOTAL_SIZE(G)'
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_ROWS IS NOT NULL
 GROUP BY TABLE_SCHEMA;

-- 테이블 총 용량 확인
SELECT COUNT(1) AS 'TABLES'
     , CONCAT(ROUND(SUM(TABLE_ROWS) / (1024 * 1024), 2), 'M') AS 'TABLE_ROWS_SIZE'
     , CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024 * 1024), 2), 'G') AS 'DATA_SIZE'
     , CONCAT(ROUND(SUM(INDEX_LENGTH) / (1024 * 1024 * 1024), 2), 'G') AS 'IDX_SIZE'
     , CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024 * 1024), 2), 'G') AS 'TOTAL_SIZE'
     , ROUND(SUM(INDEX_LENGTH) / SUM(DATA_LENGTH), 2) AS 'IDX_FRAC'
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_ROWS IS NOT NULL;

-- 테이블별 용량 확인
SELECT CONCAT(TABLE_SCHEMA,'.', TABLE_NAME) AS 'TABLE_NAME'
     , CONCAT(ROUND(DATA_LENGTH / (1024 * 1024 * 1024) , 2), 'G') AS 'DATA_SIZE'
     , CONCAT(ROUND(INDEX_LENGTH / (1024 * 1024 * 1024), 2), 'G') AS 'IDX_SIZE'
     , CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024 * 1024), 2), 'G') AS 'TOTAL_SIZE'
     , ROUND(INDEX_LENGTH / DATA_LENGTH, 2) AS 'IDX_FRAC'
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_ROWS IS NOT NULL;