[1] 인덱스 튜닝

(1) B*Tree 인덱스를 활용한 인덱스 튜닝 방법

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

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

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

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

• 묵시적 형변환 : 인덱스 칼럼을 사용자가 명시적으로 가공하지 않더라도 조건절에서 비교되는 두 값의 데이터 타입이 다르면 내부적으로 형변환
• 가급적 명시적으로 변환함수를 사용하는 것이 좋다.
• 문자형과 숫자형이 만나면 숫자형으로, 문자형과 날짜형이 만나면 날짜형으로 변환하는 등 데이터 타입간
  우선순위가 존재한다.
  - 성능을 위해서라면 인덱스 칼럼과 비교되는 반대쪽을 인덱스 칼럼 데이터 타입에 맞춰면 된다.
• 묵시적 형변환은 주로 성능 측면에서 언급되곤 하지만, 올바른 결과집합을 얻기 위해서라도 변환함수를 명시하는 것이 바람직하다.
  - 묵시적 형변환은 쿼리 수행 도중 에러를 발생시키거나 결과집합을 틀리게 만드는 요인이 될 수 있기 때문

SQL> select * from emp where deptno='20'; -- access("DEPTNO"=20) => 문자형 상수 '20'이 숫자형 20으로 변환됨
-- 묵시적 형변환이 잘못된 경우
 SQL> select * from emp where cdeptno=20; -- filter(TO_NUMBER("CDEPTNO")=20) => 문자형 CDEPTNO 칼럼이 숫자형으로 변환됨
-- 문자형 cdeptno 칼럼이 숫자형으로 변환된 것을 볼 수 있고, emp 테이블을 Full Scan하는 실행계획이 수립되었다.

2. 부정형 비교

• 정상적인 인덱스 사용은 어렵다.

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

• is not null 조건도 부정형 비교에 해당된다.

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

• Oracle에서 ‘부서코드’에 단일 칼럼 인덱스가 존재한다면 그 인덱스 전체를 스캔하면서 얻은 레코드는 모두 ‘부서코드 is not null’ 조건을 만족한다.
• Oracle은 단일 칼럼 인덱스에 null 값은 저장하지 않기 때문이다.
• 결합 인덱스일 때는 인덱스 구성 칼럼 중 하나라도 값이 null이 아닌 레코드는 인덱스에 저장한다.
• 인덱스도 구성칼럼이 모두 null인 레코드에서 is null 조건만으로 검색할 때 :: 인덱스만 뒤져선 찾을 수 없다.
  즉 , 인덱스 사용이 불가능
• SQL Server는 단일, 결합을 가리지 않고 null이 아닌 레코드를 인덱스에서 모두 찾을 수 있다.

is null 조건에 대한 Index Range Scan이 가능한 경우

• 다른 인덱스 칼럼에 is null이 아닌 조건식이 하나라도 있는 경우
• 다른 인덱스 칼럼에 not null 제약이 있는 경우
• 물론 인덱스 선두 칼럼이 조건절에 누락되지 않아야 한다.

[2] 테이블 Random 액세스 최소화

테이블 Random 액세스 = ‘Table Access By Index ROWID’

(1) 인덱스 ROWID에 의한 테이블 Random 액세스

• 쿼리에서 참조되는 칼럼이 인덱스에 모두 포함되는 경우가 아니라면, ‘테이블 Random 액세스’가 일어난다.
• 즉, 인덱스 찾아서->ROWID를 이용->데이터찾기 작업을 한다.
• ‘Table Access By Index ROWID’라고 표시된 부분을 말한다.
• 인덱스 ROWID에 의한 테이블 액세스 구조

1. 인덱스 ROWID

• 인덱스에 저장돼 있는 rowid
• 흔히 ‘물리적 주소정보’이며 오브젝트 번호, 데이터 파일 번호, 블록 번호 같은 물리적 요소들로 구성된다.
• rowid가 물리적 위치 정보로 구성되지만 인덱스에서 테이블 레코드로 직접 연결되는 구조는 아니기 때문에
  보는 시각에 따라서는 ‘논리적 주소정보’라고 표현한다.
• 데이터 블록을 읽을 때는 항상 버퍼 캐시를 경유하며 메모리 상에서 버퍼 블록을 찾기 위해 해시 구조와 알고리즘을 사용
  • 해시 키(Key) 값으로는 rowid에 내포된 데이터 블록 주소(Data Block Address, DBA)를 사용

2. 인덱스 ROWID를 이용해 테이블 블록을 읽는 메커니즘

1. 인덱스에서 하나의 rowid를 읽고 DBA(디스크 상의 블록 위치 정보)를 해시 함수에 적용해 해시 값을 확인한다.
2. 해시 값을 이용해 해시 버킷을 찾아간다.
3. 해시 버킷에 연결된 해시 체인을 스캔하면서 블록 헤더을 찾는다.
4. 해시 체인에서 블록 헤더를 찾으면 거기 저장된 포인터를 이용해 버퍼 블록을 읽는다.
5. 해시 체인을 스캔하고도 블록 헤더를 찾지 못하면, LRU 리스트를 스캔하면서 Free 버퍼를 찾는다.
   디스크에서 읽은 블록을 적재하기 위해 빈 캐시 공간을 찾는 것이다.
6. LRU 리스트에서 Free 버퍼를 얻지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보한다.
7. Free 버퍼를 확보하고 나면 디스크에서 블록을 읽어 캐시에 적재한다.

3. 인덱스 rowid에 의한 테이블 액세스가 생각만큼 빠르지 않은 이유

• 위 처리 과정 중에는 래치(Latch), 버퍼 Lock 같은 Internal Lock을 획득하거나 다른 백그라운드 프로세스의 선처리 결과를 기다리는 내부 메커니즘이 작동한다.
• 경합까지 발생한다면 블록 하나를 읽더라도 생각보다 큰 비용을 치르게 된다.
• 특히, 다량의 테이블 레코드를 읽을 때의 성능 저하가 심각하다.

4. 클러스터링 팩터

• 특정 칼럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미
• 클러스터링 팩터를 통해 인덱스 ROWID에 의한 테이블 액세스 비용을 평가
• 클러스터링 팩터가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다.
• 예를 들어 「거주지역 = ‘제주’」에 해당하는 고객 데이터가 물리적으로 근접해 있다면 흩어져 있을 때보다 데이터를 찾는 속도가 빨라진다.

(2) 인덱스 손익분기점

• Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점
• 선택도(Selectivity)가 높은 인덱스는 효용가치가 낮다.
• 인덱스 손익분기점이 10%라는 의미는 1,000개 중 100개 레코드 이상을 읽을 때는 인덱스를 이용하는 것보다 테이블 전체를 스캔하는 것이 더 빠르다.
• 클러스터링 팩터에 따라 크게 달라진다.
  • 클러스터링 팩터가 나쁘면 손익분기점은 5% 미만에서 결정되며, 심할 때는(BCHR가 매우 안 좋을 때) 1% 미만으로 결정된다.

1. 인덱스에 의한 액세스가 Full Table Scan보다 더 느리게 만드는 가장 핵심적인 두 가지 요인

(1) 액세스 방식 : Random / Sequential

• 인덱스 rowid에 의한 테이블 액세스는 Random 액세스
• Full Table Scan은 Sequential 액세스

(2) 디스크 I/O 방식 : Single Block Read / Multiblock Read

• 인덱스 rowid에 의한 테이블 액세스는 Single Block Read 방식
• Full Table Scan은 Multiblock Read 방식

2.손익분기점 극복하기

• 손익분기점 원리에 따르면 선택도(Selectivity)가 높은 인덱스는 효용가치가 낮지만, 그렇다고 테이블 전체를 스캔하는 것은 부담스러울 때가 많다.

(1) SQL Server의 클러스터형 인덱스와 Oracle IOT

• 테이블을 인덱스 구조로 생성하는 것
• 테이블 자체가 인덱스 구조이므로 항상 정렬된 상태를 유지한다.
• 인덱스 리프 블록이 곧 데이터 블록이기 때문에 인덱스를 수직 탐색한 다음에 테이블 레코드를 읽기 위한 추가적인 Random 액세스가 불필요하다.

(2) SQL Server의 Include Index

• 인덱스 키 외에 미리 지정한 칼럼을 리프 레벨에 함께 저장하는 기능
• 테이블 Random 액세스 횟수를 줄인다.

(3) Oracle 클러스터 테이블

• 키 값이 같은 레코드를 같은 블록에 저장하기 때문에 클러스터 테이블에 대한 클러스터 인덱스를 이용할 때는
  테이블 Random 액세스가 키 값별로 한 번씩만 발생한다.

(4) 파티셔닝

• 읽고자 하는 데이터가 많을 때 인덱스를 이용하지 않는 편이 낫다.
• 그치만 수천만 건에 이르는 테이블을 Full Scan해야 한다면 난감한 경우 대량 범위검색 조건으로 자주 사용되는 칼럼 기준으로 테이블을 파티셔닝한다.
• Full Table Scan 하더라도 일부 파티션만 읽고 멈춘다.
• 클러스터와 파티셔닝의 차이
  • 클러스터 : 기준 키 값이 같은 레코드를 블록 단위로 모아 저장
    • 클러스터에 도달해서는 Sequential 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다.
  • 파티셔닝은 : 세그먼트 단위로 저장

(5) 부분범위처리

• 인덱스 스캔 비효율이 없도록 잘 구성된 인덱스를 이용한다.

(3) 테이블 Random 액세스 최소화 튜닝 방법

1. 인덱스 칼럼 추가

2. Covered Index

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

3. Include Index :: 위에 있음

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

해시 클러스터 테이블

• 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조
• 클러스터 키로 데이터를 검색하거나 저장할 위치를 찾을 때 해시 함수를 사용
• 해시 함수가 인덱스 역할을 대신한다.
• 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환한다.
• 별도의 인덱스 구조를 생성하지 않는 장점이 있다.

해시 클러스터의 활용성을 떨어뜨리는 중요한 제약사항

• ‘=’ 검색만 가능하다.
• 항상 ‘=’ 조건으로만 검색되는 칼럼을 해시 키로 선정

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

• 테이블에는 데이터가 무작위로 입력되는 반면, 그것을 가리키는 인덱스는 정해진 키(key) 순으로 정렬되기 때문에 대개 클러스터링 팩터가 좋지 않다.
• 클러스터링 팩터가 나쁜 인덱스를 이용해 많은 양의 데이터를 읽는 SQL 튜닝하기가 가장 어렵다.

해당 인덱스 기준으로 테이블을 재생성함으로써 클러스터링 팩터를 인위적으로 좋게 만드는 방법

• 극적이지만 인덱스가 여러 개인 상황에서 특정 인덱스를 기준으로 테이블을 재정렬하면 다른 인덱스의 클러스터링 팩터가 나빠질 수 있다.

[3] 인덱스 스캔범위 최소화

(1) 데이터베이스 I/O 원리 : Random 액세스와 Sequential 액세스

• Sequential 액세스 : 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식
• Random 액세스 : 레코드간 논리적, 물리적 순서를 따르지 않고 한 건을 읽기 위해 한 블록씩 접근(=touch)하는 방식

(2) 인덱스 선행 칼럼이 범위조건일 때의 비효율

1. 인덱스 구성 칼럼이 조건절에서 모두 등치(=) 조건일 때

• 리프 블록을 스캔하면서 읽은 레코드는 모두 테이블 액세스로 진행
• 읽고서 버리는 레코드가 하나도 없으므로 인덱스 스캔 단계에서의 효율은 최상임
• 인덱스 칼럼 중 일부가 ‘=’ 조건이 아니거나 조건절에서 생략되더라도 그것이 뒤쪽 칼럼일 때는 비효율이 없다.

2. 인덱스 선행 칼럼이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건일 때

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

(3) 범위조건을 In-List로 전환

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

• between 조건을 아래와 같이 IN-List로 바꿔주면 가끔 큰 효과가 일어난다.

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

1. 인덱스 선두 칼럼의 between 조건을 IN-List 조건으로 바꿀 때 주의할 점

• IN-List 개수가 많지 않아야 한다. 필요 없는 범위를 스캔하는 비효율은 사라지겠지만 인덱스 수직 탐색이 여러 번 발생하기 때문이다.

2. IN-List 개수가 많을 때

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

3. Index Skip Scan

• Oracle이라면 위와 같은 상황에서 Index Skip Scan이 유용할 수 있다.
• 인덱스 선두 칼럼이 누락됐을 때뿐만 아니라 부등호, between, like 같은 범위검색 조건일 때도 사용될 수 있다.

(4) 범위조건을 2개 이상 사용할 때의 비효율

• 스캔량이 소량일 때는 그 차이가 미미하지만 대량일 때는 상당한 성능차이가 난다.

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

• 인덱스 구성이 [회사 + 지역 + 상품명]일 때, 범위검색 조건을 2개 이상 사용한다.
  • 첫 번째가 인덱스 스캔 범위를 거의 결정한다.
  • 두 번째는 필터 조건 역할만 하기 때문에 성능상 불리

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

• 지역 칼럼에 대한 검색조건이 입력되지 않을 수도 있어 위와 같이 LIKE 연산자를 사용한 거라면 2개로 만들어 사용하는 것이 좋다.

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] 인덱스 설계

(1) 결합 인덱스 구성을 위한 기본 공식

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

1. 인덱스 선두 칼럼이 조건절에 반드시 사용한다.

• 조건절에 항상 사용되거나, 적어도 자주 사용되는 컬럼들을 선정하는 것이다.
• 그렇게 선정된 칼럼 중 ‘=’ 조건으로 자주 조회되는 칼럼을 앞쪽에 두어야 한다.

2. 소트 오퍼레이션을 생략하도록 하기 위해 칼럼을 추가한다.

• 인덱스는 항상 정렬 상태를 유지하므로 order by, group by를 위한 소트 연산을 생략할 수 있도록한다.
• 따라서 조건절에 사용되지 않은 칼럼이더라도 소트 연산을 대체할 목적으로 인덱스 구성에 포함시킴으로써 성능 개선을 도모한다.
• 인덱스 칼럼 구성과 같은 순서로 누락 없이(뒤쪽 칼럼이 누락되는 것은 상관없음) order by절에 기술해 주어야 한다.
• 단, 인덱스 구성 칼럼이 조건절에서 ‘=’ 연산자로 비교된다면, 그 칼럼은 order by절에서 누락되거나 인덱스와 다른 순서로 기술하더라도 상관없다. 이런 규칙은 group by절에도 똑같이 적용된다.

3. 선택도가 충분히 낮은지 판단

• 인덱스를 스캔하면서 테이블을 액세스하는 양이 일정 수준(=손익분기점)을 넘는 순간 Full Table Scan 보다 오히려 느려지기 때문이다.

(2) 추가적인 고려사항

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