DB 힌트 개념정리

소개 배경

최근 DB에 대해 공부하며, 힌트라는 개념이 약하다고 생각하여, 해당 개념을 정리하고자 포스팅하게 되었다.

우선 DB의 힌트 개념을 알기전 쿼리 실행 계획과, 쿼리 옵티마이저에 대한 개념을 익혀야한다.

쿼리 실행 계획(execution plan)

실행 계획은 DB에서 query를 수행할 때, 어떤 방식으로 데이터를 조회 및 처리할지 나타내는 계획하는 것이다.
이때 실행 계획은 DBMS에 의해 쿼리 옵티마이저가 최적의 방법을 선택하여, 생성한다.

쿼리 옵티마이저(query optimizer)

쿼리 옵티마이저란 쿼리를 처리하는데 있어, 가장 효율적인 방법을 선택하기 위해 여러가지 정보를 고려한다.

 

해당 정보에는 테이블의 크기, 인덱스의 유무, 통계 정보, 서버 자원 상황 등이 포함된다.

쿼리 옵티마이저는 위의 정보들을 기반으로, 실행계획을 여러가지 구성하게된다.

 

1. DB에서 데이터 조회 방법
2. 인덱스 사용 여부
3. 조인 순서
4. 데이터 정렬

쿼리 실행 계획과 쿼리 옵티마이저는 DB의 성능과 관련이 깊고, 적절한 실행 계획을 생성하고 최적의 처리 방법을 선택하는 쿼리 옵티마이저를 사용하면 쿼리 처리 속도가 빨라지고, DB의 성능이 개선된다.

DB 힌트

앞의 쿼리 실행 계획과 쿼리 옵티마이저 같은 경우 상당히 간단한 개념들로만 정의를 하였다.

DB 힌트를 이해하기 위해선 해당 개념을 알고 있어야했기에 간단히 설명했고, 정의는 다음과 같다.

💡 DB에서 힌트(Hint)란 쿼리 실행 계획을 만들 때, 쿼리 옵티마이저가 사용할 특별한 지시사항을 의미한다.

힌트란 SQL 튜닝의 핵심 부분으로 일종의 지시 구문이다. DB가 항상 최적의 실행 경로를 만들어 내기는 불가능하기 때문에 직접 최적의 실행 경로를 작성해 주는 것이다.

즉, 힌트란 쿼리 옵티마이저에게 어떤 방식으로 쿼리를 실행할지 제안을 하는 역할이다.

정리하자면, 개발자는 실행 계획을 직접 수정할일이 거의 없고 쿼리 옵티마이저가 자동으로 실행계획을 구성하여 주지만, 개발자가 해당방법이 최적의 방법이 아니라고 판단하여,

이를 수정하기 위해 직접 Hint를 제공하여 쿼리 옵티마이저에게 실행 계획의 구성방법을 최적으로 제공할 수 있다.

예를 들어, 인덱스를 사용하도록 지시하거나, 테이블 조인 순서를 제안하는 등의 방식이 있다.

 

하지만, 힌트는 적절하게 사용하지 않으면 오히려 성능 저하나 예상치 못한 결과를 초래할 수 있기 때문에, 고려를 해야하는 부분이 존재한다.

힌트의 사용이유

• 액세스 경로,조인 순서, 병렬 및 직렬 처리, Optimizer의 목표(Goal)를 변경하기 위해 사용한다.
• 데이터 값을 정렬해야 하는 경우, 힌트의 사용이 필요하다.
• 또한, 드라이빙 테이블을 원하는 대로 선정하고자 할 때도 사용된다.
• 성능 향상 : DB 힌트를 사용하면, 최적화 엔진이 실행계획을 선택하는데 도움을 준다.
• 예측 가능한 실행 계획 : DB 힌트를 사용하면, 개발자의 의도대로 일관된 실행계획을 유지할 수 있다.
• 특정 상황에 대응 가능 : DB 힌트를 사용하면 데이터베이스 엔진이 예상하지 못한 상황에서도 특정 실행 계획을 선택하도록 지시할 수 있다.

DB 힌트 종류

대표적으로 사용되는 RDB인 MySql과 Oracle DB에서는 서로 다른 힌트를 제공한다.

 

Oracle DB 대표 Hint - OPTIMIZER MODE 지정 값

1. /*+ ALL_ROWS */

• 목적 : Best Throughput
• 용 도 : 전체 RESOURCE 소비를 최소화시키기 위한 힌트. Cost-Based 접근방식으로 ALL_ROWS는 Full Table Scan을 선호하며, CBO(Cost Based Optimization)는 default로 ALL_ROWS를 선택한다.

2. /*+ FIRST_ROWS */

• 목적 : Best Response Time
• 용도 : 조건에 맞는 첫 번째 row를 리턴하기 위한 Resource 소비를 최소화시키기 위한 힌트이며 Cost-Based 접근방식을 사용

Oracle DB 대표 Hint - Access Methods(접근 방법)

1. /*+ FULL(table_name) */

• Table을 Full Scan 하길 원할 때 사용

2. /*+ HASH(table) */

• Hash scan을 선택하도록 지정 (HASHKEYS Parameter로 만들어진 Cluster내에 저장된 Table에만 적용)

3. /*+ CLUSTER(table_name) */

• Cluster Scan을 선택하도록 지정. 따라서 Clustered Object만 적용

Oracle DB 대표 Hint - Join Orders

1. /*+ ORDERED */

• From절에 기술된 테이블 순서대로 join이 일어나도록 유도

Mysql 힌트

MYSql의 경우 원래 인덱스 힌트와 옵티마이저 힌트로 구성되어있었으나 Mysql 5.7 버전 이후에는 인덱스 힌트를 사용하지 않고 옵티마이저 힌트를 사용한다.

Mysql - 옵티마이저 힌트

• GLOBAL : 전체 쿼리 블록에 영향을 끼치는 힌트
• QUERY BLOCK : 특정 쿼리 블록에 사용할 수 있는 힌트로서 힌트가 명시된 쿼리블록에만 힌트가 영향을 미친다.
• TABLE-LEVEL : 특정 테이블의 이름을 사용할 수 있는 힌트
• INDEX-LEVEL : 특정 인덱스의 이름을 사용할 수 있는 힌트

 

 

결론

DB 개념을 익히고자 정리해본 글이지만, 정말 DB 최적화와 관련된 분야는 깊이팔수록 어렵게 다가온다. 근본적인 공부와 그를 실제 활용을 통한 학습을 추구해야겠다.