2.3 인덱스 확장기능 사용법
2.3.1 Index Range Scan
- Index Range Scan 은 B*Tree 인덱스의 가장 일반적인 현택의 방식
- 수직적 탐색 + 수평적 탐색(필요한 범위만)
- 선두 칼럼을 가공하지 않은 상태로 조건정레 사용해야 한다 -> 조건 만족시 무조건 Range Scan 가능
2.3.2 Index Full Scan
- 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
- 최적의 인덱스가 없을때 차선으로 선택된다.
create index emp_ename_sal_idx on emp (ename, sal);
select * from emp
where sal > 2000
order by ename;- 선두 컬럼인 ename 이 조건정레 없으므로 Range Scan 불가능
- SAL 컬럼이 인덱스에 있으므로 Index Full Scan 활용
Index Full Scan vs Table Full Scan
- 선두 컬럼이 조건절에 없으면 Table Full Scan 을 먼저 고려한다.
- 인덱스가 차지하는 면적은 테이블보다 훨씬 적다. 이런 경우 테이블 전체보다는 인덱스 전체를 스캔하는 것이 더 효율적일 수 있다.
- 이런 경우 옵티마이저가 Index Full Scan 방식을 선택한다.
- 하지만 스캔 데이터 자체가 많은 경우에는 Single block 전략인 Index 보다는 Multi Block 전략인 Table Full Scan 으로 힌트 설정을 하는 것이 더 효과적일 수 있다,
- Index Full Scan 의 경우 Order By 효과를 가지므로 해당 연산을 생략할 목적으로 사용되기도 한다.
select /*+ first_rows */ *
from emp
where sal > 1000
order by ename;
-- 실제 order by 가 아닌 INDEX(FULL SCAN) 이 사용
-- first_rows 힌트로 옵티마이저 모드를 변경
-- 소트 연산을 생략함으로써 전체 집합 중 처음 일부를 빠르게 출력할 목적으로 옵티마이저가 Index Full Scan 방식을 선택(부분범위 처리가 가능한 상황에서 극적인 성능 개선 효과를 가진다)
-- 하지만 first rows 는 더 많은 I/O 를 발생시킬 수 있으므로 주의요먕2.3.3 Index Unique Scan
- 수직적 탐색으로만 데이터를 스캔하는 ㅂ아식
- 인덱스를
=조건으로 탐색하는 경우 작동 - 해당 인덱스의 키 컬럼을 모두 '=' 조건으로 검색 시 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다.
- 복합키도 가능
2.3.4 Index Skip Scan
- 오라클은 9i 버전 부터 인덱스 선두 컬럼이 조건절에 없어도 Table Full Scan 이 아닌 인덱스를 활용하는 새로운 스캔 방식인 Index Skip Skan 을 도입하였다
- 조건절에 빠진 인덱스의 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다
- 인덱스 선두 컬럼이 존재할 때 유용한 경우도 존재하고 다양한 케이스에서 효율적인 경우들이 존재한다(책 120 ~ 121 p)
- Index Range Scane 이 불가능하거나 효율적이지 못한 상황에서 Index skip Scan 이 효과를 발휘하는 경우가 종종 있다.
select * from 사원 where 성별 = '남' and 연봉 between 2000 and 4000- 위의 경우 루트 블록의 3,4 row 만 탐색하면 된다.
- 남, 여 말고 다른 성별의 유무 존재가 가능하므로 1,6,7,10 row 도 탐색한다.
2.3.5 Index Fast Full Scan
- Index Full Scan 보다 빠른 방식이다
- 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔한다.
- Index Full Scan
루트 -> 브랜치1 -> 1,2,3,4,5,6,7,8,9,10 순서로 진행
- Index Fast Scan
1,2,10,3,9,8,7,4,5,6(연결리스트를 무시한채 익스텐트에서 순서대로 읽는다)
- Index Fast Full Scan 은 Multiblock I/O 방식을 사용하므로 대량의 인덱스 블록을 읽을때 효과를 발휘한다.
- 속도가 빠르지만 연결리스트 구조를 무시하므로 정렬이 되지 않는다
- 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼있을 떄만 사용할 수 있다.
- 인덱스가 파티션 되어 있지 않더라도 병렬 쿼리가 가능하다
2.3.6 Index Range Scan Descending
- Index Range Scan 과 기본적으로 동일한 방식이다.
- 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 더는다.
3 인덱스 튜닝
3.1 테이블 액세스 최소화
- SQL 튜닝은 랜덤 I/O 와의 전쟁이다
- 테이블 랜덤 액세스를 최소화하는 방법들을 알아보자
3.1.1 테이블 랜덤 액세스
인덱스 ROWID 는 논리적 주소이다
- 인덱스를 스캔한 후 방드시 테이블을 액세스한다.
- 인덱스 ROW ID 는 논리적 주소로 테이블 레코드를 찾아가기위한 주소 정보를 가진다
메인 메모리 DB 와 비교
- 잘 튜닝된 OLTP 성 DB 는 버퍼 캐시 히트율이 99% 이상이다.
- 그렇다면 디스크 DB 는 메인 메모리 DB(디스크 상의 주소정보가 아닌 메모리상의 주소정보(Pointer) 를 가져 엄청나게 빠르다) 와 비교했을때 성능이 비슷할 거 같지만 실제 성능이 좋지 않으며 대량 인덱스 액세스 시 엄청난 차이를 보인다.
- 오라클은 테이블 브록이 수시로 버퍼 캐시에서 밀려났다가 다시 캐싱된다. 따라서 직접적인 포인터 연결이 불가능하다
- 디크스 주소 정보를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다.
I/O 메커니즘 복습
- 블록을 읽을 떄 디스크로 가지 않고 버퍼 캐시를 활용한다
- DBA 를 ㅌ오해 해시 함수에 입력해서 해시 체인을 찾고 버퍼 헤더를 찾는다.
- 즉 해싱 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 찾는다.
- 데이터를 버퍼캐시에서 먼저 찾고 없을때만 디스크에서 블록을 읽는다.
- 모든 데이터가 캐싱되어도 매번 DBA 해싱과 래치 획득 과정을 거쳐야 되고 lock 까지 고려한다면 인덱스 ROWID 를 이용한 테이블 액세스는 고비용 구조이다.
3.1.2 인덱스 클러스터링 팩터
- 클러스터링 팩터(Clustering FActor, CF) : 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여 있는 정도
- CF 가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다
- 물리적으로 모여있을때 조회 속도가 빠르다
왜 CF 가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋은가?
- 오라클은 래치 획득과 해시 체인 스캔 과정을 거처 테이블 블록에 대한 포인터를 바로 해제하지 않고 유지한다(버퍼 Pinning)
- 이를 통해 논리적 블록 I/O 과정을 생략할 수 있다.
- CF 가 안좋은 경우 데이블 액세스 하는 횟수만큼 블록 I/O 가 발생한다(책 138p)
3.1.3 인덱스 손익분기점
- 인덱스 ROWID 를 이용한 테이블 액세스는 고비용 구조이다.
- Index Range Scane 이 Table Full Scan 보다 느려지는 지점을 인덱스 손익분지검이라고 부른다.
- 핵심 지점이 2가지 있다.
- Table Full Scan 은 시퀀셜 액세스인 반면, 인덱스 ROWID 를 이용한 테이블 액세스는 랜덤 액세스 방식이다.
- Table Full Scan 은 Multiblock I/O 인 반면, 인덱스 ROWID 를 이용한 테이블 액세스는 Single Block I/O 방식이다.
- CF가 나쁘면 논리적 I/O 가 늘어 물리적 I/O 도 증가한다.
온라인 프로그램 튜닝 vs 배치 프로그램 튜닝
- 대량의 데이터를 읽고 갱신하는 배치(Batch) 프로그램은 항상 전체범위 처리 기준으로 튜닝해야 한다.
- 일부가 아닌 전체를 빠르게 처리하는 것을 목표로 삼아햐 한다.
- 이때 인덱스와 NL 조인보다 Full Scan 과 해시 조인이 유리하다.
- 하지만 초대용량 테이블은 Full Scan 이 부담스러울 수 있다.
- 이런 경우 파티션 활용 전략과 병렬 처리가 중요하다
- 자주 구분되는 컬럼을 기준으로 파티션을 분리하면 효과적이다.
- 파티션 테이블에도 인덱스를 사용할 수 있지만, Full Scan 이 대부분 효과적이다. 왜냐하면 파티셔닝 이유 자체가 대부분 Full Scan 을 빠르게 하기 위하여서 이다.
인덱스는 다양한 튜닝 도구중 하나이다. 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾으려 할 때 주로 사용된다.
3.1.4 인덱스 컬럼 추가
- 테이블 액세스 최소화를 위해 사용되는 가장 일반적인 튜닝 기번은 인덱스에 컬럼을 추가하는 것이다.
- 기존 인덱스 가 A+B 인 경우 B+A 로 바꾸면 좋지만 실 운영 환경에서 변경은 쉽지 않다
- 그렇다고 모든 인덱스를 추가하다보면 관리 비용과 DML 부하에 따른 트랜잭션 선능 저하가 발생할 수 있다.
- 기존 인덱스에 컬럼을 추가하는 것으로 효과를 볼 수 있ㄷ.
- 인덱스 스캔량은 줄지 않지만 테이블 랜덤 액세스 횟수를 줄여준다.
3.1.5 인덱스만 읽고 처리
- 테이블 랜덤 액세스가 아무리 많아도 필터 조건에 의 해 버려지는 레코드가 없다면 비효율은 없다.
- 비효율이 없더라고 인덱스 스캔 과정에서 얻은 데이터가 많다면 그만큼 테이블 랜덤 액세스가 많이 발생하여 성능이 느리다.
- 반드시 성능을 개선해야 한다면 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세스가 아예 발생하지 않게 하는 방법을 고려할 수 있다.
- 이를
Covered 쿼리, 이 쿼리에 사용한 인덱스를Covered 인덱스라고 부른다. - 성능은 매우 좋지만, 추가해야 할 컬럼이 많으면 실제 적용이 어려울 수 있다.
Include 인덱스
- 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능
create index emp_x01 on emp (deptno) include (sal)- 수평적 탐색에서 include 인덱스 컬럼을 사용하여 필터 조건으로 활요할 수 있다.
- include 를 통해 인덱스 스캔량을 줄일 수 있다.
- 이를 통해 테이블 랜덤 액세스를 줄일 수 있다.
- 필터 조건이므로 이를 통해 소트 연산을 생략할 수 있다
3.1.6 인덱스 구조 테이블
- 랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성할 수 있다.
- 위 그림과 같이 인덱스 리프 블록이 곧 데이터 블록이다
- 오라클은 IOT(Index-Organized Table) 이라 부르고 MS-SQL Server 는 클러스터형(Clustered) 인덱스 라고 부른다.
- IOT 는 인위적으로 클러스터링 팩터를 좋게 만드는 방법이다.
create table ~~ organization index;
-- organization 옵션으로 가능3.1.7 클러스터 테이블
인덱스 클러스터 테이블
- 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장한다
- 한 블록에 모두 담을 수 없는 경우 새로운 블록을 항당해 클러스터 체인으로 여녁한다.
- 여러 테이블 레코드를 같은 블록에 저장할 수도 있는데 이를
다중 테이블 클러스터라고 부른다.- 일반 테이블은 하나의 데이터 블록을 여러 테이블리 공유할 수 없다.
create cluster c_dept# ( deptno number(2) ) index;
create index c_dept#_idx on cluster c_dept#;
- 클러스터에 테이블을 담기 전에 반드시 클러스터 인덱스를 정의해야 한다.
- 왜냐하면 클러스터 인덱스는 데이터 검색 용도 뿐 아니라 데이터 저장될 위치를 찾을 떄도 사용하기 때문이다.
- 클러스터 인덱스도 B*Tree 인덱스 구조를 띄지만, 테이블 레코드와 1:M 관계를 가져 클러스터 인덱스의 키 값은 항상 unique 하다는 특징을 가진다.
- 해당 클러스터 키로 한번만 랜덤 액세스가 발생하고 그 후에는 시퀀셜 방식으로 스캔하기 때문에 효율적이다.
- 실행계획을 보면
INDEX (UNIQUE SCAN)이 발생하는 것을 볼 수 있다.
해시 클러스터 테이블
- 해시 클러스터는 인덱스를 사용하지 않고 해시 알고리즘을 사용한다는 점만 다르다
create cluster c_dept# ( deptno number(2) ) hashkeys 4;- 실행계획을 보면
TABLE ACCESS(HASH)인것을 볼 수 있다.
3.2 부분범위 처리 활용
3.2.1 부분범위 처리
- DBMS 가 클라이언트에게 데이터를 전송할 때 일정량씩 나누어 전송한다.
- 전송하지 않은 분량이 많이 남아있어도 추가 Fetch Call 을 받기 전까지 그대로 멈춰서 대기한다.
- order by 가 추가되면 전체 범위처리로 바뀐다.
- 하지만 인덱스가 적용된다면 항상 정렬상태이므로 바로 전송가능하다
- 매번 보내는 양(Array Size) 는 환경에 맞추어 설정한다.
- 이러한 설정은 DB 가 아닌 프로그램 몫이다
- 따라서 Initial Fetch 와 Array Size 를 프로그램에서 설정해주어야 한다.
3.2.2 부분처리 구현
책 p164 ~ 165 참고
3.2.3 OLTP 환경에서 부분범위 처리에 의한 성능개선 원리
- OLTP 업무에서 쿼리 결과는 많아도 사용자는 모든 데이터를 일일히 확인하지 않는다.
- 주로 정렬 순서에서 상위 일부 데이터만 확인한다.
- 정렬 상태를 유지하는 인덱스를 활용하면 정렬 작업을 생략하고 앞쪽 일부 데이터를 아주 빠르게 보여줄 수있다.
멈출 수 있어야 의미있는 부분범위 처리
- 클라이언트와 DB 서버 사이에 WAS< AP 서버 등이 존재하는 n-Tier 아키텍처에서는 클라이언트개 특정 DB 커넥션을 독점할 수 없다.
- 단위 작업을 마치면 DB 커넥션을 곧바로 커넥션 풀에 반환해야 한다.
- 그 전에 SQL 조회 결과를 클라이언트에게 모두 전송하고 커서를 닫아야 한다.
- 하지만 실제로 부분범위 처리가 가능하고 5장 3절에서 이야기 하자
클린코드 지향