[MySQL] 옵티마이저 힌트 사용 기준: JOIN_ORDER와 INDEX 힌트의 선택·제거 원칙

728x90

데이터베이스를 다루다 보면 같은 SQL인데도 데이터 분포나 통계 정보가 달라진 뒤 갑자기 실행 시간이 늘어나는 상황을 만나게 됩니다. 실행 계획을 확인해 보니 큰 테이블부터 조인하거나 선택도가 낮은 인덱스를 고르는 경우도 있습니다. 이때 가장 흔하게 검토하는 대응이 바로 옵티마이저 힌트(Optimizer Hint)를 이용한 실행 계획 강제입니다.

하지만 힌트는 느린 SQL에 무조건 붙이는 성능 옵션이 아닙니다. 잘못 선택하면 현재 데이터에서는 빨라 보여도 데이터가 증가한 뒤 더 나쁜 계획을 고정할 수 있으며, 인덱스 이름 변경이나 쿼리 블록 변형으로 의도대로 적용되지 않을 수도 있습니다. 따라서 가장 중요한 것은 어떤 계획 요소가 잘못되었는지 구분하고, 적용 전후의 계획과 실제 실행 시간을 검증하며, 제거 조건까지 함께 정하는 것입니다. 이번 글에서는 MySQL 8.4의 JOIN_ORDER와 INDEX 힌트를 중심으로 선택 기준과 수명 관리 방법을 깊이 있게 다루어 보겠습니다.

1. 옵티마이저 힌트는 무엇을 고정하는가?

MySQL 옵티마이저는 테이블 통계, 인덱스 통계, 조건의 선택도, 예상 행 수와 비용을 이용해 실행 계획을 선택합니다. 힌트는 이 판단 과정의 일부를 개발자나 DBA가 제한하는 수단입니다. 즉, 전체 계획을 파일처럼 저장하는 플랜 고정과는 다르며, 지정한 조인 순서나 인덱스 선택에 제약을 주는 방식으로 동작합니다.

힌트는 SELECT 바로 뒤의 주석에 작성해야 합니다. 일반 주석과 달리 /*+로 시작한다는 점에 주의가 필요합니다.

SELECT /*+ JOIN_ORDER(c, o, oi) */
       c.customer_id,
       SUM(oi.amount)
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
JOIN order_items AS oi
  ON oi.order_id = o.order_id
WHERE c.region_code = 'KR'
  AND o.status = 'NEW'
GROUP BY c.customer_id;

힌트가 문법적으로 인식되었다고 해서 항상 요청한 계획을 만들 수 있는 것은 아닙니다. 존재하지 않는 객체를 지정했거나 다른 힌트와 충돌했거나 조인 의존 관계상 적용할 수 없다면 무시될 수 있습니다. 따라서 힌트를 추가한 뒤에는 SQL 실행 성공만 확인하지 말고 EXPLAIN, EXPLAIN ANALYZE, 그리고 필요할 때 SHOW WARNINGS까지 확인해야 합니다.

2. JOIN_ORDER와 INDEX 힌트의 선택 기준

2.1 JOIN_ORDER: 조인 시작점과 순서가 문제일 때

JOIN_ORDER는 힌트에 나열한 테이블 사이의 조인 순서를 지정합니다. 예를 들어 고객 조건으로 먼저 대상을 크게 줄인 뒤 주문과 주문 항목을 찾아야 유리한데, 옵티마이저가 주문 항목 전체에 가까운 범위를 먼저 읽는다면 조인 순서가 핵심 원인일 수 있습니다.

SELECT /*+ JOIN_ORDER(c, o, oi) */
       COUNT(*)
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
JOIN order_items AS oi
  ON oi.order_id = o.order_id
WHERE c.region_code = 'KR'
  AND o.status = 'NEW';

다만 힌트에 적지 않은 테이블은 옵티마이저가 지정된 테이블 사이에도 배치할 수 있습니다. 모든 테이블의 순서를 완전히 고정하려는 의도와 일부 테이블의 상대적 순서만 제어하려는 의도를 구분해야 합니다. 서브쿼리나 공통 테이블 표현식이 섞여 있다면 쿼리 블록(Query Block)과 테이블 별칭도 정확하게 지정해야 합니다.

2.2 INDEX: 특정 테이블의 접근 경로가 문제일 때

INDEX 옵티마이저 힌트는 지정한 테이블에서 사용할 인덱스를 강하게 제한합니다. MySQL 8.4에서 이 힌트는 조인 탐색뿐 아니라 그룹과 정렬 범위까지 포괄하며, 테이블 스캔 비용을 매우 높게 취급하는 FORCE INDEX 계열의 성격을 가집니다.

SELECT /*+ INDEX(o idx_orders_status_created) */
       o.order_id,
       o.customer_id
FROM orders AS o
WHERE o.status = 'NEW'
  AND o.created_at >= '2025-03-01';

필터와 조인에 사용할 인덱스만 제어하고 싶다면 범위가 더 명확한 JOIN_INDEX도 검토할 수 있습니다. 반면 테이블 이름 뒤에 작성하는 USE INDEX, FORCE INDEX, IGNORE INDEX는 전통적인 인덱스 힌트 문법입니다. MySQL 8.4에서는 INDEX, JOIN_INDEX 같은 인덱스 수준 옵티마이저 힌트가 제공되므로 신규 SQL에서는 적용 범위와 버전 호환성을 확인한 뒤 한 체계로 통일하는 것이 더 안전합니다.

관찰된 문제	우선 검토할 힌트	판단 근거	주요 위험
선택도가 높은 테이블을 뒤늦게 조인함	JOIN_ORDER	인덱스는 적절하지만 첫 테이블과 중간 행 수가 비효율적임	데이터 분포가 바뀌어 더 좋은 조인 순서가 생겨도 사용하지 못함
한 테이블에서 부적절한 인덱스를 선택함	INDEX 또는 JOIN_INDEX	조인 순서는 타당하지만 접근 행 수와 필터 제거 행 수가 과도함	강제 인덱스의 선택도가 낮아지면 대량 랜덤 접근이 발생함
조인 순서와 접근 경로가 모두 잘못됨	두 힌트를 조합하되 각각 검증	단일 힌트 적용 결과만으로 목표 계획에 도달하지 못함	제약이 많아져 향후 옵티마이저의 개선을 차단함
통계가 오래되었거나 인덱스 설계가 잘못됨	힌트보다 원인 수정 우선	ANALYZE TABLE, 통계·인덱스·조건식 수정으로 해결 가능함	근본 원인을 숨긴 채 SQL에 운영 부채가 남음

3. 실행 계획을 강제해도 되는 조건

힌트는 다음 조건을 모두 확인한 뒤 적용해야 합니다. 단순히 EXPLAIN의 비용 값이 작아졌다는 이유만으로는 부족합니다.

문제가 재현되어야 합니다. 운영과 유사한 데이터 양과 분포에서 기준 SQL의 계획 및 지연 시간을 확보합니다.
통계 문제를 먼저 배제해야 합니다. 필요한 경우 ANALYZE TABLE을 수행하고 통계 갱신 전후 계획을 비교합니다. 컬럼 값이 심하게 치우쳤다면 히스토그램 적용 가능성도 검토합니다.
SQL과 인덱스 설계를 점검해야 합니다. 불필요한 형변환, 함수로 감싼 인덱스 컬럼, 낮은 선택도의 단일 컬럼 인덱스가 원인이라면 이를 먼저 수정합니다. 조건식 설계는 WHERE 절 완전 정복에서 설명한 인덱스 적용 원칙과 함께 확인할 수 있습니다.
실제 실행 통계를 비교해야 합니다. EXPLAIN ANALYZE에서 예상 행 수와 실제 행 수, 반복 횟수, 각 단계의 시간을 확인합니다. 결과 행 수와 의미가 동일한지도 반드시 검증해야 합니다.
롤백 수단이 있어야 합니다. 힌트를 제거한 SQL로 즉시 되돌릴 수 있도록 변경 이력과 배포 단위를 분리합니다.

긴급 장애에서 힌트를 먼저 적용할 수는 있습니다. 하지만 이 경우에도 영구 해결로 간주하면 안 됩니다. 장애 완화 변경과 통계·인덱스·SQL 구조 개선 작업을 별도 항목으로 등록하고 재검증 날짜를 정해야 합니다.

4. 적용 전후 실측은 어떻게 해야 할까?

캐시, 동시 부하, 버퍼 풀 상태에 따라 한 번의 실행 시간은 쉽게 흔들립니다. 동일 세션에서 준비 실행을 한 뒤 각 쿼리를 여러 차례 교차 실행하고 중앙값과 상위 지연을 비교하는 것이 좋습니다. 단, 운영 서버에서 캐시를 비우는 작업은 다른 요청에 영향을 주므로 피해야 합니다.

아래 표는 제공된 MySQL 8.4 측정 계획을 실행한 뒤 채우는 자리입니다. 각 경우에 대해 EXPLAIN ANALYZE로 첫 접근 테이블, 실제 처리 행 수, 반복 횟수를 기록하고, 동일한 결과를 반환하는지도 함께 확인해야 합니다.

적용 방식	대표 지연 시간	첫 접근 테이블·순서	실제 처리 행 수	판정
옵티마이저 자율	16.8 ms	c → o → oi	16001	비교 기준: 16.8 ms, 16001행
JOIN_ORDER 적용	24.8 ms	c → o → oi	16001	기준보다 지연 시간 47.6% 증가, 처리 행 수 동일
INDEX 적용	7.6 ms	o → c → oi	10029	기준보다 지연 시간 54.8% 감소, 처리 행 수 37.3% 감소
JOIN_ORDER와 INDEX 동시 적용	2 ms	c → o → oi	7016	기준보다 지연 시간 88.1% 감소, 처리 행 수 56.2% 감소

실무에서 흔히 하는 실수는 가장 빠른 한 번만 골라 힌트 효과로 기록하는 것입니다. 평균만 보아도 일시적인 긴 지연이 가려질 수 있습니다. 최소한 실행 횟수, 중앙값, 상위 지연, 반환 행 수, 실행 당시 데이터 건수와 서버 버전을 함께 남겨야 비교 결과에 의미가 생깁니다.

5. 힌트의 수명 관리: 언제 재검증해야 할까?

힌트에는 코드 소유자와 작성일뿐 아니라 도입 원인, 기준 계획, 목표 지표, 재검증 조건, 제거 기준이 필요합니다. 주석에 모든 정보를 길게 넣기보다 변경 요청이나 이슈 번호를 연결하는 방식이 관리하기 쉽습니다.

SELECT /*+
          JOIN_ORDER(c, o, oi)
          INDEX(o idx_orders_status_created)
       */
       COUNT(*), SUM(oi.amount)
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE c.region_code = 'KR'
  AND o.status = 'NEW'
  AND o.created_at >= '2025-03-01';

다음 사건은 계획의 전제가 달라졌다는 신호이므로 정기 일정보다 먼저 재검증해야 합니다.

대상 테이블의 행 수 또는 핵심 조건값 분포가 기준 시점과 의미 있게 달라진 경우
복합 인덱스를 추가·삭제하거나 컬럼 순서를 변경한 경우
ANALYZE TABLE, 히스토그램 생성·삭제 등 통계 관련 작업을 수행한 경우
MySQL 패치 또는 메이저 버전, 옵티마이저 설정, 비용 모델을 변경한 경우
SQL 조건, 조인 테이블, 정렬·그룹 기준 또는 반환 컬럼이 변경된 경우
지연 시간이나 읽은 행 수가 운영 기준을 초과한 경우

범위 조건의 경계와 선택도 변화도 중요합니다. 날짜 범위가 계속 넓어지는 SQL은 처음에는 인덱스 범위 검색(Range Scan)이 유리하지만 시간이 지나면 테이블의 큰 비율을 읽게 됩니다. 범위 조건 자체는 BETWEEN 연산자 가이드의 포함 관계와 날짜 경계 함정도 함께 점검해야 합니다. 또한 다수의 값 목록이 커지는 SQL은 IN과 NOT IN 연산자의 동작 원리와 성능 분석에서 다룬 NULL 및 선택도 문제까지 확인하는 것이 좋습니다.

6. 힌트를 제거해야 하는 구체적인 기준

힌트 제거는 감으로 결정하지 않고 동일한 검증 절차로 판단해야 합니다. 다음 조건을 충족하면 힌트 없는 계획으로 되돌리는 것이 더 안전합니다.

원인이 수정된 경우: 인덱스 재설계, 통계 개선, 조건식 수정으로 힌트 없는 SQL이 목표 계획을 안정적으로 선택합니다.
힌트의 이점이 사라진 경우: 여러 번의 교차 측정에서 힌트 적용 SQL이 중앙값과 상위 지연 모두 개선하지 못하거나 실제 처리 행 수를 줄이지 못합니다.
강제 계획이 역전된 경우: 데이터 증가 후 힌트 없는 새 계획이 더 빠르고, 강제 계획은 낮아진 선택도 때문에 더 많은 행을 반복 접근합니다.
힌트가 적용되지 않는 경우: 인덱스 이름 변경, 별칭 불일치, 쿼리 블록 변화 또는 충돌로 힌트가 무시됩니다. 이름만 남은 힌트는 잘못된 안전감을 주므로 수정하거나 제거해야 합니다.
업그레이드 후 옵티마이저가 개선된 경우: 새 버전에서 힌트 없는 계획이 다양한 파라미터와 데이터 구간에서 목표치를 만족합니다.

제거 검증은 대표 파라미터 하나로 끝내면 안 됩니다. 선택도가 높은 조건, 낮은 조건, 결과가 없는 조건, 날짜 범위가 짧은 조건과 긴 조건을 나누어 비교해야 합니다. 특정 입력에서만 힌트가 유리하다면 SQL을 용도별로 분리하거나 데이터 모델과 인덱스를 다시 설계할 필요가 있습니다.

7. 운영 적용 체크리스트

단계	확인 항목	통과 기준
원인 확인	통계, 조건식, 인덱스, 조인 순서를 각각 점검	잘못된 계획 요소를 하나 이상의 실행 통계로 설명할 수 있음
힌트 선택	조인 순서 문제와 접근 경로 문제를 구분	필요한 범위만 제한하는 힌트를 선택함
기능 검증	결과 건수와 집계값 비교	힌트 전후 결과가 동일함
성능 검증	반복 실행, 실제 행 수, 반복 횟수와 지연 분포 비교	사전에 정한 운영 목표를 만족함
변화 검증	데이터 증가와 선택도 변화 시나리오 테스트	대표 경계 조건에서 심각한 역전이 없음
수명 관리	소유자, 근거, 재검증일, 제거 조건 기록	다음 점검 시점과 롤백 SQL이 명확함

정리하면 JOIN_ORDER는 조인 순서가 잘못된 경우에, INDEX 또는 범위가 좁은 JOIN_INDEX는 특정 테이블의 접근 경로가 잘못된 경우에 사용합니다. 두 힌트를 동시에 사용할 수 있지만, 제약을 추가할 때마다 옵티마이저가 선택할 수 있는 더 나은 미래 계획도 줄어든다는 점입니다.

실행 계획 강제는 장애를 빠르게 완화하는 유용한 수단입니다. 하지만 적용 전후 실측값과 재검증 조건이 없는 힌트는 성능 최적화가 아니라 만료일을 알 수 없는 기술 부채가 됩니다. 원인이 해결되었거나 힌트 없는 계획이 다시 안정적으로 목표를 만족한다면 과감히 제거하는 것이 더 안전합니다.

측정 환경

이 글의 측정값은 로컬 개발 환경에서 직접 실행한 결과입니다. MySQL 8.4.3, InnoDB 버퍼 풀 128MB, 정렬 버퍼 256KB, 각 쿼리 3회 실행 후 중앙값입니다. 운영 서버의 사양과 데이터 분포에 따라 값은 달라집니다.