study MySQL 옵티마이저 힌트 옵티마이저와 힌트 해시 조인 인덱스 정렬 선호 조인 최적화 알고리즘 Exhaustive 알고리즘 Greedy 검색 알고리즘 Heuristic

조인 최적화 알고리즘

Exhaustive와 Greedy 알고리즘

책너두 6기 34일차

백은빈, 이성욱의 Real MySQL8.0 1권 p.356 ~ p.369

내용정리

09 옵티마이저와 힌트

9.3 고급 최적화

9.3.1 옵티마이저 스위치 옵션

9.3.1.19 해시 조인(hash_join)

해시 조인은 첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코드를 찾는 데까지는 시간이 많이 걸리지 않는다.

네스티드 루프 조인은 마지막 레코드를 찾는 데까지는 시간이 많이 걸리지만 첫 번째 레코드를 찾는 것은 상대적으로 훨씬 빠르다.

따라서 해시 조인 쿼리는 최고 스루풋(Best Throughput)에 적합하고 네스티드 루프 조인은 최고 응답 속도(Best Response-time)에 적합하다. 일반적인 웹 서비스는 온라인 트랜잭션(OLTP)서비스이기 때문에 스루풋도 중요하지만 응답 속도가 더 중요하다. 반면 분석과 같은 서비스는 전체적으로 처리 소요 시간이 더 중요하다.

9.3.1.20 인덱스 정렬 선호(prefer_ordering_index)

MySQL 옵티마이저는 ORDER BY 또는 GROUP BY를 인데스를 사용해 처리 가능한 경우 쿼리의 실행 계획에서 이 인덱스의 가중치를 높이 설정해서 실행된다.

9.3.2 조인 최적화 알고리즘

조인 쿼리의 실행 계획 최적화를 위한 알고리즘은 2개가 있다.

9.3.2.1 Exhaustive 검색 알고리즘

FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법. 테이블 갯수가 n개라면 n!개의 가능한 조인 조합이 발생한다.

9.3.2.2 Greedy 검색 알고리즘

Exhaustive 검색 알고리즘의 시간 소모 문제점을 해결하기 위한 알고리즘이다.

  1. 전체 N개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
  2. 1번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정
  3. 2번에서 선정된 실행 계획의 첫 번째 테이블을 “부분 실행 계획”의 첫 번째 테이블로 선정
  4. 전체 N-1개의 테이블 중(3번 제외)에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
  5. 4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 “부분 실행 계획”에 대입해 실행 비용을 계산
  6. 5번의 결과, 최적의 계획에서 두 번쨰 테이블을 3번에서 생성된 “부분 실행 계획”의 두 번쨰 테이블로 선정
  7. 남은 테이블이 모두 없어질때까지 4~6번 반복하며 “부분 실행 계획”에 테이블의 조인 순서를 기록
  8. 최종적으로 “부분 실행 계획”이 테이블의 조인 순서로 결정

optimizer_search_depth 두 알고리즘 중 어떤 것을 사용할지 결정하는 변수. 0~62까지가 범위인데, 1~62의 정수값이면 Greedy 검색 대상을 지정된 개수로 한정해서 최적의 실행 계획을 산출하고, 0으로 설정되면 옵티마이저가 자동으로 결정.

optimizer_prune_level: Heuristic 검색이 작동하는 방식을 제어. 1로 설정하면 옵티마이저는 조인 순서 최적화에 경험 기반의 Heuristic 알고리즘을 사용한다.

CATALOG
    MySQL