2025-10-21

오늘 배운 것

9.4 쿼리 힌트

• 인덱스 힌트 : MySQL 예전 버전에서 사용되어 오던 힌트
• 옵티마이저 : MySQL 5.6 버전부터 새롭게 추가되기 시작한 힌트

9.4.1 인덱스 힌트

• 인덱스 힌트들은 모두 SQL 문법에 맞게 사용해야 하기 때문에 ANSI-SQL 표준 문법을 준수하지 못하게 되는 단점이 있음
• 인덱스 힌트는 SELECT와 UPDATE 명령에서만 사용 가능
• 옵티마이저 힌트들은 MySQL서버를 제외한 다른 RDBMS에서 주석으로 해석
• 따라서 가능하다면 인덱스 힌트보다는 옵티마이저 힌트 사용하는 것을 장려

9.4.1.1 STRAIGHT_JOIN

• 옵티마이저 힌트인 동시에 조인 키워드임
• SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할
• 옵티마이저는 그때그때 각 테이블의 통계 정보와 쿼리의 조건을 기반으로 가장 최적이라고 판단되는 순서로 조인
  • 일반적으로 칼럼들의 인덱스 여부로 조인 순서 결정
  • 조인 칼럼의 인덱스에 아무런 문제가 없을 경우에는 레코드가 적은 테이블을 드라이빙으로 선택
• 인덱스 힌트는 사용해야 하는 위치가 이미 결정되어 있음
  • STRAIGHT_JOIN키워드는 SELECT 키워드 바로 뒤
• STRAIGHT_JOIN 힌트는 옵티마이저가 FROM 절에 명시된 테이블의 순서대로 조인을 수행하도록 유도
• 아래 조건 대로 실행계획 수립 못할 시 힌트 사용하기
  • 임시 테이블과 일반 테이블의 조인
    • 일반적으로 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋음
  • 임시 테이블끼리 조인
    • 항상 인덱스가 없기 때문에 어느 테이블을 먼저 드라이빙으로 읽어도 무관하므로 크기가 작은 테이블을 드라이빙으로 선택하는 것이 좋음
  • 일반 테이블끼리 조인
    • 양쪽 모두 조인 칼럼에 인덱스가 있거나 없으면 레코드 건수가 적은 테이블을 드라이빙으로 선택
    • 그 이외의 경우에는 조인 컬럼에 인덱스가 없는 테이블을 드라이빙으로 선택하는 것이 좋음
  • 여기서 레코드 건수는 인덱스를 사용할 수 있는 WHERE 조건까지 포함해서 그 조건을 만족하는 레코드 건수를 의미(전체 테이블 레코드 건수 x)
• STRAIGHT_JOIN과 비슷한 역할을 하는 옵티마이저 힌트
  • JOIN_FIXED_ORDER
    • STRAIGHT_JOIN과 동일
  • JOIN_ORDER
  • JOIN_PREFIX
  • JOIN_SUFFIX

9.4.1.2 USE INDEX / FORCE INDEX / IGNORE INDEX

• 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 함
• 강제로 특정 인덱스를 사용하도록 힌트를 추가할 때 사용
• 키워드 뒤에 사용할 인덱스의 이름을 괄호로 묶어서 사용
• 괄호 안에 아무것도 없거나 존재하지 않는 인덱스 이름을 사용할 경우에는 쿼리의 문법 오류로 처리
• 사용자가 부여한 이름이 없는 PK는 “PRIMARY”라고 명시
• USE INDEX
  • 특정 테이블의 인덱스를 사용하도록 권장하는 힌트
  • 대부분 채택하지만 항상 해당 인덱스를 사용하는 것은 아님
• FORCE INDEX
  • USE INDEX 보다 옵티마이저에게 미치는 영향이 더 강한 힌트
  • 대체로 USE INDEX 힌트를 부여했는데도 그 인덱스를 사용하지 않는 경우라면 FORCE INDEX를 사용해도 그 인덱스를 사용하지 않음
• IGNORE INDEX
  • 특정 인덱스를 사용하지 못하게 하는 용도로 사용
  • 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 IGNORE INDEX 힌트를 사용할 수 있음
• 용도는 명시는 선택사항
  • USE INDEX FOR JOIN
    • 테이블 간의 조인뿐만 아니라 레코드를 검색하기 위한 용도까지 포함하는 용어
  • USE INDEX FOR ORDER BY
    • ORDER BY 용도로만 사용할 수 있게 제한
  • USE INDEX FOR GROUP BY
    • GROUP BY 용도로만 사용할 수 있게 제한
• 옵티마이저가 최적으로 용도를 선택하기 때문에 크게 고려하지 않아도 됌
• 전문 검색(Full Text search) 인덱스가 있는 경우 옵티마이저는 다른 일반 보조 인덱스(B-Tree)를 사용할 수 있는 상황이라고 하더라도 전문 검색 인덱스를 선택하는 경우가 많음
  • PK랑 전문 검색 인덱스에 선택 시 가중치를 두고 실행 계획을 수립하기 때문
• 힌트를 사용하는 것보단 가장 훌륭한 최적화는 그 쿼리를 서비스에서 없애버리거나 튜닝할 필요가 없게 데이터를 최소화하는 것
  • 그것이 어렵다면 데이터 모델의 단순화를 통해 쿼리를 간결하게 만들고 힌트가 필요치 않게 하는것
  • 실무에서는 앞쪽의 작업들에 상당한 시간과 작업 능력이 필요하기 때문에 항상 이런 힌트에 의존하는 경우가 많다

9.4.1.3 SQL_CALC_FOUND_ROWS

• LIMIT를 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 더 많다고 하더라도 LIMIT에 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색 작업을 멈춤
• SQL_CALC_FOUND_ROWS 힌트가 포함된 쿼리의 경우는 끝까지 검색을 수행
• 해당 쿼리가 실행된 경우에는 FOUND_ROWS()라는 함수를 이용해 LIMIT을 제외한 조건을 만족하는 레코드가 전체 몇건이었는지를 알아낼 수 있다
• 웹 프로그램의 페이징 기능에 적용하기 위해서 사용할 수도 있음
• 단점
  • SELECT 쿼리 문장이 UNION으로 연결된 경우에는 SQL_CALC_FOUND_ROWS를 사용해도 FOUND_ROWS() 함수로 정확한 레코드 건수를 가져올 수 없다
  • 오히려 잘 튜닝되어있는 인덱스를 활용하는 것이 더 빠르기 때문에 사용하지 않는 것이 좋다
  • 따라서 레코드 카운터용 쿼리와 데이터를 조회하는 쿼리를 분리하는 것이 더 효율적

9.4.2 옵티마이저 힌트

9.4.2.1 옵티마이저 힌트 종류

• 인덱스 : 특정 인덱스의 이름을 사용 가능
• 테이블 : 특정 테이블의 이름을 사용 가능
• 쿼리 블록 : 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트로서, 특정 쿼리 블록의 이름을 명시하는 것이 아니라 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵티마이저 힌트
• 글로벌 : 전체 쿼리에 대해서 영향을 미치는 힌트
• 해당 종류에 따라 힌트 위치가 달라지지 않음
• 모든 인덱스 수준의 힌트는 반드시 테이블 명이 선행되고 사용할 인덱스 명을 사용해야 함
• 쿼리 블록 : 각 SELECT 키워드로 시작하는 서브 쿼리 영역
  • 특정 쿼리 블록에 영향을 미치는 옵티마이저 힌트는 그 쿼리 블록 내에서 사용될 수도 있지만 외부 쿼리 블록에서 사용할 수도 있다.
  • 특정 쿼리 블록을 외부 쿼리 블록에서 사용하려면 “QB_NAME()” 힌트를 이용해 해당 쿼리 블록에 이름을 부여해야 함

9.4.2.2 MAX_EXECUTION_TIME

• 쿼리의 실행 계획에 영향을 미치지 않는 힌트
• 단순히 쿼리의 최대 실행시간을 설정
• 밀리초 단위로 시간을 설정
• 쿼리가 지정된 시간을 초과하면 실패

9.4.2.3 SET_VAR

• 시스템 변수 조정을 위한 힌트
  • 모든 시스템 변수 조정이 가능한 것은 아님
• 실행 계획을 바꾸는 용도 뿐만 아니라 조인 버퍼나 정렬용 버퍼의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능응 향상 시키는 용도로도 사용 가능

9.4.2.4 SEMIJOIN & NO_SEMIJOIN

• 어떤 세미 조인 최적화 전략을 사용할지를 제어하는데 사용
• 종류
  • Duplicate Weed-out : SEMIJOIN(DUPSWEEDOUT)
  • First Match : SEMIJOIN(FIRSTMATCH)
  • Loose Scan : SEMIJOIN(LOOSESCAN)
  • Materialization : SEMIJOIN(MATERIALIZATION)
  • Table Pull-out : 없음
    • 테이블 풀 아웃 최적화 전략은 사용할 수 있다면 항상 사용하는 것이 더 나은 성능을 보장하므로 별도로 힌트를 사용할 수 없다
    • 다른 최적화 전략들은 상황에 따라 사용하지 않는 것이 더 나은 성능을 낼 수 있기 때문에 NO_SEMIJOIN 힌트를 사용하여 우회할 수 있다.
  • 세미 조인 최적화 힌트는 외부 쿼리가 아니라 서브 쿼리에 명시해야한다.
  • 혹은 서브쿼리에 쿼리 블록 이름을 정의하고 실제 세미 조인 힌트는 외부 쿼리 블록에 명시하는 방법이 있다.

9.4.2.5 SUBQUERY

• 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법
• 종류
  • IN-to-EXISTS : SUBQUERY(INTOEXISTS)
  • Materialization : SUBQUERY(MATERIALIZATION)
• 안티 세미 조인의 최적화는 IN(subquery) 형태의 쿼리에 사용 불가하여 위 방법으로 힌트 사용 가능

9.4.2.6 BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

• MySQL 8.0.20 버전부터는 블록 네스티드 루프 조인까지 해시 조인 알고리즘이 대체
• MySQL 8.0.20 버전 이상 : BNL 힌트는 해시 조인을 사용하도록 유도하는 힌트로 용도 변경
  • 이름만 BNL이고 사실 해시 조인 on/off기능이라고 생각하면 됌
• MySQL 8.0.18 버전 : HASHJOIN, NO_HASHJOIN 힌트는 이 버전에서만 유효
• 조인 조건이 되는 칼럼의 인덱스가 적절히 준비되어 있다면 해시 조인은 거의 사용되지 않음
  • 왜?
    • 해시조인과 블록 네스티드 루프 조인은 비 인덱스 조인에서 유용한것
    • 인덱스가 있다면 조인 키로 내부 테이블 인덱스를 바로 룩업 하는 인덱스 네스티드 루프 조인을 사용하는 것이 좋음

9.4.2.7 JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

• STRAIGHT_JOIN 힌트의 단점
  • FROM절에 사용된 테이블 의 순서를 조인 순서에 맞게 변경해야 됌
  • 일부는 순서만 강제하고 나머지는 옵티마이저에게 맡기게 못함
• 해당 단점 보완을 위해 사용
• 종류
  • JOIN_FIXED_ORDER
    • STRAIGHT_JOIN과 동일
    • FROM 절의 테이블 순서대로 조인을 수행
  • JOIN_ORDER
    • FROM 절에 사용된 테이블의 순서가 아니라 힌트에 명시된 테이블의 순서대로 조인을 실행하는 힌트
  • JOIN_PREFIX
    • 조인에서 드라이빙 테이블만 강제하는 힌트
  • JOIN_SUFFIX
    • 조인에서 드리븐 테이블(가장 마지막에 조인돼야 할 테이블들) 만 강제하는 힌트

9.4.2.8 MERGE & NO_MERGE

• 예전 버전에서는 FROM절에 사용된 서브쿼리를 항상 내부 임시 테이블(파생 테이블)로 생성했고 이는 불필요한 자원소모를 유발함
• 이후 5.7과 8.0 버전에서는 가능하면 임시 테이블 사용하지 않게 FROM 절의 서브 쿼리를 외부 쿼리와 병합하는 최적화를 도입했다.
• 하지만 때로는 내부 임시 테이블을 생성하는 것이 더 나은 선택일 수 있기 때문에 해당 힌트를 사용하여 최적화를 시도한다.

9.4.2.9 INDEX_MERGE & NO_INDEX_MERGE

• 인덱스 머지 : 하나의 테이블에 대해 여러 개의 인덱스를 동시에 사용
  • MySQL 서버는 가능하다면 테이블당 하나의 인덱스만을 이용해 쿼리를 처리하려고 함
  • 하나의 인덱스로 검색 대상 범위를 충분히 좁힐 수 없다면 사용 가능한 다른 인덱스를 사용하기도 한다.
  • 여러 인덱스로 검색된 레코드로부터 교집합 또는 합집합만을 구해서 그 결과를 반환
• 항상 인덱스 머지를 사용하는 것이 성능향상에 도움이 되는 것은 아니므로 제어 용도로 사용가능하다

9.4.2.10 NO_ICP

• 인덱스 컨디션 푸시다운 최적화는 사용 가능하다면 항상 선응 향상에 도움이 되므로 옵티마이저는 최대한 사용하는 방향으로 실행 계획을 수립
• 하지만 인덱스 컨디션 푸시다운으로 인해 여러 실행 계획의 비용 계산이 잘못 된다면 결과적으로 잘못된 실행 계획을 수립하게 될 수 있어 비활성화하여 최적화 가능하다

9.4.2.11 SKIP_SCAN & NO_SKIP_SCAN

• 인덱스 스킵 스캔 : 인덱스의 선행 칼럼에 대한 조건이 없어도 옵티마이저가 해당 인덱스르 사용할 수 있게 해줌
• 하지만 선행 칼럼이 가지는 유니크한 값의 개수가 많아진다면 성능이 오히려 떨어짐
• 이러한 인덱스 스킵 스캔 제어를 위한 최적화 사용 가능

9.4.2. 12 INDEX & NO_INDEX

• MySQL 서버에서 사용되던 인덱스 힌트를 대체하는 용도로 제공
• 테이블명과 인덱스 이름을 함께 명시해야 함