2025-09-30

오늘 배운 것

9.3 고급 최적화

9.3.1 옵티마이저 스위치 옵션

9.3.1.9 세미 조인(semijoin)

  • 세미 조인
    • 다른 테이블과 실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리
  • 세미 조인 쿼리 최적화 [“= (subquery)”, “IN (subquery)” ]
    • 세미 조인 최적화
    • IN-to-EXISTS 최적화
    • MATERIALIZATION 최적화
  • 안티 세미 조인 쿼리 최적화 [ “<> (subquery)”, “NOT IN (subquery)” ]
    • IN-to-EXISTS 최적화
    • MATERIALIZATION 최적화
  • 최근 세미 조인 쿼리 최적화
    • Table Pull-out
      • 사용가능하다면 항상 세미 조인보다는 좋은 성능을 내기 때문에 별도의 제어 옵티마이저 옵션이 없음
    • Duplicate Weed-out
    • First Match
    • Loose Scan
    • Materialization
  • 쿼리에 사용되는 테이블과 조인 조건의 특성에 따라 옵티마이저는 사용 가능한 전략들을 선별적으로 사용

9.3.1.10 테이블 풀-아웃(Table Pull-out)

  • 세미 조인의 서브 쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성
  • 실행 계획에서 해당 테이블들의 id 칼럼 값이 같은지 다른지를 비교하고 Extra 칼럼에 아무것도 출력되지 않았는지 확인하여서 최적화가 사용되었는지 체크 가능
  • 제한 사항 및 특성
    • 세미 조인 서브쿼리에서만 사용 가능
    • 서브쿼리 부분이 UNIQUE 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우에만 사용 가능
    • 테이블 풀-아웃 최적화와 함께 기존 쿼리에서 가능했던 최적화 방법을 사용할 수 있으므로 최대한 적용하는 것이 좋음
    • 서브 쿼리의 모든 테이블이 아우터 쿼리로 끄집어 낼 수 있다면 서브 쿼리 자체는 없어짐
    • 테이블 풀-아웃 최적화가 서브쿼리를 조인으로 풀어서 사용하는 것이기 때문에 서브쿼리를 사용해도 괜찮다

9.3.1.11 퍼스트 매치(firstmatch)

  • IN(subquery) 형태의 세미 조인은 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행
    • IN-to-EXISTS 변환과 거의 비슷한 처리 로직 수행
  • 장점
    • IN-to-EXISTS에서는 동등 조건 전파가 서브쿼리 내에서만 가능했지만, FirstMatch는 조인 형태로 처리되기 때문에 서브 쿼리 뿐만 아니라 아우터 테이블까지 전파되어 더 많은 조건이 주어져 더 나은 실행 계획 수립
    • IN-to-EXISTS 에서는 아무런 조건없이 변환이 가능한 경우에 무조건 그 최적화를 수행 했지만, FirstMatch는 서브 쿼리의 모든 또는 일부만 최적화 수행할지 선택 가능
  • 제한 사항 및 특성
    • 하나의 레코드만 검색되면 더이상의 검색을 멈추는 단축 실행 경로 이기 때문에 서브 쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후 실행
    • 상관 서브쿼리에서도 사용 가능
    • GROUP BY나 집합 함수가 사용된 서브쿼리의 최적화에는 사용될 수 없음

9.3.1.12 루스 스캔(loosescan)

  • 인덱스를 사용하는 GROUP BY 최적화 방법
    • 루스 인덱스 스캔과 비슷한 읽기 방식
  • 특성
    • 루스 인덱스 스캔으로 서브 쿼리 테이블 읽음
    • 아우터 테이블을 드리븐으로 사용해서 조인을 수행
    • 서브 쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰줘야 사용
      • 한개의 컬럼으로된 인덱스
      • 복합 인덱스의 경우 첫번째 키가 WHERE 절에 있어야함

9.3.1.13 구체화(Materialization)

  • 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화
    • 내부 임시 테이블을 생성
  • 제한 사항과 특성
    • IN(subquery)에서 서브쿼리는 상관 서브쿼리가 아니어야 한다
    • 서브쿼리에 GROUP BY나 집합 함수들이 사용돼도 구체화를 사용할 수 있음
    • 내부 임시 테이블이 사용됨

9.3.1.14 중복 제거(Duplicated Weed-out)

  • 세미 조인 서브 쿼리를 일반적인 INNER JOIN쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 최적화
    • INNER JOIN + GROUP BY
  • 장점 및 제약 사항
    • 상관 서브쿼리도 사용 가능
    • 서브 쿼리에 GROUP BY 나 집합 함수 사용시 사용 불가
    • 서브쿼리의 테이블을 조인으로 처리하여 최적화 할 수 있는 방법이 많음

9.3.1.15 컨디션 팬아웃(condition_fanout_filter)

  • 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행
  • 레코드 비율 계산 가능한 칼럼 조건
    • WHERE 조건절에 사용된 컬럼에 대해 인덱스가 있는 경우
    • WHERE 조건절에 사용된 컬럼에 대해 히스토그램이 있는 경우
  • 주의사항
    • 비율 계산 등으로 쿼리의 실행 계획 수립에 더 많은 시간과 컴퓨팅 자원 사용
    • 쿼리 실행 계획이 잘못된 선택을 한 적이 별로없으면 성능 향상에 딱히 도움이 되지 않음
    • 해당 쿼리 빈도가 높으면 실행계획수립 오버헤드가 크므로 바로 업그레이드 하는 것보단 테스팅 후 업그레이드 하기

9.3.1.16 파생 테이블 머지(derived_merge)

  • 파생 테이블 : FROM 절에 사용된 서브 쿼리
  • 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분 제거
  • 옵티마이저가 자동으로 서브쿼리를 외부 쿼리로 병합 불가한 경우
    • 집계 함수와 윈도우 함수가 사용된 서브쿼리
    • DISTINCT가 사용된 서브쿼리
    • GROUP BY나 HAVING이 사용된 서브쿼리
    • LIMIT이 사용된 서브쿼리
    • UNION 또는 UNION ALL을 포함하는 서브쿼리
    • SELECT 절에 사용된 서브쿼리
    • 값이 변경되는 사용자 변수가 사용된 서브 쿼리

9.3.1.17 인비저블 인덱스(use_invisible_indexes)

  • 인덱스가 존재하면 옵티마이저는 항상 실행 계획 수립 시 인덱스 검토
  • 인덱스를 삭제하지 않고, 해당 인덱스를 사용하지 못하게 제어하는 기능
    • ALTER TABLE 테이블 명 ALTER INDEX 인덱스명 INVISIBLE;

9.3.1.18 스킵 스캔(skip_scan)

  • 인덱스의 선행 칼럼이 조건절에 사용되지 않더라도 후행 칼럼의 조건만으로 인덱스를 이용한 쿼리 성능 개선
  • 옵티마이저는 인덱스의 선행 칼럼이 소수의 유니크한 값을 가질 때만 최적화 사용
    • 선행 칼럼이 매우 다양한 값을 가지게 되면 오히려 비효율적이기 때문

9.3.1.19 해시 조인(hash_join)

  • 해시 조인
    • 첫번째 레코드 찾는데 시간이 많이 걸림
    • 최종 레코드 찾는 시간은 빠름
    • 최고 응답 속도 전략에서 유리
      • 웹서비스(응답속도 중요)
  • 네스티드 루프 조인
    • 첫번째 레코드 찾는데 시간이 빠름
    • 최종 레코드 찾는데 까지는 시간이 많이 컬림
    • 최고 스루풋 전략에 적합
      • 분석 서비스(처리소요시간 중요)
  • 조인 조건의 칼럼이 인덱스가 없거나 조인 대상 테이블 중 일부의 레코드 건수가 매우 적은 경우 ⇒ 해시 조인 사용
    • 네스티드루프 사용하기 어려울 때 사용하는 차선책
    • 항상 빠른 것이 아니기 때문에 강제로 옵티마이저 힌트를 줘서 해시 조인으로 유도하는 것이 항상 좋은 것은 아님
  • 8.0.17 버전
    • 블록네스티드 루프 조인 알고리즘이 조인 조건이 인덱스를 제대로 활용할 수 없을 때 사용
    • 인덱스 잘 설계된 데이터 베이스에서는 거의 사용되지 않음
  • 8.0.18, 8.0.19 버전
    • 동등 조인 → 해시 조인
    • 안티조인, 세미 조인 → 블록 네스티드 루프 조인
  • 8.0.20 이후
    • 네스티드 루프 조인 사용 불가 시 전부 해시 조인 사용
  • 해시 조인 방법
    • 빌드 단계
      • 조인 대상 테이블 중에서 레코드 건수가 적어서 해시 테이블로 만들기에 용이한 테이블(빌드 테이블)을 골라서 메모리에 해시 테이블을 생성
    • 프로브 단계
      • 나머지 테이블(프로브 테이블)의 레코드를 읽어서 해시 테이블의 일치 레코드 찾기
  • 해시 테이블의 레코드 건수가 많을 때
    • 빌드 테이블과 프로브 테이블을 적당한 크기(하나의 청크가 조인 버퍼보다 작도록)의 청크로 분리 후 청크별로 해신 조인 방식으로 처리
  • 클래식 해시 조인
    • 메모리에서 한번에 처리 가능한 해시 조인
  • 그레이스 해시 조인
    • 청크로 나누는 해시 조인
  • mysql은 클래식 해시 조인과 그레이스 해시 조인을 하이브리드하게 사용하며 xxHash64 해시 함수 사용

9.3.1.20 인덱스 정렬 선호(prefer_ordering_index)

  • 옵티마이저는 ORDER BY 또는 GROUP BY를 인덱스를 사용해 처리 가능한 경우에 해당 인덱스의 가중치를 높이 설정해서 실행
  • 하지만 레코드 건수가 많은 경우에도 정렬된 인덱스 활용으로 실행 계획 수립을 하는 실수를 가끔 저지름
  • 따라서 ORDER BY 인덱스의 너무 많은 가중치를 부여하지 않도록 제어 가능

results matching ""

    No results matching ""