[sql 튜닝] 인덱스 튜닝 - 3.1. 테이블 엑세스 최소화

3.1. 테이블 엑세스 최소화

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

• 

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

  • 물론 데이터 파일 번호, 로우 번호 등 물리적인 내용을 담고는 있지만, 물리적으로 직접 연결되지는 않고 실제 테이블에 접근하기 위한 논리적 주소를 저장하고 있기 때문이다.
  • 포인터와 같은 역할은 하지만, 실제 포인터와는 다르고 정보를 갖고 데이터에 접근하는데 시간이 걸린다. (물리적으로 연결된 것이 아님. )

• 메인 메모리 DB vs 잘 캐싱된 DB

  • 데이터를 모두 메모리에 올려두고 I/O 작업을 수행하는 메인 메모리 DB (MMDB)
  • OLTP 성 DB는 캐시 히트율이 99%이기 때문에 디스크대신 거의 메모리에서 데이터를 읽음.
  • 그런데도 둘의 성능의 차이가 꽤 크다. (MMDB 가 빠름).
  • 그 이유는 MMDB 는 인덱스 생성 시, 메모리의 실제 주소를 저장하는 반면 오라클 DB 는 주소에 접근하기 위한 데이터를 저장하기 때문이다. (DBA 에서 해시 알고리즘을 이용해서 정보를 찾아감.)

• I/O 복습

  • DBA(데이터 파일 번호 + 블록 번호) 디스크로 가기전에, 버퍼 캐시에서 먼저 찾아본다.
  • 버퍼 캐시에서 찾는법
    • 해시함수를 통해 버퍼헤더에 접근, 버퍼 블록을 찾아감.
      
  • DBA 를 얻는 법
    • 인덱스 스캔: 리프노드에 저장된 ROWID 분해
    • 테이블 풀 스캔: 익스텐트 맵에 저장된 정보
    • 이를 토대로 버퍼 해시 체인 -> 버퍼 블록에 접근하여 실제 메모리 주소를 얻음.
  • 이처럼 데이터가 캐시되어있다고 해도, 테이블 레코드를 찾기 위해 DBA 해싱과 래치 획득과정을 반복해야 함.
    • 동시 접근이 심할때는, 캐시 버퍼 체인 래치와 버퍼 Lock 에 대한 경합까지 발생한다.
  • 즉 ROWID 를 이용한 테이블 엑세스는 비용이 꽤 많이 든다.

3.1.2. 인덱스 클러스터링 팩터 (Clustering Factor, CF)
- 칼럼에 대해 같은 값이 얼마나 물리적으로 근접해있는지 나타내는 인자.
- CF 값이 큰 칼럼을 인덱스로 만들 경우, 성능향상을 기대할 수 있다.
- index 도 결국 논리적 I/O 를 매번 실행한다는데 왜?
- "buffer pinning" : 실제 메모리 주소를 얻게 되면, 주소를 들고 있는다. 다음 접근한 테이블 레코드 주소가 이전과 같다면, 들고 있던 주소에 바로 접근할 수 있기 때문에 논리적 I/O 과정을 생략할 수 있다.

• 3.1.3. 인덱스 손익 분기점

  • 테이블 full scan vs Index Range Scan
    • Multi Block IO vs Single Block IO
    • 순차적인 접근 vs 랜덤 접근
  • 이러한 차이점으로 인해서, Index Range Scan 이 Table Full Scan보다 성능이 좋은 시점이 존재한다.
    • 보통은 전체 데이터 건의 5~20%에 해당하지만 데이터 양이 많을 수록 Table Full Scan 이 유리하다.
    • CF 값이 높을 수록 손익 분기점의 위치가 많이 증가한다. (랜덤 I/O 가 감소하기 때문.)

• 온라인 프로그램 (온라인 트랜잭션 활용) vs 배치 프로그램

  • 소량의 데이터를 읽고 갱신하기 vs 대량 데이터 읽고 갱신하기
  • 인덱스 활용 vs table full scan
  • NL 조인 vs hash join (mysql, mariadb 지원 x)
  • 특히 대량의 데이터인 경우 파티셔닝까지 한 후 풀 스캔이 좋을 수도 있다.

3.1.4. 인덱스 칼럼 추가

• 생각보다 인덱스 자체의 추가는 비용이 많이 든다.

  • DML 시의 트랜잭션 성능이 저하될 가능성이 존재한다.
  • 인덱스를 관리하는 비용이 과부화된다.

• 인덱스 칼럼을 추가하는 것의 효과

  • 직접 테이블 레코드에 접근하는 랜덤 IO 과정이 줄어들 수 있다

  • DEPNO + JOB 인덱스

  • 

  • DEPTNO + JOB + SAL 인덱스로 SAL 추가 된 경우

  • 

  • 6번에서 1번으로 테이블 엑세스 횟수가 감소.

3.1.5. 인덱스만 읽고 처리
- 칼럼 전체를 인덱스의 구성으로 넣어서, 아예 테이블 엑세스 횟수를 최소화하는 방법도 있다.
- 단 칼럼 수가 너무 많지 않아야 구현 가능할 것.

- Include 인덱스 : SQL Server 에서 지원. 

3.1.6. 인덱스 구조 테이블

• 테이블 구조 자체가 인덱스가 되는 것.
• 기존에서는 리프 노드에 ROWID 가 저장되었던 것과 다르게, 데이터 블록이 된다.
• 테이블을 인덱스 구조로 만드는 구문
  • 보통의 테이블은 'organization heap'이다.
  • 

3.1.7. 클러스터 테이블

• 인덱스 클러스터와 해시 클러스터 두 가지가 있다.

• 인덱스 클러스터

  • 클러스터 키 값이 같은 데이터를 한 블록에 모아서 저장하는 구조. 블록이 가득차면 블록을 새로 할당해서 클러스터 체인으로 연결한다.

  • 

  • (특이하기 블록 안에 여러 테이블의 레코드가 들어갈 수도 있다.)

  • 인덱스 클러스터를 만드는 방법

    1. 클러스터 생성

    2. 클러스터 인덱스 정의 (데이터 저장시에도 필요하기 때문에 정의가 선제되어야 한다.)

    3. 테이블 생성

           create table dept ( deptno number(2) not null , dname varchar2(14) not null , loc varchar2(13) ) cluster c_dept#( deptno ); )

  • 클러스터 키로 묶인 블록의 첫번째 데이터 블록에 대해서만 인덱스가 주소를 저장하고 있다.

    • 따라서 같은 클러스터 값에 대해서는 딱 한번의 랜덤 I/O 만 발생하게 된다.
      

  • 해시 클러스터 테이블

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

  • 구성 방법

    1. 클러스터 생성

           create cluster c_dept# ( deptno number (2) ) hashkeys 4 ;

    2. 클러스터 테이블 생성

       create table dept (
       deptno number(2) not null
       , dname varchar2 (14) n ot null
       , loc varchar2 (13 ))
       cluster c_dept#( deptn o );

    • 해시 클러스터 조회 시 실행 계획