study MySQL 옵티마이저 힌트 옵티마이저와 힌트 세미 조인 인덱스 머지 세미 조인 테이블 풀-아웃 퍼스트매치 루스 스캔 단축 실행 경로

세미 조인 최적화

최근의 최적화 방법들

책너두 6기 32일차

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

내용정리

09 옵티마이저와 힌트

9.3 고급 최적화

9.3.1 옵티마이저 스위치 옵션

9.3.1.5 인덱스 머지(index_merge)

인덱스를 이용해 쿼리를 실행할 경우 테이블별로 하나의 인덱스만 사용하지 않고 2개 이상의 인덱스를 이용해 쿼리를 처리하는 옵션

9.3.1.6 인덱스 머지 -교집합(index_merge_intersection)

쿼리가 2개의 where 조건을 가질 때 각각의 인덱스 키를 모두 사용하여 쿼리를 처리하는 것. 결과를 교집합만 반환한다.

9.3.1.7 인덱스 머지-합집합(index_merge_union)

교집합과 달리 OR 연산자를 사용하는 것. 합집합을 반환한다. 우선순위 큐를 이용하여 중복된 레코드들을 정렬 없이 걸러낸다.

9.3.1.8 인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)

위에서는 별도의 정렬 없이 합집합을 구하였지만, 인덱스 머지 작업 중 결과의 정렬이 필요할 떄는 이 방법을 사용한다.

9.3.1.9 세미 조인(semijoin)

실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리(Semi-join)

최적화 방법

  • 세미 조인 형태(=,IN)
    • 세미 조인 최적화
    • IN-to-EXISTS 최적화
    • MATERIALIZATION 최적화
  • 안티 세미 조인 쿼리 형태(<>, NOT IN)
    • IN-to-EXISTS
    • MATHERIALIZATION 최적화
  • 세미 조인 최적화(최근)
    • Table Pull-out
    • Duplicate Weed-out
    • First Match
    • Loose Scan
    • Materialization
9.3.1.10 테이블 풀-아웃(Table Pull-out)

세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화

  • 세미 조인 서브쿼리에서만 사용 가능하다.
  • 서브쿼리 부분이 UNIQUE 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우에만 사용 가능하다
  • 적용되도 기존 쿼리에서 최적화 방법이 사용 불가능한 것이 아니다.
  • 서브쿼리의 모든 테이블이 아우터 쿼리로 끄집어 낼 수 있다면 서브쿼리 자체는 없어진다.
  • 서브쿼리를 조인으로 풀어서 사용할 필요가 없다.
9.3.1.11 퍼스트 매치(firstmatch)

IN 형태의 세미 조인을 EXISTS 형태로 튜닝한 것과 비슷한 방법으로 실행된다.

장점

  • 가끔은 여러 테이블이 조인되는 경우 원래 쿼리에는 없던 동등 조건을 옵티마이저가 자동으로 추가하는 형태의 최적화가 실행되기도 한다.
  • 모든 테이블에 대해 Firstmatch로 최적화를 수행할지 아니면 일부 테이블에 대해서만 수행할지 취사선택할 수 있다.

제한 사항

  • 서브쿼리에서 하나의 레코드만 검색되면 멈추는 단축 실행 경로(Short-cut path)라서 서브쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행된다.
  • 실행 계획의 Extra 칼럼에는 “FirstMatch(table-N)”문구가 표시
  • 상관 서브쿼리(Correlated subquery)에서도 사용될 수 있다.
  • GROUP BY나 집합 함수가 사용된 서브쿼리의 최적화에는 사용될 수 없다.
9.3.1.12 루스 스캔(loosescan)

루스 인덱스 스캔과 비슷한 읽기 방식을 사용한다.

특성

  • 루스 인덱스 스캔으로 서브쿼리 테이블을 읽고, 그다음으로 아우터 테이블을 드리븐으로 사용해서 조인을 수행하기 떄문에 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있어야 된다.
CATALOG
    MySQL