본문 바로가기
자격증/SQLP

[SQLP] 친절한SQL튜닝 3장 정리

by ordinary_daisy 2024. 11. 11.

3장.인덱스 튜닝

3.1 테이블 액세스 최소화

랜덤 I/O를 얼마나 줄일 수 있느냐가 인덱스 튜닝에 핵심

3.1.1 테이블 랜덤 액세스

인덱스 ROWID는 물리적 주소? 논리적 주소?

  • 인덱스를 스캔하는 이유는 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 곧바로 테이블 레코드를 찾아가기 위한 주소값인 ROWID를 얻으려는 데 있음
    • 인덱스 ROWID는 물리적 주소가 아닌 논리적 주소임
    • 물리적으로 직접 연결되어 있지 않고, 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담고 있기 때문
  • 실행계획에서 인덱스 스캔은 TABLE ACCESS BY INDEX ROWID 라고 표시된 부분을 확인

    인덱스 ROWID는 우편주소

  • ROWID를 우편주소에, 메인 메모리 DB가 사용하는 포인터를 전화번호에 비유할 수 있음
  • 하나의 레코드를 가장 빠르게 찾는다고 알려진 'ROWID에 의한 테이블 액세스'가 굉장한 고비용 연산인지 정확히 이해해야 함

I/O 매커니즘

  • I/O 성능을 높이려면 버퍼캐시를 활용해야 함 => 캐싱되어 있으면 테이블 액세스가 불필요하기 때문
  • 해싱 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 찾아감
  • 인덱스 Scan 할 때는 리프 블록에서 읽은 ROWID를 분해해서 DBA 정보를 얻음
  • 테이블 Full Scan 할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻음
  • 모든 데이터가 캐싱되어 있어도 매번 DBA 해싱과 래치 획득 과정을 반복해야 함
  • 동시 액세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생함

3.1.2 인덱스 클러스터링 팩터

  • 클러스터링 팩터(CF)가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋음
    • 테이블 액세스량에 비해 블록I/O가 적게 발생함
  • 테이블 액세스량에 비해 블록I/O가 적게 발생하는 이유는 '버퍼 Pinning' 덕분 (p138.그림 참고)
    • '버퍼 Pinning'이란? 인덱스 ROWID로 테이블 액세스할 때, 오라클을 래치 획득과 해시 체인 스캔 과정을 거쳐, 찾아간 테이블 블록에 대한 포인터(메모리 주소값)을 바로 해제하지 않고 일단 유지하는 개념 ~> 기출문제 나왔음
    • 버퍼 Pinning이 발동되면 래치 획득과 해시 체인 스캔 과정을 생략하고 테이블 블록을 바로 읽을 수 있음 => 논리적I/O블록 생략 가능

3.1.3 인덱스 손익분기점

  • Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 인덱스 손익분기점이라고 부름
  • 인덱스 손익 분기점은 보통 5~20%의 낮은 수준에서 결정됨
    • CF가 좋고 나쁨에 따라 손익분기점 수치가 결정됨
    • CF가 나쁘면 5% 이하, 좋으면 90%까지 올라가기도 함
  • Index Range Scan이 더 느려지는 이유
    • Table Full Scan은 시퀀셜 액세스인 반면, Index Range Scan는 랜덤 액세스
    • Table Full Scan은 MultiblockI/O 인 반면, Index Range Scan는 SingleBlockI/O 방식
  • 항상 테이블 스캔이 나쁜 것도 아니고, 항상 인덱스 스캔이 좋은 것도 아니라는 것을 명심

온라인 프로그램 튜닝 vs 배치 프로그램 튜닝

  • 온라인 프로그램은 보통 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 중요함
    • 조인도 대부분 NL방식 사용하고, 부분범위 처리를 통해 빠른 응답속도를 내는 것이 좋음
  • 배치 프로그램은 항상 전체범위 처리 기준으로 튜닝해야 함
    • 대량 데이터를 빠르게 처리하려면 인덱스 스캔과 NL조인보다 테이블 풀스캔과 해시 조인이 유리함
    • 초대용량의 데이터의 경우 테이블 풀스캔을 빠르게 처리하기 위해 테이블 파티션 활용 전략이 매우 중요한 튜닝 요소임

3.1.4 인덱스 컬럼 추가

  • 가장 일반적으로 사용하는 튜닝 기법
  • 인덱스가 많아지면 관리 비용이 증가함은 물론 DML 부하에 따른 트랜잭션 성능 저하가 생길 수 있음

3.1.5 인덱스만 읽고 처리

  • 인덱스만 읽어서 처리하는 쿼리를 'Covered 쿼리' 라고 부르며, 그 쿼리에 사용한 인덱스를 'Covered 인덱스'라고 부름
    • select 문에 인덱스 컬럼만 포함되어야 가능
    • 이 방법이 효과는 매우 좋지만 추가해야 할 컬럼이 많아 실제 적용하기 곤란한 경우도 많음

Include 인덱스

  • Oracle엔 없지만 SQL Server 2005 버전에 추가된 Include 인덱스 기능을 소개
  • Include 인덱스는 순전히 테이블 랜덤 인덱스를 줄이는 용도로 개발됨
  • 이는 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능이고, 인덱스 생성 시 include 옵션을 지정하면 적용됨
    • ex) create index emp_x01 on emp(deptno) include(sal)
  • 다중 컬럼 인덱스와 다른 점
    • 다중 컬럼 인덱스는 DEPTNO, SAL 모두 루트와 브랜치 블록에 저장함 -> 모두 수직적 탐색에 사용 가능
    • Include 인덱스 SAL 컬럼은 리프 블록에만 저장함 -> 수직적 탐색엔 DEPTNO만 사용하고, 수평적 탐색엔 SAL 컬럼을 필터 조건으로 사용

3.1.6 인덱스 구조 테이블

  • 오라클은 'IOT(Index-Organized Table)', SQL Server는 '클러스터형인덱스'라고 부름
  • IOT는 인위적으로 CF를 좋게 만드는 방법 중 하나
  • IOT는 인덱스 리프블록이 곧 데이터 블록인 구조라, 정렬 상태를 유지하며 데이터를 입력함

3.1.7 클러스터 테이블

인덱스 클러스터 테이블

  • 일반 테이블 구조에선 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖지만, 클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖음
  • 랜덤 액세스가 값 하나당 한 번씩 밖에 발생하지 않고, 클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는 게 핵심 원리
  • 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조
    • 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해 클러스터 체인으로 연결함
  • 인덱스 클러스터 테이블을 구성
    create cluster c_dept#(deptno number(2)) index;
  • 클러스터에 테이블을 담기 전 클러스터 인덱스를 반드시 정의해야 함
    create index c_dept#_idx on cluster c_dept#;
  • 클러스터 인덱스를 만들었으면 클러스터 테이블을 생성함
      create table dept(
      deptno number(2) not null,
      dname   varchar2(14) not null,
      loc     varchar2(13)
  ) cluster c_dept#(deptno);
해스 클러스터 테이블
  • 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다르고, 이외에는 인덱스 클러스터 테이블과 동일함

3.2 부분범위 처리 활용

  • 테이블 랜덤 액세스로 인한 인덱스 손익분기점의 한계를 극복할 히든카드인 부분범위 처리에 대해 소개하는 챕터

    3.2.1 부분범위 처리

  • 부분범위 처리란 데이터를 모두 읽어 한 번에 전송하지 않고, 먼저 읽는 일정량(Array size)를 전송하고 멈추는 개념
  • 먼저 읽은 데이터를 전송하고 나면 서버 프로세스는 CPU를 OS에 반환하고 대기 큐에서 잠을 자고, 다음 Fetch Call을 받으면 대기 큐에서 나와 그다음 데이터부터 일정량을 읽어서 전송하고 또다시 잠을 자게 됨

    정렬 조건이 있을 때 부분범위 처리

  • 정렬 조건으로 인덱스 선두 컬럼을 줘야 정렬 연산을 생략할 수 있음

3.2.2 부분범위 처리 구현

  • p164,165 참고

3.2.3 OLTP 환경에서 부분범위 처리에 의한 성능개선 원리

  • OLTP 시스템이라고 항상 소량 데이터만 조회하는 것이 아니기 때문에, 인덱스 순으로 order by 구문을 구성하면 정렬 연산을 생략할 수 있음

3.3 인덱스 스캔 효율화

  • 운영 환경에서 가능한 일반적인 튜닝 기법은 인덱스 컬럼 추가임

3.3.1 인덱스 탐색

  • p174~179 예시 참고

3.3.2 인덱스 스캔 효율성

  • 인덱스 선행 컬럼, 선두 컬럼이 where 조건에 있느냐 없느냐에 따라 스캔 효율성이 달라짐

3.3.3 액세스 조건과 필터 조건

  • WHERE절에 명시되어 있는 컬럼 중 액세스 조건과 필터 조건이 결정됨
  • 액세스 조건이란, 인덱스 스캔 범위를 결정하는 조건절
    • 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하고, 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는 데 영향을 미치는 조건절
  • 필터 조건이란, 인덱스에서 테이블로 액세스할지를 결정하는 조건절
    • 쿼리 다음 단계로 전다랗거나 최종 결과 집합에 포함할지를 결정함
  • 옵티마이저 비용 계산 원리
    비용 = 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 액세스 비용
      = 인덱스 루트와 브랜치 레벨에서 읽는 블록 수 +
    인덱스 리프 블록을 스캔하는 과정에서 읽는 블록 수 + 
    테이블 액세스 과정에 읽는 블록 수

3.3.4 비교 연산자 종류와 컬럼 순서에 따른 군집성

  • 테이블과 달리 인덱스에는 '같은 값'을 갖는 레코드들이 서로 군집해 있음
  • p187~189 예시 참고
  • OR Expansion or INLIST ITERTOR
    • OR조건
      • A OR (B AND C) = (A OR B) AND (A OR C) 가 아님
      • Expansion쿼리변환: 옵티마이저가 union all로 변환할 수 있음
      • use_concat .힌트로 union all로 or expansion을 일으킴
    • IN조건
      • 기본적으로 필터조건
      • IN-LIST ITERATOR쿼리변환: 옵티마이저가 변환할 수 있음
        • 스캔조건
        • 스캔이 多회 일어남
        • union all 과 동일

3.3.5 인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 생기는 비효율

  • 인덱스 스캔 효율성은 인덱스 컬럼을 조건저렝 모두 등치(=) 조건으로 사용할 때 가장 좋음
  • 인덱스 컬럼 중 일부가 조건절에 없거나 등치 조건이 아니더라도, 그것이 뒤쪽 컬럼일 때는 비효율이 없음
  • 반면, 인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위 검색 조건이면, 인덱스를 스캔하는 단계에서 비효율이 생김

3.3.6 BETWEEN을 IN-LIST로 전환

  • 옵티마이저가 판단하여 내부적으로 BETWEEN구문을 INLIST ITERATOR로 변환하여 실행
  • 개념을 이해해보자면 IN-LIST 개수만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 '='조건으로 검색하는 것과 같음
  • 주의 사항
    • IN-LIST의 개수가 많지 않아야 함
    • 데이터 분포나 수직적 탐색 비용을 따져보지도 않고 BETWEEN을 IN-LIST로 변환하는 것은 위험

3.3.7 Index Skip Scan 활용

  • BETWEEN 조건을 IN-LIST 조건으로 변환하면 도움이 되는 상황에서 굳이 조건절을 바꾸지 않고도 효과를 낼 방법은 Index Skip Scan 임
  • 인덱스 선두 컬럼이 BETWEEN이어서 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 때, Index Skip Scan의 위력이 나타남

티스토리툴바