Hint
DB에서 힌트(Hint)란 쿼리 실행 계획을 만들 때, 쿼리 옵티마이저가 사용할 특별한 지시사항을 의미한다. 힌트란 SQL 튜닝의 핵심 부분으로 일종의 지시 구문이다. DB가 항상 최적의 실행 경로를 만들어 내기는 불가능하기 때문에 직접 최적의 실행 경로를 작성해 주는 것이다. 즉, 힌트란 쿼리 옵티마이저에게 어떤 방식으로 쿼리를 실행할지 제안을 하는 역할이다.
정리하자면, 개발자는 실행 계획을 직접 수정할일이 거의 없고 쿼리 옵티마이저가 자동으로 실행계획을 구성하여 주지만, 개발자가 해당방법이 최적의 방법이 아니라고 판단하여, 이를 수정하기 위해 직접 Hint를 제공하여 쿼리 옵티마이저에게 실행 계획의 구성방법을 최적으로 제공할 수 있다. 예를 들어, 인덱스를 사용하도록 지시하거나, 테이블 조인 순서를 제안하는 등의 방식이 있다.
하지만, 힌트는 적절하게 사용하지 않으면 오히려 성능 저하나 예상치 못한 결과를 초래할 수 있기 때문에, 고려를 해야하는 부분이 존재한다.
힌트의 사용이유
- 액세스 경로,조인 순서, 병렬 및 직렬 처리, Optimizer의 목표(Goal)를 변경하기 위해 사용한다.
- 데이터 값을 정렬해야 하는 경우, 힌트의 사용이 필요하다.
- 또한, 드라이빙 테이블을 원하는 대로 선정하고자 할 때도 사용된다.
- 성능 향상 : DB 힌트를 사용하면, 최적화 엔진이 실행계획을 선택하는데 도움을 준다.
- 예측 가능한 실행 계획 : DB 힌트를 사용하면, 개발자의 의도대로 일관된 실행계획을 유지할 수 있다.
- 특정 상황에 대응 가능 : DB 힌트를 사용하면 데이터베이스 엔진이 예상하지 못한 상황에서도 특정 실행 계획을 선택하도록 지시할 수 있다.
DB 힌트 종류
대표적으로 사용되는 RDB인 MySql과 Oracle DB에서는 서로 다른 힌트를 제공한다.
Oracle DB 대표 Hint - OPTIMIZER MODE 지정 값
/*+ ALL_ROWS */
- 목적 : Best Throughput
- 용도 : 전체 RESOURCE 소비를 최소화시키기 위한 힌트. Cost-Based 접근방식으로 ALL_ROWS는 Full Table Scan을 선호하며, CBO(Cost Based Optimization)는 default로 ALL_ROWS를 선택한다.
/*+ FIRST_ROWS */
- 목적 : Best Response Time
- 용도 : 조건에 맞는 첫 번째 row를 리턴하기 위한 Resource 소비를 최소화시키기 위한 힌트이며 Cost-Based 접근방식을 사용
Oracle DB 대표 Hint - Access Methods(접근 방법)
/*+ FULL(table_name) */
- Table을 Full Scan 하길 원할 때 사용
/*+ HASH(table) */
- Hash scan을 선택하도록 지정 (HASHKEYS Parameter로 만들어진 Cluster내에 저장된 Table에만 적용)
/*+ CLUSTER(table_name) */
- Cluster Scan을 선택하도록 지정. 따라서 Clustered Object만 적용
Oracle DB 대표 Hint - Join Orders
/*+ ORDERED */
- From절에 기술된 테이블 순서대로 join이 일어나도록 유도
Mysql 힌트
MYSql의 경우 원래 인덱스 힌트와 옵티마이저 힌트로 구성되어있었으나 Mysql 5.7 버전 이후에는 인덱스 힌트를 사용하지 않고 옵티마이저 힌트를 사용한다.
Mysql - 옵티마이저 힌트
- GLOBAL : 전체 쿼리 블록에 영향을 끼치는 힌트
-- 최대 실행 시간 설정
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1;
-- 리소스 그룹 설정
SELECT /*+ RESOURCE_GROUP(user_group) */ * FROM t1;- QUERY BLOCK : 특정 쿼리 블록에 사용할 수 있는 힌트로서 힌트가 명시된 쿼리블록에만 힌트가 영향을 미친다.
-- 쿼리 블록에 이름 지정
SELECT /*+ QB_NAME(qb1) */ * FROM t1
WHERE id IN (
SELECT /*+ QB_NAME(qb2) */ id FROM t2
);
-- 특정 쿼리 블록의 서브쿼리 전략 지정
SELECT * FROM t1
WHERE id IN (
SELECT /*+ SUBQUERY(@qb2 MATERIALIZATION) */ id FROM t2
);
-- 파생 테이블 병합 제어
SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;- TABLE-LEVEL : 특정 테이블의 이름을 사용할 수 있는 힌트
-- 조인 순서 지정
SELECT /*+ JOIN_ORDER(t1, t2, t3) */ *
FROM t1, t2, t3
WHERE t1.id = t2.id AND t2.id = t3.id;
-- 특정 테이블에 조인 방식 지정
SELECT /*+ BNL(t2) */ * FROM t1 JOIN t2 ON t1.id = t2.id;
-- 특정 테이블의 MRR 사용
SELECT /*+ MRR(t1) */ * FROM t1 WHERE id BETWEEN 1 AND 100;- INDEX-LEVEL : 특정 인덱스의 이름을 사용할 수 있는 힌트
-- 인덱스 사용 권장
SELECT /*+ INDEX(t1 idx_name) */ * FROM t1 WHERE name = 'John';
-- 인덱스 사용 안 함
SELECT /*+ NO_INDEX(t1 idx_name) */ * FROM t1 WHERE name = 'John';힌트가 무시되는 경우
-
문법 오류: 테이블 별칭을 잘못 사용하거나 존재하지 않는 인덱스 지정
- 예: SELECT /+ INDEX(employees idx) / FROM employees e
- 별칭 e를 써야 하는데 테이블명을 씀
-
물리적으로 불가능한 경우:
- WHERE 절이 없는데 인덱스 스캔 힌트
- 조인 조건이 없는데 특정 조인 방법 힌트
-
옵티마이저 판단: 힌트를 따르는 것이 명백히 비효율적인 경우
(MySQL은 힌트를 강력하게 따르지만 Oracle은 권고 수준) -
버전 미지원: 해당 MySQL/Oracle 버전에서 지원하지 않는 힌트
SHOW WARNINGS나 EXPLAIN으로 힌트가 제대로 적용되었는지 확인해야 한다.
힌트 사용시 주의 사항
-
데이터 변화에 취약
- 데이터 분포가 바뀌면 최적이던 힌트가 독이 될 수 있음
- 정기적인 성능 모니터링 필요
-
유지보수성 저하
- 힌트는 코드를 복잡하게 만들고
- 왜 그 힌트를 사용했는지 주석으로 명확히 남겨야 함
-
근본 원인 해결 우선
- 통계 정보 갱신 (ANALYZE TABLE)
- 적절한 인덱스 생성
- 쿼리 자체의 재작성
이런 것들을 먼저 시도해야 합니다
-
버전 업그레이드 시 재검토
- 새 버전의 옵티마이저는 더 똑똑할 수 있음
- 힌트가 오히려 방해가 될 수 있음
-
과도한 힌트 사용 지양
- 여러 힌트를 조합하면 예측하기 어려움
- 최소한의 힌트로 목적 달성
원칙은 힌트 없이도 잘 동작하게 만들되, 불가피한 경우에만 명확한 이유와 함께 사용하는 것이 좋다.
힌트는 SQL 옵티마이저에게 쿼리 실행 계획을 수립할 때 특정 방향을 제시하는 기능이다. DB는 옵티마이저를 통해서 실행 계획을 생성하는데, 항상 최적의 실행 계획을 만들 수 는 없다. 그래서 힌트를 제시함으로써 옵티마이저가 최적의 실행 계획을 만들도록 도울수 있다. 개발자의 의도대로 예측 가능한 실행 계획을 세울 수 있다. MySQL의 경우 5.7이후 버전 부터는 옵티마이저 힌트를 주로 사용하며 GLOBAL, QUERY BLOCK, TABLE, INDEX 레벨로 구분된다. 그래서 특정 인덱스 사용을 권장하거나 조인 방법을 지정할수도 있다. 다만 힌트는 최후의 수단으로 사용해야 한다. 힌트를 사용하면 코드가 복잡해지고, 데이터 분포가 바뀌는 경우에는 최적이던 힌트가 오히려 비효율적일 수 도 있다. 따라서 옵티마이저가 잘못된 실행 계획을 선택하는 근본 원인(통계 정보 부족, 인덱스 부재 등)을 먼저 해결해야 하고, 힌트 사용 시에도 실행 계획을 반드시 확인해야 하는 것이 좋다.
힌트가 무시되는 경우는 옵티마이저가 그 힌트가 명백히 비효율적이라고 판단했을 때나 문법 오류가 있는 경우에는 힌트가 무시된다.
