본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.
8.4 R-Tree 인덱스
MySQL의 공간 인덱스(Spartial Index)는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스로, 기본적인 내부 매커니즘은 B-Tree와 흡사하다.
8.4.1 구조 및 특성
MySQL은 공간 정보의 저장 및 검색을 위해 여러 가지 기하학적 도형 정보를 관리할 수 있는 데이터 타입을 제공한다.
위 이미지는 MySQL에서 대표적으로 지원하는 데이터 타입이다. GEOMETRY 타입은 아머지 3개 타입의 슈퍼타입으로, POINT와 LINE, POLYGON 객체를 모두 저장할 수 있다.
MBR(Minumum Bounding Rectangle)은 각 도형을 감싸는 최소 크기의 사각형을 의미하며, 위 이미지는 각 도형의 MBR을 보여주고 있다. 이 사각형들의 포함 관계를 B-Tree 형태로 구현한 인덱스가 R-Tree 인덱스다.
위 이미지는 각 공간 데이터, 즉 도형 객체가 존재할 때 MBR을 3개의 레벨로 나눠서 그린 것이다.
- 최상위 레벨: R1, R2
- 차상위 레벨: R3, R4, R5, R6
- 최하위 레벨: R7 ~ R14
최하위 레벨의 MBR은 각 도형 데이터의 MBR, 차상위 레벨의 MBR은 중간 크기의 MBR이다. 최상위 MVR은 R-Tree의 루트 노드에 저장되는 정보이며, 차상위 그룹 MBR은 R-Tree의 브랜치 노드가 된다. 마지막으로 각 도형의 객체는 리프 노드에 저장되므로 아래와 같이 R-Tree 인덱스의 내부를 표현할 수 있다.
8.4.2 R-Tree 인덱스의 용도
R-Tree는 MRB 정보를 이용해 B-Tree 형태로 인덱스를 구축하므로 Rectangle의 'R'과 B-Tree의 'Tree'를 섞어 R-Tree라는 이름이 붙여졌으며, 공간(Spatial) 인덱스라고도 한다. 일반적으로는 WGS84(GPS) 기준의 위도, 경도 좌표 저장에 주로 사용되며, CAD/CAM 소프트웨어 또는 회로 디자인 등과 같이 좌표 시스템에 기반을 둔 정보에 대해서는 모두 적용할 수 있다. MySQL에서 ST_Contains(), ST_Within() 함수를 사용해 공간 인덱스를 통한 거리 기반 검색이 가능하다.
8.5 전문 검색 인덱스
문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문(Full Text) 검색에는 InnoDB나 MyISAM 스토리지 엔진에서 제공하는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다. 문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘을 전문 검색(Full Text search) 인덱스라고 한다.
8.5.1 인덱스 알고리즘
어근 분석 알고리즘
MySQL 서버의 전문 검색 인덱스는 불용어 처리, 어근 분석 과정을 거쳐서 색인 작업이 수행된다.
불용어 처리는 검색에서 별 가치가 없는 단어를 필터링해서 제거하는 작업을 의미한다. MySQL 서버는 불용어가 소스코드에 정의돼 있지만, 사용자가 별도로 불용어를 정의할 수 있는 기능을 제공한다.
어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다.MySQL 서버에서는 오픈소스 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 사용할 수 있게 지원한다. 하지만 MeCab은 일본어를 위한 형태소 분석 프로그램으로, 한글에 맞게 완성도를 갖추는 작업은 많은 시간과 노력이 필요하다.
n-gram 알고리즘
MeCab을 이용한 형태소 분석은 많은 노력과 시간이 필요해 전문적인 검색 엔진을 고려한는 것이 아니라면 범용적으로 적용하기는 쉽지 않다. 이런 단점을 보완하기 위해 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘인 n-gram 알고리즘이 도입되었다.
n-gram이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다. 형태소 분석보다는 알고리즘이 단순하고 국가별 언어에 대한 이해와 준비 작업이 필요 없는 반면, 만들어진 인덱스의 크기는 상당히 큰 편이다. n-gram에서 'n'은 인덱싱할 키워드의 최소 글자수로, 일반적으로는 2글자 단위로 키워드를 쪼개서 인덱싱하는 2-gram(Bi-gram) 방식이 많이 사용된다.
2-gram 알고리즘으로 다음 문장의 토큰을 분리하는 방법을 살펴보자.
That is the question
각 단어는 다음과 같이 띄어쓰기와 마침표를 기준으로 4개의 단어로 구분되고, 2글짜식 중첩해서 토큰으로 분리된다. 이렇게 구분된 각 토큰을 인덱스에 저장하며, 중복된 토큰은 하나의 인덱스 엔트리로 병합되어 저장된다.
이후 MySQL 서버는 생성된 토큰들에 대해 불용어를 걸러내는 작업을 수행하는데, 불용어와 동일하거나 불용어를 포함하는 경우 걸러서 버린 이후 남은 토큰들을 전문 검색 인덱스에 등록한다. 기본적으로 MySQL 서버에 내장된 불용어는 information_schema.innodb_ft_default_stopword 테이블을 통해 확인할 수 있다.
8.5.2 전문 검색 인덱스의 가용성
전문 검색 인덱스를 사용하려면 반드시 다음 두 가지 조건을 갖춰야 한다.
- 쿼리 문장이 전문 검색을 위한 문법(
MATCH ... AGAINST ...)을 사용 - 테이블이 전문 검색 대상 칼럼에 대한 전문 인덱스 보유
물론 LIKE를 사용한 검색 쿼리로도 원하는 검색 결과를 얻을 수 있으나, 이는 전문 검색 인덱스를 이용한 것이 아닌 풀 테이블 스캔으로 쿼리가 처리된다.
8.6 함수 기반 인덱스
일반적인 인덱스는 칼럼의 값 일부 또는 전체에 대해서만 인덱스 생성이 허용된다. 하지만 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데, 이러한 경우 함수 기반 인덱스를 활용할 수 있다. MySQL 서버의 함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있을 뿐, 실제 인덱스의 내부 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.
8.6.1 가상 칼럼을 이용한 인덱스
이전 버전의 MySQL 서버는 원하는 조건의 칼럼을 추가하고 모든 레코드에 대해 해당 칼럼을 업데이트하는 작업을 거쳐야했으나, MySQL 8.0 버전부터는 가상 칼럼을 추가하고 그 가상 칼럼에 인덱스를 생성할 수 있게 됐다. 예시 쿼리는 다음과 같다.
mysql> ALTER TABLE user
ADD full_name VARCHAR(30) AS (CONCAT(first_name,' ',last_name)) VIRTUAL,
ADD INDEX ix_fullname (full_name)그러나 가상 칼럼은 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블 구조가 변경된다는 단점이 있다.
8.6.2 함수를 이용한 인덱스
MySQL 8.0 버전부터 다음과 같이 테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.
mysql> CREATE TABLE (
user_id BIGINT,
first_name VARCHAR(10),
last_name VARCHAR(10),
PRIMARY KEY (user_id),
INDEX ix_fullname ((CONCAT(first_name,' ',last_name)))
);함수를 직접 사용하는 인덱스는 테이블의 구조는 변경하지 않고, 계산된 결괏값의 검색을 빠르게 만들어준다. 함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용돼야 한다.
8.7 멀티 밸류 인덱스
멀티 밸류(Multi-Value) 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스다. 일반적인 DBMS 기준으로는 정규화에 위배되는 형태이나, 최근 RDBMS들이 JSON 데이터 타입을 지원하기 시작하며 JSON의 배열 타입의 필드에 저장된 원소들에 대한 인덱스 요건이 발생한 것이다.
멀티 밸류 인덱스를 활용하기 위해서는 일반적인 조건 방식이 아닌 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.
MEMBER OF()JSON_CONTAINS()JSON_OVERLAPS()
