Index
인덱스는 SELECT를 사용해서 테이블을 조회할 때 결과를 빠르게 추출하도록 도와주는 기능이다. 즉, 인덱스는 데이터를 빠르게 찾을 수 있도록 도와주는 도구이다.
인덱스의 종류
클러스터형 인덱스(Clustered Index)
클러스터형 인덱스는 기본 키(primary key)로 지정하면 자동으로 생성되며 테이블에 1개만 만들 수 있다. 또한 기본 키로 지정한 열을 기준으로 자동 정렬된다. 클러스터형 인덱스는 영어사전처럼 책의 내용이 이미 알파벳 순서대로 정렬되어 있는 것과 같다. 따라서 별도의 찾아보기가 필요 없고 책 자체가 찾아보기 역할을 같이 한다.
보조 인덱스(Secondary Index)
보조 인덱스에는 중복을 허용하지 않는 고유 보조 인덱스와 중복을 허용하는 단순 보조 인덱스가 있다. 또한 보조 인덱스는 자동 정렬되지 않는다.
고유 보조 인덱스는 고유 키(unique key)로 지정하면 자동 생성할 수 있고, CREATE을 사용하여 직접 고유 보조 인덱스를 생성할 수도 있다. 또한 CREATE을 사용하여 중복이 허용되는 단순 보조 인덱스도 직접 생성할 수 있다. 보조 인덱스는 책의 뒤편에 찾아보기가 있는 일반적인 책과 같은 역할을 한다.
인덱스의 내부 작동 원리
클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리(B+tree)로 만들어진다. 균형 트리 구조에서 데이터가 저장되는 공간을 노드라고 하는데, MySQL에서는 이를 '페이지'라고 부른다. 페이지는 최소한의 저장 단위로, 16Kbyte(16384byte)의 크기를 가진다. 즉, 균형 트리에 데이터를 1개만 입력해도 페이지 하나(16Kbyte)의 공간이 필요하다.
그렇다면 왜 균형 트리가 검색에 효과적일까?
만약 전체 테이블 검색(full table scan) 방식으로 MMM을 찾는다면 아래와 같은 과정을 거칠 것이다.
총 3개의 페이지(8건의 데이터)를 탐색해야 MMM을 찾을 수 있다.
반면에 균형 트리에서 MMM을 찾는다면 아래와 같은 과정을 거칠 것이다. 균형 트리는 무조건 루트 페이지부터 검색하고, 데이터가 모두 정렬되어 있으므로 MMM이 속해있는 리프 페이지인 3번째 리프 페이지로 바로 이동이 가능하다.
총 2개의 페이지(5건의 데이터)를 탐색하면 MMM을 찾을 수 있다. 이와 같은 이유로 균형 트리를 이용하면 검색의 효율을 높일 수 있다.
균형 트리의 페이지 분할
균형 트리를 이용하면 검색의 효율을 높일 수 있다. 즉, SELECT의 속도를 향상시킬 수 있다. 하지만 인덱스를 균형 트리로 구성하면 데이터 변경 작업(INSERT, UPDATE, DELETE) 시 성능이 나빠지는데, 이는 페이지 분할이라는 작업이 발생하기 때문이다. 페이지 분할이란 새로운 페이지를 준비해서 데이터를 나누는 작업을 말한다. 페이지 분할이 자주 일어나면 성능에 큰 영향을 줄 수 있다.
만약 위 데이터에서 III와 GGG가 추가되었을 때 페이지 분할 과정은 아래와 같다.
- JJJ가 두번째 리프 페이지의 4번 자리로 이동하고 III가 3번 자리에 삽입될 것이다.
- GGG가 두번째 리프 페이지에 추가되어야 하는데, 공간이 부족하여 하나의 페이지를 새로 생성한다.
- 두번째 리프 페이지와 새로 생성된 페이지에 데이터를 적당히 나눈다.
- 새로 생성된 페이지를 루트 페이지에 등록한다.
이와 같은 과정이 자주 반복된다면, 루트 페이지에서도 페이지 분할이 일어날 것이고 중간 페이지가 생성될 수 있다. 이 과정 자체가 매우 비효율적이기 때문에 균형 트리 형태의 인덱스 구성에서 데이터 변경 작업 시 성능을 매우 저하시킬 수 있다.
클러스터형 인덱스 구조
클러스터형 인덱스의 구조는 위에서 언급한 균형 트리의 형태와 같다. 클러스터형 인덱스에서는 리프 페이지가 곧 데이터 페이지가 된다(데이터 자동 정렬o).
보조 인덱스 구조
보조 인덱스는 인덱스 페이지와 데이터 페이지가 분리되어 있는 구조를 가진다(데이터 자동 정렬x). 인덱스 페이지는 정렬이 되어 있고 데이터 페이지는 정렬이 안되어 있는데, 이는 '책 뒤편에 찾아보기'가 알파벳 순서로 정렬되어 있는 것과 같은 역할이라 생각하면 된다. 우선 인덱스 페이지의 리프 페이지에 인덱스로 구성한 열을 정렬하고 실제 데이터가 있는 위치를 저장한다. 데이터의 위치는 '페이지 번호 + #위치'로 기록되어 있다.
데이터를 검색할 때 hash table의 시간복잡도는 O(1)이고 B+tree는 O(logn)으로 더 느린데, 왜 index는 hash table이 아니라 B+tree로 구현되나?
Hash table을 사용하면 하나의 데이터를 탐색하는 시간은 O(1)으로 B+tree보다 빠르지만, 값이 정렬되어 있지 않기 때문에 부등호를 사용하는 query에 대해서는 매우 비효율적이게 되어 데이터를 정렬해서 저장하는 B+tree를 사용한다. hash index는 빠른 데이터 검색이 필요할 때 유용하다. 하지만 hash index는 등호 연산에만 특화되었기 때문에 index로써 사용되는 경우는 매우 드물다. 데이터가 조금이라도 달라지만 hash function이 완전히 다른 hash 값을 생성하는데, 이런 특성 때문에 부등호 연산을 사용하는 DB 검색에는 적합하지 않다. 반면에 B+tree는 항상 정렬된 상태를 유지하여 부등호 연산에 유리하다.
인덱스의 장점과 단점
장점
- SELECT 문으로 데이터를 검색하는 속도가 매우 빨라진다.
- 그 결과 컴퓨터의 부담이 줄어서 전체 시스템의 성능이 향상된다.
단점
- 인덱스도 공간을 차지하기 때문에, 데이터베이스 내에 추가적인 공간이 필요하다. (상황에 따라 다르겠지만) 인덱스는 대략 테이블 크기의 10% 정도의 공간이 추가로 필요하다.
- 처음에 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.
- SELECT가 아닌 데이터의 변경 작업(INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능이 나빠질 수도 있다.
- 필요 없는 인덱스를 만드는 경우, 데이터베이스 쓸데 없이 공간이 사용되고 인덱스를 이용해서 데이터를 검색하는 과정이 매우 느려질 수 있다.
따라서, 데이터의 변경 작업이 빈번하게 발생하지 않는 상황에서 꼭 필요한 인덱스만 만드는 것이 중요하다.
인덱스를 효과적으로 사용하는 방법
하나의 열에 하나의 인덱스를 생성하는 것이 가장 일반적이다.
하나의 열에 2개 이상의 인덱스를 만들 수도 있고 2개 이상의 열을 묶어서 하나의 인덱스로 만들 수도 있지만, 이런 경우는 매우 드물다.
WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
SELECT 문을 사용할 때, WHERE 절의 조건에 해당 열이 나와야 인덱스를 사용한다.
SELECT mem_id, mem_name, mem_number, addr
FROM member
WHERE mem_name = '소녀시대';만약 위의 SQL을 사용한다고 가정하면, mem_id, mem_number, addr 열에는 인덱스를 생성해도 전혀 사용되지 않는다. 따라서 mem_name 열 외에 다른 열에 인덱스를 만드는 것으 공간 낭비가 된다.
WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
만약 mem_name 열에 인덱스를 사용해서 검색 효율이 상당히 좋아진다고 하더라도, 검색 작업의 빈도가 매우 낮고 INSERT, UPDATE, DELETE 작업의 빈도가 높다면 오히려 전체적인 성능이 나빠지는 결과를 만들어낼 것이다.
데이터의 중복이 많은 열은 인덱스를 만들어도 의미가 없다.
인덱스 열에 들어 있는 데이터 종류가 적으면 인덱스가 큰 효과를 내지 못한다.
SELECT * FROM 회원_테이블 WHERE 성별 = 'M';위와 같이 중복이 많은 열을 인덱스로 만들 경우 의미가 없을 뿐더러, 오히려 성능을 저하시킨다.
클러스터형 인덱스는 하나만 생성할 수 있다.
클러스터형 인덱스는 데이터 페이지를 읽는 수가 보조 인덱스보다 적기 때문에 성능이 더 우수하다. 따라서 하나밖에 지정하지 못하는 클러스터형 인덱스(기본 키)는 조회할 때 가장 많이 사용되는 열에 지정하는 것이 효과적이다.
사용하지 않는 인덱스는 제거한다.
실제로 사용되는 SQL을 분석하여 WHERE 조건에서 사용되지 않는 열의 인덱스는 제거할 필요가 있다. 그럼 공간을 확보할 뿐만 아니라, 데이터 입력 시 발생되는 부하도 많이 줄일 수 있다.
