📔설명

SQLD 2과목 3챕터의 SQL 최적화 기본 원리 요점 정리

---

👉🏻옵티마이저와 실행계획

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

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

• 실행계획에 단계별 예상 비용 및 건수 등이 표시
• 인덱스가 존재하더라도 전체 테이블 스캔이 비용이 덜 들면 그것이 유리하다고 판단
• 테이블, 인덱스, 컬럼 등 객체의 통계정보를 사용하여 실행계획을 수립하므로, 통계정보가 변경되면 SQL의 실행계획이 달라질 수 있음

규칙기반 옵티마이저

• 제일 높은 우선순위 : 행에 대한 고유 주소 사용
• 제일 낮은 우선순위 : 테이블 전체 스캔
• 적절한 인덱스가 존재하면 항상 인덱스를 사용하려고 함
  (전체 테이블 스캔보다는..)

실행계획

실행계획 : SQL 처리를 위한 실행 절차와 방법 표현

실행계획을 통해 알 수 있는 정보

• 액세스 기법
• 질의 처리 예상 비용(Cost)
• 조인 순서(위->아래, 안->밖)
• 연산
• 조인 방법

SQL 처리 흐름도(Access Flow Diagram)

• SQL 실행 시간은 알 수 없음
• 인덱스 스캔, 테이블 전체 스캔과 같은 액세스 기법 표현
• SQL의 내부적인 처리 절차를 시각적으로 표현
• 실행계획과 관련
• 성능적인 측면의 표현 가능

인덱스 범위 스캔

• 결과가 없으면 한 건도 반환 안 할 수 있음

---

📑인덱스(Index)

• 기본 인덱스에 중복된 키 값 X
• 기본 인덱스에 null 값 나타날 수 없음
• 보조 인덱스에는 중복 가능
• 자주 변경되는 속성은 인덱스 정의시 좋지 않음
  (update, delete) 성능에 좋지 않음
• 테이블 전체 읽는 경우 FTS(Full-Text Search) 사용
  (인덱스 거의 불필요)
• 인덱스는 조회 성능 향상/삽입,삭제,갱신 성능 저하
• B-tree는 관계형 DB의 주요 인덱스 구조
• 대량의 데이터 삽입 시, 모든 인덱스를 제거하고 데이터 입력
  (그렇지 않으면 데이터 입력시마다 매번 인덱스 정렬)
• 인덱스는 내림차순 정렬시 내림차순으로 정렬 가능
• 매우 많은 양 읽을 때는 테이블 스캔이 유리
• SQL Server의 클러스터형 인덱스 는 Oracle의 IOT(Index-Organized Table)와 매우 유사
• 인덱스 구성 컬럼 이외의 데이터가 update시, update작업에는 부하 없음 ( INSERT, DELETE와는 다름)
• 인덱스 활용 데이터 조회 시 인덱스를 구성하는 컬럼들의 순서는 SQL 실행 성능과 매우 관계

B-TREE 인덱스

B-TREE 인덱스 : 브랜치 블록과 리프 블록으로 구성, 브랜치 블록은 분기를 목적으로 하고 리프 블록은 인덱스를 구성하는 컬럼의 값으로 정렬. 일반적으로 OLTP(온라인 트랜잭션 처리)시스템 환경에서 가장 많이 사용

• 일반적으로 테이블 내의 데이터 중 10% 이하만 검색시 유리
• 일치 및 범위 검색에 적절한 구조

CLUSTERED 인덱스

CLUSTERED 인덱스 : 인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장

BITMAP 인덱스

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

INDEX SCAN

• UNIQUE SCAN : 유일한 값 하나 찾기(ex.고객아이디)
• RANGE SCAN : 어떠한 조건에서 범위 찾기
• SKIP SCAN : 결합 인덱스의 선행 컬럼에 대한 조건이 없고, 후행 컬럼에 대한 조건이 있는 경우
• FAST FULL SCAN : select절과 조건절에 사용된 모든 컬럼이 인덱스 컬럼으로 인덱스 블록만 스캔하여 원하는 데이터 검색
• FULL SCAN : 전체 데이터

---

🔗Join(조인) 수행

오라클의 실행계획에 나타나는 기본적인 Join 기법으로는 NL Join, Hash Join, Sort Merge Join 등

• SQL문의 EXISTS : SEMI JOIN으로 표현

Nested Loop Join (NL Join)

• OLTP의 목록 처리 업무에 사용
• 조인 칼럼에 적당한 인덱스가 있어 자연조인이 효율적일 때 유용
• Driving Table의 조인 데이터 양이 큰 영향을 주는 조인 방식
  (Driving Table: Join시 먼저 액세스돼서 Access Path를 주도하는 테이블)
• 유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경우 유용
• 선택도가 낮은(결과 행의 수가 적은)테이블이 선행 테이블로 선택되는 것이 유리
• 랜덤 액세스 방식으로 데이터를 읽음
• 실행 속도 : 선행 테이블 사이즈 * 후행 테이블 접근 횟수

Single Loop Join

• 조인 칼럼에 적당한 인덱스가 존재할 경우 사용
• 선행 테이블의 각 레코드들에 대해 후행 테이블의 인덱스 접근 구조를 사용하여 직접 검색 후 조인

Sort Merge Join (SMJ)

Sort Merge Join : 조인 컬럼 기준으로 데이터를 정렬하여 조인 수행

• DW 등에서 데이터 집계하는 업무에 많이 사용
• 조인 컬럼에 적당한 인덱스가 없어서 NL조인이 비효율적일 때 사용
• Driving Table의 개념이 중요하지 않은 조인 방식
• 조인 조건의 인덱스 유무에 영향 받지 X
• EQUI(=), NONEQUI(between, >, < 등)에서 동작
• 조인 연결고리의 비교 연산자가 범위 연산인 경우 Nested Loop보다 유리
• Join대상이 Join Key 컬럼으로 정렬되어있을 때는 Sort Merge Join이 Hash Join보다 우수한 성능을 보여줄 수 있음
• 랜덤 액세스로 NL Join에서 부담되는 넓은 범위의 데이터를 처리할 때 이용. 그러나 정렬 데이터가 많아 메모리에서 모든 정렬 작업 수행이 어려운 경우 임시 영역(디스크)를 사용하므로 성능이 떨어질 수 있음

Hash Join

• DW 등에서 데이터 집계하는 업무에 많이 사용
• Sort Merge Join하기에 두 테이블이 너무 커서 Sort 부하가 심할 때 유용
• EQUI(=) 조인 조건에서만 동작
• 조인 컬럼에 적당한 인덱스가 없어서 NL조인이 비효율적일 때 사용
• NL 조인시 Driving 집합쪽으로 조인 액세스량이 많아 Random 액세스 부하가 심할 때 사용
• 결과 행의 수가 작은 테이블을 선행 테이블로 사용하는 것이 유리
• 조인 칼럼의 인덱스를 사용하지 않으므로, 조인 칼럼 인덱스가 없어도 사용할 수 있는 조인
• 해쉬 함수를 이용하여 조인을 수행
• 메모리의 지나친 사용으로 오버헤드 발생 가능성

해시 조인 순서

1. 선행 테이블에서 주어진 조건을 만족하는 레코드를 필터링
2. 선행 테이블의 조인 키를 기준으로 해시 함수를 적용하여 해시 테이블 생성
3. 1번, 2번 작업을 선행 테이블에서 조건을 만족하는 모든 행을 수행
4. 후행 테이블에서 주어진 조건을 만족하는 레코드를 필터링
5. 후행 테이블의 조인 키를 기준으로 해시 함수를 적용하여 선행 테이블에서 해시 함수 반환값과 같은 값을 반환하는 해당 버킷 찾기