[Real MySQL] 9-2. 옵티마이저와 힌트: 고급 최적화

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.

 

1. 옵티마이저 스위치 옵션

조인 옵션

optimizer_switch 시스템 변수

• 옵티마이저 스위치 옵션 등록을 위한 키로 사용됨

 

MRR과 배치 키 액세스 (batch key access)

• Multi-Range Read
  • 네스티드 루프 조인의 단점을 보완함 (레코드를 매번 새롭게 랜덤 액세스하므로 최적화하여 읽을 수 없음)
  • 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링함
  • 조인 버퍼에 레코드가 가득차면, MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한번에 요청함
  • 읽어야 할 레코드를 정렬된 순서로 접근해서 디스크 읽기를 최소화 할 수 있음

• 배치 키 엑세스
  • MRR을 응용해서 실행되는 조인 방식
  • 부가적인 정렬 작업이 필요함 (성능에 안좋은 영향을 미칠 경우도 발생함)

 

인덱스 컨디션 푸시다운 (index_condition_pushdown)

• 인덱스에서 처리할 수 있는 조건을 최대한 먼저 평가한 후, 스토리지 엔진에 접근
• 다중 컬럼 인덱스에서 일부 컬럼이 WHERE 조건에 사용될 때 적용됨

 

CREATE INDEX idx_category_price ON products(category, price);

SELECT * FROM products WHERE category = 'Electronics' AND price > 500;

• 인덱스에서 price 컬럼이 있으므로, 필터링 후 관련 레코드를 스토리지 엔진에서 읽음

 

인덱스 확장 (use_index_extensions)

• 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지 결정하는 옵션
• 프라이머리 키가 필요없는 상황이면 자동으로 세컨더리 인덱스의 컬럼만 가지고 조회함

 

인덱스 머지 (index_merge)

• 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리
  • 보통 하나의 인덱스만 사용하는 것이 효율적
  • 단, 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 때 사용함

 

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

• 각 인덱스에서 얻은 값들끼리 교집합하여 최종 결과를 만들어낸 경우

 

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

• 각 인덱스에서 얻은 값들끼리 합집합하여 최종 결과를 만들어낸 경우
  • OR 조건 사용 시, 사용됨
• 우선순위 큐 알고리즘
  • 인덱스를 이용해 각각 조건을 만족하는 레코드를 가져온 후, 프라이머리 키를 기준으로 중복을 제거하면서 결과를 병합함
  • 이 과정에서 우선순위 큐를 사용하여 가장 작은 프라이머리 키 부터 병합 가능
  • 우선순위 큐를 활용하면 별도의 정렬 없이 효율적으로 중복을 제거하면서 병합 가능

 

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

• 인덱스 머지 중 중간 결과의 정렬이 필요할 경우 사용함
• 한 쪽 집합은 정렬이 되어있지만 다른 한쪽은 정렬되어 있지 않은 경우
• 정렬되어있지 않은 집합을 정렬하고 최종 결과를 만듬

 

세미 조인

explain 
    select * from employees e 
    where e.emp_no IN 
    (select de.emp_no from dept_emp de where de.from_date='1995-01-01');

• 서브 쿼리에서 특정 조건에 일치하는 레코드가 있는지만 체크하는 방식
  • 실제 조인 수행 X
• 최적화 방법
  
  • IN-to-EXISTS 최적화: IN -> EXISTS로 변환 (첫번째 일치하는 값을 찾으면 바로 종료하도록 함)
  • MATERIALIZATION 최적화: 서브 쿼리 결과를 임시 테이블로 저장하여 처리

 

세미 조인 최적화: 테이블 풀-아웃 (Table Pull-out)

• 서브 쿼리를 아우터 쿼리로 끄집어낸 후, 쿼리를 조인으로 재작성하는 형태의 최적화
• 세미 조인 서브쿼리에서만 사용 가능함

 

세미 조인 최적화: 퍼스트매치 (firstmatch)

• IN 서브 쿼리를 조인으로 처리하는 방식
  • 일치하는 레코드 1건만 찾으면 더이상 테이블 검색을 하지 않음
• 동등 조건 전파
  • 하나의 테이블에서 사용된 조건이 조인된 다른 테이블에도 전파되는 최적화 방식
  • 불필요한 데이터를 미리 필터링하여 쿼리 성능을 향상시킴
  • 퍼스트매치는 동등 조건 전파가 아우터 쿼리의 테이블까지 전파됨
  • IN-to-EXISTS 는 동등 조건 전파가 서브쿼리 내에서만 가능함
• 서브쿼리의 모든 테이블에 퍼스트매치 최적화를 수행할지 일부만 할지 취사 선택할 수 있음

 

SELECT * 
FROM employees e
WHERE e.emp_no IN (
    SELECT de.emp_no 
    FROM dept_emp de 
    WHERE de.from_date = '1995-01-01'
);

SELECT * 
FROM employees e
WHERE EXISTS (
    SELECT 1 
    FROM dept_emp de 
    WHERE de.emp_no = e.emp_no AND de.from_date = '1995-01-01'
);

 

세미 조인 최적화: 루스 스캔 (loosescan)

• 서브 쿼리를 조인으로 최적화
  • 서브쿼리 테이블을 드라이빙 테이블로
  • 아우터 테이블을 드리븐 테이블로 
• 서브쿼리 테이블을 루스 인덱스 스캔과 유사하게 읽음
  • 조인 컬럼을 최적으로 처리함
  • 서브쿼리에서 조건에 맞는 데이터만을 가져옴

 

세미 조인 최적화: 구체화 (materialization)

• 세미 조인에 쓰인 서브쿼리를 통채로 구체화해서 쿼리를 최적화한다는 의미
• 구체화는 내부 임시 테이블을 생성한다는 뜻

 

세미 조인 최적화: 중복 제거 (Duplicated Weed-out)

• 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고, GROUP BY로 중복된 레코드를 제거하는 방법

 

세미 조인 최적화: 컨디션 팬아웃 (condition_fanout_filter)

• 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행

 

세미 조인 최적화: 파생 테이블 머지 (derived_merge)

• 파생 테이블: FROM 절에 사용된 서브 쿼리
• 파생 테이블에 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화 도입

 

세미 조인 최적화: 인비저블 인덱스 (use_invisible_indexes)

• 옵티마이저가 실행 계획 수립 시, 인덱스를 사용하지 못하게 제어하는 기능 제공

ALTER TABLE employees ALTER INDEX ix_hiredate INVISIBLE;

 

세미 조인 최적화: 스킵 스캔 (skip_scan)

• 기본적으로 인덱스를 사용하려면 조건절의 컬럼이 인덱스 컬럼과 동일해야 함
• 제한적으로 인덱스 사용이 가능
  • 선행 칼럼이 조건절에 사용되지 않더라도, 후행 칼럼의 조건만으로 인덱스를 이용한 쿼리 성능 개선이 가능함
  • 단, 선행 칼럼이 소수의 유니크한 값을 가질때만 사용됨

 

세미 조인 최적화: 해시 조인 (hash_join)

• 작은 테이블을 메모리에 해시 테이블로 생성하고, 큰 테이블을 읽어오면서 해시 테이블을 탐색하여 일치하는 데이터를 찾는 방식
  • 네스티드 루프 조인이 최적의 성능을 내기 어려운 경우 대안으로 사용됨
  • 조인 조건에 사용되는 컬럼에 인덱스가 없거나, 조인 대상 테이블 중 하나가 매우 작은 경우에 적합
• 빌드 단계
  • 조인 대상 테이블 중 작은 테이블을 메모리에 해시 테이블로 저장함
  • 해시 테이블로 만들 때, 조인 조건이 되는 컬럼을 키로 사용하여 해시 맵 생성
• 프로프 단계
  • 큰 테이블을 읽으면서, 해시 테이블을 조회하여 일치하는 레코드를 찾음
  • 해시 테이블을 기반으로 빠르게 조인을 수행
• Best Throughput 전략에 적합함
  • 첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코드를 찾는 데까지는 시간이 많이 걸리지 않음
  • 분석과 같은 서비스에 적합
• 조인 버퍼 공간이 부족할 경우, 빌드 테이블과 청크 테이블을 적당한 청크로 쪼개서 청크별로 해시 조인을 처리함
• 시스템 변수
  • join_buffer_size: 조인 버퍼 사이즈 제어

 

세미 조인 최적화: 인덱스 정렬 선호 (prefer_ordering_index)

• ORDER BY or GROUP BY를 처리할 때, 인덱스 사용이 가능할 경우 이 인덱스의 가중치를 높이 설정해서 실행함

 

select * from employees
 where hire_date between '1985-01-01' and '1985-02-01' 
 order by emp_no;

1. ix_hiredate 인덱스를 이용해 where 절 조건에 일치하는 레코드를 찾은 다음, emp_no로 정렬
2. 프라이머리 키를 정순으로 읽으면서 hire_date 컬럼의 조건에 일치하는지 비교 후 결과 반환

 

• 기본적으로 1번이 효율적이지만 옵티마이저가 잘못된 실행 계획을 선택할 수도 있음
• 인덱스에 가중치를 주지 않도록 설정할 수 있음

set session optimizer_switch='prefer_ordering_index=OFF'

 

2. 조인 최적화 알고리즘

Exhaustive 검색 알고리즘

• FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법
• 테이블 갯수의 팩토리얼 수 만큼 조합이 발생함

 

Greedy 검색 알고리즘

• Exhaustive 검색 알고리즘의 시간 소모적인 문제를 해결하기 위한 최적화 기법

 

과정

1. 전체 N개의 테이블 중에서 optimizer_search_depth 시스템 변수에 정의된 테이블로 가능한 조인 조합을 생성
2. 1번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정
3. 2번에서 선정된 실행 계획의 첫 번째 테이블을 부분 실행 계획의 첫 번째 테이블로 선정
4. 전체 N-1개의 테이블 중 (3번에서 선택된 테이블 제외) optimizer_search_depth 시스템 변수에 정의된 개수로 조인 조합 생성
5. 반복하면서 실행 계획 완성하기

 

시스템 변수

• optimizer_search_depth
  • 어떤 알고리즘을 선택할지 결정하는 변수 
  • 0: Exhaustive
  • 1~62: Greedy
• optimizer_prune_level
  • Heuristic 검색 작동 방식 제어
  • 다양한 조인 순서의 비용을 계산하는 도중 중간에 이전 값보다 더 크게 계산되면 포기할 수 있음