[Real MySQL 8.0] 8. 인덱스

인덱스 매우 중요...

 

8.1 디스크 읽기 방식

데이터베이스 성능 튜닝 = 디스크 I/O 줄이기

 

8.1.1 HDD, SSD

SSD에서는 HDD의 원판을 플래시 메모리로 대체 했다. 원판을 돌릴 필요가 없으므로 아주 빨리 데이터를 읽을 수 있다. 플래시 메모리 이므로 전원이 차단되도 데이터가 보존된다.

순차 I/O의 경우 둘의 차이는 거의 없지만, HDD에 비해 SSD의 큰 장점은 랜덤 I/O에 있다.(원판 돌릴 필요 없음)

데이터베이스 서버의 경우 랜덤 I/O가 많아서 SSD가 최적.

 

8.1.2 랜덤 I/O와 순차 I/O

순차 I/O의 경우, 디스크에서 블록 단위로 읽어오면 연속한 주소에 해당하는 데이터는 디스크 I/O 없이 로드할 수 있다. 하지만, 랜덤 I/O는 (거의 모든 경우) I/O가 발생할때 마다 디스크에서 데이터가 존재하는 블록를 읽어와야 한다.

HDD의 경우, 적절한 블록을 탐색할 때 디스크 헤드를 움직여야하는데 이 과정이 오버헤드가 크다.(데이터를 메모리에 로드하는데 드는 거의 모든 시간이 디스크 헤더 움직이는 시간임)

SSD의 경우, 헤드가 존재하지 않으므로 해당 블록을 메모리에 로드하는 오버헤드가 HDD보다 작다. 하지만 여전히 랜덤 I/O의 경우 같은 횟수의 시스템 콜이 발생하는 것은 동일하다.

 

8.2 인덱스란?

DBMS에서는 SortedList를 인덱스의 자료구조로 사용한다.

SortedList: 데이터를 정렬된 상태로 저장하는 자료구조. 이미 정렬되어 있기 때문에 조회는 매우 빠르나, 정렬해서 저장해야하기 때문에 삽입, 갱신이 (ArrayList에 비해) 상대적으로 느리다.

종류

- 프라이머리 키: 유니크, non null. 레코드 식별자.

- 세컨더리 키: 일반적으로 사용하는 인덱스(프라이머리 키를 제외한 인덱스).

데이터 저장방식으로는 B-tree, Hash table, 등이 있다.

 

8.3 B-tree 인덱스

가장 범용적으로 자주 사용되는 인덱스 알고리즘

리프노드에 실제 데이터를 찾기 위한 주소(프라이머리 키)가 저장되어있다(인덱스 키: 주소 와 같이 키: 밸류 쌍으로 저장).

인덱스는 정렬돼있지만 인덱스에 대응하는 프라이머리 키는 그렇지 않다.

8.3.1 ~ 8.3.3

컬럼에 인덱스를 걸면, 테이블 외에 인덱스를 관리하기 위한 공간이 필요하다.

컬럼하나에 인덱스를 추가함으로 인해, INSERT, UPDATE를 위한 디스크 비용은 대략 인덱스 하나당 1.5배가 든다. -> 인덱스를 왕창 건다고 쿼리 성능이 무조건 좋아지지 않는 이유. 

8.3.4 인덱스 키 검색

인덱스를 사용하면 검색은 빨라짐

b-tree구조 상 suffix로 검색하는 것은 인덱스 활용이 불가능하다.

인덱스는 락과도 관련이 깊다.

8.3.3.1 ~ 8.3.3.2

인덱스 키 사이즈가 커질 수록, 인덱스 레코드의 크기는 커지고, 한 페이지에 저장할 수 있는 인덱스의 개수는 줄어든다.

그 결과 같은 b-tree 높이라도 조회할 수 있는 인덱스의 수가 줄어든다.

따라서 인덱스 키 사이즈는 작을수록 좋다.

8.3.3.3 선택도(기수성)

기수성: 인덱스에서 유니크한 키의 개수

중복된 값이 많을 수록 기수성은 작아지고, 선택도 또한 줄어든다. 기수성이 높을수록(선택도가 높을수록) 조회할 수가 줄어들기 때문에 더 빠르게 처리된다.

8.3.3.4 읽어야 하는 레코드의 건수

대략적으로, 인덱스를 통해 레코드를 읽을 때 바로 레코드를 읽는 경우보다 4~5배 비용이 더 든다.

따라서 전체 테이블의 20~25% 이상의 데이터를 읽어야하는 경우, 인덱스를 타는게 더 비효율적일 수 있다.