MySQL FTS(Full Text Search)

Full Text Search

게시글의 내용 검색 시 키워드를 사용해 검색을 하게 된다.
제목과 달리 내용에는 매우 긴 문자열을 포함하기 때문에 검색 성능을 높이기 위해서 인덱스를 사용할 것이다.

여기서 한 가지 주의해야할 점은 like 검색을 할 때는 단어% 형식(해당 단어가 접두사)일때만 인덱스 검색이 가능하다는 것이다.

어떤 단어를 검색하기 위해서는 %단어% 형식으로 검색을 하게 되는데 이와 같은 형식으로 검색을 한다면 인덱스를 사용하지 않고 검색하게 된다.

이와 같은 문제를 해결하기 위해서 Full Text Search가 존재한다.

• Full Text Search는 InnoDB와 MyISAM 엔진에서만 지원한다.
• FULLTEXT 인덱스는 text, char, varchar 타입 컬럼에만 생성 가능
• 인덱스 힌트의 사용이 일부 제한된다
• 여러 개의 컬럼에 FULLTEXT 인덱스를 지정할 수 있다.

Full Text 인덱스는 일반적인 인덱스와 달리 B-Tree 구조를 사용하지 않는다. 대신 역색인(inverted index) 방식으로 인덱스를 저장한다.

Create FULLTEXT 인덱스

-- 테이블 생성과 동시에 FULLTEXT 인덱스 생성
CREATE TABLE 테이블명(
    컬럼명1 데이터타입,
    컬럼명2 데이터타입,
    ...
    FULLTEXT 인덱스명 (컬럼명)
);

-- ALTER로 존재하는 테이블에 FULLTEXT 인덱스 추가. 
CREATE TABLE 테이블명(
    컬럼명1 데이터타입,
    컬럼명2 데이터타입,
    ... 
);

ALTER TABLE 테이블명
    ADD FULLTEXT (컬럼명);

-- CREATE로 존재하는 테이블에 FULLTEXT 인덱스 추가.(따로 인덱스명을 정의)
CREATE TABLE 테이블명(
    컬럼명1 데이터타입,
    컬럼명2 데이터타입,
    ... 
);

CREATE FULLTEXT INDEX 인덱스명
    ON 테이블명 (컬럼명);

Drop FULLTEXT 인덱스

ALTER TABLE 테이블명
    DROP INDEX FULLTEXT (컬럼명);

중지 단어

FULLTEXT 인덱스는 긴 문장에 대해 인덱스를 생성하기 때문에 그 양이 커질 수밖에 없다. 그러므로 검색에서 중요하지 않은 단어들은 아예 FULLTEXT 인덱스로 만들지 않는 것이 좋다.

문장 : 이번 선거는 아주 중요한 행사므로 모두 꼭 참여 바랍니다.
형태소 : 이번, 선거는, 아주, 중요한, 행사므로, 모두, 꼭, 참여, 바랍니다

문장을 형태소로 쪼개었을 때, 중요하지 않은 단어들은 FULLTEXT 인덱스 생성에서 제외하는 것이 좋다.
위 문장에서는 이번, 아주, 모두, 꼭 과 같은 단어들로는 검색할 이유가 없으므로 FULLTEXT 인덱스 생성에서는 제외시킨다.
이와 같은 단어들을 중지 단어라고 한다.

MySQL 8.0에서는 information_schema.innodb_ft_default_stopword에 36개의 중지 단어를 포함한다. (영어 단어. a, an, the 등)

사용자가 필요하다면 별도의 테이블에 중지 단어를 추가한 후 적용시킬 수도 있다. FULLTEXT 인덱스 생성 시 이 중지 단어들을 적용시키면 MySQL은 중지 단어들을 제외하고 인덱스를 만들어 주게 된다.
=> 전체 텍스트 인덱스의 크기를 최소화 시키는 효과

FTS를 위한 쿼리

SELECT ~ WHERE에서 MATCH(), AGAINST()를 사용하여 FTS 쿼리가 가능하다.

MATCH(컬럼명1, 컬럼명2, ...) AGAINST(expr [search_modifier])

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

자연어 검색

특별히 옵션을 지정하지 않거나, IN NATURAL LANGUAGE MODE 옵션을 주는 경우 자연어 검색을 한다. 자연어 검색은 단어가 정확한 것을 검색해 준다.

신문(newspaper) 테이블의 기사(article) 컬럼에 FULLTEXT 인덱스가 생성되어 있다고 가정하자.
'영화' 라는 단어가 들어간 기사를 찾기 위해 다음과 같이 쿼리를 작성한다.

SELECT * FROM newspaper
    WHERE MATCH(article) AGAINST('영화');

이때 주의할 점은 '영화' 라는 단어를 정확하게 검색하기 때문에 '영화는', '영화가', '한국영화' 등의 단어가 들어간 컬럼은 검색하지 않는다.

'영화' 또는 '배우'와 같이 두 단어 중 하나가 포함된 기사를 찾으려면 다음과 같이 사용한다.

SELECT * FROM newspaper
    WHERE MATCH(article) AGAINST('영화 배우');

불린 모드 검색

불린 모드 검색은 정확히 일치하지 않는 것도 검색을 한다. 사용을 하려면 IN BOOLEAN MODE 옵션을 줘야 한다.

• + : 필수 연산자
• - : 제외 연산자
• * : 부분 검색 연산자

'영화를', '영화가', '영화는' 과 같이 '영화' 가 앞에 들어간 모든 결과를 검색하려면 다음과 같이 검색한다.

SELECT * FROM newspaper
    WHERE MATCH(article) AGAINST('영화*' IN BOOLEAN MODE);

'영화 배우' 단어가 정확히 들어 있는 기사 내용을 검색하고 싶다면 다음과 같이 사용한다.

SELECT * FROM newspaper
    WHERE MATCH(article) AGAINST('영화 배우' IN BOOLEAN MODE);

'영화 배우' 단어가 정확히 들어 있는 기사 중 '공포'의 내용이 꼭 들어간 결과만 검색하고 싶다면 다음과 같이 사용한다.
필수 단어 앞에 필수 연산자인 +를 붙인다.

SELECT * FROM newspaper
    WHERE MATCH(article) AGAINST('영화 배우 +공포' IN BOOLEAN MODE);

'영화 배우' 단어가 들어 있는 기사 중 '남자'의 내용은 검색에서 제외하고 싶다면 다음과 같이 사용한다.

SELECT * FROM newspaper
    WHERE MATCH(article) AGAINST('영화 배우 -남자' IN BOOLEAN MODE);

설정 변경

SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

위 변수는 FULLTEXT 검색을 위한 단어의 최소 토큰 크기를 담고 있다.
기본 값은 3으로, 2글자 이하의 단어 검색이 불가능하기 때문에 설정 값을 바꿔주어야 한다.

이를 위해 my.ini 파일에서 해당 값을 바꾸고 MySQL을 재부팅해야 한다.
링크를 참고하여 my.ini를 수정하는데, my.ini 파일 내에 innodb_ft_min_token_size과 관련된 설정이 존재하지 않기 때문에 가장 마지막 줄에 다음과 같이 추가한다.

innodb_ft_min_token_size = 2

FTS 예시

CREATE DATABASE IF NOT EXISTS FulltextDB;
use FulltextDB;

CREATE TABLE FulltextTbl(
    id INT AUTO_INCREMENT PRIMARY KEY, -- 고유 번호
    title VARCHAR(15) NOT NULL, -- 영화 제목
    description VARCHAR(1000) -- 영화 내용 요약
);

INSERT INTO FulltextTbl
    VALUES (NULL, '광해, 왕이 된 남자', '왕위를 둘러싼 권력 다툼과 당쟁으로 혼란이 극에 달한 광해군 8년'),
    (NULL, '간첩', '남한 내에 고정간첩 5만 명이 암약하고 있으며 특히 권력 핵심부에도 침투해있다.'),
    (NULL, '남자가 사랑할 때', '대책 없는 한 남자이야기. 형 집에 얹혀 살며 조카에게 무시당하는 남자'),
    (NULL, '레지던트 이블 5', '인류 구원의 마지막 퍼즐, 이 여자가 모든 것을 끝낸다.'),
    (NULL, '파괴자들', '사랑은 모든 것을 파괴한다! 한 여자를 구하기 위한, 두 남자의 잔인한 액션 본능!'),
    (NULL, '킹콩을 들다', '역도에 목숨을 건 시골소녀들이 만드는 기적 같은 신화'),
    (NULL, '테드', '지상 최대 황금 찾기 프로젝트! 500년 전 사라진 황금도시를 찾아라!'),
    (NULL, '타이타닉', '비극 속에 침몰한 세기의 사랑, 스크린에 되살아날 영원한 감동'),
    (NULL, '8월의 크리스마스', '시한부 인생 사진사와 여자 주차 단속원 사이의 미묘한 사랑'),
    (NULL, '늑대와 춤을', '늑대와 친해져 모닥불 아래서 함께 춤을 추는 전쟁 영웅 이야기'),
    (NULL, '국가대표', '동계올림픽 유치를 위해 정식 종목인 스키점프 국가대표팀이 급조된다.'),
    (NULL, '쇼생크 탈출', '그는 누명을 쓰고 쇼생크 감옥에 감금된다. 그리고 역사적인 탈출'),
    (NULL, '인생은 아름다워', '귀도는 삼촌의 호텔에서 웨이터로 일하면서 또 다시 도라를 만난다.'),
    (NULL, '사운드 오브 뮤직', '수녀 지망생 마리아는 명문 트랩가의 가정교사로 들어간다'),
    (NULL, '매트릭스', '2199년. 인공 두뇌를 가진 컴퓨터가 지배하는 세계.');

CREATE FULLTEXT INDEX idx_description ON FulltextTbl (description);

위와 같이 DB, 테이블 생성 후 테이블에 데이터를 삽입했다. 그리고 FulltextTbl의 description 컬럼에 FULLTEXT 인덱스를 생성하였다.

성능 비교

일반적인 like문을 사용해 검색하는 경우 인덱스를 사용하지 않고 전체 테이블을 스캔한다. (접두사 검색은 인덱스 사용이 가능하지만, 접미사 혹은 가운데 단어 검색 시 인덱스 사용 x)

SELECT * FROM FulltextTbl WHERE description like '%남자%';

쿼리 실행 후 Execution plan에서 위와 같은 다이어그램을 볼 수 있다. Full Table Sacn으로 테이블 내부의 모든 행을 읽은 것을 확인할 수 있다.

SELECT * FROM FulltextTbl
    WHERE MATCH(description) AGAINST('남자*' IN BOOLEAN MODE);

이번에는 Fulltext 인덱스를 사용하여 검색이 완료된 것을 확인할 수 있다.

query cost를 비교했을 때도 1.75 -> 0.35로 큰 감소를 보였다.
(query cost는 상대적인 지표이며 실제 실행 시간과는 차이가 있다. 그러나 일반적으로 query cost가 낮을 수록 실제 실행 시간도 적을 확률이 높다. 현재 테이블에 데이터가 적으므로 실행 시간의 차이가 나지 않기 때문에 query cost로 비교)

점수 측정

MATCH(), AGAINST()를 컬럼으로 선택해 점수를 확인할 수 있다.
FULLTEXT SEARCH에서 높은 연관도를 가지는 row일수록 높은 점수를 보인다.

일반적으로 FTS 측정 알고리즘으로 TF-IDF (Term Frequency-Inverse Document Frequency)을 사용한다. TF 지표는 문서 내에서 특정 단어가 얼마나 많이 등장하는 지에 대해서 높은 점수를 매기고, IDF 지표는 문서 내 단어 비율이 적을 수록 높은 점수를 매긴다. 그리고 지표들 간의 점수를 취합한 후 문서 길이에 따라 정규화를 시켜서 최종적인 점수를 획득한다.

SELECT *, MATCH(description) AGAINST('남자* 여자*' IN BOOLEAN MODE) AS 점수
    FROM FulltextTbl
    WHERE MATCH(description) AGAINST('남자* 여자*' IN BOOLEAN MODE);

위 쿼리에서는 description 컬럼에 '남자' 혹은 '여자' 가 들어간 행을 찾고 점수와 함께 반환한다.

결과는 위와 같다. id가 5인 행은 '남자'와 '여자'를 모두 포함하고 있기 때문에 가장 높은 점수를 획득했다.

검색

SELECT * FROM FulltextTbl
WHERE MATCH(description) AGAINST('+남자* +여자*' IN BOOLEAN MODE);

위와 같이 쿼리를 하는 경우에는 단어 각각에 필수 연산자 +를 붙이고 있기 때문에 description 컬럼에 '남자'와 '여자' 단어가 모두 포함된 행들을 검색하게 된다.

SELECT * FROM FulltextTbl
WHERE MATCH(description) AGAINST('남자* -여자*' IN BOOLEAN MODE);

위와 같이 쿼리를 하는 경우에는 '여자'라는 단어에 제외 연산자 -를 붙이고 있기 때문에 description 컬럼에 '남자'를 포함하지만 '여자'는 포함하지 않는 행들을 검색하게 된다.

FULLTEXT 인덱스 단어 확인

-- 검색 전 반드시 SET GLOBAL innodb_ft_aux_table =  'DB명/테이블명';
-- 검색하려는 DB와 테이블을 설정
SET GLOBAL innodb_ft_aux_table =  'fulltextdb/fulltexttbl'; -- 모두 소문자
SELECT word, doc_count, doc_id, position
    FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

FULLTEXT 인덱스가 만들어진 단어들을 조회하기 위해 먼저 DB와 테이블을 지정하고 위와 같이 쿼리를 하여 조회할 수 있다.

중지 단어 설정

중지 단어 설정을 위해서 테이블을 만들어야 한다.
테이블 내부의 컬럼명과 데이터 타입은 반드시 형식을 따라야 한다. (형식을 따르지 않는 경우 오류 발생)

https://dev.mysql.com/doc/refman/8.4/en/fulltext-stopwords.html#fulltext-stopwords-stopwords-for-innodb-search-indexes

CREATE TABLE user_stopword(
    value varchar(30)); -- 컬럼명 소문자 value, 데이터타입 varchar(30)

INSERT INTO user_stopword VALUES 
    ('그는'), ('그리고'), ('극에'); -- 중지 단어 삽입

그리고 다음과 같이 innodb_ft_server_stopword 글로벌 변수에 stopword 테이블을 등록하면 된다.

SET GLOBAL innodb_ft_server_stopword_table='fulltextdb/user_stopword';
-- 모두 소문자
SHOW GLOBAL VARIABLES LIKE 'innodb_ft_server_stopword_table'; 
-- 확인 쿼리

중지 단어 테이블은 GLOBAL 변수이기 때문에 DB 서버 내부의 모든 DB들에 통합적으로 적용되며, 하나의 테이블만 등록할 수 있다.

기존에 생성해둔 인덱스에는 자동으로 적용되지 않기 때문에, FULLTEXT 인덱스를 삭제하고 재생성해야 한다.

DROP INDEX idx_description ON FulltextTbl; -- 기존 FULLTEXT 인덱스 삭제
CREATE FULLTEXT INDEX idx_description ON FulltextTbl(description);
-- FULLTEXT 인덱스 새로 생성
SELECT word, doc_count, doc_id, position
    FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; -- 확인

기존의 인덱스를 제거하고 재성성한 결과 중지 단어를 제외한 FULLTEXT 인덱스가 생성된다.