MySQL) OPTIMIZE TABLE Statement

조회쿼리 실행시 40초가 넘는 상황이 생겼다. 쿼리튜닝을 시도하며 끙끙대다가, 아주 간단한 방법으로 해결되었다.

 

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

 

OPTIMIZE TABLE 을 실행한 테이블들은 데이터 이관을 위해 행들이 여러번 지워졌다 삭제되거나, 잦은 변형이 가해진 테이블들이였다. 그래서 그런지 너무 드라마틱하게 성능이 개선되었고(40초 이상 > 0.3초 이하), 사실 그동안 OPTIMIZE TABLE은 쿼리성능개선을 위해 생각하던 옵션이 아니였다. 그래서 이번기회에 OPTIMIZE TABLE에 대해 정리해보려고 한다.

 

https://dev.mysql.com/doc/refman/9.0/en/optimize-table.html 참고

MySQL :: MySQL 9.0 Reference Manual :: 15.7.3.4 OPTIMIZE TABLE Statement

 

 

1. OPTIMIZE TABLE

OPTIMIZE TABLE은 테이블 데이터와 연관된 인덱스 데이터를 물리적으로 재구성하여 저장 공간을 줄이고 테이블에 액세스할 때 I/O 효율성을 향상시킨다. 각 테이블에 대해 수행되는 정확한 변경 사항은 해당 테이블에서 사용된 스토리지 엔진에 따라 다르다. SELECT와 INSERT 권한이 필요하다.

 

다음과 같은 경우에 사용하면 유용하다.

• InnoDB 테이블에서 대규모 데이터 조작 후 : InnoDB 테이블에 대해 많은 삽입, 업데이트 또는 삭제 작업을 수행한 경우에 innodb_file_per_table 옵션이 활성화되어 있으면, 테이블은 독립적인 .ibd 파일을 사용한다. 이때 테이블과 인덱스가 재구성되며, 디스크 공간이 운영 체제에서 재사용될 수 있도록 회수한다.
  
  
• InnoDB 테이블의 FULLTEXT 인덱스에서 데이터 조작 후 : InnoDB 테이블에서 FULLTEXT 인덱스가 있는 열에 대해 대규모 삽입, 수정, 삭제 작업을 한 경우, 먼저 innodb_optimize_fulltext_only=1 옵션을 설정해야 한다. 검색 인덱스의 유지 기간을 적절하게 유지하려면 innodb_ft_num_word_optimize 옵션을 설정하여 인덱스에서 업데이트할 단어 수를 지정하고, 여러 번의 OPTIMIZE TABLE 명령을 실행해 검색 인덱스가 완전히 업데이트될 때까지 작업을 진행해야 한다.
  
  
• MyISAM 또는 ARCHIVE 테이블에서 대규모 삭제 또는 수정 후 : MyISAM 또는 ARCHIVE 테이블에서 대규모 삭제 작업을 하거나 VARCHAR, VARBINARY, BLOB, TEXT 같은 가변 길이 행이 있는 테이블에서 많은 변경 작업을 한 경우. 삭제된 행은 연결 리스트로 유지되며, 이후 삽입 작업에서 이 리스트를 재사용한다. OPTIMIZE TABLE 명령어는 사용하지 않은 공간을 회수하고 데이터 파일의 조각화를 제거하는 데 사용된다. 광범위한 변경 작업 후에는 이 명령어로 테이블을 사용하는 명령어의 성능이 크게 향상될 수 있다.

 

 

2. 작동 방식

 

• InnoDB 테이블: OPTIMIZE TABLE은 내부적으로 ALTER TABLE ... FORCE로 변환되어 테이블을 재구성하고 인덱스 통계를 업데이트하며, 사용되지 않는 공간을 해제한다. OPTIMIZE TABLE은 온라인 DDL을 사용하여 DML 작업과의 충돌을 최소화하고, 테이블 복사 방식이 아닌 "인 플레이스(In-place)" 방식으로 테이블을 재구성한다.
• MyISAM 테이블: 삭제되거나 분리된 행이 있는 경우 테이블을 복구하고, 인덱스 페이지가 정렬되지 않은 경우 이를 재정렬한다. 테이블 통계가 최신이 아니면 통계를 업데이트하여 성능을 향상시킨다.

 

3. 제한 사항 및 기타 고려 사항

 

• NDB Cluster 테이블에 대해서도 최적화 작업이 지원되며, --ndb-optimization-delay를 사용해 성능을 조정할 수 있다.
• 고정 폭 열이 있는 메모리 테이블 및 Disk Data 테이블에서는 OPTIMIZE TABLE이 작동하지 않는다.
• 뷰(View) 에서는 OPTIMIZE TABLE이 지원되지 않는다.
• 파티션된 테이블에서도 최적화 작업을 수행할 수 있다.
• OPTIMIZE TABLE 은 바이너리 로그에 기록되어 복제본에 반영된다. 

 

4. InnoDB

InnoDB는 데이터를 페이지 할당 방식으로 저장하므로, MyISAM 같은 레거시 스토리지 엔진과 같은 방식으로 조각화 문제를 겪지 않는다. 그러나 서버에서 처리할 트랜잭션 작업량에 따라 최적화 실행 여부를 결정한다

• 일정 수준의 조각화는 자연스러운 현상이다. InnoDB는 페이지를 약 93%만 채우며, 업데이트 시 페이지 분할을 방지하기 위해 추가 공간을 남겨둔다.
• 삭제 작업은 페이지에 틈을 남겨 페이지가 기대만큼 채워지지 않을 수 있으며, 이 경우 테이블을 최적화하는 것이 유리할 수 있다.
• 행 업데이트는 주로 동일한 페이지 내에서 데이터를 다시 기록하므로, 충분한 공간이 있을 경우 성능에 영향을 덜 미친다.
• **다중 버전 동시성 제어(MVCC)**를 사용하는 InnoDB는 여러 버전의 데이터를 유지하므로 시간이 지나면서 인덱스에 틈이 생길 수 있다.

 

5. MyISAM

MyISAM 테이블에 대해 OPTIMIZE TABLE은 다음과 같은 작업을 수행한다

1. 테이블에 삭제되거나 분리된 행이 있으면 테이블을 복구한다.
2. 인덱스 페이지가 정렬되지 않은 경우, 이를 정렬한다.
3. 테이블 통계가 최신 상태가 아니고, 복구가 인덱스 정렬을 통해 이루어지지 않은 경우 통계를 업데이트한다.