[SQLD 암기] 최적화 기본원리

* CBO

 테이블 및 인덱스 등의 통계 정보를 활용하여 SQL 문을 실행하는데 소요될 처리시간 및 CPU, IO 자원량 등을 계산하여

 가장 효율적일 것으로 예상되는 실행 계획을 선택하는 옵티마이저

테이블, 인덱스, 컬럼 등 객체 통계정보로 실행 계획을 수립하고 통계정보가 변경되면 SQL의 실행계획이 달라질 수 있음

* 실행계획 정보 구성요소

 - 조인 기법, 조인 순서

 - 액세스 기법

 - 연산

 - 최적화 정보

 - 질의 처리 예상 비용

 + 실제 처리 건수는 트레이스 정보로 알 수 있고, 예상 처리 건수만 알 수 있음

 

* 실행 계획

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

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

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

 

* SQL 처리 흐름도

 - SQL 실행계획을 시각화하여 표현

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

 - SQL 내부적인 처리 절차를 시각적으로 표현해 줌

 - SQL 처리 흐름도는 성능적인 측면도 표현할 수 있음

 

* 규칙기반 옵티마이저

 제일 낮은 우선순위는 전체 테이블 스캔, 제일 높은 우선순위는 ROWID를 활용한 테이블 액세스 방법

 규칙기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스를 사용하려 함

 - 규칙기반 옵티마이저에서 제일 높은 우선순위는 행에 대한 고유 주소를 사용하는 방법이다

 

* 옵티마이저와 실행계획

 - 인덱스 범위 스캔은 결과 건수만큼 반환, 결과가 없으면 반환하지 않음

 - ORACLE의 규칙기반 옵티마이저에서 가장 우선순위가 높은 규칙은 single row by rowed 액세스 기법

 - ORACLE 실행계획에 나타나는 기본적인 JOIN 기법에는, NL, HASH, SORT MERGE JOIN이 있음

 - NL JOIN은 OLTP 목록 처리 업무에 많이 사용하고 HASH, SORT MERGE는 DW 등 데이터 집계 업무에 많이 사용

 

* 관계형 데이터베이스 인덱스

 - 기본 인덱스는 primary key임 = unique key + NOT NULL

 - 테이블 전체 데이터를 읽는 경우 인덱스는 불필요

 - 인덱스는 조회만을 위한 오브젝트, 삽입, 삭제, 갱신 시 부하를 가중

 - B 트리는 관계형 데이터 베이스의 주요 인덱스 구조, 관계형 데이터베이스에서 가장 많이 사용되는 인덱스

 - 인덱스가 존재하는 상황에서 데이터를 입력하면, 매번 인덱스 정렬 발생

    모든 인덱스 제거 -> 데이터 삽입 -> 인덱스 재생성

 

* 보조 인덱스

 - UNIQUE 인덱스가 아니면 중복 데이터 입력 가능

 - 자주 변경되는 속성을 인덱스로 설정하면 UPDATE, DELETE 성능에 좋지 않은 영향을 미침

   -> 인덱스 후보로 적절하지 않음

 

* 인덱스 종류

 - B TREE 인덱스 : 브랜치 블록과 리프 블록으로 구성,

                        브랜치 블록은 분기가 목적, 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬

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

                        테이블 데이터 중 10% 이하 데이터 검색에 유리

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

 - CLUSTERED 인덱스 : 인덱스의 리프 페이지가 곧 데이터 페이지

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

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

 - BITMAP 인덱스 : 시스템에서 사용될 질의를

                          시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 설계

                          하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조

 

* 인덱스

 - 인덱스는 인덱스 구성 컬럼으로 항상 내림차순으로 정렬

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

 - INSERT, UPDATE, DELETE 등의 DML 처리 성능을 저하시킬 수 있음

 - 인덱스를 스캔하여 테이블로 데이터를 찾아가는 방식은 랜덤 액세스

   랜덤 액세스는 부하가 커서 많은 양 데이터를 읽을 때 별로임

 - 인덱스를 구성하는 컬럼 이외의 데이터가 UPDATE 될 때는 인덱스로 인한 부하가 발생되지 않음

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

 

* NL JOIN

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

 - driving table의 조인 데이터 양이 큰 영향을 주는 조인 방식

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

 - 주로 랜덤 액세스 방식으로 데이터를 읽음

 - 선택도가 낮은(결과 행 수가 적은) 테이블이 선행 테이블로 선택되는 것이 일반적으로 유리

 

* SORT MERGE JOIN

 - 주로 스캔 방식으로 데이터를 읽음

 - 조인 컬럼에 적당한 인덱스가 없어서 NL 조인이 비효율적일 때 사용

 - driving table의 개념이 중요하지 않은 조인 방식

 - 조인 조건의 인덱스의 유무에 영향을 받지 않음

 - NON-EQUI 조건에서도 사용 가능

 

* HASH JOIN

 - 소트머지 조인하기에 두 테이블이 너무 커서 소트 부하가 심할 때 유용

 - EQUI 조인 조건에서만 동작 ( 동등 조건 )

 - 가용 메모리에 담길 정도로 충분히 작고 해시 키 속성에 중복 값이 적을 때 효과적

 - 조인 컬럼에 적당한 인덱스가 없어서 자연조인이 비효율 적일 때 사용

 - 자연조인이 드라이빙 집합 쪽으로 조인 액세스 량이 많아 랜덤 액세스 부하가 심할 때 사용

 - 조인 컬럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있는 조인 기법

 - 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 성능에 유리

 - 일반적으로 HASH 가 SORT MERGE보다 우수한 성능을 보임

   테이블이 JOIN KEY 컬럼으로 정렬되어 있을 때는 SORT MERGE가 더 우수한 성능을 낼 수 있음