[SQLP] 인덱스 튜닝

당당·2024년 4월 23일
SQLP인덱스 튜닝
0

SQLP

목록 보기
8/12

📔설명

인덱스 기본 원리, 테이블 액세스 최소화, 인덱스 스캔 효율화, 인덱스 설계에 대해 알아보자

🍜인덱스 기본 원리

필요한 데이터를 빨리 찾으려면 인덱스의 도움이 필요
=> 인덱스가 아예 없거나, 적절한 인덱스를 찾지 못하면 테이블 전체를 읽어야 해 시간이 오래걸림

1. 인덱스 구조

인덱스 기본 구조

맨 위쪽 뿌리(Root)에서부터 가지(Branch)를 거쳐 맨 아래 나뭇잎(Leaf)까지 연결되는 구조
=> 깊이(Height) : 루트에서 리프 블록까지의 거리, 반복적으로 인덱스 탐색시 성능 영향
=> 루트, 브랜치 블록 : 각 하위 노드들의 데이터 값 범위를 나타내는 키 값과 그 키 값에 해당하는 블록을 찾는데 필요한 주소 정보(ROWID) 가짐
=> 키 값이 같을 때 ROWID순으로 정렬
=> 리프 블록 : 항상 인덱스 키 값 순으로 정렬, 범위 스캔가능, 정방향과 역방향 스캔 가능하도록 양방향 연결 리스트 구조로 연결

null값 인덱스

  • Oracle : 인덱스 구성 컬럼이 모두 null인 레코드는 인덱스에 저장X, 구성 컬럼 중 하나라도 null값이 아니면 인덱스에 저장, null값이 맨 뒤
  • SQL Server : 인덱스 구성 컬럼이 모두 null인 레코드도 인덱스에 저장, null값 맨 앞

인덱스 탐색

수평적 탐색 : 인덱스 리프 블록에 저장된 레코드끼리 연결된 순서에 따라 스캔하는 것
수직적 탐색 : 수평적 탐색을 위한 시작 시점을 찾는 과정, 루트에서 리프 블록까지 아래쪽으로 진행


2. 다양한 인덱스 스캔 방식

Index Range Scan

Index Range Scan : 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후, 리프 블록을 필요한 범위(Range)만 스캔
=> B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식
=> SQL Server에서는 Index Seek으로 표현
=> 인덱스 선두 컬럼이 조건절에 사용돼야 가능

create index emp_deptno_idx on emp(deptno); 

set autotrace traceonly explain
select * from emp where deptno = 20;

인덱스를 스캔하는 범위를 얼마나 줄일 수 있느냐, 테이블로 액세스하는 횟수를 얼마나 줄일 수 있느냐가 인덱스 설계와 SQL 튜닝의 핵심 원리

결과 집합은 인덱스 칼럼 순으로 정렬된 상태가 됨

Index Full Scan


Index Full Scan : 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색
=> 최적의 인덱스가 없을 때 차선으로 선택
=> SQL Server에서는 Index Scan으로 표현
=> 인덱스 선두 컬럼이 조건절에 없을 때

create index emp_idx on emp (ename, sal); 

set autotrace traceonly exp

select * from emp 
where sal > 2000
order by ename;

가장 첫 번째 리프 블록을 찾아가기 위한 수직적 탐색이 먼저 일어나긴 함

Index Full Scan의 효용성
=> 인덱스 선두 컬럼이 조건절에 없으면, 옵티마이저는 우선 Table Full Scan을 고려
=> 대용량 테이블이어서 Table Full Scan 부담이 크다면 인덱스 활용하는 방법 다시 고려
=> 테이블 저장공간은 가로x세로, 즉 칼럼길이x레코드 수로 결정되므로 인덱스 차지 면적은 테이블보다 훨씬 적음
=> 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 일부에 대해서만 테이블 액세스가 발생하는 경우, 옵티마이저는 Index Full Scan 선택

select * from emp where sal > 5000 order by ename;

연봉이 5000을 초과하는 사원이 전체 중 극히 일부라면 Table Full Scan보다는 Index Full Scan을 통한 필터링이 더 큰 효과
=> 할 수 있다면 인덱스 구성 조정을 해 Index Range Scan으로 변경

인덱스를 이용한 소트 연산 대체
=> Index Range Scan과 마찬가지로 결과 집합이 인덱스 칼럼 순으로 정렬됨

select /*+ first_rows */ * from emp
where sal > 1000
order by ename;

대부분 사원의 연봉이 1000을 초과해 Index Full Scan을 하면 Table Full Scan보다 불리하다.
=> first_rows 힌트(fastfirstrow)를 이용해 옵티마이저 모드를 바꿔 전체 집합 중 처음 일부만을 빠르게 리턴할 목적으로 소트 연산을 생략하기 위해 Index Full Scan 방식을 선택

Index Unique Scan

Index Unique Scan : 수직적 탐색만으로 데이터를 찾는 스캔 방식
=> Unique 인덱스를 =조건으로 탐색하는 경우
=> SQL Server에서는 Index Seek으로 표시(Range Scan과 Unique Scan을 구분X)

create unique index pk_emp on emp(empno);

alter table emp add
constraint pk_emp primary key(empno) using index pk_emp;

set autotrace traceonly explain 
select empno, ename from emp where empno = 7788;

Index Skip Scan

Index Skip Scan : 인덱스 선두 칼럼이 조건절로 사용되지 않았을 때, 인덱스를 활용하는 새로운 스캔방식
=> 루트 또는 브랜치 블록에서 읽은 칼럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 가능성이 있는 하위 블록(브랜치 또는 리프)만 골라서 액세스하는 방식
=> 조건절에 빠진 인덱스 선두 칼럼의 Distinct Value 개수가 적고, 후행 칼럼의 Distinct Value 개수가 많을 때 유용

-- 사원_IDX : 성별+연봉

select * from 사원 where 연봉 between 2000 and 4000;

Execution Plan
--------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
2 1 	INDEX (SKIP SCAN) OF '사원_IDX' (INDEX)
--성별 값을 In-List로 제공
select * from 사원
where 연봉 between 2000 and 4000 3
and 성별 in ('남', '여');

Execution Plan
--------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 INLIST ITERATOR
2 1 	TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
3 2 		INDEX (RANGE SCAN) OF '사원_IDX' (INDEX)

INLIST LITERATOR : 조건절 In-List에 제공된 값의 종류만큼 인덱스 탐색을 반복 수행
=> 해당 튜닝 기법은 In-List로 제공하는 값의 종류가 적어야 효과적

Index Fast Full Scan

Index Fast Full Scan : Index Full Scan보다 빠르다
=> 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔

Index Range Scan Descending

Index Range Scan Descending : 인덱스를 뒤에서부터 앞쪽으로 스캔
=> 내림차순으로 정렬된 결과 집합 얻음
=> Index Range Scan과 기본적으로 동일
=> SQL Server에서는 Index Scan 뒤에 ORDERED BACKWARD 표시

select * from emp
where empno is not null
order by empno desc;

max값을 구할 때도 해당 칼럼에 인덱스가 있으면 뒤에서 한 건만 읽고 멈추면 됨

create index emp_x02 on emp(deptno, sal);

select deptno, dname, loc 
	,(select max(sal) from emp where deptno = d.deptno)
from dept d


3. 인덱스 종류

B*TREE 인덱스

B*Tree 인덱스 구조에서 나타날 수 있는 Index Fragmentation

Unbalanced Index : delete 작업 때문에 인덱스가 불균형 상태에 놓일 수 있다는 것
=> 다른 리프 노드에 비해 루트 블록과의 거리가 더 멀거나 가까운 리프 노드가 생길 수 있다는 것
=> B*Tree 구조에서 이런 현상은 절대 발생 X
=> B*Tree 인덱스의 B는 Balanced로서, 루트로부터 모든 리프 블록까지의 높이가 동일

Index Skew : 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상
=> 대량의 delete 작업을 마치고 나면, 밑 예시처럼 인덱스 왼쪽에 있는 리프 블록들은 텅 비는 반면 오른쪽은 꽉 찬 상태
=> Oracle은 텅 빈 인덱스 블록은 커밋하는 순간 freelist로 반환되지만, 인덱스 구조 상에는 그대로 남음
=> 상위 브랜치에서 해당 리프 블록을 가리키는 엔트리가 그대로 남아 있어 인덱스 정렬 순서상 그곳에 입력될 새로운 값이 들어오면 언제든 재사용 가능
=> 새로운 값이 하나라도 입력되기 전 다른 노드에 인덱스 분할이 발생하면, 그것을 위해서도 이 블록이 재사용 가능, 이땐 상위 브랜치에서 해당 리프 블록 가리키는 엔트리 제거돼 다른 쪽 브랜치의 자식 노드로 이동하고, freelist에서도 제거
=> 레코드가 모두 삭제된 블록은 언제든 재사용 가능하지만, 다시 채워질 때까진 인덱스 스캔 효율이 낮음
=> SQL Server에선 Index Skew 현상 발생X, 주기적으로 B*Tree 인덱스를 체크해 지워진 레코드와 페이지를 정리해주기 때문
=> 인덱스 레코드를 지우면 바로 리프 페이지에서 제거되는 것이 아니라, Ghost 레코드로 마크됐다가 이를 정리해주는 별도의 쓰레드에 의해 비동기 방식으로 제거

-- 대량의 delete 작업
create table t as select rownum no from big_table where rownum <= 1000000 ;

create index t_idx on t(no) ;

delete from t where no <= 500000 ;

commit;

Index Sparse : 인덱스 블록 전반에 걸쳐 밀도(density)가 떨어지는 현상
=> 아래 예시로, t_idx 블록의 밀도가 50%밖에 되지 않음 (50만건을 지우고 나서도 스캔한 인덱스 블록이 똑같이 2001개)
=> 지워진 자리에 인덱스 정렬 순서에 따라 새로운 값이 입력되면, 그 공간은 재사용되지만, 대량의 delete 작업 후 한동안 인덱스 스캔 효율이 낮다는 문제점 발생
=> Index Skew처럼 블록이 아예 텅 비면 곧바로 freelist로 반환돼 언제든 재사용되지만, Index Sparse는 지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용되지 않을 수 있음

create table t as select rownum no from big_table where rownum <= 1000000 ;

create index t_idx on t(no) ;

select /*+ index(t) */ count(*) from t where no > 0;

COUNT(*)
----------
1000000

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2001 consistent gets
… ……

delete from t where mod(no, 10) < 5 ;
-- 500000 행이 삭제되었습니다.

commit;

select /*+ index(t) */ count(*) from t where no > 0; 

COUNT(*) 
---------- 
500000

Statistics
----------------------------------------------------------
0 recursive calls 
0 db block gets 
2001 consistent gets
… ……

인덱스 재생성 : Fragmentation 때문에 인덱스 크기가 계속 증가하고 스캔 효율이 나빠지면 인덱스를 재생성하거나 DBMS의 명령어를 이용해 빈 공간 제거가 유용
=> 일반적으로 인덱스 블록에는 어느 정도 공간을 남겨두는 것이 좋음. 빈 공간을 제거해 인덱스 구조를 슬림화하면, 저장 효율이나 스캔 효율에는 좋으나 인덱스 분할이 자주 발생해 DML 성능이 나빠짐
=> 인덱스 재생성 작업을 시행할 때

  • 인덱스 분할에 의한 경합이 현저히 높을 때
  • 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때. 특히, NL 조인에서 반복 액세스 되는 인덱스 높이가 증가했을 때
  • 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때
  • 총 레코드 수가 일정한데도 인덱스가 계속 커질 때

비트맵 인덱스

  • 색상 컬럼에 생성한 비트맵 인덱스를 보면, 키 값이 BLUE인 4, 7, 9번째 비트가 1인데 상응하는 테이블 레코드의 색상 값이 BLUE임을 뜻함

  • 부정형 조건에도 사용 가능
    => BLUE가 아닌 값을 찾으려면 인덱스 첫 번째 행에서 0으로 설정된 비트를 찾으면 됨

  • 비트맵 인덱스는 NULL도 저장

--가능
select * from 상품
where 색상 is null

  • Distinct Value 개수가 적을 때 비트맵 인덱스 사용하면 저장효율 매우 좋음
    => B*Tree 인덱스보다 훨씬 적은 용량을 차지해 인덱스가 여러 개 필요한 대용량 테이블에 유용

  • 하나의 단독 비트맵 인덱스는 쓰임새가 별로 없고, 여러 비트맵 인덱스를 동시에 사용하면 효과 좋음

-- 여러 개의 비트맵 인덱스로 Bitwise 연산 수행시 테이블 액세스량 줄면 성능 개선 기대
select 지역, sum(판매량), sum(판매금액)
from 연도별지역별상품매출
where (크기 = 'SMALL' or 크기 is null)
and 색상 = 'GREEN'
and 출시연도 = '2010'
group by 지역;
  • 여러 인덱스를 동시에 활용할 수 있기 때문에 임의 질의(ad-hoc query)가 많은 환경에 적합
  • Lock에 의한 DML 부하가 심함
    => 레코드 하나만 변경되더라도 해당 비트맵 범위에 속한 모든 레코드에 LOCK이 걸림
    => OLTP성 환경에 비트맵 인덱스 사용 불가
    => 대용량 데이터 웨어하우스(OLAP) 환경에 적합

함수기반 인덱스

함수기반 인덱스(Function Based Index, FBI) : 칼럼 값 자체가 아닌, 칼럼에 특정 함수를 적용한 값으로 B*TRee 인덱스 생성
=> 인덱스 칼럼을 가공시 정상적인 인덱스 사용 불가능
=> 함수를 씌워 인덱스를 만들면 인덱스 사용 가능 

--주문수량이 100보다 작거나 null인 주문건
select * from 주문 where nvl(주문수량, 0) < 100
--주문수량이 null인 레코드는 인덱스에 0으로 저장
create index emp_x01 on emp( nvl(주문수량, 0) );

리버스 키 인덱스

리버스 키 인덱스 : 입력된 키 값을 거꾸로 변환해서 저장하는 인덱스

일련번호나 주문일시 같은 칼럼은 입력되는 값이 순차적으로 증가하므로 가장 오른쪽 리프 블록에만 데이터가 쌓임
=> Right Growing 인덱스
=> 동시 INSERT가 심할 때 인덱스 블록 경합을 일으켜 트랜잭션 처리량 감소시킴

create index 주문_x01 on 주문( reverse(주문일시) );

함수기반 인덱스 처럼 reverse 함수에서 반환된 값을 저장하는 인덱스라고 생각하면 쉬움

입력된 값을 거꾸로 변환해서 저장하면 데이터가 고르게 분포
=> 데이터를 거꾸로 입력하기 때문에 =조건으로만 검색 가능
=> 범위검색 조건 사용 X

클러스터 인덱스

인덱스 클러스터 테이블 : 클러스터 키 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조
=> 한 블록에 모두 담을 수 없을 땐 새로운 블록을 할당해 클러스터 체인으로 연결
=> 여러 테이블 레코드가 물리적으로 같은 블록에 저장되도록 클러스터 할당 가능 (다중 테이블 인덱스 클러스터), 여러 테이블을 서로 조인된 상태로 저장
=> B*Tree 인덱스 구조를 사용하지만, 해당 키 값을 저장하는 첫 번째 데이터 블록만 가리킨다는 점이 다름
=> 클러스터 인덱스 키 값은 항상 Unique하며, 테이블 레코드와 1:M 관계를 가짐
=> 클러스터 인덱스를 스캔하면서 값을 찾을 땐 랜덤 액세스가 값 하나당 한 번씩만 발생
=> 클러스터에 도달해선 시퀀셜 방식으로 스캔하기 때문에 넓은 범위 검색 때 유리
=> 새로운 값이 자주 입력(새 클러스터 할당)되거나 수정이 자주 발생하는 칼럼(클러스터 이동)은 클러스터 키 선정X

-- 인덱스 클러스터를 만들고, 클러스터 인덱스 정의
create cluster c_deptno# ( deptno number(2) ) index ;

create index i_deptno# on cluster c_deptno#;
--생성한 클러스터에 테이블을 담기만 하면 됨 
create table emp 2 cluster c_deptno#
as
select * from scott.emp;

클러스터형 인덱스와 IOT

SQL Server에서 지원되는 인덱스는 클러스터형 인덱스(Clustered Index)와 비클러스터형 인덱스(Non-Clustered Index) 두 가지
=> 비클러스터형 인덱스는 B*Tree 인덱스와 같음

  • 클러스터형 인덱스와 IOT 구조
    => 클러스터형 인덱스도 구조적으로는 B*Tree 인덱스와 같은 형태
    => 별도의 테이블을 생성하지 않고 모든 행 데이터를 인덱스 리프 페이지에 저장 (인덱스 리프 페이지가 곧 데이터 페이지)
    => 정렬 상태를 유지하며 데이터 삽입하므로 테이블마다 단 하나만 생성 가능 (한 테이블이 두 개의 정렬 순서를 가질 수 없으므로)
    => 항상 정렬된 상태 유지해야 하므로 데이터 입력 시 성능이 느린 단점
    => 비클러스터형 인덱스를 생성해도 정렬 유지하는 점은 같으나, 클러스터형 인덱스는 인덱스 키 값 외에 많은 데이터를 리프 페이지에 저장하기 때문에 인덱스 분할이 자주 발생하여 DML 부하 심함
    => 넓은 범위의 데이터를 검색할 때 유리하기 때문에 사용
    => 같은 값을 가진 레코드가 정렬된 상태로 모여 있고, 리프 레벨이 곧 데이터 페이지이므로, 즉 정렬된 리프 페이지를 시퀀셜 방식으로 스캔하면서 검색 값을 모두 찾을 수 있고 찾은 레코드에 대해선 추가 테이블 랜덤 액세스가 필요하지 않음
    => Oracle IOT와 비슷 (Oracle IOT는 PK에만 생성 가능)
    => SQL Server 클러스터형 인덱스는 중복 값이 있는 칼럼에도 생성 가능하며, 이를 식별하기 위해 uniquifier 함께 저장

  • 클러스터형 인덱스와 IOT 활용
    => 넓은 범위를 주로 검색하는 테이블
    => 크기가 작고 NL 조인으로 반복 룩업하는 테이블
    => 칼럼 수가 적고 로우 수가 많은 테이블
    => 테이블 입력과 조회 패턴이 서로 다른 테이블

-- 100명의 영업사원
-- 실적 등록은 일자별로 되나, 실적 조회는 주로 사원별로 이루어짐
select substring(일자, 1, 6) 월도 , sum(판매금액) 총판매금액, avg(판매금액) 평균판매금액 
from 영업실적
where 사번 = 'S1234'
and 일자 between '20090101' and '20091231'
group by substring(일자, 1, 6);

비클러스터형 인덱스 사용 시 사원마다 365개의 데이터 페이지를 랜덤 액세스 방식으로 읽어야 함
=> 특정 사원의 1년치 영업실적이 365개의 페이지에 흩어져 저장되기 때문
=> 사번이 첫 번째 정렬 기준이 되도록 클러스터형 인덱스 생성해주면 한 페이지만 읽어 처리 가능

--sql server
create clustered index 영업실적_idx on 영업실적(사번, 일자);

--oracle
create table 영업실적 (
  사번 varchar2(5),
  일자 varchar2(8),
  ... ,
  constraint 영업실적_PK primary key (사번, 일자) ) organization index;
  • 2차 인덱스로부터 클러스터형 인덱스와 IOT 참조 방식
    => SQL Server는 클러스터형 인덱스를 가리키는 2차 인덱스를 비클러스터형 인덱스라고 부름
    => Oracle은 IOT를 가리키는 2차 인덱스를 Secondary Index라고 부름
    => 비클러스터형 인덱스가 rowid 대신 클러스터형 인덱스의 키 값을 갖도록 변경
    => 클러스터형 인덱스가 키 값을 갱신하지 않는 한, 인덱스 분할 때문에 비클러스터형 인덱스를 갱신할 필요X (rowid 직접 참조시 클러스터형 인덱스가 인덱스 분할에 의해 레코드 위치 변경될 때마다 비클러스터형 인덱스가 갖는 rowid 모두 갱신해야 했었음)
    => DML 부하 대신, 더 많은 I/O가 발생하는 부작용 발생, 비클러스터형 인덱스에서 읽히는 레코드마다 건건이 클러스터형 인덱스 수직 탐색을 반복하기 때문
    => IOT 레코드의 위치는 영구적이지 않으므로 Oracle은 Logical Rowid를 사용
    => physical guess : Secondary 인덱스 최초 생성하거나 재생성한 시점에 IOT 레코드가 위치했던 데이터 블록 주소(DBA), IOT 레코드가 다른 블록으로 이동하더라도 갱신X
    => physical guess가 가리키는 블록을 찾아갔다가 찾는 레코드 없으면 PK로 다시 탐색
Logical Rowid=PK + physical guess

4. 인덱스 튜닝 기초

범위 스캔이 불가능하거나 인덱스 사용이 불가능한 경우

인덱스 선두 칼럼을 조건절에서 가공하면 정상적으로 인덱스 사용 불가 (FBI 인덱스 제외)

select * from 업체 where substr(업체명, 1, 2) = '대한';

부정형 비교 사용해도 사용 불가

select * from 고객 where 직업 <> '학생'

select * from 사원 where 부서코드 is not null;

인덱스 사용 자체가 불가능하지는 않다. Index Full Scan은 가능하다.

select * from 사원 where 연락처 is null

Oracle에서 is null 조건으로만 검색할 때 인덱스 구성 칼럼이 모두 null인 레코드는 인덱스에 저장하지 않으므로 인덱스 사용 불가능 (SQL Server는 가능)
=> 다른 인덱스 칼럼에 is null이 아닌 조건식이 하나라도 있거나 not null 제약이 있다면 Index Range Scan 가능 (선두 칼럼이 조건절에서 누락되지 않아야 함)

인덱스 칼럼의 가공

묵시적 형변환

인덱스 칼럼을 사용자가 가공하지 않더라도 조건절에서 비교되는 두 값의 데이터 타입이 다르면 내부적으로 형변환 일어남
=> 문자형과 숫자형이 만나면 옵티마이저가 문자형을 숫자형으로 변환
=> 날짜형과 문자형이 만나면 옵티마이저가 문자형을 날짜형으로 변환

-- cdeptno는 문자형 칼럼
-- cdeptno에 인덱스 생성
select * from emp where cdeptno = 20

-------------------------------------------------------------
| Id | Operation 	| Name | Rows | Bytes | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | 	| 3 	| 273 | 2 |
|* 1 | TABLE ACCESS FULL | EMP | 3 | 273 | 2 |
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("EMP"."CDEPTNO")=20) -- 문자형 CDEPTNO 칼럼이 숫자형으로 변환됨

cdeptno 칼럼이 숫자형으로 변환되어, 인덱스 사용 불가


🦪테이블 액세스 최소화

1. 인덱스 ROWID에 의한 테이블 랜덤 액세스

쿼리에서 참조되는 칼럼이 인덱스에 모두 포함되는 경우가 아니라면, 테이블 랜덤 액세스 발생
=> Table Access By Index ROWID
=> SQL Server는 RID Lookup으로 표시

인덱스 ROWID에 의한 테이블 액세스 구조

rowid : 물리적 위치 정보(오브젝트 번호, 데이터 파일 번호, 블록 번호 등)로 구성되지만, 인덱스에서 테이블 레코드로 직접 연결되는 구조X
=> 메모리 상의 위치정보가 아닌 디스크 상의 위치정보
=> 데이터 블록 읽을 땐 항상 버퍼 캐시 경유하므로 메모리 상에서 버퍼 블록을 찾기 위해 해시 구조와 알고리즘 사용, 해시 키 값은 rowid에 내포된 DBA(데이터 블록 주소)

인덱스 ROWID를 이용해 데이터 블록을 읽는 메커니즘

  • 인덱스에서 하나의 rowid를 읽고 DBA를 해시함수에 적용해 해시 값 확인
  • 해시 값을 이용해 해시 버킷 찾아감
  • 해시 버킷에 연결된 해시 체인을 스캔하면서 블록 헤더 찾음
  • 해시 체인에서 블록 헤더를 찾으면 거기 저장된 포인터를 이용해 버퍼 블록 읽음
  • 블록 헤더를 찾지 못하면, LRU 리스트를 스캔하면서 Free 버퍼를 찾음 (블록 적재를 위해 빈 캐시 공간 찾는 것)
  • Free 버퍼를 얻지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼 확보
  • Free 버퍼를 확보하고 나면 디스크에서 블록을 읽어 캐시에 적재

위와 같은 이유로 다량의 테이블 레코드를 읽을 때 인덱스 rowid에 의한 테이블 액세스로 인해 성능 저하가 심각

클러스터링 팩터

클러스터링 팩터 : 특정 칼럼을 기준으로 같은 값을 갖는 데이터가 서로 모여 있는 정도
=> 인덱스 ROWID에 의한 테이블 액세스 비용 평가

인덱스 클러스터링 팩터가 가장 좋으면, 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 일치

인덱스 클러스터링 팩터가 가장 나쁘면, 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 전혀 일치 X

클러스터링 팩터가 좋은 칼럼에 생성한 인덱스는 검색 효율 좋음
=> ex. 거주지역=제주에 해당하는 고객 데이터가 물리적으로 근접해 있다면 데이터 찾는 속도가 더 빠름

인덱스 손익분기점

인덱스 손익분기점 : Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점

인덱스에 의한 액세스가 Full Table Scan보다 더 느리게 만드는 핵심 요인

  • 인덱스 rowid에 의한 테이블 액세스는 랜덤 액세스인 반면, Full Table Scan은 시퀀셜 액세스 방식
  • 디스크I/O시, 인덱스 rowid에 의한 테이블 액세스는 Single Block Read 방식을 사용하나, Full Table Scan은 Multiblock Read 방식 사용

손익분기점 극복하기

  • SQL Server의 클러스터형 인덱스와 Oracle IOT로 테이블을 인덱스 구조로 생성
    => 테이블 자체가 항상 정렬된 상태를 유지하고, 인덱스 리프 블록이 곧 데이터 블록이어서 인덱스 수직 탐색 후 테이블 레코드를 읽기 위한 추가적 랜덤 액세스 불필요
  • SQL Server의 Include Index
    => 인덱스 키 외에 미리 지정한 칼럼을 리프 레벨에 함께 저장하는 기능으로, 테이블 랜덤 액세스 횟수를 줄이도록 도움
  • Oracle의 클러스터 테이블
    => 키 값이 같은 레코드를 같은 블록에 저장하므로, 클러스터 테이블에 대한 클러스터 인덱스 이용시 테이블 랜덤 액세스가 키 값별로 한 번씩만 발생, 클러스터에 도달해서는 시퀀셜 방식으로 읽어 비효율X
  • 파티셔닝
    => 수천만 건에 이르는 테이블을 Full Table Scan하더라도 일부 파티션만 읽고 멈추게 가능
    => 세그먼트 단위로 저장
  • 부분범위처리 원리 이용
    => 인덱스 스캔 비효율이 없도록 부분범위처리 방식으로 프로그램 구현

2. 테이블 액세스 최소화 튜닝

인덱스 칼럼 추가

create index emp_x01 on emp(deptno, job);

select /*+ index(emp emp_x01) */ ename, job, sal
from emp
where deptno = 30 and sal >= 2000;

조건을 만족하는 사원은 단 한명뿐인데, 테이블 액세스가 6번 발생

운영 환경에서는 인덱스 구성을 함부로 바꾸기 쉽지 않으므로 sal 칼럼을 추가해보자

--emp_x01 : deptno+job+sal
select /*+ index(emp emp_x01) */ ename, job, sal
from emp
where deptno = 30 and sal >= 2000;

인덱스 스캔량은 줄지 않았으나, 테이블 랜덤 액세스 횟수를 줄여줌

Covered Index

테이블 랜덤 액세스가 아무리 많더라도, 필터 조건에 의해 버려지는 레코드가 거의 없다면 거기에 비효율은 없음
=> Covered 인덱스 : 아예 테이블 액세스가 발생하지 않도록 필요한 모든 칼럼을 인덱스에 포함시키는 방법
=> Covered 쿼리 : 인덱스만 읽고 처리하는 쿼리

Include Index

Include Index : 인덱스 키 외에 미리 지정한 칼럼을 리프 레벨에 함께 저장하는 기능
=> include 옵션으로 지정

--sql server
create index emp_x01 on emp (deptno) include (sal)

인덱스를 deptno+sal순으로 생성하면, sal칼럼도 수직적 탐색에 사용될 수 있도록 그 값을 루프와 브랜치 블록에 저장하지만, include옵션으로만 지정하면 리프 블록에만 저장
=> 수직적 탐색에는 사용X, 수평적 탐색을 위한 필터 조건으로만 사용함으로써 테이블 랜덤 액세스 횟수 줄임

IOT, 클러스터형 인덱스, 클러스터 테이블 활용

해시 클러스터 테이블 : 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장
=> 클러스터 키로 데이터를 검색하거나 저장할 위치를 찾을 때 해시함수 사용
=> 해시 함수가 인덱스 역할을 대신하며, 해싱 알고리즘을 통해 클러스터 키 값을 데이터 블록 주소로 변환
=> = 검색만 가능

수동으로 클러스터링 팩터 높이기

해당 인덱스 기준으로 테이블을 재생성함으로써 클러스터링 팩터를 인위적으로 좋게 만듦
=> 인덱스가 여러개일 경우, 다른 인덱스의 클러스터링 팩터가 나빠질 수 있음
=> 가장 자주 사용되는 인덱스를 기준으로 테이블을 Reorg

배치 I/O

배치 I/O : 인덱스를 이용해 테이블을 액세스하다가 버퍼 캐시 블록을 찾지 못하면, 디스크 블록을 바로 읽는데, 데이터 블록에 대한 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리
=> 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다를 수 있음

create index emp_x01 on emp(deptno, job, empno);

set autotrace traceonly exp;

--오라클 12c 버전에 추가
select /*+ batch_table_access_by_rowid(e) */ *
from emp e
where deptno=20
order by job, empno;

---------------------------------------------------------------------------------------
| Id  | Operation		    			| Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT 				|	      |     5 |   190 |     2	(0)| 00:00:01 |
|   1 |  SORT ORDER BY					| 		 |     5 |   190 |     2	(0)| 00:00:01 |
|   2 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP |     5 |   190 |     2	(0)| 00:00:01 |
|   3 |   INDEX RANGE SCAN 				| EMP_X01 |     5 |       |     1	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

소트 연산을 생략할 수 있는 인덱스 구성인데도 SORT ORDER BY 오퍼레이션이 나타났다.
=> 배치 I/O 기능 작동시 데이터 정렬 순서 보장X로 옵티마이저가 이런 선택을 한 것


🍣인덱스 스캔 효율화

인덱스를 시퀀셜 방식으로 스캔하는 단계에서 생기는 비효율 해소 원리

1. 인덱스 선행 칼럼이 범위조건일 때의 비효율

인덱스 구성 칼럼이 조건절에서 모두 등치(=) 조건으로 비교되면 리프 블록을 스캔하며 읽은 레코드는 모두 테이블 액세스로 이어져, 효율 최상
=> 인덱스 칼럼 중 일부가 = 조건이 아니거나, 조건절에서 생략되더라도 뒤쪽 칼럼이면 비효율X

-- index : 아파트시세코드 + 평형 + 평형타입 + 인터넷매물
where 아파트시세코드 = :a
where 아파트시세코드 = :a and 평형 = :b
where 아파트시세코드 = :a and 평형 = :b and 평형타입 = :c
where 아파트시세코드 = :a and 평형 = :b and 평형타입 between :c and :d

인덱스 선행 칼럼이 조건절에 누락되거나 between 등 범위검색 조건이 사용되면 인덱스 스캔 단계에서 비효율 발생

-- index : 아파트시세코드 + 평형 + 평형타입 + 인터넷매물
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매
where 아파트시세코드='A01011350900056'
and 평형 = '59'
and 평형타입 = 'A'
and 인터넷매물 between '1' and '2'
order by 입력일 desc

-- 선행 칼럼이 모두 = 조건이므로 전혀 비효율 X

인덱스 선행 칼럼이 모두 = 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 이유는 조건을 만족하는 레코드가 모두 한데 모여 있기 때문

-- index : 인터넷매물 + 아파트시세코드 + 평형 + 평형타입

선두 칼럼에 between 연산자를 사용하면 나머지 조건을 만족하는 레코드들이 인터넷 매물 값 별로 흩어짐
=> 조건을 만족하지 않는 레코드까지 스캔하고서 버려야 하는 비효율 생김


2. 범위조건을 In-List로 전환

-- index : 인터넷매물 + 아파트시세코드 + 평형 + 평형타입
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매 
where 인터넷매물 in ('1', '2')
and 아파트시세코드='A01011350900056'
and 평형 = '59'
and 평형타입 = 'A'
order by 입력일 desc

왼쪽 화살표가 두개인 이유는 인덱스 수직적 탐색이 두 번 발생하기 때문

-------------------------------------------------------------
| Id | Operation 				| Name | Rows | Bytes |
-------------------------------------------------------------
| 0 | SELECT STATEMENT 			| 		| 1 	| 37 |
| 1 | 	INLIST ITERATOR 			| 		| 		| 	|
| 2 | 		TABLE ACCESS BY INDEX ROWID | 매물아파트매매 | 1 | 37 |
| 3 | 			INDEX RANGE SCAN 			| 매물아파트매매_PK | 1 | 	|
-------------------------------------------------------------

INLIST ITERATOR이 실행계획에 생기며, SQL Server에서는 스캔 수가 2로 표시된다. (매물아파트매매 테이블 스캔수)

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매
where 인터넷매물 = '1'
and 아파트시세코드='A01011350900056'
and 평형 = '59' 
and 평형타입 = 'A'
union all
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드 
from 매물아파트매매 
where 인터넷매물 = '2' 
and 아파트시세코드='A01011350900056' 
and 평형 = '59' 
and 평형타입 = 'A' 
order by 입력일 desc

인덱스를 두 번 탐색하는 것은 위와 같은 SQL과 같다.

between 조건을 In-List로 바꿀 때, In-List 개수가 많지 않아야 함
=> 인덱스 수직 탐색이 여러번 발생하기 때문
=> Oracle의 경우 Index Skip Scan이 유용 (선두칼럼이 범위검색 조건일 때도 사용 가능)


3. 범위조건을 2개 이상 사용할 때의 비효율

-- index : 회사+지역+상품명
select 고객ID, 상품명, 지역, ... 
from 가입상품 
where 회사 = :com 
and 지역 like :reg || '%' 
and 상품명 like :prod || '%';

범위검색 조건을 2개 이상 사용하면, 첫 번째가 인덱스 스캔 범위를 거의 결정하고, 두 번째는 필터 조건 역할만 하기 때문에 성능상 불리

select 고객ID, 상품명, 지역, ... 
from 가입상품 
where :reg is null 
and 회사 = :com 
and 상품명 like :prod || '%'

union all 

select 고객ID, 상품명, 지역, ... 
from 가입상품 
where :reg is not null 
and 회사 = :com 
and 지역 = :reg 
and 상품명 like :prod || '%'

union all의 상단 쿼리는 비효율이 있겠지만, 하단 쿼리는 최상으로 수행


🍤인덱스 설계

1. 결합 인덱스 구성을 위한 기본 공식

  • 조건절에 항상 사용되거나 자주 사용되는 칼럼 선정
  • 선정된 칼럼 중 =조건으로 자주 조회되는 칼럼을 앞쪽
  • 소트 오퍼레이션 생략을 위해 칼럼 추가
    => 인덱스 칼럼 구성과 같은 순서로 누락 없이(뒤쪽 칼럼은 상관X) order by절에 기술,
    단 인덱스 구성 칼럼이 조건절에서 =연산자로 비교된다면 그 칼럼은 order by 절에서 누락되거나 인덱스와 다른 순서로 기술해도 상관X

선택도 이슈

  • 선택도가 높은 인덱스는 생성해봤자 효용가치가 별로 없음

2. 추가적인 고려사항

  • 쿼리 수행 빈도
  • 업무 상 중요도
  • 클러스터링 팩터
  • 데이터량
  • DML 부하 (= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 칼럼 포함 여부)
  • 저장 공간
  • 인덱스 관리 비용 등

3. 인덱스 설계도 작성

profile
당당
MSSQL DBA 신입
이전 포스트

[SQLP] SQL 분석 도구

다음 포스트

[SQLP] 조인 튜닝

0개의 댓글