데이터베이스(DB) 옵티마이저 란?

 

1. 데이터베이스 옵티마이저란? 

옵티마이저(Optimizer)는 데이터베이스에서 최적의 실행 계획을 선택하는 역할을 합니다. 실행 계획이란 쿼리를 어떻게 처리할지 결정하는 로드맵 같은 것입니다.

옵티마이저의 주요 역할

• 통계 정보 기반 분석: 테이블의 데이터 분포와 크기 등을 고려
• 다양한 실행 경로 비교: 여러 접근 방법(Index Scan, Full Table Scan 등)을 비교
• 비용 기반 최적화: 가장 비용이 적은 경로 선택

예를 들어, 아래와 같은 쿼리가 있다고 가정해봅시다:

SELECT *
FROM orders
WHERE order_date = '2023-12-01';

옵티마이저는 다음과 같은 질문을 스스로 던집니다:

1. 해당 테이블에 인덱스가 있나요?
2. 데이터가 얼마나 많을까요?
3. 인덱스를 사용하는 것이 더 빠를까요, 아니면 테이블 전체를 읽는 것이 더 빠를까요?

결국 옵티마이저는 가장 효율적인 방식으로 쿼리를 실행하도록 결정합니다.

---

2. 실행 계획과 예시

실행 계획을 확인하는 방법은 데이터베이스 시스템마다 다릅니다. Oracle, MySQL, PostgreSQL 등 다양한 DBMS에서 실행 계획을 확인할 수 있는 명령어가 제공됩니다.

Oracle에서 실행 계획 확인하기

EXPLAIN PLAN FOR
SELECT *
FROM orders
WHERE order_date = '2023-12-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

이 결과는 테이블 스캔 방식, 사용된 인덱스, 예상 비용 등을 보여줍니다. 예를 들어:

| ID | OPERATION         | OBJECT_NAME | COST |
|----|-------------------|-------------|------|
| 1  | TABLE ACCESS FULL | ORDERS      | 100  |

MySQL에서 실행 계획 확인하기

EXPLAIN
SELECT *
FROM orders
WHERE order_date = '2023-12-01';

결과 예시:

| id | select_type | table  | type | possible_keys | key    | rows | Extra       |
|----|-------------|--------|------|---------------|--------|------|-------------|
| 1  | SIMPLE      | orders | ref  | idx_order_date| PRIMARY|  500 | Using index |

---

3. 옵티마이저가 실행 계획을 선택하는 과정

옵티마이저가 실행 계획을 결정하는 과정은 다음과 같은 단계로 나눌 수 있습니다:

1) 통계 정보 수집

옵티마이저는 테이블과 인덱스의 통계 정보를 바탕으로 데이터 분포를 분석합니다. 이 정보는 ANALYZE TABLE 명령어를 통해 갱신할 수 있습니다.

ANALYZE TABLE orders COMPUTE STATISTICS;

2) 가능한 실행 경로 탐색

옵티마이저는 여러 가지 실행 방법을 고려합니다:

• Full Table Scan: 테이블의 모든 데이터를 읽는 방식
• Index Scan: 인덱스를 통해 특정 데이터만 읽는 방식
• Hash Join / Nested Loop Join: 두 테이블을 조인할 때 사용

3) 비용 계산

옵티마이저는 각 실행 경로의 비용을 계산합니다. 비용은 주로 다음을 기준으로 평가됩니다:

• 디스크 I/O 횟수
• 메모리 사용량
• 네트워크 전송 비용

4) 최적의 실행 계획 선택

최종적으로 가장 낮은 비용의 실행 계획이 선택됩니다.

---

4. 옵티마이저 관련 최적화

1. 인덱스 활용: 필요한 컬럼에 적절한 인덱스를 생성하세요. 예시: CREATE INDEX idx_order_date ON orders(order_date);
2. 통계 정보 최신화: 통계 정보가 오래되면 옵티마이저가 잘못된 실행 계획을 선택할 수 있습니다. 주기적으로 통계를 갱신하세요.
3. 힌트 사용: 옵티마이저가 실행 계획을 잘못 선택하는 경우, 힌트를 사용해 원하는 실행 방식을 강제할 수 있습니다. 예를 들어: SELECT /*+ INDEX(orders idx_order_date) */ * FROM orders WHERE order_date = '2023-12-01';
4. 서브쿼리 최적화: 서브쿼리를 사용하는 경우, 가능한 경우 JOIN으로 변환.

---

5. 잘못된 실행 계획의 원인과 해결책

옵티마이저가 항상 완벽한 것은 아닙니다. 다음과 같은 이유로 잘못된 실행 계획이 선택될 수 있습니다:

• 통계 정보 부족: 통계 정보가 없거나 오래되었을 경우

• 복잡한 쿼리: 쿼리가 지나치게 복잡할 경우
• 비효율적인 인덱스 설계: 쿼리에 맞지 않는 인덱스 사용

해결 방법

1. 통계 정보 갱신: EXEC DBMS_STATS.GATHER_TABLE_STATS('orders');
2. 실행 계획 검토 후 힌트 적용
3. 쿼리 리팩토링

---