Fulltext Search

mysql에서 text검색을 할 경우 like 문을 사용하는데 이럴 경우 index를 타지 않는 경우가 있어 검색 속도가 떨어지고 자연검색어(가령 띄워쓰기로 검색어를 입력하는 경우) 검색 효율도 떨어지게 됩니다.
이때 사용할 수 있는 것이 아래에 소개해 드리는 fulltext 검색입니다.
FullText 검색은 단어 또는 구문에 대한 검색을 의미하며, 게시물본문이나 제목 같이 문서 내 키워드를 검색할 수 있습니다.

사용전제조건

CHAR, VARCHAR 혹은 TEXT 타입의 컬럼에서만 생성

Full-text 인덱스는 InnoDB나 MyISAM 엔진에서만 사용할 수 있으며, CHAR, VARCHAR 혹은 TEXT 타입의 컬럼에서만 생성할 수 있습니다.

검색어의 길이 짧은 경우

검색어의 길이 짧은 경우 검색되지 않으므로 2자 이상으로 할경우는 my.cnf의 수정이 필요하다.

show variables like '%ft_min%';

• my.cnf

ft_min_word_len=2 // 디폴트는 4로 되어 있다.
innodb_ft_min_token_size=2

검색에서 제외되는 검색어

Stopword 로 지정된 단어는 무시됩니다.
Stopword는 a, the, some 과 같은 의미가 없는 단어들로, built-in stopword가 있으며 아래와 같이 확인할 수 있습니다.

select * from INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

구문

MATCH (col1, col2,...) AGAINST (expr [search_modifier])

• search_modifier
  • IN NATURAL LANGUAGE MODE (자연어 검색)
  • IN BOOLEAN MODE (불린모드 검색)
  • WITH QUERY EXPANSION(쿼리확장 검색)

사용예

1. IN NATURAL LANGUAGE MODE (자연어 검색)

자연어 검색은 검색 문자열을 단어 단위(token_size)로 분리한 후, 해당 단어 중 하나라도 포함되는 행을 찾습니다.

SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('온스토리 이야기'); // 기본은 NATURAL LANGUAGE MODE 이다.
SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('온스토리 이야기' IN NATURAL LANGUAGE MODE); // NATURAL LANGUAGE MODE

매치율

입력된 검색어의 키워드가 얼마나 더 많이 포함되어 있는지에 따라 매치율(유사성 측정값)이 결정 되는데전체 테이블의 50% 이상의 레코드가 검색된 키워드를 가지고 있다면, 그 키워드는 검색어로서 의미가 없다고 판단하고 검색 결과에서 배제 시키게 됩니다.
이 때 매치율이 계산될 때는row내의 고유 단어 수, 총 단어 수, 특정 단어를 포함하는 row 수 등을 기준으로 계산됩니다.   검색 결과는 가장 높은 관련성을 가진 결과부터 자동 정렬되는데, 아래와 같은 조건에 한해 자동 정렬합니다.  

• ORDER BY 절이 없어야 합니다.
• 검색은 테이블 검색이 아닌 FULLTEXT Index를 사용하여 수행해야 합니다.
• 쿼리가 테이블을 조인하는 경우, FULLTEXT Index는 조인에서 가장 왼쪽에 있는 non-constant 테이블이어야 합니다.  

검색 매치율는 아래와 같이 확인할 수 있습니다.

SELECT MATCH(title, body) AGAINST('온스토리 이야기') as score FROM `posts`;

대소문자

자연어 검색은 기본적으로 검색은 대소문자를 구분하지 않는 방식으로 수행됩니다.대소문자를 구분하는 전체 텍스트 검색을 수행하려면  binary collation을 사용할 수 있는데요.예를 들어, latin1 캐릭터 셋을 사용하는 열에 latin1_bin을 할당해서 대소문자를 구분하도록 설정할 수 있습니다.

2. IN BOOLEAN MODE (불린모드 검색)

불린 모드 검색은 문자열을 단어 단위로 분리한 후, 추가적인 검색 규칙을 적용되어서 단어가 포함되는 행을 찾습니다.
불린 모드 검색은 IN BOOLEAN MODE를 지정해서 검색할 수 있습니다

• 검색의 정확도에 따라 결과가 정렬 되지 않음.
• 구문 검색이 가능.
• 필수(+), 예외(-), 부분(*) , 구문("") 등 연산자를 사용 가능.
• 검색 키워드를 쌍따움표("") 로 묶으면 해당 전체 구문 그대로 포함된 게시글을 검색.

SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('온스토리 이야기' in BOOLEAN MODE); // '온스토리'나  '이야기' 가 들어 있는 posts 출력
SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('"온스토리 이야기"' in BOOLEAN MODE); // '온스토리 이야기' 가 들어 있는 posts 출력
SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('온스토리*' in BOOLEAN MODE); // '온스토리'로 시작하는 모든 단어가 들어있는 posts 출력 ('온스토리%') 와 동이 구문
SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('+온스토리 -이야기"' in BOOLEAN MODE) // '온스토리'는 포함하고 '이야기'는 포함하지 않는 posts 출력 

BOOLEAN MODE Operator(연산자)

3. WITH QUERY EXPANSION(쿼리확장 검색)

쿼리 확장 검색은 자연어 검색을 확장한 내용으로, 2단계에 걸쳐서 검색을 수행합니다.
첫 단계에서는 자연어 검색을 수행한 후, 첫 번째 검색의 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두 번째 검색을 수행합니다.
  쿼리 확장 검색은 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION, 혹은 WITH QUERY EXPANSION  한정자를 사용합니다.
  쿼리 확장 검색은 일반적으로 검색 구문이 아주 짧을 때 유용합니다.
사용자가 전체 텍스트 검색 엔진에 없는 "자연어 연관 내용"이라는 "암묵적인 지식(아래 설명 참고)"으로 나오는 결과들입니다.

SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('온스토리 이야기' WITH QUERY EXPANSION); // WITH QUERY EXPANSION

위의 query는 두번의 query를 실행하는 데 아래처럼 IN NATURAL LANGUAGE MODE (자연어 검색) 을 실행한 후 이 것의 결과값(여기서는 title과 body의 내용) 을 다시 자연어 검색에 넣는 것입니다.

[첫번째 query의 title, body의 결과] = SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('온스토리 이야기');
SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('[첫번째 query의 title, body의 결과]');

FullText Index

위의 Match 에 들어가는 컬럼들에 대해서 index를 잡아야 하는데 아래와 같이 기존 테이블을 변경하거나 새로 생성 하시면 됩니다.
MariaDB에서는 ngram 지원을 하지 않음.(Mroonga 플러그인을 사용하면 Ngram 검색 가능)

ALTER TABLE [테이블명] ADD FULLTEXT INDEX [인덱스명] (column1,...) WITH PARSER ngram; // MariaDB에서는 'WITH PARSER ngram' 삭제
CREATE FULLTEXT INDEX [인덱스명] ON [테이블명] (column1,...) WITH PARSER ngram; // MariaDB에서는 'WITH PARSER ngram' 삭제

FullText Index for MariaDB 를 위한 설정

FULLTEXT INDEX는 단어 기준으로 검색되며 MariaDB에서는 Ngram 검색은 지원하지 않음. Mroonga 플러그인을 사용하면 Ngram 검색 가능

인덱스 재 생성

기존 테이블에 추가한 경우 기존 테이블의 있던 데이타도 인덱스를 재 생성 하여야 한다.

REPAIR TABLE [테이블명] QUICK;
OPTIMIZE TABLE [테이블명];

FULLTEXT INDEX 를 이용한 조회 쿼리

IN NATURAL LANGUAGE MODE(자연어 검색): default

단어 기준으로 검색 하기 때문에 '온스토리' 키워드로 검색해도 '세상의재미있는이야기온스토리' 는 검색 되지 않는다 . 이를 해결하기 위해서는 Mroonga 플러그인은 사용해야한다.

SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('온스토리 이야기'); // 기본은 NATURAL LANGUAGE MODE 이다.
SELECT * FROM `posts` WHERE MATCH(title, body) AGAINST('온스토리 이야기' IN NATURAL LANGUAGE MODE); // NATURAL LANGUAGE MODE