메뉴 건너뛰기

bysql.net

이화식 선생님의 새로쓴 대용량 데이터 베이스 1의 온라인 스터디 입니다.
매주 각 스터디 팀원들이 담당 분량을 정리해서 올리고 토론식으로 진행합니다.
스터디 포스팅은 팀원들에의해 이루어지지만 스터디 참여는 사이트 회원 모두가 가능합니다.

진행기간: 2009.07 ~ 2009.10. 종료

4.1. 인덱스 선정기준(4.1.1.-4.1.5.)

2009.08.17 10:35

balto 조회 수:100838

(편집이 잘 안될 수도 있어 파일 첨부했습니다.)

 

4장 인덱스 수립전략

(무엇을 배우는가?)

- 최소의 인덱스로 최대의 액세스 형태를 만족시켜야 한다.

- 인덱스 구성을 위한 종합적인 전략을 수립하는 절차를 설명한다.


4.1. 인덱스의 선정 기준

4.1.1. 테이블 형태별 적용기준


가) 적은 데이터를 가진 소형 테이블

- 적은 데이터란 DB_FILE_MULTIBLOCK_READ_COUNT 값 보다 크기가 작은 블럭을 가진 테이블이라 가정하자.

- 인덱스 유무가 성능에 영향을 미치지 않으므로 인덱스를 안 만들 수도 있다

  (인덱스를 생성하는 것이 나쁘다는 애기는 아니다.)

- 기본키에는 인덱스를 생성하는 것을 권장한다.

- 인덱스 존재 여부는 조인의 실행 계획이나 무결성에 영향을 미친다.

- 인덱스 일체형 테이블을 구성하는 것도 좋은 방법이다.(IOT)

- 대량의 반복이 발생할 때는 인덱스가 조금이라도 효과가 있을 수 있다.


나) 참조되는 역할을 하는 중대형 테이블

- 고객테이블 같이 참조 위주 테이블, 데이터는 많고 액세스 조건이 일정하며, 랜덤액세스되며 데이터증감이 적다.

- 좁은 범위만 필요하거나 조인때 내측루프에 기본키에 의해 연결

- 인덱스를 많이 만들어 두는 것이 효과적이며, PCTFREE를 0으로 만들어도 좋음.

- 간혹 인덱스 재생성하면 된다.


다) 업무의 구체적인 행위를 관리하는 중대형 테이블

- 매출정보테이블 같이 액세스 조건이 다양하고, 데이터가 꾸준히 증가한다.

- B-tree 인덱스가 유리하고 결합인덱스가 많이 사용되며 결합인덱스 컬럼 구성에 따라 영향을 받는다.

- 인덱스 전략 수립에 신중해야 한다. 액세스 유형을 잘 수집하고 인덱스 조합을 잘 찾는다.

- 인덱스 추가시 데이터 증감에 따른 속도 저하도 가능하므로 주의한다.

- 비트맵인덱스를 고려해볼 수도 있다.(배치처리로 다량의 데이터가 추가되고, 다양한 액세스가 발생한다면) ?


라) 저장용 대형 테이블

- 로그기록처럼 대량의 테이블에 지속적으로 데이터가 입력된다. 저장이 목적이며 액세스 형태가 다양하지 않다.

- 삭제나 갱신이 거의 없기 때문에 PCTFREE를 0으로 둘 수 있다.

- PRIMARY KEY를 두지 않을 수 있으며 필요시 UNIQUE INDEX를 생성해 준다.

- 파티션을 고려할 수 있다.(오래된 데이터는 참조 가능성이 줄어듦으로 최근의 파티션에 많은 인덱스를 생성하고 그렇지 않은 경우는 최소한의 인덱스를 생성한다.

- 특정파티션에 인덱스를 만들기 위해 USQBLE/UNUSABLE 기능을 사용한다.

  (UNUSABLE 파티션에 질의를 하면 오류이므로 FROM 절에 특정 파티션을 지정한다.


4.1.2. 분포도와 손익분기점

- 인덱스가 처리범위를 줄이는데 얼마나 효율적인가(충실한가는) 분포도와 관련있다.

- 보통은 컬럼의 분포도가 10-15%를 넘지 않아야 효과가 있다(왜?)

  (넘으면 전체 테이블 스캔이 더 유리하다)

   비교 : 전체테이블 100 블록 vs 인덱스 1블록 + 0.1 * ...

- 각 컬럼의 분포도는 높아도 결합인덱스의 분포도는 낮아진다.

- 클러스터링을 할 경우 클러스터링 팩터의 향상은 인덱스 액세스 비용을 낮춘다.

- 손익분기점을 넘는 경우라도 부분범위를 액세스할 경우 손익이 생긴다.

  (예, 30% 손익분기점이라도 1/100만 액세스했을 경우 이익이다)


4.1.3. 인덱스머지와 결합인덱스 비교

- 검색조건이 한개의 테이블에 2개 속성인 경우 비교

(인덱스머지)

- 인덱스머지는 INDEX1과 INDEX2에서 검색하여 인덱스에 저장된 ROWID를 비교하여 검색(테이블 액세스하기 전에 ROWID를 모두 알아낸다)

- 그러나, 두 인덱스가 비슷한 분포도를 가질때 유리, 많이 차이나면 적은쪽 인덱스만 이용하여 테이블을 직접액세스하는 것이 유리(한쪽이 = 조건이 아닐 경우)

    x1 + x2 + selected blocks(c1,c2) > x1 + selected blocks(c1) 

  예를들면 : 1 + 3 + 1 > 1 + 2 (?)

(결합인덱스)

- 결합인덱스로부터 ROWID를 모두 찾아낸다)


4.1.4 결합인덱스의 특징

- 결합인덱스의 단점

   컬럼 중 일부만 조건을 받거나 = 아닌 연산자가 많을 경우

가) 분포도와 결합순서의 상관관계

- 인덱스 결합시 분포도가 좋은 컬럼이 앞에 있는 것이 좋은가?

  =로 비교되면 순서는 별 영향이 없다. B-tree 검색시 영향을 받지 않는다.

나) = 이 결합순서에 미치는 영향

- 첫번째 조건이 = 이면 첫번째 조건을 만족하는 것들 중 두번째 조건의 범위를 액세스 한다.

- 첫번째 컬럼이 = 이 아닌 범위 조건이 오면 첫번째 조건의 범위를 모두 검색하여 두번째 조건을 만족하는 것을 찾는다.

- 결론적으로 결합인덱스에서는 = 조건인 컬럼이 먼저 있어야하므로 분포도 보다 컬럼의 순서가 중요하다.

다) IN 연산자를 이용한 징검다리 조건

- 속성1이 = 검색이 주이고 속성2가 =이 아닌 경우가 많다면 (속성2, 속성1) 결합인덱스를 생성한다.

- 그러나 가끔씩 속성1이 LIKE나 BETWEEN을 사용하고 속성2가 =을 사용한다면 결합인덱스 (속성2, 속성1)을 또 만든다.(?)

- 결합인덱스에서 IN과 BETWEEN 연산자 차이 -> 결합인덱스에서 필요한 부분만 검색

- 결합인덱스에서 IN 연산자의 징검다리 효과(그림 1-4-5)

   WHERE col2 IN (111,112) AND col1 ='A'

    => WHERE (col2=111 AND col1='A') OR (col2=112 AND col1='A')

라) 처리범위에 직접적인 영향을 주지 못하는 컬럼의 추가 기준

- = 비교가 아니지만 결합인덱스에 포함을 시킬지 여부

- = 비교가 아니어도 결합인덱스에 있으면 인덱스에서 검색을 시도하므로 ROWID를 직접 찾을 수 있다.

- SELECT 리스트에만 사용되는 컬럼도 경우에 따라서는 필요하다 - 인덱스조인을 유도(?)


4.1.5. 결합인덱스의 컬럼 순서 결정 기준

- 결합인덱스 컬럼 순서 결정 우선순위

        1단계 : 항상 사용하는가?

        2단계 : 항상 = 로 사용하는가?

        3단계 : 어느 것이 더 좋은 분포도를 갖는가?

        4단계 : 자주 정렬되는 순서는 무엇인가?

        5단계 : 부가적으로 추가시킬 컬럼은 어떤 것으로 할 것인가?

- 예) 다음 예에서 1개의 인덱스를 생성한다면?

        ① c1(=), c2(between), c3(=)

        ② c2(=), c3(between), c4(>)

        ③ c1(=), c2(=), c3(like)

        ④ c1(=), c2(=), c4(>)

        

        1단계 : 항상 사용하는가?

        => c2(4번), c1(3번), c3(2번), c4(2번)

           c2가 가정 먼저.

          그렇지만 c2이 카디널리티가 높지 않아 인덱스스킵스캔으로한다면 ②번은 c1(=)이 있다고 본다. 정답은 c1이 가장 먼저.

          아니면 ② 번을 따로 만족하는 인덱스를 만든다면 c1=c2=3번이 된다.

        2단계 : 항상 = 로 사용하는가?

        => c1=c2=3회라고 가정하면 c1은 항상 =를 사용하므로 정답은 c1+c2

        3단계 : 어느 것이 더 좋은 분포도를 갖는가?

        => ①번의 c2를 BETWEEN을 IN으로 바꾸면 = 처리가 가능하므로 c1+c2, c2+c1 2개의 정답이고, c1과 c2 중 분포도가 좋은 것을 앞으로한다.

주의 : 액세스 형태가 바뀌면 인덱스도 바뀌어야하므로 액세스형태를 예측하여 바꾼다. 예를 들면 =가 아닌 다른 연산자 사용 가능성을 검토한다.

        4단계 : 자주 정렬되는 순서는 무엇인가?

        => 정렬이 필요한 속성을 감안한다. 정렬이 필요한 속성을 인덱스로 했을 경우 부분범위처리에도 유리하다. 

        5단계 : 부가적으로 추가시킬 컬럼은 어떤 것으로 할 것인가?

        => c3과 c4는 체크기능을 담당한다. c3가 앞에오면 유리한 경우는 ①③번, c4가 앞에오면 유리한 경우는 ④번, 같은 경우는 ②번 경우이므로 c1+c2+c3+c4로 생성.

        만약 ④가 빈번하게 사용된다면 c1+c2+c4로 생성.




번호 제목 글쓴이 날짜 조회 수
25 1.1. 테이블과 인덱스의 분리형 [3] file 운영자 2009.07.04 105345
24 1.2 인덱스 일체형 테이블(Inde-Organized Table) [3] shadou 2009.07.06 100835
23 1.3 클러스터링 테이블 [5] file 운영자 2009.07.05 103060
22 1.액세스 영향 요소의 이해 tofriend 2009.07.01 127540
21 2.1. B-tree 인덱스 [2] 운영자 2009.07.10 107246
20 2.2. 비트맵(Bitmap) 인덱스 [2] file 휘휘 2009.07.14 122603
19 2.2. 연결고리 상태가 조인에 미치는 영향 file balto 2009.09.05 118402
18 2.3 함수기반 인덱스 (FBI,Function-Based Index) [1] 운영자 2009.07.11 160408
17 2.3. 조인 종류별 특징및 활용 방안 휘휘 2009.09.12 93187
16 2.3.4. 해쉬 (Hash) 조인 file 휘휘 2009.09.13 98360
15 2부 1.4.(1.4.1-1.4.7) 부분범위처리로의 유도 (중요하지 않은 부분 조금 미완성) file balto 2009.08.29 95823
14 2부 2.3.(2.3.6-2.3.8) 조인 종류별 특징 및 활용 방안(스타조인, 비트맵조인인덱스) [1] file balto 2009.09.07 107225
13 3.1 SQL의 실행계획 (1/3) 운영자 2009.07.18 102185
12 3.1 SQL의 실행계획 (2/3) 운영자 2009.07.24 101084
11 3.1 SQL의 실행계획 (3/3) file 운영자 2009.07.18 93379
10 3.2. 실행계획의 유형 (1/3) - 3.2.1. 스캔의 기본유형 휘휘 2009.08.02 103279
9 3.2.2. 데이터 연결을 위한 실행 계획 [2] file balto 2009.08.06 101281
8 3.2.4. 비트맵(Bitmap) 실행계획 휘휘 2009.08.23 95118
7 3.3. 실행계획의 제어 [1] 휘휘 2009.08.18 100567
» 4.1. 인덱스 선정기준(4.1.1.-4.1.5.) [3] file balto 2009.08.17 100838