인덱스 튜닝 1

테이블 액세스 최소화

SQL 튜닝은 랜덤 I/O와의 전쟁

• SQL 성능 향상을 위해 DBMS가 제공하는 많은 기능이 느린 랜덤 I/O를 극복하기 위해 개발됐고, 조인 메소드의 발전뿐만아니라 많은 튜닝 기법도 랜덤 I/O 최소화가 목적

테이블 랜덤 액세스

• 관리적인 측면을 제외하고 성능적 측면에서 파티션을 분리하는 이유
  • 인덱스로 검색해도 빠른데 굳이 테이블 파티셔닝을 하는 이유는?
• 인덱스 ROWID는 물리적 주소? 논리적 주소?
  • 인덱스를 스캔하는 이유는 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값, 즉 ROWID를 얻으려는 목적이다.
  • 인덱스 ROWID는 데이터파일 번호, 오브젝트 번호, 블록 번호같은 물리적 요소로 구성되어 있지만 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고 있어서 논리적 주소 라고 할 수 있다.
• DBMS에서 인덱스 ROWID를 이용해서 테이블 액세스를 하는 것은 고비용인데 인덱스는 결국 테이블 액세스를 효율적으로 하기 위한 방법이고 Index Scan + 테이블 액세스라는 과정이 포함되어 있기 때문이다.
  • Memory DB의 성능이 압도적으로 좋을 수 밖에없다. -> DISK I/O가 없기 때문

  버퍼 캐시 탐색 메커니즘

  1. 인덱스로 테이블 블록을 액세스 할 때는 리프 블록에서 읽은 ROWID를 분해해서 DBA정보를 얻고, 테이블을 Full Scan할 때는 익스텐트 맵을 통해 읽은 블록들의 DBA정보를 얻는다.
  2. DBA(데이터 파일 번호 + 블록 번호)를 해시 함수에 입력해 해시 체인을 찾고 거기서 버퍼 헤더를 읽는다. 해시 함수를 사용하기 때문에 버퍼 헤더는 항상 같은 해시 체인에 연결된다.
  3. 버퍼 헤더에 있는 버퍼 블록 주소를 통해 버퍼 블록을 찾아가고 버퍼 블록은 매번 다른 위치에 캐싱된다.
  4. ROWID가 가리키는 테이블 블록을 버퍼 블록에서 먼저 찾아보고 못 찾을 때만 디스크에서 블록을 읽고 버퍼 캐시에 적재한 후 읽는다.
  • 테이블 레코드를 찾기 위해서는 결국 DBA 해싱과 래치 획득 과정이 반복되며 동시 액세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합이 자주 발생한다.
    

인덱스 클러스터링 팩터

클러스터링 팩터란 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 것

• 데이터가 물리적으로 근접해 있으면 흩어져 있을 때보다 데이터를 찾는 속도가 빠르다.

  인덱스 클러스터링 팩터의 좋은 예시

  • 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 100% 일치한다.
    

  인덱스 클러스터링 팩터의 안 좋은 예시

  • 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 전혀 일치하지 않는다.
    
• 인덱스 클러스터링 팩터 효과
  • 테이블 액세스량에 비해 블록 I/O가 적게 발생하는 효과를 가진다.
    - 버퍼 Pinning으로 인해서 얻을 수 있는 효과인데 다음 인덱스 레코드를 읽었는데 '직전과 같은'테이블 블록을 가리키면 래치 획득과 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있다. -> 논리적인 Block I/O 과정 생략
    

인덱스 손익분기점

Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 '인덱스 손익분기점'이라고 부른다.

• 인덱스를 이용해서 테이블 액세스할 때는 전체 데이터중에 몇 건을 추출하는냐에 따라 성능이 크게 달라진다.
  • 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식
  • 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O 방식
• 일반적으로 인덱스 손익분기점은 보통 5~20%의 낮은 수준에서 결정되며 CF가 나쁘면 손익분기점은 5%미만에서 결정된다.
  • 읽어야 하는 데이터가 클 수록 손익분기점은 낮아진다.
    

상황별 튜닝 방식

• 온라인 프로그램 튜닝
  • 온라인 프로그램 튜닝은 대량의 데이터에서 소량을 읽는 것이 많기 때문에 인덱스를 활용하는 것이 중요하고 조인 방식도 인덱스 방식을 사용하는 NL조인을 사용하는 것이 유리하다.
• 배치 프로그램 튜닝
  • 배치 프로그램은 대량 데이터를 읽고 갱신해야하기 때문에 항상 전체범위 처리 기준으로 튜닝해야한다. 그래서 Full Scan과 해시 조인이 유리하다.
• 초대용량 테이블
  • 초대용량 테이블을 Full Scan하면 시간도 오래걸리고 시스템 부하도 심하다. 이럴때 사용하는 것이 파티션 활용 전략이고 병렬 처리까지 사용해서 좋은 효과를 낼 수 있다.
    • 테이블을 파티셔닝하는 이유도 결국 Full Scan을 빠르게 처리하기 위해서이다.

인덱스 컬럼 추가

테이블 액세스 최소화를 위해 가장 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가하는 것이다.

• 인덱스 컬럼 예시
  • select * from emp where deptno = 20 and sal >= 2000
  • EMP 테이블에 현재 PK 이외에 [DEPTNO + JOB]순으로 구성한 EMP_X01 인덱스
    • 사용되는 인덱스는 deptno만 있기 때문에 Job에 대한 모든 경우를 테이블 액세스한다.
  • EMP 테이블에 현재 PK 이외에 [DEPTNO + JOB + SAL]순으로 구성한 EMP_X01 인덱스
    • 사용되는 인덱스 deptno와 sal 모두 인덱스에 포함되기 때문에 한 경우만 테이블 액세스한다.
  • 인덱스 스캔량은 줄지 않지만, 테이블 랜덤 액세스 횟수를 줄여준다.
    

인덱스만 읽고 처리

테이블 랜덤 액세스가 아무리 많아도 필터 조건에 의해 버려지는 레코드가 거의 없는 경우 어떻게 처리할 것인가?

• 인덱스만 읽고 처리하는 쿼리를 Covered query라고하며 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세스가 아예 발생하지 않게하는 방법이다.
  • 추가해야하는 컬럼이 많아질 수록 실제 적용에 어려움이 존재한다.
• Include 인덱스
  • 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능이다. 테이블 랜덤 액세스를 줄이는 용도로 개발되었다.
  • create index emp_x02 on emp(deptno, sal)
    • 기존의 인덱스는 DEPTNO와 SAL 컬럼 모두 루트와 브랜치 블록에 저장해서 둘 다 수직적 탐색에 사용할 수 있다.
  • create index emp_x01 on emp (deptno) include (sal)
    • SAL 컬럼을 리프 블록에만 저장하기 때문에 수직적 탐색에는 DEPTNO만 사용하고 수평적 탐색에는 SAL 컬럼도 포함해 필터 조건으로 사용한다.

인덱스 구조 테이블

랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성

• 오라클에서는 이를 IOT(Index-Organized Table)이라고 지칭.
• 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있다.
  즉, IOT에서는 인덱스 리프 블록이 곧 데이터 블록이다.
  
  • 일반 테이블은 힙 구조 테이블이며 Freelist로부터 할당 받은 블록에 정해진 순서 없이 데이터를 입력한다. 반면, IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력한다.
• IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법중 하나이며 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있으므로 랜덤 액세스가 아닌 시퀀셜 방식으로 데이터를 액세스한다. 이 때문에 Between이나 부등호 조건으로 넓은 범위를 읽을 때 유리하다.

Q&A
1. 데이터를 입력할 때는 랜덤 방식을 사용한다 -> 힙 구조 자체가 정렬되어 있지 않나?...
2. 인덱스 리프 블록에 데이터를 저장하면 인덱스 관리 테이블이 너무 커지지않나??..

클러스터 테이블

클러스터 테이블에는 인덱스 클러스터와 해시 클러스터 두 가지가 있다.

인덱스 클러스터 테이블

• 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조이다. 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해서 클러스터 체인으로 연결한다.
  
  • 클러스터형 인덱스는 IOT에 가까운데 오라클 클러스터는 키 값이 같은 데이터를 같은 공간에 저장해두지만 IOT같은 클러스터형 인덱스처럼 정렬하지는 않는다.
• 클러스터 인덱스를 스캔하면서 값을 찾을 때는 랜덤 액세스가 값 하나당 한번 씩 밖에 발생하지 않고 클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 읽을 때 효율적이다.
  

인덱스 클러스터 테이블

• 해시 클러스터는 인덱스를 사용하지 않고 해시 알고리즘을 사용해서 클러스터를 찾아간다.
  

부분범위 처리 활용

부분범위 처리

DBMS는 클라이언트에게 데이터를 전송할 때 일정량씩 나누어 전송한다. 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아있어도 서버 프로세스는 클라이언트로부터 추가 Fetch Call을 받기 전까지 그대로 멈춰있는다.

-> OLTP 환경에서 대용량 데이터를 빠르게 핸들링할 수 있는 아주 중요한 원리

Array Size가 10인 상태에서 위 JAVA 프로그램이 데이터를 읽어 들이는 메커니즘

1. 최초 rs.next() 호출 시 Fetch Call을 통해 DB 서버로부터 전송받은 데이터 10건을 클라이언트 캐시에 저장한다.
2. 이후 rs.next() 호출할 때는 Fetch Call을 발생시키지 않고 캐시에서 데이터를 읽는다.
3. 캐시에 저장한 데이터를 모두 소진한 상태에서 rs.next() 호출 시 추가 Fetch Call을 통해 10건을 전송받는다.
4. 100건을 다 읽을 때까지 2~3번 과정을 반복한다.

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

ResultSet rs = stmt.executeQuery('select name from bit_table order by created')

• DB는 모든 데이터를 다 읽고나서 created 순으로 정렬을 마치고나서 클라이언트에게 데이터 전송을 시작할 수있다. -> 전체범위처리
• created 컬럼이 선두인 인덱스가 있다면 인덱스는 항상 정렬된 상태를 유지하므로 부분범위 처리가 가능하다.

ArraySize 조정을 통한 Fetch Call 최소화

• Array Size를 통해서 상황에 맞는 최적의 Fetch Call 횟수를 조절할 수 있다.

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

• OLTP는 Online Transaction Processing의 줄임말
• 인덱스의 순서를 활용해서 Sort Order By 연산을 생략함으로써 정렬 작업을 생략해서 앞쪽 일부데이터를 빠르게 보여줄 수 있는 성능개선 효과를 노릴수 있다.

  멈출 수 있어야 의미있는 부분범위 처리

  • 부분범위 처리의 핵심은 앞쪽 일부만 출력하고 멈출 수 있는가이다.
  • 클라이언트 - WEB - WAS - DB 구조인 n-Tier 아키텍처에서는 클라이언트가 특정 DB 커넥션을 독점할 수 없다. 그래서 SQL 조회 결과를 클라이언트에게 모두 전송하고 커서를 닫아야한다. -> SQL 결과집합을 조금씩 나눠서 전송하도록 구현하기 어렵다.

배치 I/O

• 디스크 랜덤 I/O 성능을 높이기 위해 오라클의 배치 I/O는 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안한 기능이다.
  • 테이블 블록에 대한 디스크 I/O Call을 미웠다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리한다.
  • 배치 I/O 기능이 작동하면 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다를 수 있다는 사실에 주목해야 한다.
    • 소트 생략이 가능한 인덱스를 사용하더라도 배치 I/O 기능이 작동하면 데이터 정렬 순서를 보장 할 수 없기 때문에 주의해야한다.

  인덱스로 소트 연산을 생략할 수 없거나 SQL에 ORDER BY가 없으면, 랜덤 I/O 성능을 향상하는 BATCH I/O를 사용하는것은 효과적이다.