MySQL 테이블 용량 확인 및 테이블 개수 조회

 

1. 데이터베이스 테이블 개수 조회

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'DB_NAME';

 

2. 특정 테이블 용량 확인

SELECT table_name AS 'TableName',
	   table_rows,
       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)',
       ROUND(SUM(data_length+index_length)/(1024*1024*1024), 2) AS 'All(GB)',
       ROUND(data_length/(1024*1024*1024), 2) AS 'Data(GB)',
       ROUND(index_length/(1024*1024*1024), 2) AS 'Index(GB)'
  FROM information_schema.tables
 WHERE table_schema = 'DB_NAME' 
     AND table_name = 'TABLE_NAME'
 GROUP BY table_name
 ORDER BY data_length DESC;

 

3. 전체 테이블별 크기 조회

SELECT table_name,
       ROUND((data_length + index_length)/(1024),2) as 'Size(KB)',
       ROUND((data_length + index_length)/(1024*1024),2) as 'Size(MB)',
       ROUND((data_length + index_length)/(1024*1024*1024),2) as 'Size(GB)'
FROM information_schema.tables
WHERE table_schema = 'DB_NAME'
GROUP BY table_name
ORDER BY 2 DESC;