MySQL FullText Search, 제대로 이해하기

MySQL의 fulltext search 에 대한 이해와 MATCH AGAINST 사용법을 간단히 알아보는 것이 해당 포스팅의 목표입니다.

후속 포스팅으로 FULLTEXT 검색 파서 중 하나인 "ngram" 사용법에 대해 다룰 때 구문 사용법을 조금 더 자세히 알아볼 예정이며, 해당 포스팅에서는 FullText 기본적인 내용과 활용법에 대해 알아보고자 합니다.

 

 

Full-Text Searches

MySQL을 사용할 때, LIKE 연산을 통한 패턴 일치 검색을 사용하고는 합니다.

LIKE 연산을 통해 검색을 하게 되면 인덱스를 통한 검색을 못할 때가 생기곤 하는데요.

이럴 때 고려해볼 수 있는 것이 바로 오늘 다룰 전문 검색, Full-Text 검색입니다.

 

FullText 검색은 단어 또는 구문에 대한 검색을 의미하며, 

가령 게시물 내용이나 제목 등과 같이 문서 내 키워드를 검색할 수 있습니다.

 

 

 

MATCH ... AGAINST

전체 텍스트 검색은 MATCH AGAINST 구문을 사용하여 수행됩니다.

 

구문은 아래와 같은 형태인데요.

MATCH는 쉼표로 구분되며 검색할 열을 지정하며,

AGAINST는 검색할 문자열과 수행할 검색 유형을 나타내는 search modifier를 사용합니다.

 

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

 

search_modifier:
  {
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

 

 

예를 들어, 간단한 예시를 들면 아래와 같은 형식으로 사용할 수 있습니다.

 

SELECT * FROM article WHERE MATCH(content) AGAINST('covid -virus' in boolean mode);

 

 

 

✔️ FULLTEXT INDEX

Full-text index는 MySQL에서 에서 FULLTEXT 타입의 인덱스입니다.

Full-Text index를 정의하는 방법은 테이블 생성 시 CREATE TABLE 구문을 사용하거나

생성 후 ALTER TABLE 이나 CREATE INDEX 구문을 통해 추가할 수 있습니다.

 

예를 들어 Parser 중 ngram을 사용한다고 한다면, 아래와 같은 형식으로 생성할 수 있습니다.

 

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;
# Or:
CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;

 

 

✔️ Condition

Full-text 인덱스는 InnoDB나 MyISAM 엔진에서만 사용할 수 있으며,

CHAR, VARCHAR 혹은 TEXT 타입의 컬럼에서만 생성할 수 있습니다.

 

 

✔️ Parser

MySQL은 빌트인(내장)된 ngram parser를 지원하며, 중국어와 일본어 그리고 한글(CJK)를 지원합니다.

또한, 일본어를 위한 플러그인 파서인 MeCab을 설치할 수도 있습니다.

ngram 파서는 이후 포스팅에서 자세히 다루겠습니다 🙌🏻

 

 

✔️ Load Large Data

대규모 데이터셋의 경우에는 인덱스가 걸린 채 로드하는 것보다,

FULLTEXT가 없는 상태로 로딩하고 인덱스를 거는게 훨씬 더 빠릅니다. 

 

 

 

:: Search Types

MySQL은 자연어 검색, 불린 모드 검색, 쿼리 확장 검색의 3가지 종류의 FULLTEXT 검색 방식을 지원합니다.

지금부터 이 세가지 검색 타입에 대해 알아보겠습니다.

 

Natural Language Searches

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

 

자연어 검색 기본 검색 타입으로,

MATCH ... AGAINST 구문에 별도의 옵션을 지칭하지 않으면 자연어 검색 모드로 검색하게 됩니다.

혹은 아래와 같이 AGAINST에 명시적으로 표시할 수 있습니다.

 

mysql> SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

 

 

✔️ 매치율

입력된 검색어의 키워드가 얼마나 더 많이 포함되어 있는지에 따라 매치율(유사성 측정값)이 결정 되는데
전체 테이블의 50% 이상의 레코드가 검색된 키워드를 가지고 있다면,

그 키워드는 검색어로서 의미가 없다고 판단하고 검색 결과에서 배제 시키게 됩니다.

이 때 매치율이 계산될 때는row내의 고유 단어 수, 총 단어 수, 특정 단어를 포함하는 row 수 등을 기준으로 계산됩니다.

 

검색 결과는 가장 높은 관련성을 가진 결과부터 자동 정렬되는데,

아래와 같은 조건에 한해 자동 정렬합니다.

 

- ORDER BY 절이 없어야 합니다.

- 검색은 테이블 검색이 아닌 FULLTEXT Index를 사용하여 수행해야 합니다.

- 쿼리가 테이블을 조인하는 경우, FULLTEXT Index는 조인에서 가장 왼쪽에 있는 non-constant 테이블이어야 합니다.

 

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

 

mysql> SELECT match(description) AGAINST('Epic') AS score FROM film LIMIT 0, 10;
+--------------------+
| score              |
+--------------------+
| 0.5844167470932007 |
| 0.5844167470932007 |
|                  0 |
|                  0 |
|                  0 |
| 0.5844167470932007 |
|                  0 |
| 0.5844167470932007 |
|                  0 |
|                  0 |
+--------------------+
10 rows in set (0.00 sec)

 

✔️ 대소문자

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

 

 

✔️  무시되는 검색어

길이가 기준보다 짧거나, 특정 단어(Stopword)는 풀텍스트 검색에서 무시됩니다.

 

👉🏻 너무 짧은 단어

단어의 기본 길이로 지정된 길이보다 짧을 경우 무시됩니다.

기본 길이는 아래와 같이 확인할 수 있습니다.

 

mysql> show variables like '%ft_min%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| ft_min_word_len          | 4     |
| innodb_ft_min_token_size | 3     |
+--------------------------+-------+
2 rows in set (0.02 sec)

 

ft_min_word_len가 기본값인 4로 지정되어 있는 것을 확인할 수 있습니다.

 

👉🏻 Stopword

Stopword 로 지정된 단어는 무시됩니다.

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

 

mysql> select * from INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
...

 

 

 

Boolean Searches

 

불린 모드 검색은 문자열을 단어 단위로 분리한 후, 추가적인 검색 규칙을 적용되어서 단어가 포함되는 행을 찾습니다.

불린 모드 검색은 IN BOOLEAN MODE를 지정해서 검색할 수 있습니다.

 

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

 

위의 검색은 "MySQL"은 추가하되, "YourSQL"은 포함하지 않는 검색 규칙을 적용해서 검색하는 구문입니다.
따라서 쿼리는 "MySQL" 단어를 포함하지만 "YourSQL" 단어를 포함하지 않는 모든 행을 검색합니다.

 

InnoDB 엔진을 사용하면 MATCH() 식의 모든 열에 FULLTEXT 인덱스가 필요하지만, 

MyISAM 검색 인덱스에 대한 부울 쿼리는 FULLTEXT 인덱스가 없어도 작동할 수 있습니다.

하지만 실행되는 검색은 상당히 느립니다.

 

이 때, 아래와 같은 기호로 검색 조건을 추가할 수 있습니다.

 

Operator

Operator	Description
+	AND, 반드시 포함하는 단어
–	NOT, 반드시 제외하는 단어
>	포함하며, 검색 순위를 높일 단어 +mysql >tutorial : mysql과 tutorial가 포함하는 행을 찾을 때, tutorial이 포함되면 검색 랭킹이 높아짐
<	포함하되,검색 순위를 낮출 단어 +mysql <training : mysql과 training가 포함하는 행을 찾지만, training이 포함되면 검색 랭킹이 낮아짐
()	하위 표현식으로 그룹화 (포함, 제외, 순위 지정 등) +mysql +(>tutorial <training) : mysql AND tutorial, mysql AND training 이지만, tutorial의 우선순위가 더욱 높게 지정
~	Negate.  '-' 연산자와 비슷하지만 제외 시키지는 않고 검색 조건을 낮춤
*	Wildcard. 와일드카드 my* : mysql, mybatis 등 my 뒤의 와일드 카드로 붙음
“”	구문 정의

 

 

with Query Expansion

쿼리 확장 검색은 자연어 검색을 확장한 내용으로, 2단계에 걸쳐서 검색을 수행합니다.

첫 단계에서는 자연어 검색을 수행한 후,

첫 번째 검색의 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두 번째 검색을 수행합니다.

 

쿼리 확장 검색은 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION,

혹은WITH QUERY EXPANSION  한정자를 사용합니다.

사용 예시는 아래에서 확인하실 수 있습니다.

 

쿼리 확장 검색은 일반적으로 검색 구문이 아주 짧을 때 유용합니다.

사용자가 전체 텍스트 검색 엔진에 없는 "자연어 연관 내용"이라는 "암묵적인 지식(아래 설명 참고)"으로 나오는 결과들입니다.

 

아래 예시로 설명을 더해보겠습니다.

 

mysql> SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' WITH QUERY EXPANSION);
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  3 | Optimizing MySQL      | In this tutorial we show ...             |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
+----+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)

예를 들어, "데이터베이스"를 검색하는 사용자는 "MySQL", "Oracle", "DB2" 및 "RDBMS"가 모두 "데이터베이스"와 연관되어 있기 때문에 반환되는 것을 확인할 수 있습니다.

그래서 위에서 말한 암묵적인 지식이 바로, 데이터베이스와 관련된 "MySQL", "Oracle", "DB2" 라는 내용을 의미합니다.

 

혹은, 사용자가 철자를 잘 모를 때, "Georges Simenon"의 "Maigret"이라는 책을 검색한다고 가정해봅시다.
쿼리 확장 없이 "Megre and the reluctant witnesses"을 검색하면 "Maigret and the Reluctant Witnesses"만을 찾을 수 있습니다.

만약 쿼리 확장을 사용한다면, 두 번째 검색을 할 때 에서 오타로 추측된 단어인 "Maigret"이라는 단어가 포함된 모든 책을 찾을 수 있습니다.

 

 

 

 

 

그럼 지금까지 FullText 검색에 대한 내용을 살펴보았습니다.

오타나 잘못된 내용을 댓글로 남겨주세요!

감사합니다 ☺️ 

 

 

참고: https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html