SQLP 요점정리 #14 : 3과목 Tuning - 4장 1,2절 Index

3과목 SQL 고급 활용 및 Tuning

4장 Index와 Join

4.1 Index

Index의 NULL값

• Oracle : 모두 NULL인 값은 저장하지 않음. NULL은 맨 뒤에 저장
• MS-SQL : 모두 NULL인 값도 저장. NULL은 맨 앞에 저장

Index 탐색

• 수직 탐색
  • 수평적 탐색을 위한 시작점을 찾는 과정
  • Root Node에서 Leaf Node까지의 검색
  • Index 조건에 맞는 첫번째 값을 찾아가는 연산
• 수평 탐색
  • Leaf Node끼리 연결된 Link를 통해서 범위 검색

Index Scan 방식

1. Index Range Scan

INDEX (RANGE SCAN) OF '인덱스명' (INDEX)

• 수직 탐색 후 Leaf Block을 필요한 범위만 Scan
• B*Tree Index의 가장 일반적이고 정상적인 형태
• Index 구성 칼럼 중 선두 컬럼이 조건절에 사용되어야만 가능
• 생성된 결과집합은 Index 컬럼 순대로 정렬된 상태 (ORDER BY, MIN, MAX 처리 가능)

2. Index Full Scan

INDEX (FULL SCAN) OF '인덱스명' (INDEX)

• 수직 탐색 없이 Leaf Block을 처음부터 끝까지 수평적으로 Scan
• 대개의 경우 Index의 선두 컬럼이 조건절에 없으면 Optimizer는 Table Full Scan을 고려한다.
• 하지만 다음 경우에는 Optimzer가 전략적으로 Index Full Scan을 고려한다.
  • Table이 대용량이거나, Index의 나머지 칼럼으로 대부분의 record를 filtering하고 일부에 대해서만 Table 액세스가 발생하는 경우
  • ORDER BY 연산을 해야하는데 first_rows Hint를 이용하여 부분범위 처리를 할 경우. 하지만 이 경우 사용자가 FETCH를 끝까지 다 할 경우 Table Full Scan보다 더 비효율적이게 된다.

3. Index Unique Scan

INDEX (UNIQUE SCAN) OF 'PK 또는 Unique Index명` (UNIQUE)

• 수직 탐색만으로 Scan
• Unique Index를 = 조건으로 검색할 경우에만 작동

4. Index Skip Scan

INDEX (SKIP SCAN) OF '인덱스명' (INDEX)

• INDEX의 선두컬럼이 조건절에 빠졌을 경우 대부분 Table Full Scan을 사용하지만 9i에서 새로 생긴 방식
• 선두 컬럼의 DISTICT 수가 적고, 후행 컬럼의 DISTINCT 수가 많을 경우 효과적

5. Index Fast Full Scan

• Index에 포함된 컬럼만으로 조회가 가능한 경우
• Index Tree구조를 무시하고 Segment 전체를 Multiblock Read 방식으로 Scan
• 결과집합 순서가 보장 안됨
• Parallel Scan 가능

6. Index Range Scan Descending

INDEX (RANGE SCAN DESCENDING) OF '인덱스명' (INDEX)

• Index Range Scan과 동일한 방법이나 뒤에서 앞으로 Scan
• Index에 저장된 순서와는 반대로 정렬된 결과집합을 출력
• MIN / MAX 값을 구할 경우 1건만 읽고 멈추는 실행계획으로 유도할 경우에 좋음

INDEX (RANGE SCAN (MIN/MAX)) OF '인덱스명' (INDEX)

Index 종류

1. B*Tree Index

• 가장 기본적인 Index 형식
• 최적의 성능을 위해서는 Index Fragmentation을 고려해야 함
  1. Unbalnaced Index
     • Root에서 Leaf Node까지의 Height가 다른 경우
     • BTree에서는 이런 현상이 발생하지 않음 (BalancedTree에서 Unbalanced 현상이 생기는건 말이 안됨)
  2. Index Skew
     • Index Enrty가 한쪽으로 치우치는 현상
     • 대량의 delete 작업 후에는 한쪽의 Leaf Node들이 empty 상태가 됨
     • empty Node들은 언제든 재사용 가능하지만, 다시 채워질 때까지는 Index Scan 효율이 떨어짐
     • MS-SQL은 주기적으로 Index를 정리해주므로 이런 현상이 나타나지 않음
  3. Index Sparse
     • Index Block 내의 밀도(density)가 떨어지는 현상
     • 아래와 같은 현상이 일어나면 Index Sparse라 판단
       • Index 내의 record는 많이 삭제했지만, Index Scan Block 수는 변화가 없음
       • Index record수는 일정한데, Index 사용 공간이 점점 커짐
  4. Index 재생성
     • 위와 같은 Fragmentation 현상을 제거하기 위해 Index를 삭제 후 재생성
     • 재생성은 수행시간과 부하가 큰 작업이므로 아래와 같이 예상효과가 확실할 때만 시행하는게 바람직함
       1. Index 분할에 의한 경합이 현저히 높을 때
       2. 자주 사용되는 Index Scan 효율을 높이고자 할 때. 특히 NL Join에서 반복 액세스 되는 경우
       3. 대량의 delete 작업 이후 새로운 record가 입력될 때까지 오랜 시간이 걸릴 때
       4. 총 record수가 일정한데도 Index가 계속 커질 때

2 Bitmap Index

• Column이 가질수 있는 값의 DISTINCT 수만큼의 BIT(0,1)를 2차원 배열로 표현
• 여러 INDEX를 동시에 활용할 수 있어서, 정형화되지 않은 ad-hoc query가 많은 환경에 효과적
• DISTICT수가 적을 경우에는 B*Tree Index보다 적은 공간을 차지하지만, DISTINCT수가 많으면 더 큰 공간을 차지할 수도 있다.
• 등치(=), 부정(<>), NULL 조건을 찾는데 효과적이다.
• 하나의 record만 변경되더라도 전체에 Lock이 걸리므로 OLTP환경에서는 쓸수 없음
• 대용량의 DW(OLAP) 환경에 적합

3. FBI (Function Based Index)

• 컬럼값 자체가 아닌 함수결과값으로 B*Tree Index를 생성
• Index 컬럼 자체를 가공하면 정상적인 Index 사용이 불가능한데, 이 경우 효과적으로 Scan 가능

CREATE INDEX EMP_NVL_COMM ON EMP(NVL(COMM,0));      -- 상여금이 없는 경우 0

CREATE INDEX EMP_UPPER_ENAME ON EMP(UPPER(ENAME));  -- 이름을 대소문자 구분없이 검색해야 할 경우

4. Reverse Key Index

• Key 값을 Reverse() 함수를 거쳐서 저장
• 한쪽으로만 치우치는 형태의 값을 분산적으로 저장하고자 할 경우 효과적
  • 주문일시의 경우 항상 증가된 값이 들어오르모 오른쪽 Leaf Block에만 데이터가 쌓인다. (Right Growing Index)
  • 이럴 경우 주문일자를 거꾸로 입력하면 Leaf 전체에 고르게 저장되므로 Transaction을 분산시키는 효과를 얻을 수 있다.
• 하지만, 등치(=) 조건으로만 검색이 가능 (부등호, BETWEEN, LIKE로의 검색은 불가능)

CREATE INDEX IDX_주문_주문일시 ON 주문(REVERSE(주문일시));

5. Cluster Index

CREATE CLUSTER C_DEPTNO (DEPTNO NUMBER(2)) INDEX;
CREATE INDEX   I_DEPTNO ON CLUSTER C_DEPTNO;

• Oracel Clusterd Table : 값은 Key값을 가진 record를 한 block에 저장 (1 block의 크기가 넘으면 cluster chain으로 연결)
• CLUSTER에 대한 Index를 생성
  • Index Key값은 Unique
  • Index Key : Table Record가 1:M 관계 (일반 Index는 1:1)
  • Index Key에서 Random Scan, Cluster 내에서는 Sqeuential Scan
• 다음과 같은 경우 유리
  • Scan 범위가 넓을 경우
  • 크기가 작고 NL Join으로 반복 Lookup하는 Table
  • Column 수가 적고 rocord가 많은 Table
  • 데이터 입력 패턴과 조회 패턴이 서로 다른 Table
    • ex. 실적등록 및 조회의 경우
      • 입력은 일자별로 진행되지만, 조회는 사원별로 하는 경우
      • 일반 Table의 경우 사원마다 365일의 데이터 페이지를 Random 액세스 해야함
      • 사번을 기준으로 CLUSTER를 생성하면 효과적

4.2 Index Tuning

Index Tuning 기초

• Index 선두 컬럼이 조건절에 사용되더라도 Index Range Scan이 불가능하거나 Index를 사용못하는 경우
  • 칼럼 가공 : SUBSTR(DNAME, 1, 2) = '영업'
  • 부정 비교 : DNAME <> '영업부'
  • NOT NULL 조건 : COMM IS NOT NULL (당연히 INDEX에는 NULL이 없으므로, 그냥 Index Full Scan과 같다.)
• Index 컬럼 가공에 대한 Tuning
  • SUBSTR(DNAME, 1, 2) = '영업' -> DNAME LIKE '영업%'
  • SAL * 12 > 3000 -> SAL > 3000 / 12
  • TO_CHAR(일시,'yyyymmdd') = :dt -> 일시 >= TO_DATE(:dt,'yyyymmdd') AND 일시 < TO_DATE(:dt,'yyyymmdd') + 1
  • 연령 || 직업 = '30공무원' -> 연령 = 30 AND 직업 = '공무원'
  • 회원번호 || 지점번호 = :str -> 회원번호 = SUBSTR(:str,1,2) AND 지점번호 = SUBSTR(:str,3,4)
• Implicit Conversion (묵시적 형변환)
  • 컬럼 타입과 비교값 타입이 다를 경우 묵시적 형변환이 발생
  • 문자형과 숫자형 : 문자형을 숫자형으로 변환
  • 문자형과 날짜형 : 문자형을 날짜형으로 변환
  • 변환되는게 컬럼쪽이라면 Index를 사용하지 못하고 Table Full Scan 발생
  • 이런 변환의 우선순위를 외우기 보다는 그냥 비교값을 컬럼 타입으로 변환해주는게 좋음

Table Random 액세스 최소화

• Index rowid에 의한 Table 액세스

TABLE ACCESS (BY INDEX ROWID) OF '컬럼' (TABLE)

• rowid는 disk 상의 위치정보이다.
• DB Buffer Cache에서의 해당 Block의 위치는 rowid의 hash값을 이용하여 bucket list로 찾아간다.
• bucket list 상에서 값을 찾는 과정에서도 Latch, 버퍼 Lock등을 이용해서 찾는다.
• Clustering Factor
  • Index상 같은 Block에 있는 record들라도 Table상에서는 다른 Block들에 있을 수 있다.
  • Index와 Table의 Record정렬 순서가 비교적 같은 경우에는 Clustering Factor가 좋아서 Index Scan 효율이 좋다.
• Index 손익분기점
  • Index Range Scan에 의한 비용이 Table Full Scan보다 느려지는 지점
  • 일반적으로 5 ~ 20% 정도지만 Clustering Factor가 나쁘면 5% 미만이 될수도 있고, 아주 좋을 땐 90%까지 올라가기도 한다.
  • Index rowid에 의한 Table 액세스 : Random 액세스, Single Block Read
  • Full Table Scan : Sequential 액세스, Multiblock Read
• 손익분기점 극복하기
  1. Clustering Index, IOT : Table을 Index구조로 생성함으로써 항상 정렬된 상태를 유지
  2. Index Key 이외의 미리 지정된 칼럼을 Leaf Level에 모두 저장 (MS-SQL의 Include index)
  3. Oracle의 Clusterd Table : Key값이 같은 record를 같은 Block에 저장함으로 Random 액세스는 Key값당 1번
  4. Partitionion : 자주 사용하는 컬럼 기준으로 파티셔닝을 하면 Full Table Scan이더라도 일부 파티션만 읽고 멈출 수 있음

Table Random 액세스 최소화 Tuning

1. Index 컬럼 추가

• 기존 Index의 구성이나 신규 추가는 실 운영 환경에서는 함부로 바꾸기가 쉽지 않다.
• 기존 Index에 조건절의 비교조건을 보고 뒤에 컬럼을 추가해줘서 최대한 조건비교를 Index 내에서 끝내고 Table 액세스를 최소화 한다.

CREATE INDEX I_EMP_01 ON EMP(DEPTNO, JOB);

SELECT /*+ INDEX(I_EMP_01) */ ENAME, JOB, SAL
  FROM EMP
 WHERE DEPTNO = 30
   AND SAL >= 2000;

위의 상황에서는 DEPTNO = 30인 조건에 맞는 record들에 대해서 Table에서 record들을 가져와야 한다.
아래와 같이 기존 Index를 그대로 두고 컬럼만 추가해 줄 경우 Index의 액세스는 줄일 수 없지만, Table 액세스는 줄일 수 있다.

CREATE INDEX I_EMP_01 ON EMP(DEPTNO, JOB, SAL);

• 만약 Index의 칼럼만으로도 조회가 가능하다면 Table 액세스 자체를 안해도 될 수 있다.
  • MS-SQL에서는 이 경우를 Covered Index, Covered Query 라 부른다.
  • Include Index : MS-SQL에서는 Index에 Key가 아닌 일반 컬럼 추가가 가능하다.
    • CREATE INDEX EMP_01 ON EMP (DEPTNO) INCLUDE (SAL);

2. IOT, Cluster 활용

• Index Key를 이용해서 한번만에 찾아가서 Sequential로 찾음
• Index Key의 Hash값으로 찾아가므로 = 조건으로만 검색되는 칼럼을 Key로 해야한다.

3. 수동으로 Clustering Factor 높이기

• 자주 이용되는 Index를 기준으로 Table을 재생성
• 한 Table에 Index가 여러 개인 경우 그 중 1개에 대해서만 Clustering Factor가 좋게 할 수 있다.

Index Scan 범위 최소화

• 일반적으로 Random 액세스 발생량을 줄이고, Sequential 액세스에 의한 선택 배중을 높이면 성능이 좋아진다.

1. Index 선행 칼럼이 범위조건일 경우

• 범위 조건 이후의 칼럼들에 대해서는 Range Scan을 해야 하므로 읽어야 할 범위가 넓어진다.
• 이럴 경우에는 Index 컬럼 순서를 바꿔주는게 효과적이다.

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

• 범위조건으로 검색하는 컬럼의 순서를 바꾸기가 힘든 경우에는 BETWEEN 조건을 IN-List로 바꿔주면 효과적이다.
  • 실행계획상에 INLIST ITERATOR로 처리되므로 Scan 범위를 줄일 수 있다.
  • IN-List는 UNION ALL로 따로 실행되는 거랑 같다.
  • IN-List의 개수가 적을 경우 효과적이다. 많다면 범위조건일 경우보다 비효율적일 수 있다.

DEPTNO BETWEEN 10 AND 20 -> DEPTNO IN (10, 20)

3. 범위조건이 2개 이상일 경우

CREATE INDEX I_PROD ON PRODUCT(COMPANY, PTYPE, PNAME);

SELECT * FROM PRODUCT
 WHERE COMPANY = :com
   AND PTYPE LIKE :ptype || '%'
   AND PNAME LIKE :pname || '%';

• 첫번째 범위조건에 의해서 스캔 범위가 거의 결정나고, 두번째는 필터 조건 역할만 한다.
• 만약 이 경우 첫번째 범위조건이 입력되지 않을 수 있다면 SQL을 2개로 나누는게 효과적이다.

SELECT * FROM PRODUCT
 WHERE :ptype IS NULL
   AND COMPANY = :com
   AND PNAME LIKE :pname || '%';

 UNION ALL

SELECT * FROM PRODUCT
 WHERE :ptype IS NOT NULL
   AND COMPANY = :com
   AND PTYPE = :ptype
   AND PNAME LIKE :pname || '%';

Index 설계

• 기본 공식
  1. 조건절에 항상 사용되거나, 자주 사용되는 컬럼을 선정
  2. = 조건으로 자주 조회되는 칼럼을 앞으로
  3. 조건절에 사용되지 않더라도 Sort 연산 (ORDER BY, GROUP BY)를 대체할 목적으로 구성이 가능
  4. 선택도(Selectivy)가 충분히 낮지 않다면 Full Table Scan보다 느려지기 때문에 의미가 없음
  5. 추가 고려사항
     • Query 수행 빈도
     • 업무상 중요도
     • Clustering Factor
     • 데이터량
     • DML 부하
     • 저장 공간
     • Index 관리 비용