MySQL 힌트(Hint) 종류 및 사용법, 꼭 확인해야 할 주의사항

Oracle에서도 힌트가 있었듯이, MySQL에서도 힌트를 사용할 수 있습니다. 우리가 수행하는 쿼리 성능을 최적화하려면 인덱스 설정, 조인 방식, 데이터베이스 설정 등을 고려해야 합니다. 그러나 가끔은 MySQL 옵티마이저가 제시하는 실행계획이 기대에 미치지 못할 때가 있습니다.
 
이럴 때 힌트(Hint)를 사용하여 MySQL 옵티마이져에게 내가 원하는 실행 계획을 생성하도록 요청할 수 있습니다. 이글은 김사원이 "MySQL 힌트는 오라클하고 다르네요"라는  말이 화두가 되어 작성하게 됐습니다. 뒷부분에 주의 사항까지 정리했으니 확인하세요. 
 

MySQL 힌트(Hint) 종류 및 사용법

 

MySQL 힌트 사용법

MySQL 힌트는 쿼리에서 특정 실행 계획을 지정하도록 하는 명령어입니다. 쿼리 옵티마이저(Query Optimizer)는 힌트를 참고하여 최적의 쿼리 실행 경로를 선택합니다. 하지만 힌트를 반드시 옵티마이저가 따르는 것은 아니며, 권장 사항 정도로 생각 합니다.  
 
MySQL에서 힌트는 두 가지 방식으로 요청할 수 있습니다. 첫 번째, 오라클과 동일한 방식인 /*+ */ 힌트 블록을 사용하는 것입니다. 두 번째, SQL 쿼리 구문내에 포함해서 사용하는 방식입니다. 이 방식은 좀 낯설죠?
 

1. 힌트의 기본 구문

힌트는 쿼리의 SELECT, INSERT, UPDATE, DELETE와 같은 명령어 바로 뒤에 /*+ */ 내에 작성하면 됩니다. 다음은 기본 구문입니다

SELECT /*+ 제시할 힌트 내용 */ col_name, ...  FROM table_name ;

 
여기서 /*+ 힌트 내용 */ 안에 힌트를 지정하여 사용할 수 있습니다. 이것은 앞에서 말한 것처럼 오라클과 동일한 방식입니다. 
 

2. SQL구문 내의 인덱스 힌트

이 부분이 오라클과의 차이점이라고 할 수 있는데요. MySQL은 인덱스 힌트를 쿼리구문 내에서 지정합니다. 예를 들어, 테이블명 바로 뒤에 구문과 함께 위치합니다. 가장 빈번하게 사용되는 인덱스 힌트들에 대해 예시입니다. 
 

1. USE INDEX

이 힌트는 특정 인덱스를 사용하도록 옵티마이저에게 지시합니다. 여러 인덱스 중에서 가장 쿼리 패턴과 일치하는 것을 선택해야 합니다. 그런데 원하는 인덱스가 지정되지 않을 때는 강제로 특정 인덱스를 사용해야 할 때가 있습니다.

SELECT * FROM table_name USE INDEX (index_name)  WHERE column_name = 'value' ;

 

2. IGNORE INDEX

때로는 인덱스를 사용하지 않는 것이 더 효율적일 수도 있습니다. 이 힌트는 특정 인덱스를 사용하지 않도록 강제합니다.  인덱스가 쿼리 성능을 저하시키는 경우에 유용한 방법입니다.

SELECT *  FROM table_name IGNORE INDEX (index_name)  WHERE column_name = 'value' ;

 

3. FORCE INDEX

이 힌트는 MySQL이 인덱스를 반드시 사용하도록 합니다. USE INDEX와 비슷하지만, 더 강력한 명령입니다. 특정 인덱스를 사용하지 않으면 쿼리가 매우 비효율적이거나 실행되지 않는 경우 사용합니다.

SELECT *  FROM table_name FORCE INDEX (index_name)  WHERE column_name = 'value' ;

 
그 외에도 아래와 같은 3가지 인덱스 지정 방식이 추가로 있는데요. 개인적인 생각은 굳이 용도를 표시하며 인덱스를 지정할 필요가 얼마나 있을까(?) 하는 생각이 듭니다. 

• USE INDEX FOR JOIN : 테이블간 조인 또는 레코드 검색을 위한 인덱스 지정
• USE INDEX FOR ORDER BY : ORDER BY 용도로 사용할 인덱스 지정
• USE INDEX FOR GROUP BY : GROUP BY 용도로 사용할 인덱스 지정

SELECT *  FROM table_name   USE INDEX (index_name1)   USE INDEX (index_name2) FOR ORDER BY   USE INDEX (index_name3) FOR GROUP BY WHERE  column_name = 'value' ;

 

MySQL 힌트 종류

아래는 MySQL 매뉴얼에서 제공하는 공식적인 Hint의 종류입니다. 생각보다 많은 힌트들이 존재합니다. 각 힌트별로 적용 범위는 아래와 같습니다. 

• 전역 : 힌트는 전체 문장에 영향을 미침
• 쿼리 블록 : 힌트는 문장 내의 특정 쿼리 블록에 영향을 미침
• 테이블 : 힌트는 쿼리 블록 내의 특정 테이블에 영향을 미침
• 인덱스 : 힌트는 테이블 내의 특정 인덱스에 영향을 미침

힌트 이름	설명	적용 범위
BKA, NO_BKA	일괄 처리 키 액세스 조인 처리에 영향을 미칩니다.	쿼리 블록, 테이블
BNL, NO_BNL	해시 조인 최적화에 영향을 미칩니다.	쿼리 블록, 테이블
DERIVED_CONDITION_PUSHDOWN,  NO_DERIVED_CONDITION_PUSHDOWN	구체화된 파생 테이블에 대해 파생 조건 푸시다운 최적화를 사용하거나 무시합니다.	쿼리 블록, 테이블
GROUP_INDEX,  NO_GROUP_INDEX	GROUP BY작업 에서 인덱스 스캔에 지정된 인덱스를 사용하거나 무시합니다.	인덱스
HASH_JOIN,  NO_HASH_JOIN	Hash Join 최적화에 영향을 미칩니다(MySQL 8.4에서는 영향이 없음)	쿼리 블록, 테이블
INDEX, NO_INDEX	JOIN_INDEX, GROUP_INDEX,  ORDER_INDEX 의 조합으로 작동 하거나 NO_JOIN_INDEX, NO_GROUP_INDEX, NO_ORDER_INDEX의 조합으로 작동합니다.	인덱스
INDEX_MERGE,  NO_INDEX_MERGE	인덱스 병합 최적화에 영향을 미칩니다.	테이블, 인덱스
JOIN_FIXED_ORDER	조인 순서를 FROM절에 지정된 테이블 순서를 사용합니다.	쿼리 블록
JOIN_INDEX,  NO_JOIN_INDEX	모든 액세스 방법에 대해 지정된 인덱스를 사용하거나 무시합니다.	인덱스
JOIN_ORDER	조인 순서에 대한 힌트에 지정된 테이블 순서를 사용합니다.	쿼리 블록
JOIN_PREFIX	조인 순서의 첫 번째 테이블에 대해 힌트에 지정된 테이블 순서를 사용합니다.	쿼리 블록
JOIN_SUFFIX	조인 순서의 마지막 테이블에 대해 힌트에 지정된 테이블 순서를 사용합니다.	쿼리 블록
MAX_EXECUTION_TIME	문장 실행 시간을 제한합니다	전역
MERGE, NO_MERGE	파생 테이블/뷰가 외부 쿼리 블록으로 병합되는 데 영향을 미칩니다.	테이블
MRR, NO_MRR	다중 범위 읽기 최적화에 영향을 미칩니다.	테이블, 인덱스
NO_ICP	인덱스 조건 푸시다운 최적화에 영향을 미칩니다.	테이블, 인덱스
NO_RANGE_OPTIMIZATION	범위 최적화에 영향을 미칩니다	테이블, 인덱스
ORDER_INDEX,  NO_ORDER_INDEX	행 정렬에 지정된 인덱스를 사용하거나 무시합니다.	인덱스
QB_NAME	쿼리 블록에 이름을 지정합니다.	쿼리 블록
RESOURCE_GROUP	명령문 실행 중 리소스 그룹 설정합니다.	전역
SEMIJOIN, NO_SEMIJOIN	semijoin 및 antijoin 전략에 영향을 미칩니다.	쿼리 블록
SKIP_SCAN,  NO_SKIP_SCAN	스킵 스캔 최적화에 영향을 미칩니다	테이블, 인덱스
SET_VAR	문장 실행 중 변수를 설정합니다.	전역
SUBQUERY	실체화, IN하위 EXISTS 쿼리 전략 에 영향을 미칩니다.	쿼리 블록

 

자주 사용되는 MySQL 힌트 종류

위에서 언급한 것처럼 힌트는 많지만 dbms의 특성상 자주 사용하는 힌트가 있습니다. 바로 조인 순서에 관한 힌트 입니다. 아무래도 조인순서가 중요하기 때문입니다. 자주 사용되는 두개의 힌트를 바로 확인하시죠. 
 

1. STRAIGHT_JOIN

이 힌트는 옵티마이저가 테이블을 조인하는 순서를 왼쪽 테이블(t1)을 먼저 읽게 합니다. 왼쪽 드라이빙 테이블에 비해 오른쪽 드리븐 테이블의 데이터가 너무 많은 경우 효과적입니다. 

SELECT t1.*, t2.* FROM table1 t1 STRAIGHT_JOIN table2 t2  ON t1.id = t2.id ;

 

2. JOIN_ORDER

JOIN_ORDER 힌트는 지정된 테이블 순서를 사용하여 테이블을 조인하도록 지시합니다. 힌트는 명명된 테이블에 적용됩니다. 이는 조인의 순서를 지정한 대로 실행하도록 하는 힌트입니다. 

SELECT /*+ JOIN_ORDER(t2, t3, t1) */ t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id INNER JOIN table3 t3 ON t2.id = t3.id ;

 
그런데 "조인 순서를 어떻게 조정해야 하는데요?" 라고 묻는다면, 조인 순서는 NL조인에서는 데이터가 작은 테이블이 먼저 드라이빙되는 것이 좋습니다. 만약 NL 조인의 원리와 특장점이 궁금하다면 링크를 참고하세요. 
 
 
 

힌트 사용 시 주의사항

1. 과도한 힌트 사용 자제

힌트는 필요할 때만 사용하는 것이 좋습니다. 무분별한 사용은 쿼리 성능을 오히려 저하시킬 수 있습니다. 옵티마이저는 일반적으로 다양한 상황에 맞게 최적화 알고리즘을 설계합니다. 힌트로 인해 옵티마이저의 자동 최적화를 방해할 수 있습니다. 
 
여러 힌트를 한 쿼리에 동시에 사용하는 경우 이들 간의 상호작용으로 인해 예상치 못한 결과가 발생할 수도 있습니다. 가능한한 최소한의 힌트만 사용하고 필요 없는 힌트는 제거해야 합니다. 
 
결론적으로 힌트는 옵티마이저가 특정 상황에 잘못된 결정을 내린 경에만 사용해야 합니다. 각 쿼리에 대한 실행 계획을 보고 실제 힌트가 필요한 상황인지 검토 후 적용해야 합니다. 
 

MySQL 힌트 사용시 주의사항

2. 업그레이드 시 호환성

MySQL 버전 업그레이드 시 힌트가 예상대로 작동하지 않을 수 있습니다. 예를 들어, 힌트가 새로운 버전에서 더 이상 지원되지 않거나, 옵티마이저가 더 나은 실행 계획을 선택할 수 있습니다. 
 
힌트를 사용한 쿼리는 업그레이드 후에도 성능 테스트가 필요합니다. 특히 대규모 업그레이드 후에는 실행 계획이 어떻게 변했는지 확인하고 필요에 다라 수정해야 할 수 있습니다. 
 

3. 테스트 및 관리 필수

힌트를 사용하기 전과 후의 성능 차이를 반드시 테스트하여 힌트가 실제로 성능을 개선하는지 확인해야 합니다. 또 시간이 지남에 따라 성능이 변할 수도 있습니다. 예를 들어, 데이터 양이나, 분포가 변하게 되면 초기에는 좋았던 힌트가 성능을 저하시키는 주범일 수 있습니다. 
 
이런 이유 때문에 힌트는 많이 사용하면 관리 포인트가 늘어납니다. 힌트는 단기적으로 성능 문제를 해결하는 데 유용할 수 있지만 장기적으로는 성능의 문제가 되지 않는지 테스트하고 검토해야 하는 관리 대상입니다. 
 

마치며

MySQL 힌트는 쿼리 성능을 미세 조정하는 데 좋은 대안입니다. 하지만 힌트를 사용할 때는 신중해야 합니다. 오라클 dbms처럼 발단된 옵티마이저는 힌트가 필요 없을 정도로 자동 실행계획의 성능이 좋아졌습니다. 사용자의 엉뚱한 힌트에 대해서는 거르는 정도까지 발달해 왔습니다. 
 
MySQL은 오픈소스 dbms로 옵티마이저는 발전하는 중입니다. 내가 지시한 힌트로 인해 잘못된 결정을 내릴 가능성이 아직은 높습니다. 그래서 필요할 때에만 사용하여 옵티마이저의 자동 최적화 기능을 활용하는 것이 좋습니다. 
 
오라클 힌트(hint) 종류 및 사용법의 모든 것

오라클 힌트(hint) 종류 및 사용법