메뉴 건너뛰기

bysql.net

5_장_요약

2011.11.16 02:54

balto 조회 수:6977

SQL 최적화 기본 원리


제1절 옵티마이저와 실행계획

  • 옵티마이저
    • 질의한 SQL문에 대해 최적의 실행 방법 결정
    • 규칙기반 옵티마이저 (RBO, Rule Based Optimizer)
      • 규칙(우선 순위)을 가지고 실행계획 생성
      • 실행계획을 비교적 쉽게 예측 가능
    • 비용기반 옵티마이저(CBO, Cost Based Optimizer)
      • 비용이 가장 적은 실행계획을 선택 (정확한 통계정보가 중요 요소)
      • 질의 변환기 : 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈
      • 대안 계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈
      • 대안 계획 : 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해서 생성
      • 비용 예측기 : 대안 계획 생성기에 의해서 생성된 대안 계획의 비용을 예측하는 모듈

  • 실행계획
    • SQL에서 요구한 사항을 처리하기 위한 절차와 방법 의미
    • 조인 순서(Join Order)
    • 조인 기법(Join Method) : NL Join, Hash Join, Sort Merge Join 등
    • 액세스 기법(Access Method) : 인덱스 스캔(Index Scan), 전체 테이블 스캔(Full Table Scan) 등
    • 최적화 정보(Optimization Information) : 각 단계별 예상 비용
      • Cost : 상대적인 비용 정보
      • Card : Cardinality
      • Bytes: 결과 집합의 메모리 양

      ※ 비용 정보 : 실제실행 비용이 아닌 통계 정보를 바탕으로 옵티마이저가 계산한 예상치

    • 연산(Operation) : 조인 기법, 액세스 기법, 필터, 정렬, 집계, 뷰 등 

  • SQL 처리 흐름도(Access Flow Diagram)
    • SQL의 내부적인 처리 절차를 시각적으로 표현한 도표

 

제2절 인덱스 기본

1. 인덱스 특징과 종류

가. 트리 기반 인덱스

DBMS에서 가장 일반적인 인덱스는 B-트리 인덱스이다.

SQL_244.jpg

 

B-트리 인덱스 = 브랜치 블록(Branch Block)과 리프 블록(Leaf Block)

    - 브랜치 블록 중에서 가장 상위에서 있는 블록을 루트 블록(Root Block)

    - 브랜치 블록은 다음 단계의 블록을 가리키는 포인터를 가지고 있다.

    - 리프 블록은 트리의 가장 아래 단계에 존재한다.

    - 리프 블록은 인덱스를 구성하는 칼럼의 데이터와

       해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자(RID, Record Identifier/Rowid)로 구성

    - 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬된다.

       만약 인덱스 데이터의 값이 동일하면 레코드 식별자의 순서로 저장된다.

    - 리프 블록은 양방향 링크(Double Link)를 가지고 있다.

       이것을 통해서 오름 차순(Ascending Order)과 내림 차순(Descending Order) 검색을 쉽게 할 수 있다.

    - B-트리 인덱스는 ‘=’로 검색하는 일치(Exact Match) 검색과 ‘BETWEEN’, ‘>’ 등과 같은 연산자로 검색하는 범위(Range) 검색 모두에 적합한 구조

 

나. SQL Server의 클러스터형 인덱스

SQL Server의 인덱스 종류 : 저장 구조에 따라 클러스터형(clustered) 인덱스와 비클러스터형(nonclustered) 인덱스로 나뉜다.

 

 2. 전체 테이블 스캔과 인덱스 스캔

가. 전체 테이블 스캔

옵티마이저가 연산으로서 전체 테이블 스캔 방식을 선택하는 이유는 일반적으로 다음과 같다.

1) SQL문에 조건이 존재하지 않는 경우

     SQL문에 조건이 존재하지 않는다는 것은 테이블에 존재하는 모든 데이터가 답이 된다는 것이다. 

2) SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하는 않는 경우

    또한 주어진 조건에 사용 가능한 인덱스는 존재하나 함수를 사용하여 인덱스 칼럼을 변형한 경우에도 인덱스를 사용할 수 없다.

3) 옵티마이저의 취사 선택

    조건을 만족하는 데이터가 많은 경우, 결과를 추출하기 위해서 테이블의 대부분의 블록을 액세스해야 한다고 옵티마이저가 판단하면

                    조건에 사용 가능한 인덱스가 존재해도 전체 테이블 스캔 방식으로 읽을 수 있다.

4) 그 밖의 경우

    병렬처리 방식으로 처리하는 경우 또는 전체 테이블 스캔 방식의 힌트를 사용한 경우

나. 인덱스 스캔

1) 인덱스 유일 스캔은 유일 인덱스(Unique Index)를 사용하여 단 하나의 데이터를 추출하는 방식이다.

    유일 인덱스는 중복을 허락하지 않는 인덱스이다.

    유일 인덱스 구성 칼럼에 모두 '='로 값이 주어지면 결과는 최대 1건이 된다.

2) 인덱스 범위 스캔은 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식이다.

     유일 인덱스의 구성 칼럼 모두에 대해 ‘=’로 값이 주어지지 않은 경우와

     비유일 인덱스(Non-Unique Index)를 이용하는 모든 액세스 방식은 인덱스 범위 스캔 방식으로 데이터를 액세스하는 것이다.

3) 인덱스 역순 범위 스캔은 인덱스의 리프 블록의 양방향 링크를 이용하여 내림 차순으로 데이터를 읽는 방식이다.

    이 방식을 이용하여 최대값(Max Value)을 쉽게 찾을 수 있다. 이 또한 인덱스 범위 스캔의 일종이다.

4) 기타방식들 - 인덱스 전체 스캔(Index Full Scan), 인덱스 고속 전체 스캔(Fast Full Index Scan), 인덱스 스킵 스캔(Index Skip Scan) 

 

 


제3절 조인 수행 원리


번호 제목 글쓴이 날짜 조회 수
47 5 연습문제 RYUM 2014.08.07 9534
46 5 연습문제 RYUM 2014.08.07 7509
» 5_장_요약 balto 2011.11.16 6977
44 SQL 문제 [2] file balto 2011.11.15 26818
43 제3절_조인_수행_원리 노랑배 2011.11.10 6378
42 제1절_옵티마이저와_실행계획 실천하자 2011.11.08 5274
41 제1절 옵티마이저와 실행계획 실천하자 2011.11.07 5989
40 4_연습문제 monsterRachel 2011.11.03 10834
39 4_장_요약 suspace 2011.11.03 6423
38 제6절_윈도우_함수(WINDOW_FUNCTION) suspace 2011.10.27 20530
37 제8절_절차형_SQL monsterRachel 2011.10.27 8682
36 제7절_DCL(DATA_CONTROL_LANGUAGE) file DB지기 2011.10.26 8522
35 제2절_인덱스_기본4 balto 2011.10.24 7707
34 .. balto 2011.10.14 4840
33 제1절_표준_조인(STANDARD_JOIN) 노랑배 2011.10.13 15659
32 3_연습문제 DB지기 2011.10.13 7076
31 3_장_요약 file DB지기 2011.10.13 6066
30 제3절_계층형_질의와_셀프_조인 [1] 실천하자 2011.10.12 11483
29 제2절_집합_연산자(SET_OPERATOR) [1] 실천하자 2011.10.11 13864
28 제5절_그룹_함수(GROUP_FUNCTION) file balto 2011.10.08 11424