쿼리튜닝의 핵심, 옵티마이저에 대해서

옵티마이저(영어: Optimizer)는 데이터베이스 관리 시스템 (DBMS)에서 쿼리를 처리할 때 최적의 실행 계획을 결정하는 역할을 하는 부분입니다.

 

 

DBMS는 사용자로부터 요청받은 SQL 쿼리를 어떻게 처리할지 결정하는 과정에서 옵티마이저를 사용합니다.

옵티마이저의 주요 목표는 다음과 같습니다:

1. 성능 최적화:
   • 목표: 쿼리를 가장 빠르게 실행하여 사용자에게 빠른 결과를 제공하는 것.
   • 예시: 아래의 쿼리는 users 테이블에서 사용자 이름이 "John"인 레코드를 찾는 쿼리입니다. 옵티마이저는 인덱스를 사용하여 레코드를 빠르게 찾습니다.

SELECT * FROM users WHERE name = 'John';

 

2. 리소스 최적화:

• 목표: 제한된 컴퓨터 자원을 효율적으로 활용하여 다른 작업에 영향을 주지 않도록 하는 것.
• 예시: 여러 사용자가 동시에 데이터베이스에 액세스하고 있을 때, 각각의 쿼리는 자원(예를 들어, 메모리)를 공평하게 사용하여 시스템의 부하를 분산시킵니다.

 

3. 최적화 원칙:

• 목표: 데이터베이스의 구조와 통계 정보를 기반으로 쿼리 실행 계획을 결정하여 최상의 결과를 얻는 것.
• 예시: JOIN을 사용하여 두 개의 테이블을 결합하는 쿼리에서, 옵티마이저는 어떤 테이블을 먼저 스캔하고 어떤 인덱스를 사용해야 가장 효율적인지 결정합니다.

SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;

 

 

4. 다양한 실행 계획 생성:

• 목표: 여러 다양한 실행 계획을 생성하고, 그 중에서 비용 효율적인 것을 선택하는 것.
• 예시: 쿼리에서 여러 조건이 있는 경우, 옵티마이저는 여러 가지 실행 계획을 고려하며, 가장 효율적인 순서와 조인 방법을 선택합니다. 이렇게 하여 서로 다른 실행 계획을 비교하여 최상의 성능을 얻을 수 있습니다.

 

옵티마이저의 유형은 데이터베이스 시스템마다 다를 수 있으며, 대표적으로 두 가지 유형이 있습니다:

규칙 기반 옵티마이저 (Rule-Based Optimizer, RBO)

규칙 기반 옵티마이저는 미리 정의된 규칙 집합을 사용하여 쿼리 실행 계획을 결정하는 방식입니다.

각 규칙은 특정한 쿼리 패턴에 대해 어떤 실행 계획을 사용해야 하는지를 정의합니다.

규칙 기반 옵티마이저는 일반적으로 간단한 쿼리와 작은 데이터베이스에서 사용됩니다.

예를 들어, 특정 조인 순서를 따르거나 인덱스를 사용해야 한다는 규칙을 가지고 있을 수 있습니다.

장점:

• 간단하고 예측 가능합니다.
• 규칙에 따라 실행 계획이 항상 동일하므로 성능 예측이 용이합니다.

단점:

• 모든 쿼리 패턴을 다루기 어렵습니다.
• 최적의 실행 계획을 찾지 못할 수 있으며, 성능이 최적이 아닐 수 있습니다.

비용 기반 옵티마이저 (Cost-Based Optimizer, CBO):

비용 기반 옵티마이저는 각 실행 계획에 대한 예상 비용을 계산하고, 가장 비용이 낮은 실행 계획을 선택합니다.

이러한 비용은 쿼리 실행에 소요되는 시간, I/O 작업, 메모리 사용 등을 고려하여 계산됩니다.

비용 기반 옵티마이저는 현대 데이터베이스 시스템에서 주로 사용됩니다.

장점:

• 다양한 쿼리 패턴을 다룰 수 있습니다.
• 최적의 실행 계획을 선택하여 성능을 최적화할 수 있습니다.

단점:

• 실행 계획을 결정하는 데 높은 계산 비용이 필요합니다.
• 최적의 실행 계획을 찾는 과정이 복잡하며, 때로는 최악의 경우 성능 문제를 일으킬 수 있습니다.

비용 기반 옵티마이저는 현대 데이터베이스에서 흔히 사용되며, 데이터베이스의 통계 정보와 인덱스 선택, 조인 순서 등을 고려하여 실행 계획을 결정합니다. 규칙 기반 옵티마이저보다는 더 복잡하지만, 더 효율적인 실행 계획을 찾을 수 있는 장점이 있습니다.