Index(인덱스)

목적

RDMBS에서 검색 속도를 높이기 위한 기술

두꺼운 책의 목차와 같다고 보면 된다.

정의

데이터베이스 안의 레코드를 full scan을 때리지 않고, B+ Tree로 구성된 구조에서 Index 파일 검색으로 속도를 향상시키는 기술이다.

파일 구성

테이블 생성 시 다음과 같은 파일들이 생성된다.

• FRM(format file) : 테이블 구조 저장 파일
• MYD(MYSQL Data file) : 실제 데이터 파일
• MYI(MYSQL Index file) : Index 정보 파일(Index 사용 시 생성)

여기서 사용자가 쿼리를 통해 Index를 사용하는 칼럼을 검색하게 되면, 이때 MYI 파일의 내용을 사용한다.

단점

• Index 생성 시, .mdb 파일 크기가 증가한다.
• 여러 사용자가 응용 프로그램에서 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
• 인덱스 된 Field에서 Data를 업데이트하거나, Record를 추가 또는 삭제시 성능이 떨어진다.
• 데이터 변경이 자주 일어나는 경우, Index를 재작성해야 하므로 성능에 영향을 미친다.

장점

• 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킨다.
• 질의에서 그룹화 작업의 속도를 향상시킨다.
• 인덱스를 사용하면 raw의 고유성을 강화시킬 수 있다.
• 테이블의 기본 키는 자동 인덱스 된다.

인덱스를 사용하기 적합한 경우

• WHERE절에서 자주 사용되는 컬럼
• 데이터의 중복도(cardinality)가 낮은 컬럼
  • cardinality가 높은 경우는 적합하지 않다(ex : 성별과 같이 타입이 적은 경우)
• 외래키가 사용되는 컬럼
• Join에 자주 사용되는 컬럼

주의사항

1. DML(데이터 조작어 : Data Manipulation Language)에 취약함

INSERT

index split 현상 발생 가능(인덱스의 block들이 하나에서 두 개로 나누어지는 현상)

• 인덱스는 데이터가 순서대로 정렬되어야 하는데, 기존 블록에 여유 공간이 없는 상황에서 그 블록에 새로운 데이터가 입력되면 기존 블록의 내용 중 일부를 새 블록에다가 기록한 후 기존 블록에 빈 공간을 만들어서 새로운 데이터를 추가하게 된다(성능면에서 매우 불리)

• index split은 새로운 블록을 할당 받고 key를 옮기는 작업을 수행한다. 그 수행 과적이 redo에 기록되고 많은 양의 redo를 유발한다.

• index split이 이루어지는 동안 해당 블록에 대해 키 값이 변경되면 안되므로 DML이 블로킹된다.

DELETE

• 테이블에서 데이터가 DELETE될 경우 : 지워지고 다른 데이터가 그 공간을 사용 가능
• index에서 데이터가 DELETE될 경우 : 데이터가 지워지지 않고 사용은 되지 않는다.
  • 테이블에 데이터가 1만건 있는 경우, index에는 2만건이 있을 수 있다.

UPDATE

• index에는 UPDATE 개념이 없다.
• 테이블에 UPDATE가 발생할 경우 index에서는 DELETE가 먼저 발생한 후 새로운 작업의 INSERT 작업이 발생한다. DELETE와 INSERT 두개의 작업이 인덱스에 동시에 일어나게 되어 오히려 더 큰 부하를 일으키게 된다.

2. 타 SQL 실행에 악영향을 줄 수 있다.

갑자기 인덱스를 추가하게 되면 잘 작동하던 쿼리에 옵티마이저(SQL을 어떤 식으로 실행할지 계획해 주는 DBMS 소프트웨어)가 실행 계획을 바꾸는 경우가 생겨 갑자기 아주 느려질 때가 있다.

Index 종류

1. B-Tree Index

   • 기본(default) Index
   • cardinality가 높은 컬럼에 적합

2. Full-Text Index

   • 다수의 단어 등을 포함하는 컬럼에서 단어 및 구문을 검색하기 위함
   • 데이터를 인정 단위로 분할하여 인덱싱
   • CHAR, VARCHAR, TEXT 컬럼에만 적용 가능

ALTER TABLE ratings
ADD FULLTEXT INDEX index_full_text (comment);

SELECT * FROM ratings
WHERE MATCH(comment) AGAINST('라떼는 말이야');

3. Hash Index

   • 일치 여부만 확인
   • 인덱스 크기가 작고 검색이 매우 빠름
   • InnoDB 엔진에서는 내부적으로 Adoptive Hash Index가 구현되어 있다.

4. Bitmap Index

   • cardinality가 낮은 컬럼에 적합
   • MySQL에는 없음 ㅠㅠ

출처 | https://lalwr.blogspot.com/2016/02/db-index.html