[SQLP 친절한 SQL 튜닝 정리] 3장- 인덱스 튜닝(테이블 엑세스 최소화)

SQLP를 준비하며 "친절한 sql 튜닝" 책 3장 인덱스 튜닝 부분을 정리한 내용 입니다.
친절한SQL튜닝 도서

3장-인덱스 튜닝

 

3.1 테이블 엑세스 최소화

 

3.1.1 테이블 랜덤 엑세스

 

인덱스의 테이블 엑세스

• 조건절에 인덱스가 모두 포함되는 경우가 아니라면, 인덱스 스캔 후 반드시 테이블을 엑세스 한다.
• INDEX RANGE SCAN 후 ROWID로 TABLE ACCESS

 

인덱스 ROWID는 논리적 주소

• 인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 테이블 레코드를 찾아가기 위한 주소값(ROWID)를 얻으려는데 있다.
• 인덱스 ROWID는 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담을 뿐, 테이블 레코드와 물리적으로 직접 연결된 구조는 아니다.

메인 메모리 DB와 비교

• 메인 메모리 DB는 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB
• 오라클은 테이블 블록이 수시로 버퍼캐시에서 밀려 났다 다시 캐싱되며, 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 포인터로 직접 연결할 수 없는 구조. 메모리 주소 정보(포인터)가 아닌 디스크 주소정보(DBA)를 이용 해 해시 알고리즘으로 버퍼블록을 찾는다.

인덱스 ROW 현실 객체와 비교

• 디스크 DB(오라클)
  • 우편주소 : 봉투에 적힌 주소로 우체부 아저씨가 일일이 전달
• 메모리 DB
  • 전화번호 : 물리적으로 연결된 통신망을 이용 해 곧바로 상대방과 통화

 

3.1.2 인덱스 클러스터링  팩터

• 클러스터링 팩터(Clustering Factor, 이하 CF)는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미
• CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋다
  • 분가한 열명의 자녀가 모두 한 동네에 모여 살면, 부모가 자녀들 집에 모두 방문하는데 하루면 충분
• 테이블 엑세스량에 비해 블록I/O가 적으면 클러스터링 팩토가 좋고, 테이블 엑세스량 만큼 블록 I/O가 발생하면 클러스터링 팩터가 매우 안좋은 상태

클러스터링 도식화

• 첫번째 사진은 클러스터링 팩터가 좋은 경우, 두번째는 나쁜 경우

 

인덱스 클러스터링 팩터 효과

클러스터링 팩터

• 인덱스 ROWID로 테이블을 엑세스 할 때, 오라클은 찾아간 테이블 블록에 대한 포인터(메모리 주소밗)을 바로 해제하지 않고 일단 유지(버퍼 Pinnung)
• 이 상태에서 다음 인덱스 레코드를 읽었을때, '직전과 같은'테이블 블록이라면 래치 획득, 해시 체인 스캔 과정을 생략하고 바로 테이블을 읽을 수 있다.(논리적 I/O 과정 생략)
• 위 그림에서 블록 I/O가 적게 발생하는 원리
  • 굵은 실선 : 실제 블록 I/O 발생
  • 가는 점선 : 포인터로 바로 엑세스 하는 경우
  • 다음에 읽을 테이블 블록과 직전 읽은 테이블 블록의 주소가 같기 때문에 블록 I/O를 생략 할 수 있음.

3.1.3 인덱스 손익분기점

인덱스 손익 분기점

• Index Range Scan에 의한 테이블 엑세스가 Table Full Scan보다 느려지는 지점
• Table Full Scan은 성능이 항상 일정하나 인덱스를 이용해 테이블 엑세스를 할 때는 천제 건 중 몇건을 추출하느냐에 따라 성능이 크게 달라진다.
• 인덱스를 이용한 테이블 엑세스가 Table Full Scan보다 더 느려지게 만드는 가장 핵심적인 요인
  • Table Full Scan은 시퀀셜 엑세스인 반면, 인덱스 ROWID를 이용한 테이블 엑세스는 랜덤 방식
  • Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 엑세스는 Single Block I/O 방식.

CF에 따른 손익 분기점

• 인덱스 CF가 나쁘면 테이블 블록을 여러번 반복 엑세스 하면서 논리적/물리적 I/O 횟수가 증가 하기 때문에 손익분기점이 낮아진다.
• 인덱스 CF가 아주 좋을 때는 손익분기점이 높아진다.

 

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

 

온라인 프로그램

• 보통 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 중요
• 인덱스를 이용해 소트 연산을 생략 해 부분범위 처리 방식으로 구현할 수 있다면, 온라인 환경에서 대량 데이터를 조회할 때도 빠른 응답 속도를 낼 수 있음

배치 프로그램

• 대량 데이터를 읽고 갱신하는 배치 프로그램은 항상 전체범위 처리 기준으로 튜닝
• 대량 데이터를 빠르게 처리하려면, 인덱스와 NL 조인보다 Full Scan과 해시 조인이 유리

인덱스 컬럼 추가를 통한 튜닝 사례

SELECT
	렌탈관리번호, 고객명, 서비스관리번호, 서비스번호, 예약접수일시
    , 방문국가코드, 방문국가코드2, 방문국가코드3, 로밍승인번호, 자동로밍여부
FROM 로밍렌탈
WHERE 서비스번호 LIKE '010%'
AND 사용여부 = 'Y'

• 로밍렌탈_N2 = [서비스번호] 인덱스 구성
• 인덱스를 스캔 하고 얻은 건수(ROWS)는 266,476으로 그 건수만큼 테이블을 랜덤 엑세스 한다.
• 그 단계에서 읽은 블록수는 255,957(266,968-1,011)으로 전체 블록 I/O에 99.66%차지하고 있음
• 테이블을 총 266,476 방문 했지만, 최종 결과 집합은 1,909건으로 사용여부='Y'컬럼에서 대부분 필터링 되고 있음을 볼 수 있다.

• 로밍렌탈_N2=[서비스번호 + 사용여부] 인덱스 구성
• 로밍렌탈_N2 인덱스에 사용여부 인덱스를 추가하게 되면 인덱스를 거쳐 테이블을 1,909  방문 했고, 모두 결과 집합에 포함되었다.
• 인덱스 컬럼 추가를 통해 불필요한 테이블 엑세스를  줄여 총 블록 I/O도 2,902개로 줄였다.

 

3.1.5 인덱스만 읽고 처리

 

쿼리나 인덱스에 문제가 있어서가 아니라 절대 일량이 많아서 느린 경우에는 어쩔 수 없지만,

쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 엑세스가 아예 발생하지 않게 하는 방법을 고려해볼 수 있다.

 

Coverd 쿼리

• 인덱스만 읽어서 처리하는 쿼리이며, 쿼리에 사용한 인덱스를 Covered 인덱스라고 부른다.
• 테이블 엑세스를 하지 않기 때문에 성능이 획기적으로 좋아지지만, 추가해야 할 컬럼이 많아져 실제 적용하기 어려운 부분이 있다.

Include 인덱스

• SQL server 2005버전에 추가 된 기능(Oracle은 없음)
• 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능.
  • 리프블록에만 저장 해 수평적 탐색에만 사용 된다.
  • 테이블 랜덤 엑세스를 줄이는 용도로만 사용

 

3.1.6 인덱스 구조 테이블

• 랜덤엑세스가 발생하지 않도록 테이블을 인덱스 구조로 생성 하는것
• 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장
• 오라클은 IOT(Index-Organized Table), MS-SQL Server 는 클러스터형 인덱스 라고 부름
• 일반 테이블과 차이
  • 일반 테이블
    • 힙 구조 테이블로 데이터 입력 시 랜덤 방식 사용
    • Freelist로 부터 할당 받은 블록에 정해진 순서 없이 데이터를 입력
  • 인덱스 구조 테이블
    • 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력
    • 인위적으로 클러스터링 팩터가 좋게 만드는 방법 중 하나

 

3.1.7 클러스터 테이블

인덱스 클러스터 테이블

인덱스 클러스터 테이블

• 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조
• 한 블록에 모두 담을 수 없을 때, 새로운 블록을 할당 해 클러스터 체인으로 연결 된다.
• 여러 테이블 레코드를 같은 블록에 저장할 수도 있음(다중 테이블 클러스터)
• 키 값이 같은 데이터를 같은 공간에 저장하기 때문에 IOT에 가까움
• 테이블 레코드와 1:M 관계를 갖는다. 그렇기 때문에 키 값은 항상 Unique 하다.
• 클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓음 범위를 읽더라도 비효율이 없다.