🎱 옵티마이저

SQL 쿼리를 가장 효율적으로 실행하기 위해 DB가 내부적으로 실행 계획을 최적화하는 컴포넌트

• 동일한 결과를 반환하는 여러 실행 경로 중 가장 빠른 방법을 선택
• 비용(Cost), 통계(Statistics), 인덱스 정보 등을 바탕으로 판단 ➡️ 옵티마이저가 엉뚱한 선택을 하지 않도록 하기 위해 인덱스 설계, 통계 수집, 정규화 등이 중요
  
• 같은 쿼리문도 옵티마이저 선택에 따라 수초~수분 차이가 날 수 있음
• 어떤 인덱스를 쓰는지, 어떤 JOIN 순서를 선택하는지 모두 옵티마이저가 결정

🎱 작동 기반 요소

⚽️ 비용

옵티마이저의 실행 계획을 비교하는 기준

항목	설명
I/O 비용	디스크에서 데이터를 읽어오는 비용 (랜덤/순차 접근 등)
CPU 비용	정렬, 필터링, 조인 연산 등에 소요되는 계산량
메모리 사용량	정렬, 해시 조인 등에 필요한 메모리 공간
네트워크 비용 (분산 DB)	노드 간 데이터 전송량

• 기본적으로 작은 테이블 먼저 읽고, 선택도가 높은 조건은 인덱스 이용하면 비용이 낮아진다고 판단

⚽️ 통계

옵티마이저가 실행 계획을 세울 때 참고하는 테이블/인덱스/컬럼에 대한 메타 정보

항목	설명
테이블의 총 행 수	COUNT(*) 수준의 추정값
인덱스의 유니크도(Cardinality)	특정 컬럼값이 얼마나 다양하게 분포돼 있는지
NULL 여부	특정 컬럼에 NULL이 얼마나 있는지
값의 범위	MIN, MAX 값 등
데이터 분포 히스토그램 (MySQL 8+)	특정 컬럼값이 어떻게 분포돼 있는지 상세히 기록

• 어떤 인덱스를 사용할지
• 어떤 테이블을 먼저 읽을지 (JOIN 순서)
• Index Scan vs Full Table Scan 선택
• 병합 가능한 인덱스가 있는지

⚽️ 인덱스

옵티마이저가 접근 경로를 결정하는 요소

항목	설명
인덱스 존재 여부	해당 컬럼에 인덱스가 존재하는가?
인덱스 유니크도	유니크 인덱스일수록 효율 ↑
인덱스 범위 조건	=, BETWEEN, LIKE 'abc%' 는 인덱스 활용 가능
다중 컬럼 인덱스	복합 인덱스의 선두 컬럼을 조건으로 쓸 수 있는가?
인덱스 커버 여부	SELECT 대상 컬럼이 모두 인덱스에 포함돼 있으면 → 커버링 인덱스

• 선택도가 높다 = 조건이 정확하고 좁다 = 인덱스 사용에 적합하다
• 선택도가 낮다 = 너무 많은 행이 걸린다 = 인덱스 의미가 줄어든다

🎱 작동 원리 및 처리 순서

1. 파싱

• SQL 문장을 읽고 문법적 구조를 분석하여 내부 트리(Tree)로 변환
• 이 단계는 실행 계획 생성 이전의 전처리 단계

2. 전처리 및 구조화

• 뷰, 서브쿼리, JOIN 등이 포함된 경우 내부적으로 재구성
• 쿼리 리라이팅(query rewriting)도 이 시점에 일부 일어남

3. 통계 정보 분석

• 각 테이블의 행 수, 인덱스 정보, 컬럼 분포도 등 참조
• 옵티마이저는 이 통계를 기반으로 비용(cost) 을 계산
  ➡️ 통계를 최신 상태로 유지하는 것이 중요

4. 실행 계획 후보 생성

• 가능한 여러 JOIN 순서, 인덱스 사용 여부, 접근 방식 등을 조합해 실행 계획 후보 생성

5. 비용 기반 평가

• 각 실행 계획의 예상 I/O 비용, CPU 비용 등을 계산
  ➡️ 가장 낮은 비용의 계획 선택
• 인덱스 여부도 반영해서 실제 접근 방식 결정

6. 선택된 실행 계획 실행

• 선택된 실행 계획이 실행기로 전달됨
• 여기서부터 실제 쿼리 실행이 시작됨

🎱 활용

⚽️ 실행 계획 확인

EXPLAIN SELECT * FROM Employees WHERE Department = 'Sales';

• EXPLAIN 뒤에 오는 쿼리의
• 어떤 인덱스를 썼는지
• 어떤 테이블이 먼저 읽혔는지
• 조인 방식은 무엇인지 (Nested Loop / Hash Join 등)
• type, key, rows, Extra 등을 확인
• EXPLAIN ANALYZE는 MySQL 8.0+에서 예상 비용과 실제 실행 정보까지 보여줌

⚽️ 옵티마이저 힌트

SELECT /*+ INDEX(Employees dept_idx) */ * 
FROM Employees 
WHERE Department = 'Sales';

• /*+ HINT */ 형태로 옵티마이저의 동작을 유도
• 힌트 종류
  • INDEX(table index_name) 특정 인덱스 사용하도록 유도
    
  • NO_INDEX_MERGE() 인덱스 병합 사용 안 함
    
  • STRAIGHT_JOIN JOIN 순서 강제 지정
    

⚽️ 통계 갱신

ANALYZE TABLE Employees;

• ANALYZE TABLE 로 테이블에 대해 실행 계획용 통계를 다시 계산
• 필요 상황

  • 대량 데이터 INSERT/DELETE 이후

  • 인덱스 추가 후에도 성능 변화 없음

  • 쿼리 성능이 갑자기 나빠짐

  • 동일 쿼리인데 실행 시간에 편차가 큼

    ➡️ 트래픽 적은 시간대에 실행!

⚽️ JOIN 순서 지정

SELECT *
FROM A
STRAIGHT_JOIN B ON A.id = B.a_id;

SELECT /*+JOIN_FIXED_ORDER */ *
FROM A
JOIN B ON A.id = B.a_id
JOIN C ON C.id = A.a_id;

• STRAIGHT_JOIN
  • A → B 작성 순서대로 조인
  • SQL 키워드
  • 첫번째 JOIN 순서만 지정
  • 전통적으로 사용 가능
• /*+ JOIN_FIXED_ORDER */
  • A → B → C 작성 순서대로 조인
  • 힌트 형식
  • 모든 JOIN 순서를 지정
  • MySQL 8.0+
• 테이블 2개 이상을 JOIN 할 때, “어떤 테이블을 먼저 읽느냐”에 따라 전체 쿼리 성능이 크게 달라짐
  • 작은 테이블을 먼저 읽고 이에 큰 테이블을 매칭하는 것이 효율적
  • JOIN 키에 인덱스가 있는지, 얼마나 잘 좁혀지는지 (선택도)
  • WHERE 조건이 있는 테이블을 먼저 읽는 것이 효율적일 수 있음