[친절한 SQL 튜닝] 2. 인덱스 기본 [2]

 

2.2.1 인덱스를 사용한다는 것

• 인덱스 컬럼(선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있음
• '인덱스를 정상적으로 사용한다'는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미함, 즉 리프 블록 일부만 스캔하는 Index Range Scan을 의미함
• 인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해야만 함, 즉 일부가 아닌 전체를 스캔하는 Index Full Scan 방식으로 작동함

 

---

 

2.2.2 인덱스를 Range Scan 할 수 없는 이유

인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다.
Index Range Scan에서 'Range'는 범위를 의미한다. 즉, Index Range Scan은 인덱스에서 일정 범위를 스캔한다는 뜻이다.
일정 범위를 스캔하려면 '시작 지점'과 '끝 지점'이 있어야 한다.
 
 
예를 들어, 생년월일 순으로 나열한 학생들이 있어, 2007년 1월에 태어난 학생을 찾으려면 2007년 1월 1일 이후에 태어난 첫 번째 학생을 찾은 후 순서대로 스캔하다가 2007년 2월 1일 이후에 태어난 첫 번째 학생을 만나는 순간 멈추면 된다. 이 경우는 인덱스 수직적 탐색에 해당한다.
 
하지만 년도와 상관없이 5월에 태어난 학생을 찾는 경우엔 스캔 시작점이나 끝 지점을 알 수가 없어 전교생을 다 스캔해야만 한다.
 
데이터베이스에서도 조건절을 처리할 때 같은 문제를 직면한다.
 

WHERE SUBSTR(생년월일, 5, 2) = '05'
WHERE NVL(주문수량, 0) < 100

 
위 조건절을 보면 알 수 있다.
 
가공하지 않은 생년월일과 주문수량을 인덱스로 만들었지만, "5월에 태어난 학생"과 "값이 NULL이면 0으로 치환한 값" 기준으로 쿼리를 작성하면 인덱스 스캔 시작지점을 찾을 수 없다. 그래서 인덱스를 정상적으로 사용할 수 없다.
 

WHERE 업체명 LIKE '%대한%'
WHERE (전화번호 = :TEL_NO OR 고객명 = :CUST_NM)

 
 
LIKE로 중간 값을 검색할 때도 마찬가지다. "대한"으로 시작하는 값은 특정 구간에 모여 있으므로 Range Scan이 가능하지만, "대한"을 포함 하는 값은 전체 구간에 걸쳐 흩어져 있어 Range Scan이 불가능하다.
 
OR도 일반적으로 인덱스를 사용하지는 않지만, use_concat 힌트를 사용하여 UNION ALL로 변환하여 Range Scan이 가능하다.
 

SELECT *
  FROM 고객
 WHERE 고객명 = :CUST_NM -- 고객명이 선두 컬럼인 Index Range Scan
UNION ALL
SELECT *
  FROM 고객
 WHERE 전화번호 = :TEL_NO -- 전화번호가 선두 컬럼인 Index Range Scan
   AND (고객명 <> :CUST_NM OR 고객명 IS NULL)

 
 
IN도 OR와 동일한 기능을 하는데, 마찬가지로 UNION ALL로 표현하면 Range Scan이 가능하다.
IN은 보통 SQL 옵티마이저가 IN-List Iterator 방식을 사용한다.(IN-List 개수만큼 Index Range Scan 반복)
 
 

---

 
 

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

인덱스를 Range Scan할 수 있는 이유는 데이터가 정렬돼 있기 때문이다.
따라서 소트 연산 생략 효과도 부수적으로 얻을 수 있게 된다.
 
[장비번호+변경일자+변경순번] 순으로 구성한 상태변경이력 테이블이 있다고 하자.
장비번호, 변경일자, 변경순번 순으로 정렬이 되어 있다고 보면 아래와 같이 장비번호와 변경일자를 모두  "=" 조건으로 검색할 때 PK 인덱스를 사용하면 결과 집합은 변경순번 순으로 출력된다.
 

SELECT *
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
   AND 변경일자 = '20180316'

 
옵티마이저는 이런 속성을 활용해 SQL에 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않는다. PK 인덱스를 스캔하면서 출력한 결과집합은 어차피 변경순번 순으로 정렬되기 때문이다.
만약 정렬 연산을 생략할 수 있게 인덱스가 구성돼 있지 않다면, SORT ORDER BY 연산 단계가 추가된다.
 
내림차순(DESC) 정렬에도 인덱스를 활용할 수 있다.
인덱스 리프 블록은 양방향 연결 리스트 구조이기 때문에 오름차순 정렬일 때는 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색한 후 우측으로 수평적 탐색을 한다.
 
반대로 내림차순 정렬일 때는 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 수평적 탐색을 한다.
 
 

---

 

2.2.5 ORDER BY 절에서 컬럼 가공

 
조건절이 아닌 ORDER BY 또는 SELECT-LIST 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 있다.
 
예를 들어 상태변경이력 PK 인덱스를 [장비번호+변경일자+변경순번] 순으로 구성했다면, 아래 SQL도 정렬 연산을 생략할 수 있다.
 

SELECT *
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
 ORDER BY 변경일자, 변경순번

 
ORDER BY 절을 가공할 경우는 정렬 연산을 생략할 수 없다.
 

SELECT *
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
 ORDER BY 변경일자||변경순번

 
인덱스에는 가공하지 않은 상태로 값을 저장했는데, 가공한 값 기준으로 정렬을 할 경우에 문제가 발생한다.
 
 

---

 

2.2.6 SELECT-LIST에서 컬럼 가공

 
인덱스를 [장비번호+변경일자+변경순번] 순으로 구성하면, 아래와 같이 변경순번 최소값을 구할 때도 옵티마이저는 정렬 연산을 따로 수행하지 않는다. 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번째 읽은 레코드가 바로 최소값이기 때문이다.
 

SELECT MIN(변경순번)
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
   AND 변경일자 = '20180316'

 
 
변경순번 최대값을 구할 때도 마찬가지로 정렬 연산을 수행하지 않는다. 최소값을 찾아 수직적 탐색할 때 왼쪽으로 내려갔다면, 최대값을 찾을 때는 오른쪽으로 내려가는 점만 다르다.
 

SELECT MAX(변경순번)
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
   AND 변경일자 = '20180316'

 
 
아래와 같이 인덱스는 문자열 기준으로 정렬돼 있는데, 이를 숫자값으로 바꾼 값 기준으로 작성하면 정렬 연산을 생략할 수 없다.
 

SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)    -- 숫자값으로 가공되어 정렬 연산 생략 불가
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
   AND 변경일자 = '20180316'
   
 ----------------------------------------------------------------------------------
 
SELECT NVL(TO_NUMBER(MAX(변경순번)), 0)    -- 정렬 연산 없이 최종 변경순번 출력 가능 
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
   AND 변경일자 = '20180316'

 
 

---

 

2.2.7 자동 형변환

 
고객 테이블에 생년월일이 선두 컬럼인 인덱스가 있다고 하자. 아래 SQL은 생년월일 컬럼을 조건절에서 가공하지 않았는데도 옵티마이저는 테이블 전체 스캔을 선택한다.
 

SELECT *                                
  FROM 고객
 WHERE 생년월일 = 19821225    -- 숫자값으로 가공되어 Index Range Scan 불가능
 
 -----------------------------------------------------------------------------
 
 SELECT *
   FROM 고객
  WHERE TO_NUMBER(생년월일) = 19821225 -- 문자형이므로 Index Range Scan 가능