SQL 최적화 기본 원리

CBO(Cost Based Optimizer)

- 테이블 및 인덱스 등의 통계 정보를 활용하여 SQL문을 실행하는데 소요될 처리 시간 및 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저를 비용기반 옵티마이저라고 한다.

- 비용기반 옵티마이저는 테이블, 인덱스, 칼럼 등 객체의 통계정보를 사용하여 실행계획을 수립하므로 통계정보가 변경되면 SQL의 실행계획이 달라질 수 있다.

실행계획

- SQL 처리를 위한 실행 절차와 방법을 표현한 것이다.

- 조인 방법, 조인 순서, 액세스 기법 등이 표현된다.

- 실행방법이 달라진다고 해서 결과가 달라지지는 않는다.

- CBO의 실행계획에는 단계별 예상 비용 및 건수 등이 표시된다.

- 인덱스 범위 스캔은 결과 건수만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않을 수 있다.

- 규칙기반 옵티마이저에서 제일 낮은 우선순위는 전체 테이블 스캔이고, 제일 높은 우선순위는 ROWID를 활용하여 테이블을 액세스하는 방법이다.

- Oracle의 실행 계획에 나타나는 기본적인 JOIN 기법으로는 NL JOIN, HASH JOIN, Sort Merge JOIN 등이 있다.

SQL 처리 흐름도(Access Flow Diagram)

- SQL 실행계획을 시각화해서 표현한 것이다.

- 처리 흐름도만 보고 실행 시간을 알 수 없다.

- 인덱스 스캔, 테이블 전체 스캔등과 같은 액세스 기법이 표현된다.

- SQL의 내부적인 처리 절차를 시각적으로 표현해준다.

옵티마이저 실행계획을 통해서 알 수 있는 정보

- 엑세스 기법

- 질의 처리 예상 비용

- 조인 순서

- 실제 처리 건수 → 트레이스 정보를 통해서 확인해야한다.

인덱스

- 인덱스의 목적은 조회 성능을 최적화하는 것이다.

- 기본 인덱스는 UNIQUE & NOT NULL의 제약조건을 가진다.

- 보조 인덱스는 UNIQUE 인덱스가 아니라면 중복 데이터의 입력 가능하다.

- 자주 변경되는 속성을 인덱스로 선정할 경우 UPDATE, DELETE 성능에 좋지 않는 영향을 미치므로 인덱스 후보로 적절하지 않다.

- 테이블의 전체 데이터를 읽는 경우는 인덱스가 거의 불필요하다.

- B 트리는 관계형 데이터베이스의 주요 인덱스 구조이다. (일치 및 범위 검색에 적잘한 구조)

- INSERT, DELETE 작업과는 다르게 UPDATE 작업에는 부하가 없을 수도 있다.

- SQL Server의 클러스터형 인덱스는 Oracle의 IOT와 매우 유사하다.

B-TREE 인덱스

- 브랜치 블록과 리프 블록으로 구성되며, 브랜치 블록은 분기를 목적으로 하고 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬된다.

- 일반적으로 OLTP 시스템 환경에서 가장 많이 사용된다.

CLUSTERED 인덱스

: 인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프 페이지의 모든 데이터는 인덱스 키 칼럼 순으로 물리적으로 정려되어 저장된다.

BITMAP 인덱스

: 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해서 설계되었으며, 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조이다.

Nested Loop Join

- 조인 칼럼에 적당한 인덱스가 있어서 자연조인이 효율적일 때 유용하다.

- Driving Table의 조인 데이터 양이 큰 영향을 주는 조인 방식이다.

- 유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경우 유용하다.

- 중첩된 반복문과 유사한 방식으로 조인을 수행한다.

Sort Merge Join

- 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행한다.

- NL Join은 주로 랜덤 엑세스 방식으로 데이터를 읽는 반면 Sort Merge Join은 주로 스캔 방식으로 읽는다.

- Sort Merge Join은 랜덤 엑세스로 NL Join에서 부담이 되던 넓은 범위의 데이터를 처리할 때 이용되던 조인 기법이다.

- 그러나 Sort Merge Join은 정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우에는 임시 영역(디스크)을 사용하기 때문에 성능이 떨어질 수 있다.

Hash Join

- 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있는 조인 기법이다.

- Hash Join은 해쉬 함수를 이용하여 조인을 수행

- 때문에 ‘=‘로 수행하는 조인 즉, 동등 조인에서만 사용할 수 있다.

Hash JOIN이 효과적일 수있는 조건

- 조인 칼럼에 적당한 인덱스가 없어서 자연조건이 비효율적일 경우

- 자연조인시 드라이빙 집합 쪽으로 조인 엑세스량이 많아 Random 엑세스 부하가 심할 경우

- 소트 머지 조인을 하기에는 두 테이블이 너무 커서 소트 부하가 심할 경우