인덱스 튜닝

인덱스 튜닝

• 책에서는 랜덤 I/O를 줄이는 것을 강조함

3.1 테이블 엑세스 최소화

• 이번장에서는 테이블 랜덤 엑세스를 최소화하는 구체적인 방법들을 소개

3.1.1 테이블 랜덤 엑세스

• 인덱스로 검색하는데 느린 이유는 무엇일까?
  • 인덱스에 대한 이해도가 필요하다.

• 물리적 주소 vs 논리적 주소
  • 물리적 주소 : 실제 물리적 주소
  • 논리적 주소 : 논리적 의미를 담고 있음

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

  • 인덱스를 사용하는 이유는 ROWID를 빠르게 얻기 위함이다.

  • ROWID가 데이터파일 번호, 오브젝트 번호, 블록 번호 같은 물리적 요소로 이루어져 있기 때문에 물리적 주소라고 생각한다면 완전히 틀린 것은 아니다.

  • 하지만 ROWID는 논리적 주소에 가깝다.

    • 인덱스에서 ROWID를 찾자마자 바로 데이터를 찾아다주는 것이 아니다

    • ROWID를 확인 후 디스크에 접근하기 때문에 논리적 주소 정보라고 할 수 있다.

• 메인메모리 DB와 비교
  • 메인메모리 DB의 경우 메모리상의 주소정보를 인덱스로 갖는다.
  • 그 말은 인덱스를 경우해 테이블을 바로 융엑세스 할 수 있다는 의미다.
  • 오라클은 테이블 블록이 수시로 버퍼캐시에서 밀려났다가 다시 캐싱되기 때문에 인덱스에서 포인터로 직접 연결할 수 없는 구조다.
  • 메모리 주소 정보(포인터)가 아닌 디스크 주소 정보(Data Block Address)를 이용해 해시 알고리즘으로 버퍼 블록을 찾아감

• I/O 메커니즘 복습
  • DBA(=데이터파일번호 + 블록번호)는 디스크 상에서 블록을 찾기 위한 주소 정보
  • 매번 디스크에서 블록을 읽을 수 없으니 버퍼 캐시를 활용해야 함
  • 디스크 접근 전 DBA를 해시 함수에 넣은 다음 해시 체인을 찾고 버퍼 헤더를 찾는다.
  • 버퍼 헤더는 실제 데이터가 담긴 버퍼 블록의 주소값을 가지고 있다.

3.1.2 인덱스 클러스터링 팩터

• 클러스터링 팩터(Clustering Factor, CF)는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미함
  • 예를 들어 '거주지 = 제주'인 고객 데이터가 물리적으로 근접해 있는 것과 같다.
  • 인덱스 정렬순서와 물리적 접근도를 확인하면 된다.

• 인덱스 클러스팅 팩터 효과
  • 인덱스 ROWID로 테이블을 엑세스 할 때, 오라클은 래치 획득과 해시 체인 스캔과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터를 바로 해제하지 않고 일단 유지한다. 버퍼 Pinning이라 한다.
  • 이 상태에서 다음 인덱스 레코드를 읽는데 우연히 직전과 같은 테이블 블록을 가리키면 래치 획득과 해시 체인 스캔과정을 스킵하기 때문에 IO 효율이 좋아짐

3.1.3 인덱스 손익분기점

• 인덱스 ROWID를 이용한 테이블 엑세스는 고비용 구조이다.
  • 읽어야 할 데이터가 일정량을 넘는 순간 table full scan보다 느려진다.
  • 이 느려지는 시기가 손익분기점이다.

• Table Full Scan은 성능이 일정하다.
  • 반면 인덱스 스캔은 몇 건을 추출하느냐에 따라 성능이 천차만별로 달라진다.

• 인덱스를 이용한 테이블 엑세스가 Table Full Scan보다 느려지게 만드는 핵심적인 두 요소
  • Table Full Scan은 시퀀셜 엑세스인 반면, 인덱스 ROWID를 이용한 테이블 엑세스는 랜덤 엑세스 방식이다.
  • Table Full Scan은 Multiblock IO인 반면, 인덱스 ROWID를 이용한 테이블 엑세스는 Single Block IO이다.

인덱스 스캔 효율화

• 인덱스 스캔 효율성 측정
  • SQL 트레이스 사용

Row Source Operation
--------------------------------------------------------------------------
TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=7471 pr=1466 pw=0 time=22137 us)
  INDEX RANGE SCAN BIG_TABLE_IDX (cr=7463 pr=1466 pw=0 time =22328 us)

• cr = 읽은 블록의 개수 (consistent read) (buffer cache에서 읽은 개수)
• pr = physical read (디스크에서 읽은 개수)
• pw = physical write (디스크 블럭 쓰기)

인덱스 엑세스 조건, 인덱스 필터 조건

• 인덱스 엑세스 조건 : 인덱스 스캔 범위를 결정하는 조건절
• 인덱스 필터 조건 : 테이블로 엑세스할지를 결정하는 조건절

• 옵티마이저의 비용 계산 원리

  • 인덱스 수직적 탐색비용 + 인덱스 수평적 탐색비용 + 테이블 랜덤 엑세스 비용

    = 인덱스 루트와 브랜치 레밸에서 읽는 블록 수 +

    인덱스 리프 블록을 스캔하는 과정에 읽는 블록 수 +

    테이블 엑세스 과정에 읽는 블록 수

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

• 인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 등치 조건으로 사용할 때가 가장 좋다.
  • 리프 블록을 스캔하면서 읽은 레코드가 모두 테이블 엑세스로 이어지기 때문이다.
  • 인덱스 컬럼중 일부가 조건절에 없거나 등치 조건이 아니더라도, 그것이 뒤쪽 컬럼일 때는 비효율 없음

• 인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위검색 조건이면, 인덱스를 스캔하는 단계에서 비효율이 생긴다.
  • BETWEEN을 IN-List로 전환하면 효과를 얻는 경우도 있다.

3.4 인덱스 설계

• 인덱스 설계가 어려운 이유
  • 인덱스를 마음대로 생성하면 테이블마다 인덱스가 수십 개씩 달리게 되고 관리비용 뿐만아니라 시스템 부하를 증가시키는 요인이 된다.
    • DML 성능 저하 -> TPS 저하
    • 데이터베이스 사이즈 증가 -> 디스크 공간 낭비
    • 데이터베이스 관리 및 운영 비용 상승
  • 예를 들어 테이블에 인덱스가 6개 달려있다면, 테이블에 새로 데이터가 추가될 때 인덱스에도 각각 데이터를 추가하고 정렬해줘야 한다.
  • 데이터를 지우는 상황에서도 마찬가지다.

• 가장 중요한 두가지 선택 기준
  • 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.
  • ' = ' 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.

​

• 스캔 효율성 이외의 판단 기준
  • 수행 빈도 (가장 중요)
  • 업무상 중요도
  • 클러스터링 팩터
  • 데이터량
  • DML 부하(기존 인덱스 개수, 초당 DML 발생량, 자주 갱신하는 컬럼 여부 등)
  • 저장 공간
  • 인덱스 관리 비용

• 소트 연산을 생략하기 위한 방법
  • ' = ' 연산자로 사용한 조건절 컬럼 선정
  • ORDER BY 절에 기술한 컬럼 추가
  • ' = ' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정