'친절한 SQL 튜닝' 요약정리 2탄: 인덱스의 기본

(주)디비안 조시형 대표님의  '친절한 SQL 튜닝의 바이블'  

구입하시길 강력 추천드립니다.

책 구입을 원하시는분은 요기를 클릭하시면 됩니다.

-저자 소개
現) 주식회사 디비안 대표이사 
오라클 성능 고도화 원리와 해법 1, 2 저자 
국가공인 SQLP, DAP 자격검정 전문위원
한국데이터진흥원과 한국DB산업협의회가 공동 주관하는 제1회 우수DB人상을 수상
인터넷 카페 디비안 포럼(WWW.DBIAN.NET) 운영
前) 이랜드, 인슈넷, 디엠에스랩, 엔코아, 비투엔


하단의 내용은 제가 예전에 읽었던 내용을 요약 정리한 것입니다.

 

1. 인덱스 튜닝의 두가지 핵심요소

 

1) 인덱스 스캔 효율화 튜닝

-인덱스 스캔과정에서 발생하는 비효율을 줄이는 것

-인덱스 엑세스 조건(수직적 탐색 ,수평적 탐색)

 

인덱스 선행 컬럼이 조건절이 없거나 =조건이 아니면 인덱스 스캔 과정에 비효율이 발생함

*선두 컬럼은 인덱스 구성상 맨 앞쪽에 있는 컬럼을 지칭할 때 사용하고 선행컬럼은 어떤 컬럼보다 상대적으로 앞쪽에 놓인 컬럼을 지칭 

(조건절 컬럼들의 인덱스가 다 있다는 전제하에) 첫번째 나타나는 범위검색 조건까지(요기까지는 레코들이 서로 군집해있음)  (or 선행컬럼이 없는 앞까지)가  인덱스 엑세스 조건(인덱스 스캔범위를 결정)이고 나머지는 필터 조건임(테이블로 엑세스)

 

인덱스 선정 조건

인덱스 생성 여부를 결정할때는 선택도가 매우 중요하지만 컬럼 간 순서를 결정할 때는  각 컬럼의 선택도보다 필수조건 여부, 연산자 형태가 더 중요한 판단 기준이다.

조건절에 항상 사용하거나 자주 사용하는 컬럼을 선정한다

'=' 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.

 

2) 랜덤엑세스 최소화 튜닝 (테이블의 엑세스 횟수를 줄이는 것)<-- 더 중요함!!!

-인덱스 스캔 후 테이블 레코드를 엑세스 할 때 랜덤 I/O 방식으로 사용하므로 이를 랜덤 액세스 최소화 튜닝이라고함.

-인덱스 필터 조건

 

 

SQL 튜닝은 랜덤 I/O와의 전쟁임!!!

 

 

 

 

2. 인덱스 구조

 

1) 범위 스캔이 가능한 이유는 인덱스가 정렬되어 있기 때문이다. 일반적으로 DBMS는 B TREE 인덱스를 사용

 

 

2) 인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나눌 수 있음

 

-인덱스 수직적 탐색(인덱스 스캔 시작지점을 찾는 과정)

정렬된 인덱스 레코드 중 조건을 만족하는 첫번째 레코드를 찾는 과정이다. 즉 인덱스 스캔 시작 지점을 찾는 과정

 

 

-인덱스 수평적 탐색(데이터를 찾는 과정)

수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔함

인덱스를 수평적으로 탐색하는 이유는 조건절을 만족하는 데이터를 모두 찾기위해  Rowid를 얻기 위해

 

 

-결합인덱스 구조와 탐색

 

 

select 이름, 성별
from 사원
where 성별 = ‘여자’
and 이름 = ‘유관순’

1)인덱스를 [성별 + 이름] 순으로 구성한 경우

총 사원 50명 중에서 성별 = ‘여자’인 레코드 25건을 찾고, 거기서 이름을 검사해 최종적으로 2명 출력

->25번의 검사

2) 인덱스를 [이름 + 성별] 순으로 구성한 경우

총 사원 50명 중에서 이름 = ‘유관순＇인 레코드 2건을 찾고, 거기서 성별을 검사해 최종적으로 2명 출력

->2번의 검사

선택도가 낮은 "이름" 컬럼을 앞쪽에 두고 결합인덱스를 생성해야 검사횟수 줄일 수 있어 성능에 유리하다

 

<===위에 있는 말은 틀린말이다. 인덱스 탐색 과정을 마치 엑셀의 데이터 필터 기능처럼 이해한거임

 

인덱스를 위 어떤 경우로 구성하든 읽는 인덱스 블록 개수는 똑같다.

 

 

DBMS가 사용하는 B tree 인덱스는 엑셀처럼 평면 구조가 아니고 다단계 구조임 

루트에서 브랜치를 거쳐 리프 블록까지 탐색하면서 '여자'이면서 '유관순'인 첫 번째 사원을 바로 찾아간다.

거기서부터 두건을 스캔한다. 정확히 말하면 유관순이 아닌 레코드를 만날때까지 세건을 스캔한다.

인덱스를 이름+성별 순으로 구성해도 마찬가지다. 따라서 어느 컬럼을 앞에 두든 일량에는 차이가 없다.

 

https://richardfoote.wordpress.com/2008/02/13/its-less-efficient-to-have-low-cardinality-leading-columns-in-an-index-right/

 

https://richardfoote.wordpress.com/about

 

 

 

 

 

3.인덱스 기본 사용법

 

- 색인에서  맨앞 단어로 찾으니깐 빨리 찾는건데 색인의 중간값으로 찾으면  의미가 없어짐

- 색인이 정렬돼 있더라도 가공한 값이나 중간값으로는 스캔 시작점을 찾을 수 없음

- index range scan이라함은 인덱스에서 일정 범위를(시작지점과 끝지점) 스캔한다는 뜻

- 인덱스 컬럼을(조건절에 사용한 컬럼) 가공하면 인덱스를 정상적으로 사용할 수없음(컬럼가공, 중간값 검색 like, or) 

 

 

ex)

 

-where substr(생년월일, 5,2)=’08’

where to_number(생년월일)=19811212 자동 형변환.

생년월일이 문자형인데 조건절 비교값을 숫자형으로 표현했기 때문에 range scan이 불가함

 

 

-where 업체명 like %삼성%

삼성으로 시작하는 값이 특정 구간에 모여 있으면 range scan이 가능하지만 삼성을 포함하는 값은 전체 구간에 걸쳐 흩어져있어 range scan이 불가함

 

-where ( 전화번호 = :tel _num OR 고객명 =:name)

위와 같이 or 조건으로 검색할 때 수직적 탐색을 통해 전화번호가 01032225555이거나 고객명이 홍길동인 어느 한 시작지점을 바로 찾을 수 없어서 Range scan이 불가

where 전화번호 in( tel1, tel2)

In 조건은 or 조건을 표현하는 다른 방식일 뿐이다. 그러나 SQL 옵티마이저가 In-List-Iterator 방식을 사용한다.In List 개수만큼 index range scan을 반복하는 것이다. 이를 통해 SQL을 union all 과 같이 변환한것과 같은 효과를 얻을 수 있다.

 

위 쿼리를 union all 로 2개로 쪼개서 붙이면됨

xx where  전화번호 = :tel _num   union all xx where 고객명 =:name

or 조건을 SQL 옵티마이저가 위와 같은 형태로 변환 할 수 있는데, 이를 or expansion이라고 함

 

 

 

 

 

4.인덱스 사용조건

 

인덱스 :소속팀+사원명_연령

select xxx from 사원 where 사원명 =’홍길동’

인덱스를 소속팀으로 정렬하고 소속팀이 같으면 사원명순으로 정렬하고 사원명까지 같으면 연령순으로 정렬한다는의미

레인지 스캔불가! 소속팀이 선두 인덱스라 이름이 같은 홍길동은 막 흩어져있음.

인덱스를 range scan 하기위한 가장 첫번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다는 사실

 

 

 

 

5.인덱스를 이용한 소트 연산 생략

 

ex)

 

-인덱스 :pk 장비버호+변경일자+변경순번

where 장비번호=c and 변경일자 20180316   (order by 변경순번)

order by 없어도  장비번호 변경일자는 fix 되서 변경순번순으로 정렬되있음.

 

-오름차순 정렬일때는 가장 작은 값을 찾아 좌측으로 수직적 탐색 -> 우측으로 수평적 탐색

내림차순 정렬일때는 가장 큰 값을 찾아 우측으로 수직적 탐색 ->좌측으로 수평적 탐색.

 

-인덱스 :pk 장비번호+변경일자+변경순번

where 장비번호=c and 변경일자 20180316   order by 변경순번 desc

이렇게 해도 실행계획에 sort order by 연산이 없음 index range scan 단계에 descending이라고 표시됨

 

 

-인덱스 :pk 장비번호+변경일자+변경순번

where 장비번호=c and 변경일자= 20150202   order by 변경순번 || 변경순번

order by 절에서 컬럼 가공했기에 정렬연산 들어감

 

-select To_char(A.주문번호, ‘FMxxx’) as 주분번호 from 주문 A where… order by 주문번호

이것도 To_char(A.주문번호, ‘FMxxx’) 이걸 가르키기에 정렬연산 들어감.

해결방안은 by 주문번호 대신 by A.주문번호 하면됨.

 

-select -list에서 컬럼 가공

인덱스 :pk 장비버호+변경일자+변경순번

select Min(변경순번) where 장비번호=c and 변경일자 20170202  

정렬 연산 수행 안함. 수직적 탐생을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫번째 읽은 레코드가 바로 최소값임

 

-select max(변경순번)

where 장비번호=c and 변경일자 20170202

정렬 연산 수행 안함. 수직적 탐생을 통해 조건을 만족하는 가장 오른쪽 지점으로 내려가서  첫번째 읽은 레코드가 바로 최대값임

select nvl(max(to_number(변경순번)),0)  정렬연산 생략 불가

select nvl(to_number(max(변경순번)),0)  정렬연산 생략 가능

 

 

 

 

6.인덱스 확장 기능 사용법

 

인덱스 스캔방식

 

1) index range scan

인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후 필요한 범위만 스캔함

 

 

2) index full scan

수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식임

 

인덱스 (ename, sal)

where sal>2000 order by ename.

인덱스 선두컬럼인 ename가 조건절에 없음

table full scan vs  index full scan 

index full scan은 대게 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택됨

데이터 저장 공간은 가로*세로  즉 컬럼 길이 * 레코드 수에 의해 결정되므로 인덱스가 차지하는 면적은 테이블보다 훨씬 작기때문.

 

인덱스 (ename, sal)

where sal>9000 order by ename.

인덱스 선두컬럼인 ename가 조건절에 없음

sal 컬럼이 선두인 인덱스를 생성해 주는 것이 좋음

 

 

3) index unique scan

 

- 수직적 탐색만으로 데이터를 찾는 스캔방식으로 unique인덱스를 = 조건으로 탐색하는 경우에 작동함

- Unique인덱스라고해도 범위조건 (between ,부등호, like)검색할때는 index range scan으로 처리됨

- 또한 unique 결합 인덱스에 대해 일부 컬럼만으로 검색할때도 index range scan 이 나타남

- 주문상품 pk인덱스를 (주문일자+고객id+상품id)로 구성했는데 주문일자와 고객id로만 검색하는 경우 range scan 

 

 

4) index skip scan

 

- 인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 table full scan을 선택함

- table full scan 보다 i/o를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면 index full scan을 사용하기도 함

 

 

5) index range scan descending

 

인덱스 (ename)

인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과 집합을 얻는다는 점만 다름

select * from emp where empno>0 order by empno desc