Query Hint

https://sundaland.tistory.com/140

 

[ ▶ Query Hint ]

쿼리 힌트(query hint)는 데이터베이스 관리 시스템(DBMS)의 쿼리 최적화(Optimizer)기가 쿼리를 실행할 때, 최적화 경로와 전략을 어떻게 선택할지에 대해 개발자가 명시적인 지침을 제공하는 방법이다. DBMS는 기본적으로 통계 정보와 내부 알고리즘을 통해 쿼리의 최적 실행 계획을 자동으로 선택하지만, 때로는 최적화기의 판단이 항상 최선이 아닐 수 있습니다. 이럴 때 쿼리 힌트를 사용해 실행 계획을 제어할 수 있다.

DBMS는 각 쿼리에 대해 실행 계획을 수립할 때, 조인 순서, 인덱스 선택, 테이블 액세스 방식, 메모리 사용 방식 등 여러 요소를 고려한다. 쿼리 힌트는 이러한 요소 중 하나 이상에 영향을 주어, 성능을 개선하거나 잘못된 실행 계획을 피하는 데 도움을 줄 수 있다.

 

[ ▷ 쿼리 힌트의 목적 ]

• 최적화된 실행 계획 강제: 쿼리 최적화기가 선택한 실행 계획보다 더 나은 실행 계획을 강제로 적용할 수 있다.
• 성능 문제 해결: 기본 실행 계획으로 인해 성능 문제가 발생할 경우, 힌트를 통해 개선된 성능을 도출할 수 있다.
• 특정 환경에서의 튜닝: 데이터 분포나 인덱스 구조, 데이터 양에 따라 실행 계획이 비효율적일 때, 힌트를 통해 그 환경에 맞는 최적의 전략을 지정할 수 있다.

 

[ ▷ DBMS별 쿼리 힌트 특징 ]

DBMS마다 쿼리 힌트의 구문과 기능이 다르다. 대표적으로 MySQL, Oracle, SQL Server가 있으며, 각각의 힌트 사용 방식이 약간씩 다르다.

[ ▷ MySQL 쿼리 힌트 ]

MySQL에서는 특정 인덱스를 선택하거나, 조인 방법을 제어하는 힌트를 사용할 수 있다. MySQL 힌트는 /*+ HINT_NAME */ 형식으로 사용힌다.

• USE INDEX: 특정 테이블에 대해 사용할 인덱스를 지정한다.
• SELECT * FROM users USE INDEX (user_idx) WHERE id = 1;
• FORCE INDEX: 인덱스 사용을 강제합니다. 기본적으로 최적화기는 테이블 스캔이 더 효율적이라고 판단할 수 있지만, 힌트로 강제로 인덱스를 사용하게 할 수 있다.
• SELECT * FROM users FORCE INDEX (user_idx) WHERE id = 1;
• STRAIGHT_JOIN: MySQL은 최적화기가 조인 순서를 변경할 수 있지만, 이 힌트는 FROM 절에 명시된 순서대로 조인을 수행하도록 강제한다.
• SELECT STRAIGHT_JOIN * FROM users u JOIN orders o ON u.id = o.user_id;

 

[ ▷ Oracle 쿼리 힌트 ]

Oracle은 다양한 쿼리 힌트를 지원하며, 특히 대규모 데이터를 처리하거나 복잡한 조인에서 성능을 극대화할 수 있도록 세밀하게 튜닝할 수 있다.

• INDEX: 특정 인덱스를 사용하도록 강제한다.
• SELECT /*+ INDEX(users user_idx) */ * FROM users WHERE id = 1;
• FULL: 테이블 전체 스캔을 강제합니다. 인덱스 스캔이 효율적이지 않을 때, 이를 강제할 수 있다.
• SELECT /*+ FULL(users) */ * FROM users WHERE id = 1;
• PARALLEL: 쿼리를 병렬로 처리하도록 지시합니다. 대용량 데이터를 처리할 때 성능을 극대화할 수 있다.
• SELECT /*+ PARALLEL(users, 4) */ * FROM users;
• LEADING: 조인의 순서를 강제하여 특정 테이블이 먼저 처리되도록 지시한다.
• SELECT /*+ LEADING(users) */ * FROM users JOIN orders ON users.id = orders.user_id;
• NO_MERGE: 특정 서브쿼리가 메인 쿼리와 병합되지 않도록 합니다. 서브쿼리가 독립적으로 처리되어야 할 때 유용하다.
• SELECT /*+ NO_MERGE */ * FROM (SELECT * FROM users);

 

[ ▷ SQL Server 쿼리 힌트 ]

SQL Server는 실행 계획을 제어하기 위한 다양한 힌트를 제공하며, 쿼리 최적화가 비효율적일 때 이를 보완할 수 있는 기능을 제공한다.

• FORCESEEK: 특정 인덱스를 사용해 인덱스 탐색(Seek)을 강제한다.
• SELECT * FROM users WITH (FORCESEEK) WHERE id = 1;
• FORCESCAN: 인덱스 스캔을 강제한다.
• SELECT * FROM users WITH (FORCESCAN) WHERE id = 1;
• HASH JOIN: 해시 조인을 사용하도록 지시한다. 일반적으로 해시 조인은 대용량 데이터에 적합하다.
• SELECT * FROM users u JOIN orders o ON u.id = o.user_id OPTION (HASH JOIN);
• LOOP JOIN: 중첩 루프 조인을 사용하도록 강제한다. 작은 데이터 세트나 인덱스가 잘 설정된 경우에 유리하다.
• SELECT * FROM users u JOIN orders o ON u.id = o.user_id OPTION (LOOP JOIN);

 

[ ▷ 쿼리 힌트의 동작 원리 ]

쿼리 힌트는 데이터베이스의 쿼리 최적화기(query optimizer)가 실행 계획을 수립할 때 고려하는 힌트다. 최적화기는 SQL 쿼리와 테이블 통계를 기반으로 최적의 실행 계획을 자동으로 수립한다. 하지만 최적화기는 모든 상황에서 최선의 계획을 선택하지 않을 수 있으며, 쿼리 힌트는 이를 보완해줄 수 있다.

쿼리 최적화기가 실행 계획을 세우는 과정은 아래와 같다.

1. 파싱(Parsing): 쿼리가 데이터베이스에서 구문 분석된다.
2. 통계 분석: 쿼리에서 사용하는 테이블, 인덱스, 통계 정보를 분석하여 성능 예측을 한다.
3. 실행 계획 생성: 가능한 여러 실행 계획을 평가하고, 가장 적합한 계획을 선택합니다. 여기서 힌트가 있다면 힌트에 맞는 실행 계획이 생성된다.

쿼리 힌트는 이 과정에서 최적화기의 판단을 보완하거나 무시하고, 사용자가 의도한 방식으로 실행 계획을 세우도록 유도한다.

 

[ ▷ 쿼리 힌트 사용 시 고려사항 ]

• 효율성: 모든 쿼리 힌트가 성능을 항상 개선하는 것은 아니다. 데이터베이스의 상태, 데이터 분포, 인덱스 상태에 따라 힌트가 오히려 성능을 저하시킬 수 있다. 따라서 쿼리 힌트를 적용한 후 반드시 성능 테스트를 수행해야 한다.
• 유지보수성: 힌트를 사용하면 쿼리의 동작을 인위적으로 변경하는 것이기 때문에, 데이터베이스의 통계 정보가 변경되거나 데이터가 변화함에 따라 쿼리 힌트가 더 이상 유효하지 않을 수 있다.
• DBMS 의존성: 쿼리 힌트는 DBMS에 따라 다르게 구현됩니다. MySQL에서 사용하는 힌트가 Oracle에서는 지원되지 않거나 동작 방식이 다를 수 있다. 따라서 특정 DBMS에 종속적인 힌트를 사용할 경우, 이식성을 고려해야 한다.

 

 

[ ▷ MySQL 쿼리 힌트 실전 예시 ]

△ USE INDEX 힌트를 이용한 특정 인덱스 강제 사용

users 테이블에 여러 인덱스가 정의되어 있을 때, 특정 인덱스 사용을 강제하고 싶을 때 유용하다.

SELECT * 
FROM users USE INDEX (user_idx) 
WHERE email = 'test@example.com';

• 설명: user_idx 인덱스를 사용하도록 강제하여, 쿼리 최적화기가 다른 인덱스를 선택하지 않도록 한다.
• 적용 시점: 여러 인덱스 중 최적화기가 잘못된 인덱스를 선택하거나, 인덱스를 아예 사용하지 않는 상황에서 사용.

 

△ FORCE INDEX 힌트를 이용해 인덱스 사용을 강제

최적화가 테이블 스캔을 선택할 때, 인덱스를 강제로 사용하게 할 수 있다.

SELECT * 
FROM users FORCE INDEX (user_idx) 
WHERE email = 'test@example.com';

• 설명: user_idx 인덱스를 무조건 사용하게 강제한다. 기본적인 USE INDEX와 비슷하지만, 더 강력하게 인덱스를 강제한다.
• 적용 시점: 기본 인덱스 선택 과정에서 인덱스를 무시하고 테이블 스캔을 선택하는 경우.

 

△ STRAIGHT_JOIN 힌트를 이용한 조인 순서 강제

조인할 때 기본적으로 최적화기는 테이블 순서를 변경할 수 있다. 그러나 이 힌트는 명시된 순서대로 조인을 수행하도록 강제한다.

SELECT STRAIGHT_JOIN * 
FROM users u 
JOIN orders o ON u.id = o.user_id;

• 설명: users 테이블이 먼저 처리되고, 그 후 orders 테이블과 조인된다.
• 적용 시점: 조인 순서에 따른 성능 차이가 클 때, 순서를 고정하여 원하는 계획을 얻고자 할 때.

 

[ ▷ Oracle 쿼리 힌트 실전 예시 ]

△ FULL 힌트를 이용한 테이블 풀 스캔 강제

Oracle에서 특정 상황에서 인덱스 사용이 비효율적일 경우 테이블 전체 스캔을 강제할 수 있다.

SELECT /*+ FULL(users) */ * 
FROM users 
WHERE creation_date < TO_DATE('2020-01-01', 'YYYY-MM-DD');

• 설명: 인덱스를 사용하지 않고 테이블을 전체 스캔하도록 강제한다. 데이터의 분포상 인덱스 사용이 비효율적일 때 유용하다.
• 적용 시점: 테이블의 상당 부분이 필터링될 때, 인덱스 사용이 오히려 성능을 저하시킬 수 있는 상황.

 

△ INDEX 힌트를 사용해 특정 인덱스 강제 적용

Oracle에서는 인덱스를 선택적으로 사용하도록 힌트를 줄 수 있다.

SELECT /*+ INDEX(users user_email_idx) */ * 
FROM users 
WHERE email = 'test@example.com';

• 설명: user_email_idx 인덱스를 사용하여 쿼리를 실행하도록 강제한다.
• 적용 시점: 인덱스가 잘못 선택되거나 인덱스를 무시하는 경우.

 

△  PARALLEL 힌트를 이용한 병렬 처리

대용량 데이터를 처리할 때, 병렬 처리를 사용해 쿼리 성능을 개선할 수 있다.

SELECT /*+ PARALLEL(users, 4) */ * 
FROM users;

• 설명: users 테이블에 대해 4개의 병렬 프로세스를 사용하여 쿼리를 실행한다.
• 적용 시점: 데이터가 매우 많아 단일 프로세스에서 처리하기 힘들 때.

 

△ NO_MERGE 힌트를 이용해 서브쿼리 병합 방지

Oracle에서는 최적화기가 서브쿼리를 병합(Merge)하여 메인 쿼리와 함께 실행하려 할 수 있다. 이를 방지하고자 NO_MERGE 힌트를 사용할 수 있다.

SELECT /*+ NO_MERGE */ * 
FROM (SELECT * FROM users WHERE status = 'active');

• 설명: 서브쿼리를 독립적으로 처리하게 하여 병합되지 않도록 한다.
• 적용 시점: 서브쿼리 병합이 성능을 저하시킬 때.

 

△ LEADING 힌트로 조인 순서 지정

조인 최적화 시 특정 테이블을 먼저 처리하도록 강제할 수 있다.

SELECT /*+ LEADING(users) */ * 
FROM users 
JOIN orders ON users.id = orders.user_id;

• 설명: users 테이블을 먼저 처리한 후, 그 결과를 orders 테이블과 조인하도록 지시한다.
• 적용 시점: 조인 순서에 따라 성능이 크게 달라질 때.

 

[ ▷ SQL Server 쿼리 힌트 실전 예시 ]

△ FORCESEEK 힌트를 이용한 인덱스 탐색 강제

SQL Server에서 인덱스 탐색(Seek)을 강제하고, 스캔 대신 효율적인 탐색 방식으로 쿼리를 처리하도록 할 수 있다.

SELECT * 
FROM users WITH (FORCESEEK) 
WHERE id = 1;

• 설명: users 테이블에서 인덱스를 탐색하도록 강제한다. 이로 인해 테이블의 특정 행만 조회하게 되어, 전체 스캔 대신 효율적인 탐색이 수행된다.
• 적용 시점: 인덱스 탐색이 스캔보다 효율적일 때.

 

△ FORCESCAN 힌트를 이용한 테이블 또는 인덱스 스캔 강제

반대로 인덱스 탐색 대신 인덱스 또는 테이블 전체 스캔을 강제할 수도 있다.

SELECT * 
FROM users WITH (FORCESCAN) 
WHERE status = 'active';

• 설명: 인덱스 스캔을 강제하여 status 필드로 전체 데이터 범위를 검색한다.
• 적용 시점: 특정 필드로 전체 데이터를 스캔하는 것이 더 효율적일 때.

 

△ LOOP JOIN 힌트를 이용한 루프 조인 강제

SQL Server는 기본적으로 최적화기를 통해 조인 방식을 결정하지만, LOOP JOIN 힌트로 중첩 루프 조인을 강제할 수 있다.

SELECT * 
FROM users u 
JOIN orders o ON u.id = o.user_id 
OPTION (LOOP JOIN);

• 설명: 중첩 루프 조인을 사용하여 조인 연산을 수행하게 강제한다. 중첩 루프 조인은 작은 데이터 세트에 적합하다.
• 적용 시점: 데이터가 적거나 인덱스가 잘 설정된 경우에 성능을 높일 수 있습니다.

 

△ MERGE JOIN 힌트를 사용한 병합 조인 강제

MERGE JOIN 힌트는 병합 조인을 사용하도록 강제하여, 두 테이블이 모두 정렬된 상태에서 빠른 조인을 수행하도록 할 수 있다.

SELECT * 
FROM users u 
JOIN orders o ON u.id = o.user_id 
OPTION (MERGE JOIN);

• 설명: 병합 조인을 사용하여, 두 테이블이 모두 정렬된 경우 빠르게 데이터를 조인한다.
• 적용 시점: 두 테이블이 모두 정렬되어 있고 대량의 데이터를 조인할 때.

 

△ HASH JOIN 힌트를 이용한 해시 조인 강제

대량의 데이터셋에서 해시 조인을 강제할 수 있다.

SELECT * 
FROM users u 
JOIN orders o ON u.id = o.user_id 
OPTION (HASH JOIN);

• 설명: 해시 조인을 사용하여, 대량의 데이터를 효율적으로 조인한다.
• 적용 시점: 대량 데이터 조인 시 성능 향상을 위해 해시 조인을 강제해야 할 때.

 

 

쿼리 힌트는 데이터베이스 성능을 미세하게 튜닝할 수 있는 강력한 도구다. 하지만 잘못 사용하면 오히려 성능을 저하시킬 수 있으므로, 신중하게 적용하고 항상 테스트를 통해 그 효과를 검증하는 것이 중요하다.