DB - 인덱스(Index)

 

DB Index

DB 인덱스는 RDBMS 에서 검색 속도를 높이기 위한 기술이다.

 

테이블의 Column 을 색인화 한다.(따로 파일로 저장함)

-> 해당 테이블의 레코드를 Full scan(전체 스캔) 하지 않는다.

-> 색인화 된 (BTree : 이진 트리) 구조로 만들어지므로, Index 파일 검색으로 인해 검색 속도가 향상된다.

 

DB Index 파일 구조

테이블을 생성하면 MYD, MYI, FRM 등 3개의 파일이 생성된다.

- FRM : 테이블 구조가 저장되어 있는 파일

- MYD : 실제 데이터가 있는 파일

- MYI : 인덱스 정보가 들어가 있는 파일

 

Index 를 사용하지 않는 경우 MYI 파일은 비어져 있다. 그러나 인덱스를 사용하는 경우 MYI 파일이 생성된다.

이후에 사용자가 Select 쿼리로 인덱스를 사용하는 Column 을 탐색할 시 MYI 파일의 내용을 검색하게 된다.

 

인덱스 사용시 단점

- 인덱스 생성 시, .mdb 파일 크기가 증가한다.

- 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.

- 인덱스 된 Field 에서 Data 를 업데이트 하거나, Record 를 추가 또는 삭제 시 성능이 떨어지게 된다.

- 데이터 변경 작업이 자주 일어나는 경우, 인덱스를 재작성 해야 하므로 성능에 영향을 미치게 된다.

 

- 인덱스 생성

/*
CREATE INDEX 인덱스이름
ON 테이블이름 (필드이름1, 필드이름2, ...);
*/

CREATE INDEX item_index
ON item(id);

- 테이블에서 생성된 인덱스 내용 확인

-- SHOW INDEX FROM 테이블이름;

SHOW INDEX FROM item;

 

 

Index 를 사용하면 좋은 경우

1. Where 절에서 자주 사용되는 Column

2. 외래키가 자주 사용되는 Column

3. Join 에 자주 사용되는 Column

 

Index 사용을 피해야 하는 경우

1. Data 중복도가 높은 Column

2. DML 이 자주 일어나는 Column

 

* DML 이 일어났을 때의 상황

* Insert

기존 block 에 여유가 없을 때 새로운 Data 가 입력됨

-> 새로운 block 을 할당 받은 후, key 를 옮기는 작업을 수행(많은 양의 Redo 가 기록되고 유발된다.)

-> Index split 작업 동안, 해당 block 의 key 값에 대해서 DML 이 블로킹된다.(대기 이벤트 발생)

 

** Redo, Undo

- Redo 는 "다시 하다" 라는 뜻을 가지고 Undo 는 "원 상태로 돌리다" 라는 뜻을 가지고 있다. 즉, Redo 는 무언가를 다시 하는 것이고 Undo 는 무언가를 되돌리는 뜻이다.

- Redo 는 기본적으로 복구의 역할을 한다. 오라클 서버에 무슨 작업을 하든지 모두 Redo 에 기록된다.(Undo 포함)

- Undo 는 작업 롤백, 읽기 일관성, 복구를 한다.

- Redo 와 Undo 의 공통점은 복구를 한다는 것이다.

 

* Delete

테이블과 인덱스 상황에서의 비교

- 테이블에서 데이터가 delete 되는 경우 : 데이터가 지워지고, 다른 데이터가 그 공간을 사용할 수 있게 된다.

- 인덱스에서 데이터가 delete 되는 경우 : 데이터가 지워지지 않고, 사용 안됨 표시만 되어진다.

-> 테이블의 데이터 수와 인덱스의 데이터 수가 달라질 수 있다.

 

* Update

테이블에서 update 가 발생했을 때, 인덱스는 update 를 할 수 없다.

인덱스 에서는 update 시엔 delete 가 발생한 후, 새로운 인덱스에서의 insert 작업을 수행하게 되어

테이블에서 update 를 할 때보다 2배 가량의 작업 시간이 소요된다. 

 

 

 

참고 : https://gyoogle.dev/blog/

👨🏻‍💻 Tech Interview