인덱스

 

 

보통 인덱스에 대해 설명할 때 책에 있는 찾아보기로 비유를 많이 한다

 

찾아보기를 사용해서 해당 키워드가 책의 어느 부분에 있는지 빠르게 찾아낼 수 있으나 찾아보기가 책의 내용에 비해 너무 많다면, 해당 키워드가 너무나도 많이 분포되어 있다면 매우 비효율적이게 된다

 

인덱스도 마찬가지이다

인덱스를 사용해서 얻을 수 있는 장점은

1. 검색 속도가 빨라질 수 있다

2. 해당 쿼리의 부하가 줄어들어 시스템 전체의 성능이 향상될 수 있다

 

물론 단점도 있다

1. 인덱스는 데이터베이스 크기의 10% 정도의 추가공간을 필요로 한다

2. 처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다

3. 데이터의 변경 작업(Insert, Update, Delete) 이 자주 일어나는 경우 성능이 나빠질 수 있다

 

MySQL 은 인덱스를 사용하는 것이 빠를지 아니면 전체 테이블을 검색하는 것이 빠를지 알아서 판단한다

 

인덱스는 클러스터형 인덱스(Clustered Index)와 보조 인덱스(Secondary Index)로 구분할 수 있다

 

B-Tree (Balanced Tree, 균형 트리)

인덱스를 구현할 때 기본적으로 B-Tree 구조를 사용한다

 

노드가 페이지(16Kbyte 저장단위)로 표현되는데 이런 B-Tree 구조를 사용하기 때문에 인덱스를 잘 생성하면 검색 성능이 향상될 수 있다

 

반대로, 데이터 변경 작업 중에서 특히 Insert 작업이 매우 느려질 수 있다

 

원인은 페이지 분할이다

 

페이지에 빈칸이 없다면 페이지 분할이 발생하는데, 그로 인해 하나의 데이터를 넣는다 하더라도 여러번의 페이지 할당과 여러번의 페이지 분할이 발생하게 될 수 있다는 것이다

 

클러스터형 인덱스와 보조 인덱스의 경우에 따라 성능의 차이도 존재한다

역시 이러한 구조로 인해 발생하는 것인데 우선 인덱스의 종류에 따른 차이를 알아보자

 

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

클러스터형 인덱스

클러스터형 인덱스는 테이블당 한 개만 생성할 수 있다

테이블 생성 시에 PK 혹은 Unique NotNull 로 지정하게 된다면 클러스터형 인덱스가 생성되게 된다

물론 PK와 Unique NotNull 이 함께 있다면 PK 가 우선으로 하여 생성된다

 

인덱스는 PK 가 지정한 열로 데이터가 오름차순 정렬된다

 

클러스터형 인덱스의 생성 시에는 데이터 페이지 전체가 다시 정렬된다

-> 이미 대용량 데이터 입력되어 있다면 클러스터형 인덱스 생성 시 심각한 시스템 부하를 줄 수 있음

 

보조 인덱스

보조 인덱스는 테이블 당 여러개를 생성할 수 있다

 

Unique 로 지정한 열은 보조 인덱스가 생성된다

 

보조 인덱스의 생성 시 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다

 

 

 

 

클러스터형 인덱스는 보조 인덱스보다 검색 속도가 더 빠르다

 

 

위에 나온 B-Tree 의 페이지도 두가지로 나뉜다

인덱스 페이지와 데이터 페이지이다

 

보조 인덱스는 데이터 페이지를 건드리지 않고, 별도의 장소에 인덱스 페이지를 생성한다

인덱스 페이지는 데이터 위치 포인터를 가지고 있다(주소값, 데이터 위치를 가리킴)

 

클러스터형 인덱스의 경우 데이터페이지를 사용하기에 범위 검색 시 성능이 우수하다

 

따라서 보조 인덱스는 클러스터형 인덱스에 비해 검색을 할 때 몇가지 과정을 더 거치게 되므로 일반적으로 클러스터형 인덱스가 보조 인덱스보다 검색에 있어 더 빠르다

 

하지만 입력에 있어서는 다르다

 

대용량 테이블일 경우에 Insert 작업이 대개는 클러스터형 인덱스가 더 시스템 부하가 많이 생긴다

 

 

인덱스는 어떤 상황에서 생성해야 하는가

인덱스 생성의 절대 기준이 있지는 않다

테이블 데이터 구성과 어떤 조회를 많이 사용하는가에 따라 인덱스를 생성해야 한다

 

Where 절에서 자주 사용되는 열에 인덱스를 만들어야 한다

 

테이블 조회 시에 인덱스를 사용하는 경우는 where 절의 조건에 해당 열이 나오는 경우에만 주로 사용된다

하지만 where 절의 조건에 사용된다하더라도 조회보다 insert 가 더 자주 사용된다면, 하필 클러스터형 인덱스라면 성능이 나빠질 수 있다

 

데이터의 중복이 높은 경우 효과가 적다

-> enum 타입이라던지 데이터의 중복이 많은 경우에는 인덱스를 만들어도 별 효과가 없다

 

 

Join 에 자주 사용되는 열에 생성하는 것이 좋다

 

 

데이터 변경이 얼마나 자주 일어나는지 고려해야 한다

-> 데이터 변경이 자주 일어나게 된다면 인덱스로 인해 성능이 저하될 수 있다