Real MySQL 8.0 Ch.8 — R-Tree, 전문 검색, 함수 기반, 멀티 밸류 인덱스

B-Tree 인덱스에 이어, MySQL이 제공하는 나머지 인덱스를 정리한다.

R-Tree 인덱스(공간 인덱스)

R-Tree 알고리즘으로 2차원 데이터를 인덱싱하고 검색하는 인덱스다. B-Tree와 구조는 비슷하지만, 인덱스를 구성하는 값이 1차원의 스칼라 값이 아니라 2차원의 공간 개념 값이라는 점이 다르다.

MySQL의 공간 확장(Spatial Extension)을 이용하면 위치 기반 서비스를 간단하게 구현할 수 있다. 공간 확장은 세 가지로 구성된다.

공간 데이터를 저장할 수 있는 데이터 타입
공간 데이터의 검색을 위한 공간 인덱스(R-Tree 알고리즘)
공간 데이터의 연산 함수

구조

공간 데이터 타입에는 Point, Line, Polygon이 있고, 이 셋의 슈퍼 타입인 Geometry에는 모든 객체를 저장할 수 있다.

R-Tree를 이해하려면 MBR(Minimum Bounding Rectangle)을 알아야 한다. MBR은 도형을 감싸는 최소 크기의 사각형이다. 이 사각형들의 포함 관계를 B-Tree 형태로 구현한 인덱스가 R-Tree다. 이름도 Rectangle의 R과 B-Tree를 섞은 것이다.

용도

GPS 기준의 위도, 경도 좌표를 저장하는 데 주로 사용한다.
도형의 포함 관계로 만들어진 인덱스이므로, ST_Contains()나 ST_Within()처럼 포함 관계를 비교하는 함수로 검색할 때만 인덱스를 이용할 수 있다.
예: 현재 사용자의 위치로부터 반경 5km 이내의 음식점 검색

전문 검색 인덱스

B-Tree 인덱스는 크지 않은 데이터나 이미 키워드화한 작은 값에 대한 인덱싱 알고리즘이다.

컬럼의 값이 1MB라도 전체를 인덱스 키로 쓰지 않고 1,000바이트(MyISAM) 또는 3,072바이트(InnoDB)까지만 잘라서 사용한다.
전체 일치 또는 좌측 일부 일치 검색만 가능하다.

따라서 문서의 내용 전체를 인덱스화해 특정 키워드가 포함된 문서를 찾는 전문(Full Text) 검색에는 B-Tree 인덱스를 쓸 수 없다. 문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘을 전문 검색 인덱스라고 한다.

인덱스 알고리즘

문서 본문에서 사용자가 검색하게 될 키워드를 분석해 내고, 그 키워드로 인덱스를 구축한다. 키워드를 뽑는 기법에 따라 어근 분석과 n-gram으로 나뉜다.

어근 분석 알고리즘은 두 단계로 진행된다.

불용어(Stop Word) 처리: 검색에서 별 가치가 없는 단어를 필터링해 제거한다. 불용어는 개수가 많지 않아 코드에 상수로 정의하는 경우가 많고, 유연성을 위해 데이터베이스화해서 사용자가 추가·삭제할 수 있게 하기도 한다.
어근 분석: 검색어로 선정된 단어의 원형을 찾는다. 문장을 해체해 각 단어의 품사를 식별하는 과정이 필요하다. MySQL은 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 지원한다.

n-gram 알고리즘은 본문을 무조건 몇 글자씩 잘라서 인덱싱한다.

형태소 분석은 매우 전문적인 알고리즘이어서 만족할 만한 결과를 내기 쉽지 않다. n-gram은 단순히 키워드를 검색해 내기 위한 방법이다.
알고리즘이 단순하고, 언어에 대한 이해와 준비 작업이 필요 없다.
대신 만들어지는 인덱스의 크기가 상당히 크다.
n은 인덱싱할 키워드의 최소 글자 수다. 일반적으로 2글자 단위로 쪼개는 2-gram이 많이 쓰인다.

가용성

전문 검색 인덱스를 사용하려면 두 조건을 모두 갖춰야 한다.

쿼리가 전문 검색 문법(MATCH ... AGAINST ...)을 사용한다.
테이블이 검색 대상 컬럼에 대한 전문 인덱스를 가지고 있다.

LIKE 검색으로는 전문 검색 인덱스를 사용할 수 없다.

함수 기반 인덱스

일반적인 인덱스는 컬럼의 값 전체 또는 일부에 대해서만 생성할 수 있다. 컬럼의 값을 변형해서 만든 값에 인덱스가 필요할 때 함수 기반 인덱스를 사용한다.

인덱싱할 값을 계산하는 과정만 다를 뿐, 내부 구조와 유지 관리 방법은 B-Tree 인덱스와 동일하다. MySQL에서 구현하는 방법은 두 가지다.

가상 컬럼을 이용한 인덱스

CREATE TABLE user (
  user_id BIGINT,
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  PRIMARY KEY (user_id)
);

first_name과 last_name을 합쳐서 검색해야 한다고 하자. 예전에는 두 값을 합친 full_name 컬럼을 실제로 추가하고 그 컬럼에 인덱스를 만들어야 했다. 이제는 가상 컬럼을 추가하고 그 가상 컬럼에 인덱스를 생성할 수 있다.

ALTER TABLE user
  ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
  ADD INDEX ix_fullname (full_name);

이제 full_name 컬럼에 대한 검색도 ix_fullname 인덱스를 이용해 실행 계획이 만들어진다. 다만 가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 같아서, 실제 테이블의 구조가 변경된다는 단점이 있다.

함수를 이용한 인덱스

가상 컬럼은 5.7에서도 사용할 수 있었지만, 함수를 인덱스 생성 구문에 직접 쓸 수는 없었다. 8.0부터는 테이블 구조를 변경하지 않고 함수를 직접 사용하는 인덱스를 생성할 수 있다.

CREATE TABLE user (
  user_id BIGINT,
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  PRIMARY KEY (user_id),
  INDEX ix_fullname ((CONCAT(first_name, ' ', last_name)))
);

함수 기반 인덱스를 제대로 활용하려면 조건절에 인덱스에 명시된 표현식이 그대로 사용돼야 한다.

SELECT * FROM user WHERE CONCAT(first_name, ' ', last_name) = 'Matt Lee';

멀티 밸류 인덱스

전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다. 즉 인덱스 키와 데이터 레코드가 1:1 관계다.

멀티 밸류(Multi-Value) 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 인덱스다. 정규화에는 위배되는 형태지만, 최근 RDBMS들이 JSON 데이터 타입을 지원하면서 JSON 배열 타입 필드의 원소들에 대한 인덱스 요건이 생겼다.

JSON 포맷으로 데이터를 저장하는 MongoDB는 처음부터 이런 인덱스를 지원했지만, MySQL은 JSON 타입 컬럼만 지원하다가 8.0에서 멀티 밸류 인덱스를 지원하기 시작했다.

CREATE TABLE user (
  user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  credit_info JSON,
  INDEX mx_creditscores ((CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY)))
);

멀티 밸류 인덱스를 활용하려면 반드시 다음 함수로 검색해야 옵티마이저가 인덱스를 사용하는 실행 계획을 수립한다.

MEMBER OF()
JSON_CONTAINS()
JSON_OVERLAPS()

SELECT * FROM user WHERE 360 MEMBER OF (credit_info->'$.credit_scores');

참고

Real MySQL 8.0