인덱스 기본 (2) - 기본 사용법

 

인덱스 기본 사용법은 인덱스 Range Scan 하는 방법을 의미한다. 

인덱스 Range Scan을 할 수 없게 되는 이유를 알고 나면, Rnage Scan 하는 방법도 자연스럽게 터득한다.

 

1. 인덱스를 사용한다는 것 

 

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

색인을 아예 사용할 수 없는 것은 아니나, 가공한 값이나 중간값 색인으로 검색하기 위해선 색인 전체를 스캔해야 한다.

 

데이터베이스 세계에서도 이는 마찬가지로, 인덱스 칼럼(선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.

인덱스를 정상적으로 사용한다는 것은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다 중간에 멈추는 것을 의미한다. 

즉 리프 블록 일부만 스캔하는 것이 Index Range Scan이다. 

이때 컬럼을 가공한다면, 스캔 시작점을 찾을 수 없고, 멈출 수 없어 리프 블록 전체를 스캔해야 한다. 

즉, 일부가 아닌 전체를 스캔하는 Index Full Scan 방식으로 작동한다.

 

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

 

인덱스 칼럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다.

Index Range Scan은 인덱스에서 일정 범위를 스캔한다는 것이다. 일정 범위를 스캔하려면 시작과 끝을 알아야 한다.

따라서 인덱스가 걸린 조건절을 가공하면, 시작점을 찾기 못하고 Range Scan이 불가능하다.

where 업체명 like '%대한%'

where (전화번호 = tel_no OR 고객명 = cust_nm)

 

LIKE나 OR을 사용하는 경우도 시작점을 찾을 수 없기 때문에 Range Scan을 할 수 없다.

다만 OR 연산은 아래와 같이 UNION ALL을 사용하면, Range Scan이 가능하다! (이때 인덱스는 고객명, 전화번호 선두인 인덱스가 각각 존재해야함)

SELECT *
FROM 고객
WHERE 고객명 = cust_nm
union all
SELECT *
FROM 고객
WHERE 전화번호 = tel_no
AND (고객명 <> : cust_nm or 고객명 is null)

 

OR이 안되기 때문에 같은 이유로 IN연산도 불가능하다. 다만 , UNION ALL로 작성하면 이 또한 가능하긴하다.

그래서 IN 조건절에 대해서 SQL 옵티마이저가 IN-List Iterator 방식을 사용한다. In-List 개수만큼 Index Range Scan을 반복하는 것이다. 이를 통해 UNION ALL과 같은 효과를 얻을 수 있다. 

친절한 SQL튜닝

*OR이나 IN이 조건으로 사용되었을 때,  

 - OR 혹은 IN이 걸려있는 컬럼이 선두 컬럼이라면, OR-Expansion 혹은 IN-List Iterator가 사용된다. (UNION ALL 방식으로 쿼리변형)

 - 선두에 = 조건이고 인덱스 후행 컬럼에 OR이나 IN 조건이 사용되면, Index Filter 조건으로 사용된다. 

3. 인덱스 사용 조건

 

(1) 인덱스 선두 컬럼이 가공하지 않은 상태로 조건절에 있어야 한다. 

친절한 SQL튜닝

 

위와 같이 구성된 인덱스에서 사원명 = '홍길동'으로 조회할 때, INDEX RANGE SCAN이 작동하지 않는다.

소속팀,사원명,나이 순서로 정렬되어 있기 때문에, 시작지점을 찾을 수 없고, 흩어져있는 홍길동은 리프블록에서 모두 찾아야 하기 때문이다. 

 

--인덱스
select * from TAX1234
WHERE 기분연도 = stdr_year
and substr(과세구분코드,1,4) = txtn_dcd
--생략

 

위 쿼리도 Index Range Scan이 가능하다! 선두 컬럼이 가공되지 않은 상태로 조건절에 있기 때문이다.

물론 Index Range Scan을 한다고 무조건 성능상 우위에 있는 것은 아니다.

인덱스를 정말 잘 타는지는 인덱스 리프 블록에서 스캔하는 양을 따져봐야 알 수 있다. 

---

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

 

인덱스를 Range Scan 할 수 있는 이유는 데이터가 정렬돼 있기 때문이다. 

찾고자 하는 데이터가 정렬된 상태로 모여있기 때문에 전체가 아닌 일정 부분만 읽다가 멈출 수 있다.

인덱스가 정렬되어 있기 때문에 소트 연산 생략 효과를 부수적으로 얻게 된다. 

 

아래와 같이 장비번호+변경일자+변경순번 순으로 구성한 상태변경이력 테이블이 있다고 해보자

친절한 SQL튜닝

PK 인덱스에서 장비번호,변경일자가 같은 레코드는 변경순번으로 정렬돼 있다.

장비번호와 변경일자를 모두 = 조건으로 검색해 PK 인덱스를 사용하면, 결과집합은 변경순번으로 출력된다.

이런 속성을 이용해 SQL에 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않는다.

 

만약 정렬 연산을 생략할 수 있게 인덱스가 구성돼 있지 않다면, 아래와 같이 SORT ORDER BY 연산 단계가 추가된다.

친절한 SQL튜닝 내

내림차순 정렬에도 인덱스를 활용할 수 있다. 이럴 때도 ORDER BY를 생략하고 실행계획에 INDEX RANGE SCAN DESCENDING이라고 표시된다.

친절한 SQL튜닝

 

---

5. ORDER BY 절에서 칼럼 가공

 

WHERE절 뿐 아니라, ORDER BY 또는 SELECT-LIST에서 컬럼을 가공하여 인덱스를 정상적으로 사용할 수 없는 경우도 종종있다.

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

 

위와 같이 ORDER BY절을 가공하면, SORT 연산을 생략할 수 없다. 

 

SELECT *
FROM (

	SELECT TO_CHAR(A.주문번호, 'FM00000') AS 주문번호, A.업체번호, A.주문금액
    FROM 주문 A
    WHERE A.주문일자 = dt
    AND A.주문번호 > NVL(--..생략)
    ORDER BY 주문번호
)
ORDER BY ROWNUM <= 30

 

SELECT절에 가공된 칼럼을 기준으로 ORDER BY를 진행해도 SORT 연산을 생략할 수 없다. 

 

*TO_CHAR함수에 FM000000 옵션을 사용하면, 첫 번째 인자에 입력한 숫자값을 0으로 시작하는 여섯자리 문자열로 반환해 준다.

 

이럴땐 ORDER BY에 테이블 ALIAS를 붙여주기만 하면 된다. 

 

---

6. SELECT-LIST에서 칼럼 가공 

 

인덱스를 장비번호 + 변경일자 + 변경순번순으로 구성하면, 최소나 최대값을 구할 때도 정렬 연산을 따로 수행하지 않는다.

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

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

 

인덱스를 이용해 이처럼 정렬 연산 없이 최소 또는 최대값을 빠르게 찾을 때 아래와 같은 실행계획이 나타난다. 

친절한 SQL튜닝

다음과 같은 경우에는 정렬 연산 생략을 사용할 수 없다.

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

 

변경순번을 가공한 값에 MAX를 걸었기 때문이다. 이럴 땐 TO_NUMBER(MAX(변경순번))로 순서를 바꿔서 정렬을 생략하도록 유도하자

 

최종 변경일자에 더해 최종 변경순번까지 출력하려면 아래와 같이 SQL을 작성할 수 있다. 

SELECT 장비번호, 장비명, 상태코드
,(SELECT MAX(변경일자) --생략)
, (SELECT MAX(변경순번) --생략)
--생략

 

하지만, 상태변경이력 테이블을 여러 번 읽어야 하므로 비효율적이다. 또한 PK 컬럼이 많아지면 SQL이 훨씬 복잡해진다.

아래와 같이 작성하면, 좀더 간결하게 표현할 수 있다.

 

SELECT 장비번호, 장비명, 상태코드, SUBSTR(최종이력, 1, 8) 최종변경일자, SUBSTR(최종이력,9) 최종변경순번
FROM(
SELECT 장비번호,장비명,상태코드
,(SELECT MAX(변경일자||변경순번) --생략)
--생략
)

 

변경일자||변경순번으로 가공한 컬럼값의 MAX를 가지고와서 적절히 자르는 것이다.

 

이와 같이 SQL을 작성하면, 각 장비당 이력이 많지 않으면 크게 상관없지만, 이력이 많다면 성능에 문제가 될 수 있는 패턴이다. 인덱스 컬럼을 가공했기 때문이다. 각 장비에 속한 과거 이력 데이터를 모두 읽어야하므로 장비당 이력 레코드가 많다면 바로 직전에 본 복잡한 SQL보다 성능이 더 안좋을 수 있다. 

 

* 해결방법은 뒤에서 알아보자 

 

*SELEC-LIST는 SELECT에서 원하는 값만 뽑아오는 것을 의미한다. 

---

7. 자동 형변환

 

• 숫자형 vs 문자형 -> 문자를 숫자형으로 자동 형변환
• 날짜 vs 문자형 -> 문자를 날짜형으로 자동 형변환 
• LIKE일 때는 문자형 기준으로 숫자형 컬럼이 변환 

 

자동형변환은 에러를 발생시킬 사능성이 크다. 따라서 이 기능에 의존하지 말고, 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환해 주어야 한다. 

 

---

 

정리

 

1. 인덱스를 잘 사용한다는 것 -> 수직적, 수평적 탐색이 잘 이루어져서 INDEX RANGE SCAN이 되어야 한다.

   -> 이를 위해 다음 조건을 만족해야함

        1. 선두컬럼에 = 조건이 걸려있을 것 

        2. 선두컬럼을 가공하거나, 중간 컬럼부터 탐색하지 않을 것 

        

2. 선두컬럼에 OR 혹은 IN 조건이 걸려있다면, OR-Expansion 혹은 IN-List Iterator로 쿼리를 변형한다 (UNION ALL 방식)

    -> 하지만, OR 혹은 IN은 인덱스 =조건과 다르다 -> 선두 컬럼 외에 걸려있으면 Filter 조건으로 사용된다.

 

3. 인덱스를 이용한 SORT 연산 생략 

    1. 사용한 모든 인덱스에 = 조건이 걸려있을 때

    2. 선두컬럼에 = 조건과 ORDER BY에 나머지 컬럼이 순서대로 지정되어 있을 때 

    3. ORDER BY절을 가공하거나, 가공한 컬럼을 ORDER BY에서 사용하는 경우 SORT 연산을 생략할 수 없음

    4. SELECT에서 선두컬럼에 대한 조건이 =으로 존재하고, MIN,MAX를 구할 때 SORT 연산이 생략된다. 

       -> SELECT 컬럼의 값을 가공해야할 때 먼저 MAX나 MIN을 구한 뒤 가공하면 작동한다.

    5.  = 조건을 선두에 넣고, ORDER BY에서 사용한 값을 인덱스 뒤에 차례대로 추가하면, SORT연산을 생략할 수 있다. 

 

4. 자동형변환 

    문자 VS 숫자 -> 숫자

    날짜 VS 문자 -> 날짜

    LIKE 연산에선 숫자를 문자로 바꿈 

 

자동형 변환도 컬럼을 가공한 것과 같다. 따라서 인덱스가 작동하지 않을 수 있다. 

 

> 자동 형변환 팁 (조건 값은 컬럼과 같은 자료형을 사용한다)

 

WHERE 절에서 DATE 자료형의 컬럼에는 DATE 형태의 조건을, 문자열 자료형의 날짜 컬럼에는 문자열 조건값을 사용

--문자열 날짜 자료형과 문자열 조건값 사용법
WHERE T1.ORD_YMD = '20170313'

--DATE 자료형과 문자열 조건값 사용법
WHERE T1.ORD_DT = '20170313';

--DATE 자료형 범위조건 처리 
WHERE T1.ORD_DT >= TO_DATE('20170313','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170313','YYYYMMDD') +1

* 조건 값이 무조건 문자형이라면, 테이블의 컬럼을 자동 변환하는 경우는 없다 (다만 DBMS 이관 및 올바른 사용법을 위해 무조건 문자 조건을 사용하는 방법은 지양하도록하자

---

참고자료 

https://www.yes24.com/Product/Goods/61254539     

친절한 SQL 튜닝 - 예스24