[SQLD] SQL 최적화 기본 원리

옵티마이저와 실행계획

옵티마이저의 역할

• 사용자가 질의한 SQL문에 대해 최적의 실행 방법(실행 계획)을 결정하는 역할을 수행.
• 옵티마이저가 선택한 실행 방법의 적절성 여부는 질의의 수행 속도에 가장 큰 영향을 미친다.

최적의 실행 방법 결정 : 어떤 방법으로 처리하는 것이 최소 일량으로 동일한 일을 처리할 수 있을지 결정하는 것

옵티마이저의 방식

1. 규칙기반 옵티마이저(RBO, Rule Based Optimizer)
2. 비용기반 옵티마이저(CBO, Cost Based Optimizer)

💡 관계형 데이터베이스는 비용기반만 제공한다.

규칙기반 옵티마이저

• 규칙(우선순위)를 가지고 실행계획을 생성
• 우선순위가 높은 규칙이 적은 일량으로 해당 작업을 수행한다고 판단.
• 실행계획을 생성할 때 참조하는 정보 : 인덱스 유무와 종류, 연산자의 종류, 참조하는 객체의 종류 등

비용기반 옵티마이저

• SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식.
• 비용을 예측하기 위해 테이블, 인덱스, 컬럼 등의 다양한 객체 통계정보와 시스템 통계정보 등을 이용
• 통계정보가 없는 경우, 정확한 비용 예측이 불가능해 비효율적인 실행계획을 생성할 수 있기 때문에 정확한 통계정보를 유지하는 것이 중요하다.

💡 비용 : SQL문을 처리하기 위해 예상되는 소요시간, 자원 사용량

비용기반 옵티마이저의 구성요소

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

실행계획

SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미하며 SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업

실행계획 구성 요소

1. 조인 순서 : 조인작업을 수행할 때 참조하는 테이블 순서
2. 조인 기법 : 두 개의 테이블을 조인할 때 사용할 수 있는 방법
3. 액세스 기법 : 하나의 테이블을 액세스할 때 사용할 수 있는 방법
4. 최적화 정보 : 옵티마이저가 실행계획의 각 단계마다 예상되는 비용 사항을 표시한 것
5. 연산 : 여러가지 조작을 통해 원하는 결과를 얻어내는 일련의 작업

---

인덱스 기본

인덱스

• 원하는 데이터를 쉽게 찾을 수 있도록 도와주는 것.
• 테이블을 기반으로 선택적으로 생성할 수 있는 구조
• 인덱스의 목적 : 검색 성능의 최적화
• 단점 : 테이블과 인덱스를 함께 변경해야 하는 DML 작업에서는 느려질 수 있다.

트리 기반 인덱스(대표 : B-트리 인덱스)의 구성

• 브랜치 블록 : 분기를 목적으로 하는 블록
• 루트 블록 : 브랜치 블록 중에서 가장 상위에 있는 블록
• 리프 블록 : 트리의 가장 아래 단계에 존재하는 블록.

B-트리 인덱스

• ‘=’로 검색하는 일치 검색과, 비교 연산자로 검색하는 범위 검색에 적합한 구조

B-트리 인덱스의 검색 과정

1. 브랜치 블록의 가장 왼쪽 값 <= 찾고자 하는 값 : 왼쪽 포인터로 이동
2. 찾고자 하는 값이 브랜치 블록의 값 사이에 존재 : 가운데 포인터로 이동
3. 브랜치 블록의 오른쪽에 있는 값 < 찾고자 하는 값 : 오른쪽 포인터로 이동

SQL Server의 클러스터형 인덱스의 특징

1. 인덱스의 리프 페이지가 곧 데이터 페이지
   테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다.
   클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 컬럼 값을 곧바로 얻을 수 있다.
2. 리프 페이즈의 모든 로우(=데이터)는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장된다.
   테이블 로우는 물리적으로 한 가지 순서대로 정렬되므로 클러스터형 인덱스는 테이블 당 한 개만 생성 가능

전체 테이블 스캔

• 테이블에 존재하는 모든 데이터를 읽어가면서 조건에 맞으면 결과로 추출하고 조건에 맞지 않으면 버리는 방식
• 인덱스의 존재 유무와 상관없이 항상 이용 가능한 스캔 방식

전체 테이블 스캔을 선택하는 이유

1. SQL문에 조건이 존재하지 않는 경우
2. SQL문의 주어진 조건에 사용 가능한 인덱스가 존재 X
3. 옵티마이저의 취사 선택
4. 병렬 처리 방식으로 처리하는 경우
5. 전체 테이블 스캔 방식의 힌트를 사용한 경우

인덱스 스캔

• 인덱스를 구성하는 컬럼의 값을 기반으로 데이터를 추출하는 액세스 기법
• 사용 가능한 적절한 인덱스가 존재할 때만 이용할 수 있다.

인덱스 스캔의 종류

1. 인덱스 유일 스캔

   • 유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식
   • 중복을 허용 X
   • 유일 인덱스 구성 컬럼에 모두 ‘=’로 값이 주어지며 결과는 최대 1건이 된다.

2. 인덱스 범위 스캔

   • 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식

3. 인덱스 역순 범위 스캔

   • 인덱스의 리프 블록의 양방향 링크를 이용하여 내림차순으로 데이터를 읽는 방식
   • 인덱스 범위 스캔의 일종

---

조인 수행 원리

조인 기법

• 두 개의 테이블을 조인할 때 사용할 수 있는 방법
  • NL Join, Hash Join, Sort Merge Join

NL Join

• 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인을 수행
• 선행 테이블의 조건을 만족하는 모든 행의 수만큼 반복 수행
• 랜덤 방식으로 데이터를 액세스 하기 때문에 처리 범위가 좁은 것이 유리
• 조인이 성공하면 바로 조인 결과를 사용자에게 보여줄 수 있다.

Sort Merge Join

• 조인 컬럼을 기준으로 데이터를 정렬하여 조인을 수행
• 스캔 방식으로 데이터를 읽는다.
• 넓은 범위의 데이터를 처리할 때 이용
• 동등 조인 뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능하다.
• 조인 컬럼의 인덱스가 존재하지 않을 경우에도 사용 가능

Hash Join

• 해시 기법을 이용하여 조인을 수행
• 조인을 수행할 테이블의 조인 컬럼을 기준으로 해시 함수를 수행하여 서로 동일한 해시 값을 갖는 것들 사이에서 실제 값이 같은지를 비교하며 조인을 수행
• 정렬 작업의 부담을 해결하기 위한 대안
• 조인 컬럼의 인덱스가 존재하지 않을 경우에소 사용 가능
• 조인 작업을 수행하기 위해 해시 테이블을 메모리에 생성해야 한다.
• 결과 행의 수가 적은 테이블을 선행 테이블로 선택해야 유리