SQL 최적화
개념
- DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정을 의미
- SQL 옵티마이저를 통해 DBMS 내부 엔진에서 프로시저를 만듦
- 즉, SQL 옵티마이저는 SQL문을 최적화하여 데이터베이스 성능을 결정하는 가장 핵심적인 엔진을 뜻함
- 최적화 과정을 'SQL 파싱' 혹은 파싱 과정을 포함해 'SQL 최적화'라고도 표현
과정
1. SQL 파싱
사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서(Parser)가 파싱을 진행
- 파싱 트리 생성 : SQL문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
- 문법(Syntax) 확인 : 사용할 수 없는 키워드를 사용하는 등의 문법적 오류가 없는지 확인
- 의미(Sementic) 확인 : 존재하지 않는 테이블을 사용하는 등의 의미상 오류가 없는지 확인
2. SQL 최적화
옵티마이저(Optimizer)를 통해 SQL 최적화 진행
- 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성
- 생성된 다양한 실행경로를 비교한 후 가장 효율적인 하나를 선택
3. 로우 소스 생성 (Row-Source Generator)
SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 도는 프로시저 형태로 포맷팅
SQL 옵티마이저 (SQL Optimizer)
개념
- 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진
- 요구사항을 만족하는 결과를 추출하는 다양한 실행 방법들 중 최적의 실행 방법을 결정하는 역할
- DBMS는 옵티마이저가 결정한 실행 방법대로 실행 엔진이 데이터를 처리하여 결과 데이터를 사용자에게 전달하기만 함
- 즉, 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행
- 옵티마이저가 최적의 실행 방법을 결정하는 방식에 따라 구분
- 현재의 대부분 DBMS는 비용기반 옵티마이저만 제공
- 하위 버전 호환성을 위해 규칙기반 옵티마이저가 남아있음
과정
- 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 검색
- 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용 산정
- 최저 비용을 나타내는 실행계획 선택
* 데이터 딕셔너리 (Data Dictionary) : 시스템 전체에서 나타나는 데이터 항목들에 대한 정보를 지정한 중앙 저장소
규칙기반 옵티마이저(RBO, Rule Based Optimizer)
설명
- 참조하는 정보를 이용하여 규칙(우선 순위)를 가지고 실행계획 생성하는 최적화 방식
- 실행계획을 생성하기 위한 참조하는 정보
- 이용 가능한 인덱스 유무와 종류(유일, 비유일, 단일, 복합 인덱스)
- SQL문에서 사용하는 연산자(=, <, <>, LIKE, BETWEEN 등)의 종류
- SQL문에서 참조하는 객체(힙 테이블, 클러스터 테이블 등)의 종류
- 우선 순위가 높은 규칙이 적은 일량으로 해당 작업을 수행하는 방법이라고 판단하는 옵티마이저
규칙기반 옵티마이저의 규칙
- 순위의 숫자가 낮을수록 높은 우선 순위를 의미
- 인덱스를 이용한 액세스 방식이 전체 테이블 액세스 방식보다 우선순위 높음
- SQL문에서 이용 가능한 인덱스 존재 할 경우 : 항상 인덱스를 사용하는 실행계획 생성
- 조인 순서 결정 시 조인 컬럼의 인덱스 존재 유무에 따라 선행 테이블(Driving Table)로 선택
- 한쪽 테이블에만 인덱스가 존재할 경우 : 인덱스가 없는 테이블을 선행 테이블로 선택해서 조인 수행
- 양쪽 테이블 모두 인덱스가 존재할 경우 : 액세스 기법에 따라 우선 순위가 높은 테이블을 선행 테이블로 선택
- 양쪽 테이블 모두 인덱스가 없을 경우 : FROM 절 뒤에 나열된 테이블을 선행 테이블로 선택
- 조인 테이블의 우선 순위가 동일할 경우 : FROM 절 뒤에 나열된 테이블의 역순으로 선행 테이블을 선택
조인 기법에 따른 액세스 기법
- 양쪽 조인 컬럼에 모두 인덱스가 없을 경우 : Sort Merge Join 사용
- 한쪽 조인 컬럼에 인덱스가 존재할 경우 : NL Join 사용
| 순위 | 액세스 기법 |
| 1 | Single row by rowid |
| 2 | Single row by cluster join |
| 3 | Single row by hash cluster key with unique or primary key |
| 4 | Single row by unique or primary key |
| 5 | Cluster join |
| 6 | Hash cluster key |
| 7 | Indexed cluster key |
| 8 | Composite index |
| 9 | Single column index |
| 10 | Bounded range search on indexed columns |
| 11 | Unbounded range search on indexed columns |
| 12 | Sort merge join |
| 13 | MAX or MIN of indexed column |
| 14 | ORDER BY on indexed column |
| 15 | FULL table scan |
- 규칙1 : Single row by rowid
- ROWID를 통해 테이블에 하나의 행을 액세스 하는 방식
- 하나의 행을 액세스하는 가장 빠른 방법
- 규칙4 : Single row by unique or primary key
- 유일 인덱스(Unique Index)를 통해 하나의 행을 액세스 하는 방식
- 인덱스를 먼저 액세스하고 인덱스에 존재하는 ROWID를 추출하여 테이블의 행을 액세스
- 규칙8 : Composite index
- 복합 인덱스에 동등(=) 연산자 조건으로 검색하는 방식
- 예시를 위한 조건) A+B컬럼의 복합 인덱스 생성
- 예시) 조건절에 WHERE A=10 AND B=1 형태로 검색
- 인덱스 구성 컬럼의 개수가 더 많고 해당 인덱스의 모든 구성 컬럼에 대해 '='로 값이 주어질수록 우선순위가 높음
- 예시를 위한 조건 : 각각의 A+B 인덱스와 A+B+C 인덱스가 존재
- 예시1) A/B/C 컬럼 모두에 '='로 값이 주어질 경우 : A+B+C 인덱스가 우선 순위가 높음
- 예시2) A/B 컬럼에만 '='로 값이 주어질 경우 : A+B+C 인덱스는 일부 컬럼에 대한 값만 주어졌기 때문에 A+B 인덱스의 우선 순위가 더 높음
- 복합 인덱스에 동등(=) 연산자 조건으로 검색하는 방식
- 규칙9 : Single column index
- 단일 컬럼 인덱스에 동등(=) 연산자 조건으로 검색하는 방식
- 예시를 위한 조건) A 컬럼에 단일 컬럼 인덱스 생성
- 예시) 조건절에 WHERE A=10 형태로 검색
- 단일 컬럼 인덱스에 동등(=) 연산자 조건으로 검색하는 방식
- 규칙 10 : Bounded rnage search on indexed columns
- 인덱스가 생성되어 있는 컬럼에 양쪽 범위를 한정하는 형태로 검색하는 방식
- 양쪽 범위를 한정하는 연산자 : BETWEEN, LIKE 등
- 예시를 위한 조건) A 컬럼에 인덱스 생성
- 예시1) 조건절에 WHERE A BETWEEN '10' AND '20'
- 예시2) 조건절에 WHERE A LIKE '1%'
- 규칙 11 : Unbounded range search on indexed columns
- 인덱스가 생성되어 있는 컬럼 한쪽 범위에만 범위를 한정하는 형태로 검색하는 방식
- 한쪽 범위에만 범위를 한정하는 연산자 : >, >=, <, <= 등
- 예시를 위한 조건) A 컬럼에 인덱스 생성
- 예시) 조건절에 WHERE A > '10'
- 규칙 15 : Full table scan
- 전체 테이블을 액세스하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출하는 방식
비용기반 옵티마이저(CBO, Cost Based Optimizer)
설명
- 규칙기반 옵티마이저의 단점을 극복하기 위해 출현
- 규칙기반 옵티마이저는 규칙에 따라 실행 계획 생성하지만 실제로는 낮은 우선 순위의 컬럼이 일량이 더 적을 수 있으며, 단순히 몇 개의 규칙만으로 현실의 모든 사항을 정확히 예측할 수 없음
- 예시) 조건절에 '=' 연산자와 'BETWEEN' 연산자 사용시 '=' 컬럼의 인덱스를 사용하여 실행계획을 생성하지만, 실제로는 'BETWEEN' 컬럼의 인덱스를 사용하는 것이 보다 일량이 적을 수 있음
- SQL문을 처리하는데 필요한 비용(처리 소요시간, 자원 사용량)이 가장 적은 실행계획을 선택하는 옵티마이저
- 비용을 예측하기 위해 다양한 객체 통계정보와 시스템 통계 정보 등을 이용
- 다양한 객체 통계정보 : 테이블, 인덱스, 컬럼 등
- 정확한 통계정보를 유지하는 것이 비용기반 최적에 중요한 요소로 작용
- 통계정보가 없는 경우 정확한 비용 예측이 불가능해져서 비효율적인 실행계획을 생성할 수 있음
비용기반 옵티마이저의 구성요소
- 질의 변환기
- 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈
- 대안 계획 생성기
- 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈
- 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해 생성
- 대부분의 상용 옵티마이저들은 대안 계획의 수를 제약하는 다양한 방법을 사용하여 제공
(가능한 모든 대안 계획을 생성해야 최적화를 수행할 수 있지만 그만큼 최적화 수행 시간이 오래 걸리기 때문)
- 비용 예측기
- 대안 계획 생성기에 의해 생성된 대안 계획의 비용을 예측하는 모듈
- 보다 나은 예측을 위해 정확한 통계정보를 필요로 함
(연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야 비용 예측도 정확해지기 때문) - 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 동일 SQL문도 서로 다른 실행계획이 생성될 수 있음
- 옵티마이저의 다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다는 단점 존재
참고
- 저자 조시형님 도서 - 친절한 SQL 튜닝
- 한국데이터산업진흥원 DATA ON-AIR - SQL 옵티마이저와 실행계획
- 위키백과 - 데이터 사전