옵티마이저와 실행계획
옵티마이저의 역할
- 사용자가 질의한 SQL문에 대해 최적의 실행 방법(실행 계획)을 결정하는 역할을 수행.
- 옵티마이저가 선택한 실행 방법의 적절성 여부는 질의의 수행 속도에 가장 큰 영향을 미친다.
최적의 실행 방법 결정 : 어떤 방법으로 처리하는 것이 최소 일량으로 동일한 일을 처리할 수 있을지 결정하는 것
옵티마이저의 방식
- 규칙기반 옵티마이저(RBO, Rule Based Optimizer)
- 비용기반 옵티마이저(CBO, Cost Based Optimizer)
💡 관계형 데이터베이스는 비용기반만 제공한다.
규칙기반 옵티마이저
- 규칙(우선순위)를 가지고 실행계획을 생성
- 우선순위가 높은 규칙이 적은 일량으로 해당 작업을 수행한다고 판단.
- 실행계획을 생성할 때 참조하는 정보 : 인덱스 유무와 종류, 연산자의 종류, 참조하는 객체의 종류 등
비용기반 옵티마이저
- SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식.
- 비용을 예측하기 위해 테이블, 인덱스, 컬럼 등의 다양한 객체 통계정보와 시스템 통계정보 등을 이용
- 통계정보가 없는 경우, 정확한 비용 예측이 불가능해 비효율적인 실행계획을 생성할 수 있기 때문에 정확한 통계정보를 유지하는 것이 중요하다.
💡 비용 : SQL문을 처리하기 위해 예상되는 소요시간, 자원 사용량
비용기반 옵티마이저의 구성요소
- 질의 변환기 : 사용자가 작성한 SQL문을 처리에 용이한 형태로 변환하는 모듈
- 대안 계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈
- 비용 예측기 : 대안 계획 생성기에 의해서 생성된 대안 계획의 비용을 예측하는 모듈
실행계획
SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미하며 SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업
실행계획 구성 요소
- 조인 순서 : 조인작업을 수행할 때 참조하는 테이블 순서
- 조인 기법 : 두 개의 테이블을 조인할 때 사용할 수 있는 방법
- 액세스 기법 : 하나의 테이블을 액세스할 때 사용할 수 있는 방법
- 최적화 정보 : 옵티마이저가 실행계획의 각 단계마다 예상되는 비용 사항을 표시한 것
- 연산 : 여러가지 조작을 통해 원하는 결과를 얻어내는 일련의 작업
인덱스 기본
인덱스
- 원하는 데이터를 쉽게 찾을 수 있도록 도와주는 것.
- 테이블을 기반으로 선택적으로 생성할 수 있는 구조
- 인덱스의 목적 : 검색 성능의 최적화
- 단점 : 테이블과 인덱스를 함께 변경해야 하는 DML 작업에서는 느려질 수 있다.
트리 기반 인덱스(대표 : B-트리 인덱스)의 구성
- 브랜치 블록 : 분기를 목적으로 하는 블록
- 루트 블록 : 브랜치 블록 중에서 가장 상위에 있는 블록
- 리프 블록 : 트리의 가장 아래 단계에 존재하는 블록.
B-트리 인덱스
- ‘=’로 검색하는 일치 검색과, 비교 연산자로 검색하는 범위 검색에 적합한 구조
B-트리 인덱스의 검색 과정
- 브랜치 블록의 가장 왼쪽 값 <= 찾고자 하는 값 : 왼쪽 포인터로 이동
- 찾고자 하는 값이 브랜치 블록의 값 사이에 존재 : 가운데 포인터로 이동
- 브랜치 블록의 오른쪽에 있는 값 < 찾고자 하는 값 : 오른쪽 포인터로 이동
SQL Server의 클러스터형 인덱스의 특징
- 인덱스의 리프 페이지가 곧 데이터 페이지
테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다.
클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 컬럼 값을 곧바로 얻을 수 있다.
- 리프 페이즈의 모든 로우(=데이터)는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장된다.
테이블 로우는 물리적으로 한 가지 순서대로 정렬되므로 클러스터형 인덱스는 테이블 당 한 개만 생성 가능
전체 테이블 스캔
- 테이블에 존재하는 모든 데이터를 읽어가면서 조건에 맞으면 결과로 추출하고 조건에 맞지 않으면 버리는 방식
- 인덱스의 존재 유무와 상관없이 항상 이용 가능한 스캔 방식
전체 테이블 스캔을 선택하는 이유
- SQL문에 조건이 존재하지 않는 경우
- SQL문의 주어진 조건에 사용 가능한 인덱스가 존재 X
- 옵티마이저의 취사 선택
- 병렬 처리 방식으로 처리하는 경우
- 전체 테이블 스캔 방식의 힌트를 사용한 경우
인덱스 스캔
- 인덱스를 구성하는 컬럼의 값을 기반으로 데이터를 추출하는 액세스 기법
- 사용 가능한 적절한 인덱스가 존재할 때만 이용할 수 있다.
인덱스 스캔의 종류
-
인덱스 유일 스캔
- 유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식
- 중복을 허용 X
- 유일 인덱스 구성 컬럼에 모두 ‘=’로 값이 주어지며 결과는 최대 1건이 된다.
-
인덱스 범위 스캔
- 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식
-
인덱스 역순 범위 스캔
- 인덱스의 리프 블록의 양방향 링크를 이용하여 내림차순으로 데이터를 읽는 방식
- 인덱스 범위 스캔의 일종
조인 수행 원리
조인 기법
- 두 개의 테이블을 조인할 때 사용할 수 있는 방법
- NL Join, Hash Join, Sort Merge Join
NL Join
- 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인을 수행
- 선행 테이블의 조건을 만족하는 모든 행의 수만큼 반복 수행
- 랜덤 방식으로 데이터를 액세스 하기 때문에 처리 범위가 좁은 것이 유리
- 조인이 성공하면 바로 조인 결과를 사용자에게 보여줄 수 있다.
Sort Merge Join
- 조인 컬럼을 기준으로 데이터를 정렬하여 조인을 수행
- 스캔 방식으로 데이터를 읽는다.
- 넓은 범위의 데이터를 처리할 때 이용
- 동등 조인 뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능하다.
- 조인 컬럼의 인덱스가 존재하지 않을 경우에도 사용 가능
Hash Join
- 해시 기법을 이용하여 조인을 수행
- 조인을 수행할 테이블의 조인 컬럼을 기준으로 해시 함수를 수행하여 서로 동일한 해시 값을 갖는 것들 사이에서 실제 값이 같은지를 비교하며 조인을 수행
- 정렬 작업의 부담을 해결하기 위한 대안
- 조인 컬럼의 인덱스가 존재하지 않을 경우에소 사용 가능
- 조인 작업을 수행하기 위해 해시 테이블을 메모리에 생성해야 한다.
- 결과 행의 수가 적은 테이블을 선행 테이블로 선택해야 유리