전체 텍스트 검색
전체 텍스트 검색 개요
전체 텍스트 검색은 긴 문자로 구성된 구조화되지 않은 텍스트 데이터(예로, 신문 기사) 등을 빠르게 검색하기 위한 부가적인 MySQL의 기능이다.
전체 텍스트 검색은 저장된 텍스트의 키워드 기반의 쿼리를 위해서 빠른 인덱싱이 가능하다.
[그림 11-1] 신문기사 테이블과 신문기사 인덱스 개념도
[그림 11-1]과 같은 데이터가 들어 있는 테이블과 인덱스를 생각해 보자. (신문기사 내용 중 ~~~부분은 여기서 생략된 것이지, 실제는 모두 들어 있다고 생각하자.)
[그림 11-1]의 테이블 구조는 아마도 다음과 같을 것이다. 내용은 필자가 설명을 위해서 억지로 만든 것이므로 세부적인 것은 무시하자.
신문기사를 계속 입력할 때마다 테이블의 크기 및 인덱스의 크기가 커질 것이다.
이제 신문기사를 검색해 보자.
위와 같이 검색하면 당연히 인덱스를 잘 사용하게 될 것이다. 그런데, 신문기사의 내용을 모두 안다면 위와 같은 검색을 할 이유가 없다. 그래서 아마도 chapter 06에서 배운 LIKE 검색을 사용하게 될 것이다.
즉, 교통과 관련된 신문기사를 검색하려면 다음과 같이 사용하면 된다.
이렇게 검색하면 인덱스가 정렬되어 있으므로, 해당되는 내용이 인덱스를 통해서 빠르게 검색된다. 아마도 결과는 2022.10.19일자 기사가 검색될 것이다.
그런데, 문제는 2020.5.5일자 기사도 '교통'과 관련된 기사라는 점이다. 그래서, 앞에 들어 있든지 중간에 들어 있든지 모든 '교통'과 관련된 내용을 검색하려면 다음과 같이 사용하면 된다.
이렇게 하면 '교통'이라는 글자가 들어간 모든 기사(이 예에서는 2022.10.19일자, 2020.5.5일자 2건)가 검색될 것이다. 그런데, 문제는 이렇게 되면 인덱스를 사용할 수 없다는 점이다.
2020.5.5일자의 '교통'은 중간에 들어 있으므로 인덱스를 사용할 방법이 없으므로 당연히 MySQL은 테이블 검색(전체 테이블을 읽는 것)을 하게 된다. 만약, 10년치 기사 중에서 검색했다면 MySQL서버는 엄청난 부하가 발생되고 그 응답시간도 어쩌면 수 분~수 시간이 걸릴 지도 모르겠다.
전체 텍스트 검색은 이러한 문제를 해결해 준다. 즉, 전체 텍스트 검색은 첫 글자뿐 아니라, 중간의 단어나 문장으로도 인덱스를 생성해 주기 때문에 지금과 같은 상황에서도 인덱스(정확히는 전체 텍스트 인덱스)를 사용할 수 있어 순식간에 검색 결과를 얻을 수 있다.
전체 텍스트 인덱스
전체 텍스트 인덱스(FULLTEXT Index) 생성
전체 텍스트 인덱스는 신문기사와 같이, 텍스트로 이루어진 문자열 데이터의 내용을 가지고 생성한 인덱스를 말한다. MySQL에서 생성한 일반적인 인덱스와는 몇 가지 차이점이 있다.
- 전체 텍스트 인덱스는 InnoDB와 MyISAM 테이블만 지원한다.
- 전체 텍스트 인덱스는 char, varchar, text의 열에만 생성이 가능하다.
- 인덱스 힌트의 사용이 일부 제한된다.
- 여러 개 열에 FULLTEXT 인덱스를 지정할 수 있다.
전체 텍스트 인덱스를 생성하는 방법은 3가지가 있다. 우선 형식을 살펴보자.
전체 텍스트 인덱스(FULLTEXT Index) 삭제
전체 텍스트 인덱스를 삭제하는 방법은 일반 인덱스와 비슷한 방법으로 ALTER TABLE...DROP INDEX문을 사용한다.
중지 단어
전체 텍스트 인덱스는 긴 문장에 대해서 인덱스를 생성하기 때문에 그 양이 커질 수밖에 없다. 그러므로, 실제로 검색에서 무시할 만한 단어들은 아예 전체 텍스트 인덱스로 생성하지 않는 편이 좋을 것이다. 전체 텍스트 인덱스 생성 시에 다음과 같은 경우를 생각해 보자.
위와 같은 문장에서 전체 텍스트 인덱스를 만든다면 '이번', '아주', '모두', '꼭' 등과 같은 단어로는 검색할 이유가 없으므로 제외시키는 것이 좋다. 이것이 중지 단어다.
MySQL 8.0은 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 테이블에 36개의 중지 단어를 가지고 있다.
전체 텍스트 검색을 위한 쿼리
전체 텍스트 인덱스를 생성한 후에, 전체 텍스트 인덱스를 이용하기 위한 쿼리는 일반 SELECT문의 WHERE절에 MATCH() AGAINST()를 사용하면 된다.
MySQL 도움말에 나온 형식은 다음과 같다.
약간 복잡해 보이지만 사용은 별로 어렵지 않다. 우선 기본적으로 기억할 사항은 MATCH() 함수는 WHERE절에서 사용한다는 점이다.
-
자연어 검색
특별히 옵션을 지정하지 않거나 IN NATURAL LANGUAGE MODE를 붙이면 자연어 검색을 한다. 자연어 검색은 단어가 정확한 것을 검색해 준다.
예로, 신문이라는 테이블의 기사라는 열에 전체 텍스트 인덱스가 생성되어 있다고 가정해 보자
'영화'라는 단어가 들어간 기사를 찾으려면 다음과 같이 사용한다.
여기서는 '영화'라는 정확한 단어만 검색되며 '영화는', '영화가', '한국영화'등의 단어가 들어간 열은 검색하지 않는다.
'영화'또는 '배우'와 같이 두 단어 중 하나가 포함된 기사를 찾으려면 다음과 같이 사용한다.
-
불린 모드 검색
단어나 문장이 정확히 일치하지 않는 것도 검색하는 것을 말하는데 IN BOOLEAN MODE 옵션을 붙여줘야 한다. 또한 불린 모드 검색은 필수인 '+', 제외하기 위한 '-', 부분 검색을 위한 '*' 연산자 등의 다양한 연산자를 지원한다.
'영화를','영화가','영화는'등의 '영화'가 앞에 들어간 모든 결과를 검색하고 싶다면 다음과 같이 사용한다.
'영화 배우'단어가 정확히 들어 있는 기사의 내용을 검색하고 싶다면 다음과 같이 사용한다.
정확히 '영화 배우'단어가 들어간 결과만 출력된다.
'영화 배우'단어가 들어가 있는 기사 중에서 '공포'의 내용이 꼭 들어간 결과만 검색하고 싶다면 다음과 같이 사용한다.
'영화 배우'가 들어간 결과 중에서 '공포'가 들어간 것만 검색된다.
'영화 배우'단어가 들어 있는 기사 중에서 '남자'의 내용은 검색에서 제외하고 싶다면 다음과 같이 사용한다.
결과 중에서 남자가 들어간 결과는 제외된다.
<실습>
전체 텍스트 검색을 실습해 보자.
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
3글자 이상인 것만 검색이 된다는 뜻.
2글자 검색하려면 my.ini 파일을 변경해야 한 후 MySQL을 재부팅 해야한다.
innodb_ft_min_token_size=2 를 맨 아랫줄에 추가 후 저장.
MySQL 재시작
CREATE DATABASE IF EXISTS FulltextDB;
USE FulltextDB;
DROP TABLE IF EXISTS FulltextTbl;
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년.인공 두뇌를 가진 컴퓨터가 지배하는 세계.');
SELECT * FROM FulltextTbl WHERE description LIKE '%남자%';
SELECT * FROM FulltextTbl WHERE description LIKE '%남자%';의 결과임
FULL TABLE SCAN한 것을 알 수 있음.
CREATE FULLTEXT INDEX idx_description ON FulltextTbl(description);
SHOW INDEX FROM FulltextTbl;
SELECT * FROM FulltextTbl WHERE MATCH(description) AGAINST('남자*' IN BOOLEAN MODE);
Fulltext Index를 사용해서 검색한 것을 확인할 수 있음.
SELECT *, MATCH(description) AGAINST('남자* 여자*' IN BOOLEAN MODE) AS 점수 FROM FulltextTbl WHERE MATCH(description) AGAINST('남자* 여자*' IN BOOLEAN MODE);
남자, 여자 하나만 있어도 검색o
SELECT * FROM FulltextTbl WHERE MATCH(description) AGAINST('+남자* +여자*' IN BOOLEAN MODE);
남자, 여자 모두 들어있는 것만 검색 가능
SELECT * FROM FulltextTbl WHERE MATCH(description) AGAINST('남자* -여자*' IN BOOLEAN MODE);
남자가 들어있는 것중에 여자가 들어있는 것을 제외한 것 검색
SET GLOBAL innodb_ft_aux_table = 'fulltextdb/fulltexttbl'; -- 모두 소문자
SELECT word, doc_count, doc_id, position
FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
DROP INDEX idx_description ON FulltextTbl;
CREATE TABLE user_stopword (value VARCHAR(30));
INSERT INTO user_stopword VALUES ('그는'), ('그리고'), ('극에'); -- 중지 단어로 추가
SET GLOBAL innodb_ft_server_stopword_table = 'fulltextdb/user_stopword'; -- 모두 소문자
SHOW GLOBAL VARIABLES LIKE 'innodb_ft_server_stopword_table';
CREATE FULLTEXT INDEX idx_description ON FulltextTbl(description);
SELECT word, doc_count, doc_id, position
FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
