[mysql] fulltext index

개요

mysql 데이터베이스를 사용하다보면, 조회 속도 개선을 위한 작업을 많이 하게 됩니다.

그래서 index를 많이 사용하게 되는데, 기본적인 index는 column값이 정확하게 일치하는 기준으로 사용할 수 있습니다.

 

column값이 텍스트를 활용하고, 값이 일부만 일치하는 경우 조회를 하기 위해서는 like 쿼리를 사용하는데, 그 속도가 매우 느립니다.

완전 동일한 조회가 아니라서 기본적인 index를 사용할 수도 없구요.

이럴 때, fulltext를 사용하면 조회 속도를 개선할 수 있습니다.

 

원리

fulltext index를 사용하는 방법을 알아보기 전에, 원리를 우선 간단하게 알아보겠습니다.

column의 값이 영어 이름이라고 가정하고, '마이클 잭슨'과 '마이클 조던' 이라는 2개의 값이 존재한다고 가정해보겠습니다.

이 때, '마이클'이 들어가는 이름을 검색하고 싶다고 하면 like '%마이클%' 이런 쿼리를 쓰게 될 텐데요.

 

fulltext index를 사용하게 되면, column의 값을 띄어쓰기 단위로 나누어서 미리 그 단어가 들어있는 문서에 대해서 정보를 만들어 놓게 됩니다. 

좀 더 자세하게 예시를 들어보겠습니다.

예시

(doc id, name)

(1, '마이클 잭슨')

(2, '마이클 조던')

토큰 분리

'마이클 잭슨' -> '마이클', '잭슨'

'마이클 조던' -> '마이클', '조던'

토큰 별 문서 정보 저장

(token, doc ids)

('마이클', (1, 2))

('잭슨', (1))

('조던', (2))

 

위의 예시와 같이 미리 '마이클', '조던', '잭슨' 이렇게 분리를 해두고, 어떤 문서에서 등장하는지 기록을 해둔다면 '마이클'이 들어간 문서는 바로 1, 2라는 것을 알 수 있을 것 입니다.

이것이 fulltext index의 원리입니다.

사용법

그러면 이제는 실제로 query를 어떻게 사용해야 하는지 알아보겠습니다.

우선, fulltext index를 생성하는 방법입니다.

생성 방법은 아래와 같이 3가지 방법이 있습니다.

# 테이블 생성 시 정의
CREATE TABLE 테이블이름(
  …,
  열이름 데이터형식,
  …,
  FULLTEXT INDEX 인덱스이름 (열이름)
);
# 기 존재하는 테이블에 인덱스 생성
CREATE FULLTEXT INDEX 인덱스이름 ON 테이블이름 (열이름);
# ALTER 명령을 활용한 테이블 변경
ALTER TABLE 테이블이름 ADD FULLTEXT (열이름);

조회 쿼리가 기존과 좀 다르게 느껴질 수 있는데, 아래와 같이 match와 against라는 구문을 활용해야 합니다.

SELECT * FROM 테이블 WHERE MATCH(열이름) AGAINST('토큰');

아래는 실제 예시 쿼리입니다.

#테이블 생성
CREATE TABLE name(
	id int AUTO_INCREMENT PRIMARY KEY,
	name TEXT NOT NULL,
	FULLTEXT INDEX ft_idx_name (name)
);
# 데이터 생성
INSERT INTO name(name) VALUES ('마이클 조던'), ('마이클 잭슨')
# 조회
SELECT * FROM name
WHERE MATCH(name) AGAINST('마이클')

이렇게 하면, 2개의 row가 반환이 될 것 입니다.

주의 사항

'마이클'로는 잘 조회가 되었을텐데, '조던' 혹은 '잭슨'으로 확인해보면 조회가 안 될텐데요.

그 이유는 innodb_ft_min_token_size 이라는 변수 때문입니다.

show variables like 'innodb_ft_min_token_size'

위의 쿼리를 실행해서 결과를 확인해보면 3이라는 결과를 볼 수 있을텐데요.

위 변수는 토큰의 최소 길이를 뜻하고, 3으로 기본적으로 세팅되어 있습니다.

그래서 3보다 짧은 '잭슨'이나 '조던'은 조회가 안되고 '마이클'만 조회가 가능합니다.

 

위 변수는 mysql설정으로 조정 할 수 있기 때문에, 조정해서 사용하시면 됩니다.