[SQL] - SQL 최적화의 원리

SQLD를 준비하며 공부했던 내용으로, 2020 이기적 SQL 개발자(Developer) 이론서 + 기출문제를 참고했다.

SQL 최적화의 원리

• 옵티마이저
  • SQL을 어떻게 실행할 것인지 계획, SQL 실행계획을 수립하고 SQL을 실행
  • SQL의 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어
  • 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라서 성능이 달라짐
  • 옵티마이저의 실행 계획은 SQL 성능에 아주 중요한 역할을 함

• 옵티마이저 특징

  • 데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정함
  • 옵티마이저는 여러 개의 실행 계획 중에서 최저 비용을 가지고 있는 계획을 선택해서 SQL을 실행함

• 옵티마이저의 필요성

  • SQL 개발자가 작성한 SQL문을 어떻게 실행하느냐에 따라 성능이 달라짐
  • 옵티마이저에게 실행 계획을 변경하도록 요청할 수 있는데 이때 힌트(HINT)를 사용함

• 옵티마이저 실행 계획 확인

  • SQL 실행 계획을 PLAN_TABLE에 저장함
  • PLAN_TABLE을 조회해서 실행 계획을 확인할 수 있음

    DESC PLAN_TABLE;

• 옵티마이저의 실행 방법

  • 개발자가 SQL을 실행하면 파싱을 실행해서 SQL의 문법 검사 및 구문분석을 수행함
  • 구문 분석이 완료되면 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립함
  • 옵티마이저는 기본적으로 비용 기반 옵티마이저를 사용해서 실행 계획을 수립함, 비용 기반 옵티마이저는 통계정보를 활용해서 최적의 실행 계획을 수립하는 것
  • 실행 계획 수립이 완료되면 최종적으로 SQL을 실행하고 실행이 완료되면 데이터를 인출(Fetch)함
  • 개발자 SQL → Parsing → 규칙기반/비용기반 옵티마이저 → 실행게획(PLAN_TABLE) → SQL 실행
  • 옵티마이저 엔진 1) Query Transformer: SQL문을 효율적으로 실행하기 위해서 옵티마이저가 변환함, SQL이 변환되어도 그 결과는 동일함 2) Estimator: 통계정보를 사용해서 SQL 실행비용을 계산함, 총비용은 최적의 실행 계획을 수립하기 위해서임 3) Plan Generator: SQL을 실행할 실행 계획을 수립함

    • 규칙 기반 옵티마이저는 실행 계획을 수립할 때 15개의 우선순위를 기준으로 실행 계획을 수립함

    • 최신 Oracle 버전은 비용 기반 옵티마이저를 기본적으로 사용

      SELECT /*+ RULE */ * FROM EMP
      WHERE ROWID = 'AAAHYhAABAAALNJAAN';

      • 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라서 성능이 달라짐

    • '/+ RULE /'를 사용해서 옵티마이저에서 규칙 기반 옵티마이저로 실행하도록 알려줌 (HINT)

  • 비용 기반 옵티마이저
    • 오브젝트 통계 및 시스템 통계를 사용해서 총 비용을 계산함
    • 총비용이라는 것은 SQL문을 실행하기 위해서 예상되는 소요시간 혹은 자원의 사용량
    • 총비용이 적은 쪽으로 실행 계획을 수립하되, 비용 기반 옵티마이저에서 통계정보가 부적절한 경우 성능 저항가 발생할 수 있음
  • 인덱스
    • 데이터를 빠르게 검색할 수 있는 방법을 제공함
    • 인덱스는 인덱스 키(ex.EMPNO)로 정렬(SORT)되어 있기 때문에 원하는 데이터를 빠르게 조회함
    • 인덱스는 오름차순(ASCENDING) 및 내림차순(DESCENDING) 탐색이 가능함
    • 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고 하나의 인덱스는 여러 개의 칼럼으로 구성될 수 있음
    • 테이블을 생성할 때 기본키(Primary Key)는 자동으로 인덱스가 만들어지고 인덱스의 이름은 SYSXXXX임
    • 인덱스의 구조는 Root Block, Branch Block, Leaf Block으로 구성됨
    • Root Block은 인덱스 트리에서 가장 상위에 있는 노드, Branch Block은 다음 단계의 주소를 가지고 있는 포인터(pointer)로 되어 있음
    • Leaf Block은 인덱스 키와 ROWID로 구성되고 인덱스 키는 정렬되어서 저장되어 있음
    • Leaf Block은 Double Linked List 형태로 되어 있어 양방향 탐색 가능
    • Leaf Block에서 인덱스 키를 읽으면 ROWID를 사용해서 EMP테이블의 행을 직접 읽을 수 있음
  • 인덱스 생성

    • 'CREATE INDEX'문을 사용해서 생성 가능

    • 인덱스를 생성할 때 한 개 이상의 칼럼을 사용해서 생성할 수 있음

    • 인덱스 키는 기본적으로 오름차순으로 정렬하고 'DESC'구를 포함하면 내림차순으로 정렬함

      CREATE INDEX IND EMP ON
      					EMP(ENAME ASC, SAL DESC)

  • 인덱스 유일 스캔(Index Unique SCAN)

    • Unique Index SCAN은 인덱스의 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생됨

    • ex.EMPNO가 중복되지 않는 경우 특정 하나의 EMPNO를 조회함

      SELECT * FROM EMP WHERE EMPNO=1000;

  • 인덱스 범위 스캔(INDEX Range SCAN)

    • Index Range SCAN은 SELECT문에서 특정 범위를 조회하는 WHERE문을 사용할 경우 발생함

    • ex. LIKE, BETWEEN / 데이터의 양이 적은 경우는 인덱스 자체를 실행하지 않고 TABLE FULL SCAN이 될 수 있음

    • Index Range SCAN은 인덱스의 Leaf Block의 특정 범위를 스캔한 것

      SELECT EMPNO FROM EMP
      WHERE EMPNO >=1000;

  • 인덱스 전체 스캔(Index Full SCAN)

    • 인덱스에서 검색되는 인덱스 키가 많은 경우에 Leaf Block의 처음부터 끝까지 전체를 읽어 들임

      SELECT ENAME, SAL FROM EMP
      WHERE EMPNO LIKE '%' AND SAL >0;

  • High Watermark: 테이블에서 데이터가 저장되어 있는 최상위 위치

• 실행계획(Execution Plan)

  • EMP 테이블과 DEPT 테이블을 조인하고 EMP 테이블의 DEPTNO 번호가 10번인 것을 조회하는 SQL

    SELECT * FROM EMP, DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO
    	AND EMP.DEPTNO = 10;

  • Nested Loop방식의 조인은 DEPT 테이블에서 먼저 데이터를 찾고 그다음 EMP 테이블을 찾는 것을 의미함, 이를 Random Access라고 함

  • INDEX를 검색하고 ROWID를 사용해서 테이블을 조회하는 순서로 실행됨, 먼저 조회되는 테이블은 Outer Table, 그 다음 조회되는 테이블은 Inner Table

• 옵티마이저 조인

  1) Nested Loop 조인

  • 하나의 테이블에서 데이터를 먼저 찾고 그다음 테이블을 조인하는 방식

  • 먼저 조회되는 테이블을 외부 테이블(Outer Table)이라고 하고 그다음 조회되는 테이블을 내부 테이블(Inner Table)이라고 함

  • 외부 테이블(선행 테이블)의 크기가 작은 것을 먼저 찾는 것이 중요함, 데이터가 스캔되는 범위를 줄일 수 있기 때문

  • RANDOM ACCESS가 발생하는데 많이 발생하면 성능 지연이 발생함, RANDOM ACCESS의 양을 줄여야 성능이 향상됨

    SELECT /*+ ordered use_nl(b) */ *
    FROM EMP a, DEPT b
    WHERE a.DEPTNO = b.DEPTNO
    	AND a.DEPTNO = 10;

  • use_nl 힌트를 사용해서 의도적으로 Nested Loop 조인을 실행함

  • 실행 계획을 보면 EMP 테이블을 먼저 FULL SCAN하고 그다음 DEPT 테이블을 FULL SCAN하여 Nest Loop 조인을 하는 것

  • ordered 힌트는 FROM 절에 나오는 테이블 순서대로 조인을 하게 되는 것, ordered 힌트는 혼자 사용되지 않고 use_nl, use_merge, use_hash 힌트와 함께 조인함

  2) Sort Merge 조인

  • 두 개의 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩(Loading)하고 SORT를 수행함

  • 두 개의 테이블에 대해서 SORT가 완료되면 두 개의 테이블을 병합(Merge)함

  • 정렬(SORT)이 발생하기 때문에 데이터양이 많아지면 성능이 떨어지게 됨

  • 정렬 데이터양이 너무 많으면 정렬은 임시 영역에서 수행됨, 임시 영역은 디스크에 있기 때문에 성능이 급격히 떨어짐

    SELECT /*+ ordered use_merge(b) */ *
    FROM EMP a, DEPT b
    WHERE a.DEPTNO = b.DEPTNO
    	AND a.DEPTNO = 10;

  3) Hash 조인

  • 두 개의 테이블 중에서 작은 테이블을 HASH 메모리에 로딩하고 두 개의 테이블의 조인 키를 사용해서 해시 테이블을 생성함

  • 해시 함수를 사용해서 주소를 계산하고 해당 주소를 사용해서 테이블을 조인하기 때문에 CPU 연산을 많이 함

  • 특히 Hash 조인 시에는 선행 테이블이 충분히 메모리에 로딩되는 크기여야 함

    SELECT /*+ ordered use_hash(b) */ *
    FROM EMP a, DEPT b
    WHERE a.DEPTNO = b.DEPTNO
    	AND a.DEPTNO = 10;