MySQL5.6 InnoDB와 MyISAM 인덱스 통계 수집

8.3.7 InnoDB와 MyISAM 인덱스 통계 수집

스토리지 엔진 최적화에 의해 사용되는 테이블에 대한 통계를 수집합니다. 테이블 통계 값 그룹에 근거하지만, 여기에서 값 그룹은 동일한 키 접두어 값을 가진 행의 집합입니다. 최적화의 목적에서 중요한 통계는 평균 그룹 크기입니다.

MySQL은 평균 그룹 크기를 다음과 같이 사용합니다.

각 ref 액세스마다 읽어야하는 행 수를 추정하기
부분 결합으로 생성되는 행 수, 즉이 형식의 조작으로 생성되는 행 수를 추정하기 :
```
 (...) JOIN tbl_name ON tbl_name . key = expr
```

인덱스의 평균 값 그룹 크기가 많을수록 조회 당 평균 행 수가 늘어나 기 때문에 그 두 가지 목적으로 인덱스가 도움이되지 않습니다. 인덱스가 최적화의 목적에 도움이되도록하려면 각 인덱스 값으로 타겟 테이블의 행을 적게하는 것이 가장 좋습니다. 지정된 인덱스 값이 많은 행을 생성하는 경우, 그 인덱스는별로 도움이되지 않고, MySQL이 그것을 사용할 가능성은 적습니다.

평균 그룹 크기 값 그룹 수인 테이블 중요도와 관련이 있습니다. SHOW INDEX 명령문은 N/S 에 따라 중요도 값을 표시합니다. 여기서 N 은 테이블 내의 행수에서 S 는 평균 값 그룹 크기입니다. 그 비율에서 테이블 값 그룹의 개수를 알 수 있습니다.

<=> 비교 연산자에 근거한 결합은 NULL 의 취급은 다른 값과 다르지 않습니다. 다른 어떤 N 에 대해서도 N <=> N 과 똑같이, NULL <=> NULL 입니다.

그러나 = 연산자를 기준으로 조인은 NULL 은 NULL 이 아닌 값과 다릅니다. expr1 또는 expr2 (또는 모두)가 NULL 이면 expr1 = expr2 는 true가 없습니다. 이것은 형식 tbl_name.key = expr 비교 ref 액세스에 영향을 미칩니다. expr 의 현재 값이 NULL 의 경우 비교는 true가되지 않기 때문에 MySQL은 테이블에 액세스하지 않습니다.

= 비교에서는 테이블에 NULL 값의 수는 문제가되지 않습니다. 최적화의 목적으로 관련있는 값은 NULL 이 아닌 값 그룹의 평균 크기입니다. 그러나 MySQL은 현재 평균 크기를 수집하거나 사용 할 수 없습니다.

InnoDB 와 MyISAM 테이블에서는 innodb_stats_method 및 myisam_stats_method 시스템 변수를 각각 사용하여 테이블 통계 수집에 약간의 제어가 가능합니다. 이러한 변수는 3 가지 가능성있는 값을 사용할 수 있으며 다음과 같이 다릅니다.

변수가 nulls_equal 에 설정되어있는 경우, 모든 NULL 값이 동일하게 취급됩니다 (즉, 그들 모두가 단일 값 그룹을 형성합니다).

NULL 값 그룹 크기가 NULL 이 아닌 값 그룹 크기보다 훨씬 큰 경우,이 메소드는 평균 그룹 크기를 위쪽으로 왜곡합니다. 그러면 최적화는 NULL 이 아닌 값을 검색하는 결합에 대해 인덱스가 실제 이상으로 쓸모없는 것처럼 보입니다. 결과적으로 nulls_equal 메소드에 의해 최적화에 ref 액세스에 대해 인덱스를 사용해야 경우에도 사용하지 않도록 할 수 있습니다.
변수가 nulls_unequal 에 설정되어있는 경우, NULL 값은 동일한 것으로 간주되지 않습니다. 대신 각 NULL 값은 크기 1의 개별 값 그룹을 형성합니다.

많은 NULL 값이있는 경우,이 메소드는 평균 그룹 크기를 아래쪽으로 휘게합니다. NULL 이외의 평균치 그룹 크기가 큰 경우 NULL 값을 크기 1의 그룹으로 계산하면 최적화 프로그램은 NULL 이 아닌 값을 검색하는 결합에 대해 인덱스 값을 많은 견적습니다. 결과적으로 nulls_unequal 메소드에 의해 다른 메소드가 적합 할 수있는 경우에, 최적화에 ref 조회에이 인덱스를 사용 할 수 있습니다.
변수가 nulls_ignored 에 설정되어있는 경우, NULL 값은 무시됩니다.

= 보다 <=> 를 사용하는 많은 결합을 사용하는 경향이 있다면, 비교 NULL 값은 특별한 것이 아니라 NULL 은 서로 같습니다. 이 경우 nulls_equal 이 적절한 통계 방법입니다.

innodb_stats_method 시스템 변수는 글로벌 값이 있습니다. myisam_stats_method 시스템 변수는 글로벌 값과 세션 값을 모두 있습니다. 글로벌 값을 설정하면 해당 스토리지 엔진에서 테이블의 통계 수집에 영향을줍니다. 세션 값을 설정하면 현재의 클라이언트 연결에 대해서만 통계 수집에 영향을줍니다. 이것은 myisam_stats_method 세션 값을 설정함으로써 다른 클라이언트에 영향을주지 않고 지정된 메소드에서 테이블의 통계를 강제로 다시 생성시킬 수 있다는 것을 의미합니다.

테이블 통계를 다시 생성하려면 다음 중 하나의 방법을 사용할 수 있습니다.

myisamchk --stats_method = method_name --analyze를 실행합니다
테이블을 변경하여 통계를 오래시켜 (예를 들어, 행을 삽입하고 삭제합니다) 다음 myisam_stats_method 를 설정하고 ANALYZE TABLE 명령문을 발행합니다.

innodb_stats_method 과 myisam_stats_method 의 사용에 대한 몇 가지 경고는 다음과 같습니다.

전술 한 바와 같이, 테이블 통계를 명시 적으로 수집 할 수 있습니다. 그러나 MySQL은 통계를 자동으로 수집 할 수도 있습니다. 예를 들어, 테이블에 문 실행 도중에 그러한 문에서 테이블을 변경하는 것도있어, MySQL은 통계를 수집 할 수 있습니다. (예를 들어, 이것은 대량 삽입 또는 삭제하거나 일부 ALTER TABLE 문에서 일어날 수 있습니다.)이 행해진 경우, 그 시점에서의 innodb_stats_method 또는 myisam_stats_method 값을 사용하여 통계가 수집됩니다 . 따라서 한 방법을 사용하여 통계를 수집해서 나중에 테이블의 통계가 자동으로 수집 된 때 시스템 변수에 다른 메소드가 설정되어 있으면 다른 방법이 사용됩니다 .
특정 테이블의 통계를 생성하는 데 사용 된 메소드를 전하는 방법은 없습니다.
이 변수는 InnoDB 와 MyISAM 테이블에만 적용됩니다. 다른 스토리지 엔진은 테이블 통계를 수집하는 방법은 하나 밖에 없습니다. 보통은 nulls_equal 메소드에 가까운 것입니다.