8. 인덱스 (1)

인덱스는 데이터베이스 쿼리의 성능을 언급하면서 뻬놓을 수 없는 부분이다. 각 인덱스의 특성과 차이는 상당히 중요하며, 물리 수준의 모델링을 할 때도 중요한 요소가 된다.

MySQL 8.0 버전까지 업그레이드되어 오면서 다른 상용 RDBMS에서 제공하는 많은 기능을 지원하게 됐으며, 기존의 MyISAM 스토리지 엔진에서만 제공하던 전문 검색이나 위치 기반 검색 기능도 모두 InnoDB 스토리지 엔진에서 사용할 수 있게 개선되었다.

하지만 아무리 MySQL 서버의 옵티마이저가 발전하고 성능이 개선됐다고 해도 여전히 관리자의 역할은 매우 중요하며, 인덱스에 대한 기본 지식은 지금도 앞으로도 개발자나 관리자에게 매우 중요하고 쿼리 튜닝의 기본이 될 것이다.

디스크 읽기 방식

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

데이터베이스나 쿼리 튜닝에 어느정도 지식을 사용자가 절감하고 있듯이 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관권일 때가 많다.

HDD와 SSD

데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다. SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 원판을 제거하는 대신 플래시 메모리를 장착하고 있다. 따라서 원판을 물리적으로 회전시킬 필요가 없으므로 빠르게 데이터를 읽고 쓸 수 있다.

플래시 메모리를 사용하는 SSD는 하드 디스크 드라이브보다 용량은 적지만 1000배 가량 빠른 성능을 보여주기 때문에 요즘은 DBMS용 서버는 SSD를 채택하고 있다.

디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O 에서는 SSD가 압도적으로 빠르진 않지만 데이터베이스 서버에서는 대부분 랜덤 I/O가 발생하여 대부분의 상황에서 SSD 성능이 우수하다.

랜덤 I/O와 순차 I/O

하드디스크 기준으로 랜덤 I/O는 그만큼 디스크 헤드를 자주 움직여야 한다는 뜻이며, SSD도 랜덤 I/O가 순차 I/O에 비해 스루풋이 떨어진다. 데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 도는 InnoDB 로그 버퍼 등의 기능으로 대응하고 있다.

쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 바꿔 실행할 방법은 그다지 많지 않고, 일반적으로 쿼리 튜닝은 랜덤 I/O 자체를 줄여주는 것이 목적이다.

랜덤 I/O를 줄인다는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O 사용하고 풀 테이블 스캔은 순차 I/O 사용한다. 따라서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다.
이런 형태는 온라인 서비스에서는 거의 활용되지 않으며 주로 데이터웨어하우스나 통계 작업에서 많이 활용된다.

인덱스란?

책의 맨 끝에 있는 색인으로 설명된다. 색인으로 찾는 페이지 번호는 데이터 파일에 저장된 레코드의 주소에 비유될 수 있다.

DBMS도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸리기 때문에, 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만들고, 빠른 검색을 위해 칼럼의 값을 기준으로 정렬해놓는다.

자료구조로 비유하면, SortedList, ArrayList를 예로 들 수 있다.

SortedList: DBMS의 인덱스와 같은 자료 구조. 저장된 값을 항상 정렬된 상태로 유지한다.
- 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리다.
- 이미 정렬돼 있어 원하는 값을 아주 빨리 찾아올 수 있다.
- INSERT, UPDATE는 느려지지만 SELECT는 매우 빠르다.
ArrayList: 데이터 파일과 같은 자료구조. 저장된 순서대로 별도 정렬 없이 그대로 저장한다.

인덱스 추가시 주의사항

결론적으로 DBMS에서 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. 따라서 다음을 고려하여 인덱스 추가를 결정한다.

데이터의 저장 속도를 어느정도까지 희생할 수 있는지
읽기 속도를 얼마나 더 빠르게 만들어야 하는지

SELECT쿼리 문장의 WHERE조건절에 사용되는 컬럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다.

인덱스 분류

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

역할 별 분류

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

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

데이터 저장 방식(알고리즘) 분류

대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있다. 최근에는 Fractal-Tree 인덱스나 로그 기반의 Merge-Tree 인덱스와 같은 알고리즘을 사용하는 DBMS도 개발되고 있다.

B-Tree 알고리즘
- 가장 일반적으로 사용되는 인덱스 알고리즘으로, 상당히 오래전에 도입된 만큼 성숙해진 상태이다.
- 컬럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다.
Hash 인덱스 알고리즘
- 컬럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다.
- 값을 변형해서 인덱싱하므로 전방(Prefix)일치와 같이 값의 일부만 검색하거나 범위 검색할 때에는 해시 인덱스를 사용할 수 없다.
- 메모리 기반의 데이터베이스에서 많이 사용된다.

데이터 중복 허용 여부로 분류

데이터의 중복 허용 여부로 분류하면 유니크 인덱스와 유니크하지 않은 인덱스로 구분할 수 있다.

인덱스가 유니크한지 아닌지는 단순히 같은 값이 1개만 존재하는지 1개 이상 존재할 수 있는지를 의미하지만, 실제 DBMS의 쿼리를 실행할 때 유니크 인덱스에 대해 동등 조건으로 검색한다는 것은 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 내기 때문에 옵티마이저에게는 상당히 중요하다.