[DB] SQL 튜닝 part 2 - 인덱스 구조 및 사용법

인덱스
대용량 테이블에서 필요한 데이터만 빠르게 엑세스 하기 위해 사용하는 오브젝트
인덱스는 구성하는 컬럼 순서를 기반으로 정렬되기 때문에 범위 스캔이 가능하다.

구조
B-tree 구조로 되어 있어 리프노드까지 빠르게 접근할 수 있고 데이터 정렬 순서에 따라 다음 데이터 리프노드와 연결.
상위 노드는 하위 노드들을 가르키는 주소값을 가지고 있고, 리프노드는 테이블 레코드의 주소값을 가지고 있다.

Range Scan
조건절에서 인덱스 컬럼을 사용하면 정렬된 인덱스 트리를 스캔하면서 조건을 만족하는 데이터만 골라서 엑세스한다.
그래서 조건에 만족하지 않는 인덱스 내 탐색 및 데이터 레코드 엑세스를 줄여서 응답속도를 높여준다.
반면에 인덱스를 구성하는 컬럼을 조건으로 사용하지 않거나 인덱스 구성 컬럼 중에서 선두 컬럼을 조건절에서 적절히 사용하지 않는다면 인덱스 트리 스캔하더라도 Range Scans이 아닌 Full Scan을 하게 되므로 인덱스 생성과 사용에 유의해야 한다.

유의사항
SQL문을 작성할 때, 조건절에서 인덱스 컬럼을 가공하면 쿼리가 수행 되면서 가공되는 값에 맞는 새로운 인덱스를 생성해서 사용하기 때문에 기존에 생성된 인덱스를 사용하여 얻는 빠른 범위 스캔을 할 수 없게 된다.
그러나 후 순위 컬럼이 가공되어 조건절에 사용 되어도 선두 컬럼이 가공되지 않으면 선두 컬럼의 조건에 한하여 범위 스캔이 실행계획에 포함된다.

인덱스 컬럼 가공의 예

중간 값 검색	SELECT * FROM TEST WHERE NAME LIKE ‘%am%’;
컬럼 변형	SELECT * FROM TEST WHERE SUBSTR(NAME,1,2) = ‘na’ SELECT * FROM TEST WHERE NVL(COUNT,0) < 100 SELECT * FROM TEST WHERE NAME || TYPE = ‘nametype’
부정형 비교	SELECT * FROM TEST WHERE NAME <> ‘name’
OR 조건	SELECT * FROM TEST WHERE NAME = ‘name’ or TYPE= ‘type’
NULL 비교	SELECT * FROM TEST WHERE NAME IS NULL

인덱스 컬럼과 Null 비교하는 방법
Not Null 제약이 있는 컬럼과 함께 인덱스를 구성
다른 인덱스 구성컬럼의 IS NULL이 아닌 조건 사용

부정형 비교 해결방법
부정형 비교(‘<>’ , ’not in’, ‘not null’ 등)가 긍정형으로 변경가능한 경우 긍정형으로 비교한다.
컬럼의 값 종류가 소수이고 고정적이면 in list 비교로 변경하거나 변경 가능성이 있다면 동일한 값을 얻을 수 있는 Table과 Join하는 방법이 있다.

자동 형변환
비교되는 두 값의 데이터 타입이 다르면 묵시적으로 형변환이 발생하여 인덱스 컬럼이 가공되어 Range Scan이 안되는 경우도 있다.
따라서 조건절에서 컬럼의 값과 비교하고자 하는 데이터는 컬럼의 유형과 동일한 데이터 유형으로 전달하는 것이 중요하다.

가공된 인덱스 컬럼 해소 예시

인덱스 컬럼 가공	인덱스 컬럼 가공 X
SELECT * FROM TEST WHERE SUBSTR(COL1, 1,2) = “te”	SELECT * FROM TEST WHERE COL1 LIKE ‘te%’
SELECT * FROM TEST WHERE COL1 * 12 = 3600	SELECT * FROM TEST WHERE COL1 = 3600 / 12
SELECT * FROM TEST WHERE FLOOR(COL1) < :val	SELECT * FROM TEST WHERE COL1 < CEIL(:val)
SELECT * FROM TEST WHERE TO_CHAR(COL1, ‘YYYYMMDD’) = :dt	SELECT * FROM TEST WHERE COL1 >= TO_DATE(:dt,’YYYYMMDD’) AND COL1 < TO_DATE(:dt, ‘YYYYMMDD’)+1
SELECT * FROM TEST WHERE COL1 || COL2 = ‘testtest2’	SELECT * FROM TEST WHERE COL1 = ‘test’ AND COL2 = ‘test2’
SELECT * FROM TEST WHERE NVL(COL1,0) <> 0	SELECT * FROM TEST WHERE COL1 <> 0
SELECT * FROM TEST WHERE NVL(COL1,0) >= 100	SELECT * FROM TEST WHERE COL1 >= 100

인덱스를 이용한 소트 연산 생략
인덱스는 생성할 때 명시된 컬럼의 순서에 따라 정렬된다는 점을 활용한다.
ORDER BY 조건에 인덱스의 구성 컬럼 순서대로 명시 했을 경우 SORT 연산이 생략된다.
그러나 인덱스 컬럼을 가공할 경우, WHERE 조건절과 마찬가지로 미리 생성해 놓은 인덱스를 사용할 수 없기 때문에 SORT 연산이 수행된다.


인덱스 스캔의 종류

• Index Range Scan
  B-tree 인덱스의 가장 일반적인 형태의 엑세스 방식
  루트에서 리프 블럭까지 수직적 탐색 후, 필요한 범위만 스캔
  인덱스 선두 컬럼이 조건절에 사용되어야 범위 스캔 가능
  
• Index Full Scan
  수직적 탐색을 하지 않고 수평적으로 인덱스 전 범위 탐색
  인덱스 선두컬럼이 조건절에 없으면 Table Full Scan이지만 후위 컬럼들이 인덱스로 구성되어 있을 경우, 대부분의 테이블 레코드는 필터링 되고 일부 데이터만 엑세스
  
• Index Unique Scan
  인덱스 B-tree에서 수직적 탐색으로만 데이터를 찾는 스캔 방식
  인덱스 키 컬럼을 모두 ‘=‘ 조건으로 검색할 때는 조건에 적합한 리프 블럭으로 바로 접근하여 최소한의 범위만 스캔
  
• Index Range Scan Descending
  조건에 만족하는 리프 블럭들의 뒤에서부터 읽어서 내림차순 정렬로 데이터 확인
  
• Index Skip Scan
  조건절에 인덱스 선두 컬럼 없어도 인덱스를 사용하는 스캔방식
  선두 컬럼의 Distinct Value 수가 적고 후행 컬럼의 Distinct Value 수가 많을 때 유용
  루트에서 스캔하면서 리프 블럭으로 도달한 후, 구간을 건너 뛰면서 레코드 스캔
• Index Fast Full Scan
  인덱스 트리구조를 무시하고 세그먼트 전체를 Multiblock Read
  디스크로부터 대량의 인덱스 블록을 읽어야할 때 효과적
  쿼리에 사용되는 모든 컬럼이 인덱스 컬럼에 포함돼 있을 때만 가능