SQL 최적화의 기본원리

데이터베이스를 활용해 애플리케이션을 만드는 것은 애플리케이션 개발에 있어서 큰 한 부분을 차지한다.

하지만 데이터베이스의 기초지식없이 단지 원하는 데이터를 가져오기 위한 복붙과 복잡한 SQL 쿼리로 데이터만을 가져오는데 집중하다 보면 나중에 커다란 대가를 치르게 될지도 모른다.

 

내가 DBA를 진로로 삼지않았지만 그래도 공통적인 부분에 대해선 공부를 하고 가는 게 맞다고 생각해 SQLD를 공부하면서 최적화에 대해 나중에 찾아볼 수 있도록 글을 남긴다.

 

 

옵티마이저의 실행 계획

 

1. 옵티마이저(Optimizer)

 

 - 옵티마이저라는 단어는 IT카톡방이든 네이버든 구글이든 최적화에 관련해 검색을 할때 빠지지 않고 나오는 부분이다. 이 옵티마이저는 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행한다. 이러한 최적의 실행방법을 실행 계획(Exection Plan)이라고 한다. 관계형 데이터베이스는 궁극적으로 SQL문을 통해서만 데이터를 처리할 수 있다. 다른 프로그래밍 언어와 달리 SQL은 사용자의 요구사항만 기술할 뿐 처리과정에 대한 기술을 하지 않는다. 그러므로 사용자의 요구사항을 만족하는 결과를 찾는 방법은 여러 개가 존재할 수 있고 그중에 적합한 SQL문을 옵티마이저가 찾아 요구사항에 맞는 데이터를 추출한다.

 

다양한 실행방법들 중에서 최적의 실행 방법을 결정하는 것이 바로 옵티마이저의 역할이다. 관계형 데이터베이스는 옵티마이저가 결정한 실행 방법대로 실행 엔진이 데이터를 처리하여 결과 데이터를 사용자에게 전달할 뿐이다.

옵티마이저가 선택한 실행 방법의 적절성 여부는 질의의 수행 속도에 큰 영향을 미치게 된다.

여기서 최적의 실행 방법 결정이라는 것은 어떤 방법으로 처리하는 것이 최소 일량으로 동일한 일을 처리할 수 있을지 결정하는 것이다.

 

그러나 옵티마이저는 실제로 SQL문을 처리해보지 않은 상태에서 다양한 실행계획들을 결정해야 하는 어려움을 가지고 있다.

 

옵티마이저는 실행계획을 선택해야하는데 이 방법을 결정하는 방식에 따라 2가지의 옵티마이저가 존재한다.

 

첫 번째는 규칙 기반 옵티마이저(RBO, Rule Based Optimizer)

두 번째는 비용 기반 옵티마이저(CBO, Cost Based Optimizer)

 

현재 대부분의 관계형 데이터베이스는 비용 기반 옵티마이저만을 제공하는데 하위 버전 호환성을 위한 규칙 기반 옵티마이저가 남아있긴 하다. 그렇지만 규칙 기반 옵티마이저의 규칙은 보편 타당성에 근거한 것들이기 때문에 규칙을 공부할 필요가 있다고 한다.

 

 

1. 규칙기반 옵티마이저

 

 규칙기반 옵티마이저는 우선순위를 가지고 실행계획을 생성한다.

실행계획을 생성하는 규칙을 이해하면 누구나 쉽게 실행계획을 예측할 수 있기 때문이다. 규칙 기반 옵티마이저가 실행계획을 생성할 때 참조하는 정보에는 SQL문을 실행하기 위해서 이용 가능한 인덱스 유무와 종류, SQL문에서 사용하는 연산가의 종류 그리고 SQL문에서 참조하는 객체(힙 테이블, 클러스터 테이블)등의 종류가 있다.

 

결과적으로 규칙기반 옵티마이저는 우선순위가 높은 규칙이 적은 일 량으로 해당 작업을 수행하는 방법이라고 판단한다.

 

우선순위 1  Single Row by RowId

 

- ROWID를 통해서 테이블에서 하나의 행을 액세스하는 방법이다. ROWID는 행이 포함된 데이터 파일, 블록 등의 정보를 가지고 있기 때문에 다른 정보를 참조하지 않고도 바로 원하는 행을 액세스 할 수 있다.

 

 

우선순위 4 Single Row by Unique or Primary Key

 

- 유일 인덱스 (Unique Index)를 통해서 하나의 행을 액세스하는 방식이다. 이 방식은 인덱스를 먼저 액세스하고 인덱스에 존재하는 ROWID를 추출하며 테이블의 행을 액세스 한다.

 

우선순위 8 Composite Index

 

 - 복합 인덱스에 동일 조건으로 검색하는 경우이다.  복합 인덱스가 여러개가 존재한다고 해도  " = " 로 값이 주어질수록 우선순위가 높게 부여되어 해당되는 복합 인덱스가 선택되어 사용된다.

 

우선순위 9 Single Column Indexes

 

 - 단일 칼럼 인덱스에 " = " 조건으로 검색하는 경우이다.

 

우선순위 10 Bounded Range Search On Indexed Column

 

 - 인덱스가 생성되어 있는 칼럼에 양쪽 범위를 한정하는 형태로 검색하는 경우이다. 이러한 연산자는 Between , Like 등이 있다. 

 

우선순위 11 Unbounded Range Search On Indexed Columns

 

 - 인덱스가 생성된 칼럼에 한쪽 범위만 한정하는 형태로 검색하는 형태이다 <, > , <= , >= 등이 있다.

 

우선순위 15 Full Table Scan 

 

- 전체 테이블에 액세스하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출한다.

 

규칙 기반 옵티마이저는 인덱스를 이용한 액세스 방식이 전체 테이블 액세스 방식보다 우선순위가 기본적으로 높다. 

따라서 규칙 기반 옵티마이저는 해당 SQL문에서 이용 가능한 인덱스가 존재한다면 전체 테이블 액세스 방식보다는 항상 인덱스를 사용하는 실행 계획을 생성한다.

 

규칙 기반 옵티마이저가 조인 순서를 결정할 때는 조인 칼럼 인덱스의 존재 유무가 중요한 판단의 기준이다. 조인 칼럼에 대한 인덱스가 양쪽 테이블에 모두 존재한다면  우선순위를 고려해 선행 테이블을 선택하는데 이 선행 테이블을 Driving Table이라고도 한다. 만약 조인 칼럼에 모두 인덱스가 존재하지 않으면 From절에 나열된 테이블의 역순으로 선행 테이블을 선택한다.

 

규칙 기반 옵티마이저의 조인 기법의 선택은 다음과 같다. 양쪽 조인 칼럼이 모두 없으면 Sort Merge Join 둘 중 하나라도 존재하면 일반적으로 NLJOIN을 사용한다.

 

 

 

2. 비용기반 옵티마이저

 

규칙 기반 옵티마이저는 조건절에서 " = " 연산자와 Between 연산자가 사용되면 규칙에 따라 " = " 칼럼 인덱스가 보다 적은 처리 범위로 작업을 할 것이라고 판단하여 적용한다.

하지만 실제로는 Between 칼럼을 사용한 인덱스가 보다 일 량이 더 적을 수가 있다. 

비용 기반 옵티마이저는 이러한 규칙 기반 옵티마이저의 단점을 극복하기 위해 출현하였다.

SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다. 여기서 비용이란 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량을 의미한다.

 

이러한 비용을 예측하기 위해서 RBO가 사용하지 않는 TABEL, INDEX, COLUMN 등의 다양한 객체 통계정보와 시스템 통계정보를 이용한다.

 

통계정보가 없는 경우 비용기반 옵티마이저는 정확한 비용 예측이 불가능해져서 비효율적인 실행 계획을 생성할 수 있다.

기본 흐름

 

 

구성 요소를 먼저 살펴보자

 

1) 질의변환기는 사용자가 작성한 SQL문을 처리하기 위해 보다 용이한 형태로 변환하는 모듈이다.

 

2) 대안 계획 생성기는 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈이다.

 

3) 대안 계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등 다양한 변경을 통해 생성한다.

대부분은 상용 Optimizer는 대안 계획의 수를 제약하는 방법을 사용한다.

 

4) 비용예측기는 대안 계획 생성기에 의해 생성된 대안 계획의 비용을 예측하는 모듈이다. 대안 계획의 정확한 비용을 예측하기 위해 옵티마이저는 정확한 통계정보를 필요로 하며 대안 계획을 구성하는 각 연산에 대한 비용 계산식이 정확해야 한다.

 

CBO는 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 수행하는 방법으로 실행 계획을 생성할 수도 있다. 단순히 인덱스 안탔다고 느린 게 아니라는 소리다. CBO는 DBMS의 버전, 설정 정보, 

통계정보 등 차이가 존재하여 같은 SQL문이 서로다른 실행계획을 선택할 수도 있다.