LIKE 연산을 통해 검색을 하게 되면 인덱스를 통한 검색이 어려운데 이럴 때 고려해볼 수 있는 것이 Full-Text 검색으로FullText 검색은 단어 또는 구문에 대한 검색을 의미.
MyISAM은 MySQL 5.5 버전 이상부터, innoDB는 MySQL 5.6 버전 부터 지원.
실행 방법
MATCH … AGAINST
전체 텍스트 검색은 MATCH AGAINST 구문을 사용하여 수행.
MATCH (col1,col2,...) AGAINST (expr [search_modifier])MATCH는 쉼표로 구분되며 검색할 열을 지정하며, AGAINST는 검색할 문자열과 수행할 검색 방식을 지정
검색 유형 :
{
IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
}FULLTEXT INDEX
MySQL에서 에서 FULLTEXT 타입의 인덱스로 Full-Text 검색을 위해서는 인덱스 설정이 필요한데
데이터 타입이 CHAR, VARCHAR, TEXT 인 경우에만 FULLTEXT INDEX 설정이 가능함.
CREATE FULLTEXT INDEX title ON news (title);이때, 영어는 잘 검색이 되나 한글은 잘 검색이 안되는 이슈.
이를 위해 MySQL fulltext 검색 알고리즘 중, Ngram 사용.
- MySQL은 빌트인(내장)된 Ngram parser를 지원하며, 중국어와 일본어 그리고 한글(CJK)를 지원
CREATE FULLTEXT INDEX title ON news (title) WITH PARSER ngram;Ngram parser
일련의 텍스트를 n개의 문자로 구성된 연속된 시퀀스로 토큰화하여 검색.
ngram 파서의 기본 ngram 토큰 크기는 2(bigram)이며, 한 글자(문자)만 검색하려면 ngram_token_size를 1로 설정이 필요.
ngram_token_size = 1이 때, ngram 파서를 사용하는 FULLTEXT 인덱스의 경우에는 아래의 구성 옵션이 무시됨.
- innodb_ft_min_token_size / innodb_ft_max_token_size
- ft_min_word_len / ft_max_word_len
검색 유형
1. IN NATURAL LANGUAGE 검색
검색 문자열을 단어 단위(token_size)로 분리한 후, 해당 단어 중 하나라도 포함되는 행을 찾음.
자연어 검색은 기본 검색 타입으로 MATCH … AGAINST 구문에 별도의 옵션을 지칭하지 않으면 자연어 검색 모드로 검색됨. (혹은 AGAINST 구문에 IN NATURAL LANGUAGE MODE 입력)
SELECT
n.seq,
n.title
FROM
news AS n
WHERE
MATCH (n.title) AGAINST ('KBO' IN NATURAL LANGUAGE MODE);매치율
입력된 검색어의 키워드가 얼마나 더 많이 포함되어 있는지에 따라 매치율(유사성 측정값)이 결정 되는데
전체 테이블의 50% 이상의 레코드가 검색된 키워드를 가지고 있는 경우, 그 키워드는 검색어로서 의미가 없다고 판단하고 검색 결과에서 배제됨.
이 때 매치율은 row내의 고유 단어 수, 총 단어 수, 특정 단어를 포함하는 row 수 등을 기준으로 계산되며
검색 결과는 가장 높은 관련성을 가진 결과부터 자동 정렬되는데, 아래와 같은 조건에 한해 자동 정렬.
- ORDER BY 절이 없어야 함.
- 검색은 테이블 검색이 아닌 FULLTEXT Index를 사용하여 수행해야 함.
- 쿼리가 테이블을 조인하는 경우, FULLTEXT Index는 조인에서 가장 왼쪽에 있는 non-constant 테이블이어야 함.
대소문자
기본적으로 검색은 대소문자를 구분하지 않는 방식으로 수행.
대소문자를 구분하는 전체 텍스트 검색을 수행하려면 binary collation을 사용하면됨.
검색어 길이
길이가 기준보다 짧거나, 특정 단어(Stopword)는 풀텍스트 검색에서 무시됨.
최소 인덱싱 글자수 설정
innodb_ft_min_token_size = 1
ft_min_word_len = 12. BOOLEAN 검색
불린 모드 검색은 문자열을 단어 단위로 분리한 후, 추가적인 검색 규칙을 적용되어서 단어가 포함되는 행을 찾음.
불린 모드 검색은 IN BOOLEAN MODE를 직접 지정해서 검색할 수 있으며 연산자를 사용하여 검색 조건을 추가 가능함.
SELECT
n.seq,
n.title
FROM
news AS n
WHERE
MATCH (n.title) AGAINST ('KBO' IN BOOLEAN MODE);연산자
+ : AND, 반드시 포함하는 단어
– : NOT, 반드시 제외하는 단어
> : 포함하며, 검색 순위를 높일 단어
- +mysql >tutorial
- mysql과 tutorial가 포함하는 행을 찾을 때, tutorial이 포함되면 검색 랭킹이 높아짐
< : 포함하되,검색 순위를 낮출 단어
- +mysql <training
- mysql과 training가 포함하는 행을 찾지만, training이 포함되면 검색 랭킹이 낮아짐
() : 하위 표현식으로 그룹화 (포함, 제외, 순위 지정 등)
- +mysql +(>tutorial <training)
- mysql AND tutorial, mysql AND training 이지만, tutorial의 우선순위가 더욱 높게 지정
~ : ‘-‘ 연산자와 비슷하지만 제외 시키지는 않고 검색 조건을 낮춤
* : 와일드 카드로 붙음
“” : 구문 정의
3. with Query Expansion 검색
자연어 검색을 확장한 내용으로, 2단계에 걸쳐서 검색을 수행.
첫 단계에서는 자연어 검색을 수행한 후,
첫 번째 검색의 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두 번째 검색을 수행함.
쿼리 확장 검색은 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 혹은 WITH QUERY EXPANSION을 직접 지정해서 사용.
SELECT
n.seq,
n.title
FROM
news AS n
WHERE
MATCH (n.title) AGAINST ('KBO' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
SELECT
n.seq,
n.title
FROM
news AS n
WHERE
MATCH (n.title) AGAINST ('KBO' WITH QUERY EXPANSION);쿼리 확장 검색은 일반적으로 검색 구문이 아주 짧을 때 유용하며
만약 쿼리 확장을 사용한다면, 두 번째 검색을 할 때 에서 오타로 추측된 단어가 포함된 내용을 찾을 수 있음.
“월드컵” 이라는 검색어가 있을 경우.
자연어 모드에서는 “월”, “드”, “컵” 용어 중 일치하는 게 있으면 출력.
불린 모드에서는 “+월 +드 +컵”로 검색되어 출력.
중지 단어
mysql에서 가지고 있는 중지 단어가 36개 정도 있는데 사용자가 별도의 테이블에 중지 단어를 추가한 후에 적용할 수도 있음.
방법
- 중지 단어를 저장할 테이블을 만드는데, 컬럼명운 무조건 value 로 지정해야하며 타입은 VARCHAR로 지정.
CREATE TABLE stop_word_table (value VARCHAR(50)); - 그리고 중지 단어를 INSERT
INSERT INTO stop_word_table VALUES ('그리고'), ('매우'), ('왜냐하면'); - 중지 단어 테이블로 사용할 테이블 지정
SET GLOBAL innodb_ft_server_stopword_table = 'contents/stop_word_table'; SHOW GLOBAL VARIABLES LIKE 'innodb_ft_server_stopword_table';
중지단어도 검색을 허용하게 할 경우
innodb_ft_enable_stopword = 0인덱스 추가 후, 다음날 쿼리를 실행시에 예상했던 인덱스를 타지 않는 현상 발생
→ ANALYZE TABLE {table_name}
Reference.