Database Index

개요

만약 내가 굉장히 두꺼운 책을 가지고 있고 이 책을 보며 공부를 하고있다고 상상해보자.
책의 어떤 부분의 개념이 잘 기억나지 않아서 다시 책을 찾아보려한다. 근데 목차가 없어서 찾아보려는 내용을 찾으려면 책의 맨 앞부터 끝까지 꼼꼼히 보며 찾아야한다면 너무 귀찮지 않을까?

데이터베이스도 마찬가지다. 방대한 데이터 중에서 찾는 것은 데이터베이스에게도 부담스러운 일이다.
따라서 데이터베이스는 일종의 목차를 만들어서 사용하는 전략을 사용한다. 이 것이 바로 인덱스다.

인덱스는 데이터를 빠르게 찾을 수 있도록 도와주는 존재로 실무에서 현실적으로 인덱스 없이 데이터베이스를 운영한다는 것이 불가능하다고한다.

책의 목차에는 제일 앞의 순서 목차가 있고 제일 뒤의 키워드 목차가 있듯이 데이터베이스에도 마찬가지다. 자세한 내용은 뒤의 인덱스의 종류에서 알아보자.

인덱스의 작동 원리

클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어진다. 이는 나무를 거꾸로 표현한 자료구조로 제일 상단의 루트, 중간 줄기를 중간, 끝에 달린 잎을 리프라고 부른다.

균형 트리

균형트리는 데이터를 검색할 때 뛰어난 성능을 기대할 수 있다.
균형 트리 구조에서 데이터가 저장되는 공간을 노드라고 한다.
루트 노드는 가장 상위의 노드를 말하고 모든 시작은 루트 노드에서 시작된다.

이 때 노드는 MySQL에서 페이지라고 부르는데 페이지는 최소한의 저장 단위로 16384바이트 크기를 가진다. 예를 들어 한 건의 데이터만 존재해도 1개의 페이지가 필요하다.

mysql> SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

페이자가 4개 있고 알파벳 데이터가 저장되어있다고 가정해보자.
특정 알파벳을 찾기 위해서는 Full Table Scan을 하는 방법밖에 없다.
첫 페이지에는 A, B, D가 있고 두 번째 페이지에는 F, H, J가 있다. 세 번째 페이지에는 L, M, O가 있다.

여기서 M을 찾는다고 해보자.
첫 페이지 스캔 -> 두 번째 페이지 스캔 -> 세 번째 페이지 스캔
총 3페이지를 검색해서 찾아냈다.

그러나 균형 트리라면 다르다. 한 페이지에 알파벳이 3개씩 들어가고 루트페이지엔 A, F, L이 있다. 여기서 M을 찾고싶을 경우 균형트리에선 루트 페이지를 읽고 그 다음 L페이지로 접근해서 M을 찾으면 된다. 총 스캔한 페이지 수는 2개이다. 데이터가 많아질 수록 이점은 더 커진다.

균형 트리의 페이지 분할

인덱스를 사용할 때 꼭 들어본 말이 변경이 자주 일어나는 테이블에 인덱스를 생성할 경우 인덱스를 만들면 CUD 작업 속도가 더 느려진다는 것인데 그 이유를 알아보자.

이 것은 바로 페이지 분할 때문이다.
페이지 분할이란 새로운 페이지를 준비해서 데이터를 나누는 작업을 말하는데 이 것이 빈번하게 일어날수록 속도가 느려지게 된다.

예를 들어 좀전에 살펴본 페이지 구조를 살펴보자.
I라는 알파벳이 새로 추가된다면 하나의 페이지에 3개의 데이터가 있다가 하나가 추가되었을 뿐이니 단순히 기존의 데이터가 한칸 이동하는 것 외에는 큰 변화가 일어나지 않는다.

그러나 페이지에 남은 공간이 없을 경우 우선 새로운 페이지를 하나 만들어야하고 기존의 페이지를 분할해야한다.
그리고 루트 페이지에 분할된 리프 페이지를 등록하려고 할 때 루트페이지가 꽉차서 더 이상 등록할 수 있는 공간이 없어진다. 그렇다면 루트페이지도 페이지분할을 해야한다.
하나의 데이터를 입력하려 했을 뿐인데 연쇄적으로 페이지 분할이 일어나야한다는 것이다. 이러한 이유로 인덱스를 사용하면 CUR작업의 속도가 느려지게 된다.

인덱스의 종류

클러스터형 인덱스

개요에서 보았듯이 인덱스에도 종류가 있다.
클러스터형 인덱스와 보조 인덱스.

클러스터형 인덱스는 기본키에 자동으로 생성되는 인덱스를 말한다.
보통 기본키는 1,2,3…과 같이 순차적으로 증가하는 값을 사용하는데 이렇게 기본키로 지정된 열에 대해 자동으로 인덱스가 설정된다는 것이다.

한 테이블의 기본키는 하나이기에 클러스터형 인덱스는 하나만 존재한다.
이 클러스터형 인덱스는 하나의 특징이 있다.
그것은 바로 클러스터형 인덱스를 기준으로 데이터가 정렬된다는 것이다.

예를 들어 순차적으로 증가하는 속성을 기본키로 정하면 1부터 시작해서 1씩 증가하는 순으로 정렬되게된다. 그리고 email을 기본키로 정하면 알파벳순으로 정렬된다.

보조 인덱스

우리가 주로 기술 서적을 읽을 때 책의 제일 뒷 부분을 펼쳐서 내가 궁금한 개념을 찾아보곤 한다. 보조 인덱스도 마찬가지다.
테이블에서 우리가 찾고 싶은 데이터를 빠르게 찾을 수 있게 데이터를 정리해 놓은 것이라고 할 수 있다.

보조인덱스는 클러스터형 인덱스와 다르게 하나의 테이블에 여러 개 설정할 수 있다.
보조 인덱스를 기준으로 자동으로 정렬되지는 않는데 제일 뒤에 찾아보기 페이지가 생겼다고 본문의 순서가 변경되거나 하지 않는 것을 떠올리며 이해하면 좋다.
책의 목차 구성은 그대로 있고 뒤의 찾아보기 페이지만 키워드의 알파벳 순으로 정렬되는 것과 같다.

장단점

인덱스를 사용하면 데이터를 전체 스캔할 필요 없이 빠르게 찾을 수 있다는 걸 알았다. 그러나 인덱스는 항상 좋기만할까?

아니다.

책의 찾아보기를 예로 들었을 경우 굉장히 빈번하게 등장하는 키워드에 대해 전부 페이지를 적어놨다고 해보자.
책의 찾아보기에서 페이지를 보고 앞으로 넘어가 내가 원하는 내용이 있는지 보고 없다면 다시 맨 뒷장으로 가서 페이지를 확인하고 다시 앞으로 돌아와서….

위의 과정을 반복해야할 것이다. 이럴 경우 차라리 처음부터 찾는게 효율적일지도 모른다. 그리고 인덱스도 일종의 테이블이기 때문에 인덱스를 생성하면 전체 저장공간의 약 10%를 차지한다고 한다.

즉, 인덱스를 사용하기 알맞는 경우와 그렇지 않은 경우가 있다는 말이다.

그래서 장단점을 한 번 정리해보자면

• 장점
  • 검색속도가 향상된다.
  • 컴퓨터의 부담이 줄어들어 전체 시스템의 성능이 향상된다.
• 단점
  • 인덱스가 시스템의 저장공간을 필요로한다.
  • 잘못이해하고 사용하면 성능이 나빠진다.

주의점

• 중복된 데이터가 많은 열에 인덱스 생성을 지양해야한다. 만일 테이블에서 유저의 성별을 관리하고 있다면 F/M 둘중 하나의 값을 가질 것이다. 유저가 100만명이라면 F가 50만명, M이 50만명이 될 것이다. 이러한 상황에서 검색을 한다면 50만건의 데이터가 나올 것이다. 인덱스를 참고하려 왔다 갔다 해야하기 때문에 오히려 성능이 더 나빠진다.

• Where절에서 사용되는 열에 인덱스 생성. SELECT문을 사용할 때 where절의 조건에 해당 열이 나와야 인덱스를 사용한다.