[MySQL] 데이터베이스 및 테이블별 용량 (Size by databases and tables) 조회

 

 

---

데이터베이스별, 테이블별, 전체 용량을 아래 쿼리를 통해 확인 할 수 있다.

 

 

1. 데이터베이스별 용량 (Database Size) 조회

SELECT table_schema AS 'DatabaseName',
                ROUND(SUM(data_length+index_length)/1024/1024, 1) AS 'Size(MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY 2 DESC;

 

2. 전체 용량 (Total Size) 조회

SELECT ROUND(SUM(data_length+index_length)/1024/1024, 1) AS 'Used(MB)',
                 ROUND(SUM(data_free)/1024/1024, 1) AS 'Free(MB)'
FROM information_schema.tables;

 

3. 테이블별 용량 (Table Size) 조회

SELECT table_name AS 'TableName',
                 ROUND(SUM(data_length+index_length)/(1024*1024), 2) AS 'All(MB)',
                 ROUND(data_length/(1024*1024), 2) AS 'Data(MB)',
                 ROUND(index_length/(1024*1024), 2) AS 'Index(MB)'
FROM information_schema.tables
GROUP BY table_name
ORDER BY data_length DESC;