[SQLP] 4장. 인덱스와 조인 - 인덱스 튜닝

1. 인덱스 튜닝 기초

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

아래와 같이 인덱스 선두 칼럼을 조건절에서 가공하면 정상적으로 인덱스를 사용할 수 없다.

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

나. 인덱스 칼럼의 가공

인덱스 칼럼을 가공하면 정상적인 Index Range Scan이 불가능해진다.

SELECT *
FROM 업체
WHERE SUBSTR(업체명,1,2) = '대한'

위와 같은 SQL은 아래처럼 바꿔야 인덱스를 탈 수 있다.

SELECT *
FROM 업체
WHERE 업체명 LIKE '대한%'

다. 묵시적 형변환

인덱스 칼럼을 사용자가 명시적으로 가공하지 않더라도 조건절에서 비교되는 두 값의 데이터 타입이 다르면 내부적으로 형변환이 일어난다.

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

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

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

나. 인덱스 손익분기점

앞서 설명한 것처럼 인덱스 rowid에 의한 테이블 액세스는 생각보다 고비용 구조이고, 따라서 일정량을 넘는 순간 테이블 전체를 스캔할 때보다 오히려 더 느려진다. Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 흔히 ‘손익 분기점’이라고 부른다.

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

1) 인덱스 칼럼 추가

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

위처럼 되어있는 쿼리에서 job 인덱스 컬럼을 추가하여 개선할 수 있다.

select ename, job, sal 
from emp 
where deptno = 30 and job = 'CLERK'

2) Covered Index 
아예 테이블 액세스가 발생하지 않도록 필요한 모든 칼럼을 인덱스에 포함시키는 방법을 고려해 볼 수 있다. SQL Server에서는 그런 인덱스를 ‘Covered 인덱스’라고 부르며, 인덱스만 읽고 처리하는 쿼리를 ‘Covered 쿼리’라고 부른다.

3) Include Index (SQL Server 만 가능) 
인덱스를 생성할 때 아래와 같이 include 옵션을 지정하면 되고, 칼럼을 최대 1,023개까지 지정할 수 있다.

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

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

I/O 튜닝의 핵심 원리로서 아래 두 가지 항목을 꼽았다. 
① Random 액세스 발생량을 줄인다. ② Sequential 액세스에 의한 선택 비중을 높인다.

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

인덱스 구성 칼럼이 조건절에서 모두 등치(=) 조건으로 비교되면 리프 블록을 스캔하면서 읽은 레코드는 모두 테이블 액세스로 이어진다. 읽고서 버리는 레코드가 하나도 없으므로 인덱스 스캔 단계에서의 효율은 최상이다.

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

범위검색 칼럼이 맨 뒤로 가도록 인덱스를 [아파트시세코드 + 평형 + 평형타입 + 인터넷매물] 순으로 변경하면 좋겠지만 운영 중인 시스템에서 인덱스 구성을 바꾸기는 쉽지 않다. 이럴 때 between 조건을 아래와 같이 IN-List로 바꿔주면 가끔 큰 효과를 얻는다.

4. 인덱스 설계

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

인덱스 스캔 방식에 여러 가지가 있지만 가장 정상적이고 일반적인 것은 Index Range Scan이라고 했다. 이를 위해서는 인덱스 선두 칼럼이 조건절에 반드시 사용되어야만 한다. 따라서 결합 인덱스를 구성할 때 첫 번째 기준은, 조건절에 항상 사용되거나, 적어도 자주 사은, 그렇게 선정된 칼럼 중 ‘=’ 조건으로 자주 조회되는 칼럼을 앞쪽에 두어야 한다는 것이다.

나. 추가적인 고려사항

• 쿼리 수행 빈도

• 업무상 중요도

• 클러스터링 팩터

• 데이터량

• DML 부하(= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 칼럼 포함 여부 등)

• 저장 공간

• 인덱스 관리 비용 등

다. 인덱스 설계도 작성

인덱스 설계 시 시스템 전체 효율을 고려해야 한다. 조화를 이룬 건축물을 짓기 위해 설계도가 필수인 것처럼 인덱스 설계 시에도 전체를 조망할 수 있는 설계도면이 필요한 이유다.