MySQL 인덱스 기초

2023.10.10 복습 리팩토링 시작

디스크 읽기

이번에 인덱스에서 자주 나올 랜덤 I/O와 순차 I/O에 대한 개념을 정리하고 넘어가겠습니다.

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

• 컴퓨터에서 CPU나 메모리 같은 주요 장치는 대부분 전자식 장치지만 하드 디스크 드라이브는 기계식 장치다.
• 그래서 데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다.
• 이러한 HDD를 대체하기 위해 전자식 저장 매체인 SSD가 많이 출시되고 있다.
• SSD도 기존 하드디스크 드라이브에서 데이터 저장용 플래터(원판)을 제거하고 그 대신 플래시 메모리를 장착하고 있다.
• 그래서 원판을 기계적으로 회전시킬 필요가 없으므로 아주 빨리 데이터를 읽고 쓸 수 있다.
• 플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다.
• 컴퓨터의 메모리보다는 느리지만 HDD보다는 훨씬 빠르다.
• 메모리와 디스크의 처리 속도는 10만 배 이상의 차이를 보인다.
• 그에 비해 플래시 메모리를 사용하는 SSD는 1000배 가량의 차이를 보인다.
• 시중에 판매되는 SSD는 기존 하드 디스크 드라이브보다는 용량이 적으며 가격도 비싼 편이지만 예전보다는 SSD가 훨씬 대중화된 상태이며 요즘은 DBMS용으로 사용할 서버에는 대부분 SSD를 채택하고 있다.

디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 순차 I/O에서는 SSD가 하드 디스크 드라이브보다 조금 빠르거나 거의 비슷한 성능을 보이기도 한다.

하지만 SSD의 장점은 기존 하드 디스크 드라이브보다 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이라고 볼 수 있다.

 

책의 예시에서는 SSD는 초당 436개의 트랜잭션을 처리했지만 하드 디스크 드라이브는 초당 60개의 트랜잭션밖에 처리하지 못했다.

일반적인 웹 서비스 환경의 데이터베이스에는 SSD가 하드 디스크 드라이브보다는 훨씬 빠르다.

랜덤 I/O와 순차 I/O

• 랜덤 I/O라는 표현은 하드 디스크 드라이브의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미하는데, 사실 순차 I/O 또한 이 작업 과정은 같다.
• 책의 예시를 살펴보면 순차 I/O는 3개의 페이지를 디스크에 기록하기 위해 1번 시스템 콜을 요청했지만, 랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 3번 시스템 콜을 요청했다.
• 즉, 디스크에 기록해야 할 위치를 찾기 위해 순차 I/O는 디스크 헤드를 1번 움직였고, 랜덤 I/O는 디스크 헤드를 3번 움직였다.

디스크에 데이터를 쓰고 읽는 데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다.

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

• 그래서 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 크다.
• 데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장돼 있다.
• 디스크 원판을 가지지 않는 SSD는 랜덤 I/O와 순차 I/O의 차이가 없을 것으로 예측하지만, 실제로는 그렇지 않다.
• SSD 드라이브에서도 랜덤 I/O는 여전히 순차 I/O보다 전체 스루풋이 떨어진다.
• 쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 바꿔서 실행할 방법은 거의 없다.
• 일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적이라고 할 수 있다.
• 여기서 랜덤 I/O를 줄인다는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.

인덱스란

인덱스는 늘 책의 맨 끝에 있는 색인으로 설명된다.

책의 색인이 인덱스라면 책의 내용은 데이터 파일에 해당한다.

책의 찾아보기, 색인을 통해 알아낼 수 있는 페이지 번호는 데이터 파일에 저장된 레코드의 주소에 비유된다.

 

DBMS도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸린다.

그래서 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만들어 두는 것이다.

DBMS의 인덱스도 책의 색인(찾아보기)의 동일한 점은 정렬이다.

책의 찾아보기도 ㄱ,ㄴ,ㄷ ... 과 같은 순서로 정렬해 있다.

DBMS 인덱스도 마찬가지로 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다.

 

프로그래밍 언어에서 자주 등장하는 SortedList는 DBMSDML 인덱스와 같은 자료 구조다.

ArrayList는 데이터 파일과 같은 자료 구조를 사용한다.

SortedList는 저장되는 값을 항상 정렬된 상태로 유지하는 자료 구조이며,

ArrayList는 저장되는 순서 그대로 유지하는 자료 구조다.

DBMS의 인덱스도 SortedList와 마찬가지로 저장되는 칼럼의 값을 이용해 항상 정렬된 상태를 유지한다.

데이터 파일은 ArrayList와 동일하게 저장된 순서대로 별도의 정렬 없이 그대로 저장한다.

• SortedList의 장점 : 이미 정렬 돼 있어서 원하는 값을 아주 빨리 가져올 수 있다.
• SortedList의 단점 : 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리다.

DBMS의 인덱스도 인덱스가 많은 테이블은 당연히 INSERT, UPDATE, DELETE 문장의 처리가 느려진다.

하지만 이지 정렬된 찾아보기용 표, 즉 인덱스를 사용하므로 SELECT 문장은 매우 빠르게 처리할 수 있다.

 

결론은 데이터의 저장 성능을 희생하고 데이터의 읽기 속도를 높이는 기능이 바로 DBMS의 인덱스다.

인덱스를 하나 더 추가할지 말지는 데이터의 저장 속도를 어디까지 희생할 수 있는지,

읽기 속도를 얼마나 더 빠르게 만들어야 하느냐에 따라 결정해야 한다.

 

인덱스는 데이터를 관리하는 방식(알고리즘)과 중복 값의 허용 여부 등에 따라 여러 가지로 나눠볼 수 있다.

앞으로 키라는 말과 인덱스를 같은 의미라고 생각하자. 

인덱스를 역할별로 구분해 본다면 프라이머리 키와 보조 키로 구분할 수 있다.

• 프라이머리 키는 이미 잘 아는 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스다. 이 칼럼은 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에 우리는 이를 식별자라고도 부른다. 프라이머리 키는 NULL 값을 허용하지 않으며 중복을 허용하지 않는다.
• 프라이머리 키를 제외한 나머지 모든 인덱스는 세컨더리 인덱스로 분류한다. 유니크 인덱스는 프라이머리 키와 성격이 비슷하고 프라이머리 키를 대체해서 사용할 수도 있다고 해서 대체 키라고도 하는데, 별도로 분류하기도 하고 그냥 세컨더리 인덱스로 분류하기도 한다.

데이터 저장 방식(알고리즘)으로 많은 분류가 가능하지만 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있다.

우리는 곧 B-Tree 인덱스에 대해 더 살펴볼 것이고 Hash 인덱스는 나중에 다뤄보겠다. 

Hash 인덱스는 메모리 기반의 데이터베이스에서 많이 사용한다고 한다.

MySQL 인덱스의 대표적인 기능으로는 전문 검색용 인덱스나 공간 검색용 인덱스 등을 예로 들 수 있다.

 

다음 시간에는 B-Tree 인덱스에 대해 공부해보겠습니다.

이상으로 포스팅을 마칩니다. 감사합니다.

참고

REAL MySQL 8.0 1권

 

2023.10.11 복습 리팩토링 마무리