6부 - SQL 최적화

1. 기본 방향

• SQL 튜닝 시 중점적으로 점검할 항목

  • 통계
  • 최적 실행계획이 수립되도록 테이블 통계 정보가 존재하고 주기적으로 갱신되는가?
  • 인덱스
  • 적절한 인덱스를 사용하는가?
  • 인덱스가 있음에도 사용하지 못하는가?
  • 인덱스 수가 많아서 INSERT 처리 시간이 오래걸리는가?
  • 조인
  • 처리하는 데이터 양과 연결 관계에 맞는 조인 방식이 선정됐는가?
  • 조인 관계에서 선행 테이블은 적합한가?
  • 테이블
  • 데이터 수정과 삭제로 테이블 조각화가 발생해 재구성할 필요는 있는가?
  • 파티션으로 INSERT 부하를 분산해 성능 개선이 가능한가?

2. SQL 을 위한 기본 지식

2.1 데이터베이스의 기본 구조

• 클라이언트

  • 클라이언트는 DB 솔루션에서 제공하는 클라이언트 모듈을 통해 데이터베이스에 저장된 데이터를 조회하거나 수정할 수 있는 기능을 제공한다.
  • 클라이언트 측면에서 중요 개선 사항
  • 복구 건의 레코드를 한번의 호출로 집합 처리하거나 두 개 이상의 쿼리를 통합 처리함으로써 DB 호출 수를 줄인다.
  • JDBC Statement를 캐시하거나 SQL 커서를 오픈한 상태로 유지함으로써 쿼리 소프트 파싱을 회피해 성능을 개선할 수 있다.
  • 패치 크기(Fetch Size)를 증가시켜 DB 엔진에서 클라이언트로 조회된 데이터를 전송 할 때 한번에 많은 데이터를 전송함으로써 성능을 개선할 수 있다.

• 데이터베이스 엔진

  • 엔진은 클라이언트 요청을 받아 파일시스템에 저장된 데이터를 조회하거나 수정하는 기능을 수행한다.
  • 엔진 내부에는 락 관리와 트랜잭션 관리가 이뤄진다.
  • 데이터베이스 엔진은 처리 성능 개선을 위해 동작에 필요한 기초 정보와 파일시스템에 저장된 데이터 중 일부를 메모리에 캐시하고 있어 큰 메모리를 필요로 한다.

• 데이터베이스 엔진 메모리의 주요 용도

  • 데이터 캐시
  • 파일시스템에 저장된 데이터가 조회되면 해당 데이터를 메모리 데이터 캐시에 저장해 이후 동일 데이터 조회 시 파일 시스템의 물리적인 입출력이 발생하지 않게 한다.
  • 실행계획
  • SQL이 처음 호출되면 대상 데이터를 찾는 방법이 기술돼 있는 실행계획을 만들고 이후 동일한 SQL이 수행되면 기존에 만들어진 실행계획을 재사용한다.
  • DB 정보
  • 테이블 구성 정보(메타 정보)와 통계를 저장하고 있으면서 SQL을 검증하고 실행계획을 수립할 때 참조한다.
  • 복구 로그
  • 복구 로그는 데이터 변경 전후 내역을 모두 저장해 데이터베이스에 장애가 발생했을 때 데이터를 복구하는 용도로 사용한다.

• 데이터베이스 서버 성능 개선을 위해서는 아래 사항에 유의해야 한다.

  • 데이터 캐시는 클수록 성능 개선 효과가 있지만 개선 효과가 메모리 크기에 비례해서 증가하지는 않는다. 어느 수준이상 증가하면 개선 효과가 감소되는 커브 곡선을 그리므로 캐시 적중률을 참고해서 적당한 크기를 찾아야 한다.
  • 바인드 변수를 사용하지 않으면 SQL의 조회 조건 값이 달리질 때마다 엔진은 서로 다른 SQL로 인식해 새로운 실행계획을 세우는 하드 파싱이 발생한다.
  • 테이블과 인덱스 전체 블록 개수, 데이터 건수, 항목별 선택도, 인덱스 현황 등 통계 정보를 기반으로 실행계획을 수립하는데 통계 정보가 없거나 오래되어 실제 데이터 분포와 다른 경우 잘못된 실행계획이 수립되어 성능 저하를 유발할 수 있다.

• 파일 시스템(저장)

  • 데이터 조회 시 물리적 IO가 발생하므로 데이터베이스 구조에서 가장 느린 부분이다.

2.2 블록 단위 처리

• 데이터베이스의 저장 구조는 데이터베이스, 테이블스페이스, 세그먼트, 익스텐트, 블록순으로 계층 관계를 이루고 있다.
• 불록은 데이터 IO의 가장 작은 단위이며, 할당할 수 있는 최소 단위이기도 하다.
• SELECT를 실행했을 때 읽은 블록이 많다는 것은 레코드를 찾는 과정이 불합리하다는 것을 의미한다.
• SQL 튜닝을 간단하게 정의하면 읽는 블록 수를 줄여주는 것이다.

2.3 캐시 IO 대 물리 IO

• 데이터베이스 구조에서 성능에 가장 영향을 주는 부분은 파일시스템에서 발생하는 물리IO다.

• 물리 IO가 주로 발생하는 쿼리인데 캐시로 인해 빠르게 조회되어 대상에서 제외될 수도 있다.

  • 읽은 블록 수가 많다면 줄일 수 있는 방안을 강구하는 것이 먼저다.

3. 성능 개선 대상 식별

3.1 SQL 수행 통계

3.2 실행 중인 세션 상태

3.3 수행 중인 SQL의 수행 상태 확인

3.4 락 대기

3.5 AWR 보고서

3.6 StatsPack

4. SQL 실행계획

4.1 SQL 실행계확과 수행 결과

4.1.1 SQL 실행계획 보기

4.1.2 SQL 수행 결과 확인

4.2 실행계획의 이해

• 실행계획이란 옵티마이저가 특정 SQL을 실행하기 위해 수행할 일련의 동작을 트리형식으로 표현한 계획이다.

4.2.1 조인

• 기본 조인 방식으로는 내포 조인, 해시 조인, 병합 조인, 스타 조인이라는 방식이 있다.

• 내포 조인(Neated loops join)

  • 선행 테이블과 후행 테이블을 오가며 반복적으로 탐색해서 원하는 레코드를 찾는다.
  • 선행 테이블에서 탐색된 레코드 건수만큼 후행 테이블의 랜덤 엑세스 탐색이 일어난다.
  • 즉, 선행 테이블의 조회 건수가 조인의 전체 작업량을 좌우한다.
  • 선행 테이블과 연결고리가 되는 후행 테이블의 조인 항목은 꼭 인덱스가 있어야 한다.
  • 연골고리에 인덱스가 없는데 내포 조인이 사용되면 후행 테이블에 전체 범위 탐색(Full Scan)이 발생한다.

• 병합 조인(Merge join)

  • 조인되는 두 테이블의 연결고리가 되는 항목에 대한 인덱스가 없거나 대량 데이터를 처리하는 경우 내포 조인에서 발생하는 후행 테이블의 랜덤 엑세스를 제거하기 위해 사용하는 조인 방식이다.
  • 조인되는 두 테이블은 조인에 앞서 대상 데이터를 줄이기 위해 각 테이블에 대한 WHERE 조건으로 레코드를 먼저 모두 조회한 후에 연결고리가 되는 항목을 기준으로 정렬한다.
  • 양쪽에 정렬된 레코드를 순차적으로 비교해 가면서 연결고리가 일치하는 레코드를 찾는다.
  • 선후행 테이블 어느 것이 되든 상관없다.
  • 정렬된 데이터를 조인하는 작업은 효과적이지만 정렬을 해야하는 부담이 있다.

• 해시 조인(Hash join)

  • 선행 테이블에서 조인에 앞어 전체 범위 탐색으로 조인 대상 레코드를 모두 조회해서 연결고리인 항목에 대해 해시 맵을 만든다.
  • 그리고 후행 테이블은 내부 탐색 조건에 일치하는 레코드를 찾았을 때마다 해시 연산을 통해 해시 맵에 일치하는 레코드가 있는지 비교해서 조인을 수행한다.
  • 선행 테이블은 한 번에 전체 범위 탐색으로 데이터를 추출하고 후행 테이블은 순차적으로 부분 범위 탐색하므로 부분 범위 처리가 이뤄진다.
  • -

4..2 SQL 실행계획의 동작

4.3 인덱스

4.3.1 인덱스 구조

• 인덱스는 루트, 브랜치, 리프 블록으로 구성된다.

4.3.2 인덱스 종류

4.3.3 인덱스 사용 원칙

• 인덱스 생성 원칙

  • 1순위는 테이블의 기본 키다. 기본 키는 가장 빈도가 높은 테이블 엑세스 경로다.
  • 2순위는 테이블 간 연결고리로 사용되는 조인 항목 그룹이다. 온라인 SQL은 대부분 내포 조인을 사용하는데 후행 테이블의 연골고리에 인덱스가 없으면 성능에 치명적이다.
  • 3순위는 테이블 액세스 경로 상에 빈번하게 관찰되는 항목의 그룹이다. 항목 그룹은 해당 테이블을 조회하는 모든 SQL의 조회 조건에 사용된 항목들의 모임을 나열해서 그룹핑했을 때 가장 빈번하게 나타나느 항목 모임이다.
  • 인덱스를 만들 때는 선행 항목이 중요하다. 조회 조건에 선행 항목이 없으면 인덱스를 사용하지 못한다.
  • BETWWEN, < > 등 범위를 지정하는 조건이 주로 주어지는 항목은 결합 인덱스에서 일치(=) 조건 항목 이후에 위치하도록 구성해야 인덱스 탐색 범위를 줄일 수 있다.

• 조회 조건에 쓰인 항목에 인덱스가 있는데도 아래와 같은 경우는 인덱스를 사용하지 못한다.

  • 항목 변형 (SUBSTR, VALUE * 10)
  • NULL 사용 (VALUE IS NULL)
  • 부정 표현 (VALUE ≠ null)
  • 앞 % 사용 (VALUE LIKE = ‘%ABC’)
  • 내부 형변환 (NUMBER_VALUE = ‘1000’)

4.3.4 인덱스 탐색 방식

• 인덱스 유일 탐색(Index Unique Scan)

  • 인덱스를 구성하는 모든 항목이 WHERE 절에 일치(=) 조건으로 들어왔을 때 레코드 한건을 찾아가는 탐색으로 가장 빠르다.

• 인덱스 범위 탐색(Index Range Scan)

  • 인덱스의 일정한 범위를 읽어내는 인덱스 범위 탐색이다.
  • 한 개의 항목으로 구성된 일반 단일 인덱스가 있을 때 해당 항목이 WHERE 절에 일치 조건으로 있더라도 인덱스 탐색 방식은 범위 탐색을 수행한다. (유니크 인덱스가 아니기 때문에 동일한 레코드가 있을 수 있다)

• 인덱스 전체 탐색(Index Full Scan)

  • 테이블 전체 탐색과 마찬가지로 인덱스를 순차적으로 처음부터 끝까지 읽어들이는 탐색 방식이다.

• 인덱스 고속 전체 탐색(Index Fast Full Scan)

  • 고속 전체 탐색은 멀티 블록 IO로 순서에 관계없이 인덱스 전체를 탐색한다. 병철 처리를 할 수 있다는 점도 차이점이다.

• 인덱스 스킵 탐색(Index Skip Scan)

  • 일반적으로 결합 인덱스의 선행 항목이 WHERE 절에 없으면 해당 인덱스를 사용하지 않는다.
  • 그러나 선행 항목의 선택도 값이 커서 유일한 값의 종류가 소수일 때는 인덱스를 사용해 탐색하는 것이 테이블 전체 탐색보다 효율적인 경우가 있다.

4.3.5 인덱스 수와 성능

• 인덱스 수가 증가하면 INSERT나 UPDATE 처럼 데이터를 변경하는 DML에는 성능 저하가 발생한다.
• 데이터 입력이 거의 없는 테이블은 인덱스를 추가하는 데 부담이 없으나 입력이 빈번한 거래 로그나 업무 처리용 테이블은 제약받을 수 밖에 없다.

4.4 테이블

• 테이블에서 데이터를 읽는 방식에는 두 가지가 있다.

  • 인덱스 탐색을 통해 대상 레코드를 바로 찾아가서 읽는 방법
  • 전체 테이블을 읽어서 대상 레코드를 필터링해서 찾는 방법 (Table Full Scan)
• -

4.4.1 Direct-Path와 Conventional-Path

4.4.2 파티션 테이블

• 대용량 데이터베이스의 경우 한 테이블에 존재하는 레코드가 수십억 건을 저장하기도 한다.
• 데이터가 분산돼 있어 조회 시 데이터 탐색 범위가 넓어져 성능 저하가 발생한다.
• 이러한 문제를 해결하기 위해 데이터베이스는 관리와 조회 시에 중요한 역활을 담당하는 특정 컴럼을 기준으로 여러 개의 테이블로 나눠서 레코드를 저장, 관리하는 파티션이라는 기능이 있다.
• 논리적으로는 하나의 테이블이지만 파티션 키 값에 따라 물리적으로 수 개에서 수천 개에 이르는 테이블을 나눠서 저장하게 된다.