[ SQLD 이론 심화] Chapter 05. SQL 최적화 기본 원리

1. 옵티마이저와 실행 계획

 

• 옵티마이저란
  사용자가 질의한 SQL문에 대한 최적의 실행방법을 결정하는 역할을 수행함
  이러한 최적의 실행방법을 실행계획이라고 함
  다양한 실행 방법중 최적의 실행 방법을 결정함
  
  
• 비용기반 옵티마이저
  비용이 가장 적게 드는 실행계획을 선택하는 방식 (비용은 예상되는 시간 또는 자원을 의미함)
  테이블, 인덱스 등의 통계정보와 시스템 통계정보를 이용하여 최적의 실행계획을 도출함
  인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 테이블 풀 스캔을 유도함
  
  
• 구성 요소
  질의 변환기 : 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환
  비용 예측기 : 생성된 대안 계획의 비용을 예측하는 모듈
  대안계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈

2. 인덱스 기본

 

• 인덱스
  원하는 데이터를 쉽게 찾을 수 있도록 돕는 개념
  인덱스를 과도하게 가지게되면 DML작업 시 부하가 발생함
  
  루프 블록, 브랜치 블록, 리프 블록으로 구성됨
  가장 상위에 존재하는 블록이 루트 블록이고 브랜치 블록은 분기를 목적으로 하는 블록이다
  리프 블록은 트리의 가장 아래 단계에 존재하는 블록
  리프 블록은 인덱스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자인 ROWID로 구성
  
  
• 인덱스 구조 상세
  루프와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 가짐
  LMC(LeftMost Child)가 가리키는 주소로 찾아간 블록에는 키 값을 가진 첫번째 레코드보다 작거나 같은 레코드가 저장돼 있음.
  리프 블록에 저장된 각 레코드는 키 값 순으로 정렬돼 있을 뿐만 아니라 주소값 즉 ROWID를 가짐
  인덱스를 스캔하는 이유는 검색조건을 만족하는 소량의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위해서이다
  
  
• 인덱스 스캔 효율화
  인덱스 스캔 검색 시 유일성이 있는 속성부터 스캔하는것이 성능상 좋음
• 랜덤 액세스 최소화
  해당 작업은 DBMS 성능 부하의 주 요인이 됨
  
  
• Single Block I/O vs Multi Block I/O
  Single Block I/O는 인덱스 블록을 통해 테이블 블록의 주소를 얻어내 DB 버퍼 캐시로 데이터 접근 시
  
  Multi Block I/O는 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call시 디스크 상에 그 블록과 인접한 블록들을
  한꺼번에 읽어 캐시에 미리 적재하는 것

 

3. 조인 수행 원리

 

• 조인
  두 개 이상의 테이블을 하나의 집합으로 만드는 연산
  
  
• NL 조인
  RANDOM 엑세스 위주
  한 레코드 씩 순차 진행
  DRIVING 테이블 처리 범위에 의해 전체 성능이 결정됨
  
  
• 소트 머지 조인
  실시간 인덱스 생성 : 양쪽 집합을 정렬한 다음에는 NL 과 같음
  
  
• 해시 조인
  대량의 데이터 처리가 필요하고 쿼리 수행시간이 오래걸리는 대용량 테이블을 조인할 떄 사용
  Random 엑세스 부하 X. 정렬 부하 X
  작은 집합을 build input으로 하고 큰 집합을 probe input으로 하는 것이 중요