[데이터베이스] Index (인덱스)

디스크 읽기 방식

하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SSD)

컴퓨터 대부분의 주요 장치는 대부분 디지털 방식이지만 HDD는 아날로그 방식의 장치이다. 

디지털 방식으로 동작하는 주요 장치 간의 데이터 소통은 굉장히 빠르지만 HDD만 그 페이스에 맞추지 못하는 상황이 발생한다.

그래서 DB에서 디스크 장치는 항상 병목이 되어 느려지는 현상이 빈번한데 이러한 기계식 디스크 드라이브를 대체하기 위해 나온 것이 SSD(Solid State Drive)이다. 

데이터베이스 서버에서 순차 I/O 작업의 비중이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 많아 DBMS에는 SSD가 더 적합하다. 

 

 

랜덤 I/O와 순차 I/O 

디스크 드라이브는 데이터를 읽을 때 원판 플레터를 회전하며 데이터를 찾는데 순차 I/O는 논리/물리적 순서를 따라 차례대로 데이터를 읽어나가는 방식이고 랜덤 I/O는 논리/물리적 순서를 따르지 않고 한건의 데이터를 읽기위해 한 블록씩 접근하는 방식이다.

5번을 제외한 모든 작업이 랜덤 I/O에 해당한다.

5번은 논리적으로 연속하게 데이터를 읽어 들이지만 나머지는 연속하지 않은 방향으로 한 블록씩 데이터를 읽어 들인다.

 

순차 I/O는 3개의 페이지를 디스크에 기록하기 위해 한번의 시스템 콜을 요청했지만 랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 세번의 시스템 콜을 요청했다.

즉, 순차 I/O는 디스크의 헤드를 1번 움직였지만 랜덤 I/O는 디스크의 헤드를 3번 움직였다.

디스크에서 데이터를 읽고 쓰는데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 시간이 대부분을 차지한다.

→ 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한번에 기록하느냐에 의해 결정된다. 

 

 

Index (인덱스)란 ? 

인덱스는 DBMS의 저장 성능을 희생하고 검색 성능을 높이기 위해 만들어진 자료구조이다.

사람의 이름과 그 사람이 빌린 책 번호가 저장되어 있는 데이터베이스에서 특정 사람이 빌린 모든 책을 찾고 싶다고 하자

인덱스가 없다면 테이블의 Location 1 ~ 100,000까지 full scan을 수행하면서 이름을 찾아야 한다. 이는 매우 비효율적이다. 

그래서 다음과 같이 row에서 이름과 row_id를 빼서 인덱스를 만들고 이름을 기준으로 정렬한다. 

 

이렇게하면 Index에서 특정 이름이 처음 등장하는 시점부터 더 이상 나오지 않는 시점까지만 탐색을 수행하면 되고 빌린 책을 알고 싶다면 여기서 탐색한 row_id를 가지고 실제 쿼리를 날리면 된다.

심지어 탐색 과정에서 B-Tree 자료 구조의 이점때문에 탐색 속도도 빠르다.

 

 

인덱스 생성 과정

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

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

인덱스를 사용하지 않는 경우 MYI 파일은 비어있다가 인덱싱하는 경우 MYI 파일이 생성된다.

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

 

 

DML이 일어났을 때의 상황

INSERT

기존 Block에 여유가 있을 때 새로운 Data가 발생한다.

새로운 Block을 할당받은 후 Key를 옮기는 작업을 수행 → 많은 양의 REDO가 유발되고 기록된다.

 

DELETE

Table과 Index 상황 비교

Table에서 data가 delete 되는 경우 : Data가 지워지고, 다른 Data가 그 공간을 사용 가능

Index에서 Data가 delete 되는 경우 : Data가 지워지지 않고, 사용 안 됨 표시만 해둔다.

→ Table의 Data 수와 Index의 Data 수가 다를 수 있음

 

UPDATE

Table에서 UPDATE가 발생하면 Index는 UPDATE 할 수 없다.

Index에서는 DELETE가 발생한 후, 새로운 작업의 INSERT 작업 / 2배의 작업이 소요

 

 

인덱스 관리

DBMS는 인덱스를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다.

따라서 인덱스가 적용된 컬럼에 삽입, 갱신, 삭제가 수행된다면 각각 다음과 같은 연산을 추가로 해줘야 하며 그에 따른 오버헤드가 발생한다.

• INSERT : 새로운 데이터에 대한 인덱스를 추가한다.
• DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행한다.
• UPDATE : 기존의 인덱스를 사용하지 않음 처리하고 갱신된 데이터에 대해 인덱스를 추가한다.

 

 

인덱스의 장/단점

장점

• 테이블 조회 속도 상승 및 그에 따른 성능 향상
• 전반적인 시스템 부하 감소 

 

단점

• 인덱스 관리를 위해 DB의 약 10%에 해당하는 저장 공간 필요 
  • .mdb 파일의 크기가 증가한다.
• 인덱스 관리를 위해 추가 작업 필요
• 한 페이지를 동시에 수정할 수 있는 병렬성이 줄어든다.
• 인덱스 잘못 사용시 오히려 성능 저하하는 역효과 발생
  • INSERT, UPDATE, DELETE가 빈번한 속성에 인덱스를 걸면 인덱스의 크기가 비대해져 성능 하락
  • UPDATE, DELETE는 기존 인덱스를 삭제하지 않고 '사용하지 않음' 처리한다.
  • 만약 어떤 테이블에 UPDATE, DELETE가 빈번하게 발생하면 실제 데이터는 10만건이지만 인덱스는 100만건이 넘어가게 되어  SQL 처리시 비대해진 인덱스에 의해 오히려 성능이 하락한다.

 

 

그렇다면 언제 인덱스를 사용해야 할까 ?

• 규모가 작지 않은 테이블 
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼 
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼

조건 검색 Where 절의 효율성
인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건 (Where)에 맞는 데이터들을 빠르게 찾아낼 수 있다.
이것이 인덱스(Index)를 사용하는 가장 큰 이유이다.
 
정렬 Order by 절의 효율성
인덱스(Index)를 사용하면 Order by에 의한 Sort 과정을 피할수 있다.
Order by는 굉장히 부하가 많이 걸리는 작업이다. 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생된다. 하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 된다.
이미 정렬이 되어 있기 때문에 가져오기만 하면 된다.

• 데이터의 중복도가 낮은 컬럼(카디널리티가 높은 컬럼)

예를 들어 성별 컬럼이 있다고 하자
이때 성별에 인덱스를 걸어봤자 탐색할 수 있는 값이 2개밖에 없으므로 하나의 성별이 붙은 데이터를 검색하는데 가장 최악의 상황에는 full scan을 할 수 있다.
또한 인덱스는 내부적으로 Key, Value의 트리 형태로 데이터를  저장하는데 Key가 중복되어 여러개 존재하면 검색할 대상이 증가한다. 
이러한 이유로 데이터의 중복도가 낮아서 분포도가 높은 컬럼에 대해 인덱스를 사용해야 한다. 

 

 

 

참고 

https://steady-coding.tistory.com/536

https://gyoogle.dev/blog/computer-science/data-base/Index-.html

https://coding-factory.tistory.com/746