[MySQL] 인덱스

 

 

 

컴퓨터에서 CPU나 메모리같은 주요 장치는 전자식이지만, 데이터를 저장하는 HDD는 기계식이다.

 

이런 HDD를 대체하기 위해 전자식 저장 도구인 SSD가 등장했다. SSD는 전자식으로 데이터에 접근해 훨신 빠르고, 플래시 메모리를 장착하고 있어 전원이 공급되지 않아도 데이터가 삭제되지 않는다.

 

MySQL에서 인덱스는 테이블에서 칼럼의 값과 해당 레코드가 저장된 주소를 키 - 값으로 저장하는 자료구조이다.

인덱스는 정렬된 상태로 보관되기에 특정 레코드에 접근할 때 인덱스를 사용하면 빠르게 접근할 수 있다.

데이터베이스 테이블에 대한 참조로 생각하면 된다.

 

B-Tree 인덱스

B-Tree는 데이터베이스의 인덱싱 알고리즘 중 가장 일반적으로 사용되고 가장 먼저 도입된 알고리즘이다. (Balanced Tree)

트리에서 리프 노드는 항상 실제 레코드를 찾아갈 수 있는 주솟값을 가지고, 루트 노드와 브랜치 노드는 자식 노드의 주소를 가리킨다.

 

 

 

B-Tree에 새로운 키 값이 저장될 때는 저장될 키 값을 사용해 적절한 위치를 검색한다.

위치가 결정되면 레코드의 키 값과 레코드의 주소 정보를 리프 노드에 저장하는데.. 리프 노드가 꽉 차 있는 경우 분리하는 작업을 거쳐 비용이 많이 든다.

 

삭제는 리프 노드의 삭제 마크만 추가해 주면 돼 비교적 간단하고, 수정은 키 값을 삭제하고 새로운 키 값을 추가하는 방식으로 처리된다.

 

구축된 인덱스는 검색 시 루트 노드부터 시작해 리프 노드까지 이동하며 비교 작업을 수행하고, 덕분에 빠르게 데이터에 접근할 수 있다.

 

 

인덱스를 잘못 생성하면 성능이 저하되고 공간을 낭비하기 쉬우니 생성 전 몇 가지를 고려하자.

 

1. Cardinality

인덱스 키 값의 유일한 값의 개수를 의미한다.

Cardinality가 높으면 해당 열에 고유한 값이 많음을 의미하고, 낮으면 중복되는 값이 많음을 의미한다.

 

일반적으로 카디널리티가 높은 열에 인덱스를 생성하면 인덱스를 통해 특정 값을 찾을 때 중복된 값을 여러 번 검색할 필요가 없게 돼 효율성이 높아진다.

 

옵티마이저는 보통 인덱스를 통해 데이터에 접근하는 작업의 오버헤드를 테이블에서 직접 접근하는 작업의 오버헤드보다 4~5배 더 크다고 예측하니 인덱스로 읽어야 할 레코드가 전체의 20%가 넘어가면 인덱스 말고 다른 방법을 생각해보자.

 

 

2. 인덱스의 크기 

InnoDB 스토리지 엔진은 페이지나 블록 단위로 데이터를 저장한다.

인덱스도 결국 페이지 단위로 관리되고, 푸트 / 브랜치 / 리프 노드를 구분하는 단위도 페이지이다.

 

인덱스 키 값이 너무 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 I/O 작업이 발생해 느려지게 된다.

또한 키 값이 커지면 B-Tree의 깊이도 깊어지고, 깊어진 만큼 I/O가 발생하게 되니 인덱스 키 크기는 최대한 작게 설정하는 편이 합리적이다.

 

 

3. 인덱스 스캔 방식 

검색해야 할 인덱스의 범위가 결정됐을 때는 인덱스 레인지 스캔을 사용한다.

루트 노드부터 시작해 리프 노드까지 들어가서 레코드의 시작 지점을 찾은 후 리프 노드의 레코드를 순서대로 읽는다.

차례대로 읽는 중 리프 노드의 끝에 도달하면 리프 노드간의 링크를 사용해 다음 리프 노드를 찾고 다시 읽는다.

스캔을 멈출 위치에 도착하면 레코드를 반환하고 쿼리를 마친다.

 

반면 인덱스의 처음부터 끝까지 모두 읽는 방식으로 탐색하는 인덱스 풀 스캔 방식도 있다.

테이블이 아닌 인덱스의 모든 엔트리를 탐색하는 방식으로, 인덱스는 테이블과 다른 장소에 보관되어있고 인덱스가 테이블보다 크기가 작으니 인덱스만 읽는게 효율적일 경우가 있다.

인덱스에 포함된 칼럼만으로 쿼리를 처리할 수 있으면 테이블을 레코드를 읽을 필요가 없어 I/O가 줄어든다.

 

이 외에도 여러 가지 스캔 방식이 있다.

인덱스를 어떻게 설정하는지에 따라서 각자 다른 방식의 스캔을 사용하게 되고 스캔 방식마다 쿼리의 효율이 크게 차이나니 인덱스의 스캔 방식을 제대로 이해한 후 인덱스를 설정하자.

 

쿼리에서 WHERE / GROUP BY / ORDER BY 구문이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용하는지 알고 있어야 인덱스로 성능을 끌어올릴 수 있다. 

 

 

4. 클러스터링 인덱스

InnoDB에서 사용하는 인덱스로 테이블의 PK에만 적용되는 인덱스이다.

프라이머리 키 값에 의해 레코드의 저장 위치가 결정되고, 만약 PK가 변경된다면 데이터가 저장되는 물리적인 위치도 바뀌게 된다.

 

테이블은 하나의 클러스터링 인덱스만 가질 수 있고, PK가 있으면 PK를 기반으로 만들고 없다면 UNIQUE 인덱스를, 그것마저 없다면 유니크한 값을 가지는 칼럼을 내부적으로 추가하고 클러스터링 키로 사용한다. (추가되는 키는 노출되지 않는다)

 

클러스터링 인덱스가 아닌 인덱스는 모두 세컨더리 인덱스로 취급된다.

인덱스 항목이 클러스터링 인덱스의 키 값을 참조하니 세컨더리 인덱스를 사용해 데이터를 조회하는 경우 참조된 클러스터링 인덱스의 키 값을 찾은 후 실제 데이터에 접근하게 된다. 

 

클러스터링 인덱스를 사용해서 데이터를 다루는 경우 조회 성능이 매우 뛰어나지만 직접 데이터의 저장 위치를 다루기에 쓰기 작업에서는 성능이 떨어진다.

 

 

이렇듯, 인덱스와 PK를 어떻게 설정하는지에 따라서 쿼리의 실행 성능이 크게 달라진다.

 

InnoDB를 사용할 때는 클러스터링 인덱스를 고려해 PK를 무조건 AUTOINCREMENT로 설정하기보다는 칼럼을 대표할 수 있는 값이 있다면 해당 칼럼을 PK로 설정하고, 클러스터링 인덱스의 크기가 작은 방향으로 PK로 설정하자.

 

테이블에 FK를 설정할 경우 연관되는 테이블의 칼럼에 인덱스까지 함께 설정되니... 인덱스와 PK, FK 를 설정할 때는 위와 같은 내용을 숙지하고 해당 인덱스를 통해 쿼리가 어떻게 실행되는지, PK를 설정할 때 인덱스는 어떻게 설정될지 등을 고려하자.

 

 

 

InnoDB가 MySQL의 기본 스토리지 엔진이 되면서 InnoDB에도 전문 검색 인덱스가 도입됐다. (Full Text Search)

 

지금까지 사용했던 인덱스들은 일반적으로 크지 않은 데이터에 대한 인덱스였는데, 전문 검색 인덱스는 문서의 전체 내용을 인덱스화해서 사용자가 검색하게 될 키워드를 분석해 빠르게 검색할 수 있도록 한다.

 

InnoDB는 두 가지 과정을 거쳐 인덱스화한다.

 

1. 불용어 처리

검색어에서 가치가 없는 단어를 모두 필터링해서 제거하는 작업이다. (그리고, 또는, 따라서 등..)

불용어를 상수로 정의해서 처리하는 경우가 많고, 사용자가 불용어를 직접 지정할 수 있다.

 

2. 어근 분석

검색어로 선정된 단어의 원형을 찾는 작업이다.

각 국가별로 언어가 다르고 문법이 다르니 언어별로 다른 라이브러리를 사용하는데, 한국어는 일본어와 비슷해 일본어를 처리하는 MeCab 라이브러리를 사용해 한글을 처리하는데... 문장 구조 인식을 위해 언어를 학습하는 과정이 쉽지 않다.

 

따라서 n-gram 알고리즘이 도입됐다. 

키워드를 검색하기 위한 인덱싱 알고리즘으로, 본문을 몇 글자씩 잘라서 인덱싱한다.

문장을 단어로 분할하고, 단어는 다시 토큰으로 구분해 각 토큰을 인덱스로 저장하는 방식이다. 

 

전문 검색 인덱스를 사용하려면 쿼리를 작성할 때 전용 문법 (MATCH ... AGAINST) 을 사용해야 하고, 테이블이 전문 인덱스를 보유하고 있어야 함에 주의하자.