OPTIMIZE TABLE Statement

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

- OPTIMIZE TABLE은 테이블 데이터 및 관련 인덱스 데이터의 물리적 저장소를 재구성하여 저장 공간을 줄이고 테이블에 액세스할때 I/O 효율성이 향상된다. 테이블의 스토리지 엔진에 따라 테이블의 변경사항이 달라진다. 

- OPTIMIZE TABLE은 테이블 유형에 따라 다음과 같은 경우에 사용한다. 

  - innodb_file_per_table 옵션이 활성화된 상태에서 자체 .ibd 파일이 있는 InnoDB 테이블에서

    대량 삽입, 업데이트 또는 삭제작업을 수행한 후 테이블과 인덱스가 재구성되고 운영 체제에서 사용하기 위해

    디스크 공간을 재확보 할 수 있다.

  - InnoDB 테이블의 Fulltext 인덱스의 일부인 컬럼에 대량의 삽입, 업데이트 또는 삭제 작업을 수행한 후

    innodb_optimize_fulltext_only=1 옵션을 설정한다. 색인 유지 보수 기간을 적절한 시간으로 유지하기 위해

    검색 인덱스에서 업데이트하는 단어 개수를 지정하는 innodb_ft_num_word_optimize 옵션을 설정하고

    검색 인덱스가 완전히 업데이트 될때까지 OPTIMIZE TABLE문을 순차적으로 실행한다.

  - MyISAM 또는 ARCHIVE 테이블의 큰 부분을 삭제하거나 가변 길이 행

    (VARCHAR, VARBINARY, BLOB 또는 TEXT 컬럼이 있는 테이블)이 있는 MyISAM 또는 ARCHIVE 테이블을

    많이 변경한후 삭제된 행은 연결된 목록에서 유지 관리되고 후속 INSERT 작업은 이전 행 위치를 재사용한다. 

    OPTIMIZE TABLE 을 사용하여 사용되지 않는 공간을 회수하고 데이터 파일의 조각 모음을 만들수 있다.

    테이블에 대한 광범위한 변경이 있을 경우 OPTIMIZE TABLE 문을 실행하여 성능을 향상시킬수 도 있다. 

- OPTIMIZE TABLE 은 테이블에 대한 SELECT, INSERT 권한이 필요하다.

- OPTIMIZE TABLE 은 InnoDB, MyISAM 및 ARCHIVE 테이블에서 동작한다. 

  OPTIMIZE TABLE 은 in-memory NDB 테이블의 동적 컬럼도 지원한다. in-memory 테이블의 고정 너비 컬럼에는

  작동하지 않으며 디스크 데이터 테이블에서도 작동하지 않는다. NDB 클러스터 테이블의 최적화 성능은

  최적화 테이블을 통해 행 처리 배치 간에 대기하는 시간을 제어하는  --ndb-optimization-delay 을 사용하여

  조정할 수 있다. 

- NDB 클러스터 테이블의 경우 OPTIMIZE TABLE은 최적화 작업을 수행하는 SQL Thread 를 Kill 해서 중단할 수 있다.

  기본적으로 OPTIMIZE TABLE은 다른 저장소 엔진을 사용하여 만든 테이블에 대해 작동하지 않으며 이러한 지원이

  부족하다는 결과를 반환한다. -skip-new 옵션으로 mysqld를 시작하여 다른 스토리지 엔진에 OPTIMIZE TABLE 작업을

  수행할 수 있다. 이경우 OPTIMIZE TABLE은 ALTER TABLE에 매핑된다. 

- OPTIMIZE TABLE 은 뷰에서는 작동하지 않는다. 

- OPTIMIZE TABLE 은 파티션 테이블을 지원한다. 

- OPTIMIZE TABLE 은 바이너리 로그에 기록되어 복제본에 반영된다. 

 

OPTIMIZE TABLE Output

- OPTIMIZE TABLE 은 다음표에 표시된 컬럼로 결과 집합을 반환한다. 

Column	Value
Table	테이블 이름
Op	항상 최작화
Msg_type	상태, 오류, 정보, 참고 또는 경고
Msg_text	정보 메시지

- OPTIMIZE TABLE은 테이블을 최적화하여 이전 파일에서 새로 생성된 파일에 테이블 통계를 복사하는 동안 발생하는 오류를 예외처리한다. 예를 들어 .MYD 또는 .MYI 파일의 소유자 사용자ID와 mysqld 프로세스의 사용자ID가 다른 경우 OPTIMIZE TABLE은 root 사용자로 mysqld를 실행하지 않는한 "파일 소유권을 변경할 수 없음"오류를 생성한다.

 

InnoDB 세부 정보 

- 테이블의 경우 OPTIMIZE TABLE은 TABLE ALTER ... FORCE 에 매핑된다. 

  인덱스 통계를 업데이트하고 클러스터된 인덱스에서 사용하지 않은 공간을 해제한다. 

- OPTIMIZE TABLE을 테이블에서 실행할때 다음과 같이 출력된다. 

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

- OPTIMIZE TABLE은 일반, 파티션 테이블을 OLINE DDL을 사용여 동시 DML 작업에 대한 가동 중지시간을 줄인다. 

  OPTIMIZE TABLE로 트리거되는 테이블 재구축이 완료된다. exclusive table lock은 준비 단계와 작업의 commit 단계에서만 간단히 수행된다. 준비 단계에서 마타데이터가 업데이트되고 중간 테이블이 만들어진다. commit 단계에서 테이블 메타데이터 변경이 commit 된다. 

- OPTIMIZE TABLE은 다음 조건에서 테이블을 copy 방식으로 테이블을 다시 빌드한다. 

  - old_alter_table 시스템 변수가 활성화된 경우

  - 서버 시작시 --skip-new 옵션이 적용된경우 

- OPTIMIZE TABLE은 Full Text Index가 포함된 테이블은 Online DDL을 지원하지 않고 테이블 copy 방식이 사용된다. 

- InnoDB는 페이지 할당 방법을 사용하여 데이터를 저장하며 레거시 스토리지 엔진과 동일한 방식으로 조각화가 발생하지 않는다. 

- OPTIMIZE TABLE을 실행할지 여부를 고려할때 서버가 처리할것으로 예상되는 트랜잭션 워크로드를 고려한다. 

  - 일정 수준의 조각화가 예상되어 page 를 93%까지 채워 page 를 분할하지 않고도 update 할수 있는 공간을 남겨둔다.

  - delete 작업은 page를 원하는것보다 덜 채워 간격을 남길수 있으므로 OPTIMIZE TABLE을 수행하는것이 좋다. 

  - 행에 대한 update 는 일반적으로 충분한 공간을 사용할 수 있는 경우 data type, row format에 따라 동일한 page 내에서 데이터를 다시 작성한다. 

   - 동시성 워크로드가 많기 때문에 MVCC 메커니즘을 통해 동일한 데이터의 여러버전을 유지하므로 시간이 지남에 따라 인덱스의 간격 생길수 있다. 

 

MyISAM 세부 정보 

- MyISAM 테이블의 경우 OPTIMIZE TABLE이 다음과 같이 작동한다.

  - 테이블이 삭제되거나 행을 분할할 경우 테이블을 복구한다. 

  - 인덱스 페이지가 정렬되지 않은 경우 정렬한다.

  - 테이블의 통계가 최시 상태이고 인덱스를 정렬하여 복수를 수행할 수 없는 경우 업데이트한다. 

 

 

참고

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html