[2과목] SQL 최적화 기본 원리

😸 옵티마이저 

- SQL의 실행 방법인 실행계획을 생성하는 데이터 베이스 엔진

- 사용자 SQL문에 대해 최적의 실행 방법을 결정하며 SQL 실행 속도에 가장 큰 영향을 미침

- 옵티마이저는 SQL을 입력 받아 규칙 기반 혹은 비용 기반으로 실행계획을 출력

 

• 규칙 기반 옵티마이저(RBO)

- 정의된 SQL 규칙의 우선순위로 실행계획을 생성하는 방식

- 사용 가능한 인덱스의 유무와 연산자 종류 등을 참조해서 실행계획을 생성

 

• 비용 기반 옵티마이저(CBO)

- 통계 정보를 활용하여 SQL 처리 시간 및 자원 사용량 등을 계산하고 가장 효율적인 실행계획을 생성하는 방식

- 데이터 건수를 예측하지 못하는 RBO의 단점을 개선하기 위해 사용

- 통계 정보가 없는 경우 불확실한 실행계획을 생성할 수 있으므로 최신화된 통계 정보를 유지하는 것이 중요

 

1) 비용 계산 원리

- 선택도 : 1/ NDV(중복 제거한 값의 개수)

- 카디널리티 : 총 로우수 * 선택도 = 총 로우수 / NDV

- 히스토그램 : 컬럼 값의 빈도수를 저장

 

 

👉 인덱스

- 데이터베이스에 저장된 데이터를 빠르게 조회하기 위해서 테이블에 연관된 정보를 독립적인 저장 공간에 저장한 객체

- 테이블의 특정 레코드 위치를 알려주는 용도로 사용하며 인덱스를 구성하는 컬럼의 정렬 순서에 따라 정렬

- 관계형 데이터베이스에서 가장 많이 사용되는 인덱스는 B-트리

- Oracle에서 물리적인 주소 정보러써, 내부적으로 ROWID를 사용

- 인덱스는 조회, 삽입, 삭제, 갱신 연산의 속도를 저하시킴

- 대량의 데이터를 삽입 시 모든 인덱스를 생성 후 데이터를 입력하는 것이 좋음

- SQL Server의 클러스터형 인덱스는 ORACLE의 IOT와 매우 유사

 

1) B-트리 인덱스

- 루트 블록부터 리프 블록까지 거리가 일정한 트리 구조를 가진 인덱스

- 루트 블록, 브랜치 블록, 리프 블록으로 구성되고, 리프 블록에는 테이블 데이터에 대한 ROWID가 저장

- 리프 블록은 양방향 링크를 가지고 있어, 오름차순과 내림차순 검색을 쉽게 할 수 있음

- 일치 및 범위 검색에 적절한 구조

- OLTP 환경에서 적합

 

2) 비트맵 인덱스

- 비트를 이용해서 컬럼 값을 저장하고 비트맵을 구성하는 인덱스

- 인덱스의 리프 노드는 ROWID 대신 킷값에 대한 비트맵으로 저장

- 컬럼의 Distinct Value 개수가 적을 때 사용하면 저장 효율이 높음

- Lock에 의한 DML 부하가 심해서 OLTP 환경에서 사용하기 어려움

- 읽기 위주의 대용량 DW, OLAP 환경에 적합