인덱스

인덱스 이해하기

찾아봐라

예를 들면서 시작해보자. item 이라는 테이블이 있고 이 테이블에 5천만개의 데이터가 있고 name 속성은 중복되지 않는다고 가정해보자. 이 데이터중 나는 name이 홍길동이라는 행을 찾고 싶다고 가정해보자. 가장 쉬운 방법은 이 5천만개의 데이터를 하나씩 스캔하면서 홍길동이라는 이름을 가진 행을 검색하는것이다. 그런데 이 홍길동이라는 이름을 가진 행을 몇번의 시도만에 발견하면 좋겠지만 이 데이터가 테이블의 가장 마지막에 있다면 우리는 총 5천만번의 행을 검색해야 하며 행을 찾는데 아주 많은 시간을 들여야 한다.

이렇게 데이터가 많아질수록 하나하나씩 모두 검색하는 방법은 매우 비효율적이다. 그래서 데이터베이스에는 인덱스라는 개념이 존재하며 이는 데이터를 특정 기준으로 저장한다는 걸 의미한다. 위를 다시 예로 들면 테이블이 데이터가 이름을 기준으로 정렬이 되어있으며, 가장 처음 홍이라는 이름으로 시작하는 데이터의 위치가 4천5백만번째라는 것을 바로 알 수 있었다면 우리는 앞의 4천 5백만개의 데이터를 검색하지 않고 뒤의 5백만개의 데이터에서 홍길동을 찾으면 되었다.

인덱스 좋아!

인덱스를 사용한다면 이처럼 전체의 데이터를 모두 볼 필요없이 데이터의 중간에서 원하는 데이터를 빠르게 찾을 수 있다.

야 인덱스 좋대 몽땅 걸어버리자!

인덱스를 추가한다는 것은 우리가 지정한 순서에 맞게 데이터를 넣어줘야 한다는 것이다. 근데 만약 인덱스를 엄청 추가했다면???

“이름은 가나다 순으로하며… 나이는 많은 순으로 앞에 배치하고… 사는 동네는 지번기준으로 하며… 생성시점을 기준으로… 핸드폰 벤더사는… 좋아하는 음식이… 이성교제 여부가…. 아니 어디다가 저장하라는거야?”

이렇게 열심히 따라가면서 적당한 위치를 찾아 저장했는데 실제로 사용할 때는 이름이랑 나이 순만 필요하다면?? 혹은 변경이 빈번하게 일어나는 컬럼에 인덱스를 추가하면서 해당 컬럼 수정이 있을 때마다 위와 같은 행동을 해야한다면 얼마나 괴로울텐가. 고로 무조건적으로 인덱스를 추가하는 것은 오히려 성능을 해칠수도 있다.

인덱스의 종류

MySQL에서 사용되는 인덱스는 크게 Clustered Index(클러스터형 인덱스)와 Secondary Index(보조인덱스) 두 가지가 있다. 클러스터형 인덱스는 테이블당 한 개만 생성할 수 있고, 보조 인덱스는 테이블당 여러 개를 생성할 수 있다. 또한 클러스터형 인덱스의 경우 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬한다.

클러스터형 인덱스가 생성되는 조건은 아래 두가지다

1. 테이블의 primary key로 지정한 열은 클러스터형 인덱스가 생성된다
2. 테이블에 primary key가 없는 상태에서, unique & not null 로 지정한 열은 클러스터형 인덱스가 생성된다.

인덱스의 내부 작동 이해하기

B-Tree(Balanced Tree, 균형 트리)

데이터베이스 인덱스에 널리 사용되는 트리 데이터 구조이다. 구조는 항상 정렬된 상태로 유지되므로 정확한 일치 및 범위를 빠르게 조회할 수 있다. 이 유형의 인덱스는 InnoDB 및 MyISAM과 같은 대부분의 스토리지 엔진에서 사용한다.

B-트리 노드는 이진 트리와 다르게 하나의 노드가 2개 이상의 많은 자식 노드를 지닐 수 있다.

노드로 설명하기는 이는 개념적인 정의이며 MySQL에서는 이를 페이지라 부른다. 페이지는 16Kbyte 크기의 최소한의 저장 단위이다. 다른 말로는 페이지에 아주 작은 데이터 하나만 저장된다고 하더라도 페이지는 16Kbyte를 차지하게 된다는 의미이다. (버전에 따라 다르지만 페이지 사이즈를 4Kb, 8KB, 16KB, 32KB, 64KB 등으로 지정 가능하다)

B-트리는 데이터를 검색할 때 성능이 가장 뛰어나며 반면 B-트리는 데이터 변경 작업 시에 성능이 나빠지는 단점이 있으며, 특히 INSERT 작업이 일어날 때 성능이 급격히 느려질 수 있다. 이는 페이지 분할 이라는 작업과 연관이 깊다.

페이지 분할

페이지 분할이라는 것은 페이지에 더이상 저장할 공간이 없을 때 일어나게 된다. 예를 들어 특정 페이지에 최대 4개 데이터를 삽입할 수 있다고 가정해보자. 해당 페이지에 새로운 데이터를 삽입하려고 하는데 이미 저장할 공간이 없는 상태이다. 이럴 때는 비어있는 페이지를 한 개 확보한 후에 꽉차 있는 페이지와 공평하게 나누어 각자 저장을 하게 된다. 고작 데이터 하나를 추가하는 일인데 꽤나 번거로운 작업을 하였다.

• 추가 페이지 한 개를 확보
• 루트 페이지에 추가된 페이지 등록
• 두개의 페이지가 공평하게 나누어 가져가기 위해 데이터들을 이동

그리고 예시로 들지는 않았지만 두 번째 행위 루트페이지에 추가된 페이지를 등록하려는데 루트페이지의 저장 공간이 부족하다면 루트페이지 또한 새롭게 만들면서 위와 같은 작업을 거쳐야 한다..

보조 인덱스의 구조

보조인덱스는 위의 클러스터형과는 다른 구조로 페이지가 구성된다. 클러스터형 인덱스에서는 인덱스 페이지 가 있었고, 인덱스 페이지는 루트페이지와 리프페이지로 구성되었었다. 그리고 이 리프페이지에 데이터가 담기는 구조였다. 그러나 보조인덱스는 인덱스 페이지 와 데이터 페이지 가 따로 존재하게 된다. 보조 인덱스에서는 별도의 장소에 인덱스 페이지를 생성한다.

생성하는 과정은 다음과 같다. 먼저 인덱스 페이지의 리프 페이지에 인덱스로 구성한 열을 정렬한다. 그리고 데이터 위치 포인터를 생성한다. 데이터의 위치 포인터는 클러스터형 인덱스와 달리 주소값(페이지 번호 + 오프셋)이 기록되어 바로 데이터의 위치를 가리키게 된다.

보조인덱스는 클러스터형 인덱스와 비교하여 비교적 삽입/수정/삭제에서는 성능에 주는 부하가 더 적다. 보조 인덱스는 삽입시 페이지를 정렬하는 것이 아니고 데이터 페이지의 뒤쪽에 삽입후 인덱스의 리프페이지에 적당한 위치에 주소값을 넣어주면 된다(물론 클러스터형과 같이 페이지 분할이 있을수 있지만 대게 클러스터형 인덱스가 더 시스템 부하가 많이 생긴다고 한다.)

클러스터형 인덱스와 보조 인덱스가 혼합되어 있을 경우

클러스터형 인덱스와 보조 인덱스를 혼합하여 사용하는 경우는 생각보다 복잡하지 않다. 클러스터형 인덱스 페이지는 우리가 배운 그 구조 그대로 사용하되, 차이는 보조 인덱스의 리프페이지에 있다. 위에서는 보조 인덱스의 리프페이지에 주소값(데이터 페이지 번호 + 오프셋)이 기록되었는데 클러스터형 인덱스와 같이 혼합하여 사용하는 경우에는 리프페이지에 primary key 값(=인덱스 키 값)이 담긴다. 그리고 이렇게 검색된 인덱스 키값을 이용해 인덱스 페이지의 루트리프로부터 시자하여 검색을 시작한다.