[친절한 SQL 튜닝 스터디] 인덱스 튜닝

https://velog.io/@wooncloud/series/%EC%B9%9C%EC%A0%88%ED%95%9C-SQL-%ED%8A%9C%EB%8B%9D-%EC%8A%A4%ED%84%B0%EB%94%94

시리즈 | 친절한 SQL 튜닝 스터디 - wooncloud.log

테이블 랜덤 액세스

💡 인덱스로 검색해도 빠른데, 왜 굳이 파티셔닝?
인덱스로 검색하는데 왜 느림?

대량의 데이터를 조회하면 테이블 전체 스캔하는것보다 인덱스 사용하는것이 더 느리다.

인덱스 ROWID는 물리적 주소? 논리적 주소?

인덱스를 스캔하는 이유는, 검색조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾아 테이블레코드를 찾아가기 위한 ROWID를 얻는것이다.

ROWID는 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고있기 때문에 논리적인 주소에 가깝다.

ROWID를 포인터라고 이해하면 안된다.

인덱스 ROWID는 논리적 주소이다. 디스크상에서 테이블 레코드를 찾아가기 위한 정보를 담는다.

메인 메모리 DB와 비교

메인 메모리 DB (MMDB)는 데이터를 모두 메모리에 로드해놓고 메모리에서 I/O를 수행하는 DB이다.

그럼 버퍼캐시 히트율 99% DB와 MMDB를 비교하면 MMDB가 빠르다.

버퍼캐시 히트율 99%도 거의 메모리에서 I/O가 일어난다는데 왜?

• MMDB는 디스크 저장 데이터를 버퍼캐시에 이동후 인덱스를 생성해서, 인덱스에 디스크상 주소정보가 아니라 메모리 주소정보(Pointer)를 저장해서 더 빠르다.
• 오라클은 인덱스에 포인터를 직접 연결할 수 없는 구조다. 그래서 디스크 주소정보를 이용해 해시알고리즘으로 버퍼 블록을 찾아가서 훨씬 느리다.

즉, 일반 DBMS에서 인덱스 ROWID를 이용한 테이블 액세스가 생각보다 빠르지 않다.

I/O 매커니즘 복습

• DBA는 데이터파일번호 + 블록번호로서 디스크 상에서 블록을 찾아가기 위한 주소다.
• I/O 성능을 올리려면 버퍼캐시를 활용해야 한다.
• 읽고자 하는 DBA를 해시함수에 입력해서 해시체인을 찾고 거기서 버퍼헤더를 찾음
• 인덱스로 테이블 블록을 액세스 할때 리프블록에서 읽은 ROWID를 분해해 DBA정보를 얻음.
• 테이블 FULL SCAN 할 때는 익스텐트 맵을 통해서 읽을 블록들의 DBA 정보를 얻는다.
• 모든 데이터가 캐싱되어 있더라도 테이블 레코드를 찾기 위해, DBA 해싱과 래치 획득 과정을 반복한다. 동시 액세스가 심하면 캐시버퍼 체인래치와 버퍼 LOCK에 대한 경합까지 발생.
• 결론으로 ROWID를 이용한 테이블 액세스는 고비용 구조이다.

인덱스 클러스터링 팩터

클러스터링 팩터 (CF) : 군집성 계수

특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미.

CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋다. 그 의미는 테이블 액세스량에 비해 블록 I/O가 적게 발생한다는 뜻이다. (뭉쳐 있으니까 다 들리기 수월함.)

)

인덱스 손익분기점

Index Range Scan에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점을 말한다.

인덱스를 이용한 테이블 액세스가 Table Full Scan 보다 느린 이유는 아래와 같다.

1. Table full scan은 시퀀셜 액세스인 반면, ROWID를 이용한 것은 랜덤액세스 방식
2. Table Full Scan은 Multiblock I/O이지만, 인덱스 ROWID를 이용한 액세스는 Single Block I/O이다.

CF의 정도에 따라 다른 손익분기점

)

• CF가 나쁘면 손익분기점은 5%(심하면 1%)미만에서 결정됨
• CF가 아주좋으면 90%수준까지 올라감

인덱스 손익 분기점을 보고 좋은 인덱스를 만드려고 노력해야한다.

그리고 테이블스캔이 항상 나쁜건 아니고 인덱스 스캔이 항상 좋은건 아니다.

온라인 프로그램 튜닝 vs 배치 프로그램 튜닝

온라인 프로그램

• 소량의 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 중요.
• 조인도 NL 조인을 적극 활용 (인덱스를 사용한 조인)
• 인덱스를 이용한 소트연산 생략을 사용하면 빠른 속도를 낸다.

배치 프로그램

• 배치 프로그램에서는 인덱스보다 Full Scan이 효과적
• 하지만 초대용량은 시스템 부담이 크기 때문에, 파티션 활용 전략이 중요한 튜닝 요소이다.
• 병렬 처리까지 더하면 최고닷
• 테이블을 파티셔닝 하는 이유는 Full Scan을 빠르게 하기 위함.

인덱스 컬럼 추가

)

테이블 액세스 최소화를 위해 가장 일반적인 튜닝방법은 인덱스에 컬럼을 추가하는 것이다.
테이블 액세스 단계 필터 조건에 의해 버려지는 레코드가 많을 때, 인덱스에 컬럼을 추가하면 테이블 랜덤 액세스 횟수를 줄일 수 있다.

인덱스만 읽고 처리

테이블 액세스 단계 필터 조건에 의해 버려지는 레코드가 많을땐, 인덱스에 컬럼 추가해서 성능 향상하지만, 만약 버려지는 레코드가 없다면 결국 테이블 랜덤 액세스가 많이 발생해 느릴수 밖에 없다.

이때, 쿼리에 사용된 컬럼을 모두 인덱스에 추가하여 테이블 액세스가 아예 발생하지 않게 할 수 있다. 이것을 Covered 쿼리라고 부르고, 그 쿼리에 사용한 인덱스를 'Covered 인덱스'라고 부른다.

하지만 실제로 추가해야 할 컬럼이 많아서 적용하기 곤란함.

Include 인덱스

Oracle엔 없지만 SQL Server2005에는 있는 기능이다.
인덱스 키 외에 미리 지정한 컬럼을 리프레벨에 함께 저장하는 기능이다.
Include 인덱스는 순전히 테이블 랜덤 액세스를 줄이는 용도로 개발됨.

인덱스 구조 테이블

인덱스를 이용한 테이블 액세스가 고비용 구조임. 그럼 랜덤 액세스가 아에 발생하지 않도록 테이블을 인덱스 구조로 생성한다면?

오라클은 이걸 IOT (Index Organized Table)라고 함. MS-SQL은 '클러스터형 인덱스'라고 함.

IOT (Index Organized Table)

)

)

• 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있다. 인덱스 리프 블록이 곧 데이터 블록이다.
• 힙 구조 테이블에 데이터를 입력할 때는 랜덤방식을 사용, 반면 IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력.
• IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나.
• 랜덤 액세스X 시퀀셜 액세스에 유리
• BETWEEN이나 부등호 조건으로 넓은 범위 읽을 때 유리함.

클러스터 테이블

인덱스 클러스터 테이블

• 클러스터 키값이 같은 레코드를 한 블록에 모아서 저장하는 구조.
• 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당하여 클러스터 체인으로 연결한다.

다중 테이블 클러스터

여러 테이블 레코드를 같은 블록에 저장한다. 이는 SQL Server의 클러서터형 인덱스와는 다르며 IOT에 가깝다.
클러스터 인덱스도 일반 B*Tree를 인덱스 구조를 사용하지만, 테이블 레코드를 일일이 가리키지 않고 해당 키값을 저장하는 첫 번째 데이터 블록을 가리킨다는 점이 다르다.

즉, 일반 테이블의 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖지만, 클러스터 인덱스는 테이블 레코드와 1:M관계를 가진다.

클러스터 인덱스의 키값은 항상 Unique하다.(중복값이 없다)

해시 클러스터 테이블

인덱스를 활용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다.

부분범위 처리

DBMS는 클라이언트에게 데이터를 전송할때 일정량 나누어 전송하는데, 여기서 클라이언트가 매번 추가로 Fetch Call을 한다.

대용량 데이터를 부분범위 처리를 하면 빠르게 결과를 얻을 수 있다.

만약, 아래의 쿼리를 실행하면 데이터를 언제 볼 수 있을까?

select * from flow_user

이 쿼리로 얻는 데이터는 즉각적으로 결과를 받을 수 있다. 그 이유는 DBMS가 데이터를 모두 읽어 한번에 전송하지 않고, 일부 데이터 일정량(Array Size)을 전송하고 멈추기 때문이다.
(데이터를 전송하고 나면 서버 프로세스는 CPU를 OS에 반환하고 대기큐에서 잠을 잠.)

그다음 클라이언트로부터 Fetch Call을 받으면 다시 대기 큐에서 나와 일정량 전송한다.

이렇게 사용자로부터 Fetch Call이 있을때 데이터 일정량을 나누어 전송하는 것이 '부분범위 처리'

전체 범위 처리

만약 아래의 쿼리는 언제 데이터를 볼 수 있을까?

select * from flow_user order by user_id

order by를 하면 user_id로 sort 해야 결과를 받을 수 있기 때문에 이것은 "전체 범위 처리"이다.

• 전체 범위 처리 : 풀 스캔 후 가공하여 Array Size(운반 단위) 만큼 추출되어야 결과를 얻음
• 부분 범위 처리 : 조건을 만족하는 Row 수가 Array Size에 도달되면 결과를 얻음

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

Array Size는 전송해야 할 데이터량에 따라 조절해야한다.

• 모두 전송해야하는 대량의 데이터 : Array Size 크게해서 Fetch Call을 줄인다.
• 앞쪽 일부 데이터만 Fetch하는 데이터 : Array Size를 작게 해서 불필요하게 전송하고 버려지는 데이터를 줄인다.

쿼리툴에서 부분처리

쿼리툴에서 Array Size를 조절하는 옵션이 있다. 쿼리툴마다 다름.

부분범위 처리 구현

책 164 page에 예시가 있는데, 책에서는 프레임워크에 다 구현되어있으니 가져다 쓰라고 한다.

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

OLTP는 'Online Transaction Processing'의 줄임말

• OLTP는 소량의 데이터를 읽고 갱신한다. (항상 소량 데이터만 조회하는 것은 아님)
• OLTP는 특정한 정렬 순서로 상위 일부 데이터만 확인한다.
• 인덱스와 부분범위 처리 원리를 활용하면 OLTP 환경에서 극적인 성능 개선 효과를 얻을 수 있다.

부분범위 처리 유도

쿼리 뒷쪽에 order by를 사용하여 부분처리를 유도하지 못한다면, 인덱스를 사용하여 order by를 생략해 부분 범위 처리를 유도할 수 있다.

-- 전체 범위 처리
select * from [user]
where reg_date between '2020-04-09' and '2020-04-10'
order by reg_date

-- 부분 범위 처리
select * from [user]
with(index(idx_reg_date))
where reg_date between '2020-04-09' and '2020-04-10'

-- 전체 범위 처리
select max(reg_date) from [user]

-- 부분 범위 처리
select top 1 reg_date
from [user] with(index(idx_reg_date))

전체 범위 처리는 전체를 처리하여 레포팅 하는 것에 적합하고 부분 범위 처리는 온라인으로 결과를 출력할때 즉, 반응이 빨라야 할때 사용하는 방법입니다.
부분 범위 처리가 전체 범위 처리 보다 시간이 더 오래 걸릴 수도 있으므로 용도에 맞게 써야 합니다.

배치 I/O

배치 I/O는 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안된 기능이다.

인덱스 탐색

-- 조건절 1 
where C1 = 'B'

-- 조건절 2 (스캔량 줄임)
where C1 = 'B' 
and C2 = 3

-- 조건절 3 (스캔량 줄임)
where C1 = 'B' 
and C2 >= 3

-- 조건절 4 (스캔량 줄임)
where C1 = 'B' 
and C2 <= 3

-- 조건절 5 (스캔량 줄임)
where C1 = 'B' 
and C2 between 2 and 3

-- 조건절 6 (스캔량 줄이는데 역할 못함)
where C1 = between 'A' and 'C' 
and C2 between 2 and 3

인덱스 스캔 효율성

액세스 조건과 필터 조건

액세스 조건

• 인덱스 스캔 범위를 결정하는 조건절이다.
• 스캔 시작점과 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는데 영향을 미치는 조건절이다.

필터 조건

• 테이블로 액세스할지를 결정하는 조건절이다.

옵티마이저의 비용 계산 원리

비용
= 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 액세스 비용
= 인덱스 루트와 브랜치 레벨에서 읽는 블록 수 + 인덱스 리프 블록을 스캔하는 과정에서 읽는 블록 수 + 테이블 액세스 과정에서 읽는 블록 수

비교 연산자 종류와 컬럼 순서에 따른 군집성

테이블과 달리 인덱스는 같은 값을 갖는 레코드들이 군집해 있음.

첫번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.

where C1 = 1 and C1 = 'A' and C3 = '나' and C4 = 'a'

where C1 = 1 and C1 = 'A' and C3 = '나' and C4 >= 'a'

where C1 = 1 and C1 = 'A' and C3 between '가' and '다' and C4 = 'a'

where C1 = 1 and C1 <= 'B' and C3 ='나' and C4 between 'a' and 'b'

인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 생기는 비효율

인덱스 컬럼을 조건절에 모두 등치(=) 조건으로 사용할 때 효율이 가장 좋다.

where 아파트시세코드 = :a
where 아파트시세코드 = :a and 평형 = :b
where 아파트시세코드 = :a and 평형 = :b and 평형타입 = :c
where 아파트시세코드 = :a and 평형 = :b and 평형타입 between :c and :d

반면, 인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위검색 조건이면, 인덱스를 스캔하는 단계에서 비효율이 발생

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

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

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

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

인덱스 선두 컬럼 인터넷매물에 BETWEEN이 사용되어서 나머지 조건을 만족하는 레코드들이 뿔뿔이 흩어진다.

BETWEEN을 IN-List로 전환

• 범위검색 컬럼이 맨 뒤로 가는 인덱스(BETWEEN 조건을 IN-List로 바꾸면 큰 효과를 얻음)
• In-List 개수 만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 '='조건으로 검색
• In-List 개수가 늘어날 수 있다면 BETWEEN 조건을 IN-List로 전환하는 방식은 사용하기 곤란

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

BETWEEN 조건을 IN-List로 전환할 때 주의 사항

아 안돼.. 머.. 멈춰!!!

• IN-List 개수가 많지 않아야 한다.
• 인덱스 스캔과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다.
• BETWEEN 조건 때문에 인덱스를 비효율적으로 스캔하더라도 블록 I/O 측면에서는 대개 소량에 그치는 경우가 있다.

Index Skip Scan 활용

선두 컬럼이 BETWEEN이어서 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져있을 때 Index Skip Scan의 위력이 나타난다.

IN 조건은 ‘=’인가

• IN 조건은 '='이 아니다. IN 조건은 필터 조건이다.

select * from 고객별가입상품
where 고객번호 = :cust_no and 상품id in ('NH00037', 'NH00041', 'NH00050')

select * from 고객별가입상품
where 고객번호 = :cust_no and 상품id = 'NH00037'
union all
select * from 고객별가입상품
where 고객번호 = :cust_no and 상품id = 'NH00041'
union all
select * from 고객별가입상품
where 고객번호 = :cust_no and 상품id = 'NH00050'

• 인덱스를 어떻게 구성하느냐에 따라 성능이 달라진다.
• 인덱스 구성에따라 IN-List Iterator를 사용하냐 안하냐에 따라 성능이 달라질 수 있다. 오히려 IN-List Iterator를 사용하면 성능이 떨어지는 경우도 있다.
• NUM_INDEX_KEY 힌트 : 인덱스 액세스 조건으로 사용한다.
• NUM_INDEX_KEY 힌트의 세번째 인자는 인덱스 n번째 컬럼까지만 액세스 조건으로 사용하라는 의미한다.

/*+ num_index_keys(테이블 컬럼 명 n) */

---

BETWEEN과 LIKE 스캔 범위 비교

select * from 월별고객별판매집계
where 판매월 like '2019%'

select * from 월별고객별판매집계
where 판매월 between '201901' and '201912'

• LIKE와 BETWEEN은 둘다 범위검색 조건이다.
• LIKE와 BETWEEN은 범위검색 조건을 사용할 때의 비효율이 똑같이 적용됨.
• 하지만 데이터 분포와 조건절 값에 따라 인덱스 스캔량이 서로 다를 수 있다.
• LIKE보다 BETWEEN을 사용하는게 낫다.

where 판매월 between '201901' and '201912'
    and 판매구분 = 'B'

where 판매월 like '2019%'
    and 판매구분 = 'B'

where 판매월 between '201901' and '201912'
    and 판매구분 = 'A'

where 판매월 like '2019%'
    and 판매구분 = 'A'

범위검색 조건을 남용할 때 생기는 비효율

• 인덱스 컬럼에 범위 검색 조건을 남용하면 인덱스 스캔 비효율이 생긴다.
• 인덱스 스캔 비효율이 성능에 미치는 영향이 적을 수도 있지만, 대량의 테이블을 넓은 범위로 검색할 때는 그 영향이 매우 클 수도 있다.
• 데이터 분포에 따라 인덱스 컬럼에 대한 비교 연산자를 신중하게 선택해야 한다.

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

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

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

다양한 옵션 조건 처리 방식의 장단점 비교

OR 조건 활용

• 인덱스 액세스 조건으로 사용 불가
• 인덱스 필터 조건으로 사용 불가
• 테이블 필터 조건으로만 사용 가능
• 단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면, 18c부터 인덱스 필터 조건으로 사용 가능

LIKE/BETWEEN 조건 활용

• 인덱스 선두 컬럼 (LIKE/BETWEEN)
• NULL 허용 컬럼 (LIKE/BETWEEN)
• 숫자형 컬럼 (LIKE)
• 가변 길이 컬럼 (LIKE)

UNION ALL 활용

UNION ALL 방식은 옵션 조건 컬럼도 인덱스 액세스 조건으로 사용한다는 사실이 중요하다.

NULL 허용 컬럼이더라도 사용하는데 문제 없음.

유일한 단점은 코딩량이 길어진다.

NVL/DECODE 함수 활용

• 옵션 조건 컬럼을 익데스 액세스 조건으로 사용할 수 있다.
• NVL/DECODE 함수를 여러개 사용하면 그중 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR Expansion이 일어난다.
• 따라서 OR Expansion 기준으로 선택되지 않으면 인덱스 구성 컬럼이어도 모두 필터 조건으로 처리된다.

함수 호출부하 해소를 위한 인덱스 구성

PL/SQL 함수의 성능적 특성

PL/SQL 함수는 개발자들이 일반적으로 생각하는 것보다 매우 느리다.

PL/SQL 사용자 정의 함수가 느린 이유

1. 가상머신상에서 실행되는 인터프리터 언어
2. 호출 시마다 컨텍스트 스위칭 발생
3. 내장 SQL에 대한 Recursive Call 발생 (성능을 떨어뜨리는 가장 결정적이 요소)

효과적인 인덱스 구성을 통한 함수호출 최소화

회원 테이블을 Full Scan 하는 방식으로 읽는것과, 인덱스를 통해 일부 또는 한번만 호출하도록 인덱스 구성을 잘 하면 함수 호출을 최소화 할 수 있다.

select * from 회원 a where 암호화된_전화번호 = encryption(:phone_no)

create index 회원_X03 on 회원 (생년, 암호화된_전화번호);

인덱스 설계가 어려운 이유

인덱스가 많으면 아래와 같은 문제가 생긴다

• DML 성능 저하 (TPS 저하)
• 데이터베이스 사이즈 증가 (디스크 공간 낭비)
• 데이터베이스 관리 및 운영 비용 상승

테이블에 인덱스가 여러개 달려 있으면, 신규 데이터를 입력할 때마다 여러개의 인덱스에도 데이터를 입력해야 한다.

테이블과 달리 인덱스는 정렬 상태를 유지해야 하므로 수직적 탐색을 통해 입력할 브,ㄹ록부터 찾는다. 찾은 블록에 여유공간이 없으면 인덱스 분할(Index Split)도 일어난다.

테이블에 데이터가 변경될 때, 3개의 인덱스에도 똑같이 수정해준다.

• 핵심 트랜잭션이 참조하는 테이블에 대한 DML 성능 저하는 TPS 저하로 이어진다.
• 필요하지 않은 인덱스를 많이 만들면 디스크 공간을 낭비한다.
• 데이터베이스 사이즈가 커지면 백업, 복제, 재구성을 위한 운영 비용도 상승한다.

가장 중요한 두가지 선택 기준

인덱스 스캔 방식에 가장 정상적인 방식은 Index Range Scan이다. 이를 사용하기 위해 선두컬럼을 조건절에 반드시 사용해야 한다.

1. 결합 인덱스를 구성할 때, 조건절을 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.
2. 선정한 컬럼 중 ‘=’ 조건으로 자주 조회하는 컬럼을 앞쪽에 둬야 한다.

스캔 효율성 이외의 판단 기준

그 외 고려해야 할 기준

• 수행빈도 (저자 pick⭐)
  • 자주 수행하지 않는 SQL은 비효율이 조금 있어도 큰 문제가 아니다.
  • 자주 수행되는 SQL은 최적의 인덱스를 구성해줘야 한다.
• 업무상 중요도
• 클러스터링 팩터
• 데이터량
• DML 부하
  (= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신하는 컬럼 포함 여부 등)
• 저장공간
• 인덱스 관리 비용

수행빈도 예시

(사실 여기 이해를 못하겠음..)

수행빈도와 관련해, NL 조인할 때 어느 쪽에서 자주 액세스 되는지도 중요한 판단 기준이 된다.

SELECT /** leadling(a) use_nl(b) */
b.상품코드, b.상품명, a.고객번호, a.거래일자, a.거래량, a.거래금액
FROM 거래 a, 상품 b
WHERE a.거래구분코드 = ‘AC’
AND a.거래일자 BETWEEN ‘20090101’ AND ‘20090131’
AND b.상품번호 = a.상품번호
AND b.상품번호 = ‘가전’

SELECT /** leadling(b) use_nl(a) */
b.상품코드, b.상품명, a.고객번호, a.거래일자, a.거래량, a.거래금액
FROM 거래 a, 상품 b
WHERE a.거래구분코드 = ‘AC’
AND a.거래일자 BETWEEN ‘20090101’ AND ‘20090131’
AND b.상품번호 = a.상품번호
AND b.상품번호 = ‘가전’

• 수행빈도가 매우 높은 SQL이라면, 인덱스를 최적으로 구성해 줘야 한다.
• NL 조인 Inner 쪽 인덱스는 ‘=’ 조건 컬럼을 선두에 두는 것이 중요
• 테이블 액세스 없이 인덱스에서 필터링을 마치도록 구성

데이터량도 인덱스를 설계할 때 중요한 판단 기준이다. 데이터량이 적다면 Full Scan으로도 충분히 빠르기 때문에 굳이 인덱스를 많이 만들 필요가 없다.

반대로, 인덱스를 많이 만들어도 저장 공간이나 트랜잭션 부하 측면에서 그다지 문제될 것이 없다. 테이블이 작으면, 심각하게 고민할 이유가 없다는 뜻이다.

초대용량 테이블은 INSERT도 많다. 초당 DML 발생량은 트랜잭션 성능(TPS)에 직접적인 영향을 준다.

NL 조인

SELECT /*+ ordered use_nl(e) */
   E.EMP_NO, E.ENAME, D.DNAME, E.JOP, E.SAL
FROM DEPT D, EMP E
WHERE E.DEPT_NO = D.DEPT_NO  --- [1]
    AND D.LOC = 'SEOUL'      --- [2]
    AND D.GB = '2'           --- [3]
    AND E.SAL >= 1500        --- [4]
ORDER BY SAL DESC;                   

Answer :: [2] -> [3] -> [1] -> [4]

공식을 초월한 전략적 설계

조건절 패턴이 10개가 있을때, 패턴마다 인덱스를 다 만들 수 없다.

10개중 최적을 달성해야 할 가장 핵심 액세스 경로 한두개를 선택해 최적 인덱스를 설계한다.

나머지 엑세스는 목표 성능을 만족하는 수준으로 인덱스 구성해야 한다.

소트 연산을 생략하기 위한 컬럼 추가

인덱스는 항상 정렬상태라 ORDER BY, GROUP BY를 위한 소트 연산을 생략할 수 있게 해줌. 조건절에 사용하지 않는 컬럼이라도 소트 연산을 생략할 목적 인덱스 구성에 포함시켜 성능개선 가능.

SELECT 계약ID, 청약일자, 입력자ID, 계약상태코드, 보험시작일자, 보험종료일자
  FROM 계약
 WHERE 취급지점ID = :trt_brch_id
   AND 청약일자 BETWEEN :sbcp_dt1 AND :sbcp_dt2
   AND 입력일자 >= TRUNC(SYSDATE - 3)
   AND 계약상태코드 IN (:ctr_stat_cd1, :ctr_stat_cd2, :ctr_stat_cd3)
 ORDER BY 청약일자, 입력자ID

ORDER BY 절 순서대로 『청약일자 + 입력자ID』로 구성하여 소트연산 생략

I/O를 최소화하면서도 소트 연산을 생략하려면, 아래 공식에 따라 인덱스를 구성하면 된다.

1. ‘=’ 연산자로 사용한 조건절 컬럼 선정
2. ORDER BY 절에 기술한 컬럼 추가
3. ‘=’ 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

이 공식에 따라 위에서 제시한 SQL에는 인덱스를 『취급지점ID + 청약일자 + 입력자ID』 순으로 구성

---

결합 인덱스 선택도

인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단기준이다.

• 선택도(Selectivity)
  • 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
  • 카디널리티 = 선택도 * 총레코드 수
• 인덱스 선택도
  • 인덱스 컬럼을 모두 ‘=’으로 조회할 때 평균적으로 선택되는 비율

인덱스를 생성할 때 선택도/카디널리티 를 확인해야 한다.

컬럼 순서 결정 시, 선택도 이슈

컬럼의 액세스 조건이라 어떤 컬럼이 먼저 오든 스캔범위는 같다.

인덱스 설계할 때..

• 항상 사용하는 컬럼을 앞쪽에 두기
• 그중 '=' 조건 앞쪽에 위치 시키는 것 뿐

인덱스 생성 여부를 결정할때는 선택도가 중요하다.

컬럼간 순서를 정할 때는 컬럼의 선택도보다는 ’필수조건 여부’, ‘연산자 형태’가 더 중요한 판단 기준이다.

중복 인덱스 제거

• X01 : 계약ID + 청약일자
• X02 : 계약ID + 청약일자 + 보험개시일자
• X03 : 계약ID + 청약일자 + 보험개시일자 + 보험종료일자

이 인덱스들은 중복이다. 포함 여부를 보면 X03 > X02 > X01 이기 때문에, X03을 남기고 나머지는 지워도 된다.

• X01 : 계약ID + 청약일자
• X02 : 계약ID + 보험개시일자
• X03 : 계약ID + 보험종료일자
• X04 : 계약ID + 데이터생성일시

위의 인덱스들은 중복이 아니나, 계약 ID의 평균 카디널리티가 매우 낮다면 사실상 중복이다. 이를 불완전 중복이라 부름

아래 하나만 만들면 충분하다.

• X01 : 계약ID + 청약일자 + 보험개시일자 + 보험종료일자 + 데이터생성일시

실습1

거래일자, 결제일자는 항상 BETWEEN 또는 부등호로 조회됨

• PK : 거래일자 + 관리지점번호 + 인련번호
• N1 : 계좌번호 + 거래일자
• N2 : 결제일자 + 관리지점번호
• N3 : 거래일자 + 종목코드
• N4 : 거래일자 + 계좌번호

거래일자	2,356
관리지점번호	127
일련번호	1,850
계좌번호	5,956
종목코드	1,715
결제일자	2,356
- 정답1

거래일자가 항상 BETWEEN이면 N3, N4는 둘다 거래일자가 액세스 조건임. 그래서 인덱스를 2개나 만들 필요 없다. N3, N4를 합쳐준다.

- N4 삭제
- N3 : `거래일자 + 종목코드 + 계좌번호` 로 수정

- PK : 거래일자 + 관리지점번호 + 인련번호
- N1 : 계좌번호 + 거래일자
- N2 : 결제일자 + 관리지점번호
- **N3 : 거래일자 + 종목코드 + 계좌번호**
- ~~N4 : 거래일자 + 계좌번호~~

• 정답2하지만 관리지점번호가 선두인 인덱스가 없어 PK를 관리지점, 거래일자로 조회하는 인덱스로 사용한다.
  • PK : 관리지점번호 + 거래일자 + 인련번호
  • N1 : 계좌번호 + 거래일자
  • N2 : 결제일자 + 관리지점번호
  • N3 : 거래일자 + 종목코드
  • N4 : 거래일자 + 계좌번호
• N4를 삭제하고 N3를 그대로 둔다. 계좌번호 관련 조회는 N1을 사용한다.

실습2

• PK : 주소ID + 건물동번호 + 건물호번호 + 관리번호
• N1 : 상태구분코드 + 관리번호
• N2 : 관리번호
• N3 : 주소ID + 관리번호
• 정답그렇게 되면 N2는 중복이 되므로 삭제한다.
  • PK : 주소ID + 건물동번호 + 건물호번호 + 관리번호
  • N1 : 관리번호 + 상태구분코드
  • N2 : 관리번호
  • N3 : 주소ID + 관리번호
• 상태구분코드는 NDV가 낮아 N1인덱스가 사용되지 않음. 그래서 관리번호랑 상태구분코드의 순서를 바꿔주는게 좋다.

인덱스 설계도 작성

인덱스 설계 시 시스템 전체 효율을 고려해야 한다. 조화를 이룬 건축물을 짓기 위해 설계도가 필수인 것처럼 인덱스 설계 시에도 전체를 조망할 수 있는 설계도면이 필요한 이유다.

)