Index 인덱스

INDEX의 의미

• RDBMS에서 검색 속도를 높이기 위해 사용하는 하나의 기술이다.
• INDEX는 색인이다.
• 해당 Table의 컬럼을 색인화(따로 파일로 저장)하여 검색시 해당 Table의 레코드를 full scan하는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색 속도를 빠르게 한다.
• INDEX 구조는 Tree 구조로 색인화한다.
• RDBMS에서 사용하는 INDEX는 Balance Search Tree를 사용한다.

INDEX의 원리

INDEX를 해당 컬럼에 주게 되면 초기 Table 생성시, FRM, MYD, MYI 3개의 파일이 만들어진다.

• FRM : 테이블 구조가 저장되어 있는 파일
• MYD : 실제 데이터가 있는 파일
• MYI : INDEX 정보가 들어있는 파일

INDEX를 사용하지 않는 경우, MYI 파일은 비어져 있다.

그러나 INDEX를 해당 컬럼에 만들게 되면 해당 컬럼을 따로 인덱싱하여 MYI 파일에 입력한다.

이후에 사용자가 SELECT 쿼리로 INDEX를 사용하는 쿼리를 사용시 해당 Table을 검색하는 것이 아니라 MYI 파일의 내용을 검색한다.

만약, INDEX를 사용하지 않은 SELECT 쿼리라면 해당 Table Full scan하여 모두 검색한다.

이는 책의 뒷부분에 <찾아보기>와 같은 의미로 정리해둔 단어 중에서 원하는 단어를 찾아서 페이지수를 보고 쉽게 찾을 수 있는 개념과 같다. 만약, 이 <찾아보기>가 없다면 처음부터 끝까지 모든 페이지를 보고 찾아와야할 것이다.

INDEX 장점

• 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킨다.
• 인덱스를 사용하면 테이블 행의 고유성을 강화시킬 수 있다.
• 테이블의 기본키는 자동으로 인덱스된다.
• 필드 중에는 데이터 형식 때문에 인덱스 될 수 없는 필드도 있다.
• 여러 필드로 이루어진(다중 필드)인덱스를 사용하면 첫 필드 값이 같은 레코드도 구분할 수 있다.

참고로 액세스에서 다중 필드 인덱스는 최대 10개의 필드를 포함할 수 있다.

INDEX 단점

• 인덱스를 만들면 .mdb 파일 크기가 늘어난다.
• 사용자가 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
• 인덱스된 필드에서 데이터를 업데이트하거나 레코드를 추가 또는 삭제할 때 성능이 떨어진다.
• 인덱스가 데이터베이스 공간을 차지재 추가적인 공간이 필요해진다.(DB의 10% 내외의 공간이 추가로 필요하다.)
• 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
• 데이터 변경 작업이 자주 일어날 경우에 인덱스를 재작성해야 할 필요가 있기에 성능에 영향을 끼칠 수 있다.

따라서 어느 필드를 인덱스해야 하는지 미리 시험해보고 결정하는 것이 좋다. 인덱스를 추가하면 쿼리 속도가 1초 정도 빨라지지만, 데이터 행을 추가하는 속도는 2초 정도 느려지게 되어 여러 사용자가 사용하는 경우, 레코드 잠금 문제가 발생할 수 있다.

또, 다른 필드에 대한 인덱스를 만들게 되면 성능이 별로 향상되지 않을 수도 있다. 예를 들어, 테이블에 회사 이름 필드와 성 필드가 이미 인덱스된 경우에 우편 번호를 필드로 추가해 인덱스에 포함해도 성능이 거의 향상되지 않는다. 만드는 쿼리의 종류와 관계 없이 가장 고유한 값을 갖는 필드만 인덱스해야 한다.

INDEX의 목적

RDBMS에는 INDEX가 있다. 인덱스의 목적은 RDBMS의 검색 속도를 높이는데 있다.

SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을 때만 인덱스를 사용하며, SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있다.

• DELETE, INSERT, UPDATE 쿼리에는 해당 사항이 없으며 INDEX 사용시 오히려 좀 느려진다.

상황 분석

[사용하면 좋은 경우]

1. Where절에서 자주 사용되는 Column
2. 외래키가 사용되는 Column
3. Join에 자주 사용되는 Column

[Index 사용을 피해야 하는 경우]

• Data 중복도가 높은 Column
• DML이 자주 일어나는 Column

DML에 취약

1. INSERT
   • indext split : 인덱스의 Block들이 하나에서 두개로 나누어지는 현상.
   • 인덱스는 데이터가 순서대로 정렬되어야 한다. 기존 블록에 여유 공간이 없는 상황에서 그 블록에 새로운 데이터가 입력되어야 할 경우, 오라클이 기존 블록의 내용 중 일부를 새 블록에다가 기록한 후, 기존 블록에 빈 공간을 만들어서 새로운 데이터를 추가하게 된다.
   • 성능면에서 매우 불리하다.
     • Index split은 새로운 블록을 할당받고 Key를 옮기는 복잡한 작업을 수행한다. 모든 수행 과정이 Redo에 기록되고 많은 양의 Redo를 유발한다.
     • Index split이 이루어지는 동안 해당 블록에 대해 키 값이 변경되면 안되므로 DML이 블로킹된다.
2. DELETE
   • 테이블에서 데이터가 Delete될 경우, 지워지고 다른 데이터가 그 공간을 사용할 수 있다.
   • index에서 데이터가 delete될 경우, 데이터가 지워지지 않고 사용 안됨 표시만 해둔다.
   • 즉, 테이블에 데이터가 1만건 있는 경우, 인덱스에는 2만건이 있을 수 있다는 뜻이다.
   • 인덱스를 사용해도 수행 속도를 기대하기 힘들다.
3. UPDATE
   • 인덱스에는 Update 개념이 없다.
   • 테이블에 update가 발생할 경우, 인덱스에서는 delete가 먼저 발생한 후 새로운 작업의 insert 작업이 발생한다.
   • delete와 insert 두 개의 작업이 인덱스에서 동시에 일어나 다른 DML보다 더 큰 부하를 주게 된다.