10.3.8 InnoDB and MyISAM Index Statistics Collection

10.3.8 InnoDB and MyISAM Index Statistics Collection

스토리지 엔진은 옵티마이저에서 사용하기 위해 테이블에 대한 통계를 수집합니다. 테이블 통계는 value group에 기반하며, 여기서 value group은 동일한 key prefix value를 가진 행들의 집합입니다. 옵티마이저 관점에서 중요한 통계는 평균 value group 크기입니다.

MySQL은 평균 value group 크기를 다음과 같은 방식으로 사용합니다:

• 각 ref 액세스마다 읽어야 할 행 수를 추정하기 위해

• partial join이 얼마나 많은 행을 생성하는지, 즉 다음 형식의 연산으로 생성되는 행 수를 추정하기 위해

1(...) JOIN tbl_name ON tbl_name.key = expr

인덱스에 대한 평균 value group 크기가 증가할수록, 조회당 평균 행 수가 증가하므로 이 두 목적에 대해 인덱스의 유용성은 줄어듭니다. 최적화 목적에 적합한 좋은 인덱스가 되려면, 각 인덱스 value가 테이블 내의 소수의 행만을 대상으로 하는 것이 가장 좋습니다. 특정 인덱스 value가 많은 수의 행을 반환하는 경우, 그 인덱스는 덜 유용해지며 MySQL이 이를 사용할 가능성도 줄어듭니다.

평균 value group 크기는 테이블 카디널리티와 관련이 있으며, 카디널리티는 value group의 개수입니다. SHOW INDEX 구문은 _N/S_를 기반으로 하는 카디널리티 값을 표시하는데, 여기서 _N_은 테이블의 행 수이고 _S_는 평균 value group 크기입니다. 이 비율은 테이블 내 value group의 대략적인 개수를 제공합니다.

<=> 비교 연산자에 기반한 조인의 경우, NULL은 다른 값과 다르게 취급되지 않습니다: NULL <=> NULL이며, 이는 다른 어떤 값에 대해서도 N <=> N인 것과 같습니다 (_N_에 대해).

그러나 = 연산자에 기반한 조인의 경우, NULL은 non-NULL 값과 다릅니다: expr1 = expr2는 expr1 또는 expr2 (또는 둘 다)가 NULL인 경우 참이 아닙니다. 이는 tbl_name.key = expr 형식의 비교에 대한 ref 액세스에 영향을 줍니다. 현재 expr 값이 NULL이면 비교가 참이 될 수 없으므로 MySQL은 테이블에 액세스하지 않습니다.

= 비교의 경우, 테이블에 NULL 값이 얼마나 많은지는 중요하지 않습니다. 최적화 관점에서 관련 있는 값은 non-NULL value group의 평균 크기입니다. 그러나 MySQL은 현재 이 평균 크기를 수집하거나 사용하는 것을 지원하지 않습니다.

InnoDB 및 MyISAM 테이블에 대해서는 각각 innodb_stats_method 및 myisam_stats_method 시스템 변수를 사용하여 테이블 통계 수집을 어느 정도 제어할 수 있습니다. 이 변수들은 다음과 같이 서로 다른 세 가지 값을 가집니다:

• Variable이 nulls_equal로 설정되어 있으면, 모든 NULL 값은 동일한 값으로 취급됩니다 (즉, 모두 하나의 value group을 형성).

  NULL value group 크기가 평균 non-NULL value group 크기보다 훨씬 큰 경우, 이 방법은 평균 value group 크기를 위쪽으로 왜곡합니다. 이는 옵티마이저 관점에서 인덱스가 non-NULL 값을 찾는 조인에 실제보다 덜 유용한 것처럼 보이게 만듭니다. 결과적으로 nulls_equal 방법은 옵티마이저가 ref 액세스에 대해 인덱스를 사용해야 할 때 사용하지 않게 만들 수 있습니다.

• Variable이 nulls_unequal로 설정되면, NULL 값들은 동일하게 간주되지 않습니다. 대신, 각 NULL 값은 크기가 1인 개별 value group을 형성합니다.

  많은 NULL 값이 있는 경우, 이 방법은 평균 value group 크기를 아래쪽으로 왜곡합니다. 평균 non-NULL value group 크기가 큰 경우, 각 NULL 값을 크기 1인 그룹으로 계산하면 옵티마이저가 non-NULL 값을 찾는 조인에 대해 인덱스의 가치를 과대평가하게 됩니다. 그 결과, nulls_unequal 방법은 다른 방법이 더 나을 수 있는 상황에서도 옵티마이저가 이 인덱스를 ref 조회에 사용하게 만들 수 있습니다.

• Variable이 nulls_ignored로 설정되면, NULL 값은 무시됩니다.

  <=>를 사용하는 조인을 =보다 많이 사용하는 경향이 있다면, 비교에서 NULL 값은 특별하지 않으며 하나의 NULL은 다른 NULL과 같습니다. 이 경우, nulls_equal이 적절한 통계 수집 방법입니다.

innodb_stats_method 시스템 변수는 글로벌 값을 가지며, myisam_stats_method 시스템 변수는 글로벌 값과 세션 값 둘 다를 가집니다. 글로벌 값을 설정하면 해당 스토리지 엔진의 테이블에 대한 통계 수집에 영향을 줍니다. 세션 값을 설정하면 현재 클라이언트 커넥션에 대해서만 통계 수집에 영향을 줍니다. 이는 세션 수준의 myisam_stats_method를 설정함으로써 다른 클라이언트에 영향을 주지 않고 특정 테이블의 통계를 원하는 방법으로 재생성하도록 강제할 수 있음을 의미합니다.

MyISAM 테이블 통계를 재생성하려면 다음 방법 중 아무 것이나 사용할 수 있습니다:

• myisamchk --stats_method= method_name --analyze를 실행

• 테이블의 통계가 outdated되도록 테이블을 변경한 다음 (예를 들어, 행을 하나 insert한 후 삭제), myisam_stats_method를 설정하고 ANALYZE TABLE 구문을 실행

innodb_stats_method 및 myisam_stats_method의 사용과 관련된 몇 가지 주의 사항은 다음과 같습니다:

• 앞에서 설명한 것처럼 테이블 통계를 명시적으로 수집하도록 강제할 수 있습니다. 그러나 MySQL은 통계를 자동으로 수집할 수도 있습니다. 예를 들어, 특정 테이블에 대해 구문들을 실행하는 동안 일부 구문이 테이블을 수정하면, MySQL이 통계를 수집할 수 있습니다 (예: 대량 insert 또는 delete, 일부 ALTER TABLE 구문의 경우). 이 경우 통계는 그 시점에 innodb_stats_method 또는 myisam_stats_method가 가진 value를 사용하여 수집됩니다. 따라서 한 방법을 사용해 통계를 수집하더라도, 나중에 테이블의 통계가 자동으로 수집될 때 시스템 변수가 다른 방법으로 설정되어 있으면 다른 방법이 사용됩니다.

• 특정 테이블의 통계가 어떤 방법으로 생성되었는지 알 방법은 없습니다.

• 이 변수들은 InnoDB 및 MyISAM 테이블에만 적용됩니다. 다른 스토리지 엔진은 테이블 통계를 수집하기 위한 단 하나의 방법만 가지고 있습니다. 보통 이 방법은 nulls_equal 방법에 더 가깝습니다.