[DB] 3장. 인덱스 튜닝(1)

1. 테이블 랜덤 액세스 최소화

💫들어가기 전에 
- SQL 튜닝은 랜덤I/O와의 전쟁이며, 랜덤 I/O는 중요하다.
- 데이터베이스는 읽어야할 데이터량이 많으면 그 과정에 수많은 디스크 I/O가 발생하기 때문에 느리며, 이를 극복하기 위해 랜덤 I/O 최소화가 중요하다. (ex. IOT, 클러스터, 파티션, 테이블 Prefectch, Batch I/O 등등)
- 조인 메소드 발전은 랜덤I/O 최소화에 맞춰져 있다.
(조인메서드 중 NL조인이 대량 데이터를 조인할 때 느린 이유는 랜덤 I/O때문이며, 이를 극복하기 위해 소트머지 조인, 해시 조인이 개발된다.)

---

인덱스를 이용해도 조건절에 해당하는 데이터만 읽는데 왜 파티션 Pruning(조건절에 해당하는 파티션만 읽는 기능)이 필요할까?

- 인덱스만 사용하면 모든 파티션을 탐색할 수도 있지만, 파티션 프루닝을 적용하면 애초에 검색 대상 파티션 자체를 줄일 수 있어 성능이 훨씬 향상된다. 따라서 인덱스와 파티션 프루닝을 함께 활용하면 최적의 성능을 낼 수 있다.

---

🔎 테이블 랜덤 액세스 

select * from 고객 where 지역 = '서울';

Execution Plan
----------------------------------------------------------------
0      SELECT STATMENT Optimizer=ALL_ROWS
1   0	 TABLE ACCESS BY INDEX ROWID OF '고객' (TABLE)
2   1	   INDEX RANGE SCAN OF '고객_지역_IDX'	(INDEX)

 

• TABLE ACCESS BY INDEX ROWID ( ROWID를 찾아 검색 ) → 인덱스를 이용해 테이블을 액세스한다.
• 인덱스를 스캔하는 이유 : ROWID( 테이블 레코드를 찾아가기 위한 주소값)를 얻기위함이다.

※ 인덱스 ROWID는 물리적 주소일까? 논리적 주소일까?
물리적 주소라고 생각한다면 데이터파일번호, 오브젝트 번호, 블록번호 같은 물리적 요소로 구성되어 있기 때문이다.
물리적 주소라고 말해도 틀리다고 말할 수 없지만, 인덱스 ROWID는 물리적주소보다 논리적 주소에 가깝다.
물리적으로 직접 연결되지 않고, 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고 있기 때문이다.

🔎 인덱스 ROWID

• 포인터가 아니다.
• 논리적 주소이며, 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다.

🔎 메인 메모리 DB와 비교 

• 메인 메모리 DB (MMDB)
  
  • 데이터를 모두 메모리에 로드해 놓고, 메모리를 통해서만 I/O를 수행하는 DB
  • 데이터 볼륨이 크지 않으면서, 기존 디스크 DB로는 도저히 만족할 수 없을 정도의 빠른 트랜잭션 처리가 요구되는 업무에 제한적으로 사용
• 메인메모리 DB는 ROWID 대신 메모리 주소를 활용하여 즉시 데이터에 접근하므로, 디스크 기반DB보다 훨씬 빠르게 조회할 수 있다.

💫 I/O 메커니즘 

- DBA(데이터파일번호 + 블록번호) :  디스크 상에서 블록을 찾기 위한 주소 정보

- 블록을 읽을 때 디스크로 가지 않고 버퍼 캐시를 활용한다.
- DBA를 통해 해시 함수에 입력해서 해시 체인을 찾고 버퍼 헤더를 찾는다.
- 해싱 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 찾는다.
- 데이터를 버퍼캐시에서 먼저 찾고 없을때만 디스크에서 블록을 읽는다.
- 모든 데이터가 캐싱되어도 매번 DBA 해싱과 래치 획득 과정을 거쳐야 되고 lock까지 고려한다면 인덱스 ROWID 를 이용한 테이블 액세스는 고비용 구조이다.

🔎 인덱스 클러스터링 팩터 ( Clustering Factor 'CF' )

• 인덱스 클러스터링 팩터 (군집성 계수)
• 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도 
• CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋음→ 테이블 액세스량에 비해 블록 I/O가 적게 발생한다는 것을 의미한다.
• CF가 안좋은 경우 테이블 액세스 하는 횟수만큼 블록 I/O 가 발생

 

※ 인덱스 레코드마다 테이블 레코드를 건건이 블록 단위로 I/O 한다면, CF가 다르더라도 블록 I/O 발생량에 별차이 없어야 하지 않나?

- 인덱스 ROWID로 테이블을 액세스 할 때, 오라클은 래치 회득과 해시 체인 스캔 과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터를 바로 해제하지 않고 일단 유지한다. = 버퍼 Pinning

이 상태에서 다음 인덱스 레코드를 읽었는데, 마침 '직전과 같은' 테이블 블록을 가리킨다면 래치 획득과 해시 체인 스캔 과정을 생략하고 테이블 블록을 읽을 수 있다. (논리적인 블록 I/O 과정을 생략할 수 있는 것이다.)

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

• 굵은 실선은 실제 블록 I/O가 발생하는 경우
• 가는 점선은 블록을 찾아가는 과정없이 포인터로 바로 액세스하는 경우
• 버퍼 Pinning 효과를 극대화하기 위해 인덱스 레코드와 테이블 레코드의 정렬순서가 100% 일치할 필요는 없다. 다음에 읽을 테이블 블록과 직전에 읽은 테이블 블록의 주소가 같기만 하면 된다.

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

클러스터링 팩터가 좋은 인덱스를 이용하면, 테이블 액세스량에 비해 블록 I/O가 적게 발생

🔎 인덱스 손익분기점 

• Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점
• 보통 5~20%의 낮은 수준에서 결정
• 손익분기점은 인덱스 CF에 따라 달라짐

	Table Full Scan	인덱스 ROWID를 이용한 테이블 액세스
성능	추출건수와 상관 없이 성능 일정	랜덤 액세스 때문에 추출 건수가 많을 수록 느려짐
방식	시퀀셜 액세스 방식	랜덤 액세스 방식
	Multiblock I/O	Single Block I/O
인덱스를 이용한 테이블 액세스가 Table Full Scan보다 더 느린 이유는 방식의 차이

 

 

 

• CF에 따라 달라지는 이유는 인덱스 CF가 나쁘면 같은 테이블 블록을 여러번 반복 액세스하면서 논리적 I/O 횟수가 늘고, 물리적 I/O 횟수도 늘기 때문이다.
• CF가 나쁘면 손익분기점이 5%미만에서 결정되며, 심할때(BCHP이 매우 안좋을 때)는 1%미만으로 낮아진다.
• CF가 좋을때(인위적으로 전체 데이터를 인덱스 컬럼 순으로 정렬해서 재입력했을 때)는 손익분기점이 90%까지도 올라간다.
• 테이블 스캔이 항상 나쁜 것은 아니며, 인덱스 스캔이 항상 좋은 것도 아니다.

※ 인덱스 손익분기점과 버퍼캐시 히트율

- 버퍼캐시는 요즘 기준으로 보통 수백만개 블록을 캐싱하는 수준이다. 따라서 특정 인덱스로 100만 건 이상 액세스한다면 캐시 히트율은 극히 낮을 수 밖에 없다.
- 1000만 건 정도 테이블이면 인덱스 컬럼 기준으로 값이 같은 테이블 레코드가 근처에 모여 있을 가능성이 매우 작다.
- 인덱스를 스캔하면서 읽은 테이블 블록을 뒤에서 다시 읽을 가능성이 작기 때문에 거의 모든 데이터를 디스크에서 읽게 된다.
- 이런 상황이면 손익분기점 자체가 의미 없어진다. 만 건만 넘어도 Table Full Scan 방식으로 읽는 게 빠를 수 있다.

🔎 온라인 프로그램 튜닝 vs 배치(Batch) 프로그램 튜닝 

온라인 프로그램	배치프로그램
소량 데이터를 읽고 갱신	대량 데이터를 읽고 갱신
인덱스와 NL조인방식 사용	Full Scan과 해시 조인방식 사용
인덱스를 이용한 소트 연산 생략으로 성능 높임	파티션 활용 전략, 병렬 처리가 중요한 튜닝 요소

🔎 인덱스 컬럼 추가  

• 테이블 액세스 최소화를 위해 주로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가하는 것
• 기존 인덱스에 인덱스 컬럼을 추가하면 테이블 랜덤 액세스 횟수가 줄어든다. 단, 인덱스 스캔량은 줄지 않음
• 인덱스에 컬럼을 추가하다보면 인덱스 관리 비용이 증가하고, DML부하에 따른 트랜잭션 성능 저하가 생길 수 있다.

🔎 Covered 

쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세스가 아예 발생하지 않게 했을 때, 인덱스만 읽어서 처리하는 쿼리이며, 효과는 매우 좋지만, 추가해야 할 컬럼이 많아 실제 적용하기 곤란한 경우도 많다.

• 비효율이 없더라도 인덱스 스캔과정에서 얻은 데이터가 많다면 테이블 랜덤 액세스가 많이 발생하므로 성능이 느릴 수 밖에 없기에 성능 개선을 해야 한다면 Covered 사용함.

※ 비효율이 없다 : 테이블 랜덤 액세스가 많아도 필터 조건에 의해 버려지는 레코드가 없을 경우 

🔎 Include 

• 인덱스 키외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능 
• 테이블 랜덤 액세스를 줄이는 용도로 개발됨

//include 인덱스
create index emp_x01 on emp (deptno) include (sal);

//일반 인덱스
create index emp_x02 on emp (deptno, sal);

 

※  include 인덱스와 일반 인덱스 차이

include 인덱스	일반 인덱스
인덱스는 sal 컬럼을 리프 블록에만 저장	인덱스는 deptno, sal 컬럼 모두 루트와 브랜치 블록에 저장
- deptno 컬럼은 수직적 탐색으로 사용 가능 - sal 컬럼은 수평적 탐색에서 사용 가능, 테이블 랜덤 액세스 횟수를 줄이는 용도로 사용  - 소트 연산을 생략 불가능	- 수직적 탐색에서 사용가능  - 소트 연산을 생략가능
Covered 인덱스 이므로 테이블 랜덤 액세스 생략 가능

🔎 인덱스 구조 테이블 

• IOT(Index Organized Table) or 클러스터형 인덱스 (오라클에서는 IOT로, MS-SQL에는 클러스터형 인덱스라고 불림)
• 랜덤 액세스가 발생하지 않도록 테이블을 인덱스 구조로 생성하는 것

인덱스 구조 테이블	일반 테이블과 인덱스
테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장( 해당 자리에 있는 테이블 데이터를 가짐) 인덱스 리프 블록 ==  데이터 블록	테이블을 찾아가기 위한 ROWID를 갖는 일반 인덱스
정렬 상태를 유지하며 입력	'힙 구조 테이블'로 순서 없이 데이터를 입력 - 랜덤방식

 

• IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나
• 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있으므로 시퀀셜 방식으로 데이터를 액세스 함.

//테이블을 인덱스 구조로 만드는 구문
CREATE TABLE <테이블명> (
    <컬럼1>,
    <컬럼2>,
    PRIMARY KEY (<컬럼1> or <컬럼2>)
)
ORGANIZATION INDEX;

 

🔎 클러스터 테이블 ( 인덱스 클러스터, 해시 클러스터 )

1. 인덱스 클러스터 테이블

• 클러스터 키값이 같은 레코드를 한 블록에 모아서 저장하는 구조 
• 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해서 클러스터 체인으로 연결한다.
  • 다중 테이블 클러스터 : 여러 테이블 레코드를 같은 블록에 저장하는 것 
• 클러스터에 도달해서 시퀄셜 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는 것이 핵심 
• 클러스터 인덱스는 B+Tree 인덱스 구조를 사용 → 테이블 레코드를 하나씩 가리키지 않고, 해당 키값을 저장하는 첫 번째 데이터 블록을 가르킨다는 점이 차이
• 일반 테이블에 생성한 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 가지지만, 클러스터 인덱스는 1:M관계를 가진다.
• 클러스터 인덱스의 키값은 항상 Uniqure하다. (중복값이 없다)

1. 클러스터 생성
create cluster c_deptno (deptno number(2)) index;

2. 클러스터 인덱스 생성
create index c_deptno_idx on cluster c_deptno;

3. 클러스터 테이블 생성
create table dept (
  deptno   number(2)    not null,
  name     varchar2(20) not null,
  location varchar2(14))
cluster c_deptno(deptno);

 

2. 해시 클러스터 테이블 

• 인덱스를 사용하지 않고, 해시 알고리즘을 사용하여 클러스터를 찾아가는 것

1. 클러스터 생성
create cluster c_deptno (deptno number(2)) hashkeys 4;

2. 클러스터 테이블 생성
create table dept (
  deptno   number(2)    not null,
  name     varchar2(20) not null,
  location varchar2(14))
cluster c_deptno(deptno);

---

2. 부분범위 처리 활용

테이블 랜덤 액세스로 인한 인덱스 손익 분기점의 한계를 극복할 부분처리 원리를 설명해보자.

🔎 부분범위 처리

• 전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것
• DBMS가 클라이언트에게 데이터를 전송할 때도 일정량씩 나누어 전송하고, 서버 프로세스는 클라이언트로부터 추가Fetch Call을 받기 전까지 그대로 멈춰 서서 기다린다.
  
  

DB 클라이언트가 DB서버로부터 데이터 Fetch하는 과정

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

- 전체범위처리 : DB 서버는 모든 데이터를 다 읽어 created 순으로 정렬을 마치고서야 클라이언트에게 데이터 전송을 시작할 수 있다.

- Sort Area와 Temp 테이블스페이스까지 이용해 데이터 정렬을 마치고 나면 그때부터 일정량씩 나눠 클라이언트에게 데이터를 전송한다.

- 만약 created 컬럼이 선두인 인덱스가 있으면, 부분범위 처리가 가능하다.

- 인덱스는 항상 정렬된 상태를 유지하므로 전체 데이터를 정렬하지 않고도 정렬된 상태의 결과집합을 바로 전송할 수 있기 때문이다.

🔎 Array Size 조정을 통한 Fetch Call 최소화 

• 부분범위 처리 원리가 있으니, 전송해야 할 데이터량에 따라 Array Size를 조절할 필요가 있다.
• 대량 데이터를 파일로 내려받는다면, 가급적 값을 크게 설정한다.
• 반대로 앞쪽 일부 데이터만 Fetch하다 멈추는 프로그램이라면 Array Size를 작게 설정하는 것이 유리하다. 불필요하게 많은 데이터를 전송하고 버리는 비효율을 줄일 수 있기 때문이다.
• Array Size를 조정한다고 해서 전송해야 할 총량이 변하진 않지만, Fetch Call 횟수를 그만큼 줄일 수 있다.

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

• Online Transaction Processing OLTP : 온라인 트랜잭션을 처리하는 시스템
• 온라인 트랜잭션 : 소량 데이터를 읽고 갱신

OLTP환경에서는 소량의 데이터를 읽고 갱신한다고 했지만, 수천수만 건을 조회하는 경우도 있다.
다만, 업무 특성상 모든 데이터를 읽지 않고, 특정 몇개 상위 데이터만 확인하는 경우가 많다.
이때, 항상 정렬 상태를 유지하는 인덱스를 이용하면, 정렬 작업을 생략하고, 앞쪽 일부 데이터를 빠르게 보여줄 수 있다.
인덱스와 부분범위 처리 원리를 잘 활용하면 OLTP 환경에서 극적인 성능개선 효과를 얻을 수 있다.

SELECT 게시글 ID, 제목, 작성자, 등록일시
FROM 게시판
WHERE 게시판구분코드 = 'A'
ORDER BY 등록일시 desc

▷ 인덱스 선두 칼럼을 [게시판구분코드 + 등록일시] 순으로 구성한다면, Sort Order By 연산을 생략할 수 있기 때문에 게시판구분코드 = 'A' 조건을 만족하는 전체 로우를 읽지 않고도 바로 결과집합을 일부 출력할 수 있다.

🔎 배치 I/O

• 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안한 기능
• 인덱스를 이용해 테이블을 액세스하다가 버퍼 캐시에서 블록을 찾지 못하면 테이블 블록에 대한 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리한다.
• 오라클의 배치 I/O 기능은 읽는 블록마다 건건이 I/O Call을 발생시키는 것이 아니라, 버퍼 캐시에서 블록을 찾기 못하면 읽을 블록이 일정량 쌓이면 한꺼번에 처리한다. 11g에서 NL 조인 Inner 테이블 엑세스 때만 작동했지만 12c부터는 인덱스 ROWID로 테이블을 엑세스하는 어떤 부분에서든 기능이 작동할 수 있다.

---

 

다음 장에서 인덱스 스캔 효율화와 인덱스 설계에 대해 추가로 설명하겠다.