옵티마이저(Optimizer)
옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다. 개발자가 작성한 SQL을 실행하면 DBMS에서는 옵티마이저를 기반으로 실행계획을 세워서 쿼리를 실행한다.
실행 계획
실행 계획은 데이터베이스가 쿼리를 실행하기 위해 세운 구체적인 작업 순서와 방법이다. 쿼리를 실행하려면 여러 가지 결정을 내려야 한다. 테이블에서 데이터를 어떻게 가져올지, 여러 테이블을 조인한다면 어떤 순서로 합칠지, 어떤 조인 방식을 사용할지, 정렬은 언제 할지 같은 것들이다. 이런 모든 결정사항들을 단계별로 정리한 것이 실행 계획이다.
예를 들어 직원 테이블과 부서 테이블을 조인하는 쿼리가 있다고 하면, 실행 계획에는 먼저 부서 테이블에서 특정 조건에 맞는 데이터를 인덱스로 찾고, 그 다음 직원 테이블과 Nested Loop 방식으로 조인하고, 마지막으로 급여 기준으로 정렬한다는 식의 구체적인 단계들이 담긴다.
같은 쿼리라도 실행 계획은 여러 개가 가능하다. 직원 테이블을 먼저 읽을 수도 있고 부서 테이블을 먼저 읽을 수도 있고, Full Table Scan을 할 수도 있고 인덱스를 사용할 수도 있고, Hash Join을 쓸 수도 있고 Nested Loop Join을 쓸 수도 있다. 옵티마이저가 하는 일이 바로 이런 여러 실행 계획 중에서 가장 효율적인 것을 선택하는 것이다.
실행 계획을 확인하려면 데이터베이스마다 제공하는 명령어를 사용하면 된다. MySQL에서는 EXPLAIN, Oracle에서는 EXPLAIN PLAN 같은 명령어로 옵티마이저가 선택한 실행 계획을 볼 수 있다.
쿼리 실행 절차
그렇다면 우리는 사용자가 작성한 SQL쿼리가 어떻게 실행되고 어디서 어떻게 옵티마이저가 사용되는지어 우리에게 결과가 돌아오는지 알아볼 필요성이 있다.
쿼리의 실행절차를 크게 나누게 되면 아래의 4단계로 볼 수 있다.
Parsing > Optimization > Generation > Execution
SQL Parsing
개발자가 SQL을 입력하면 파서가 SQL의 문법, 권한 검사, 테이블 및 컬럼이 실제 존재하는지 등을 확인하고 쿼리를 내부적으로 이해할 수 있는 구조인 파싱 트리로 변환한다. SQL문법이 잘못되었다면 이 단계에서 걸러지며, 정상적이라면 SQL파싱 트리가 만들어진다.
Optimization
파싱단계에서 만들어진 SQL파싱트리를 기반으로 옵티마이저가 가진 여러가지 동작을 수행한다.
- Query Transformer: 파싱된 SQL을 보고 같은 결과를 도출하되, 좀 더 나은 실행 계획을 갖는 SQL로 변환이 가능한지를 판단하여 변환 작업을 수행한다. 이 때 불필요한 조건을 제거하거나 복잡한 연산을 단순화 시키는 등의 작업을 수행한다.
- Estimator : 시스템 통계정보를 딕셔너리로부터 수집하여 SQL을 실행할 때 소요되는 총비용을 계산한다.
- Plan Generator : Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행계획을 도출한다.
Generation
옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드나 프로시저 형태로 포맷팅한다.
Execution
포맷팅된 SQL을 실행하고 결과를 사용자에게 전달한다.
옵티마이저의 종류
옵티마이저는 최적화 기반을 기준으로 나누는 규칙기반 옵티마이저와, 비용기반 옵티마이저가 있다.
규칙 기반 최적화는 각 테이블이나 인덱스의 통계 정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스럽다는 이유로 사용되던 최적화 방법이지만, 현재는 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있다.
규칙기반 옵티마이저(RBO : Rule-Based Optimizer)
RBO는 실행 계획을 결정할 때 미리 정해둔 규칙에 따라 쿼리를 최적화하는 방식이다.
RBO는 쿼리 실행 계획을 만들 때 일반적으로 사용되는 JOIN, WHERE 등의 SQL 구문에 대한 고정 규칙을 적용한다. 일반적으로 규칙을 세워둘 때 실행 속도가 빠른순으로 규칙을 세워두고 우선순위가 높은 방법을 채택하여 실행계획을 세운다.
규칙이란 액세스 경로별 우선순위로서, 인덱스 구조, 연산, 조건절 형태가 순위를 결정짓는 주요인이 된다.
DBMS에서 제공하는 RBO의 경우, 대부분의 경우 규칙의 우선순위를 사용자가 변경할 수 없다. 그러나 일부 DBMS(오라클의 ORSERED hint)에서는 사용자가 규칙의 우선순위를 변경할 수 있는 설정을 제공하기도 한다.
일반적으로 고정된 RBO의 우선순위는 다음과 같다.
비용기반 옵티마이저(CBO : Cost-Based Optimizer)
CBO는 실행 계획을 선택하기 위해 쿼리를 수행하는데 소요되는 일 량 또는 시간을 기반으로 최적화하는 방식이다. CBO는 실행 계획을 최대 2천 개까지 세운 뒤 비용이 최소한으로 나온 실행 계획을 수행하게 된다.
이때 실행계획을 수립할 때 판단 기준이 되는 비용은 예상치다. 따라서 CBO는 비용을 예측하기 위해 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보 및 시스템 통계정보(CPU 속도, 디스크 I/O 속도 등)를 이용한다.
통계정보가 없는 경우 비효율적인 실행계획을 생성할 수 있으므로, 정확한 통계정보를 유지하는 것이 중요하다.
규칙기반 vs 비용기반
규칙기반 옵티마이저는 미리 정해진 우선순위 규칙에 따라 실행 계획을 선택한다. 예를 들어 인덱스가 있으면 무조건 인덱스를 사용하고, 여러 인덱스가 있으면 정해진 우선순위에 따라 선택하는 식입니다. 실제 데이터의 분포나 양과는 상관없이 규칙만 따르기 때문에, 통계 정보를 수집하거나 비용을 계산하는 과정이 필요 없다. 빠르고 단순하지만, 실제 데이터 상황을 반영하지 못해서 비효율적인 계획을 선택할 수 있다.
비용기반 옵티마이저는 여러 실행 계획 후보들의 예상 비용을 계산해서 가장 비용이 적은 방향으로 실행 계획을 세운다. 이 과정에서 테이블의 크기, 데이터 분포, 인덱스 상태 같은 통계 정보를 활용한다. 같은 쿼리라도 데이터 상황에 따라 다른 실행 계획을 선택할 수 있어서 훨씬 유연하고 효율적이다. 다만 통계 정보 수집과 비용 계산에 시간이 걸리고, 통계가 부정확하면 잘못된 계획을 선택할 수도 있다.
현대의 대부분 데이터베이스는 비용기반 옵티마이저를 사용하고, 규칙기반은 거의 사용하지 않는다. 오라클도 10g 버전 이후로는 규칙기반을 deprecated 시켰다.
옵티마이저에 영향을 미치는 요소
SQL과 연산자 형태
결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있다.
옵티마이징 팩터
쿼리를 똑같이 작성하더라도, 인덱스, IOT, 클러스터링, 파티셔닝 등을 어떻게 구성했는지에 따라 실행계획과 성능이 크게 달라진다.
DBMS 제약 설정
개체 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS가 제공하는 PK, FK, Check, Not Null 같은 제약 설정 기능을 이용할 수 있고, 이들 제약 설정은 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공한다.
예를 들어, 인덱스 칼럼에 Not Null 제약이 설정돼 있으면 옵티마이저는 전체 개수를 구하는 Count 쿼리에 이 인덱스를 활용할 수 있다.
옵티마이저 힌트
옵티마이저의 판단보다 사용자가 지정한 옵티마이저 힌트가 우선한다.
통계 정보
통계정보가 옵티마이저에게 미치는 영향력은 절대적이다. CBO의 모든 판단 기준은 통계정보에서 나온다. 주요 통계 정보들에는 다음과 같은 것들이 있다.
옵티마이저 관련 파라미터
SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일하더라도 DBMS 버전을 업그레이드하면 옵티마이저가 다르게 작동할 수 있다. 이는 옵티마이저 관련 파라미터가 추가 또는 변경되면서 나타나는 현상이다.
DBMS 버전과 종류
옵티마이저 관련 파라미터가 같더라도 버전에 따라 실행계획이 다를 수 있다. 또한, 같은 SQL이더라도 DBMS 종류에 따라 내부적으로 처리하는 방식이 다를 수 있다.
옵티마이저는 SQL을 수행할 수 있는 가장 최적의 방법을 찾는 DBMS의 핵심 엔진이다. 개발자가 생성한 SQL을 실행하면 SQL을 파싱해서 최적화하고 실행할 수 있는 형태로 만들어 SQL 실행 엔진이 실행하게 되는데, 최적화 단계를 옵티마이저가 수행한다. 옵티마이저 종류에는 규칙 기반과 비용 기반이 있다. 규칙 기반은 사전에 정의된 규칙에 따라서 쿼리를 최적화하는 방식이다. 일반적으로 규칙을 세워둘 때 실행 속도가 빠른순으로 규칙을 세워두고 우선순위가 높은 방법을 채택하여 실행계획을 세운다. 비용기반은 쿼리를 수행하는데 소요되는 비용을 계산하여서 비용이 최소한으로 나온 실행계획을 수행한다. 테이블 크기나 인덱스 분포, 데이터 분포 등과 같은 통계 정보를 활용한다. 반면 규칙 기반은 실제 데이터 상황을 전혀 고려하지 않아서 데이터가 변해도 같은 실행 규칙을 수행한다. 예를 들어 테이블이 몇개 없을 때는 풀테이블스캔을 하는 것이 더 속도가 빠른데, 규칙 기반은 인덱스가 있다면 무조건 인덱스를 사용하게되어 비효율적인 방식을 수행하게 될 수도 있다. 그래서 요즘에는 대부분 비용기반 옵티마이저를 사용한다.
옵티마이저라고 해서 항상 같은 결과를 내는 것이 아니다. DBMS의 제약 설정이나 통계 정보, 관련 파라미터 등에 따라서 다른 계산이 나올 수 도 있다.
