Real My SQL - 5장 인덱스

인덱스

인덱스는 데이터베이스에 저장해놓은 데이터를 빠르게 탐색하기 위하여 원본 데이터를 저장할 때 추가적으로 같이 저장하는 정렬된 데이터 집합이다.

흔히들 알고 있는 이진검색 방식과 같이 미리 데이터를 정렬해서 탐색범위를 줄이는 전략을 이용하는 것이 인덱스이다.

(이진검색이란 정렬된 데이터들의 중간값을 확인해 크고 작음을 따져서 전체 탐색범위를 절반씩 계속 줄여나가는 탐색 알고리즘이다)

하지만 MySQL의 인덱스는 이진트리검색 방식을 이용하고 있진 않고, 대신 B+Tree라는 자료구조를 이용하고 있다.

(하나의 노드가 하나의 키가 되는 이진트리와는 달리 B-Tree에서는 하나의 노드 안에 여러 개의 키가 저장되어 있는 형태이다)

인덱스를 사용한다는 것은 데이터를 저장할 때 원본 자료 이외의 자료를 추가적으로 작성한다는 것으로 결국 데이터를 추가하고 변경하는 작업(INSERT, UPDATE, DELETE)에서는 성능을 희생하게 되지만, 저장된 데이터를 탐색하고자 할 때는 굉장히 빠르게 작업을 처리할 수 있는 장점을 가진다.

MySQL에서 데이터를 스캔(탐색)하는 방법에는 총 2가지의 방법이 있다.

바로 위에서 언급한 인덱스를 이용한 탐색 방법과 인덱스를 이용하지 않는 방법이다.

인덱스를 이용하지 않고 데이터를 탐색한다는 것은 어떠한 정보도 없이 맨땅에서부터 원하는 데이터를 찾아내겠다는 것으로, 결국 테이블에 존재하는 모든 데이터를 읽어가며 탐색(풀테이블 스캔) 하겠다는 뜻과 같다.

인덱스를 이용한 데이터 읽기 방식

인덱스를 이용한 데이터 스캔 방식에는 크게 3가지 방식이 존재한다.

인덱스 레인지스캔, 인덱스 풀스캔, 루스 인덱스 스캔이다.

인덱스 레인지스캔은 검색조건에 해당하는 인덱스들 중 가장 첫 번째 순번에 해당하는 시작 인덱스 위치를 찾아낸 다음, 이후 검색 조건과 맞아떨어지지 않는 인덱스레코드가 나올 때까지 계속하여 다음 순번의 인덱스들을 순차적으로 읽어가는 방식의 탐색 방법이다.

어차피 인덱스의 데이터들은 이미 알맞게 정렬되어 있는 상태이기 때문에, 만약 시작점 인덱스를 찾았다면 그 이후는 검색 조건에 맞지 않는 인덱스가 나올 때까지 그저 인접한 인덱스들을 순서대로 하나씩 따라가면 되는 것이다.

그리고 MySQL 인덱스 구현에 사용되는 B+Tree 자료구조는 리프노드들이 서로 링크드하게 연결되어 있는 인접리스트 구성으로 되어있기 때문에, 한 리프노드에서 데이터의 끝에 다다랐다면 문제없이 다음 리프 노드(페이지)로 순탄하게 넘어갈 수 있게 되어있다.

인덱스 풀스캔은 인덱스가 다중 컬럼 구성으로 이루어져 있는 테이블을 탐색할 때, 첫 번째 순번의 칼럼을 탐색 조건으로 이용하지 않고 이 후 순번의 칼럼들을 조건으로 하여 검색하는 경우에 대개 일어난다.

해당 방식의 스캔은 인덱스 트리의 리프노드에 존재하는 모든 데이터를 처음부터 끝까지 전부 읽으면서 처리하는데 사실 정상적으로 인덱스를 타는 느낌의 스캔은 아니다.

하지만 적어도 풀테이블스캔보다는 낫기 때문에 완벽하게 인덱스를 사용하는 상황은 아니더라도 그나마 탐색 범위를 좁히기 위해 사용되는 느낌으로 이용된다.

루스 인덱스 스캔은 인덱스 레인지 스캔 방식과 거의 동일한데, 레인지 스캔 방식은 범위에 해당하는 모든 레코드들을 순차적으로 따라가며 전부 열람하는 반면, 루스 인덱스 스캔은 해당하는 범위에서 중간중간 필요하지 않는 인덱스들을 건너뛰어가며 열람하는 방식이라는 것에서 차이가 있다.

클러스터링 인덱스

MySQL의 InnoDB는 특별하게도 사용자가 테이블에 인덱스를 따로 설정하지 않았더라도 클러스터링 인덱스라는 것을 자동으로 만들어 테이블마다 반드시 하나 보유하게 한다.

클러스터링 인덱스는 일반적으로 테이블의 프라이머리 키를 바탕으로 구성되는 인덱스로, 만약 테이블 내 프라이머리 키가 따로 존재하지않는 경우라고 하더라도 정해진 규칙에 맞게 어떻게든 알아서 설정된다.

(만약 테이블 내 프라이머리 키가 없다면 Not Null 하면서 유니크한 칼럼들 중에 가장 첫번째 순번의 칼럼이 클러스터링 인덱스의 키가 된다. 만약 그러한 칼럼 또한 존재하지 않는다면 유니크 하면서 AutoIncrement 특성을 갖고 있는 칼럼을 시스템이 자동으로 생성해 클러스터링 인덱스의 키로 삼는다.)

(일반적으로 인덱스는 프라이머리 키와 프라이머리 키가 아닌 다른 모든 인덱스 키(보조 키)로 구분된다. 프라이머리 키는 하나의 레코드를 대표하는 값(식별자)이다.)

일반적인 인덱스 트리의 리프노드에는 각각의 인덱스와 대응되는 실제 원본 데이터의 주소값들이 저장되어 있다.

즉 인덱스 트리를 따라 리프노드에 도착했다면 리프노드에서 원본 데이터의 주소를 얻어내고 이것을 이용해 디스크에서 원본 데이터로 접근하는 방식이다.

하지만 클러스터링 인덱스 트리는 일반적인 인덱스 트리의 방식과는 다르게 리프노드에 아예 원본데이터를 전부 저장하고 있다.

그리고 실제 원본 데이터의 값들이 클러스터링 인덱스 트리의 리프노드에 전부 저장되어 있다는 특성을 이용하고 있는 InnoDB의 인덱스들은 아예 이 클러스터링 인덱스를 하나의 주소값으로 이용하고 있다.

즉 InnoDB에서 인덱스를 타는 과정은, 인덱스 트리의 리프노드에서 알맞은 클러스터링 인덱스 키값을 찾고, 이후 클러스터링 인덱스 트리로 넘어가 다시 리프노드에서 키를 찾아 전체 데이터를 가져오는 식으로 진행된다.

이러한 InnoDB의 인덱스를 타는 방식은 결국 2번이나 트리를 타기 때문에 일반적인 방식보다 다소 성능이 떨어지는 것처럼 보이지만, 프라이머리 키를 직접 이용하여 인덱스를 타는 상황이라면 데이터를 찾아오기 위해 디스크 주소에 직접 접근하지 않아도 되므로 굉장히 빠른 탐색 성능을 보여주게 된다.

더 알아둘만한 것

1. B-Tree 내 인덱스 삭제와 변경 방식으로 인하여 생기는 데이터 파편화에 관한 내용.

   (MySQL은 효율적인 성능 관리를 위하여 인덱스가 삽입,삭제,변경되었을 때 곧바로 해당 내용을 디스크에 적용시키고 업데이트하지 않는다. 그리고 인덱스가 삭제되었을 때 해당 키의 내용을 지워서 자리를 단순히 비워두기만 할 뿐이지 자리 공간 자체를 삭제 시키지 않는다. 자리 자체를 없애는 것은 성능 비용이 많이 들어가는 작업이기 때문이다. 이렇게 실질적인 데이터는 존재하지않는 비워진 껍데기 키들만 인덱스 트리에 계속해서 쌓여나가게 된다면 결국 데이터를 탐색하는 과정 또한 비효율적으로 작동하게 될 것이다)

2. 랜덤I/O와 순차I/O의 작업 성능의 차이. 랜덤I/O가 느린 이유와 전체 레코드의 20~30%를 검색할 때 어째서 인덱스를 활용한 레인지 스캔보다 테이블 풀 스캔을 이용하는 것이 나은 지에 관한 내용.