[MySQL] 인덱스(Index)란? - 인덱스 생성 방법과 주의 사항

1. 인덱스란 무엇인가

인덱스(Index)란 데이터베이스에 저장된 데이터를 빠르고 효율적으로 찾고자 만들어진 데이터 구조입니다.

 

인덱스를 직관적으로 이해하기 가장 쉬운 방법은 책을 예로 드는 것입니다.

예를 들어 천만건의 데이터로 구성된 책이 하나 있고 그 안에서 특정 단어를 찾아야한다면 어떻게 해야할까요

책의 첫 장부터 훑어서 찾아보는 방법도 있겠지만 책의 두께가 두꺼워질 수록 검색할 분량이 기하급수적으로

늘어나면서 검색 속도가 급격히 느려질 것입니다. 그래서 아마 대부분의 사람들은 책 맨 뒷장에 <찾아보기>를

볼 것입니다.

 

가나다 순서로 정렬된 <찾아보기>는 그 옆에 단어가 위치한 페이지 번호가 적혀있어서 그 페이지를 펼치면

원하는 데이터를 바로 찾을 수 있을 것입니다. '폭포수' 라는 단어는 101페이지에 있고 '프로그래밍' 이라는 단어는

762 페이지에 있다는 식으로 구성이 됩니다.

 

이처럼 정렬된 각 키워드가 어느 곳에 있는지를 인덱스라는 이름으로 구성하면, 키워드 검색으로 원하는 데이터를

빠르게 찾을 수 있습니다. 이러한 방식을 DBMS에 차용한 게 바로 인덱스(Index)입니다.

 

---

 

2. 인덱스와 B-tree(Balanced Tree, 균형 트리)

2-1. 구조와 특징

B-tree 자료구조는 DBMS에서 인덱싱 알고리즘으로 주로 사용되며, 또 처음으로 사용된 자료구조입니다.

 

B-tree 자료구조는 이진트리의 발전된 형태의 자료구조이며 모든 리프 노드가 같은 레벨에 있는걸 보장합니다.

때문에 B-tree는 노드가 새로 추가되거나 삭제될 경우 동적으로 노드를 분할하거나 통합하여 항상 균형 상태를 유지합니다. 그 외에도 B-tree에 단점을 보완한 B+tree, B*tree 도 있습니다. 하지만 대부분의 인덱스는 B-tree 자료구조로 표현이 됩니다.

 

B-tree는 루트(Root) 노드, 브랜치(Branch) 노드, 리프(Leaf) 노드로 구성되며, 

리프 노드가 모두 같은 레벨에 존재하는 균형트리입니다. 가장 상위에 있는 노드를 루트 노드라하며, 가장 하위에 있는 노드들을 리프 노드라고 합니다. 그리고 그 중간에 있는 노드들을 브랜치 노드라고 부릅니다.

 

 

2-2. 데이터 검색 과정

B-tree에 각 노드는 키값과 포인터를 가집니다. 키값은 오름차순으로 저장되어 있으며, 키값 좌우에 있는 포인터는

각각 키값보다 작은 값과 큰 값을 가진 다음 노드를 가리킵니다. 따라서 키 값을 비교하여 다음 단계의 노드를 쉽게 찾을 수 있습니다. 

 

B-tree에서 데이터를 검색하는 과정을 살펴보겠습니다. 3이라는 값을 찾는다고 가정하면

우선 루트 노드의 값 10과 찾는 값 3을 비교합니다. 3은 10보다 작으므로 왼쪽 포인터가 가리키는 노드로 이동합니다.

이동한 노드에서 다시 5와 3을 비교합니다. 다시 3은 5보다 작으니 왼쪽 포인터가 가리키는 노드로 이동합니다.

3이 저장된 노드와 비교하여 값이 일치하면 검색을 중지합니다.

 

 

---

 

3. 인덱스 종류 (클러스터형 인덱스와 보조 인덱스)

MySQL에서 사용하는 인덱스의 종류는 크게 두 가지로 나뉘는데,

클러스터형 인덱스(Clustered Index)와 보조 인덱스(Secondary Index)입니다. 

다른 DBMS에서는 클러스터형 인덱스(Clustered Index)와 비클러스트형(Non Clustered Index) 나누기도 한다. 하지만 거의 비슷한 개념이다.

 

클러스터형 인덱스와 보조 인덱스의 가장 큰 차이는 속도와 리프 노드에 존재하는 데이터입니다.

속도 측면에서 보면 대부분의 경우 클러스트형 인덱스가 보조 인덱스보다 검색 속도가 더 빠릅니다. 일부 예외 상황도 있곘지만 클러스터형 인덱스가 더 빠르다고 생각해도 무리가 없습니다.

 

또한 보조 인덱스 같은 경우에는 리프 노드에 RID라는 데이터의 저장 위치가 저장됩니다.

그래서 그 위치를 참조하여 실제 데이터가 위치한 주소를 알 수가 있습니다.

하지만 클러스터형 인덱스는 리프 노드에 테이블 자체가 저장되기 때문에 RID를 가지고 있을 필요가 없습니다.

 

클러스터형 인덱스는 테이블당 한 개만 생성할 수 있고, 보조 인덱스는 테이블당 여러 개를 생성할 수 있습니다.
또 클러스트형 인덱스는 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬합니다.

 

3-1. 클러스터 인덱스

테이블에 기본키가 존재한다면 그 열에는 자동으로 클러스터형 인덱스가 생성됩니다.

기본키는 테이블당 1개만 존재할 수 있는 것처럼 클러스터형 인덱스도 테이블에 딱 1개만 존재할 수 있습니다.

 

테이블에 기본키가 있다면 그 테이블은 기본키를 기준으로 정렬이 된다는 사실을 알고계실겁니다.

그 이유는 기본키 열이 클러스터 인덱스로 생성이 되기 때문에 데이터가 입력되는 즉시 기본키열로

데이터를 정렬하기 때문입니다.

 

만약 테이블에 기본키가 없다면 UNIQUE 속성이면서 NOT NULL속성인 열이 클러스터형 인덱스로 지정됩니다.

 

클러스터 인덱스를 활용한 검색 과정

클러스터 인덱스는 리프 노드에 테이블 자체가 저장되기 때문에 특별하게 해당 열을 찾기 위한 RID를 가지고 있을 필요가 없습니다.

예를 들어 도서번호 6번을 찾을 경우 루트 노드에서 비교하여 두 번째 행의 4보다 크고 세 번째 행의 7보다 작은 것을 발견하고 이에 따라 Page 3으로 이동하여 첫 번째 행부터 순서대로 데이터를 찾게됩니다.

 

또한 위에 보시는 것과 같이 데이터가 이미 정렬이 되어있는 상태기 때문에 검색 속도가 매우 빠릅니다.

 

 

3-2. 보조 인덱스

클러스터형 인덱스가 아닌 인덱스는 모두가 보조 인덱스입니다.

UNIQUE 속성을 가진 열이나, 외래키 열은 자동으로 보조 인덱스가 생성이됩니다.

아니면 인덱스 생성 명령어를 통해서도 보조 인덱스를 생성할 수 있습니다.

 

보조 인덱스는 테이블에 여러 개 생성할 수 있지만 함부로 남용할 경우 오히려 성능을 떨어뜨리는

결과를 초래할 수 있으므로 꼭 필요한 열에만 생성하는 것이 좋습니다.

 

 

보조 인덱스를 활용한 검색 과정

 

보조 인덱스는 클러스터형 인덱스와 달리 리프 노드에 실제 데이터값이 아닌 테이블에 데이터가 위치한

행 번호인 RID를 가지고 있습니다. RID는 '페이지 번호-페이지 내 행의 위치'의 형태로 구성되어 있습니다.

 

도서번호 9번을 찾는 경우를 살펴보면, 먼저 루트 노드를 통해 리프 노드의 두 번째 페이지를 찾아 간 후

찾고자 하는 값인 8의 RID가 4-2임을 확인합니다. 4-2는 네 번째 페이지의 두 번째 행을 의미하므로

테이블의 네 번째 페이지에 접근하여 원하는 데이터를 가져옵니다.

 

그리고 위 테이블을 살펴보면 저장된 값이 정렬되어 있지 않고 무작위로 저장되어 있는 것을 알 수 있습니다.

그 이유는 보조 인덱스는 데이터 삽입시 데이터 페이지를 정렬하는 것이 아니라 그냥 데이터 페이지의 뒤쪽

빈 부분에 삽입하기 때문입니다.

 

삽입할 때 정렬을 고려하지 않기 때문에 데이터 입력에서는 클러스터 인덱스보다 부하를 덜 주게 됩니다.

 

 

클러스터형 인덱스와 보조 인덱스 특징 정리

다음은 클러스터형 인덱스의 특징입니다.

• 클러스터형 인덱스의 생성 시에는 데이터 페이지 전체가 다시 정렬됩니다. 그러므로 이미 대용량의 데이터가 입력된 상태라면 업무시간에 클러스터형 인덱스를 생성하는 것은 심각한 시스템 부하를 줄 수 있으므로 신중하게 생각해야합니다.
• 클러스터형 인덱스는 인덱스 자체의 리프 노드에는 데이터가 위치한 주소가 아니라 테이블 자체가 있습니다.
• 클러스터형 인덱스는 보조 인덱스보다 검색 속도는 더 빠릅니다. 하지만 데이터의 입력/수정/삭제는 더 느립니다.
• 클러스터형 인덱스는 성능이 좋지만 테이블에 한 개만 생성할 수 있습니다. 그러므로 어느 열에 클러스터형 인덱스를 생성하는지에 따라서 시스템의 성능이 달라질 수 있습니다.

다음은 보조 인덱스의 특징입니다.

• 보조 인덱스의 생성 시에는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성합니다.
• 보조 인덱스의 인덱스 자체의 리프 노드에는 데이터가 위치한 주소인 RID가 저장됩니다.
• 보조 인덱스는 클러스터형 인덱스보다 검색 속도는 느리지만 입력/수정/삭제는 더 빠릅니다.
• 보조 인덱스는 여러 개 생성할 수 있지만 함부로 남용할 경우에는 오히려 시스템 성능을 떨어뜨립니다.

 

---

 

4. 인덱스의 생성  / 삭제 / 조회

4-1. 인덱스 생성

기본 인덱스 생성

CREATE INDEX 인덱스이름 ON 테이블이름 (열 이름 [ASC | DESC])
CREATE INDEX file_index ON file (created_at DESC); //내림차순 정렬, 기본값이 ASC

ALTER TABLE 테이블이름 ADD INDEX 인덱스 이름 (열 이름 [ASC | DESC] )
ALTER TABLE articles ADD INDEX articles_index (created_at)

UNIQUE 인덱스 생성

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 인덱스이름 ON 테이블이름 (열 이름 [ASC | DESC])
CREATE UNIQUE INDEX file_index ON file (created_at);

인덱스 생성 시 인덱싱 알고리즘 선택

CREATE INDEX 인덱스이름 [index_type] ON 테이블이름 (열 이름 [ASC | DESC])

CREATE INDEX file_index USING BTREE ON file (created_at ASC); //b-tree 선택
CREATE INDEX file_index USING HASH ON file (created_at ASC); //hasg index 선택

인덱스 생성시 고려사항

• 인덱스는 WHERE 절에 자주 사용되는 속성이어야 한다.
• 인덱스는 조인에 자주 사용되는 속성이어야 한다.
• 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다.(테이블당 네다섯 개 정도 권장)
• 속성이 가공되는 경우 인덱스는 사용되지 않는다.(컬럼을 함수(SUM, AVG)에 감싸서 가공을 하는 등)
• 속성의 선택도가 높은 경우에는 오히려 인덱스를 지정하지 않는게 좋다.

 

 

 

4-2. 인덱스 삭제

DROP INDEX 인덱스이름 ON 테이븡이름;
DROP INDEX file_index ON file;

인덱스 삭제 시 고려사항

• 기본키를 삭제하면 클러스터형 인덱스도 같이 삭제됩니다.
• 인덱스를 제거할 때는 되도록 보조인덱스부터 제거합니다. 만약 클러스터형 인덱스부터 제거된다면 보조인덱스의 RID값이 모두 재구성되어야합니다. 그런데 어처피 보조인덱스를 삭제할 예정이라면 굳이 재구성 비용을 들일 필요없이 보조인덱스부터 삭제하는게 효율적입니다.
• 인덱스 활용도가 떨어진다면 과감하게 삭제해줄 필요가 있습니다. 한 달에 한 번 또는 일 년에 한 번 WHERE절 조건문에 들어가는 컬럼이 인덱스를 설정할 필요가 있을까요. 계속 유지할 필요가 없는 인덱스는 삭제하는게 성능에 유리합니다.

 

 

 

4-3. 인덱스 조회

SHOW INDEX FROM 테이블이름;
SHOW INDEX FROM member;

테이블에 생성되있는 전체 인덱스 정보가 나타납니다.

 

• 'Key_name' 열에서 PRIMARY KEY는 클러스터형 인덱스를 의미합니다.
• 'Non_unique' 열은 해당 인덱스가 UNIQUE 속성인지 판별해줍니다. 위 사진에서는 Non_unique가 0으로 표시가 되는데 이 의미는 해당 인덱스가 UNIQUE 하다는 것을 의미합니다.
• 'index_type' 열은 인덱싱 알고리즘이 무엇인지 알려줍니다. 현재는 BTREE로 설정되어있습니다.

 

 

 

이상으로 인덱스에 대한 설명을 마치겠습니다. 감사합니다.