고성능을 위한 인덱싱 (1)

개요

 

인덱스란, 스토리지 엔진이 행을 빠르게 찾기 위해 사용하는 데이터 구조이다. 인덱스는 우수한 성능을 위해 매우 중요하며, 데이터가 커질수록 더더욱 중요하다. 잘못된 인덱싱의 사용은 실제 성능 문제의 주요 원인이 되기도 한다. 

인덱스 최적화는 쿼리 성능을 향상시키는 가장 강력한 방법이다. 인덱스는 성능을 수십 배나 향상시킬 수 있으며 최적의 인덱스는 때때로 단순히 양호한 인덱스보다 성능을 2배 정도 향상 시킬 수 있다. 

 

인덱싱 기본

 

MySQL에서 인덱스가 어떻게 작동하는지 이해하는 가장 쉬운 방법은 책의 색인을 떠올리는 것이다. 책에서 특정 주제에 대해 찾고 싶다면, 색인을 펴서 주제를 논하는 페이지 번호를 찾고, 해당 페이지로 이동하면 된다.

MySQL 스토리지 엔진은 비슷한 방식으로 인덱스를 사용한다. 인덱스의 데이터 구조에서 값을 검색한다. 일치하는 항목이 있을 경우, 그 항목이 포함된 행으로 이동한다.

인덱스는 테이블에 하나 이상의 열(column)을 포함한다. 하나 이상의 열을 인덱싱하는 경우, MySQL은 인덱스의 맨 왼쪽 접두사에서만 효과적으로 작동한다. 

 

인덱스 유형

 

인덱스에는 여러 가지 유형이 있으며, 각각 다른 용도에 적합하게 설계되었다. 인덱스는 서버 계층이 아닌 스토리지 엔진 계층에서 구현된다. 따라서 인덱싱은 각 엔진에 따라 조금씩 다르게 작동하며, 모든 엔진이 모든 유형의 인덱스를 지원하는 것은 아니다. 이 장에서는 InnoDB 엔진을 모든 테이블의 엔진으로 가정하고, MySQL이 지원하는 가장 일반적인 인덱스 유형에 대해 설명하고자 한다.

 

 

1. B-Tree 인덱스

 

MySQL 의 가장 대표적인 인덱스 유형으로, 대부분의 스토리지 엔진이 이 유형의 인덱스를 지원한다.

MySQL에서 사용하는 CREATE TABLE과 같은 쿼리 내에서 인덱스에는 B-Tree 용어를 사용하곤 하는데, 사실 저장소 엔진에서는 내부적으로 다른 저장소 구조를 사용할 수 있다. 가령, InnoDB는 B+트리를 사용하고, NDB Cluster 스토리지 엔진은 T-Tree 데이터 구조를 사용한다. 

 

B-Tree의 일반적인 개념은 모든 값이 순서대로 저장되며, 각 리프 페이지는 루트로부터 동일한 거리에 있다는 것이다. 아래 그림은 이 개념을 추상적으로 나타낸다.

 

B-Tree 기반 인덱스 구조 ( 기술적으로는 B+Tree )

 

B-Tree는 스토리지 엔진이 원하는 데이터를 찾기 위해 전체 테이블을 스캔할 필요가 없기 때문에 데이터 액세스 속도를 향상시킨다. B-Tree의 루트 노드의 슬롯에는 하위 노드에 대한 포인터가 있으며 스토리지 엔진은 이러한 포인터를 따른다. 하위 노드의 상한과 하한을 정의하는 노드 페이지의 값을 확인하여 올바른 포인터를 찾으며, 이 과정을 거치며 스토리지 엔진은 원하는 값이 존재하지 않음을 확인하거나, 리프 페이지에 성공적으로 도달한다.

리프 페이지는 다른 페이지에 대한 포인터 대신 인덱싱된 데이터에 대한 포인터를 가지고 있기에 특별하다. 위 그림에서는 하나의 노드 페이지와 해당 리프 페이지만 보여주지만, 사실 루트와 리프 사이에는 다양한 수준의 노드 페이지가 존재할 수 있고, 테이블의 크기가 커질수록 트리의 깊이가 깊어진다.

 

B-Tree는 인덱싱된 열을 순서대로 저장하므로 데이터 범위를 검색하는 데 유용하다. 가령, 텍스트 기반 인덱스를 조회할 때는 "A~C로 시작하는 문자열"을 찾는 것이 효율적인 셈이다.

 

B-Tree 구조로 저장되는 데이터의 예시

 

B-Tree 인덱스는 전체 키 값, 키 범위 또는 키 접두사로 조회하는데 적합하며, 조회가 인덱스의 가장 왼쪽 접두사를 사용하는 경우에만 유용하다. 가령, 아래와 같은 쿼리 상황을 살펴보자.

 

-- B-Tree 인덱스가 작동한다.
SELECT * FROM user WHERE name = 'seungo';

-- B-Tree 인덱스가 작동한다.
SELECT * FROM product WHERE name LIKE 'seung%';

-- B-Tree 인덱스가 작동하지 않는다.
SELECT * FROM product WHERE name LIKE '%eung%';

 

첫 번째 쿼리의 경우 전체 값이 일치하는지, 두 번째 쿼리의 경우 왼쪽 접두사가 일치하는지 확인한다. 만약 user 테이블 내 name 열에 인덱싱이 이루어져 있다면 첫 번째와 두 번째의 쿼리는 인덱스를 사용하여 빠른 조회가 가능하지만 세 번째 쿼리는 가장 왼쪽에서부터 조건을 찾을 수 없기에 인덱스를 사용할 수 없는 쿼리의 성능이 낮아진다. 

 

또한, 복합 인덱스가 생성된 테이블에서는 인덱스가 생성된 순서가 쿼리의 성능을 결정하기도 한다. user 테이블에 name 과  birth 순으로 복합 인덱스가 구성되어 있을 때, 아래와 같이 순서대로 조회하는 경우에만 인덱스 활용이 가능하다.

 

-- 복합 인덱스가 적용된다.
SELECT * FROM user WHERE name = 'seungo' AND birth >= '1998-01-01';

-- 복합 인덱스가 적용되지 않는다.
SELECT * FROM user WHERE birth >= '1998-01-01' AND name = 'seungo';

 

 

정리하면, 인덱스가 효과적인 경우는 다음과 같다.

 

- 전체 값 일치

- 맨 왼쪽 접두사와 일치

- 열 접두사 일치

- 값 범위 일치

- 한 부분을 정확히 일치시키고, 다른 부분의 범위와 일치

- 인덱스 전용 쿼리

 

2. 적응형 해시 인덱스

 

InnoDB 스토리지 엔진에는 적응형 "해시 인덱스"라는 특별한 기능이 있다. InnoDB는 일부 인덱스 값이 매우 자주 엑세스되고 있음을 감지하면 B-Tree 인덱스 위에서 메모리에 해시 인덱스를 구축한다. 이 프로세스는 MySQL에서 자동으로 이루어지며, 완전히 비활성화를 할 수는 있지만 제어하거나 구성할 수는 없다.

 

 

3. 풀 텍스트 인덱스

 

FULLTEXT는 값을 인덱스의 값과 직접 비교하는 대신 텍스트에서 키워드를 찾는 특수 유형의 인덱스다. 풀 텍스트 검색은 다른 유형의 일치 검색과는 차이가 크다. 불용어 ( 인터넷 검색 시, 검색 용어로 사용하지 않는 단어. 가령 관사, 전치사, 조사 등 ), 형태소 분석, 복수형 및 부울 검색과 같은 미묘함이 존재한다. 따라서 이것은 매개변수 매칭보다는 검색 엔진이 하는 작업과 유사하다고 볼 수 있다.