Real MySQL 인덱스

디스크 읽기 방식

저장소

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

SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플레터(원판)을 제거하고, 그 대신 플래시 메모리를 장착하고 있다. 그래서 디스크 원판을 기계적으로 회전시킬 필요가 없으므로 아주 빨리 데이터를 읽고 쓸 수 있다. 플레시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다. 게다가  데이터 베이스 서버에서 순차 I/O 작업은 비중이 크지 않고, 보통 랜덤 I/O 작업을 통해 작은 데이터를 읽고 쓰는 작업이 대부분이기 때문에 DBMS용으로 SSD를 많이 사용하고 있다.

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

위 그림을 보게 된다면 순차 I/O는 디스크 헤더를 한번만 사용한 반면 랜덤 I/O은 헤더가 3번을 움직였다. 헤더가 움직인 만큼 속도가 느리지므로 순차 I/O는 랜덤 I/O보다 거의 3배정도 빠르다고 볼 수 있다. 즉 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정이 된다고 볼 수 있다. 그래서 데이터베이서의 대부분의 작업은 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnnoDB 로그 버퍼 등의 기능이 내장되어있다.

 

 

인덱스

인덱스란?

DBMS에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. 기능을 높이기 때문에 where 조건절에 사용되는 컬럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히러 역효과를 불러 올 수 있다. 저장 성능이 떨어지는 이유는 DBMS의 인덱스도 SortedList와 마찬가지로 저장되는 컬럼의 값을 이용해 항상 정렬된 상태를 유지하기 때문이다. SortedList 자료구조는 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복합하고 느리지만, 이미 정렬되어 있기 때문에 원하는 데이터를 쉽게 찾을 수 있다. 

 

B-TREE 인덱스

구조 및 특성

B-Tree는 트리 구조의 최상위에 하나의 "루트 노드(Root Node)"가 존재하고 그 하위 자식 노드가 붙어있는 형태이다. 트리 구조의 가장 하위에 있는 노드를 "리프 노드(Leaf node)"라 하고, 트리 구조에서 루트 노드고 아니고, 리프 노드고 아닌 중간 노드를 "브랜치 노드(Branch node)"라고 한다.  

 

인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아 가기 위한 주소값이 저장되어 있다. 아래 그림은 이러한 관계를 보여준다. InnoDB 테이블에서 인덱스를 통해 레코드를 읽을 때는 그림 8.5에서처럼 데이터 파일을 바로 찾아가지 못한다. 그렇기 때문에 그림 8.6에서와 같이 인덱스에 저장돼 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색 한 후, 프라이머리 키 인덱스의 리프 페이지에 저장돼 있는 레코드를 읽는다. 즉 InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한번 검색해야 한다.

 

B-Tree 인덱스 추가 및 삭제

인덱스 키 추가

저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색한다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다. 리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리돼야 하는데, 이는 상위 브랜치 노드까지 처리의 범위가 넓어진다. 결국 B-Tree는 상대적으로 쓰기(새로운 키를 추가하는 작업)에 비용이 많이 든다.

인덱스 키 삭제

해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료된다. 삭제된 공간은 방치되거나 재활용할 수 있다.

인덱스 키 변경

인덱스의 키 값은 그 값에 따라 저장될리프 노드의 위치가 결정되므로 B-Tree의 키 값이 변경되는 경우에는 단순히 인덱스 상의 키 값만 변경하는 것은 불가능하다. B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후 , 다시 새로운 키 값을 추가하는 형태로 처리된다. 결국 인덱스 키 값을 변경하는 작업은 기존 인덱스 키 값을 삭제한 후 새로운 인덱스 키 값을 추가하는 작업으로 처리되고, InnoDB스토리지 엔진을 사용하는 테이블에 대해서는 이작업 모두 체인지 버터를 활용해 지연 처리가 가능하다.

 

B-Tree 인덱스 사용에 영향을 미치는 요소

인덱스 키값의 크기

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다. 또한 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다. 인덱스도 결국 페이지 단위로 관리가 된다.

이진 트리는 각 노드가 자식 노드를 2개만 가지는데, DBMS의 B-Tree는 이진 트리가 아니다. B-Tree는 자식 노드의 개수가 인덱스의 페이지 크기와 키값에 달라지는 가변적인 구조이다. 

 

 

 

 

 

 

 

참고

Real MySQL 8.0.1