질의 처리

DBMS에서 SQL문의 처리를 담당하는 모듈을 질의 처리기(Query Processor)라고 한다. 질의 처리기는 사용자가 수행을 요구한 SQL문에 대해 정당성을 검사한 후 데이터베이스에 접근하는 최적의 접근 순서와 방법을 결정하고, 이에 따라 조건에 맞는 레코드를 검색한 후 필요한 연간을 수행하여 값을 돌려준다.

  • 처리과정은 아래와 같다.

sql01

  • parsing : SQL문의 문법을 검사하고, 구분 분석 정보를 저장한다.
  • Validation : SQL문의 의미상 정당성을 검사한다.
  • Optimization : 주어진 Parse Tree에 기반하여 다양한 통계 정보와 접근 비용을 계산하여 최적화된 실행 계획을 생성한다.
  • Execution : 실행 계획 트리에 따라 SQL문을 실행한다.

옵티마이저(Optimizer)

옵티마이저는 SQL을 가장 빠르고 효율적으로 수행할 수 있는 최적의 처리 경로를 생성해주는 DBMS 내부의 핵심 엔진이다. 사용자가 SQL을 작성하면, 이를 생성하는데 필요한 처리경로를 DBMS에 내장된 옵티마이저가 자동으로 생성해준다. 옵티마이저가 생성한 SQL 처리경로를 실행계획(Execution Plan)이라고 한다. 각 실행 계획을 비교해서 최저 비용을 갖는 하나를 선택하는 과정이 쿼리를 최적화하는 과정이다.

옵티마이저의 종류

  • 규칙 기반 옵티마이저(Rule-Based Optimizer, Heuristic Optimizer)
    • 미리 정해 놓은 규칙에 따라 액세스 경로를 평가하고 실행 계획을 선택한다.
  • 비용기 반 옵티마이저(Cost-Based Optimizer)
    • 여러 통계 정보를 기초로 오퍼레이션 단계 별 예상 비용을 산정, 합산한 총비용이 가장 낮은 계획을 선택한다.
    • 레코드 갯수, 블록 개수, 평균 행 길이, 칼럼 값의 수, 칼럼 값 분포, 인덱스 높이, 클터스터링 팩터 등을 고려한다.(선택도, 카디널리티, 비용)
  • 스스로 학습하는 옵티마이저(Self-Learning Optimizer)
    • 예상치와 런타임 수행 결과를 비교하고, 실행계획을 조정한다.

저장 매체에 따른 동작방법

  • 메모리 테이블 조회
    • CPU 비용을 최도화할 수 있는 실행계획 생성한다.
    • 인덱스를 사용하는 것이 전체 테이블 스캔보다 나은 성능을 보장한다.
  • 디스크 테이블 조회
    • I/O를 최소화 할 수 있도록 실행계획 생성한다.
    • 데이터의 분포에 따라 인덱스 보다 전체 테이블 스캔이 더 적은 I/O를 발생시킨다.

실행계획(explain)

SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미한다. 생성된 실행계획을 보는 방법은 데이터베이스의 벤더마다 서로 다르기 때문에 각각의 데이터베이스별로 확인이 필요하다.

(altibase)

sql02

  • 실행 계획 트리에서 하나의 노드는 한 행에 표시된다. 왼쪽으로 들여쓰기가 많이 되어 있는 노드일수록 하위 노드이며 가장 먼저 수행된다.
  • 실행계획을 구성하는 요소에는 조인 순서, 조인 기법, 액세스 기법, 최적화 정보, 연산 등이 있다.

조인 순서

조인 순서는 조인 작업을 수행할때 access하는 테이블의 순서를 나타낸다. join할 때 먼저 access되어 access path를 주도하는 테이블을 드라이빙 테이블이라고 한다. (join할때 먼저 access되는 쪽을 드라이빙 테이블(DRIVING TABLE=OUTER TABLE) 이라고 하고, 뒤에 access되는 테이블을 inner 테이블이라고 한다.)

결정 조건

  • 드라이빙 테이블로 결정되는 것은 index의 존재 유무, 상수 조건의 유무, 혹은 from절에서의 테이블 순서에 따라 결정이 된다.

중요한 이유

  • 어떤 테이블에 먼저 access되는지에 따라 속도의 차이가 크게 날 수 있다. 데이터의 단위가 커지면 커질수록 더더욱 중요해 질 수 있다.
  • 작업 대상이 되는 테이블의 데이터가 더 적을수록(즉, 드라이빙 테이블을 더 적은 데이터를 가진 테이블로 잡을수록) access되어 전체 탐색하는 양이 줄어들게 된다.

최적화의 목표

  • 전체 처리속도의 최적화 : 쿼리 최종 결과집합을 끝까지 읽는 것을 전제로, 시스템 리소스(I/O, CPU, 메모리 등)를 가장 적게 사용하는 실행계획을 선택한다.
  • 최초 응답속도 최적화 : 전체 결과집합 중 일부만 읽다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.

개인적으로 데이터를 다뤘을때는 많아봐야 몇 백 ~ 몇 천건정도의 데이터를 가지고 하다보니 최적화된 쿼리에대해 깊이있게 고민하지 않았다. 그러나 일을 하면서 수백만 ~ 억 단위의 테이블에 접근해서 데이터를 가져와 다루다 보니 쿼리문을 작성할때 좀 더 신중해지게 되었다. 이참에 쿼리가 어떻게 처리가 되고 최적화가 되는지 다시 한번 생각해보고 넘어갈 수 있도록 해보자.