[RealMySQL 8.0] 8. 인덱스 - 디스크 읽기 방식 / 인덱스란?

8. 인덱스

인덱스는 데이터베이스 쿼리의 성능을 언급하면서 빼놓을 수 없는 부분이다.

각 인덱스의 특성과 차이는 상당히 중요하며, 물리 수준의 모델링을 할 때도 중요한 요소가 될 것이다. MySQL 서버의 옵티마이저가 발전하고 성능이 개선됐다고 해도 여전히 관리자의 역할은 매우 중요하다. 그래서 인덱스에 대한 기본 지식은 지금도 앞으로도 개발자나 관리자에게 매우 중요한 부분이며, 쿼리 튜닝의 기본이 될 것이다.

 

8.1 디스크 읽기 방식

이번 장에서는 "랜덤(Random) I/O", "순차(Sequential) I/O"와 같은 디스크 읽기 방식을 먼저 간단히 알아보고 인덱스를 살펴보자.

 

컴퓨터의 CPU나 메모리처럼 전기적 특성을 띤 장치의 성능은 짧은 시간 동안 매우 빠른 속도로 발전했지만 디스크 같은 기계식 장치의 성능은 상당히 제한적으로 발전했다. 비록 최근에는 자기 디스크 원판에 의존하는 하드 디스크보다 SSD 드라이브가 많이 활용되고 있지만, 여전히 데이터 저장 매체는 컴퓨터에서 가장 느린 부분이라는 사실에는 변함이 없다. 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건일 때가 상당히 많다.

 

 

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

컴퓨터에서 CPU나 메모리 같은 주요 장치는 대부분 전자식 장치지만 하드 디스크 드라이브는 기계식 장치다. 그래서 데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다. 이러한 기계식 하드 디스크 드라이브를 대체하기 위해 전자식 저장 매체인 SSD가 많이 출시되고 있다.

 

SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)를 제거하고 그 대신 플래시 메모리를 장착하고 있다. 디스크 원판을 기계적으로 회전시킬 필요가 없으니 아주 빨리 데이터를 일고 쓸 수 있으며 플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다. 그리고 컴퓨터의 메모리(D-Ram)보다는 느리지만 기계식 하드 디스크 드라이브보다는 훨씬 빠르다.

 

 

 

그림은 컴퓨터의 주요 부품별 처리 속도를 보여준다. Y축은 초당 처리 가능한 연산의 횟수를 의미하므로 값이 클 수록 처리 속도가 빠르다는 것을 의미한다. 메모리와 디스크의 처리 속도는 10만 배 이상의 차이를 보이는데 플래시 메모리를 사용하는 SSD는 1000배 가량의 차이를 보인다. 이러한 점에다가 SSD가 훨씬 대충화된 상태이기에 요즘은 DBMS용으로 사용할 서버에는 대부분 SSD를 채택하고 있다.

 

디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 하드 디스크 드라이브보다 조금 빠르거나 비슷한 성능을 보이기도 한다. 하지만 SSD의 장점은 기존 하드 디스크 드라이브보다 랜덤 I/O가 훨씬 빠르다는 것이다. 데이터베이스 서버에서는 순차 I/O 작업보다 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD가 DBMS용 스토리지에 최적이라고 볼 수 있다.

 

 

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

랜덤 I/O라는 표현은 하드 디스크 드라이브의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미하는데, 사실 순차 I/O 또한 이 작업 과정은 같다. 그러면 랜덤 I/O와 순차 I/O는 어떤 차이가 있을까?

 

 

 

순차 I/O는 3개의 페이지(3X16KB)를 디스크에 기록하기 위해 1번 시스템 콜을 요청했지만, 랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 3번 시스템 콜을 요청했다. 즉, 디스크에 기록해야 할 위치를 찾기 위해 순차 I/O는 디스크의 헤디를 1번 움직였고, 랜덤 I/O는 디스크 헤드를 3번 움직였다.

디스크에 데이터를 쓰고 읽는 데 걸리는 시간은 디스크 헤더를 움직여 읽고 쓸 위치로 옮기는 단계에서 결정된다. 결국 그림의 경우 순차 I/O는 랜덤 I/O보다 거의 3배 정도 빠르다고 볼 수 있다. 즉, 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다고 볼 수 있다. 그래서 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 부하가 훨씬 더 크고 그렇기에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장돼 있다.

 

디스크 원판을 가지지 않는 SSD는 랜덤 I/O와 순차 I/O의 차이가 없을 것으로 예측하지만, 여전히 랜덤 I/O가 전체 Throughput이 떨어진다. 쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 바꾸서 실행할 방법은 그다지 많지 않다. 일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적이고 이를 위해 꼭 필요한 데이터만 읽도록 쿼리를 개선해야 한다.

 

 

 

8.2 인덱스란?

데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸린다. 그래서 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만들어 두고 칼럼의 값을 주어진 순서로 미리 정렬해 보관한다.

 

SortedList는 DBMS의 인덱스와 같은 자료 구조이며, ArrayList는 데이터 파일과 같은 자료 구조를 사용한다. SortedList는 저장되는 값을 항상 정렬된 상태로 유지하며, ArrayList는 값을 저장되는 순서 그대로 유지한다. DBMS의 인덱스도 SortedList처럼 저장되는 칼럼의 값을 이용해 항상 정렬된 상태를 유지하고 데이터 파일은 ArrayList처럼 저장된 순서대로 별도의 정렬 없이 그대로 저장해 둔다.

 

SortedList의 장단점을 통해 인덱스의 장단점을 살펴보자. SortedList 자료구조는 데이터가 저장될 때마다 항상 값을 정렬해야 해서 저장하는 과정이 복잡하고 느리지만, 원하는 값은 빠르게 찾아올 수 있다. DBMS의 인덱스도 INSERT/UPDATE/DELETE 처리가 느리지만 SELECT는 빠르게 처리할 수 있다는 점과 같다.

결론적으로 DBMS에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다.

 

 

인덱스를 역할별로 구분하면 프라이머리 키(Primary key)와 보조 키(Secondary key)로 구분할 수 있다.

프라이머리 키: 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스. 식별자라 부르며 NULL/중복을 허용하지 않음

세컨더리 인덱스: 프라이머리 키를 제외한 나머지 모든 인덱스

 

데이터 저장 방식(알고리즘)별로 구분할 경우 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있다.

데이터의 중복 허용 여부로 분류하면 유니크 인덱스(Unique)와 유니크하지 않은 인덱스(Non-Unique)로 구분할 수 있다. 인덱스가 유니크한지 아닌지는 단순히 같은 값이 1개만 존재하는지 여부를 의미하지만, 실제 DBMS의 쿼리를 실행해야 하는 옵티마이저에게는 상당히 중요한 문제가 된다. 유니크 인덱스에 대해 동등 조건으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다.