[이것이 MYSQL이다] 인덱스에 대하여

본 포스트는 2021년에 업로드된 포스트입니다. 개념이 아직 정리안된 나이에 업로드한 포스트이기에 수정사항이 있으면 연락주세요.

인덱스란

인덱스란 한마디로 정의하면 인덱스 == 탐색이라고 할 수 있을것같다.

 

sql에서 인덱스를 잘 활용하면 검색속도가 무척이나 빨라진다.

 

대용량 테이블을 예시로 들어보자면 row가 30만개가 있는 테이블을 예시로 들어본다.

 

여기서 한개의 값만 찾으려면 엄청나게 많은 시간이 소요될 것이다.

 

또한 이러한 작업이 반복된다면 시스템에 큰 과부하를 줄 수 있다. 따라서 인덱스를 사용하면 검색속도(SELECT)가 엄청나게 빨라질것이다, 그러한 이유는 인덱스는 책 뒤에 찾아보기페이지와 비슷하다.

 

만약 책에서 어떠한 한 부분이 나오는 페이지를 찾으라고 할때 전체 페이지 수가 1000페이지라면 그 중에서 1페이지를 찾기란 엄청난 시간이 걸리겠지만 찾아보기 페이지를 봐서 해당 페이지의 위치로 이동하면 바로 원하는 페이지 수를 찾을 수 있다.

 

인덱스도 같은 원리라고 할 수 있다.

 

30만개의 데이터중 특정한 1개의 데이터만 찾아내기란 엄청난 시간이 소요된다 (FULL TABLE SCAN) 하지만 앞서 말한 인덱스의 개념으로 들어가면 위치를 기억하고 있어서 해당 위치로 빠르게 이동하여 데이터를 찾을 수 있기(INDEX-SCAN)에 검색속도가 대폭적으로 향상된다.

인덱스의 종류와 장단점

인덱스의 종류는 크게 2가지로 볼 수 있다.

 

첫번째는 클러스터 인덱스와 두번째는 보조 인덱스이다.

 

클러스터 인덱스는 영어사전과 같이 생각할 수 있고 보조 인덱스는 앞서 말한 찾아보기의 개념과 같이 생각할 수 있다 그러한 이유는 아래에서 자세하게 설명하겠다.

 

이러한 인덱스의 종류를 잘 파악해서 사용하고자 하는 위치에 사용한다면 앞서 말한대로 시스템의 효율성이 엄청나게 커질 것이다.

하지만 이러한 인덱스도 잘못사용하게 된다면 오히려 독이 될수 있다.

 

인덱스의 장점

1. 대용량 테이블에서 데이터를 탐색 할 때의 효율성을 극적으로 올릴 수 있다.
2. 시스템의 효율성을 대폭적으로 늘려줄 수 있다.
3. 데이터의 검색시간을 대폭적으로 줄일 수 있다.

 

인덱스의 단점

1. 소량의 데이터가 있는 테이블에는 사용하면 시스템에 악역향을 끼친다.
2. 삽입, 삭제, 수정등의 작업에서는 오히려 시스템을 저하시킬 수 있다.
3. 인덱스를 잘못된 위치에서 사용할 시 오히려 검색이 느려진다.
4. 인덱스를 생성하기 위해 약 10%의 공간이 소비된다.

 

인덱스의 장점과 단점을 정리해보았다.

 

장점의 이유는 이해할수 있겠지만 단점의 경우는 추상적이여서 잘 이해가 안갈수있다.

 

아래에서 원리를 설명하면서 차근차근 정리해보겠다.

클러스터 인덱스와 보조 인덱스

클러스터 인덱스는 사실 PRIMARY KEY의 제약조건 차제가 클러스터 인덱스다 따라서 별도의 생성 구문이 없다.

 

또한 PRIMARY KEY가 클러스터 인덱스이므로 테이블에 단 1개의 컬럼에서만 존재할 수 있다.

 

그리고 데이터의 중복을 허용하지 않는다 (기본키의 제약조건이 데이터의 중복을 허용하지 않기 때문이다.)

 

보조인덱스는 UNIQUE의 제약조건 자체가 보조 인덱스이다. 따라서 클러스터 인덱스와 동일하게 특별한 생성구문이 존재하지 않는다.

 

UNIQUE 제약조건의 특징은 데이터에 NULL값을 허용하며 중복을 허용하지 않고 여러 컬럼에 생성할 수 있다.

 

따라서 위의 내용을 정리하면 인덱스는 우리가 생성하는 제약조건에 기본적으로 내장되어 있다고 생각해도 무방하다. 이제 여기서 의문이 들 것이다 "그럼 지금까지 기본키와 UNIQUE키를 계속해서 생성해서 작업을 해왔는데 왜 인덱스가 적용이 안되는 것인가?"

 

이 질문에 대한 답은 인덱스는 Analyze문으로 인덱스의 사용을 설정해주어야 하기 때문이다. (Analyze문은 테이블 최적화 문으로 인덱스와 같은 키를 재구성 하여서 시스템의 효율을 높이는 것이므로 재구성과 동시에 인덱스를 설정하므로 인덱스의 사용을 설정한다는 말도 된다.)

 

Analyze문으로 인덱스의 사용을 설정하게 된다면 쿼리문에서 검색할때 인덱스를 사용할 수 있을것이다. (테이블에서 인덱스가 사용된 컬럼을 보려면 SHOW INDEX문을 사용하면된다.)

create index문

인덱스는 create index문으로 인덱스를 생성할 수도 있다.

 

create index문으로는 클러스터 인덱스는 생성할 수 없다 따라서 create index문은 기본적으로 생성할시 보조인덱스가 생성된다.

앞서 말했듯이 unique 인덱스는 alter문으로 생성할 수도 있다.

 

따라서 create index와 unique 제약조건의 생성방법의 기능적인 차이는 없다. (아마 원리적의 차이가 있을 것으로 예상된다.)

create index문으로 인덱스를 생성하는 방법은 다음과 같다.

CREATE INDEX test_index
 ON testTBL (userID);

ALTER TABLE testTBL ADD CONSTRAINT test_index
	UNIQUE (userID);

 

위에 예시를 보면 알 수 있듯이 위의 구문은 서로 기능적 차이는 없다.

 

둘다 UNIQUE 제약조건을 추가하여 보조 인덱스를 생성하는 방법이라고 할 수 있다.

 

이제 위의 인덱스를 적용시켜보자

ANALYZE TABLE testTBL;

 

위의 쿼리문은 앞서 위에서 설명한 애널라이즈 테이블 쿼리이다.

 

해당 쿼리문을 사용하면 testTBL에 인덱스 사용을 설정한다라는 말이 됨으로

인덱스를 통한 데이터 검색쿼리를 수행 할 수있다.

 

자세한 설명은 나중에 좀더 찾아보겠다.

 

이렇게 하여서 보조 인덱스를 따로 생성할 수 있다.

클러스터 인덱스와 PRIMARY KEY

앞서 말한듯이 클러스터 인덱스는 기본키 그 자체와 비유하자면 영어사전과 같다고 할 수 있다.

 

기본키의 특정을 정리하자면 아래와 같다

1. 테이블에 단 1개의 컬럼에만 사용이 가능하다.
2. 중복을 허용하지 않는다
3. NULL을 허용하지 않는다
4. 기본키를 사용한 컬럼에는 데이터가 정렬되어있다.

 

위와 같이 대표적인 기본키의 제약조건을 정리해보았다.

 

클러스터 인덱스는 이러한 기본키의 제약조건을 따르게 된다.

 

앞서 말한것중에서는 CREATE INDEX문을 사용하였다

 

다음 쿼리문은 항상 보조 인덱스만 생성하며 클러스터 인덱스를 생성하려면 PRIMARY의 제약조건을 추가해야지만 클러스터 인덱스가 추가된다고 할 수 있다.

보조 인덱스와 UNIQUE KEY

보조인덱스는 유니크 인덱스 그 자체다 또한 비유하자면 책의 찾아보기와 같은 원리로 동작한다.

 

유니크 키의 특성을 정리하자면 아래와 같다.

1. 중복을 허용하지 않는다.
2. NULL 값을 허용하지 않는다.
3. 테이블안에서 여러개의 컬럼에 지정할 수 있다.

 

마찬가지로 보조 인덱스는 위와 같은 UNIQUE 키의 제약조건을 따르게 된다.

 

따라서 CREATE INDEX문은 앞서 말했듯이 보조 인덱스를 생성하는 구문이며 ALTER나 테이블 생성할때 제약조건을 설정하여서 보조 인덱스를 생성 할 수 있다.

B-tree 자료구조

B-tree는 이진탐색트리의 확장 개념이라고 생각할 수 있다.

 

이진 탐색 트리는 자식 노드가 최대 2개까지 가능한 (대소 비교) 구조이다.

 

B-tree는 자식 노드가 2개 이상까지 가능한 Tree 구조이다.

 

이러한 B-tree 구조는 DB와 같은 데이터베이스에서 많이 쓰이며 MYSQL에서는 INDEX와 같은 탐색 부분에서 많이 쓰인다고 할 수 있다.

 

위의 사진은 B-tree의 기본적인 형식이다.

 

데이터 하나하나를 노드라고 하며 노드와 노드를 이어주는 선을 우리는 EDGE라고 한다.

 

파란색으로 색칠되어 있는 부분은 노드의 KEY라고 하고 빨랂색으로 색칠되어 있는 부분은 각 노드의 메모리를 의미 한다.

 

맨 위의 레벨의 노드를 ROOT 노드라고 부르며 그 다음 아래의 있는 노드들을 자식노드라고 한다.

 

B-tree는 따라서 위와 같이 부모노드와 자식노드로 레벨을 구별 할 수있다.

 

이진탐색트리와 같이 데이터 10의 노드의 맨 왼쪽부분은 10보다 작은 데이터, 10과 20의 가운데 메모리는 10보다는 크고 20보다는 작은 데이터 마지막으로 20의 맨 오른쪽 부분은 20보다 큰 데이터로 구성되어있다 또한 B-tree는 leaf 노드 (밑단 노드)의 레벨이 모두 같은 포화 이진트리 형식이라고도 말할 수 있다.

• 노드는 최대 M개 부터 M/2개 까지의 자식을 가질 수 있다.
• 노드에는 최대 M−1개 부터 [M/2]−1개의 키가 포함될 수 있다.
• 노드의 키가 x개라면 자식의 수는 x+1개 이다.

 

B-tree 노드의 특징을 정리한 표다.

 

위의 사진을 보면서 특징을 정리해보겠다.

 

먼저 B-tree의 차수를 구하는 방법은 자식노드의 개수로 차수를 구한다.

 

현재 KEY 10과 20이 들어 있는 ROOT 노드에서 자식노드의 개수는 3개이다.

 

따라서 차수가 3인 B-tree 형식이라고 할 수 있다.

 

차수가 3인 B-tree로 특징을 정리하자면

• 노드는 최대 3개 부터 3/2(2개(반올림하여 계산한다.))개 까지의 자식을 가질 수 있다.
• 노드에는 최대 3개 부터 1개의 키가 포함될 수 있다.
• 노드의 키가 16개라면 자식의 수는 17개 이다.

 

라고 정리 할 수있다. 이제 위의 사진과 조건이 일치한지 비교하자면 일치한다고 말할 수 있다.

 

B-tree에 관한 정리는 이 개념정리 문서와 통일성이 부합되지 않으므로 여기까지만 하고 B-tree관련 정의는 나중에 더 정리하겠다.

클러스터 인덱스의 검색, 삽입 과정

클러스터 노드의 검색과정과 삽입과정을 한번 살펴보겠다. 또한 이것을 기반으로 페이지 분할이라는 개념도 같이 설명하겠다.

 

위에서는 B-tree하나를 노드라고 지칭 했었지만 MYSQL은 하나의 페이지로 정의한다.

 

따라서 루트 페이지(root), 중단 페이지(parent), 밑단 페이지(leaf)로 구성이 되어있다.

 

MYSQL에서 한 페이지의 저장될 수 있는 데이터의 크기는 약 16KB이다 하지만 지금부터는 설명을 위해 한 페이지에 최대 4개의 데이터만 삽입할 수 있다고 가정하겠다.

클러스터 인덱스의 검색 과정

위의 사진을 보면 현재 10개의 데이터를 삽입하고 클러스터 인덱스를 생성했을때의 구조이다.

 

위와 같이 인덱스는 앞서 말한대로 루트페이지와 리프페이지로 구성이 되어있다.

 

현재 리프페이지에서는 삽입한 데이터들의 행이 들어 있다. 따라서 리프 페이지를 데이터 페이지 라고도 부른다.

 

클러스터 인덱스를 사용할때 보면 알 수 있듯이 현재 데이터가 알파벳 순으로 정렬이 되어 있다.

 

이제 한번 LJB 임재범이라는 데이터를 검색해보자.

 

일반 페이지에서 찾을 데이터를 검색한다면 3개의 페이지를 다 탐색해야 한다.

 

하지만 인덱스를 사용하면 현재 LJB에서 L은 S보다는 알파벳순으로는 앞에 있고 K보다는 뒤에 있다 따라서 1001번 위치의 페이지를 탐색한다면 LJB라는 데이터를 쉽게 찾을 수 있다 .

 

현재는 단 2개의 페이지로 루트페이지와 리프페이지만 탐색하여서 빠르게 탐색하였다.

 

현재 데이터가 적어서 3개를 탐색하는 것이나 2개를 탐색하는것이다 차이점이 크게 느껴지지 않는다.

 

하지만 대용량페이지에서 인덱스를 생성할 경우에는 해당 인덱스가 정말 유용할거라는 사실을 알 수 있을것이다.

클러스터 인덱스의 삽입과정과 페이지 분할

현재 페이지 분할과 삽입 과정에 대하여 설명하기 위해 위와 같은 최대 데이터 저장 개수가 4개인 페이지를 생성하였다.

 

이제 III라는 데이터를 한번 삽입해보자.

 

위와 같이 데이터는 정렬되어 있어야 하기 때문에 III가 JJJ보다 앞에 삽입된 것을 볼 수 있다.

 

이처럼 삽입은 데이터를 삽입함과 동시에 위치 탐색과 정렬을 다시 해주어야 한다.

 

따라서 단순한 검색보다는 삽입과정에서는 시스템에 부하가 클 수 있으므로 웬만해서는 클러스터가 적용된 컬럼은 삽입이나, 업데이트가 잘 일어나지 않는 부분에서 사용하는 것이 좋다.

 

만약 이러한 변경작업이 자주 일어나는 컬럼이 클러스터 인덱스가 된다면 시스템의 오히려 많은 부하를 줄 수 있는 악역향이 되버릴 수 있으므로 주의하자.

 

이제 1개의 데이터를 추가로 더 삽입해보자.

 

GGG를 삽입할 경우에는 FFF페이지에서 삽입이 되지만 현재 FFF페이지에는 데이터가 4개로 모두 꽉 채워져 있으므로 페이지 분할이 일어난다.

 

현재 FFF페이지의 3번째 데이터가 루트 페이지로 이동하여 새로운 페이지가 생성되고 데이터를 올바르게 재분배하게 된다.

 

이러한 페이지 분할과 같은 작업 경우에는 항상 M(한 페이지에 저장될 수 있는 데이터의 개수) - 1번째 데이터가 상위 페이지로 이동하여 페이지 분할을 하게 된다.

 

이러한 페이지 분할은 위와 같이 재정렬 함과 동시에 페이지를 전부 다시 분배해주어야 하기 때문에 엄청난 시스템에 부하가 생길 수도 있다.

 

따라서 지금이것을 이해했더라면 왜 데이터의 변경과 삽입이 자주일어나는 컬럼에는 클러스터 인덱스를 생성하는것이 오히려 좋지 않은 것인지 알 수 있을것이다.

 

이처럼 클러스터 인덱스는 데이터가 항상 정렬되므로 페이지가 정렬되어 있는 영어사전과 같다는 말을 한 이유이다.

보조 인덱스의 검색, 삽입 과정

이번에는 보조인덱스의 검색과 삽입 과정에 대하여 설명하겠다.

 

보조인덱스란 앞서 말했듯이 책 뒤쪽에 찾아보기와 같은 개념이라고 설명을 했었다.

보조 인덱스의 검색 과정

보조 인덱스의 경우에는 위와 같이 좀 특이한 부분이 있다.

 

루트 페이지와 리프페이지 그리고 데이터 페이지라는 것이 따로 존재한다.

 

여기서 데이터 페이지는 앞서 말한 클러스터 인덱스의 리프 페이지와 동일한 역할 을 수행한다.

 

현재 리프 페이지의 경우에는 이름과 페이지 번호 + 위치가 하나로 묶인 것이 존재한다.

 

페이지는 앞에서 말한 페이지 번호와 동일하고 데이터에서 찾아갈 위치가 존재한다.

 

보조 인덱스는 그냥 책과 같이 찾아보기와 동일하기 때문에 위치만 표시하고 데이터를 찾아가는 과정이 따로 존재하고 있다.

 

따라서 1001번의 #4는 일반적인 데이터 페이지의 1001번의 4번째 위치에 있다는 것을 확인 할 수있다.

 

따라서 JYP라는 데이터를 찾으려면 리프 100번 위치의 데이터 페이지 1000번에 4번째 위치에 있다는 것을 확인 할 수있다.

 

따라서 루트페이지, 리프페이지, 데이터페이지 총 3개의 페이지를 읽었다.

 

클러스터 인덱스보다는 검색속도가 느리긴 하지만 그래도 전체탐색보다는 대용량의 데이터일 경우에는 유용하다고 할 수 있다.

 

또한 이것으로 인하여 소용량의 테이블에서 탐색하는 과정은 전체 탐색과 비등비등하다고 할 수 있으므로 차라리 전체 탐색을 쓰는게 좋다고 한 이유다 (인덱스는 생성할때 10%의 공간을 차지한다.)

보조 인덱스의 삽입 과정

클러스터 인덱스와 거의 비슷하지만 보조 인덱스의 경우에는 데이터가 정렬되어 있지 않기 때문에 페이지 분할을 해도 그리 상대적으로 클러스터 인덱스에 비하여 시스템에 부하가 걸릴 확률이 적다.

 

따라서 삽입과 변경이 일어나는 구간에서 꼭 인덱스를 써야한다면 클러스터 인덱스보다 보조 인덱스를 사용하길 바란다 하지만 웬만해서는 사용하지 않는 것을 권장한다.

정리

위와 같이 이제 클러스터 인덱스와 보조인덱스에 대하여 알아보았다.

 

원리는 비슷한 지언정 사용하는 성능차이는 서로가 확실하기 때문에 잘 구별해서 사용해야 할 것 같다.

 

정리하자면 장점과 단점을 구별해서 사용하면 확실한 성능을 올릴 수 있다.

 

하지만 구별하지 않고 사용하면 오히려 시스템에 엄청난 부하를 줄 수 있으므로 조심해서 사용해야 하는 것이 바로 인덱스 인것 같다.

 

자세한건 B-tree 자료구조를 더욱 더 자세히 이해하게 된다면 인덱스의 기본 개념은 확실하게 잡을 수 있을것이니 한번 참고해보자.