1. 인덱스 튜닝 기초

- B*Tree 인덱스를 활용한 인덱스 튜닝 방법 

가. 범위 스캔이 불가능하거나 인덱스 사용이 아예 불가능한 경우

- 인덱스 선두 칼럼을 조건절에서 가공 (FBI 인덱스를 정의하지 않는 한) 

   select * from 업체 where substr(업체명, 1, 2) = '대한'

- 부정형 비교

   select * from 고객 where 직업 <> '학생'

- is not null 조건도 부정형 비교에 해당하므로 정상적인 인덱스 사용은 어렵다.

   select * from 사원 where 부서코드 is not null

* 위 세 경우 모두 정상적인 인덱스 범위 스캔이 불가능할 따름이지 인덱스 사용 자체가 불가능하지는 않다.

   =>Index Full Scan은 가능하다.

    (설명) 맨 마지막 SQL을 예를 들어, Oracle에서 ‘부서코드’에 단일 칼럼 인덱스가 존재한다면

                그 인덱스 전체를 스캔하면서 얻은 레코드는 모두 ‘부서코드 is not null’ 조건을 만족한다.

     Oracle은 단일 칼럼 인덱스에 null 값은 저장하지 않기 때문이다.

                결합 인덱스일 때는 인덱스 구성 칼럼 중 하나라도 값이 null이 아닌 레코드는 인덱스에 저장하는데,

                그래도 필터링을 통해 ‘부서코드 is not null’ 조건에 해당하는 레코드를 모두 찾을 수 있다.

                인덱스 사용이 불가능한 경우도 있는데, Oracle에서 아래와 같이 is null 조건만으로 검색할 때가 그렇다.

                인덱스도 구성칼럼이 모두 null인 레코드는 인덱스만 뒤져선 찾을 수 없기 때문이다.

               다른 인덱스 칼럼에 is null이 아닌 조건식이 하나라도 있거나 not null 제약이 있으면,

               Oracle에서도 is null 조건에 대한 Index Range Scan이 가능하다.

               (물론 인덱스 선두 칼럼이 조건절에 누락되지 않아야 한다.)

     SQL Server는 단일, 결합을 가리지 않고 null이 아닌 레코드를 인덱스에서 모두 찾을 수 있다.

                

나. 인덱스 칼럼의 가공

(의미) 인덱스 칼럼을 가공하면 정상적인 Index Range Scan이 불가능해진다고 했다.

가장 흔한 인덱스 칼럼 가공 사례는 [표 Ⅲ-4-2]와 같고, 오른쪽 칼럼은 각 사례에 대한 튜닝 방안이다.

다. 묵시적 형변환

(의미) 인덱스 칼럼을 사용자가 명시적으로 가공하지 않더라도 조건절에서 비교되는 두 값의 데이터 타입이 다르면

            내부적으로 형변환이 일어난다.

(예제) emp 테이블 deptno 칼럼은 number 형이다. 이 칼럼에 대한 검색조건으로는 숫자형이 옳지만,

            자칫 실수로 아래와 같이 문자형으로 코딩하는 경우가 종종 생긴다.

            SQL> select * from emp where deptno='20';
- Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     5 |   160 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     5 |   160 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_DEPT_IDX |     5 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=20)
     

       → 문자형 상수 '20'이 숫자형 20으로 변환됨

(설명) 문자형과 숫자형이 만나면 옵티마이저가 문자형을 숫자형으로 변환하며, 위 Predicate Information에서 그런 사실을 발견할 수 있다.  

            따라서 인덱스도 정상적으로 사용할 수 있게 된 것이다.

 

(예제) 이번에는 ‘cdeptno’라는 문자형 칼럼을 추가하고 인덱스까지 생성한 다음에 아래와 같이 테스트해 보자.

     SQL> select * from emp where cdeptno=20;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

(설명) 문자형 cdeptno 칼럼이 숫자형으로 변환된 것을 볼 수 있고, 이 때문에 emp 테이블을 Full Scan하는 실행계획이 수립되었다.

 

(묵시적현변환) 묵시적 형변환은 사용자가 코딩을 쉽게 하도록 도울 목적으로 대부분 DBMS가 제공하는 기능인데,

            위와 같은 부작용을 피하려면 가급적 명시적으로 변환함수를 사용하는 것이 좋다.

            문자형과 숫자형이 만나면 숫자형으로, 문자형과 날짜형이 만나면 날짜형으로 변환하는 등

            데이터타입간 우선순위가 존재한다. 성능을 위해서라면 인덱스 칼럼과 비교되는 반대쪽을

            인덱스 칼럼 데이터 타입에 맞춰면 된다. 묵시적 형변환은 주로 성능 측면에서 언급되곤 하지만,

            올바른 결과집합을 얻기 위해서라도 변환함수를 명시하는 것이 바람직하다.   

            묵시적 형변환은 쿼리 수행 도중 에러를 발생시키거나 결과집합을 틀리게 만드는 요인이 될 수 있기 때문이다.

2. 테이블 Random 액세스 최소화

가. 인덱스 ROWID에 의한 테이블 Random 액세스

(사례) 쿼리에서 참조되는 칼럼이 인덱스에 모두 포함되는 경우가 아니라면, ‘테이블 Random 액세스’가 일어난다.

            즉, 인덱스찾아서->ROWID를 이용->데이터찾기 작업을 한다.

           아래 실행계획에서 인덱스가 (고객)일 경우, ‘Table Access By Index ROWID’라고 표시된 부분을 말한다.

SQL Server는 ‘RID Lookup’이라는 표현을 사용하며, 아래 실행계획에서 알 수 있듯이

                   인덱스로부터 테이블을 NL 조인하는 것처럼 처리경로를 표현하고 있다.

StmtText

참고로, 2000 이하 버전에서는 아래 처럼 ‘Bookmark Lookup’이라고 표현했으며,

              이것이 오히려 Oracle 실행계획과 같은 모습이다.

StmtText

-------------------------------------------------------------

 |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([..].[dbo].[고객]))

 |--Index Seek(OBJECT:([..].[dbo].[고객].[고객_지역_idx]), SEEK:([지역] = '서울'))

 

*  ‘Table Access By Index Rowid’ 또는 ‘RID(=Bookmark) Lookup’으로 표현되는 테이블 Random 액세스의 내부 메커니즘

■ 인덱스 ROWID에 의한 테이블 액세스 구조

(설명) 인덱스에 저장돼 있는 rowid는 흔히 ‘물리적 주소정보’라고 일컬어지는데,

           오브젝트 번호, 데이터 파일 번호, 블록 번호 같은 물리적 요소들로 구성돼 있다.

           하지만 보는 시각에 따라서는 ‘논리적 주소정보’라고 표현하기도 한다.

           rowid가 물리적 위치 정보로 구성되지만 인덱스에서 테이블 레코드로 직접 연결되는 구조는 아니기 때문이다.

(설명) 데이터 블록을 읽을 때는 항상 버퍼 캐시를 경유하므로 메모리 상에서 버퍼 블록을 찾기 위해 해시 구조와

           알고리즘을 사용한다. 해시 키(Key) 값으로는 rowid에 내포된 데이터 블록 주소(Data Block Address, DBA)를 사용

(알고리즘) 인덱스 ROWID를 이용해 테이블 블록을 읽는 메커니즘          

            1. 인덱스에서 하나의 rowid를 읽고 DBA(디스크 상의 블록 위치 정보)를 해시 함수에 적용해 해시 값을 확인한다.

            2. 해시 값을 이용해 해시 버킷을 찾아간다.

            3. 해시 버킷에 연결된 해시 체인을 스캔하면서 블록 헤더을 찾는다.

            4. 해시 체인에서 블록 헤더를 찾으면 거기 저장된 포인터를 이용해 버퍼 블록을 읽는다.

            5. 해시 체인을 스캔하고도 블록 헤더를 찾지 못하면, LRU 리스트를 스캔하면서 Free 버퍼를 찾는다.

                 디스크에서 읽은 블록을 적재하기 위해 빈 캐시 공간을 찾는 것이다.

            6. LRU 리스트에서 Free 버퍼를 얻지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보한다.

            7. Free 버퍼를 확보하고 나면 디스크에서 블록을 읽어 캐시에 적재한다.

(주) 위 처리 과정 중에는 래치(Latch), 버퍼 Lock 같은 Internal Lock을 획득하거나

        다른 백그라운드 프로세스의 선처리 결과를 기다리는 내부 메커니즘이 작동한다.

        그런 과정에 경합까지 발생한다면 블록 하나를 읽더라도 생각보다 큰 비용을 치르게 된다.

        Oracle이나 SQL Server 같은 디스크 기반 DBMS에서 인덱스 rowid에 의한 테이블 액세스가

        생각만큼 빠르지 않은 이유가 여기에 있다.

        특히, 다량의 테이블 레코드를 읽을 때의 성능 저하가 심각하다.

        앞으로 실행계획에서 아래와 같이 ‘Table Access By Index ROWID’나 ‘RID(=Bookmark) Lookup’ 오퍼레이션을 볼 때면,

         [그림 Ⅲ-4-17]과 함께 방금 설명한 복잡한 처리 과정을 항상 떠올리기 바란다.

 

■ 클러스터링 팩터(Clustering Factor)

(설명) Oracle은 ‘클러스터링 팩터’라는 개념을 사용해 인덱스 ROWID에 의한 테이블 액세스 비용을 평가한다.

            SQL Server는 공식적으로 이 용어를 사용하진 않지만 내부적인 비용 계산식에 이런 개념이 포함돼 있을 것이다.

            클러스터링 팩터는 ‘군집성 계수(= 데이터가 모여 있는 정도)’ 쯤으로 번역될 수 있는 용어로서,

            특정 칼럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다.

            [그림 4-18]은 클러스터링 팩터가 가장 좋은 상태를 도식화한 것으로서,

            인덱스 레코드 정렬 순서와 거기서 가리키는 테이블 레코드 정렬 순서가 100% 일치하는 것을 볼 수 있다.

            반면 [그림 Ⅲ-4-19]는 인덱스 클러스터링 팩터가 가장 안 좋은 상태를 도식화한 것으로서,

            인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 전혀 일치하지 않는다.

                    

               클러스터링 팩터가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋은데,

               예를 들어 「거주지역 = ‘제주’」에 해당하는 고객 데이터가 물리적으로 근접해 있다면

               흩어져 있을 때보다 데이터를 찾는 속도가 빨라지게 마련이다.

나. 인덱스 손익분기점

(설명) 앞서 설명한 것처럼 인덱스 rowid에 의한 테이블 액세스는 생각보다 고비용 구조이고,

           따라서 일정량을 넘는 순간 테이블 전체를 스캔할 때보다 오히려 더 느려진다.

           Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 흔히 ‘손익 분기점’이라고 부른다.

          예를 들어, 인덱스 손익분기점이 10%라는 의미는 1,000개 중 100개 레코드 이상을 읽을 때는

          인덱스를 이용하는 것보다 테이블 전체를 스캔하는 것이 더 빠르다는 것이다.

          인덱스 손익분기점은 일반적으로 5~20%의 낮은 수준에서 결정되지만 클러스터링 팩터에 따라 크게 달라진다.

          클러스터링 팩터가 나쁘면 손익분기점은 5% 미만에서 결정되며, 심할 때는(BCHR가 매우 안 좋을 때) 1% 미만으로 떨어진다.

          반대로 클러스터링 팩터가 아주 좋을 때는 손익분기점이 90% 수준까지 올라가기도 한다.

(설명)  인덱스에 의한 액세스가 Full Table Scan보다 더 느리게 만드는 가장 핵심적인 두 가지 요인은 다음과 같다.

          (1) 인덱스 rowid에 의한 테이블 액세스는 Random 액세스인 반면, Full Table Scan은 Sequential 액세스 방식으로 이루어진다.

          (2) 디스크 I/O 시, 인덱스 rowid에 의한 테이블 액세스는 Single Block Read 방식을 사용하는 반면,

                Full Table Scan은 Multiblock Read 방식을 사용한다.

■ 손익분기점 극복하기

(설명) 손익분기점 원리에 따르면 선택도(Selectivity)가 높은 인덱스는 효용가치가 낮지만,

            그렇다고 테이블 전체를 스캔하는 것은 부담스러울 때가 많다.

            그럴 때 DBMS가 제공하는 기능을 잘 활용하면 인덱스의 손익분기점 한계를 극복하는 데 도움이 된다.

(방법)

(1) 첫번째는 SQL Server의 클러스터형 인덱스와 Oracle IOT로서, 

     테이블을 인덱스 구조로 생성하는 것이라고 앞서 설명하였다.

     테이블 자체가 인덱스 구조이므로 항상 정렬된 상태를 유지한다.

     그리고 인덱스 리프 블록이 곧 데이터 블록이어서 인덱스를 수직 탐색한 다음에

     테이블 레코드를 읽기 위한 추가적인 Random 액세스가 불필요하다.

(2) 두 번째는 SQL Server의 Include Index이다.

     인덱스 키 외에 미리 지정한 칼럼을 리프 레벨에 함께 저장하는 기능으로서,

     테이블 Random 액세스 횟수를 줄이도록 돕는다. 잠시 후 좀 더 자세한 설명을 보게 될 것이다.

(3) 세 번째는 Oracle이 제공하는 클러스터 테이블(Clustered Table)이다.

     키 값이 같은 레코드를 같은 블록에 저장하기 때문에 클러스터 테이블에 대한 클러스터 인덱스를 이용할 때는

     테이블 Random 액세스가 키 값별로 한 번씩만 발생한다.

     클러스터에 도달해서는 Sequential 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다.

(4) 네 번째는 파티셔닝이다.

     읽고자 하는 데이터가 많을 때는 인덱스를 이용하지 않는 편이 낫다고 하지만,

     수천만 건에 이르는 테이블을 Full Scan해야 한다면 난감하기 그지없다.

     그럴 때, 대량 범위검색 조건으로 자주 사용되는 칼럼 기준으로 테이블을 파티셔닝한다면

     Full Table Scan 하더라도 일부 파티션만 읽고 멈추도록 할 수 있다.

     클러스터는 기준 키 값이 같은 레코드를 블록 단위로 모아 저장하지만

     파티셔닝은 세그먼트 단위로 저장하는 점이 다르다. 좀 더 자세한 내용은 5장에서 보게 될 것이다.

(5) 기타 - 이런 기능 외에 1장에서 설명한 부분범위처리 원리를 잘 활용하는 것도 좋은 방법이다.

     인덱스 스캔 비효율이 없도록 잘 구성된 인덱스를 이용해 부분범위처리 방식으로 프로그램을 구현한다면

     그 인덱스의 효용성은 100%가 된다. 무조건 인덱스를 사용하는 쪽이 유리하다는 뜻이다.  

다. 테이블 Random 액세스 최소화 튜닝

1) 인덱스 칼럼 추가

    (설명) emp 테이블에 현재 PK 이외에 [deptno + job] 순으로 구성된 emp_x01 인덱스 하나만 있는 상태에서 아래 쿼리를 수행하려고 한다.

              select /*+ index(emp emp_x01) */ ename, job, sal from emp where deptno = 30 and sal >= 2000

    [그림 Ⅲ-4-20]을 보면 위 조건을 만족하는 사원이 단 한 명뿐인데, 이를 찾기 위해 테이블 액세스는 6번 발생하였다.

                 

    인덱스 구성을 [deptno + sal] 순으로 바꿔주면 좋겠지만 실 운영 환경에서는 인덱스 구성을 함부로 바꾸기가 쉽지 않다.

    기존 인덱스를 사용하는 아래와 같은 SQL이 있을 수 있기 때문이다.

    할 수 없이 인덱스를 새로 만들어야겠지만 이런 식으로 인덱스를 추가하다 보면

    테이블마다 인덱스가 수십 개씩 달려 배보다 배꼽이 더 커지게 된다.

    이럴 때, [그림 Ⅲ-4-21]처럼 [dept+job+sal]로 기존 인덱스에 sal 칼럼을 추가하는 것만으로 큰 효과를 거둘 수 있다.

    인덱스 스캔량은 줄지 않지만 테이블 Random 액세스 횟수를 줄여주기 때문이다.

    

2) Covered Index

    (설명) 테이블을 액세스하고서 필터 조건에 의해 버려지는 레코드가 많을 때,

               인덱스에 칼럼을 추가함으로써 얻는 성능 효과를 살펴보았다.

               그런데 테이블 Random 액세스가 아무리 많더라도 필터 조건에 의해 버려지는 레코드가

               거의 없다면 거기에 비효율은 없다. 이때는 어떻게 튜닝해야 할까?

               이때는 아예 테이블 액세스가 발생하지 않도록 필요한 모든 칼럼을 인덱스에 포함시키는 방법을 고려해 볼 수 있다.

               SQL Server에서는 그런 인덱스를 ‘Covered 인덱스’ 라고 부르며,

               인덱스만 읽고 처리하는 쿼리를 ‘Covered 쿼리’라고 부른다.

 

3) Include Index

    (설명) Oracle엔 아직 없는 유용한 기능이 SQL Server 2005 버전에 추가되었는데,

                인덱스 키 외에 미리 지정한 칼럼을 리프 레벨에 함께 저장하는 기능이다.

                인덱스를 생성할 때 아래와 같이 include 옵션을 지정하면 되고, 칼럼을 최대 1,023개까지 지정할 수 있다.

               create index emp_x01 on emp (deptno) include (sal)

               만약 인덱스를 [deptno + sal] 순으로 생성하면

               sal 칼럼도 수직적 탐색에 사용될 수 있도록 그 값을 루프와 브랜치 블록에 저장한다.

                하지만 위와 같이 sal 칼럼을 include 옵션으로만 지정하면 그 값은 리프 블록에만 저장한다.

                따라서 수직적 탐색에는 사용되지 못하고 수평적 탐색을 위한 필터 조건으로만 사용된다.

                그럼으로써 테이블 Random 액세스 횟수를 줄이는 효과를  가져다 준다.

 

4) IOT, 클러스터형 인덱스, 클러스터 테이블 활용

(설명) 1절에서 설명한 Oracle IOT나 SQL Server 클러스터형 인덱스을 이용하는 것도

            테이블 Random 액세스를 없애는 중요한 방법 중 하나다.

            Oracle이라면 클러스터 테이블을 이용할 수도 있다.

            IOT와 클러스터형 인덱스에 대해선 1절에서 이미 설명하였다.

            인덱스를 이용하는 인덱스 클러스터도 이미 설명했으므로 생략하고,

            여기서는 해시 클러스터에 대해서만 간단히 살펴보기로 하자.

            해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조다.

            클러스터 키로 데이터를 검색하거나 저장할 위치를 찾을 때 해시 함수를 사용한다.

            해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해

            클러스터 키 값을 데이터 블록 주소로 변환해 준다.

            별도의 인덱스 구조를 생성하지 않는 장점에도 불구하고 해시 클러스터의 활용성을 떨어뜨리는

            중요한 제약사항은, ‘=’ 검색만 가능하다는 점이다.

            항상 ‘=’ 조건으로만 검색되는 칼럼을 해시 키로 선정해야 하는 것이며,

            이는 해시 함수를 사용하기 때문에 나타나는 어쩔 수 없는 제약이다.

 

5) 수동으로 클러스터링 팩터 높이기(reorganize)

(설명) 테이블에는 데이터가 무작위로 입력되는 반면, 그것을 가리키는 인덱스는 정해진 키(key) 순으로

           정렬되기 때문에 대개 클러스터링 팩터가 좋지 않게 마련이다.

           필자의 경험에 의하면, 클러스터링 팩터가 나쁜 인덱스를 이용해 많은 양의 데이터를 읽는 SQL 튜닝하기가 가장 어렵다.

           그럴 때, 해당 인덱스 기준으로 테이블을 재생성함으로써

           클러스터링 팩터를 인위적으로 좋게 만드는 방법을 생각해 볼 수 있고,

           실제 그렇게 했을 때 나타나는 효과는 매우 극적이다.

           주의할 것은, 인덱스가 여러 개인 상황에서 특정 인덱스를 기준으로 테이블을 재정렬하면

           다른 인덱스의 클러스터링 팩터가 나빠질 수 있다는 점이다.

           다행히 두 인덱스 키 칼럼 간에 상관관계가 높다면(예를 들어, 직급과 급여)

          두 개 이상 인덱스의 클러스터링 팩터가 동시에 좋아질 수 있지만,

          그런 경우를 제외하면 대개 클러스터링 팩터가 좋은 인덱스는 테이블당 하나뿐이다.

          따라서 인위적으로 클러스터링 팩터를 높일 목적으로 테이블을 Reorg 할 때는

          가장 자주 사용되는 인덱스를 기준으로 삼아야 하며,

          혹시 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크해 봐야 한다.

          그리고 이 작업을 주기적으로 수행하려면 데이터베이스 관리 비용이 증가하고 가용성에도 영향을 미치므로

           테이블과 인덱스를 Rebuild하는 부담이 적고 그 효과가 확실할 때만 사용하는 것이 바람직하다.

3. 인덱스 스캔범위 최소화

(설명) 1장 4절에서 데이터베이스 I/O 원리를 설명하면서 Random 액세스와 Sequential 액세스의 차이점을 설명하였다.

            Sequential 액세스는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식을 말하고,

            Random 액세스는 레코드간 논리적, 물리적 순서를 따르지 않고 한 건을 읽기 위해 한 블록씩 접근(=touch)하는 방식이라고 했다.

           그리고 I/O 튜닝의 핵심 원리로서 아래 두 가지 항목을 꼽았다.

           ① Random 액세스 발생량을 줄인다.

           ② Sequential 액세스에 의한 선택 비중을 높인다.

          본 장에서는 지금까지 테이블 Random 액세스를 최소화하는 방안에 대해 설명했고, 이는 ①번 항목에 해당한다.

          지금부터는 ②번 Sequential 액세스에 의한 선택 비중을 높이는 방안,

          그 중에서도 인덱스를 Sequential 방식으로 스캔하는 단계에서 발생하는 비효율 해소 원리를 다룬다.

가. 인덱스 선행 칼럼이 범위조건일 때의 비효율

(설명) 인덱스 구성 칼럼이 조건절에서 모두 등치(=) 조건으로 비교되면

            리프 블록을 스캔하면서 읽은 레코드는 모두 테이블 액세스로 이어진다.

            읽고서 버리는 레코드가 하나도 없으므로 인덱스 스캔 단계에서의 효율은 최상이다.

            인덱스 칼럼 중 일부가 ‘=’ 조건이 아니거나 조건절에서 생략되더라도 그것이 뒤쪽 칼럼일 때는 비효율이 없다.

           예를 들어, 인덱스가 [아파트시세코드 + 평형 + 평형타입 + 인터넷매물] 순으로 구성됐을 때 조건절이 아래와 같은 경우를 말한다.

 

          반면, 인덱스 선행 칼럼이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면

          인덱스를 스캔하는 단계에서 비효율이 발생한다.

          예를 들어, 인덱스가 [아파트시세코드 + 평형 + 평형타입 + 인터넷매물] 순으로 구성된 상황에서

          아래 SQL을 수행하는 경우를 살펴보자.

[그림 Ⅲ-4-22]는 위 조건절을 만족하는 두 개 레코드(그림에서 음영 처리된 레코드)를 찾기 위해

                              인덱스를 범위 스캔하는 과정을 도식화한 것이다.

(설명) 인터넷매물이 between 조건이지만 선행 칼럼들(아파트시세코드, 평형, 평형타입)이 모두 ‘=’ 조건이기 때문에

            전혀 비효율 없이 조건을 만족하는 2건을 빠르게 찾았다.

            인덱스 선행 칼럼이 모두 ‘=’ 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은,

            조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다.

            이제 인덱스 구성을 [인터넷매물 + 아파트시세코드 + 평형 + 평형타입] 순으로 바꾸고 나서 같은 SQL을 수행해 보면,

            [그림 Ⅲ-4-23]처럼 인덱스 스캔 범위가 넓어진다.

 

            [그림 4-23 : 선행컬럼이 범위조건일때 인덱스 스캔범위] => 교과서 633 참조

 

(설명) 인덱스 선두 칼럼인 인터넷매물에 between 연산자를 사용하면

            나머지 조건(아파트시세코드=‘A01011350900056’ and 평형 = ‘59’ and 평형타입 = ‘A’)을 만족하는 레코드들이

            인터넷매물 값(0, 1, 2, 3)별로 뿔뿔이 흩어져 있게 된다.

            따라서 조건을 만족하지 않는 레코드까지 스캔하고서 버려야 하는 비효율이 생긴다.

나. 범위조건을 In-List로 전환

(설명) 범위검색 칼럼이 맨 뒤로 가도록 인덱스를 [아파트시세코드 + 평형 + 평형타입 + 인터넷매물] 순으로

           변경하면 좋겠지만 운영 중인 시스템에서 인덱스 구성을 바꾸기는 쉽지 않다.

           이럴 때 between 조건을 아래와 같이 IN-List로 바꿔주면 가끔 큰 효과를 얻는다.

 

            select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드

            from 매물아파트매매

            where 인터넷매물 in ('1', '2') and 아파트시세코드='A01011350900056'

                       and 평형 = '59' and 평형타입 = 'A' order by 입력일 desc

[그림 Ⅲ-4-24]는 between 조건을 IN-List로 바꾸었을 때의 스캔 과정을 도식화한 것이다.

(설명) 왼쪽에 화살표가 두 개인 이유는 인덱스의 수직적 탐색이 두 번 발생하기 때문이며,

           이때의 실행계획은 아래(INLIST ITERATOR 오퍼레이션 주목)와 같다.

SQL Server에서의 실행계획은 다음과 같고, 특히 트레이스를 걸면 스캔 수가 2로 표시되는 것에 주목하기 바란다.

인덱스를 위와 같이 두 번 탐색한다는 것은 SQL을 아래와 같이 작성한 것과 마찬가지가 된다.

모든 칼럼이 ‘=’ 조건인 것에 주목하기 바란다.

(설명) 인덱스 선두 칼럼의 between 조건을 IN-List 조건으로 바꿀 때 주의할 점은, IN-List 개수가 많지 않아야 한다는 것이다.

            [그림 Ⅲ-4-23]처럼 필요 없는 범위를 스캔하는 비효율은 사라지겠지만

            [그림 Ⅲ-4-24]처럼 인덱스 수직 탐색이 여러 번 발생하기 때문이다.

            IN-List 개수가 많을 때는, between 조건 때문에 리프 블록을 추가로 스캔하는 비효율보다

            IN-List 조건 때문에 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있고, 인덱스 높이(height)가 높을 때 특히 그렇다.

           Oracle이라면 위와 같은 상황에서 Index Skip Scan이 유용할 수 있다.

           1절에서 설명한 Index Skip Scan은 인덱스 선두 칼럼이 누락됐을 때뿐만 아니라

           부등호, between, like 같은 범위검색 조건일 때도 사용될 수 있다.

다. 범위조건을 2개 이상 사용할 때의 비효율

(설명) 인덱스 구성이 [회사 + 지역 + 상품명]일 때, 아래와 같이 범위검색 조건을 2개 이상 사용하면

            첫 번째가 인덱스 스캔 범위를 거의 결정되고, 두 번째는 필터 조건 역할만 하기 때문에 성능상 불리해질 수 있다.

(설명) 스캔량이 소량일 때는 그 차이가 미미하지만 대량일 때는 상당한 성능차이를 보일 수 있으므로

            인덱스 칼럼에 대한 비교 연산자를 신중하게 선택해야 한다.

            만약 지역 칼럼에 대한 검색조건이 입력되지 않을 수도 있어 위와 같이 LIKE 연산자를 사용한 거라면

            SQL을 아래와 같이 2개 만들어 사용하는 것이 좋다.

또는 아래 처럼 UNION ALL을 이용하는 방법도 있다.

 

                select 고객ID, 상품명, 지역, ...

                from 가입상품

                where  :reg is null  and  회사 = :com  and  상품명 like :prod || '%'

                union all

                select 고객ID, 상품명, 지역, ...

                from 가입상품

                where  :reg is not null  and  회사 = :com  and  지역 = :reg  and  상품명 like :prod || '%'

(설명) 기존 인덱스 구성 하에서, UNION ALL 상단 쿼리는 기존과 동일한 비효율을 안은 채 수행되겠지만

            하단 쿼리만큼은 최상으로 수행될 수 있다.

            만약 UNION ALL 상단 쿼리까지 최적화하려면 [회사 + 상품명] 순으로 구성된 인덱스를 하나 더 추가해야 한다.

            인덱스를 새로 추가하는 데 부담이 있으면 기존 인덱스 순서를 [회사 + 상품명 + 지역] 순으로 변경하는 것을 고려할 수 있는데,

            그럴 경우 UNION ALL 하단 쿼리를 처리할 때 불리해진다.

            따라서 이는 상품명 조건에 입력되는 값의 선택도에 따라 결정할 사항이다.

4. 인덱스 설계

가. 결합 인덱스 구성을 위한 기본 공식

(설명) 인덱스 스캔 방식에 여러 가지가 있지만 가장 정상적이고 일반적인 것은 Index Range Scan이라고 했다.

           이를 위해서는

          (1) 인덱스 선두 칼럼이 조건절에 반드시 사용되어야만 한다.

                따라서 결합 인덱스를 구성할 때 첫 번째 기준은, 조건절에 항상 사용되거나,

                적어도 자주 사용되는 컬럼들을 선정하는 것이다.

          (2) 두번째 기준은, 그렇게 선정된 칼럼 중 ‘=’ 조건으로 자주 조회되는 칼럼을 앞쪽에 두어야 한다는 것이다.

                그 이유에 대해서는 바로 앞에서 충분히 설명하였다.

          (3) 세 번째 기준은, 소트 오퍼레이션을 생략하도록 하기 위해 칼럼을 추가하는 것이다.

               인덱스는 항상 정렬 상태를 유지하므로 order by, group by를 위한 소트 연산을 생략할 수 있도록 해 준다.

               따라서 조건절에 사용되지 않은 칼럼이더라도 소트 연산을 대체할 목적으로

               인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수가 있다.

               인덱스를 이용해 소트 연산을 대체하려면,

               인덱스 칼럼 구성과 같은 순서로 누락 없이(뒤쪽 칼럼이 누락되는 것은 상관없음) order by절에 기술해 주어야 한다.

               단, 인덱스 구성 칼럼이 조건절에서 ‘=’ 연산자로 비교된다면, 그 칼럼은 order by절에서 누락되거나

               인덱스와 다른 순서로 기술하더라도 상관없다.

               이런 규칙은 group by절에도 똑같이 적용된다.

■ 선택도 이슈

(설명) 인덱스 생성 여부를 결정할 때는 선택도(selectivity)가 충분히 낮은지가 중요한 판단기준임이 틀림없다.

            앞에서 설명했듯이 인덱스를 스캔하면서 테이블을 액세스하는 양이 일정 수준(=손익분기점)을 넘는 순간

            Full Table Scan 보다 오히려 느려지기 때문이다.

            따라서 선택도(결합 인덱스일 때는 결합 선택도)가 높은 인덱스는 생성해 봐야 효용가치가 별로 없다.

            결합 인덱스 칼럼 간 순서를 정할 때도 개별 칼럼의 선택도가 고려사항은 될 수 있지만

            어느 쪽이 유리한지는 상황에 따라 다르다.

            개별 칼럼의 선택도보다는 조건절에서 어떤 형태로 자주 사용되는지, 사용빈도는 어느 쪽이 높은지,

            데이터를 빠르게 검색하는 데에 어느 쪽 효용성이 높은지 등이 더 중요한 판단기준이다.

나. 추가적인 고려사항

(설명) 위 공식이 결합 인덱스를 구성할 때 일반적으로 통용될 수 있는 기본 공식임은 틀림없다.

            하지만, 인덱스 설계가 그렇게 간단하지만은 않다.

            인덱스 스캔의 효율성 외에도 고려해야 할 요소들이 훨씬 많기 때문이다.

            효과적인 인덱스 설계를 위해 추가적으로 고려해야 할 요소들을 열거하면 다음과 같다.

• 쿼리 수행 빈도
• 업무상 중요도
• 클러스터링 팩터
• 데이터량
• DML 부하(= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 칼럼 포함 여부 등)
• 저장 공간
• 인덱스 관리 비용 등

(설명) 이런 상황적 요소에 대한 해석과 판단 기준이 설계자의 성향이나 스타일에 따라 다르기 때문에 결과물도 크게 달라진다.

            전장에서 똑같은 상황에 맞닥뜨리더라도 지휘관 스타일에 따라 전략과 전술이 달라지는 것처럼 말이다.

            인덱스 설계는 공식이 아닌 전략과 선택의 문제다.

            시스템 전체적인 관점에서 대안 전략들을 수립하고 그 중 최적을 선택할 수 있는 고도의 기술력과 경험이 요구되기 때문에 어렵다. 

            개별 쿼리 성능을 높일 뿐만 아니라 생성되는 인덱스 개수를 최소화함으로써 DML 부하를 줄이는 것이 중요한 목표이어야 한다.

다. 인덱스 설계도 작성

앞에서도 얘기했듯이 인덱스 설계 시 시스템 전체 효율을 고려해야 한다. 조화를 이룬 건축물을 짓기 위해 설계도가 필수인 것처럼

인덱스 설계 시에도 전체를 조망할 수 있는 설계도면이 필요한 이유다.

[그림 Ⅲ-4-25]는 인덱스 설계도를 예시한 것이다.