인덱스의 필요성

사용자가 인덱스를 사용하는 이유

• 데이터베이스에 저장된 자료를 더욱 빠르게 조회하기 위해 인덱스를 생성하요 사용함
• 모든 SQL이 인덱스를 사용해야만 하는가?
  -> 일반적으로, 인덱스는 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에 효울적이며, 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않은 것이 더 나음

B*TREE 구조

• B*TREE란 balanced tree를 의미한다. 가장 많이 사용되는 인덱스의 구조라 할 수 있으며, 인덱스의 데이터 저장 방식이기도 하다.
• Root(기준)/Branch(중간)/Leaf(말단) Node로 구성됨
  -> Branch 노드는 Leaf 노드에 연결되어 있으며, 조회하려는 값이 있는 Leaf노드까지 도달 하기 위해 비교/분기해야 할 값들이 저장됨.
  -> 인덱스를 구성하고 있는 컬럼의 데이터 외에 그 데이터가 저장되는 주소 정보를 같이 보관하고 있다. 특히, 인덱스로 구성된 칼럼의 값들은 하나같이 sorting이 되어있다. 이때 이것이 갖고 있는 ROWID는 데이터 저장주소이다.

B*TREE 인덱스 구조

• Root Node는 나무의 뿌리를 뜻한다.
• Branch Node는 줄기 또는 가지.
• 입력시킨 모든 값들이 Sorting되어있는 가운데 존재하는 부분이 Leaf Node이다. (이파리)

물리적인 저장형태

• 현재 Branch Node에 입력한 모든 값들은 사실상 리프노드에 모두 존재하는 값들 중 하나이며, 각 블락별로 볼 때 가장 작은 값들에 해당된다라고 볼 수 있다.
• Leaf Node는 컬럼의 데이터들만 갖고 있는 것이 아니고 사실상 rowid까지 가지고 있다.

논리적인 저장형태

• 앞서 본 B*TREE 형태의 구조를 단순화시켜서 논리적인 구조로 나타낸 부분이다. 즉, 데이터가 모두 저장되어 있는 Leaf Block만을 통해서 SORTING을 감안해서 나타낸 BLOCK이다.
• 그림상으로 보면 제일 위에 있는 것들이 작은 값, 맨 밑에 있는 데이터가 제일 큰 값이다.

B*TREE의 구조

• B*TREE 구조의 핵심은 Sort!

1. ORDER BY에 의한 SORT를 피할 수 있음
   -> SORTING이 발생될 때 1차적으로 발생하는 하드웨어가 사실 메모리가 된다. 메모리 사용을 통해서 SORTING이란 작업이 행해지고, 그때 처리하고자 하는 데이터 크기가 사용되는 메모리 크기보다 훨씬 큰 데이터인 경우는 DISK I/O가 추가적으로 발생한다. SORTING에 따른 전반적인 자원의 소모를 해소해주기 위해서는 SORTING을 하지 않도록 하는것이 무엇보다 중요하다. 따라서, ORDER BY절에 기준을 대신해 컬럼들을 대상으로 해서 INDEX를 만들어 놓게 되면, 그 INDEX 사용을 통해서 ORDER BY로 인해서 나타나는 SORTING을 대체할 수 있다.
   
   
2. MAX/MIN의 효율적인 처리가 가능함
   -> 보통 어떤 칼럼의 데이터중 제일 큰 값, 제일 작은 값을 제어하기 위해서 MAX/MIN을 사용하는 경우가 있다. 사실상 MAX/MIN은 각각 한건씩이다. 보통은 함수에 의해서 구하고자 하는 칼럼이 인덱스로 존재하게 되면 인덱스의 데이터를 통해서 바로 찾아낼 수 있다. 즉, 각각 한건씩만을 찾아낼 수 있다.

B*Tree 구조 활용 예1

• 어떤 테이블이 갖고있는 PRIMARY KEY로 된 인덱스를 볼 수 있다.
• 첫번째 컬럼이 맨 처음부터 등장하고, 2번째 컬럼도 ORDER BY줄에 명시되어 있고, 3번째 컬럼도 ORDER BY절에 명시되어있다? (이거 뭔 말이야?) 이와 같이, 인덱스를 구성하는 컬럼의 순서들과 일치하도록 ORDER BY 절에 그 컬럼들이 나타나있는한 현재 옵티마이저가 그 PK를 사용하게 되었을 때 사실은 기본적으로 내림차순으로 접근함을 알 수가 있다 .
• 이와 같이, SQL문이 갖는 ORDER BY절에 명시된 컬럼들을 대상으로 INDEX를 만들어 놓고 있으면 사실은 ORDER BY 때문에 있을 수 있었던 SORTING을 안할 수 있다라고 볼 수 있다.
  (이해가 잘안됩니당)

B*Tree 구조 활용 예2

• MAX값을 구할려고 하는 컬럼이 현재 테이블에 갖고 있는 인덱스, PK와 갖고 있는 컬럼 중 하나입니다.
• PK가 사용되는 경우에는 사실 그 데이터를 직접 한건만 처리하는 가운데 해당 값을 바로 구한다라고 할 수 있다.(이것도 이해가 잘안되는데,,)
• 인덱스로 구성되어 있지 않은 가운데서 MAX를 구하게 된다면 조건에 대한 데이터를 다 찾고나서, SORTING을 통해서 해당 값을 찾고 말았을 것이다.
• 반면, 지금 보는것과 같이, 인덱스를 구성하고 있는 경우에는 인덱스에 SORTING된 결과 중에서 바로 해당 값을 찾고 말았다라고 할 수 있다.

인덱스 선정 절차

1. 프로그램 개발에 이용된 모든 테이블에 대하여 Access Path 조사(각 테이블 중심으로 where절, order by절까지 다 검사해야한다 = Access Path)
2. 인덱스 칼럼 선정 및 분포도 조사
3. Critical Access Path(시스템적으로 자주 사용되는 조건들) 결정 및 우선 순위 선정
4. 인덱스 칼럼의 조합 및 순서 결정(where, and를 중심으로 해서 같이 조건으로 등장하는 컬럼들이 있을 수 있다. 단, 컬럼의 분포가 각각은 나쁘긴 해도 결합해서 분포가 계선되는 경우에는 결합 인덱스로 구성하는 것이 성능상 도움이 된다. (결합 인덱스 생성을 검토한다.))
5. 시험 생성 및 테스트
6. 결정된 인덱스를 기준으로 프로그램 반영
7. 실제 적용

인덱스 생성 및 변경 시 고려할 사항

1. 기존 프로그램의 동작에 영향성 검토
2. 필요할 때마다 인덱스 생성으로 인한 인덱스 개수의 증가와 이로 인한 DML 작업의 속도
3. 비록 개별 칼럼의 분포도가 좋지 않을지라도 다른 칼럼과 결합하여 자주 사용되고, 결합할 경우에 분포도가 양호하다면 결합 인덱스 생성을 긍정적으로 검토

인덱스 스캔의 원리

• 옵티마이저가 인덱스 사용을 위한 실행계획을 수립함

1. 조건을 만족하는 최초의 인덱스 row를 찾음
2. Access된 인덱스 row의 ROWID를 이용해서 테이블에 있는 row를 찾음(Random Access)
3. 처리 범위가 끝날 때까지 차례대로 다음 인덱스 row를 찾으면서(SCAN) 2.를 반복함

• 인덱스 스캔 시에는 한 번의 I/O가 발생할 때마다 한개의 BLOCK 씩을 처리함.

ROWID의 분해

• Table Access를 할 때 ROWID를 사용하게 된다. 현재 INDEX 데이터 각각이 ROWID를 가지고 있다. 이 ROWID는 데이터의 저장 주소를 가리킨다. 사실 4개의 정보로 나눠져있다.

ROWID In ORACLE(확장형)

• 사실 ROWID는 4개의 정보를 갖는데

1. 데이터를 가진 테이블의 정보
2. 그 테이블을 만들 때 지정했던 테이블 스페이스를 지정했던 파일에 대한 정보
3. 해당 데이터가 들어있는 BLOCK의 정보
4. 그 BLOCK안에서 몇번째 데이터인지 ROW순서

인덱스 사용

고유(Unique) 인덱스의 Equal(=) 검색

• 이제 SQL을 어떻게 작성해야만 인덱스가 제대로 사용될 수 있는지를 보아야한다.
• 보시는 바와 같이 인덱스로 구성된 컬럼에 대해서 연산자를 사용될 때 인덱스가 사용된다라고 볼 수 있다.
• INDEX UNIQUE SCAN

고유(Unique) 인덱스의 범위(Range) 검색

• INDEX RANGE SCAN을 한다.

중복(Non-Unique) 인덱스의 범위(Range) 검색

OR & IN 조건 - 결과의 결합

NOT BETWEEN 검색

• NOT BETWEEN은 OR절로 변형되기에 인덱스 사용이 가능하다.