1. 인덱스 튜닝 기초

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

-- 인덱스 선두 칼럼 가공 (FBI 예외)
select * from 업체 where substr(업체명, 1, 2) = '대한'
-- 부정형 비교
select * from 고객 where 직업 <> '학생'
-- 부정형 비교
select * from 사원 where 부서코드 is not null

• Index Range Scan 은 불가능 하지만 Index Full Scan 은 가능
• 세번째 SQL, 부서코드 단일 컬럼 인덱스 존재시 Index Full Scan 으로 얻은 레코드는 모드 조건을 만족
  • • Oracle 은 단일 컬럼 인덱스에 NULL 값 저장 안함, 결합 인덱스는 구성 하는 모든 컬럼이 NULL 인 경우 저장 안함
  • SQL Server 는 항상 NULL이 아닌 레코드를 인덱스에서 모두 찾을 수 있음

select * from 사원 where 연락처 is null

• • IS NULL 조건 검색시 인덱스에서 레코드 확인 불가능 (Oracle/ 인덱스 구성 컬럼 모두 NULL 케이스, 하지만 SQL Server 는 가능)

나. 인덱스 칼럼의 가공

• 인덱스 칼럼 가공 사례와 해결 방안

다. 묵시적 형변환

• 조건절에서 비교되는 두 값의 데이터 타입이 다른 경우 내부적으로 형변환 발생

select * from emp where deptno = '20'

------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost | 
------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 3 | 273 | 1 | 
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 3 | 273 | 1 | 
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 1 | | 1 |
-------------------------------------------------------------

Predicate Information (identified by operation id): 
---------------------------------------------------
 2 - access("EMP"."DEPTNO"=20) → 문자형 상수 '20'이 숫자형 20으로 변환됨

select * from emp where cdeptno = 20

-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 273 | 2 | 
|* 1 | TABLE ACCESS FULL | EMP | 3 | 273 | 2 |
------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
1 - filter(TO_NUMBER("EMP"."CDEPTNO")=20) → 문자형 CDEPTNO 칼럼이 숫자형으로 변환됨

• 부작용 회피를 위해 명시적 형변환 권장
• 원칙 : 인덱스 칼럼과 비교되는 반대쪽을 인덱스 칼럼 데이터 타입이 맞추면 됨

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

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

• 쿼리에서 참조되는 칼럼이 인덱스에 모두 포함되는 경우를 제외하고 항상
• "TABLE ACCESS (BY INDEX ROWID)", "RID LOOKUP"

SQL> select * from 고객 where 지역 = '서울'; 

Execution Plan ------------------------------------------------ 
0 SELECT STATEMENT Optimizer=ALL_ROWS 
1 0 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) 
2 1 INDEX (RANGE SCAN) OF '고객_지역_IDX' (INDEX)

StmtText ------------------------------------------------------------- 
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) 
  |--Index Seek(OBJECT:([..].[dbo].[고객].[고객_지역_idx]), SEEK:([지역] = '서울') 
  |--RID Lookup(OBJECT:([..].[dbo].[고객]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

• 인덱스 ROWID에 의한 테이블 액세스 구조
  • ROWID
  • '물리적 주소정보' (오브젝트 번호, 데이터 파일 번호, 블록 번호)
  • '논리적 주소정보' (인덱스에서 테이블 레코드로 직접 연결되는 구조가 아님)
    • 디스크 상의 위치 정보
    • 메모리 상의 버퍼 블록을 찾기 위해 해시구조와 알고리즘 사용 (해시 키 값 : ROWID 에 내포된 DBA(Data Block Address)를 사용)
  • 메커니즘
    **#. 인덱스에서 하나의 ROWID를 읽고 DBA를 해시 함수에 적용해 해시 값 확인
    1. 해시 값을 이용해 해시 버킷을 찾음
    2. 해시 버킷에 연결된 해시 체인을 스캔하면서 블록 헤더 찾음
    3. 해시 체인에서 블록 헤더를 찾으면, 거기 저장된 포인터를 이용해 버퍼 블록을 읽음
    4. 해시 체인을 스캔하고도 블록 헤더를 찾지 못하면, LRU 리스트를 스캔하면서 Free 버퍼 찾음 (블록을 적재하기 위함)
    5. LRU 리스트에서 Free 버퍼를 얻지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼 확보
    6. Free 버퍼를 확보하고 디스크에서 블록을 읽어 캐시에 적재
  • 메커니즘+
    • 위의 처리 과정 중 래치(Latch), Internal Lock을 획득 하거나 다른 백그라운드 프로세스의 선처리 결과를 대기
    • 디스크 기반 DBMS에서 인덱스 ROWID에 의한 테이블 액세스가 느린 이유
    • 다량의 테이블 레코드를 읽을 때의 성능 저하가 심각함

SQL> select * from 고객 where 지역 = '서울';

Execution Plan 
------------------------------------------------ 
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
2 1 INDEX (RANGE SCAN) OF '고객_지역_IDX' (INDEX)

• 클러스터링 팩터(Clustering Factor)
  • 군집성 계수 (데이터가 모여 있는 정도)
  • 인덱스 ROWID에 의한 테이블 액세스 비용 평가
  • 특정 칼럼을 기준으로 같은 값을 갖는 데이터가 서로 모여 있는 정도

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

나. 인덱스 손익분기점

• 인덱스 ROWID에 의한 테이블 액세스는 고비용 구조
• 일정량을 넘는 순간 Table Full Scan 보다 더 느려짐 (손익 분기점)
  • 예: 손익분기점 10% : 1000개 중 100개 레코드 이상을 읽을 때는 인덱스 보다 테이블 전체 스캔이 더 빠름
• 손익분기점은 보통 5~20%, (클러스터링 팩터에 의존적 : 5% ~ 90%)
• Index Range Scan + ROWID * Table Full Scan *
• Random 액세스 * Sequential 액세스 *
• Single Block Read * Multi Block Read *
• 손익분기점 극복
  • SQL Server 의 Clustered Index, Oracle 의 IOT : 테이블을 인덱스 구조로 생성 (정렬된 상태 유지, 수직 탐색 ONLY)
  • SQL Server 의 Include Index : 인덱스 키 외에 미리 지정한 칼럼을 Leaf 레벨에 함께 저장
  • Oracle 의 Clustered Table : 키 값이 같은 레코드를 같은 블록에 저장
  • 파티셔닝 : Full Table Scan 시 일부 파티션만 읽기

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

1. 인덱스 컬럼 추가
   • EMP 테이블에 EMP_PK[EMPNO], EMP_X01[DEPTNO + JOB] 인덱스 존재

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

• • 테이블 액세스 여섯번 발생, 인덱스 구성 변경(EMP_X01[DEPTNO + SAL]) 어려움

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

• • EMP_X01[DEPTNO + JOB + SAL] 구성 : 인덱스 스캔량은 줄지 않지만 테이블 Random 액세스 횟수는 감소

1. Covered Index (SQL Server)
   • Random 테이블 액세스가 아무리 많아도, 필터 조건에 의해 버려지는 레코드가 없다면 비효율이 아님
   • Random 테이블 액세스를 피하기 위해 모든 칼럼을 인덱스에 포함 / Covered Query (인덱스만 읽고 처리)
2. Include Index (SQL Server)

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

• • sal 이 Leaf 노드에 저장 됨
  • 수직적 탐색에는 사용되지 못하고, 수평적 탐색을 위한 필터 조건으로 사용 됨 (Random 테이블 액세스 감소)

1. IOT, Clustered Index, Clustered Table

• Random 테이블 액세스 감소 됨
• 해시 클러스터
  • 해시 함수에서 반환된 값이 같은 데이터는 물리적으로 함께 저장
  • 클러스터 키로 데이터를 검색하거나 저장할 위치를 찾을 때 해시 함수 사용
  • 해시 함수가 인덱스 역할을 대신 함 (클러스터 키 값을 데이터 블록 주소로 변환)
  • '=' 검색 만 가능

1. 수동으로 클러스터링 팩터 높이기

• 테이블에는 데이터가 무작위로 입력되고, 인덱스는 정해진 키 순으로 정렬 됨 (클러스터링 팩터 낮음)
• 클러스터링 팩터가 나쁜 인덱스를 이용해 많은 양의 데이터를 읽는 SQL은 튜닝이 어려움
• 특정 인덱스 기준으로 테이블을 재생성 함 으로서 클러스터링 팩터를 인위적으로 높임 (효과는 매우 극적)
• 인덱스가 여러개일 경우 특정 인덱스를 기준으로 테이블을 재정렬 하면 다른 인덱스의 클러스터링 팩터는 나빠질 수 있음 (직급,급여 처럼 상관 관계가 있는 경우 제외)
• 재정렬(REORG)시 가장 자주 사용되는 인덱스를 기준으로 처리, 그외 인덱스 영향도 검토 필요
• 관리 비용, 가용성을 고려 하여 효과가 확실할 때만 사용

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

• Sequential 액세스 : 레코드간 논리적/물리적 순서를 따라 차례대로 읽어 나가는 방식
• Random 액세스 : 레코드간 논리적/물리적 순서를 따르지 않고 한 건을 읽기 위해 한 블록씩 접근 하는 방식
• I/O 튜닝의 핵심 원리
  • Random 액세스 발생량을 줄인다.
  • Sequential 액세스에 의한 선택 비중을 높인다.

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

• 인덱스 구성 칼럼이 조건절에서 모두 '=' 조건으로 비교시 Leaf 블록을 스캔하면서 읽은 레코드는 모두 테이블 액세스로 이어짐 (효율 최상)
• 인덱스 구성 칼럼 중 일부가 '=' 조건이 아니거나 조건절에서 생략 되더라도 그것이 뒤쪽 칼럼일 때는 비효율이 없음
  • 예) 인덱스[아파트시세코드 + 평형 + 평형타입 + 인터넷매물] 존재시

where 아파트시세코드 = :a
where 아파트시세코드 = :a and 평형 = :b 
where 아파트시세코드 = :a and 평형 = :b and 평형타입 = :c 
where 아파트시세코드 = :a and 평형 = :b and 평형타입 between :c and :d 

• 인덱스 선행 칼럼이 조건절에서 누락되거나 BETWEEN, 부등호, LIKE 같은 범위검색 조건 비교시 비효율 발생

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
  from 매물아파트매매 
 where 아파트시세코드='A01011350900056' and 평형 = '59' 
   and 평형타입 = 'A' and 인터넷매물 between '1' and '2'
 order by 입력일 desc 

• • 좋은예) 인덱스[아파트시세코드 + 평형 + 평형타입 + 인터넷매물] 존재시

• • 나쁜예) 인덱스[인터넷매물 + 아파트시세코드 + 평형 + 평형타입] 존재시

• • 인터넷매물(인덱스 선두 칼럼)에 BETWEEN 연산자 적용되어 나머지 조건을 만족하는 레코드들이 인터넷매물 별로 뿔뿔이 흩어져 있게 됨

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

• 위의 나쁜예 에서 범위검색 칼럼이 맨 뒤로 가도록 인덱스[아파트시세코드 + 평형 + 평형타입 + 인터넷매물] 순으로 변경 하는 대신, BETWEEN → IN-List 적용 가능

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
  from 매물아파트매매
 where 인터넷매물 in ('1', '2') and 아파트시세코드='A01011350900056' 
  and 평형 = '59' and 평형타입 = 'A' 
order by 입력일 desc 

• • 화살표 두개 : 수직 탐색이 두번 발생

------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | 
------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 37 | | 1 | INLIST ITERATOR | | | | 
| 2 | TABLE ACCESS BY INDEX ROWID | 매물아파트매매 | 1 | 37 | | 3 | INDEX RANGE SCAN | 매물아파트매매_PK | 1 | | ------------------------------------------------------------- 

• • INLIST ITERATOR

'매물아파트매매' 테이블. 스캔 수 2, 논리적 읽기 수 8, 물리적 읽기 수 0, 미리 읽기 수 0.

Rows StmtText
----- -------------------------------------------------------- 
2 SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드, ... 
2   |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) 
2     |--Index Seek(OBJECT:([..].[dbo].[매물아파트매매].[매물아파트매매_PK]), 
      | SEEK:([매물아파트매매].[인터넷매물]='1' AND 
      | [매물아파트매매].[아파트시세코드]='A01011350900056'AND 
      | [매물아파트매매].[평형]='59' AND 
      | [매물아파트매매].[평형타입]='A' OR  
      | [매물아파트매매].[인터넷매물]='2' AND  
      | [매물아파트매매].[아파트시세코드]='A01011350900056' AND  
      | [매물아파트매매].[평형]='59' AND  
      | [매물아파트매매].[평형타입]='A')
2        |--RID Lookup(OBJECT:([SQLPRO].[dbo].[매물아파트매매]) , SEEK:([Bmk1000]=[Bmk1000])) 

• • 스캔 수 2 : 수직 탐색이 두번 발생

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드 
  from 매물아파트매매
 where 인터넷매물 = '1' 
   and 아파트시세코드='A01011350900056'
   and 평형 = '59'
   and 평형타입 = 'A'
 union all
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
  from 매물아파트매매
 where 인터넷매물 = '2'
   and 아파트시세코드='A01011350900056'
   and 평형 = '59'
   and 평형타입 = 'A'
 order by 입력일 desc 

• In-List 적용시 아이템 개수 만큼 수직 탐색이 필요 하므로 비효율 발생 가능 (인덱스 높이 주의), 적정 아이템 개수에 적용 가능
• Oracle 의 경우 Index Skip Scan 이 대안이 될 수 있음

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

• 인덱스[회사 + 지역 + 상품명] 존재시

-- 범위검색 조건을 두개 이상 사용하면, 둘중 하나만 인덱스 스캔 범위를 결정하고 나머지는 필터 조건 역할 수행 (성능상 불리)
select 고객ID, 상품명, 지역, ... 
  from 가입상품
 where 회사 = :com
   and 지역 like :reg || '%'
   and 상품명 like :prod || '%' 

-- 지역 컬럼 검색 조건이 선택적 일 경우 대안1
< SQL1 >
 select 고객ID, 상품명, 지역, ... 
  from 가입상품
 where 회사 = :com 
   and 상품명 like :prod || '%' 

< SQL2 > 
select 고객ID, 상품명, 지역, ... 
  from 가입상품 
 where 회사 = :com 
   and 지역 = :reg 
   and 상품명 like :prod || '%' 

-- 지역 컬럼 검색 조건이 선택적 일 경우 대안2 (하단 SQL 은 효율적 동작, 상단 SQL 효율을 위해서 인덱스\[회사 + 상품명\] 필요)
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 || '%' 

4. 인덱스 설계

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

1. 조건절에 항상 사용되거나, 적어도 자주 사용되는 칼럼 선정
2. 위에서 선정된 칼럼 중 '=' 조건으로 자주 조회되는 칼럼을 앞쪽에 배치
3. 소트 오퍼레이션을 생략하도록 하기 위해 칼럼 추가 (정렬된 인덱스는 ORDER BY, GROUP BY 를 위한 소트 연산 생략 지원)

• 선택도 이슈 : 손익분기점을 고려하여 선택도(selectivity) 가 충분히 낮아야 함

나. 추가적인 고려사항

• 쿼리 수행 빈도
• 업무상 중요도
• 클러스터링 팩터
• 데이터양
• DML 부하 (기존 인덱스 수, DML 발생 빈도, 갱신 컬럼 고려)
• 저장 공간
• 인덱스 관리 비용

다. 인덱스 설계도 작성

• 인덱스 설계 시 시스템 전체 효율 고려를 위해 인덱스 설계도 필요

\