MySQL 인덱스 종류, 내부 동작

1. MySQL 인덱스

다음과 같은 것들에 대해서 알아보자.

인덱스의 종류
인덱스가 사용하는 자료구조
인덱스의 내부 동작

2. 인덱스의 종류

클러스터형 인덱스와 보조 인덱스로 나뉜다.

클럭스터형 인덱스는 영어사전, 보조 인덱스는 책 뒷편에 있는 색인이라고 생각하자.

클러스터형 인덱스는 테이블당 한개만 생성가능하다. 흔히 우리가 Primary Key로 지정하는 컬럼이 클러스터형 인덱스이다. 클러스터형 인덱스는 앞에서 이야기 했듯이 생성과 동시에 오름차순으로 정렬된다. 또한, primary key는 테이블당 한개만 만들 수 있기 때문에 위의 조건을 충족한다. 클러스터형 인덱스가 될 수 있는 후보군에는 NOT NULL Uniuqe 제약조건을 경우에도 클러스터형 인덱스가 될 수 있다. 하지만 primary key랑 동시에 만든다면, primary key가 우세함으로 클러스터형 인덱스가 된다.

보조 인덱스(비 클러스터형 인덱스)라고도 한다. 여기에서는 편의상, 보조 인덱스라고 하겠다. 보조 인덱스는 검색(조회)을 용이하게 하기 위해서, 특정 컬럼(열), 컬럼들의 조합을 통해서 인덱스를 생성한다. 보조 인덱스는 테이블의 컬럼들로 지정할 수 있다.

3. 인덱스의 자료구조

인덱스는 B-TREE(Balanced Tree) 자료 구조를 가진다. 다음과 같이 테이블의 index정보를 조회해보자.

show index from tblname; 

해당 테이블의 index 정보들이 출력된다. 그중에서 index_type부분을 보면 BTREE 라고되어있다.

BTREE에서는 루트 노드, 리프(leaf) 노드와 같이 노드라는 개념으로 설명하는데

MySQL 에서는 노드를 페이지(Page)라고 부른다. 앞으로 페이지라고 부르는 건, BTREE의 하나의 노드라고 생각하면 된다.

페이지는 16Kbyte크기의 최소 저장단위이다.

3.1. 왜 BTREE 자료 구조를 사용할까?

인덱스를 사용하는 이유이기도 한데, 대부분 인덱스를 사용하는 이유는 쿼리 튜닝, 조회에 대한 효율을 높이기 위해서 사용한다. 그 말인 즉슨, 전체 데이터의 양이 크다면, 이를 얼마나 적은 범위로 스캔(탐색)해서 데이터를 가져올거냐 이런 문제가 생긴다. 당연히 전체 데이터를 스캔할 필요가 없고, 균형트리에 적절하게 분산되어 저장이 되어있다면, 특정 범위로 조회했을때, 굳이 전체 스캔을 하지 않아도 된다. 그렇기 때문에 BTREE 자료 구조를사용한다.

하지만 데이터를 저장할때 마다, 인덱스를 설정한 컬럼(열)에 대해서 인덱스를 만들어 내기 때문에(정렬된 페이지를) 이 또한 많은 비용이 든다. 그렇기 때문에 무조건 인덱스를 많이 건다고 좋은건 아니다. 그만큼 데이터의 변경(INSERT, UPDATE, DELETE)이 잦다면 그만큼 인덱스를 만들어 내기 때문에 어떤 니즈가 있느냐를 판단해서 적절하게 사용하는게 바람직하다.

4. 인덱스의 내부 동작

인덱스의 내부 동작은 클러스터형 인덱스와 보조 인덱스 그리고 혼합해서 사용하는 경우로 나눠서 설명한다.

4.1. 클러스터형 인덱스

다시 복습차원에서, 클러스터형 인덱스는 영어사전과 같고, 테이블당 한개만 만들수 있고, 흔히 primary key로 사용하는 열이 클러스터형 인덱스라고 보면된다. 데이터를 INSERT 할때마다 루트 페이지와 리프 페이지가 재 정렬이 된다. 그래서 조회 하면 (다른 보조 인덱스를 걸지 않은 상태라면) 클러스터형 인덱스 키를 기준으로 오름차순 정렬 한다는 것을 알 수 있다. 데이터가 INSERT 할때 마다 루트 페이지와 리프 페이지를 재 정렬한다고했는데, 여기에서 리프 페이지가 (데이터 페이지)이기도 하다.

클러스터형 인덱스에서는 루트 페이지, 리프 페이지(데이터 페이지)가 등장한다.

4.2. 보조 인덱스

보조 인덱스는 클러스터형에서 리프 페이지가 곧 데이터 페이지라고 했지만, 보조 인덱스에서는 데이터 페이지는 그대로 두고, 새로운 루트 페이지, 리프 페이지의 인덱스를 구성하고, 리프페이지는 데이터 페이지를 참조하는 포인터를 저장한다. 포인터에는 데이터 페이지의 메모리 주소 + OFFSET이 들어있다.

그러니까, 보조 인덱스에서는 루트 페이지, 리프 페이지, 그리고 리프 페이지가 가르키는 데이터 페이지가 등장한다.

4.3. ‼️ 검색과 삽입하는 경우를 살펴보자.

4.3.1. 검색 하는 경우

클러스터형 인덱스로 검색을 하는 경우에는 루트 페이지, 리프페이지(데이터페이지) 로 구성되어있고, 이미 데이터가 INSERT될때 정렬되어 있기 때문에, 최소 2번의 검색을 한다. (루트 한번, 그리고 리프페이지(데이터 페이지) 한번)

하지만 보조 인덱스인 경우에는, 루트 페이지, 리프페이지, 그리고 리프페이지가 가르키는 포인터(데이터페이지) 이렇게 총 3번의 페이지를 탐색한다. 데이터가 적은 경우에 이렇다라는 것이고, 데이터가 많은 경우에는 훨씬 더 차이가 많이 난다.

결론: 검색할때는 클러스터형 인덱스가, 보조 인덱스보다 훨씬 빠르다!!!

4.3.2. 삽입하는 경우

이번에는 데이터를 삽입할 때를 알아보자. 클러스터형 인덱스는 루트 페이지와 리프페이지(데이터 페이지)를 재 정렬할기 때문에 (페이지 분할) 매번 삽입할때는 비용이 많이든다. 반면 보조 인덱스인 경우에 데이터페이지는 그대로 append로 순차적으로 데이터가 쌓이는 것이고, 루트 페이지와 리프 페이지에서 데이터도 약간의 조정만 일어나지, 페이지 분할이 일어나지는 않는다.

그렇기 때문에 데이터를 삽입할때는 보조 인덱스가 클러스터형 인덱스보다 비용이 적게 들어간다.

4.4. 클러스터형 인덱스와 보조 인덱스가 혼합된 경우

위에서는 각각 클러스터형 인덱스만 존재하는 경우, 보조 인덱스만 존재하는 경우만을 설명했다. 하지만 실무에서는 클러스터형 인덱스와 보조 인덱스가 혼합되어 있는 경우가 다반사다. 이런 경우에 내부적으로는 어떻게 구성이 되어있을까?

일단은 보조 인덱스로 루트페이지와 리프 페이지가 구성되어있고, 그 리프페이지가 데이터 페이지의 참조값을 가리켰다면, 이번에는 클러스터형 인덱스의 루트 페이지를 가르킨다. 그리고 클러스터형 인덱스의 루트 페이지는 다시 리프 페이지(데이터페이지)를 가르킨다. 클러스터형 인덱스는 변함이 없다.

그렇다면 왜? 보조 인덱스의 리프페이지가 데이터페이지의 참조값을 가르키지 않고, 다시 클러스터형 인덱스의 루트 페이지를 가르키는 것일까??? 데이터 페이지의 참조값을 가르키면, 보조 인덱스의 루트 페이지 > 리프페이지 > 데이터페이지로 비교적 빠르게 검색할 수 있을텐데? 라는 생각이 들 수 있다. 사실 맞는 이야기이긴 하다.

하지만 만약에 데이터를 삽입해 본다고 가정해보자. 데이터를 삽입하게 되면, 원래 클러스터형 인덱스가 페이지 분할(기존의 방식과 동일하다)이 일어난다. 그리고 클러스터형 인덱스의 리프페이지(데이터 페이지)도 재정렬된다. 그러면 데이터페이지를 참조하는 보조 인덱스의 리프 페이지도 포인터를 다시 변경해야 한다. 이런 문제 때문에 보조 인덱스의 리프페이지가 실제 데이터 페이지의 참조값을 저장하는게 아니라, 클러스터형 인덱스의 루트 페이지를 탐색하게 mysql에서 그렇게 구성을 했다.

정리하자면 혼합되어있는 경우, 보조 인덱스를 지정한 컬럼으로 검색을 하게 되면 다음과 같다.