친절한 SQL 튜닝 - 3.1 테이블 액세스 최소화

---

3.1.1 테이블 랜덤 액세스

SQL 튜닝은 랜덤 IO와의 전쟁

인덱스를 스캔하는 이유 : 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값 즉, ROWID 를 얻기 위하여

 

인덱스 ROWID = 물리적 주소보다 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고있음

 

인덱스 = 도서 색인

인덱스 ROWID = 도서 색인에 기록된 페이지 번호

결론 : 인덱스 ROWID = 논리적 주소 (디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보)

포인터가 아니다!

 

메인메모리 DB란?

데이터를 메모리에 모두 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB

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

매번 디스크 상에서 블록을 읽을수 는 없다. 그러므로, IO 성능을 높이려면 버퍼캐시를 활용한다.

 

디스크 DB (Oracle, SQL Server와 같은 일반 DBMS)가 사용하는 ROWID는 우편주소, 메인메모리 DB가 사용하는 포인터는 전화번호에 비유

---

3.1.2 인덱스 클러스터링 팩터

클러스터링 팩터 (CF)는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다.

CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋음 (= 테이블 액세스량에 비해 블록 IO가 적게 발생함을 의미)

---

3.1.3 인덱스 손익분기점

인덱스 ROWID를 이용한 테이블 액세스는 고비용 구조이다.

 

인덱스 손익분기점 : Index Range Scan 속도가 Table Full Scan 보다 느려지는 지점

 

인덱스를 이용한 테이블 액세스가 Table Full Scan보다 더 느려지는 두가지 이유

1. Table Full Scan은 Sequential Access, 인덱스 ROWID를 이용한 테이블 액세스는 Random Access

2. Table Full Scan은 Multiblock I/O, 인덱스 ROWID를 이용한 테이블 액세스는 Singleblock I/O

---

3.1.4 인덱스 컬럼 추가

테이블 액세스 최소화를 위한 일반적인 튜닝 기법은 인덱스에 컬럼을 추가하는 것

 

[인덱스 : deptno+ job]

select * from emp where deptno=30 and sal>=2000

위 조건을 만족하는 사원은 한명인데, 이를 찾기 위해 테이블을 n번 액세스하였다.

인덱스 구성을 deptno+sal로 변경하면 좋겠지만, 다른 쿼리에서 이미 해당 인덱스를 사용하고 있을 경우 바꾸기 쉽지않다.

=> 이럴 경우, 기존 인덱스에 sal 컬럼을 추가하여 deptno+job+sal로 만들면 좋다.

---

3.1.5 인덱스만 읽고 처리

테이블 랜덤 액세스가 아무리 많아도 필터 조건에 의해 버려지는 레코드가 거의 없다면? : 비효율적이지 않다!

 

[인덱스 : 부서번호]

select 부서번호, sum(수량)
from 판매집계
where 부서번호 like '12%'
group by 부서번호;

=> like 조건에 해당하는 데이터를 찾고 테이블을 액세스 한 이후 버리는 데이터가 없다.

=> 비효율은 없더라도 인덱스 스캔 과정에서 얻은 데이터가 많다면 그만큼 테이블 랜덤 액세스도 많이 발생하므로 성능 느림

이럴경우 Covered 인덱스

쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세스가 발생하지 않게끔 즉, 인덱스만 읽어서 처리하는 쿼리

 

예시에서 인덱스를 부서번호 + 수량으로 구성한다면 성능이 획기적으로 좋아짐

만약 추가해야할 컬럼이 많다면 실제로 적용하기 곤란

 

<MS SQL에서는 include 인덱스를 사용>

create index emp_x01 on emp (deptno) include (sal)
//sal 컬럼을 리프 블록에만 저장
//수직적 탐색에서는 deptno만 사용, 수평적 탐색에서는 sal도 필터 조건으로 사용 가능


create index emp_x02 on emp(deptno, sal)
//deptno와 sal 모두 루트와 브랜치 블록에 저장
//둘 다 수직적 탐색에 사용가능


select sal from emp where deptno=20
// 이럴 경우 emp_x01, emp_x02 둘다 covered index 이므로 테이블 액세스생략 가능

select * from emp where deptno=20 ordery by sal
//emp_x02는 소트 연산 생략 가능, emp_x01은 소트 연산 생략 불가
//include 인덱스는 순전히 테이블 랜덤 액세스를 줄이는 용도로만 개발

---

3.1.6 인덱스 구조 테이블

랜덤 액세스가 발생하지 않게끔 만드는 테이블

 

Oracle : IOT (Index-Organized Table)

MS-SQL : Clustered Index

 

일반적으로 이야기하는 테이블은 힙 구조 테이블이다. 일반 힙 구조 테이블에 데이터를 입력할 때는 랜덤 방식을 사용한다.

즉, Freelist로부터 할당 받은 블록에 정해진 순서 없이 데이터를 입력한다. 반면, IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력한다.

 

create
table index_org_t ( a number, b varchar(10), constraint index_org_t_pk_primary
key(a))

organization
index;

//인덱스 구조로 테이블 생성

IOT는 인위적으로 CF(clustering factor)를 좋게 만드는 방법 중 하나

같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있으므로 sequential access (=> BETWEEN 이나 부등호 조건으로 넓은 범위를 읽을 때 유리)