[SQLD] SQL 기본 및 활용(3)

옵티마이저와 실행 계획

• 옵티마이저 : 사용자가 질의한 SQL 문에 대해 최적의 실행 방법(실행 계획)을 결정하는 역할 수행 → 어떤 방법으로 처리하는 것이 동일한 일을 최소의 일량으로 처리할 수 있을지 결정
• SQL 문 실행 순서
  • 파싱(SQL 문법 검사 및 구문 분석) → 실행(옵티마이저 실행 계획 따라) → 인출(데이터를 읽어 전송)
• 옵티마이저 최적 실행 방법 결정 방식 : 규칙/비용기반
  • 규칙기반 옵티마이저
    • 규칙(우선순위)를 가지고 실행계획 생성 : 실행계획 생성 규칙 이해하는 누구나 실행계획을 비교적 쉽게 예측 가능
    • 인덱스를 이용한 액세스 방식이 젠체 테이블 액세스 방식보다 우선 순위가 높음
      • 이용 가능 인덱스가 존재하면 전체 테이블 액세스 방식보다 항상 인덱스를 사용하는 실행 계획을 생성
    • 조인순서 결정 시 조인 칼럼 인덱스 존재가 중요한 판단 기준
      • 조인 칼럼에 대한 인덱스가 양쪽에 존재 : 우선순위가 높은 테이블이 선행(Driving)
      • 한 쪽에만 인덱스 존재 : 인덱스 없는 테이블이 선행(NL Join 사용)
      • 모두 인덱스 존재 : FROM 절의 뒤에 나열된 테이블이 선행(Sort Merge Join 사용)
      • 우선순위가 동일 : FROM 절에 나열된 테이블의 역순으로 선행 테이블 선택
  • 비용기반 옵티마이저
    • 비용(예상 소요시간, 자원)이 가장 적은 실행계획을 선택
      • 다양한 객체 통계 정보와 시스템 통계 정보 등 이용 → 통계 정보 없을 경우 정확한 비용 예측 불가능
    • 규칙 기반 옵티마이저의 단점을 극복하기 위해 출현
    • 질의 변환기 : 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈
    • 대안 계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈
      • 대안 계획은 연산 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해 생성
      • 대안 계획의 생성이 많아지면 최적화를 수행하는 시간이 그만큼 오래 걸린다.
      • 대안 계획들 중에서 최적의 대안 계획이 포함되지 않을 수도 있다.
    • 비용 예측기 : 생성된 대안 계획 비용을 예측하는 모듈
      • 연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야함
      • 정확한 통계 정보, 대안 계획을 구성하는 각 연산에 대한 비용 계산식이 정확해야함
      • 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 수행하는 방법으로 실행 계획을 생성할 수도 있다.
    • 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL문도 서로 다른 실행계획이 생성될 수 있다.
    • 비용기반 옵티마이저의 다양한 한계로 실행 계획의 예측 및 제어가 어렵다.
• 실행 계획 : SQL 에서 요구한 사항을 처리하기 위한 절차와 방법, 실행 계획마다 성능은 서로 다를 수 있다.
  • 구성 요소
    • 조인 순서(Join Order)
    • 조인 기법(Join Method)
    • 액세스 기법(Access Method)
    • 최적화 정보(Optimization Information) : 실행 계획의 각 단계마다 예상 비용 표시
      • Cost : 상대적인 비용 정보
      • Card : Cardinality(결과 집합 건수)의 약자
      • Bytes : 결과 집합이 차지하는 메모리 양(예상치)
    • 연산(Operation) : 여러 조작을 통해 원하는 결과를 얻어내는 일련의 작업
• SQL 처리 흐름도 : SQL의 내부적인 처리 절차를 시각적으로 표현(실행계획의 시각화)한 도표; 조인 순서, 액세스 기법과 조인 기법 등을 표현 가능

인덱스 기본

인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 기능. Insert, Update, Delete와 같은 DML 작업은 테이블과 인덱스를 함께 변경해야하기 때문에 오히려 느려질 수 있다는 단점이 존재한다.

• 종류

  • 트리 기반 인덱스 : DBMS에서 가장 일반적인 인덱스 : 브랜치 블록과 리프 블록, 루트 블록으로 구성

    • 포인터 : 루트 블록과 브랜치 블록의 키 값, 하위 블록 키 값의 범위 정보
    • 리프 블록은 인덱스키와 ROWID(오라클에서 데이터를 구분할 수 있는 유일한 값)로 구성, Doubly Linked List 형태라서 양방향 탐색 가능
      • 오브젝트 번호 : 해당 데이터가 속하는 오브젝트 번호, 오브젝트 별로 유일한 값을 가짐
      • 상대 파일 번호 테이블스페이스 내 데이터 파일의 순번
      • 블록 번호 : 데이터 파일 내 데이터가 속해 있는 블록의 순번
      • 데이터 번호 : 데이터 블록에 데이터가 저장되어 있는 순번

  • 클러스터형 인덱스 (SQL Server) : 인덱스의 리프 페이지가 데이터를 포함함, 리프 페이지의 모든 로우가 인덱스키 칼럼 순서대로 물리적 정렬

    	CREATE INDEX 인덱스명 테이블명 ON 테이블명 (칼럼명, ...) 

• 인덱스 스캔 효율화 : 랜덤 액세스(DBMS 성능 부하 유발) 최소화, 인덱스 칼럼 순서는 랜덤 액세스와 무관
• 스캔 방법
  • 전체 테이블 스캔 : 테이블의 모든 데이터를 일으며 데이터 추출, 읽은 블록의 재사용성을 낮다고 판단해 메모리 버퍼에서 제거 : 많은 데이터 조회에 유리
    • SQL 문에 조건이 없거나, SQL 문 조건 관련 인덱스가 없거나, 전체 테이블 스캔을 하도록 강제로 힌트를 지정하거나, 옵티마이저가 유리하다고 판단하는 경우 수행
  • 인덱스 스캔 : 인덱스 구성 칼럼 값 기반으로 데이터 추출, 인덱스를 읽어 ROWID를 찾고 해당 데이터를 찾기 위해 테이블 읽음. 일반적으로 인덱스 칼럼 순서로 정렬되어 출력 : 적은 데이터 조회에 유리
    • 랜덤 액세스에 의한 부하 발생 가능, 중복 스캔 비효율 발생
    • 인덱스 범위 스캔 : 특정 범위에 인덱스 스캔 적용
      • 인덱스 역순 범위 스캔 : 리프 블록의 Doubly Linked List 저장 방식을 활용해 인덱스를 역순으로 스캔, 결과 집합 내림차순 정렬
    • 인덱스 유일 스캔 : 인덱스키가 중복되지 않을 때 단 한 건의 데이터 추출, 등호 조건으로 조회, 검색 속도가 가장 빠름
    • 인덱스 전체 스캔 : 리프 블록을 모두 읽으며 데이터 추출
      • 인덱스 고속 전체 스캔 : 물리적으로 저장된 순서대로 인덱스 리프 블록 스캔
      • 인덱스 스킵 스캔 : 인덱스 선두 칼럼이 조건절에 없어도 활용함, 상위 블록에서 읽은 칼럼 값 정보를 이용해 조건에 맞는 데이터를 포함하 ㄹ가능성이 있는 리프 블록만 접근
• IOT(Index-Organized Table) : 인덱스키가 붙은 칼럼으로 구성된 테이블, 인덱스가 원래 테이블을 참조하지 않음, 클러스터형 인덱스와 유사

조인 수행 원리

• 조인 순서 : 항상 두 테이블을 조인
  • 선행 테이블(First Table, Outer Table, Driving Table, Build Input)
  • 후행 테이블(Second Table, Inner Table, Driven Table, Probe Input) : 선행 테이블로부터 입력값을 받아 처리, 후행 테이블에 걸리는 조인 조건이 성능에 큰 영향을 줌
• 조인 방식 : NL 조인 → 소트 머지 조인 → 해시 조인 순으로 발전
  • NL 조인(Nested Loop Join) : 선행 테이블의 데이터 하나씩 순차적으로 조인(중첩 반복문과 유사), 선행 테이블 처리 범위가 성능 결정 (소트 머지 조인은 순서와 무관), 랜덤 액세스 위주로 대용량 데이터 처리에 불리, 유니크 인덱스를 이용해 소량 테이블 조인할 경우 유리
    • 절차
      • 선행 테이블에서 조건을 만족하는 행 찾음
      • 후행 테이블에 선행 테이블의 조인키가 존재하는지 확인
      • 후행 테이블 인덱스에 선행 테이블의 조인키 존재하는지 확인
      • 인덱스 추출한 ROWID로 후행 테이블을 액세스
    • 조인 결과를 하나씩 바로 출력해 OLTP(Online Transaction Processing) 환경에 적합
  • 소트 머지 조인 : 두 테이블을 개별적으로 스캔한 후 조인, 데용량 데이터 처리 시 디스크에서 정렬이 진행되어 성능상 불리, 인덱스 유무가 성능에 큰 영향을 주지 않음
  • 해시 조인 : 조인 칼럼을 기준으로 동일한 해시 값을 갖는 데이터의 실제 값을 비교하며 조인, 두 테이블의 데이터 차이가 클 때 유리, NL 조인의 랜덤 액세스와 소트 머지 조인의 정렬 부담 해결, EQUI JOIN에서만 사용가능, 해시 메모리에서 해시 테이블을 생성해 선행 테이블이 작을 때 유리, 테이블이 커서 소트 부하가 심할 때 유리, OLAP(Online Analytical Processing) 환경에 적합