본문 바로가기
데이터 베이스 기초

릴마큐 09장: 옵티마이저와 힌트

by 코헤0121 2026. 1. 5.
728x90
반응형

9.1 개요

9.1.1 쿼리 실행 절차

  1. SQL 파싱(Parsing): 'SQL 파서'가 SQL 문장을 토큰(최소 단위 어휘)으로 분리해 파스 트리를 만듭니다. 이 과정에서 문법 오류를 가장 먼저 잡아냅니다.
  2. 최적화 및 실행 계획 수립: '옵티마이저'가 파스 트리를 바탕으로 어떤 테이블을 먼저 읽을지, 어떤 인덱스를 사용할지 결정합니다. 조인 순서를 정하고 불필요한 조건을 제거하여 가장 효율적인 실행 계획을 세우는 쿼리의 '두뇌' 역할을 하는 단계입니다.
  3. 실행(Execution): 수립된 계획대로 '쿼리 실행기'가 스토리지 엔진에 데이터를 요청합니다. 이때 핸들러(Handler) API를 통해 레코드를 실제 읽거나 쓰는 작업이 이루어집니다.

9.1.2 옵티마이저의 종류

  • 규칙 기반 최적화 (RBO): 통계 정보 없이 미리 정해진 우선순위(규칙)에 따라 실행 계획을 세웁니다. 데이터가 어떻게 분포되어 있는지 고려하지 않기 때문에 항상 같은 실행 방법을 만들어내며, 현재는 거의 사용되지 않습니다.
  • 비용 기반 최적화 (CBO): 쿼리를 처리하기 위한 여러 방법을 만들고, 각 작업에 드는 비용(디스크 I/O, CPU 사용량 등)을 통계 정보를 바탕으로 수치화합니다. 그중 가장 비용이 적게 드는 최적의 경로를 선택하는 방식으로, 현재 대부분의 DBMS가 채택하고 있습니다.

이렇게 결정된 실행 계획이 궁금할 때는 EXPLAIN 명령어를 사용해 확인할 수 있습니다

9.2 기본 데이터 처리

9.2.1 풀 테이블 스캔과 풀 인덱스 스캔

  • 선택 조건: 옵티마이저는 테이블이 매우 작거나, 인덱스 효율이 낮다고 판단될 때(전체 레코드의 20~25% 이상을 읽어야 할 때) 풀 테이블 스캔을 선택합니다.
    • 풀 테이블 스캔이란? 인덱스를 사용하지 않고 테이블을 처음부터 끝까지 읽어서 처리
    • 풀 인덱스 스캔은 인덱스를 처음부터 끝까지 읽어서 처리
  • 리드 어헤드(Read Ahead): InnoDB는 풀 스캔 시 성능 향상을 위해 연속된 데이터 페이지가 읽히면 백그라운드 스레드를 통해 미리 최대 64개의 페이지를 읽어 버퍼 풀에 적재하는 '리드 어헤드' 기능을 실행합니다.
    • 리드 어헤드란? 리드 어헤드(read-ahead) 요청은 버퍼 풀에서 해당 페이지에 대한 수요가 임박할 것을 예측하여 비동기적으로 프리페치하는(미리 가져오는) I/O 요청이다. 이 요청은 하나의 익스텐트(extent)에 해당하는 모든 페이지를 가져온다.
  • 인덱스 활용: 쿼리에 필요한 칼럼이 인덱스에만 있는 경우(커버링 인덱스), 테이블 전체보다 용량이 작은 인덱스만 읽는 '풀 인덱스 스캔'을 수행하여 I/O를 줄입니다.

9.2.2 병렬 처리 (Parallel Query)

  • 작동 방식: innodb_parallel_read_threads 변수를 설정하면 하나의 쿼리를 여러 스레드가 동시에 처리할 수 있습니다.
  • 제약 사항: 현재 MySQL 8.0에서는 아무런 WHERE 조건 없이 테이블의 전체 건수를 확인하는(COUNT(*)) 등의 단순 쿼리에서만 병렬 처리가 가능합니다.

9.2.3 ORDER BY 처리 (Using filesort)

  • 소트 버퍼(Sort Buffer): 정렬을 위해 할당된 전용 메모리 공간입니다. 정렬 후 시스템으로 즉시 반납됩니다.
  • 알고리즘 종류:
    • 싱글 패스: 소트 버퍼에 정렬 키와 조절된 모든 칼럼을 담습니다. 메모리 소모는 크지만 빠릅니다.
    • 투 패스: 정렬 키와 레코드 주소(Row ID)만 담아 정렬 후 테이블을 다시 읽습니다. BLOB/TEXT 등 레코드가 매우 클 때 사용됩니다.

 

1️⃣ ORDER BY 정렬 처리 방식 개요

MySQL에서 ORDER BY는 크게 두 가지 방식으로 처리된다.

① 인덱스를 이용한 정렬

  • 원리: 인덱스 자체가 이미 정렬돼 있음 → 순서대로 읽기만 함
  • 장점
    • 매우 빠름
    • 추가 정렬 작업 없음
    • 스트리밍 방식 처리 가능
  • 단점
    • 인덱스 유지 비용 존재 (INSERT/UPDATE/DELETE 느려짐)
  • 실행계획
    • Extra 컬럼에 아무 표시 없음

② Filesort를 이용한 정렬

  • 원리: 실행 시점에 메모리(소트 버퍼)에 담아 정렬
  • 장점
    • 인덱스 없이도 정렬 가능
    • 쓰기 작업은 상대적으로 빠름
  • 단점
    • 쿼리 실행 시 정렬 비용 발생
  • 실행계획
    • Extra 컬럼에 Using filesort

2️⃣ 인덱스를 사용할 수 없는 경우 (Filesort 발생)

다음 경우에는 인덱스 정렬이 불가능하다.

  • ORDER BY 컬럼 조합이 너무 많아 인덱스를 만들 수 없을 때
  • GROUP BY, DISTINCT 결과를 정렬할 때
  • UNION 결과처럼 임시 테이블 결과를 다시 정렬할 때
  • ORDER BY RAND() 같은 랜덤 정렬

3️⃣ 소트 버퍼(Sort Buffer)

  • 정렬 전용 메모리 공간
  • 크기: sort_buffer_size
  • 정렬 완료 후 즉시 반환됨 (세션 단위 사용)

소트 버퍼 크기에 따른 문제

  • 너무 작으면
    → 디스크에 부분 정렬 결과를 계속 쓰고 읽음 (성능 급락)
  • 너무 크면
    → 동시 접속 환경에서 메모리 고갈 → OOM-Killer 위험

4️⃣ 정렬 알고리즘: 싱글 패스 vs 투 패스

정렬 시 무엇을 소트 버퍼에 담느냐에 따라 나뉜다.

정렬 모드 (옵티마이저 트레이스에서 확인 가능)

select * from information_schema.optimizer_trace;

① <sort_key, row_id> → 투 패스

  • 정렬 키 + PK(Row ID)만 정렬
  • 정렬 후 다시 테이블을 읽어 전체 컬럼 조회

② <sort_key, additional_fields> → 싱글 패스

  • 정렬 키 + 레코드 전체
  • 고정 길이 컬럼 저장

③ <sort_key, packed_additional_fields> → 싱글 패스

  • 정렬 키 + 레코드 전체
  • 가변 길이 컬럼 저장

5️⃣ 싱글 패스 vs 투 패스 차이

싱글 패스 정렬

  • 특징
    • 한 번만 테이블 읽음
    • 소트 버퍼 사용량 큼
  • 장점
    • 빠름
  • 단점
    • 메모리 많이 사용

투 패스 정렬

  • 특징
    • 테이블을 두 번 읽음
  • 장점
    • 메모리 사용 적음
  • 단점
    • I/O 비용 큼 (느림)

투 패스가 선택되는 경우

  • 레코드 크기가 max_length_for_sort_data 초과
  • BLOB, TEXT 컬럼이 SELECT 대상에 포함될 때

6️⃣ ORDER BY 처리 흐름 (3가지 패턴)

쿼리에 ORDER BY가 있으면 다음 중 하나로 처리된다.

① 인덱스를 이용한 정렬

  • Extra: 표시 없음
  • 가장 빠름
  • 스트리밍 방식 가능

② 조인의 드라이빙 테이블만 정렬

  • Extra: Using filesort
  • 조인 전에 첫 번째 테이블만 정렬
  • 조인 결과 증가를 최소화
ORDER BY e.last_name;

③ 임시 테이블 + 정렬

  • Extra: Using temporary; Using filesort
  • 조인 결과 전체를 임시 테이블에 저장 후 정렬
  • 가장 느림
ORDER BY s.salary;

7️⃣ 정렬 방식 성능 비교 & LIMIT 효과

스트리밍 방식

  • 인덱스 정렬만 해당
  • 레코드가 생성되는 즉시 클라이언트로 전송
  • LIMIT → 성능 개선 효과 큼

버퍼링 방식

  • Filesort, GROUP BY, 임시 테이블
  • 전체 결과를 모아서 정렬 후 응답
  • LIMIT → 성능 개선 거의 없음

8️⃣ 핵심 요약 한 줄씩

  • Using filesort ≠ 디스크 정렬
    → 정렬 알고리즘 이름
  • 인덱스 정렬만 스트리밍 방식
  • Filesort는 모두 버퍼링 방식
  • 싱글 패스: 빠름, 메모리 많이 씀
  • 투 패스: 느림, 메모리 적게 씀
  • Using temporary; Using filesort는 최악의 신호

 

9.2.4 GROUP BY 처리

  • 타이트 인덱스 스캔: 인덱스를 차례대로 읽으며 그루핑을 수행합니다. 추가적인 정렬이나 임시 테이블이 필요 없어 효율적입니다.
  • 루스 인덱스 스캔: 인덱스의 레코드를 건너뛰며 필요한 부분(주로 MIN(), MAX())만 읽어 가져옵니다.
  • 임시 테이블: 인덱스를 전혀 사용할 수 없을 때 사용하며, MySQL 8.0부터는 묵시적인 정렬을 수행하지 않아 이전 버전보다 성능이 개선되었습니다.

아래는 MySQL 9.2.4 GROUP BY 처리를 ORDER BY와 비교 흐름에 맞춰 한 번에 정리한 버전이다.
실행계획 볼 때 바로 분류할 수 있게 구조 중심으로 묶었다.

1️⃣ GROUP BY 처리의 기본 성격

  • GROUP BY는 스트리밍 방식 처리 불가
  • 반드시 결과를 모아서 처리(버퍼링) 해야 함
  • 처리 방식은 인덱스를 얼마나 활용할 수 있느냐에 따라 갈린다

2️⃣ GROUP BY 처리 방식 3가지

MySQL의 GROUP BY는 다음 세 가지 중 하나로 수행된다.

  1. 타이트 인덱스 스캔 (인덱스 스캔)
  2. 루스 인덱스 스캔
  3. 임시 테이블 사용

3️⃣ 타이트 인덱스 스캔 (Index Scan Group By)

개념

  • 인덱스를 처음부터 끝까지 순서대로 읽으면서
  • 인덱스 정렬 상태를 그대로 이용해 그루핑 수행

조건

  • GROUP BY 컬럼이 조인의 드라이빙 테이블에 속해야 함
  • GROUP BY 순서대로 인덱스가 존재해야 함

특징

  • 추가 정렬 없음
  • 임시 테이블 없음
  • 가장 안정적이고 예측 가능한 방식

👉 ORDER BY의 인덱스 정렬과 개념이 거의 동일

4️⃣ 루스 인덱스 스캔 (Loose Index Scan Group By)

개념

  • 인덱스를 모두 읽지 않고
  • 필요한 그룹의 대표 값만 건너뛰며 읽음
  • 주로 MIN(), MAX() 같은 집계 함수와 함께 사용

핵심 포인트

  • 모든 GROUP BY에서 되는 게 아님
  • 옵티마이저가 내부적으로 가능하다고 판단할 때만 선택
  • 조건 판단이 매우 까다롭고 직관적이지 않음

5️⃣ 루스 인덱스 스캔 가능 여부 예제 해설

(인덱스: (col1, col2, col3))

✅ 가능한 경우

select col1, col2 from tb_test group by col1, col2;
select distinct col1, col2 from tb_test;
select col1, min(col2) from tb_test group by col1;
  • 인덱스 선두 컬럼 기준
  • 불필요한 전체 스캔 없이 대표값만 추출 가능

❌ 불가능하거나 제한적인 경우

select col1, col2 from tb_test where col3 = const group by col1, col2;
  • 인덱스 선두(col1)가 아닌 조건
  • 루스 인덱스 스캔 불가
select col2 from tb_test where col1 < const group by col1, col2;
  • SELECT 컬럼과 GROUP BY, 인덱스 활용이 어긋남

👉 WHERE / SELECT / GROUP BY / 인덱스 순서가 조금만 어긋나도 실패

6️⃣ 임시 테이블을 사용하는 GROUP BY

언제 사용되나?

  • 인덱스를 전혀 사용할 수 없을 때
  • GROUP BY 컬럼 순서와 인덱스가 맞지 않을 때
  • 조인 결과를 기준으로 GROUP BY 해야 할 때

처리 방식

  • GROUP BY 컬럼으로 구성된 유니크 인덱스를 가진 임시 테이블 생성
  • 중복 제거 + 집계 함수 계산 수행

특징

  • 가장 비용이 큼
  • 데이터 양이 많을수록 성능 급락

MySQL 8.0 개선점

  • 묵시적 정렬 제거
  • 이전 버전보다 불필요한 ORDER BY 비용 감소

7️⃣ 실행계획 관점 요약

처리 방식임시 테이블정렬성능

타이트 인덱스 스캔 ❌ ❌ ⭐⭐⭐⭐
루스 인덱스 스캔 ❌ ❌ ⭐⭐⭐
임시 테이블 ⭕ 상황에 따라 ⭐

8️⃣ ORDER BY와 GROUP BY 차이 핵심 정리

  • ORDER BY
    • 인덱스 정렬이면 스트리밍 가능
  • GROUP BY
    • 항상 버퍼링 방식
  • 둘 다
    • 인덱스를 제대로 타면 성능 차이 극심
    • 임시 테이블이 등장하면 위험 신호

 

  • GROUP BY는 인덱스를 못 타면 무조건 무거워진다
  • 타이트 인덱스 스캔이 최선
  • 루스 인덱스 스캔은 되면 좋고, 기대하면 안 된다
  • 임시 테이블은 최후의 수단
  • 실행계획에서 Temporary 보이면 구조부터 의심

 

 

9.2.5 DISTINCT 처리

  • 단독 사용: GROUP BY와 동일한 방식으로 처리됩니다. 인덱스를 사용하지 못하면 내부적으로 임시 테이블을 생성합니다.
      • distinct 키워드로 가장 흔하게 실수하는 것 중 하나는 distinct는 select 하는 모든 대상 컬럼에 대해 유니크하게 조회하는 것이다. 특정 칼럼만 유니크하게 조회하는 것이 아니다.
        • 괄호를 쳐도 의미없다!!!
  • 집합 함수와 사용: COUNT(DISTINCT column)과 같이 쓰이면 해당 칼럼의 유니크한 값을 뽑기 위해 내부적으로 유니크 인덱스를 가진 임시 테이블을 생성하므로 성능 부하가 큽니다.

9.2.6 내부 임시 테이블 활용

  • 저장 엔진: 메모리에서는 TempTable(가변 길이 칼럼 지원) 또는 MEMORY 엔진을 사용하며, 디스크에서는 InnoDB 엔진을 사용합니다.
  • 디스크 전환: 메모리 임시 테이블 크기가 temptable_max_ram(기본 1GB)을 넘으면 디스크로 전환됩니다.
  • 정렬 알고리즘 선택: 레코드 전체를 소트 버퍼에 담는 '싱글 패스'는 빠르지만 메모리 공간을 많이 차지합니다. 반면, 레코드 크기가 너무 크면(BLOB, TEXT 등 포함) 정렬 키와 주소만 담는 '투 패스' 방식을 사용하여 메모리 효율을 높입니다.
  • 디스크 전환 조건: 내부 임시 테이블은 처음에 메모리(TempTable 등)에 생성됩니다. 하지만 테이블 크기가 시스템 변수인 temptable_max_ram(기본 1GB)을 초과하면 성능을 위해 디스크(InnoDB) 기반 테이블로 자동 전환됩니다.

 

9.3 고급 최적화

9.3.1 옵티마이저 스위치 (최적화 제어판)

optimizer_switch는 특정 기능을 켤지 끌지 결정하는 스위치 박스입니다.

  • MRR (Multi-Range Read): 디스크를 지그재그로 왔다 갔다 읽지 않고, 읽을 대상을 바구니에 모아 정렬한 뒤 한 번에 순서대로 읽어오는 기술이에요.
  • 인덱스 컨디션 푸시다운(ICP): "이 데이터는 필요 없어!"라는 판단을 상위 엔진이 아닌 데이터 보관소(스토리지 엔진)에서 미리 해버려 불필요한 일감을 줄입니다.
  • 세미 조인: IN (서브쿼리) 같은 형태를 똑똑하게 조인처럼 바꿔서 중복을 싹 제거하고 필요한 것만 골라냅니다.

 

설명: MySQL 옵티마이저 스위치는 쿼리 실행 계획을 수립할 때 어떤 최적화 기법을 사용할지 제어하는 설정입니다. optimizer_switch 시스템 변수를 통해 각 기능을 on, off, default로 설정할 수 있으며, 대부분의 옵션은 기본값이 on입니다.

1. MRR & 배치 키 액세스 (mrr & batched_key_access)

설명: MRR(Multi-Range Read)은 조인 시 드라이빙 테이블에서 읽은 레코드를 즉시 처리하지 않고 조인 버퍼에 모아뒀다가, 버퍼가 가득 차면 한 번에 정렬된 순서로 스토리지 엔진에 요청하는 방식입니다.

근거: 디스크 I/O를 최소화하기 위해 데이터 페이지에 정렬된 순서로 접근합니다. 이를 응용한 조인 방식이 BKA(Batched Key Access)입니다.

2. 블록 네스티드 루프 조인 (block_nested_loop)

설명: MySQL의 기본 조인 방식인 네스티드 루프 조인에서 조인 버퍼를 활용하는 방식입니다.

근거: MySQL 8.0.18부터 해시 조인이 도입되면서 이 방식은 더 이상 사용되지 않습니다.

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

설명: WHERE 조건 중 인덱스를 사용할 수 있는 조건을 스토리지 엔진 레벨로 "밀어 넣어서" 인덱스 내에서 먼저 필터링하는 최적화 기법입니다.

근거:

SELECT * FROM employees 
WHERE last_name='Acton' AND first_name LIKE '%sal';
  • OFF 상태: last_name으로 인덱스 검색 후 → 레코드 접근해서 first_name 조건 확인
  • ON 상태: 인덱스 내에서 두 조건 모두 비교 → 일치하는 레코드만 접근

불필요한 테이블 접근을 줄여 성능을 향상시킵니다.

4. 인덱스 확장 (use_index_extensions)

설명: InnoDB의 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 하는 옵션입니다.

근거: 세컨더리 인덱스 (b)는 내부적으로 (b, a) 형태로 작동합니다(a는 PK). 이를 명시적으로 활용하여 추가적인 정렬이나 필터링 최적화가 가능합니다.

5. 인덱스 머지 (index_merge)

설명: 하나의 테이블에 대해 2개 이상의 인덱스를 동시에 사용하여 쿼리를 처리하는 방식입니다.

근거: 각 조건이 서로 다른 인덱스를 사용하고, 각 조건을 만족하는 레코드가 많을 것으로 예상될 때 효과적입니다.

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

설명: 여러 인덱스를 각각 검색한 뒤 결과의 교집합을 반환합니다.

근거:

SELECT * FROM employees 
WHERE first_name='George' AND emp_no BETWEEN 10000 AND 20000;

두 조건 모두 인덱스가 있고 각각 많은 레코드를 반환한다면, 각각 검색 후 교집합하는 것이 더 효율적일 수 있습니다. 실행계획에서 Using intersect로 표시됩니다.

5-2. 인덱스 머지 - 합집합 (index_merge_union)

설명: 여러 인덱스를 각각 검색한 뒤 결과의 합집합을 반환합니다.

근거:

SELECT * FROM employees 
WHERE first_name='Matt' OR hire_date='2022-07-13';

OR 조건일 때 사용되며, 각 인덱스 결과가 프라이머리 키로 정렬되어 있어 우선순위 큐 알고리즘으로 효율적으로 중복 제거가 가능합니다. 실행계획에서 Using union으로 표시됩니다.

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

설명: 합집합 과정에서 결과가 정렬되어 있지 않을 때, 정렬 후 합집합을 수행합니다.

근거: 일반 union과 달리 정렬이 필요한 경우 사용됩니다.

6. 세미 조인 (semijoin)

설명: 실제로 조인을 수행하지 않고, 다른 테이블에 조건을 만족하는 레코드가 존재하는지만 확인하는 쿼리 형태입니다.

근거:

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

이런 IN (subquery) 형태의 쿼리를 최적화하기 위한 다양한 전략들이 있습니다.

6-1. 테이블 풀-아웃 (Table Pull-out)

설명: 서브쿼리에 사용된 테이블을 아우터 쿼리로 끌어내서 일반 조인으로 재작성합니다.

근거: IN (subquery)가 JOIN으로 변환됩니다. 실행계획에는 특별한 표시가 없으므로 SHOW WARNINGS로 재작성된 쿼리를 확인해야 합니다.

6-2. 퍼스트 매치 (firstmatch)

설명: IN (subquery)를 EXISTS (subquery) 형태로 변환한 것과 유사하게 실행됩니다.

근거: Extra 칼럼에 FirstMatch()가 표시됩니다.

6-3. 루스 스캔 (loosescan)

설명: Loose Index Scan과 유사한 방식으로 인덱스를 건너뛰며 읽습니다.

근거: 인덱스를 효율적으로 활용하여 불필요한 레코드 접근을 최소화합니다.

6-4. 구체화 (Materialization)

설명: 서브쿼리를 임시 테이블로 구체화(Materialization)해서 쿼리를 최적화합니다.

근거: semijoin과 materialization 옵션이 모두 ON일 때 활성화됩니다(기본값).

6-5. 중복 제거 (Duplicated Weed-out)

설명: 세미 조인을 일반 INNER JOIN으로 변환한 후 중복된 레코드를 제거합니다.

근거:

-- 원본 쿼리
SELECT * FROM employees e
WHERE e.emp_no IN (
    SELECT s.emp_no FROM salaries s 
    WHERE s.salary > 150000
);

-- 변환된 쿼리
SELECT e.*
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no AND s.salary > 150000
GROUP BY e.emp_no;

7. 컨디션 팬아웃 필터 (condition_fanout_filter)

설명: 조인 순서를 결정할 때 더 정교한 통계 정보를 활용하는 최적화 기법입니다.

근거: 실행계획의 rows와 filtered 칼럼을 통해 예상 결과 건수를 계산합니다(rows * filtered / 100). 다음 조건에서 더 정확한 계산이 가능합니다:

  • WHERE 조건의 칼럼에 인덱스가 있는 경우
  • WHERE 조건의 칼럼에 히스토그램이 있는 경우

8. 파생 테이블 머지 (derived_merge)

설명: FROM 절의 서브쿼리(파생 테이블)를 외부 쿼리와 병합하여 임시 테이블 생성을 제거합니다.

근거:

-- 원본 쿼리
SELECT * FROM (
    SELECT * FROM employees WHERE first_name='Matt'
) derived_table
WHERE derived_table.hire_date='2022-07-01';

-- 병합된 쿼리
SELECT * FROM employees
WHERE hire_date='2022-07-01' AND first_name='Matt';

MySQL 5.7부터 도입되었으며, SHOW WARNINGS로 병합된 쿼리를 확인할 수 있습니다.

9. 인비저블 인덱스 (use_invisible_indexes)

설명: 인덱스를 삭제하지 않고 사용하지 못하도록 제어할 수 있는 기능입니다.

근거: MySQL 8.0부터 ALTER TABLE ... ALTER INDEX ... [VISIBLE | INVISIBLE] 명령으로 인덱스 가용 상태를 제어할 수 있습니다.

10. 스킵 스캔 (skip_scan)

설명: 복합 인덱스에서 선행 칼럼 조건 없이 후행 칼럼만으로도 인덱스를 활용할 수 있게 하는 최적화 기법입니다.

근거:

ALTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date);

SELECT * FROM employees WHERE birth_date >= '1992-01-11';

기존에는 gender 조건 없이 birth_date만으로는 인덱스를 활용할 수 없었지만, MySQL 8.0부터 인덱스 스킵 스캔으로 가능합니다. 단, 선행 칼럼이 소수의 유니크한 값을 가질 때만 효과적입니다.

11. 해시 조인 (hash_join)

설명: 두 테이블을 조인할 때 작은 테이블로 해시 테이블을 만들고, 큰 테이블을 읽으며 매칭하는 방식입니다.

근거:

  • 빌드 단계: 레코드 건수가 적은 테이블로 해시 테이블 생성
  • 프로브 단계: 나머지 테이블의 레코드를 읽어 해시 테이블에서 일치 레코드 찾기

해시 조인은 최고 스루풋 전략(첫 레코드는 느리지만 전체 완료는 빠름)에 적합하고, 네스티드 루프 조인은 최고 응답 속도 전략(첫 레코드는 빠르지만 전체 완료는 느림)에 적합합니다.

12. 인덱스 정렬 선호 (prefer_ordering_index)

설명: ORDER BY나 GROUP BY를 처리할 수 있는 인덱스에 가중치를 부여하지 않도록 제어하는 옵션입니다.

근거: 기본적으로 MySQL 옵티마이저는 ORDER BY/GROUP BY를 인덱스로 처리할 수 있으면 해당 인덱스에 높은 가중치를 줍니다. 이 옵션을 OFF로 설정하면 이런 가중치 부여를 막을 수 있습니다.

전체 요약: 옵티마이저 스위치는 MySQL이 쿼리를 실행할 때 어떤 최적화 전략을 사용할지 세밀하게 제어할 수 있는 도구입니다. 대부분의 옵션은 기본값(ON)으로 두는 것이 좋지만, 특정 상황에서는 OFF로 설정하거나 명시적으로 제어하여 더 나은 성능을 얻을 수 있습니다.

 

 

9.3.2 조인 최적화 알고리즘 (전략 선택)

설명: 여러 테이블을 조인할 때 어떤 순서로 조인할지 결정하는 알고리즘입니다. 조인 순서는 쿼리 성능에 매우 큰 영향을 미치므로, MySQL 옵티마이저는 최적의 조인 순서를 찾기 위해 다양한 알고리즘을 사용합니다.

1. Exhaustive 검색 알고리즘 (완벽주의자)

설명: MySQL 5.0 이전에 사용되던 조인 최적화 기법으로, FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법입니다.

근거:

  • 테이블이 N개라면 N! (팩토리얼) 개의 조인 조합이 가능합니다.
  • 예시:
    • 테이블 3개: 3! = 6가지 조합
    • 테이블 4개: 4! = 24가지 조합
    • 테이블 10개: 10! = 3,628,800가지 조합

장점: 모든 가능한 조합을 검토하므로 이론적으로 가장 최적의 실행 계획을 찾을 수 있습니다.

단점: 테이블 개수가 많아지면 계산량이 폭발적으로 증가하여 실행 계획 수립 자체에 너무 많은 시간이 소요됩니다.

2. Greedy 검색 알고리즘 (실용주의자)

설명: MySQL 5.0부터 도입된 조인 최적화 기법으로, 단계별로 최선의 선택을 하며 부분적으로 실행 계획을 완성해 나가는 방법입니다.

근거 (실행 절차):

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

장점:

  • Exhaustive에 비해 훨씬 적은 조합만 검토하므로 실행 계획 수립 시간이 대폭 단축됩니다.
  • 테이블 개수가 많을 때 특히 유리합니다.

단점:

  • 매 단계에서 로컬 최적값(local optimum)을 선택하므로, 전역 최적값(global optimum)을 놓칠 가능성이 있습니다.
  • 그러나 실제로는 충분히 좋은 실행 계획을 찾아냅니다.

3. 관련 시스템 변수

optimizer_search_depth

설명: Greedy 검색 알고리즘과 Exhaustive 검색 알고리즘 중 어떤 알고리즘을 사용할지 결정하는 변수입니다.

근거:

  • 값이 0: MySQL 옵티마이저가 자동으로 적절한 값을 선택
  • 값이 1~62: 해당 개수만큼의 테이블 조합을 Greedy 방식으로 검토
  • 값이 테이블 개수와 같거나 큼: 사실상 Exhaustive 검색과 동일하게 작동

실무 팁: 대부분의 경우 기본값(자동)으로 두는 것이 좋습니다.

optimizer_prune_level

설명: Heuristic(휴리스틱) 검색이 작동하는 방식을 제어하는 변수입니다.

근거:

  • Heuristic 검색은 "경험적 규칙"을 사용하여 명백히 비효율적인 조인 조합을 미리 제거하는 기법입니다.
  • 이를 통해 검토해야 할 조합의 수를 더욱 줄일 수 있습니다.

값:

  • 1 (기본값): Heuristic 검색 활성화
  • 0: Heuristic 검색 비활성화

비교 요약

항목 Exhaustive Greedy

도입 시기 MySQL 5.0 이전 MySQL 5.0 이후
검색 방식 모든 조합 검토 단계별 최선 선택
정확도 최고 (이론적 최적해) 매우 높음 (실용적 최적해)
속도 느림 (N!) 빠름
테이블 많을 때 비실용적 효율적
사용 권장 테이블 수 적을 때만 대부분의 경우

전체 요약: MySQL은 조인 순서를 결정하기 위해 Exhaustive(완벽하지만 느림)에서 Greedy(빠르고 실용적)로 발전해왔습니다. 현대적인 MySQL에서는 Greedy 알고리즘이 기본이며, optimizer_search_depth와 optimizer_prune_level 변수를 통해 최적화 수준을 조절할 수 있습니다. 대부분의 경우 기본 설정을 사용하는 것이 가장 좋습니다.

 

9.4 쿼리 힌트

마지막으로 9.4 쿼리 힌트에 대해 정리해 드릴게요. 힌트는 옵티마이저가 최적의 길을 찾지 못할 때 개발자가 직접 "이 길로 가!"라고 알려주는 나침반 같은 역할입니다.

9.4.1 인덱스 힌트

과거 버전부터 사용되던 방식으로, 특정 인덱스를 사용하도록 유도하거나 금지할 때 씁니다.

  • 종류: USE INDEX(권장), FORCE INDEX(강한 권장), IGNORE INDEX(사용 금지)가 있습니다.
  • 특징: SQL 문법의 일부로 작성되므로 ANSI-SQL 표준은 아니며, 인덱스 이름을 잘못 적으면 문법 오류가 발생할 수 있습니다.
  • 위치: 조회하려는 테이블명 바로 뒤에 작성해야 합니다.

9.4 쿼리 힌트

설명: MySQL 옵티마이저는 통계 정보를 기반으로 최적의 실행 계획을 수립하지만, 비즈니스 로직이나 데이터 특성을 100% 이해하지 못할 수 있습니다. 이럴 때 개발자가 옵티마이저에게 실행 계획 수립 방법을 직접 알려주는 것이 쿼리 힌트입니다.

근거: 쿼리 힌트는 크게 두 가지로 분류됩니다:

  • 인덱스 힌트: MySQL 초기부터 사용되던 방식
  • 옵티마이저 힌트: MySQL 5.6부터 도입된 ANSI-SQL 표준을 준수하는 방식

9.4.1 인덱스 힌트

설명: 테이블 이름 뒤에 직접 명시하여 특정 인덱스의 사용을 제어하는 힌트입니다.

근거:

  • MySQL 인덱스 힌트는 ANSI-SQL 표준 문법을 준수하지 못합니다.
  • MySQL 5.6부터 추가된 옵티마이저 힌트는 ANSI-SQL 표준을 준수하여 다른 RDBMS에서는 주석으로 해석됩니다.
  • 따라서 가능하면 인덱스 힌트보다는 옵티마이저 힌트를 사용할 것을 권장합니다.
  • 인덱스 힌트는 SELECT 명령과 UPDATE 명령에서만 사용 가능합니다.

9.4.1.1 STRAIGHT_JOIN

설명: 옵티마이저가 FROM 절에 명시된 테이블의 순서대로 조인을 수행하도록 강제하는 힌트입니다.

근거:

기본적으로 다음과 같은 쿼리에서는 어느 테이블이 드라이빙/드리븐 테이블이 될지 옵티마이저가 결정합니다:

SELECT *
FROM t1, t2, t3
WHERE ...

조인 순서는 다음 기준으로 결정됩니다:

  • 조인 칼럼의 인덱스 여부
  • 레코드 수가 적은 테이블을 드라이빙 테이블로 선택

사용 방법:

-- 방법 1
SELECT STRAIGHT_JOIN
FROM t1, t2, t3
WHERE ...

-- 방법 2
SELECT /*! STRAIGHT_JOIN */
FROM t1, t2, t3
WHERE ...

유사한 옵티마이저 힌트:

  • JOIN_FIXED_ORDER: STRAIGHT_JOIN과 동일한 역할
  • JOIN_ORDER: 특정 테이블들의 조인 순서만 지정
  • JOIN_PREFIX: 조인의 첫 번째 테이블들을 지정
  • JOIN_SUFFIX: 조인의 마지막 테이블들을 지정

9.4.1.2 USE INDEX / FORCE INDEX / IGNORE INDEX

설명: 특정 인덱스를 사용하도록 권장하거나 강제하거나 무시하도록 하는 힌트입니다.

근거:

  • 3~4개 이상의 칼럼을 포함하는 비슷한 인덱스가 여러 개 존재하는 경우, 옵티마이저가 잘못된 선택을 할 수 있습니다.
  • 이런 경우 강제로 특정 인덱스를 사용하도록 힌트를 추가합니다.
  • 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 명시해야 합니다.

힌트 종류:

  1. USE INDEX: 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장
  2. SELECT * FROM employees USE INDEX (ix_firstname) WHERE first_name = 'Matt';
  3. FORCE INDEX: USE INDEX보다 더 강하게 사용을 요구 (거의 사용하지 않음)
  4. SELECT * FROM employees FORCE INDEX (ix_firstname) WHERE first_name = 'Matt';
  5. IGNORE INDEX: 특정 인덱스를 사용하지 못하게 금지
  6. SELECT * FROM employees IGNORE INDEX (ix_firstname) WHERE first_name = 'Matt';

인덱스 용도 명시:

특정 용도로만 인덱스를 사용하도록 제한할 수 있습니다:

-- JOIN과 레코드 검색에만 사용
SELECT * FROM employees USE INDEX FOR JOIN (ix_firstname)
WHERE first_name = 'Matt';

-- ORDER BY 처리에만 사용
SELECT * FROM employees USE INDEX FOR ORDER BY (ix_hiredate)
ORDER BY hire_date;

-- GROUP BY 처리에만 사용
SELECT dept_no, COUNT(*) 
FROM dept_emp USE INDEX FOR GROUP BY (ix_deptno)
GROUP BY dept_no;

참고: FOR JOIN 키워드는 조인뿐만 아니라 레코드 검색까지 포함합니다.

9.4.1.3 SQL_CALC_FOUND_ROWS

설명: LIMIT을 사용할 때, 제한된 건수만 찾고 멈추는 것이 아니라 끝까지 검색을 수행하여 전체 레코드 수를 계산하는 힌트입니다.

사용 방법:

SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;

-- 전체 레코드 수 확인
SELECT FOUND_ROWS() AS total_record_count;

❌ 이 힌트는 사용하면 안 됩니다!

근거:

SELECT SQL_CALC_FOUND_ROWS * 
FROM employees 
WHERE first_name='Georgi' 
LIMIT 0, 20;

SELECT FOUND_ROWS() AS total_record_count;

위 쿼리의 문제점:

  • LIMIT 조건으로 20건만 가져오려고 했지만, SQL_CALC_FOUND_ROWS 힌트 때문에 전체 레코드를 전부 읽어야 합니다.
  • 이로 인해 불필요한 랜덤 I/O가 발생합니다.
  • 페이징 처리 시 성능이 크게 저하됩니다.

대안 (권장 방법):

-- 1. 전체 건수 조회 (커버링 인덱스 활용 가능)
SELECT COUNT(*) FROM employees WHERE first_name = 'Georgi';

-- 2. 실제 데이터 조회
SELECT * FROM employees WHERE first_name='Georgi' LIMIT 0, 20;

장점:

  • 전체 건수는 커버링 인덱스 쿼리로 가져올 수 있어 인덱스 스캔만으로 해결 가능
  • 실제 레코드는 LIMIT 조건만큼만 읽으므로 랜덤 I/O 최소화
  • 쿼리 2개로 나뉘지만 전체 성능은 훨씬 우수합니다

요약 비교표

힌트 용도 강도 권장도

USE INDEX 특정 인덱스 사용 권장 약함 ⭐⭐⭐
FORCE INDEX 특정 인덱스 사용 강제 강함 ⭐ (거의 불필요)
IGNORE INDEX 특정 인덱스 사용 금지 - ⭐⭐
STRAIGHT_JOIN 조인 순서 강제 강함 ⭐⭐ (신중히)
SQL_CALC_FOUND_ROWS LIMIT과 전체 건수 동시 계산 - ❌ (사용 금지)

전체 요약:

  • 인덱스 힌트는 옵티마이저가 잘못된 실행 계획을 선택할 때 개발자가 개입할 수 있는 수단입니다.
  • 그러나 ANSI-SQL 표준을 준수하지 않으므로, 가능하면 MySQL 5.6 이후의 옵티마이저 힌트를 사용하는 것이 좋습니다.
  • SQL_CALC_FOUND_ROWS는 성능 문제를 일으키므로 절대 사용하지 말고, 전체 건수 조회와 데이터 조회를 분리하는 것이 권장됩니다.
  • 인덱스 힌트는 테이블명 바로 뒤에 작성해야 하며, SELECT와 UPDATE 명령에서만 사용 가능합니다.

 

9.4.2 옵티마이저 힌트

설명: MySQL 5.6부터 도입되어 8.0에서 더욱 강화된 현대적인 힌트 방식입니다. /*+ ... */ 형태의 주석으로 작성되어 ANSI-SQL 표준을 준수하며, 다른 DBMS에서는 주석으로 인식되어 무시되므로 호환성이 좋습니다.

근거: 인덱스 힌트와 달리 옵티마이저 힌트는:

  • 표준 SQL 문법을 준수
  • 쿼리의 어느 위치에든 작성 가능
  • 더욱 세밀한 제어 가능
  • 쿼리 블록별로 독립적인 제어 가능

9.4.2.1 옵티마이저 힌트 종류

설명: 옵티마이저 힌트는 영향 범위에 따라 4개 그룹으로 분류됩니다.

영향 범위별 분류

그룹 설명 특징

인덱스 수준 특정 인덱스의 이름을 사용하는 힌트 반드시 테이블명이 선행되어야 함
테이블 수준 특정 테이블의 이름을 사용하는 힌트 테이블별 최적화 제어
쿼리 블록 수준 특정 쿼리 블록에만 영향 힌트가 명시된 블록에만 적용
글로벌 수준 전체 쿼리에 영향 쿼리 전체의 동작 제어

쿼리 블록 이름 지정 (QB_NAME)

설명: 특정 쿼리 블록을 외부 쿼리 블록에서 참조하려면 QB_NAME() 힌트로 이름을 부여해야 합니다.

근거:

SELECT /*+ JOIN_ORDER(e, s@subq1) */
    COUNT(*)
FROM employees e
WHERE e.first_name = 'Matt'
AND e.emp_no IN (
    SELECT /*+ QB_NAME(subq1) */ s.emp_no
    FROM salaries s 
    WHERE s.salary BETWEEN 50000 AND 50500
);

위 예시에서:

  • 서브쿼리에 QB_NAME(subq1)로 이름 부여
  • 외부 쿼리에서 s@subq1로 서브쿼리의 테이블 참조
  • 외부 쿼리와 서브쿼리의 조인 순서를 제어 가능

9.4.2.2 MAX_EXECUTION_TIME

설명: 쿼리의 최대 실행 시간을 설정하여 무한 루프나 과부하를 방지하는 힌트입니다.

근거:

SELECT /*+ MAX_EXECUTION_TIME(3000) */ *
FROM employees
WHERE first_name = 'Matt';
  • 단위: 밀리초 (위 예시는 3초)
  • 초과 시: ERROR 3024: Query execution was interrupted, maximum statement execution time exceeded 발생
  • 용도: 장시간 실행되는 쿼리로 인한 시스템 부하 방지

9.4.2.3 SET_VAR

설명: 해당 쿼리가 실행되는 동안만 특정 시스템 변수를 일시적으로 변경하는 힌트입니다.

근거:

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ *
FROM employees
ORDER BY last_name, first_name;

용도:

  • 실행 계획 조정
  • 조인 버퍼 크기 증가 (join_buffer_size)
  • 정렬용 버퍼 크기 증가 (sort_buffer_size)
  • 대용량 처리 쿼리의 성능 향상

장점: 전역 시스템 변수를 변경하지 않고도 특정 쿼리에만 적용 가능

9.4.2.4 SEMIJOIN & NO_SEMIJOIN

설명: 세미 조인 최적화 방식을 제어하는 힌트입니다. (9.3.1.9절 세미 조인 참조)

근거:

최적화 방식 힌트

Duplicate Weed-out SEMIJOIN(DUPSWEEDOUT)
First Match SEMIJOIN(FIRSTMATCH)
Loose Scan SEMIJOIN(LOOSESCAN)
Materialization SEMIJOIN(MATERIALIZATION)
Table Pull-out 힌트 없음 (자동 선택)

중요: 세미 조인 최적화 힌트는 외부 쿼리가 아니라 서브쿼리에 명시해야 합니다.

SELECT *
FROM employees e
WHERE e.emp_no IN (
    SELECT /*+ SEMIJOIN(MATERIALIZATION) */ s.emp_no
    FROM salaries s
    WHERE s.salary > 100000
);

9.4.2.5 SUBQUERY

설명: 세미 조인 최적화가 사용되지 못할 때 사용하는 서브쿼리 최적화 방법입니다.

근거: 다음 2가지 형태로 최적화할 수 있습니다:

  • IN-to-EXISTS: SUBQUERY(INTOEXISTS)
  • Materialization: SUBQUERY(MATERIALIZATION)

사용 상황:

  • 세미 조인 최적화는 주로 IN(subquery) 형태에 사용
  • 안티 세미 조인 (NOT IN, NOT EXISTS) 최적화에는 세미 조인을 사용할 수 없음
  • 따라서 안티 세미 조인에는 주로 SUBQUERY 힌트가 사용됨
SELECT *
FROM employees e
WHERE e.emp_no NOT IN (
    SELECT /*+ SUBQUERY(MATERIALIZATION) */ s.emp_no
    FROM salaries s
    WHERE s.salary > 100000
);

9.4.2.6 BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

설명: 조인 알고리즘을 제어하는 힌트입니다.

근거:

  • MySQL 8.0.19 이전: 블록 네스티드 루프(BNL) 조인 알고리즘 사용
  • MySQL 8.0.20 이후: 해시 조인 알고리즘으로 대체
  • 그럼에도 BNL, NO_BNL 힌트는 여전히 사용 가능 (하위 호환성)
-- 해시 조인 강제
SELECT /*+ HASHJOIN(e, d) */ *
FROM employees e, dept_emp d
WHERE e.emp_no = d.emp_no;

-- 해시 조인 방지
SELECT /*+ NO_HASHJOIN(e, d) */ *
FROM employees e, dept_emp d
WHERE e.emp_no = d.emp_no;

9.4.2.7 JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

설명: 조인 순서를 제어하는 힌트들로, 전통적인 STRAIGHT_JOIN의 단점을 보완합니다.

근거:

STRAIGHT_JOIN의 단점:

  1. FROM 절의 테이블 순서를 직접 변경해야 하는 번거로움
  2. 한 번 사용하면 모든 테이블의 조인 순서가 결정됨
  3. 일부만 강제하고 나머지는 옵티마이저에게 맡기는 것이 불가능

옵티마이저 힌트로 개선:

힌트 설명 사용 예시

JOIN_FIXED_ORDER STRAIGHT_JOIN과 동일 (모든 순서 고정) /*+ JOIN_FIXED_ORDER() */
JOIN_ORDER 힌트에 명시된 테이블 순서대로 조인 /*+ JOIN_ORDER(t1, t2, t3) */
JOIN_PREFIX 드라이빙 테이블(첫 번째 테이블들)만 강제 /*+ JOIN_PREFIX(t1, t2) */
JOIN_SUFFIX 드리븐 테이블(마지막 테이블들)만 강제 /*+ JOIN_SUFFIX(t3, t4) */

예시:

-- 드라이빙 테이블만 지정, 나머지는 옵티마이저가 결정
SELECT /*+ JOIN_PREFIX(employees) */ *
FROM employees e, dept_emp de, departments d
WHERE e.emp_no = de.emp_no
AND de.dept_no = d.dept_no;

-- 마지막 조인 테이블만 지정
SELECT /*+ JOIN_SUFFIX(departments) */ *
FROM employees e, dept_emp de, departments d
WHERE e.emp_no = de.emp_no
AND de.dept_no = d.dept_no;

9.4.2.8 MERGE & NO_MERGE

설명: FROM 절의 서브쿼리(파생 테이블)를 외부 쿼리와 병합할지 결정하는 힌트입니다.

근거:

-- 서브쿼리를 임시 테이블로 생성하지 않고 병합
SELECT /*+ MERGE(derived_table) */ *
FROM (
    SELECT * FROM employees WHERE first_name = 'Matt'
) derived_table
WHERE hire_date = '2022-07-01';

-- 서브쿼리를 임시 테이블로 생성 (병합 방지)
SELECT /*+ NO_MERGE(derived_table) */ *
FROM (
    SELECT * FROM employees WHERE first_name = 'Matt'
) derived_table
WHERE hire_date = '2022-07-01';

용도:

  • MERGE: 임시 테이블 생성 오버헤드 제거
  • NO_MERGE: 서브쿼리 결과를 먼저 구체화하여 성능 향상

9.4.2.9 INDEX_MERGE & NO_INDEX_MERGE

설명: 하나의 테이블에 대해 여러 인덱스를 동시에 사용하는 인덱스 머지를 제어하는 힌트입니다.

근거:

  • MySQL 서버는 가능하면 테이블당 하나의 인덱스만 사용하려 함
  • 검색 대상을 충분히 좁힐 수 없으면 여러 인덱스 결과의 교집합/합집합 사용
-- 인덱스 머지 강제
SELECT /*+ INDEX_MERGE(employees ix_firstname, ix_hiredate) */ *
FROM employees
WHERE first_name = 'Matt' OR hire_date = '2022-07-01';

-- 인덱스 머지 방지 (하나의 인덱스만 사용)
SELECT /*+ NO_INDEX_MERGE(employees) */ *
FROM employees
WHERE first_name = 'Matt' OR hire_date = '2022-07-01';

9.4.2.10 NO_ICP

설명: 인덱스 컨디션 푸시다운(ICP) 최적화를 비활성화하는 힌트입니다.

근거:

  • ICP는 거의 항상 성능 향상에 도움이 되므로 옵티마이저가 자동으로 사용
  • 따라서 ICP 힌트는 제공되지 않고, NO_ICP만 제공
  • 실행 계획 비용 계산이 잘못되었을 경우 비활성화하여 다른 실행 계획 선택 유도
SELECT /*+ NO_ICP(employees ix_firstname) */ *
FROM employees
WHERE last_name = 'Acton' AND first_name LIKE '%sal';

9.4.2.11 SKIP_SCAN & NO_SKIP_SCAN

설명: 인덱스 스킵 스캔 최적화를 제어하는 힌트입니다.

근거:

  • 인덱스 스킵 스캔은 선행 칼럼 조건 없이도 후행 칼럼으로 인덱스 활용 가능
  • 단점: 선행 칼럼의 유니크한 값이 많아지면 성능 저하
  • MySQL 옵티마이저가 비효율적인 스킵 스캔을 선택할 경우 NO_SKIP_SCAN으로 방지
-- 인덱스 (gender, birth_date)에서 birth_date만으로 검색
SELECT /*+ SKIP_SCAN(employees ix_gender_birthdate) */ *
FROM employees
WHERE birth_date >= '1992-01-01';

-- 스킵 스캔 방지
SELECT /*+ NO_SKIP_SCAN(employees ix_gender_birthdate) */ *
FROM employees
WHERE birth_date >= '1992-01-01';

9.4.2.12 INDEX & NO_INDEX

설명: 이전의 인덱스 힌트(USE INDEX, FORCE INDEX, IGNORE INDEX)를 대체하는 옵티마이저 힌트입니다.

근거:

인덱스 힌트와의 차이점:

  • 인덱스 힌트: 테이블 뒤에 위치, 인덱스 이름만 명시
  • 옵티마이저 힌트: 주석 형태, 테이블명과 인덱스 이름 함께 명시

비교 예시:

-- 인덱스 힌트 (구 방식)
SELECT *
FROM employees USE INDEX(ix_firstname)
WHERE first_name = 'Matt';

-- 옵티마이저 힌트 (신 방식)
SELECT /*+ INDEX(employees ix_firstname) */ *
FROM employees
WHERE first_name = 'Matt';

-- 인덱스 사용 금지
SELECT /*+ NO_INDEX(employees ix_firstname) */ *
FROM employees
WHERE first_name = 'Matt';

장점:

  • ANSI-SQL 표준 준수
  • 쿼리 어느 위치에든 작성 가능
  • 여러 테이블의 인덱스를 한 번에 제어 가능

요약 비교표

카테고리 힌트 용도

실행 제어 MAX_EXECUTION_TIME 쿼리 최대 실행 시간 설정
  SET_VAR 시스템 변수 일시 변경
조인 순서 JOIN_FIXED_ORDER 모든 테이블 순서 고정
  JOIN_ORDER 지정한 테이블 순서로 조인
  JOIN_PREFIX 드라이빙 테이블만 지정
  JOIN_SUFFIX 드리븐 테이블만 지정
서브쿼리 SEMIJOIN 세미 조인 방식 선택
  SUBQUERY 서브쿼리 최적화 방식 선택
  MERGE / NO_MERGE 파생 테이블 병합 제어
인덱스 INDEX / NO_INDEX 인덱스 사용 제어
  INDEX_MERGE 여러 인덱스 동시 사용
  SKIP_SCAN 인덱스 스킵 스캔 제어
  NO_ICP ICP 최적화 비활성화
조인 방식 HASHJOIN 해시 조인 제어
  BNL 블록 네스티드 루프 조인

전체 요약:

옵티마이저 힌트는 MySQL 5.6부터 도입된 현대적인 힌트 방식으로, 다음과 같은 장점이 있습니다:

  1. ANSI-SQL 표준 준수: /*+ ... */ 주석 형태로 다른 DBMS와 호환
  2. 세밀한 제어: 인덱스, 테이블, 쿼리 블록, 글로벌 수준까지 다양한 범위 제어
  3. 유연성: 쿼리 블록별로 독립적인 최적화 가능
  4. 명확성: 테이블명과 인덱스명을 함께 명시하여 가독성 향상

권장 사항: 가능한 한 인덱스 힌트보다는 옵티마이저 힌트를 사용하는 것이 좋습니다.

728x90
반응형
저작자표시 비영리 (새창열림)

'데이터 베이스 기초' 카테고리의 다른 글

10장: 실행 계획  (0) 2026.01.06
mysql 인덱스  (0) 2025.12.22
oracle 기본 데이터 형 및 기타 내용  (2) 2024.04.03
데이터 베이스 설치들  (0) 2024.04.02
Union, GROUP BY / HAVING, Join, GRANT  (0) 2024.04.01

관련글

티스토리툴바