'이것이 MySQL이다' 라는 책을 보며 내용을 정리 한 글입니다.
인덱스의 개념
인덱스의 개념을 이해하기 위해서는 책의 예가 가장 대표적이다.
책에서 어떤 키워드를 보다 손쉽게 찾기 위해서는 책의 가장 뒷쪽에 있는 <찾아보기>와 같은 페이지를 확인하면 이미 정렬이 되어 있어 보다 빠르고 쉽게 원하는 키워드를 찾아 접근할 수 있다.
만약 <찾아보기>와 같은 페이지가 존재하지 않는 책이라면 원하는 키워드를 찾기 위해 처음부터 찾아보는 방법 외엔 없다.
위에서 설명한 <찾아보기>가 바로 MySQL에서의 Index의 개념과 아주 유사하다.
지금까지 우리가 사용한 테이블은 데이터가 많지 않았기 때문에 인덱스까지 고려하지 않아도 성능상으로 문제가 있음을 체감하지 못했다. 하지만 데이터의 양이 굉장히 방대해지면 이 인덱스의 유무가 성능면에서 굉장한 차이를 발생시킨다.
결국 인덱스는 데이터를 좀 더 빠르게 찾을 수 있도록 해주는 도구로 이해하면 된다.
인덱스의 장단점
위의 인덱스의 개념을 이해했으면 인덱스를 사용하는 것이 무조건 좋다고 생각하기 쉽다. 하지만 인덱스의 실제 사용은 개념처럼 간단하지 않다.
실제로 많은 케이스를 고려하지 않고 설계되고 사용 된 인덱스의 경우 오히려 인덱스를 사용하지 않는 것보다 못한 결과를 초래하기 때문이다. 따라서 인덱스의 장점과 단점에 대해서부터 개념적으로 이해해보자.
책의 <찾아보기>의 기능을 서두에 이야기 했는데, 만약 <찾아보기>를 통해서 '데이터베이스'라는 단어를 찾는다고 가정해보자. 데이터베이스에 관련 된 서적에서는 '데이터베이스'라는 단어의 표현은 굉장히 많이 등장하기 때문에 '데이터베이스'라는 단어 옆에 페이지 번호는 수백 또는 수천 개가 연속해서 나오게 된다. 이러한 경우로 인해 <찾아보기>가 책의 페이지만큼 되거나 오히려 책의 내용보다 더 두꺼워 질 수 있다. 그리고 <찾아보기>를 통해서 '데이터베이스'를 찾으려고 하니 <찾아보기> 한 번, 실제 페이지 한번 이런식으로 왔다갔다 하는 반복으로 인해 시간과 수고의 낭비가 발생한다.
실제 데이터베이스에서도 이와 비슷한 일이 일어난다. 필요 없는 인덱스를 만드는 바람에 데이터베이스가 차지하는 공간만 더 늘어나고 인덱스를 이용해서 데이터를 찾는 것이 전체 테이블을 찾는 것보다 훨씬 느려진다.
장점
- 검색 속도가 무척 빨라질 수 있다. ( 항상 그런 것은 아니니 설계를 잘 해야한다.)
- 그 결과 해당 쿼리의 부하가 줄어들어 결국 시스템 전체의 성능이 향상된다.
단점
- 인덱스가 데이터베이스 공간을 차지해서 추가적인 공간이 필요해지는데, 대략 데이터베이스 크기의 10% 정도의 추가 공간이 필요하다.
- 처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
- 데이터의 변경 작업(Insert, Update, Delete)이 자주 일어날 경우에는 오히려 성능이 많이 나빠질 수 있다.
결국 인덱스는 Insert, Update, Delete와 같은 데이터의 변경 작업의 성능을 희생하여, Select와 같은 쿼리의 성능을 향상시키는 작업이며, 잘못된 사용은 오히려 사용하지 않는 것보다 더 나쁜 결과를 초래할 수 있다.
인덱스의 종류
클러스터형 인덱스(Clustered Index)
- 클러스터형 인덱스는 영어 사전처럼 책의 내용 자체가 순서대로 정렬되어 있어서 인덱스 자체가 책의 내용과 같은 것을 말한다.
- 클러스터형 인덱스는 테이블당 한 개만 생성할 수 있으며, 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬한다.
- 테이블 생성 시에 제약 조건으로 Primary Key 또는 Unique를 사용하면 인덱스가 자동 생성된다. Primary Key는 테이블당 하나만 생성할 수 있기 때문에 Primay Key가 지정된 열에 클러스터형 인덱스가 생성되는 것은 자연스러운 일이다.
- Unique에 Null False면 클러스터형 인덱스로 지정된다.
- Primary Key와 Unique NOT NULL이 있으면 Primary Key로 지정한 열에 우선 클러스터형 인덱스가 생성된다.
- Primary Key로 지정한 열로 데이터가 오름차순 정렬된다.
보조 인덱스(Secondary Index), 비클러스터형 인덱스(Non Clustered Index)
- 보조 인덱스는 앞에서 설명했던 <찾아보기>가 별도로 있고, <찾아보기>를 찾은 후에 그 옆에 표시 된 페이지로 가야 실제 찾는 내용이 있는 것을 말한다.
- 보조 인덱스는 테이블당 여러 개를 생성할 수 있다.
- Unique에 Null True면 보조 인덱스로 지정된다.
인덱스의 내부 작동
인덱스의 내부적인 작동을 이해하기 위해서는 몇가지 개념의 정립이 필요하다.
B-Tree(Balanced Tree, 균형 트리)
이 B-Tree 구조는 데이터를 검색(SELECT 구문을 사용할 때) 뛰어난 성능을 발휘한다.
만약, B-Tree 구조가 아니라면 루트 페이지 및 그 연결은 존재하지 않고 그냥 리프 페이지만 있을 것이다. 어떤 데이터를 찾기 위한 방법은 처음부터 검색하는 방법 외엔 존재하지 않게 된다.
B-Tree구조라면 우선 루트 페이지를 검색하게 된다. 모든 데이터는 정렬되어 있기 때문에 루트 페이지의 정렬을 보고 연결 된 리프 페이지로 직접 이동하면 된다.
페이지 분할
인덱스를 구성하게 되면 데이터의 변경 작업 시에 성능이 나빠지는 단점이 있다고 했다. 특히, INSERT 작업이 일어날 때 성능이 급격히 느려질 수 있다. 그 이유는 '페이지 분할'이라는 작업이 발생되기 때문이다.
페이지 분할에 대해서는 위 블로그의 그림을 보며 글을 읽으면 이해하기 어렵지 않을 것이다.
결론적으로는, 데이터의 변경 특히, INSERT 작업을 수행하게 되면 페이지에 더이상 새로운 데이터를 넣을 자리가 없게 되면 새로운 페이지를 생성하며, 루트 페이지의 경우 새로운 루트 페이지까지 생성하기 때문에 새로운 페이지 할당과 페이지 분할의 작업이 수회 이루어지게 된다.
데이터가 많을 경우 이러한 작업의 반복 또한 기하급수적으로 늘어난다. 이제 왜 데이터의 변경 작업을 수행할 때 성능이 안좋아지는지 이해했을 것이다.
클러스터형 인덱스와 보조 인덱스의 구조
클러스터형 인덱스의 구조
루트 페이지와 리프 페이지로 구성되어 있으며, 리프 페이지는 곧 데이터 페이지가 된다. 그렇기 때문에 원하는 데이터에 접근하기 위해서는 루트페이지에 접근 한 후 바로 연결 된 리프 페이지로 접근하면 데이터 페이지이기 때문에 원하는 데이터에 바로 접근할 수 있다.
보조 인덱스의 구조
보조 인덱스는 루트 페이지와 리프 페이지가 존재하고 별도로 데이터 페이지가 존재한다. 그리고 각 페이지에는 데이터 위치 포인터를 생성한다.
따라서 보조 인덱스 구조에서 데이터에 접근하기 위해서는 루트 페이지에 접근한 후 해당 위치 포인터로 접근하면 리프 페이지로 접근하게 되는데 리프 페이지에 저장되어 있는 위치 포인터에 접근해서 실제 데이터 페이지에 접근한다.
이렇게 실제 데이터 페이지까지 접근하면 실제 데이터 값에 접근할 수 있다.
따라서 리프 페이지는 정렬이 되어 있고, 이 리프 페이지가 곧 데이터 페이지이므로, 클러스터형 인덱스는 범위로 검색 시에 아주 우수한 성능을 보인다.
보조 인덱스는 데이터 페이지를 정렬하는 것이 아니기 때문에 데이터 페이지의 뒤쪽 빈 부분에 삽입된다. 따라서 클러스터형 인덱스보다 데이터 입력에서는 성능에 주는 부하가 일반적으로 더 적다.
클러스터형과 보조 인덱스의 혼합 구조
두 인덱스가 혼합 된 내부 구조의 경우 클러스터형 인덱스의 경우에는 그대로 변함이 없다. 하지만 보조 인덱스의 경우 변화가 생긴다. 클러스터형 인덱스 페이지가 없었다면 '데이터 페이지의 주소값'으로 구성되어 있겠지만, 혼합 된 경우 '클러스터형 인덱스의 키 값'을 가지게 된다. 또한, 보조 인덱스를 검색한 후에는 모두가 다시 클러스터형 인덱스의 루트 페이지부터 검색을 한다는 점이다.
따라서 혼합된 경우 다음과 같은 순서로 데이터를 검색한다.
보조 인덱스의 루트 페이지에서 먼저 검색- 클러스터형 인덱스의 키 값을 확인 후, 무조건 클러스터 인덱스의 루트 페이지로 가서 찾음
- 클러스터 인덱스의 루트 페이지에서 리프 페이지로 가서 실제 값을 찾아낸다.
그런데 이런식으로 동작을 할 경우 보조 인덱스의 리프 페이지에 기존처럼 '데이터 페이지의 번호 + #오프셋'으로 구성하면 검색이 더 빠르고 효율적이지 않을까?'하는 물음이 들 수 있다.
그렇다면 보조 인덱스의 리프 페이지가 기존과 같이 동작 할 경우에 데이터를 INSERT 하는 경우를 생각해보자. 그러면 데이터의 삽입으로 인해서 클러스터형 인덱스의 리프 페이지가 재구성되어(페이지 분할로 인해) '데이터 페이지의 번호 + #오프셋'이 대폭 변경된다.
그러면 단 한 건의 행 삽입으로 클러스터형 인덱스의 데이터 페이지의 페이지 번호 및 오프셋이 변경되므로 보조 인덱스 역시 많은 부분이 다시 구성되어야 한다. 엄청난 시스템의 부하를 발생시킬 여지가 충분하다는 뜻이다.
클러스터형 인덱스의 키를 보조 인덱스에 저장한다고 했다. 그러므로 클러스터형 인덱스를 지정할 열의 자릿수가 크면 보조 인덱스에 저장되어야 할 양도 더불어 많아진다. 따라서 혼합되어 사용 할 경우 클러스터형 인덱스로 설정 할 열은 적은 자릿수의 열을 선택하는 것이 바람직하다.
