DataBase - 옵티마이저란?

옵티마이저란?

옵티마이저란 말 그대로 최적화시켜주는 장치를 의미합니다.

Optimise(최적화) + er → 옵티마이저!

모든 DBMS에서는 쿼리의 실행계획을 수립하는 옵티마이저를 가지고 있고, 굉장히 복잡한 부분이기에 이해하는 것도 상당히 어렵습니다. 이때 중요한 것들 중 하나는 실행 계획입니다. 실행 계획에 대해 잘 이해해야만 실행 계획의 불합리한 부분을 찾아내서 더 최적화된 방법으로 수립할 수 있습니다.

옵티마이저의 종류

옵티마이저의 종류는 크게 두 가지로 나뉩니다. 비용 기반 최적화와 규칙 기반 최적화 입니다.

규칙 기반 옵티마이저

위와 같이 미리 정의된 규칙을 기반으로 최적화된 실행계획을 생성한다. 실행계획을 생성하는 규칙을 이해하면 누구나 실행계획을 비교적 쉽게 예측할 수 있다. 옵티마이저가 참조하는 정보에는 인덱스 유무와 종류, 연산자의 종류, 참조 객체의 종류 등이 있다. 이러한 참조 정보에 따라 우선순위의 규칙이 정해져 있고 이 순위를 기반으로 실행계획을 생성한다. 결과적으로 규칙기반 옵티마이저는 우선순위가 높은 규칙이 적은 일량으로 해당 작업을 수행하는 방법이라고 판단하는 것이다.

비용기반 옵티마이저

처리 비용기 가장 적은 실행계획을 선택하는 방식이다. 비용을 예측하기 위해서 규칙기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 컬럼 등의 다양한 객체 통계정보와 시스템 통계정보를 이용한다. 통계정보가 정확하지 않을 경우 정확한 비용 예측이 불가능하여 비효율적인 실행계획을 생성할 수 있다. 정확한 통계정보를 유지하는 것은 비용기반 최적화에서 중요한 요소이다.

⇒ 통계를 기반으로 판단하기 때문에 통계 정보를 유지하는 것이 가장 중요하다.

위 그림에 나와있는 내용은

• 질의 변환기 : 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈
• 대안계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈
• 비용예측기 : 통계 데이터를 통해 비용을 예측한다.

실행계획이란?
쿼리 실행 계획(Query Execution Plan)은 데이터베이스 시스템이 주어진 쿼리를 실행하기 위해 취하는 작업들의 계획을 나타냅니다. 이 계획은 쿼리를 처리하기 위해 데이터베이스 엔진이 수행할 작업들의 순서와 방법을 설명합니다. 간단히 말하면, 쿼리 실행 계획은 데이터베이스 엔진이 데이터를 가져오고 조작하는 방법을 제어하는 로드맵입니다.

옵티마이저는 실행계획을 수립한다.

이전에 질문 받았던 “옵티마이저는 쿼리를 수정하는 역할도 하나요?” 라는 얘기에 대한 이야기이다. 어떻게 보면 그렇다고 말했는데, 사실 그런 것이 아니라 규칙을 기준으로 그리고 통계 정보를 기준으로 쿼리를 어떻게 실행에 옮겨야할지 수립하는 것이다.

SELECT * FROM products 
WHERE category = 'Electronics' AND price > 1000;

위와 같은 쿼리가 있을 때 옵티마이저는 아래와 같이 실행계획을 수립한다.

1. 인덱스 스캔(Index Scan):
   1. 옵티마이저는 “category”열을 기반으로 한 인덱스를 사용하여 조건에 맞는 레코드를 선택합니다.
   2. 그런 다음 선택된 레코드 중에서 “price”가 1000보다 큰 것을 필터링합니다.
2. 필터링과 조인(Filtering and Join):
   1. 옵티마이저는 전체 테이블을 스캔하여 “cateogry”가 ‘Electronics’이고 “price”가 1000보다 큰 레코드를 찾습니다.
   2. 그런 다음 필터링된 결과를 반환합니다.
3. 조인과 정렬(Join and Sort):
   1. 옵티마이저는 다른 테이블과의 조인을 수행하거나 결과를 정렬하는 추가적인 작업을 수행할 수 있습니다.

위와 같은 과정들을 맨 처음 이야기했던 비용기반 옵티마이저와 규칙기반 옵티마이저가 각각 처리해주는 것입니다. 옵티마이저는 이러한 실행계획을 선택함으로써 쿼리의 성능을 최적화하고, 데이터베이스 시스템의 자원을 효율적으로 사용할 수 있도록 합니다.

옵티마이저 행동에 영향을 미치는 요소

1. SQL과 연산자 형태

   결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있습니다.

   [MYSQL] SQL 쿼리문 최적화 - 효율적인 쿼리를 위한 팁

2. 옵티마이징 팩터

   쿼리를 똑같이 작성하더라도 인덱스, IOT(Index-Organized Table), 클러스터링, 파티셔닝 등을 어떻게 구성했는지에 따라 실행계획과 성능이 크게 달라집니다.

   인덱스는 로우별 색인을 남기는 것인데 특정 열에 대한 검색 및 조회 성능을 향상시키는 데 사용되는 데이터 구조입니다.
   IOT는 인덱스 기반 테이블을 의미합니다. 인덱스와 테이블 데이터를 하나의 구조로 결합하여 저장합니다.
   클러스터링(Database Clustering)은 여러 대의 독립적인 데이터베이스 서버를 하나의 시스템으로 묶는 기술 또는 방법
   파티셔닝은 특정 기준에 따라서 테이블이나 인덱스를 논리적 또는 물리적인 기준에 따라 여러 개의 파티션으로 나누는 것을 말합니다. 각 파티션은 독립적으로 관리되며, 특정 기준에 따라 데이터가 분산되어 저장됩니다.

3. DBMS 제약 설정

   개체 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS가 제공하는 PK, FK, Check, NotNull 같은 제약 설정 기능을 이용할 수 있고, 이들 제약 설정은 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공합니다. 예를 들어 인덱스 칼럼에 Not Null 제약이 설정돼 있으면 옵티마이저는 전체 개수를 구하는 Count 쿼리에 이 인덱스를 활용할 수 있습니다 .

4. 옵티마이저 힌트

   옵티마이저의 판단보다 사용자가 지정한 옵티마이저 힌트가 우선합니다. 아래의 코드가 그 예시입니다. 아래 /*+ INDEX(employees idx_last_name) */ 이것이 바로 그 힌트입니다.

   SELECT /*+ INDEX(employees idx_last_name) */
   COUNT(*)
   FROM employees
   WHERE last_name = 'Smith';

   이 힌트를 활용할 경우 만약 옵티마이저가 인덱스를 사용하지 않고 전체 테이블 스캔을 선택하는 설정이 기본값이었다면, 우리가 힌트로 넘겨준 특정 인덱스를 사용하도록 유도할 수 있습니다.

5. 통계 정보

   통계정보가 옵티마이저에게 미치는 여향력은 절대적이다. 비용기반 옵티마이저의 모든 판단 기준은 통계정보에서 나온다.

옵티마이저의 한계

1. 옵티마이징 팩터의 부족
   옵티마이저는 주어진 환경에서 최적의 실행계획을 수립하기 위해 정해진 기능을 수행할 뿐입니다. 사용자가 적절히 옵티마이징 팩터( 효과적으로 구성된 인덱스, IOT, 클러스터링, 파티셔닝 등)를 제공하지 않는다면 좋은 실행계획을 수립할 수 없습니다.
2. 통계정보의 부정확성
   최적화에 필요한 모든 정보를 수집해서 보관할 수 있다면 옵티마이저도 그만큼 고성능 실행계획을 수립하겠지만, 100% 정확한 통계정보를 유지하기는 불가능합니다.
3. 하드웨어의 성능
   옵티마이저는 기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양미 맞춰져 있기에 실제 운영 시스템의 하드웨어 사양과 다를 때 옵티마이저가 잘못된 실행계획을 수립할 가능성도 높아집니다. 또한 애플리케이션 특성 (I/O 패턴, 부하 정도)에 의해서도 하드웨어 성능은 달라집니다.

옵티마이저는 만능이 아닙니다. 칼럼의 통계 정보만 가지고는 조건절에서 사용된 조건을 만족하는 데이터의 양이 어느정도인지 알 수가 없기에 비용 계산 결과가 정확하지 않습니다. 그리고 비용산정시 쿼리문 단독으로 실행된다고 가정하기에 운영 서버와 같이 쿼리가 동시 실행된다면 실제 비용은 달라질 수 있습니다.

따라서 쿼리 튜닝을 할 때에는 쿼리문의 실행계획을 꼭 보고, 옵티마이저가 비효율적으로 작동하고 있다면 오라클의 힌트 같은 부가적인 장치를 통해 올바르게 작동될 수 있도록 유도하는 방법도 필요합니다