MySQL Optimizer Hints 쿼리 튜닝

글쓴이 /

Optimizer Hints란? — 쿼리 레벨에서 옵티마이저를 제어하는 방법

MySQL 옵티마이저는 대부분의 경우 최적의 실행 계획을 선택하지만, 때로는 잘못된 판단을 합니다. 통계가 오래되었거나, 데이터 분포가 불균일하거나, 복잡한 조인에서 최적이 아닌 순서를 선택하는 경우입니다.

MySQL 5.7에서 도입된 Optimizer Hints는 /*+ HINT */ 구문으로 쿼리 단위에서 옵티마이저의 동작을 세밀하게 제어합니다. 과거의 FORCE INDEX나 STRAIGHT_JOIN보다 훨씬 체계적이고, 옵티마이저가 힌트를 무시할 수 있는 유연성도 갖고 있습니다.

Optimizer Hints vs 레거시 인덱스 힌트

항목 Optimizer Hints (/*+ */) 레거시 힌트 (FORCE/USE INDEX)
적용 범위 조인 순서, 인덱스, BKA, MRR, 세미조인, 서브쿼리 등 인덱스 선택만
구문 위치 SELECT/UPDATE/DELETE 직후 주석 테이블명 뒤
스코프 제어 쿼리 블록 단위 지정 가능 테이블 단위만
안전성 적용 불가 시 경고 후 무시 강제 적용 (위험)
MySQL 버전 5.7+ 전 버전

레거시 FORCE INDEX는 인덱스가 삭제되면 에러가 발생하지만, Optimizer Hints는 경고만 남기고 정상 실행됩니다. 프로덕션 안정성 면에서 큰 차이입니다.

기본 구문

SELECT /*+ HINT_NAME(arguments) */ columns
FROM table_name
WHERE ...;

-- 여러 힌트 동시 사용
SELECT /*+ NO_INDEX_MERGE(t1) BKA(t1) */ t1.id, t2.name
FROM orders t1
JOIN users t2 ON t1.user_id = t2.id
WHERE t1.status = 'active';

힌트는 반드시 /*+로 시작해야 합니다. 일반 주석 /* */과 구별됩니다.

카테고리별 핵심 힌트 완전 정리

1. 조인 순서 힌트

옵티마이저가 선택한 조인 순서가 최적이 아닌 경우, 직접 순서를 지정할 수 있습니다.

-- JOIN_ORDER: 지정한 순서대로 조인
SELECT /*+ JOIN_ORDER(users, orders, items) */
    u.name, o.total, i.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items i ON o.id = i.order_id
WHERE u.country = 'KR';

-- JOIN_PREFIX: 첫 번째 조인 테이블만 지정
SELECT /*+ JOIN_PREFIX(users) */
    u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN payments p ON o.id = p.order_id;

-- JOIN_SUFFIX: 마지막 조인 테이블 지정
SELECT /*+ JOIN_SUFFIX(payments) */
    u.name, o.total, p.method
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN payments p ON o.id = p.order_id;

-- JOIN_FIXED_ORDER: FROM 절 순서 그대로 (= STRAIGHT_JOIN)
SELECT /*+ JOIN_FIXED_ORDER() */
    u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

JOIN_ORDER가 가장 자주 쓰입니다. 작은 결과셋을 먼저 조인하면 중간 결과 크기가 줄어들어 성능이 크게 개선됩니다.

2. 인덱스 힌트

-- 특정 인덱스 사용 강제
SELECT /*+ INDEX(orders idx_status_created) */
    *
FROM orders
WHERE status = 'pending'
  AND created_at > '2026-01-01';

-- 인덱스 사용 금지 (풀스캔 유도)
SELECT /*+ NO_INDEX(orders idx_status_created) */
    COUNT(*)
FROM orders
WHERE status IN ('pending', 'processing', 'shipped', 'delivered');

-- Index Merge 비활성화
SELECT /*+ NO_INDEX_MERGE(orders) */
    *
FROM orders
WHERE status = 'pending'
   OR payment_method = 'card';

-- GROUP BY용 인덱스 강제
SELECT /*+ GROUP_INDEX(orders idx_user_id) */
    user_id, COUNT(*)
FROM orders
GROUP BY user_id;

-- ORDER BY용 인덱스 강제
SELECT /*+ ORDER_INDEX(orders idx_created_at) */
    *
FROM orders
ORDER BY created_at DESC
LIMIT 20;

GROUP_INDEX와 ORDER_INDEX는 MySQL 8.0.20+에서 추가된 힌트로, filesort를 제거하는 데 효과적입니다.

3. 조인 알고리즘 힌트

-- BKA (Batched Key Access) 활성화
-- 조인 시 인덱스 룩업을 배치로 처리하여 랜덤 I/O 감소
SELECT /*+ BKA(orders) */
    u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'KR';

-- BKA 비활성화
SELECT /*+ NO_BKA(orders) */
    u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Hash Join 강제 (MySQL 8.0.18+)
SELECT /*+ HASH_JOIN(t1, t2) */
    t1.id, t2.name
FROM large_table t1
JOIN another_large_table t2 ON t1.key = t2.key;

-- Hash Join 비활성화 → Nested Loop 사용
SELECT /*+ NO_HASH_JOIN(t1, t2) */
    t1.id, t2.name
FROM small_table t1
JOIN indexed_table t2 ON t1.key = t2.key;

-- MRR (Multi-Range Read) 제어
SELECT /*+ MRR(orders) */
    *
FROM orders
WHERE user_id IN (1, 2, 3, 4, 5);

SELECT /*+ NO_MRR(orders) */
    *
FROM orders
WHERE user_id BETWEEN 100 AND 200;

BKA와 MRR은 디스크 기반 테이블에서 랜덤 I/O를 순차 I/O로 변환하여 HDD 환경에서 큰 성능 향상을 보입니다. SSD에서는 효과가 줄어들 수 있습니다.

4. 서브쿼리·세미조인 힌트

-- 세미조인 전략 지정
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */
    *
FROM users u
WHERE u.id IN (SELECT /*+ QB_NAME(subq1) */ user_id FROM orders WHERE total > 10000);

-- 세미조인 전략 옵션들:
-- DUPSWEEDOUT: 중복 제거 (기본)
-- FIRSTMATCH: 첫 매치 시 즉시 반환
-- LOOSESCAN: 인덱스 루스 스캔
-- MATERIALIZATION: 서브쿼리 결과를 임시 테이블로 구체화

-- 서브쿼리를 EXISTS로 변환하지 않기
SELECT /*+ SUBQUERY(MATERIALIZATION) */
    *
FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE active = 1
);

5. 쿼리 블록 네이밍

복잡한 쿼리에서 특정 서브쿼리나 UNION 블록에 힌트를 적용하려면 QB_NAME으로 이름을 지정합니다.

SELECT /*+ JOIN_ORDER(u, o@orderblock) */
    u.name,
    sub.total_amount
FROM users u
JOIN (
    SELECT /*+ QB_NAME(orderblock) */
        user_id, SUM(total) AS total_amount
    FROM orders
    GROUP BY user_id
) sub ON u.id = sub.user_id;

6. 리소스 제어 힌트

-- 최대 실행 시간 제한 (밀리초)
SELECT /*+ MAX_EXECUTION_TIME(5000) */
    *
FROM orders
WHERE created_at > '2025-01-01'
ORDER BY total DESC;

-- 쿼리가 5초를 초과하면 자동으로 타임아웃 에러 발생
-- ERROR 3024: Query execution was interrupted, maximum statement
-- execution time exceeded

MAX_EXECUTION_TIME은 읽기 전용 SELECT에만 적용됩니다. 분석 쿼리가 프로덕션 DB를 멈추는 것을 방지하는 안전장치로 매우 유용합니다.

7. InnoDB 병렬 스캔 힌트 (MySQL 8.0.14+)

-- 병렬 스캔 스레드 수 지정
SELECT /*+ SET_VAR(innodb_parallel_read_threads=4) */
    COUNT(*)
FROM huge_table
WHERE created_at > '2025-01-01';

-- SET_VAR로 세션 변수를 쿼리 단위로 임시 변경
SELECT /*+ SET_VAR(optimizer_switch='index_merge=off') */
    *
FROM orders
WHERE status = 'pending' OR user_id = 42;

-- join_buffer_size를 쿼리 단위로 증가
SELECT /*+ SET_VAR(join_buffer_size=8388608) */
    t1.*, t2.*
FROM big_table1 t1
JOIN big_table2 t2 ON t1.key = t2.key;

SET_VAR는 매우 강력합니다. 세션 전체가 아닌 해당 쿼리 실행 동안만 변수를 변경합니다. tmp_table_size, sort_buffer_size 등을 쿼리별로 조절할 수 있습니다.

실전 시나리오별 힌트 적용

시나리오 1: 옵티마이저가 잘못된 인덱스를 선택할 때

-- 문제: status = 'active'가 전체 90%인데 인덱스를 사용
-- 옵티마이저 통계가 오래되어 cardinality를 잘못 추정

-- Before (인덱스 스캔 → 느림)
EXPLAIN SELECT * FROM orders WHERE status = 'active' AND created_at > '2026-01-01';
-- type: ref, key: idx_status → 느린 랜덤 I/O

-- After (풀스캔이 더 빠른 경우)
EXPLAIN SELECT /*+ NO_INDEX(orders idx_status) */
    * FROM orders WHERE status = 'active' AND created_at > '2026-01-01';
-- type: ALL → 순차 I/O로 더 빠름

-- 근본 해결: 통계 갱신
ANALYZE TABLE orders;

시나리오 2: 3개 이상 테이블 조인에서 순서 최적화

-- 문제: 옵티마이저가 큰 테이블부터 조인
EXPLAIN SELECT u.name, o.total, p.method, i.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN payments p ON o.id = p.order_id
JOIN order_items i ON o.id = i.order_id
WHERE u.vip = 1;  -- VIP 사용자 100명

-- 최적 순서: users(100행) → orders → payments → items
SELECT /*+ JOIN_ORDER(u, o, p, i) */
    u.name, o.total, p.method, i.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN payments p ON o.id = p.order_id
JOIN order_items i ON o.id = i.order_id
WHERE u.vip = 1;

시나리오 3: 분석 쿼리 타임아웃 보호

-- 대시보드용 집계 쿼리에 타임아웃 + 병렬 스캔
SELECT /*+ MAX_EXECUTION_TIME(10000)
           SET_VAR(innodb_parallel_read_threads=4) */
    DATE(created_at) AS dt,
    COUNT(*) AS cnt,
    SUM(total) AS revenue
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY DATE(created_at)
ORDER BY dt;

시나리오 4: filesort 제거

-- 문제: ORDER BY + LIMIT에서 filesort 발생
EXPLAIN SELECT * FROM products
WHERE category_id = 5
ORDER BY price DESC
LIMIT 20;
-- Extra: Using filesort

-- 복합 인덱스가 있다면 ORDER_INDEX로 해결
SELECT /*+ ORDER_INDEX(products idx_category_price) */
    * FROM products
WHERE category_id = 5
ORDER BY price DESC
LIMIT 20;
-- Extra: Using index condition (filesort 제거)

EXPLAIN으로 힌트 효과 검증

힌트를 적용한 후 반드시 EXPLAIN으로 실제 효과를 확인해야 합니다.

-- 힌트 적용 전
EXPLAIN FORMAT=JSON
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';

-- 힌트 적용 후
EXPLAIN FORMAT=JSON
SELECT /*+ JOIN_ORDER(u, o) INDEX(o idx_status) */
    * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';

-- 경고 확인 (힌트가 무시된 경우)
SHOW WARNINGS;
-- Level: Warning, Code: 3128
-- Message: Hint INDEX(o idx_status) is ignored

SHOW WARNINGS를 반드시 확인하세요. 힌트가 무시된 경우 경고 메시지가 표시됩니다. 존재하지 않는 인덱스, 적용 불가능한 조합 등의 이유를 알 수 있습니다.

ORM에서 Optimizer Hints 사용하기

TypeORM

// QueryBuilder에서 힌트 추가
const orders = await dataSource
  .getRepository(Order)
  .createQueryBuilder('o')
  .addSelect('/*+ JOIN_ORDER(o, u) MAX_EXECUTION_TIME(5000) */ o.id')
  .innerJoin('o.user', 'u')
  .where('o.status = :status', { status: 'pending' })
  .getMany();

// Raw Query로 직접 사용
const result = await dataSource.query(`
  SELECT /*+ INDEX(orders idx_status_created) */
    id, total, created_at
  FROM orders
  WHERE status = ? AND created_at > ?
`, ['pending', '2026-01-01']);

JPA / Hibernate

// Native Query에서 직접 사용
@Query(value = "SELECT /*+ JOIN_ORDER(o, u) */ o.* " +
    "FROM orders o JOIN users u ON o.user_id = u.id " +
    "WHERE o.status = :status",
    nativeQuery = true)
List<Order> findByStatusOptimized(@Param("status") String status);

// Hibernate 6+ addQueryHint (MySQL 전용은 아님)
List<Order> orders = session.createQuery(
        "FROM Order o WHERE o.status = :status", Order.class)
    .setParameter("status", "pending")
    .addQueryHint("MAX_EXECUTION_TIME(5000)")
    .getResultList();

Prisma

// $queryRaw로 힌트 사용
const orders = await prisma.$queryRaw`
  SELECT /*+ INDEX(orders idx_status_created) */
    id, total, created_at
  FROM orders
  WHERE status = ${status}
    AND created_at > ${startDate}
`;

힌트 사용 시 주의사항

  • 통계 갱신이 먼저: 힌트를 추가하기 전에 ANALYZE TABLE로 통계를 갱신하세요. 통계가 정확하면 옵티마이저가 올바른 판단을 합니다.
  • 데이터 변화에 취약: 오늘 최적인 힌트가 데이터 분포가 변하면 오히려 성능을 악화시킬 수 있습니다. 주기적으로 검증하세요.
  • 최소한으로 사용: 힌트가 많아지면 유지보수가 어려워집니다. 옵티마이저를 믿되, 정말 필요한 경우에만 힌트를 추가하세요.
  • 주석으로 이유 기록: 왜 이 힌트가 필요한지 반드시 주석으로 남기세요. 3개월 후의 본인도 이유를 기억하지 못합니다.
  • 버전 호환성: MySQL 8.0.20+에서 추가된 GROUP_INDEX, ORDER_INDEX 등은 이전 버전에서 경고로 무시됩니다.

전체 Optimizer Hints 참조표

카테고리 힌트 용도
조인 순서 JOIN_ORDER / JOIN_PREFIX / JOIN_SUFFIX / JOIN_FIXED_ORDER 조인 순서 제어
인덱스 INDEX / NO_INDEX / INDEX_MERGE / NO_INDEX_MERGE 인덱스 선택
인덱스 (8.0.20+) GROUP_INDEX / NO_GROUP_INDEX / ORDER_INDEX / NO_ORDER_INDEX GROUP BY/ORDER BY 인덱스
조인 알고리즘 BKA / NO_BKA / BNL / NO_BNL / HASH_JOIN / NO_HASH_JOIN 조인 방식 제어
MRR MRR / NO_MRR Multi-Range Read
세미조인 SEMIJOIN / NO_SEMIJOIN 세미조인 전략
서브쿼리 SUBQUERY(MATERIALIZATION | INTOEXISTS) 서브쿼리 전략
Derived Table MERGE / NO_MERGE 서브쿼리 머지 제어
리소스 MAX_EXECUTION_TIME(ms) 실행 시간 제한
변수 SET_VAR(var=value) 쿼리 단위 변수 변경
쿼리 블록 QB_NAME(name) 서브쿼리/UNION 블록 네이밍

마무리

MySQL Optimizer Hints는 옵티마이저가 잘못된 선택을 할 때 쿼리 단위로 정밀하게 교정하는 도구입니다. 레거시 FORCE INDEX보다 안전하고, SET_VAR로 세션 변수까지 쿼리 단위로 제어할 수 있습니다.

핵심 원칙: ANALYZE TABLE로 통계를 먼저 갱신하고, 그래도 안 되면 힌트를 최소한으로 적용하고, EXPLAIN으로 반드시 효과를 검증하세요. 힌트는 양날의 검입니다 — 데이터 분포가 바뀌면 오히려 독이 될 수 있으므로 주기적인 리뷰가 필수입니다.

관련 글로 MySQL EXPLAIN ANALYZE 심화와 MySQL 인덱스 최적화: Covering Index도 함께 참고하시면 MySQL 쿼리 튜닝의 전체 그림을 잡는 데 도움이 됩니다.

위로 스크롤
WordPress Appliance - Powered by TurnKey Linux