MySQL 테이블을 크기순으로 나열하기
MySQL에서 특정 데이터베이스에 속한 테이블들을 용량 크기 순서대로 확인하고 싶다면 information_schema.TABLES 뷰를 활용하면 간단하게 해결할 수 있습니다. 이 방법을 사용하면 각 테이블의 이름뿐만 아니라 행 수, 데이터 크기, 인덱스 크기까지 한 번에 조회할 수 있어 데이터베이스 용량 관리에 매우 유용합니다.
기본 쿼리 문법
SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "MB Size" FROM information_schema.TABLES WHERE table_schema = "yourDatabaseName" ORDER BY (data_length + index_length) ASC;
쿼리의 각 구성 요소를 살펴보면 다음과 같습니다.
- TABLE_NAME : 테이블의 이름
- table_rows : 테이블에 저장된 대략적인 행(row) 수
- data_length : 실제 데이터가 차지하는 바이트 크기
- index_length : 인덱스가 차지하는 바이트 크기
- MB Size : data_length와 index_length를 합산한 후 MB 단위로 변환한 값 (소수점 둘째 자리까지 반올림)
- ORDER BY ... ASC : 크기가 작은 테이블부터 오름차순으로 정렬
실제 실행 예제
예제 데이터베이스 'test'에 위 쿼리를 적용해 보겠습니다.
mysql> SELECT TABLE_NAME, table_rows, data_length, index_length,
-> round(((data_length + index_length) / 1024 / 1024),2) "MB Size"
-> FROM information_schema.TABLES WHERE table_schema = "test"
-> ORDER BY (data_length + index_length) ASC;
실행 결과
아래는 크기순으로 정렬된 결과의 일부입니다. (전체 240개 행 중 발췌)
+------------------------------------+------------+-------------+--------------+---------+ | TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | MB Size | +------------------------------------+------------+-------------+--------------+---------+ | empinfoview | 0 | 0 | 0 | 0.00 | | lookuptable | 0 | 0 | 0 | 0.00 | | view_student | 0 | 0 | 0 | 0.00 | | customers | 0 | 0 | 1024 | 0.00 | | addingcurrencysymboldemo | 4 | 16384 | 0 | 0.02 | | allrecordswithactive | 6 | 16384 | 0 | 0.02 | | studentdemo | 4 | 16384 | 0 | 0.02 | | constraintdemo | 0 | 16384 | 16384 | 0.03 | | insertignoredemo | 2 | 16384 | 16384 | 0.03 | | student | 2 | 16384 | 32768 | 0.05 | +------------------------------------+------------+-------------+--------------+---------+ 240 rows in set (22.56 sec)
결과를 보면 데이터가 없는 빈 테이블(0바이트)부터 시작해 용량이 점점 커지는 순서로 정렬되어 있는 것을 확인할 수 있습니다. 인덱스가 생성된 테이블은 index_length 값이 더해져 전체 크기가 커진다는 점도 눈여겨볼 만합니다.
큰 테이블부터 내림차순으로 보고 싶다면?
용량이 가장 큰 테이블부터 확인하려면 ORDER BY 절에 DESC를 사용하면 됩니다.
SELECT TABLE_NAME, round(((data_length + index_length) / 1024 / 1024),2) "MB Size" FROM information_schema.TABLES WHERE table_schema = "yourDatabaseName" ORDER BY (data_length + index_length) DESC;
알아두면 좋은 팁
- table_rows 근사치 주의 : InnoDB 엔진을 사용하는 테이블의 경우 table_rows 값은 정확한 값이 아닌 통계 기반의 근사치로 표시될 수 있습니다.
- 단위 변경 : KB 단위로 보려면 1024로 한 번만 나누고, GB 단위로 보려면 1024를 세 번 나누면 됩니다.
- 대안 명령어 : SHOW TABLE STATUS 명령어로도 비슷한 정보를 확인할 수 있지만, 원하는 조건으로 정렬하려면 information_schema를 직접 조회하는 것이 더 유연합니다.