SQLD 최적화 기본 원리 정리

옵티마이저 : 사용자가 질의한 SQL문에 대해 최적의 실행방법을 결정하는 역할을 한다.

                 최적의 실행방법 = 실행계획

 

                 옵티마이저가 최적의 실행 방법을 결정하는 방식에 따라

                 규칙기반 옵티마이저(RBO)와 비용기반 옵티마이저(CBO)로 나뉜다.

 

                 대부분의 관계형 데이터베이스는 비용기반 옵티마지어만  제공

 

 

규칙기반 옵티마이저 : 우선 순위를 가지고 실행계획 생성

                              실행계획 생성할 때 참조하는 정보에는 sql을 실행하기 위해서 이용가능한 인덱스 유무와 종류,                                sql문에서 사용하는 비교연산자 종류, 참조하는 객체의 종류 등이 있다.

                              우선순위가 높은 규칙이 적은 일량으로 해당작업을 수행하는 방법이며 15가지 규칙이 있다.

조건절에서 '=' 연산자와 'Between' 연산자가 사용되면 규칙에 따라 '=' 컬럼의 인덱스를 사용하는 것보다 적은 일량, 즉 적은 처리 범위로 작업할 것으로 판단.

그러나 단순히 몇개의 규칙만으로 모든 사항 정확히 예측 불가능하다.

 

 

 

 

비용기반 옵티마이저 : SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다.

                              테이블 및 인덱스 등의 통계정보를 활용하여 sql문을 실행하는데 소요될 처리시간 및

                              cpu, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는

                              옵티마이저

 

 

 

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

- 엑세서 기법

- 질의 처리예상 비용

- 조인 순서, 조인 기법

- 최적화 정보

- 연산 정보

 

1  NESTED LOOPS 

2        HASH JOIN 

3           TABLE ACCESS (FULL) TAB1

4           TABLE ACCESS (FULL) TAB2

5        TABLE ACCESS (BY ROWID) TAB3

6           INDEX (UNIQUE SCAN) PK_TAB3   

 

실행 계획 순서 읽을 때는, 위 ----> 아래, 안----->밖의 순서로 읽으면 된다.

3 - 4 - 2 - 6 - 5 -1

 

 

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

실행계획은 조인 방법, 조인순서, 액세스 기법, 최적화 정보, 연산 등을 표현한다.

동일한 sql문에 대해 실행계획이 달라도 실행 결과는 같다.

비용기반 옵티마이져의 실행계획에는 단계별 예상 비용 및 건수 등이 표시된다.

 

 

SQL 처리 흐름도

 

내부적 처리절차를 시각적으로 표현하며, 어떤 테이블을 먼저 읽었는지, 인덱스 스캔 수행했는지, 테이블 전체 스캔했는지 등을 파악한다.

 

성능적 관점을 위해 일량을 함께 표시하며 처리건수도 표시한다.

처리 흐름도를 통해서 인덱스 스캔, 테이블 전체 스캔 등과 같은 액세스 기법이 표현되며, 내부적 처리 절차를 시각적으로 표현해주지만, sql 실행시간은 알 수가 없다.

 

자격 검정 실전 문제 133번 옵티마이저와 실행계획에 대한 설명

SQL처리흐름도는 성능적인 측면의 표현을 고려한다. -> 일량 함께 표시

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

가장 낮은 우선 순위는 테이블 스캔이다.

인덱스 범위 스캔은 결과 건수 만큼만 반환하며, 조회 건수가 없으면 반환하지 않는다.

처리 흐름도는 인덱스 스캔 및 전체 테이블 스캔 드으이 엑세스 기법을 표현할 수 있다.

 

 

---

인덱스

: 테이블을 기반으로 선택적으로 생성할 수 있는 구조로써, 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념이다.

생성을 안해도 되고, 여러개 생성해도 되는데, 목적은 검색 성능의 최적화이다.

그러나 DML (insert, update, delete) 문은 테이블과 인덱스를 함께 변경해야하기에 느려질 수 있는 단점이 있다.

 

관계형 데이터베이스의 인덱스 설명

기본 인덱스에는 unique & not null 제약 조건이 붙으며, unique가 아니면 중복데이터가 가능하다.

자주 변경되는 속성은 update, delete 성능에 좋지 않은 영향을 미치므로 인덱스 후보에는 적절하지 않다.

테이블 전체 데이터를 읽는 경우 인덱스를 사용하지 않는 FTS를 사용한다.

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

대량의 데이터를 삽입할 때는 모든 인덱스를 생성하고 데이터를 입력하는 것이 좋다.

 

 

트리기반 인덱스 (B-Tree)

Branch 블록과 Leaf 블록으로 구성되있으며, 브랜치 블록을 루트 블록이라고 한다.

브랜치 블록은 분기를 목적으로 하고, 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬되며, OLTP 시스템 환경에거 가장 많이 사용된다.

 

인덱스를 생성할 때 동일 컬럼으로 구성된 인덱스를 중복해서 생성할 수 없다. 그러나 인덱스 구성 컬럼은 동일하지만 컬럼 순서가 다르면 서로 다른 인덱스로 생성. JOB + SAL 과 SAL + JOB은 다르다.

 

데이터가 전체 데이터의 10%의 적은 데이터에서 사용하는 것이 유용하다.

 

 

CLUSTERED 인덱스

인덱스의 리프 페이지가 곧 데이터 페이지로서, 테이블 탐색에 필요한 레코드 식별자가 리프페이지에 없다.

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

Row id 는 리프페이지에 없다. 모든 row는 인덱스 키 컬럼순으로 정렬

 

Oracle의 IOT와 매우 유사하다.

 

Bit - map 인덱스

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

---

인덱스는 테이블 기반 선택적 생성구조로서, 오름차순, 내림차순 정렬이 가능하다.

비용기반 옵티마지어는 소요되는 비용을 계산하지만 인덱스 스캔이 항상 유리한 것은 아니다.

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

인덱스 범위 스캔은 겨로가가 없으면 한 건도 반환하지 않을 수 있다.

인덱스를 활용해서 데이터를 조회할 때 인덱스를 구성하는 컬럼들의 순서는 SQL 실행 성능과 관계가 없다.

 

 

인덱스 스캔

1.  유일 스캔

-> 단 하나의 데이터를 추출하며, 중복 허락되지 않는다. '=' 주어진 경우에만 가능하다.

2. 범위 스캔

-> 한건 이상의 데이터를 추출한다.

---

조인

: 두 개 이상의 테이블을 하나의 집합으로 만드는 연산이다.

  from 절에 a,b,c 세 개의 테이블이 존재하더라도 세 개의 테이블이 동시에 조인이 수행되는 것은 아니다.

  세 개의 테이블 중에서 먼저 두 개의 테이블에 대해 조인이 수행된다.

  a, b, c를 조인한다고 할때 , a,b를 먼저 조인하면 그 결과와 c 테이블을 조인한다.

 

 

NL 조인

: 중첩 반복문과 유사한 방식으로, 랜덤 ACCESS를 한다. 처리 범위가 좁은 것에 유리한 조인 방법이다.

  반복문 외부 테이블을 outer 테이블이라 하며, 내부 테이블을 inner table이라고 한다.

 

조인 순서로는 아래와 같다.

for 선행 테이블

     for outer 테이블

          for inner talbe

 

선행 테이블이 만족 되면 후행테이블로 이동을 하며, 모든 행 수 만큼 반복적으로 실행하게 된다.

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

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

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

 

 

Sort Merge join

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

  주로 스캔 방식으로 데이터를 읽으며, NL 조인에서 부담이 되던 넓은 범위의 데이터를 처리할 때 이용되던 조인 기법이지만, 정렬할 데이터가  메모리에서 모든 정렬작업 수행하기 어려운 경우에는 디스크를 사용하기에 성능이 떨어질 수 있다.

 

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

Driving table 의 개념이 중요하지 않은 조인 방식이다.

조인 컬럼 기준으로 데이터 정렬해서 조인을 수행하며, 넓은범위에서 스캔하고, 비동등, 동등 조인 둘다 작업이 가능하다.

 

Hash Join

: 조인 컬럼의 인덱스를 사용하지 않기에, 조인 컬럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있따.

동등 조인 에서만 사용이 가능하다.

 

결과 행의 수가 큰 테이블 보다는, 행의 수가 작은 선행 테이블로 사용하는 것이 성능에 유리하다.

항상 Sort merge 조인 보다 우수하지는 않으며, join  key가 정렬된 경우에는 Sort merge join이 우수하다.

 

Sort merge 조인 하기에 두 테이블이 너무 커서 Sort 부하가 심할 때,

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

자연조인 시 드라이빙 집합 쪽으로 조인 엑세스 양이 많아 랜덤 엑세스 부하가 심할때 사용하는 것이 좋다.