3.1 테이블 엑세스 최소 화
- SQL 은 랜덤 IO와의 전쟁
인덱스 ROWID는 물리적 주소? 논리적 주소?
- 인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값 즉 ROWID 얻기
- ROWID는 물리적 주소 보다 논리적 주소에 가깝다
- 물리적으로 직접 연결되지 않고 테이블 레코드를 찾아가기 위한 논리적 주소 정보 담고 있기 때문
- 하지만 데이터 파일 번호, 오브젝트 번호, 블록 번호 같은 물리적 요소로 구성 하여 물리적 주소라 착각하기 쉬움
- 허나 논리 정보
메인 메모리 DB 비교
-
메인 메모리는 디스크를 경유하지 않고 대부분 데이터를 메모리에서 읽음
-
잘 튜닝된 OLTP 일경우 히트율이 99%
- 즉 디스크를 경유하지 않고 대부분 데이터를 메모리에서 읽는 뜻
-
메인 메모리 DB의 경우 인스턴스 기동시 디스크에 저장된 데이터를 버퍼 캐시로 로딩하고 이어서 인덱스 생성
- 이떼 인덱스는 오라클처럼 디스크상 주소정보를 갖는게 아니라 메모리상 주소정보, 즉 포인터를 갖음
- 따라서 인덱스를 경유해 테이블 엑세스하는 비용이 오라클가 비교할수 없을 정도로 낮음
-
오라클 DB는 메모리 주소 정보가 아닌 디스크 주소 정보를 이용해 버퍼 블록을 찾음
- 이는 DBA
정리: 디스크 기반 DB에서 "데이터가 메모리에 있다"는 의미
-
인덱스:
- B-Tree의 일부(특히 루트와 상위 노드)가 메모리에 캐싱되어 있을 가능성이 높음.
- 전체 B-Tree 구조가 메모리에 올라오는 것은 아님.
-
데이터 블록:
- 데이터베이스는 자주 사용하는 데이터 블록을 버퍼 캐시에 유지하여 디스크 I/O를 최소화.
- 그러나, 데이터가 메모리에 없다면 여전히 디스크에서 읽어와야 함.
-
인덱스로 테이블 블록 엑세스시
- 리프블록에서 읽은 ROWID를 분해해 DBA 정보를 얻음
-
테이블 풀 스캔시
- 익스텐트 맵을 통해 읽은 블록들의 DBA 정보를 얻음
메인 메모리 포인터는 전화
디스크 DB ROWID는 우편 주소로 생각하자
인덱스 클러스터링 팩터
- 특정 칼럼 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미
CF가 좋은 칼럼에 생성한 인덱스는 검색 효율이 좋다
- 이는 테이블 엑세스량에 비해 블록 IO가 적게 발생함을 의미
인덱스 스캔이 테이븦 풀 스켄보다 느려지게 만드는 요인
- Table Full Scan은 시퀀셜 액세스인 반면 , 인덱스 ROWID 를 이용한 테이블 액세스는 랜덤 액세스 방식
- Table Full Scan 은 Multiblock IO 반면, 인덱스 ROWID 이용한 테이블 액세스는 Single Block IO방식
온라인 프로그램
- 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는것이 무엇보다 중요
- 조인도 NL 조인
- 인덱스를 이용하는 조인 방식
- 인덱스를 이용해 소트 연산을 생략함으로 온라인 환경에서 대량 데이터를 조회할 대도 아주 빠른 응답 속도를 낼 수 있음
배치 프로그램
- 전체 범위 처리 기준으로 튜닝
- 처리 대상 집합중 일부를 빠르게 처리하는 것이 아니라 전체를 빠르게 처리하는 것이 목표
- 대량 데이터를 빠르게 처리하는 것이 목표면 인덱스와 NL 조인 보단 FULL SCAN 과 해시 조인이 유리
- 초 대용량 테이블을 FULL SCAN을 하면 상당히 오래 기다려야 하고 시스템 주는 부담도 적지 않음
- 배치 프로그램에서는 파시션 활용 전략이 매우 중요한 튜닝 요소
인덱스 컬럼 추가
- 테이블 엑세ㅔ스 최소화를 위해 가자 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가하는것
다음과 같이 기존 [DEPTNO + JOB ] 인 인덱스에서 [DEPTNO + JOB ++ SAL] 일때 인덱스 스캔량은 줄지 않지만 테이블 랜덤 액세스 횟수를 줄여주기 때문
Include 인덱스
- SQL Server 2005버전에 추가된 기능
- 인덱스 키 외 미리 지정한 컬럼을 리프 레벨에 함께 저장
create index emp_x01 on emp(deptno) include (sal)
위는 sal 칼럼을 리프 블록에만 저장 해 수직적 탐색에는 deptno 만 사용하고 수평적 탐색에는 sal 컬럼도 필터 조건으로 사용한다
- 즉 인덱스 스캔량을 줄이려고 사용
인덱스 구조 테이블
- 랜덤 액세스가 아예 발생하도록 테이블을 인덱스 구조로 생성
- IOT
- 테이블을 찾기 위해 ROWID 를 갖는 일반 인덱스와 달리 IOT는 그 자리에 테이블 데이터를 갖는다.
- 즉 테이블 블록에 있어야할 데이터를 인덱스 리프 블록에 모두 저장 중이다.
- 즉 인덱스 리프 블록이 데이터 블록
create table index_org_t
( anumber, b varchar(10), constraint index_org_t_pk primary key(A))
organization index;-
일반 테이블은 힙 구조 테이블
- 입력할때 랜덤 방식 사용
-
IOT 인덱스 구조 테이블은 정렬상태 유지하며 데이터 입력
IOT 는 인위적으로 클러스터링 팩터를 좋게 만드는 방법중 하나
- 시퀀셜 방식으로ㅜ 데이터를 액세스 하기 때문
- BETWEEN이나 부등호 조건으로 넓은 범위 읽을 시 유용
클러스터 테이블
- 클러스터 테이블에는 인덱스 클러스터와 해시 클러스터 가 있음
인덱스 클러스터 테이블
-
클러스터 키 값이 같은 래코드를 한 블록에 모아 저장하는 구조
-
한 블록에 모두 담을 수 없을 시 새로운 블록에 할당해 클러스터 체인 연결
-
여러 테이블 레코드를 같은 블록에 저장 할 수 있는데
- 다중 클러스터
-
IOT 에 가까움
// 클러스터 생성
create cluster c_dept# (deptno number(2)) index;
// 클러스터 인덱스 생성
create index c_dept#_indx on cluster c_dept#;
// 검색 혹은 저장될 위치를 찾을 때 사용 하기 때문
//클러스터 테이블 생성
create table dept(
deptno number(2) not null
, dname varchar2(14) not null
, loc varchar2(13)
cluster c_dept#(deptno)
);
클러스터 인덱스도 일반 BTree 인덱스 구조를 사용하지만 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫 번째 데이터 블록을 가리킴
-
즉 일반 인덱스 레코드는 테이블 레코드와 1:1 대응 관계
-
클러스터 인덱슽는 테이블 레코드와 1:1 관계를 갖음
-
클러스터 인덱스의 키 값은 항상 unique 함
-
즉 인덱스를 스캔하면서 값을 찾을때 랜덤 엑세스가 값 하나당 한번쌕 밖에 발생하지 않음
-
클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문 넓은 범위 읽더라도 비효율 없음
해시 클러스터 테이블
- 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아감
