10.9.1 Controlling Query Plan Evaluation

10.9.1 쿼리 플랜 평가 제어

쿼리 옵티마이저의 작업은 SQL 쿼리를 실행하기 위한 최적의 플랜을 찾는 것입니다. “좋은” 플랜과 “나쁜” 플랜 사이의 성능 차이는 (몇 초 대 몇 시간 또는 며칠 수준의) 수십 배에 달할 수 있기 때문에, MySQL의 옵티마이저를 포함한 대부분의 쿼리 옵티마이저는 가능한 모든 쿼리 평가 플랜 중에서 최적의 플랜을 찾기 위해 다소 철저한 검색을 수행합니다. 조인 쿼리의 경우, MySQL 옵티마이저가 조사하는 가능한 플랜의 수는 쿼리에서 참조되는 테이블 수에 따라 기하급수적으로 증가합니다. 테이블 수가 적을 때(일반적으로 7~10개 미만)에는 문제가 되지 않습니다.

그러나 더 큰 쿼리가 제출되면, 쿼리 최적화에 소비되는 시간이 서버 성능의 주요 병목 요소가 되기 쉽습니다.

보다 유연한 쿼리 최적화 방법에서는 사용자가 옵티마이저가 최적의 쿼리 평가 플랜을 찾기 위해 얼마나 철저하게 검색할지 제어할 수 있습니다. 일반적인 개념은, 옵티마이저가 조사하는 플랜 수가 적을수록 쿼리를 컴파일하는 데 소비되는 시간이 줄어든다는 것입니다. 반면에, 옵티마이저가 일부 플랜을 건너뛰기 때문에 최적의 플랜을 찾지 못할 수도 있습니다.

옵티마이저가 평가하는 플랜 수와 관련된 동작은 두 개의 시스템 변수로 제어할 수 있습니다:

• optimizer_prune_level 변수는 각 테이블에 대해 접근되는 행 수의 추정치를 기반으로 특정 플랜을 건너뛰도록 옵티마이저에 지시합니다. 우리의 경험에 따르면 이러한 종류의 “educated guess”는 최적의 플랜을 놓치는 경우가 드물고, 쿼리 컴파일 시간을 극적으로 줄일 수 있습니다. 그렇기 때문에 이 옵션은 기본적으로 켜져 있습니다 (optimizer_prune_level=1). 그러나 옵티마이저가 더 나은 쿼리 플랜을 놓쳤다고 생각되면, 이 옵션을 꺼서 (optimizer_prune_level=0) 사용할 수 있지만, 그 경우 쿼리 컴파일에 훨씬 더 오랜 시간이 걸릴 위험이 있습니다. 이 휴리스틱을 사용하더라도 옵티마이저는 여전히 대략 기하급수적인 수의 플랜을 탐색한다는 점에 유의해야 합니다.

• optimizer_search_depth 변수는 각 미완성 플랜의 “future”를 옵티마이저가 얼마나 멀리 내다보고, 해당 플랜을 더 확장해야 할지를 평가할지를 지정합니다. 더 작은 값의 optimizer_search_depth는 쿼리 컴파일 시간을 수십 배까지 줄일 수 있습니다. 예를 들어, 12개, 13개 이상의 테이블을 가진 쿼리는, optimizer_search_depth가 쿼리의 테이블 수에 근접한 경우, 컴파일에 몇 시간 혹은 며칠이 걸릴 수 있습니다. 반면에 동일한 쿼리를 optimizer_search_depth를 3 또는 4로 설정하여 컴파일하면, 옵티마이저는 1분도 걸리지 않고 컴파일을 완료할 수 있습니다. 합리적인 값이 얼마인지 확신이 서지 않는 경우에는, optimizer_search_depth를 0으로 설정하여 옵티마이저가 값을 자동으로 결정하도록 할 수 있습니다.