[SQL 튜닝] 인덱스 기본

 

인덱스 : 테이블에서 데이터를 검색할 때 사용

 

인덱스 튜닝의 핵심요소

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

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

2. 랜덤 액세스 최소화 튜닝(랜덤 I/O 방식을 사용)

- 테이블 엑세스 횟수를 줄이는 것

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

 

인덱스 구조

- 인덱스를 활용하면 일부만 읽고 멈출 수 있는 '범위 스캔'이 가능함

- DBMS는 B-Tree 인덱스를 사용

- B-Tree는 나무를 거꾸로 뒤집은 모양으로 뿌리가 위쪽에 있고 가지를 거쳐 맨 아래에 잎사귀가 있음

 

인덱스 수직적 탐색

- 조건을 만족하는 첫 레코드를 찾는 과정, 인덱스 스캔시작지점을 찾는 과정

- 루트를 포함한 브랜치 블록은 푯말과 같은 역할을 함.

인덱스 수평적 탐색

- 찾고자 하는 데이터가 더 안나타날 때까지 인덱스 리프 블록을 수평적으로 스캔

- 본격적으로 데이터를 찾는 과정

- 양방향 연결 리스트 구조

- 조건절을 만족하는 데이터를 찾고, ROWID를 얻기 위해 수행

 

결합 인덱스 구조

- 엑셀의 필터기능과 다름 -> DBMS는 B-Tree 구조이기 때문에 평면구조가 아니기 때문.

- [이름 + 성별] 이나 [성별 + 이름] 이나 성능은 차이가 없다.

 

인덱스 사용

- 인덱스 칼럼(선두칼럼)을 가공하지 않아야 인덱스를 정상 사용 가능

- 정상적으로 사용한다는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서 부터 스캔하다가 중간에 멈추는, 리프 블록 일부만 스캔하는 Index Range Scan을 의미한다.

- 만약 인덱스 컬럼을 가공하면, 일부가 아닌 전체를 스캔하는 Index Full Scan방식으로 작동

 

인덱스를 가공하면 Range Scan(정상 사용)할 수 없는 이유

- 인덱스 스캔 시작점을 찾을 수 없기 때문

- IN 조건절에서는 특정 지점을 찾기 어렵지만, UNION ALL방식으로 작성하면 각 브랜치 별로 인덱스 스캔 시작점을 찾을 수 있음(?)

- IN 조건절에 대해서는 SQL 옵티마이저가 IN-List Iterator 방식(IN-List 개수만큼 Index Range Scan을 반복)을 사용

- OR 또는 IN 조건절은 옵티마이저의 쿼리변환 기능을 통해 Index Range Scan으로 처리되기도 함.

 

가장 중요한 조건

- 인덱스 선두 칼럼이 조건절에 있어야 함

- 인덱스를 Range Scan하려면 인덱스 선두 칼럼이 가공되지 않은 상태로 조건절에 있어야함

 

Sort 연산 생략

- 테이블과 달리 인덱스는 정렬되어 있기 때문에 소트 연산 생략 효과도 얻을 수 있다.

- 인덱스가 소트 연산을 생략할 수 있게 구성되어 있지 않으면 Order by 문구를 써야 한다.

 

Select-List에서 컬럼 가공(?)

- Nvl(Max(to_number(순번)),0) 보다 Nvl(to_number(MAX(순번)),0) 이 성능이 좋다. 그 이유는 To_number, To_char같은 함수는 칼럼 전체에 적용되지만, Min, MAX는 해당되는 값을 찾으면 멈추기 때문이다.

 

자동 형변환

- 오라클은 다른 타입으로 되어있는 값을 비교하면 자동으로 형변환을 해주는데, 이때 숫자형과 문자형이 만나면 숫자형이 이긴다. 또한 날짜형과 문자형이 만나면 날짜형이 이기지만, 왠만하면 날짜 포맷을 정확히 지정해 주어야 한다.

- 연산자가 LIKE일때는 LIKE자체가 문자열 비교 연산자이므로 이때는 문자형 기준으로 숫자형 칼럼이 변환

- MYSQL도 자동형변환 기능이 있다.

- 하지만 자동형변환에 의존하다 보면 오류가 많이 발생할 수 있기 때문에 인덱스 칼럼 기준으로 반대편 칼럼 또는 값을 정확히 형변환해 주어야 한다.

 

인덱스 확장 기능

1. Index range scan : 인덱스루트에서 리프블록까지 수직적을 탐색한 후에 필요한 범위만 스캔

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

3. Index Unique scan : 수직적 탐색만으로 데이터를 찾는 스캔방식으로서 Unique인덱스를 '=' 조건으로 탐색하는 경우에 작동

4. Index Skip Scan : 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔방식, 조건절에 빠진 인덱스 선두컬럼의 Distinct value 개수가 적고 후행컬럼의 Distinct Value개수가 많을때 유용

5. Index Fast Full Scan : 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O(물리적) 방식으로 스캔하기 때문

6. Index Range Scan descending : 인덱스를 뒤에서부터 앞으로 스캔하여 내림차순으로 정렬된 결과집합을 얻는다는 점