MySQL 5.6 한글메뉴얼

14.13.16.2 비영구적 옵티마이저 통계 파라미터 구성

MySQL 5.6.6의 시점에서는 옵티 마이저 통계는 기본적으로 영구적이며, innodb_stats_persistent 구성 옵션으로 사용할 수 있습니다. 이 섹션에서는 innodb_stats_persistent=OFF 일 때, 또는 STATS_PERSISTENT=0 에서 개별 테이블을 만들거나 변경 될 때 적용되는 비 영구 옵티 마이저 통계의 구성에 대해 설명합니다. 영구 옵티 마이저 통계 정보는 섹션 14.13.16.1 "영구 옵티 마이저 통계 매개 변수 구성" 을 참조하십시오.

MySQL 쿼리 최적화 프로그램은 인덱스의 상대적인 선택성 에 따라 키 분포에 대한 추정 된 통계 를 사용하여 실행 계획을위한 인덱스를 선택합니다. 특정 작업을 수행하면, InnoDB 는 인덱스의 중요도 를 추정하기 위해 테이블의 각 인덱스에서 임의의 페이지를 샘플링합니다. (이 기술은 랜덤 다이브 라고합니다.) 이러한 작업은 ANALYZE TABLE 문 SHOW TABLE STATUS 문 및 재시작 후 테이블에 처음 액세스가 포함됩니다.

통계 추정치의 품질을 제어하는 (그러면 쿼리 최적화에 대한 정보를 개선하기) 위해 매개 변수 innodb_stats_transient_sample_pages 를 사용하여 추출 된 페이지 수를 변경할 수 있습니다. 샘플링되는 페이지의 기본 수는 8입니다. 이것은 정확한 추정치를 생성하기에 충분하지 않고, 쿼리 최적화 프로그램이 잘못된 인덱스 선택으로 이어질 수 있습니다. 이 기술은 큰 테이블과 조인 에서 사용되는 테이블의 경우에 특히 중요합니다. 이러한 테이블에 대한 불필요한 풀 테이블 스캔 이 성능의 심각한 문제가 될 수 있습니다. 이러한 쿼리 튜닝에 대한 팁은 섹션 8.2.1.20 "풀 테이블 스캔을 방지하는 방법" 을 참조하십시오.

글로벌 매개 변수 innodb_stats_transient_sample_pages 은 런타임에 설정할 수 있습니다.

참고

innodb_stats_persistent=0 인 경우는 innodb_stats_transient_sample_pages 값이 모든 InnoDB 테이블 및 인덱스의 인덱스 샘플링에 영향을 미칩니다. 인덱스의 샘플 크기를 변경하면 다음 잠재적으로 심각한 영향이 발생합니다.

1이나 2 등 작은 값은 중요도의 부정확 한 추정치가 생성 될 수 있습니다.
innodb_stats_transient_sample_pages 값을 크게하면 필요한 디스크 읽기가 늘어날 수 있습니다. 8보다 훨씬 큰 값 (예 : 100)에서는 테이블을 열거 나 SHOW TABLE STATUS 를 실행하는 데 속도가 크게 느려질 수 있습니다.
최적화 프로그램이 인덱스의 선택성 다른 추정치에 따라 매우 다른 쿼리 계획을 선택할 수 있습니다.

SHOW TABLE STATUS 와 SHOW INDEX 와 같은 메타 데이터 문을 실행할 때와 INFORMATION_SCHEMA.TABLES 또는 INFORMATION_SCHEMA.STATISTICS 테이블에 액세스 할 때 통계가 업데이트되도록하려면 문 SET GLOBAL innodb_stats_on_metadata=ON (또는 0 )를 실행합니다.

참고

영구 옵티 마이저 통계가 MySQL 5.6.6에서 기본적으로 활성화되었을 때, innodb_stats_on_metadata 의 기본 설정은 OFF 로 변경되었습니다. 이 변수를 사용하면 다수의 테이블 또는 인덱스를 포함하는 스키마 액세스 속도가 저하되거나 InnoDB 테이블에 대한 쿼리 실행 계획의 안정성이 저하 될 수 있습니다.

--auto-rehash 설정이 온 (디폴트) 상태에서 mysql 클라이언트가 시작되면 모든 InnoDB 테이블이 열리고 관련된 모든 인덱스에 대한 통계를 다시 추정됩니다. mysql 클라이언트의 시작 시간을 개선하기 위해 --disable-auto-rehash 옵션을 사용하여 auto-rehash를 취소 할 수 있습니다. auto-rehash 기능은 대화 형 사용자에 대한 데이터베이스 테이블 및 컬럼 이름의 자동 이름 보완을 활성화합니다.

있는 시스템에서 innodb_stats_transient_sample_pages 의 어떤 값이 가장 잘 작동하더라도이 옵션을 설정하고 그 값을 유지합니다. 과도한 I / O를 필요로하지 않고 데이터베이스의 모든 테이블에 대해 비교적 정확한 견적을 생성하는 값을 선택하십시오. 통계는 ANALYZE TABLE 을 실행할 때 이외의 다양한 시간에 자동으로 다시 계산되므로 인덱스의 샘플 크기를 늘리고, ANALYZE TABLE 을 실행하고 샘플 크기를 다시 줄여도 의미가 없습니다. innodb_stats_transient_sample_pages 큰 값으로 실행되는 ANALYZE 에 의해 계산 된보다 정확한 통계가 나중에 삭제 될 수 있습니다.

작은 테이블은 일반적으로 큰 테이블에 비해 필요한 인덱스 샘플이 적습니다. 데이터베이스에 다수의 큰 테이블이 포함되어있는 경우는 대부분이 작은 테이블 인 경우보다 큰 innodb_stats_transient_sample_pages 값을 사용하는 것을 고려하십시오.