[MySQL] Optimize Table & Analyze Table

■ ANALYZE TABLE Statement

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]

    TABLE tbl_name [, tbl_name] ...

 

ANALYZE TABLE은 키 분포 분석을 수행하고 명명된 테이블에 대한 분포를 저장합니다. MyISAM 테이블의 경우, 이 명령문은 myisamchk --analyze를 사용하는 것과 같습니다.

 

이 명령문에는 테이블에 대한 SELECT 및 INSERT 권한이 필요합니다.

 

ANALYZE TABLE은 InnoDB, NDB 및 MyISAM 테이블에서 작동합니다. 뷰에서는 작동하지 않습니다.

 

ANALYZE TABLE은 파티션된 테이블에 대해 지원되며 ALTER TABLE ... ANALYZE PARTITION을 사용하여 하나, 또는 그 이상의 특정 파티션을 분석할 수 있습니다.

 

분석하는 동안 InnoDB 및 MyISAM에 대한 읽기 잠금으로 테이블이 잠깁니다.

 

ANALYZE TABLE은 테이블 정의 캐시(table definition cache)에서 테이블을 제거하며 플러시 잠금이 필요합니다. 테이블을 사용하면서 오래 실행중인 명령문 또는 트랜잭션이 있는 경우 후속 명령문 및 트랜잭션은 플러시 잠금이 해제되기 전에 해당 조작이 완료 될 때까지 기다려야합니다. ANALYZE TABLE 자체는 일반적으로 빠르게 완료되기 때문에 동일한 테이블과 관련된 지연된 트랜잭션 또는 명령문이 남아있는 플러시 잠금으로 인한 것임을 알 수 없습니다.

 

기본적으로 서버는 ANALYZE TABLE 문을 바이너리 로그에 작성하여 복제본에 복제합니다. 로깅을 억제하려면 선택적 NO_WRITE_TO_BINLOG 키워드 또는 별명 LOCAL을 지정하십시오.

+ ANALYZE TABLE 출력

+ 키 분포 분석(Key Distribution Analysis)

+ 다른 고려 사항

 

▶︎ ANALYZE TABLE 출력

ANALYZE TABLE은 다음 표에 표시된 열이있는 결과 집합을 반환합니다.

컬럼	값
Table	테이블 이름
Op	항상 분석
Msg_type	상태(status), 에러(error), 정보(info), 참고사항(note), 경고(warning)
Msg_text	정보성 메세지

 

 

▶︎ 키 분포 분석

마지막 키 분산 분석 이후 테이블이 변경되지 않은 경우 테이블은 다시 분석되지 않습니다.

 

MySQL은 저장된 키 배포를 사용하여 상수 이외의 컬럼이나 다른항목의 조인에서 테이블 조인 순서를 결정합니다. 또한 쿼리 내의 특정 테이블에 사용할 인덱스를 결정할 때 키 배포를 사용할 수 있습니다.

 

저장된 키 분산 카디널리티를 확인하려면 SHOW INDEX 문 또는 INFORMATION_SCHEMA STATISTICS 테이블을 사용합니다.

 

InnoDB 테이블의 경우 ANALYZE TABLE은 각 인덱스 트리에서 임의의 다이빙을 수행하고 이에 따라 인덱스 카디널리티 추정치를 업데이트하여 인덱스 카디널리티를 결정합니다. 이는 추정치 일 뿐이므로 ANALYZE TABLE을 반복 실행하면 다른 숫자가 생성 될 수 있습니다. 이렇게하면 ANALYZE TABLE이 InnoDB 테이블에서 빠르지 만 모든 행을 고려하지 않기 때문에 100% 정확하지는 않습니다.

 

서버 파라미터인 innodb_stats_persistent를 활성화하면 ANALYZE TABLE에 의해 수집된 통계를 보다 정확하고 안정적으로 만들 수 있습니다. innodb_stats_persistent를 사용하는 경우 통계가 정기적으로 다시 계산되지 않으므로 (예 : 서버를 다시 시작한 후) 인덱스 컬럼 데이터를 크게 변경한 후 ANALYZE TABLE을 실행하는 것이 중요합니다.

 

innodb_stats_persistent가 사용 가능한 경우 innodb_stats_persistent_sample_pages 시스템 변수를 수정하여 임의의 샘플링 페이지 수를 변경할 수 있습니다. innodb_stats_persistent가 비활성화 된 경우 대신 innodb_stats_transient_sample_pages를 수정합니다.

 

MySQL은 조인 최적화에서 인덱스 카디널리티를 사용합니다. 참고로 이 값은 추정치입니다. 즉 정확한 값은 아닙니다. 조인이 올바른 방식으로 최적화되지 않은 경우 ANALYZE TABLE을 실행합니다. ANALYZE TABLE이 특정 테이블에 충분한 값을 생성하지 않는 경우, 쿼리에 FORCE INDEX를 사용하여 특정 인덱스를 강제로 사용하거나 max_seeks_for_key 시스템 변수를 설정하여 MySQL이 테이블보다 인덱스 조회를 선호하도록 할 수 있습니다.

 

▶︎ 다른 고려 사항

ANALYZE TABLE은 INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS 테이블에서 테이블 통계를 지우고 STATS_INITIALIZED 컬럼을 초기화되지 않음으로 설정합니다. 다음에 테이블에 액세스 할 때 통계가 다시 수집됩니다.

 

■ 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, VARBINAL, BLOB 또는 TEXT 열이 있는 테이블)이 있는 MyISAM 또는 ARCHIVE 테이블을 많이 변경한 후 선택합니다. 삭제된 행은 연결된 목록에서 유지 관리되며 후속 INSERT 작업은 이전행 위치를 재사용합니다. OPTIMIZE TABLE을 사용하여 사용되지 않은 공간을 회수하고 데이터 파일을 조각 모음 할 수 있습니다. 테이블을 광범위하게 변경한 후 이 명령문은 테이블을 사용하는 명령문의 성능을 향상시킬 수도 있습니다.

 

이 명령문에는 테이블에 대한 SELECT 및 INSERT 권한이 필요합니다.

 

OPTIMIZE TABLE은 InnoDB, MyISAM 및 ARCHIVE 테이블에서 작동합니다. OPTIMIZE TABLE은 메모리 내 NDB 테이블의 동적 컬럼에도 지원됩니다. 인 메모리 테이블의 고정 너비 컬럼에는 작동하지 않으며 디스크 데이터 테이블에는 작동하지 않습니다. NDB 클러스터 테이블에서 OPTIMIZE의 성능은 --ndb-optimization-delay를 사용하여 조정할 수 있습니다. --ndb-optimization-delay는 OPTIMIZE TABLE에 의해 행 일괄 처리 사이의 대기 시간을 제어합니다.

 

NDB 클러스터 테이블의 경우 OPTIMIZE 조작을 수행하는 SQL 스레드를 종료 (예 :)하여 OPTIMIZE TABLE을 인터럽트 할 수 있습니다.

 

기본적으로 OPTIMIZE TABLE은 다른 스토리지 엔진을 사용하여 작성된 테이블에 대해 작동하지 않으며이 지원 부족을 나타내는 결과를 리턴합니다. --skip-new 옵션으로 mysqld를 시작하여 다른 스토리지 엔진에 대해 OPTIMIZE TABLE이 작동하도록 할 수 있습니다. 이 경우 OPTIMIZE TABLE은 ALTER TABLE에 맵핑됩니다.

 

이 문장은 뷰에서는 작동하지 않습니다.

 

파티션된 테이블에 대해 OPTIMIZE TABLE이 지원됩니다.

 

기본적으로 서버는 OPTIMIZE TABLE 문을 바이너리 로그에 작성하여 복제본에 복제합니다. 로깅을 억제하려면 선택적 NO_WRITE_TO_BINLOG 키워드 또는 별명 LOCAL을 지정합니다.

+ OPTIMIZE TABLE 출력

+ InnoDB 세부 사항

+ MyISAM 세부 사항

+ 다른 고려 사항

 

▶︎ OPTIMIZE TABLE 출력

OPTIMIZE TABLE은 다음 표에 표시된 컬럼이 있는 결과 집합을 반환합니다.

컬럼	값
Table	테이블이름
Op	항상 최적화
Msg_type	상태(status), 에러(error), 정보(info), 참고사항(note), 경고(warning)
Msg_text	정보성 메세지

OPTIMIZE TABLE 테이블은 테이블 통계를 이전 파일에서 새로 작성된 파일로 복사하는 동안 발생하는 모든 오류를 포착하고 발생시킵니다. 예를 들어. .frm, .MYD 또는 .MYI 파일 소유자의 사용자 ID가 mysqld 프로세스의 사용자 ID와 다른 경우 OPTIMIZE TABLE은 root 유저에서 mysqld를 시작하지 않으면 "cannot change ownership of the file(파일의 소유권을 변경할 수 없습니다)"오류를 생성합니다.

 

▶︎ InnoDB 세부 사항

InnoDB 테이블의 경우 OPTIMIZE TABLE은 ALTER TABLE ... FORCE에 매핑됩니다. 이 테이블은 인덱스 통계를 업데이트하고 클러스터형 인덱스에서 사용되지 않은 공간을 확보하기 위해 테이블을 다시 작성합니다. 다음과 같이 InnoDB 테이블에서 OPTIMIZE TABLE을 실행할 때 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은 일반 및 파티션된 InnoDB 테이블에 온라인 DDL을 사용하므로 동시 DML 작업의 다운 타임을 줄입니다. OPTIMIZE TABLE에 의해 트리거되고 ALTER TABLE ... FORCE에 의해 덮개 아래에서 수행된 테이블 재 구축이 완료됩니다. 배타적 테이블 잠금은 작업의 준비 단계 및 커밋 단계 중 잠시만 수행됩니다. 준비 단계 동안 메타 데이터가 업데이트되고 중간 테이블이 생성됩니다. 커밋 단계 동안 테이블 메타 데이터 변경 사항이 커밋됩니다.

 

OPTIMIZE TABLE은 다음 조건에서 테이블 복사 방법을 사용하여 테이블을 재 빌드합니다.

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

+ --skip-new 옵션으로 서버를 시작한 경우

 

온라인 DDL을 사용하는 OPTIMIZE TABLE은 FULLTEXT 인덱스가 포함 된 InnoDB 테이블에 지원되지 않습니다. 대신 테이블 복사 방법이 사용됩니다.

 

InnoDB는 페이지 할당 방법을 사용하여 데이터를 저장하며 레거시 스토리지 엔진 (예 : MyISAM)과 같은 방식으로 조각화가 발생하지 않습니다. 최적화를 실행할지 여부를 고려할 때 서버가 처리 할 트랜잭션의 워크로드를 고려합니다.

+ 일정 수준의 조각화가 발생합니다. InnoDB는 페이지를 분할하지 않고도 업데이트를 위한 공간을 확보하기 위해 페이지를 93%만 사용합니다.

+ 삭제 작업을 수행하면 페이지가 원하는 것보다 덜 채워지는 간격이 발생할 수 있으므로 테이블을 최적화할 수 있습니다.

+ 행 업데이트는 일반적으로 충분한 공간이 사용 가능한 경우 데이터 유형 및 행 형식에 따라 동일한 페이지 내에서 데이터를 다시 작성합니다.

+ InnoDB는 MVCC 메커니즘을 통해 동일한 데이터의 여러 버전을 유지하므로 높은 동시성 워크로드는 시간이 지남에 따라 인덱스에 차이가 생길 수 있습니다.

 

▶︎ MyISAM 세부 사항

MyISAM 테이블의 경우 OPTIMIZE TABLE은 다음과 같이 작동합니다.

1. 테이블이 행을 삭제하거나 분할한 경우 테이블을 복구합니다.

2. 색인 페이지가 정렬되지 않은 경우 정렬합니다.

3. 테이블 통계가 최신 상태가 아니고 (인덱스를 정렬하여 복구를 수행 할 수없는 경우) 업데이트합니다.

 

▶︎ 다른 고려 사항

OPTIMIZE TABLE은 일반 및 파티션된 InnoDB 테이블에 대해 온라인으로 수행됩니다. 그렇지 않으면 MySQL은 OPTIMIZE TABLE이 실행되는 동안 테이블을 잠급니다.

OPTIMIZE TABLE은 POINT 열의 공간 인덱스와 같은 R- 트리 인덱스를 정렬하지 않습니다.