전문 검색 인덱스

• MySQL 5.6 부터 Full-Text Index(이하 전문 검색 인덱스)를 공식적으로 지원한다.
• 그 이전에서는 MyISAM 에서만 지원했기 때문에 InnoDB 사용자들은 Elasticsearch 등 외부 시스템을 사용하였다.
• 지금은 VARCHAR, TEST, CHAR 등에 대해서도 전문 검색 인덱스를 사용할 수 있다.

동작 방식

InnoDB에서는 전문 검색 인덱스를 만들 때 텍스트를 단어 단위로 토큰화하고, 각 단어에 대해 역색인(Inverted Index)구조를 구성한다.

Inverted Index, 역색인

• 일반 인덱스는 row를 기준으로 값을 이동한다.
• 역색인은 단어를 기준으로 포함된 row 리스트로 이동한다.

예를 들어 아래와 같은 단어가 있다고 가정해보자.

• Apple is fruit
• Pineapple is fruit

위와 같은 문장에서 역색인 구조는 아래와 같이 저장된다.

`applie` - [1]
`pineapple` - [2]
`fruit` - [1, 2]

생성 예시

CREATE TABLE board
(
  id      INT PRIMARY KEY,
  title   VARCHAR(255),
  context TEXT,
  FULLTEXT (title, context) -- 복합 전문 검색 인덱스
);

-- CREATE문으로 생성할 시에는 FULLTEXT를 명시해야 한다.
CREATE FULLTEXT INDEX idx_title_content
ON board (title, content);

쿼리 방법

SELECT *
FROM board
WHERE MATCH(title, context)
            AGAINST('applie' IN NATURAL LANGUAGE MODE);

전문 검색 인덱스 검색 모드

검색 모드	설명
NATURAL LANGUAGE MODE	기본 검색, 단어 빈도의 중요도 기반
BOOLEAN MODE	연산자(+, -, *, "...", ~) 사용 가늗
WITH QUERY EXPANSION	연관 단어 자동 확장(불안정하다)

-- BOOLEAN 검색 예시
SELECT *
FROM board
WHERE MATCH(title, context)
            AGAINST('+apple -pineapple' IN BOOLEAN MODE);

기본 설정 특징

항목	설명
최소 단어 길이	기본적으로 4자 이상이어야 한다
불용어(Stopword)	기본적으로 무시되는 단어가 존재한다
단어 정규화	대소문자 구분이 없으며, 특수문자를 제거하고, 중복 단어를 제거하는 등 규칙이 있다

만약 3글자 단어도 인덱싱하고 싶다면?

[mysqld]
innodb_ft_min_token_size=3
ft_min_word_len=3

이 설정을 my.cnf 또는 my.ini에 바꾼 뒤에 인덱스를 재작성 해야 적용된다.(DROP INDEX -> CREATE INDEX)

• 리눅스/유닉스: my.cnf
• 윈도우: my.ini

이때 두 가지를 설정하는 이유는 innodb_ft_min_token_size가 InnoDB 전용 적용대상이고 ft_min_word_len는 MyISAM 전용 적용대상이기 때문이다. 둘 다 설정해두는 것이 일반적이며, InnoDB를 사용한다면 첫 번째 줄을 적용하는 것이 핵심이다.

InnoDB의 전문 검색 인덱스의 구조적인 특징

특징	설명
인덱스 형태	역색인
저장 위치	Auxiliary 테이블 + 내부 FTS index table
업데이트 시	INSERT, UPDATE, DELETE 시마다 FTS 테이블을 갱신한다
검색 방식	단어 -> 문서 매핑으로 검색 후 필요 시 정렬한다

장점

• 트랜잭션, FK, 충돌 제어 등이 가능하다.
• 대량 텍스트 검색에 용이하다.
• BOOLEAN 모드로 제어 가능한 고급 검색이 가능하다.

단점

• 한국어 등 띄어쓰기 없는 단어는 부적절할 수 있다. 왜냐하면 기본적으로 전문 검색 인덱스는 형태소 분석 기능이 없기 때문이며 이때문에 부정확할 수 있다.
• 단어 길이 제한 때문에 기본 4글자 이하는 인덱싱이 안 된다.
• 실시간성 때문에 인덱스 반영이 지연될 수 있으며, 트랜잭션 중에는 인덱스에 반영되지 않을 수 있다.
• 하이라이팅, 랭킹 조정, 동의어 확장 등 고급 기능이 부족하다.
• 데이터가 너무 많을 시에는 Elasticsearch가 더 효율적이다.

설계 방법

• 검색 대상이 중복 단어가 많고 짧은 단어가 많을 경우, 기본 설정으로는 검색이 잘 안 된다.
• BOOLEAN MODE를 활용하면 검색 제어가 쉬워진다.
• 한글 형태소를 분석하려면 MySQL 대신 Elasticsearch + Nori 등을 병행해서 사용해야 한다.
  • 그 외에 MySQL에서 텍스트 토큰을 분리한 후 저장하는 N-gram 전처리 방식이 있다고 한다.