About FULLTEXT Index

개요

MySQL에서 문자열 검색시 LIKE 와 같은 연산자를 통해 원하는 텍스트를 필터링하여 조회한다.

SELECT 이름 FROM 사원 WHERE 이름 LIKE '%가%';

그러나 검색할 텍스트 건수가 많을 경우 과부하, 응답시간 증가 등의 성능 저하를 유발할 수 있다.

이 문제는 전체 텍스트 검색 기능을 통해 해결할 수 있다. 전체 텍스트 검색은 첫 글자뿐 아니라 중간 단어, 문장으로도 인덱스를 생성해주기 때문에, 전체 텍스트 인덱스를 통해 효율적으로 검색 결과를 도출할 수 있다.

풀텍스트 인덱스

전체 텍스트 검색은 긴 문자의 텍스트 데이터를 빠르게 검색하기 위한 MySQL이 부가기능이다. 이 인덱스도 결국 인덱스 종류의 한 가지이다.

일반 인덱스와의 차이는, 풀텍스트 인덱스는 긴 문장 전체를 대상으로 인덱싱을 하며, InnoDB/MyISAM 스토리지 엔진만 지원하고, CHAR , VARCHAR , TEXT 타입만 인덱싱을 할 수 있다. 또한 여러 개의 열에 풀텍스트 인덱스를 지정할 수 있다.

풀텍스트 인덱스 사용

풀텍스트 인덱스 생성 방식

CREATE TABLE <table_name> (
	-- ...
	<col_name> <data_type>,
	-- ...
	FULLTEXT <index_name> <col_name>
);

CREATE FULLTEXT INDEX <index_name> ON <table_name> (col_name);

ALTER TABLE <table_name> ADD FULLTEXT (col_name);

풀텍스트 인덱스 삭제

ALTER TABLE <table_name> DROP INDEX FULLTEXT <col_name>;

DROP INDEX <index_name> ON <table_name>;

풀텍스트 인덱스 확인

SHOW INDEX FROM <table_name> -- 전체 인덱스 확인으로 가능

전체 텍스트 검색

전체 텍스트 검색을 하기 위해선 쿼리의 WHERE 절에 MATCH(), AGAINST() 와 같은 특수한 메서드를 이용해야 한다.

예시 테이블

CREATE TABLE test_table(
	id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(15) NOT NULL,
    description VARCHAR(1000)
);

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

자연어 검색

특별히 옵션을 지정하지 않거나 뒤에 in natural language mode 를 붙이면 자연어 검색을 한다. 자연어 검색은 단어가 정확한 것을 검색해준다.

SELECT * FROM test_table WHERE MATCH(description) AGAINST('영화');

SELECT * FROM test_table WHERE MATCH(description) AGAINST('영화' in natural language mode);

SELECT * FROM test_table WHERE MATCH(description) AGAINST('영화 배우');
-- '영화' 또는 '배우' 두 단어 중 하나가 포함된 description 검색

‘영화’라는 정확한 단어만 검색되며 ‘영화는’, ‘영화가’ 등 능동적인 검색이 불가하다.

불린 모드 검색

불린 모드 검색은 단어, 문장이 정확하게 일치하지 않는 것도 검색하는 것을 의미한다. 뒤에 in boolean mode 를 붙여주면 적용된다. 이 검색은 필수인 + , 제외하기 위한 - , 부분 검색을 위한 * 연산자 등의 다양한 연산자를 지원한다.

+(검색 필수)

SELECT * FROM test_table
WHERE MATCH(description) 
AGAINST('영화+액션' IN BOOLEAN MODE)

영화를 찾되 반드시 액션이 들어가 있는 칼럼

-(검색 제외)

SELECT * FROM test_table
WHERE MATCH(description) 
AGAINST('영화-액션' IN BOOLEAN MODE)

영화를 찾되 액션이 포함되지 않은 칼럼

~(검색 부정)

SELECT * FROM test_table
WHERE MATCH(description)
AGAINST('영화~액션' IN BOOLEAN MODE)

영화를 찾되 액션이 없는 칼럼이 액션이 있는 칼럼보다 아래 순위

*(부분 검색)

SELECT * FROM test_table
WHERE MATCH(description)
AGAINST('영화*' IN BOOLEAN MODE)

영화를’, ‘영화가’ 등 포함 검색

“(부분 검색 “” 내에 있는 구문과 정확인 동일한 철자의 구문)

SELECT * FROM test_table
WHERE MATCH(description)
AGAINST("재밌는 영화" IN BOOLEAN MODE)

”재밌는 영화”, “재밌는 영화가” 등 가능
”재밌는 한국 영화” 등 불가능

예시 쿼리

-- 님자가 포함된 행을 찾고 여자가 포함되어 있다면 상위행으로 찾기
SELECT * FROM test_table
WHERE MATCH(description) AGAINST('+남자* 여자*' IN BOOLEAN MODE);

-- 여자가 포함된 행을 찾고 남자가 있으면 상위 행으로 찾기
SELECT * FROM test_table 
WHERE MATCH(description) AGAINST('남자* +여자*' IN BOOLEAN MODE);

-- 딱 '남자' 가 포함된 행중에서 여자가 포함된 행만 찾기
SELECT * FROM test_table 
WHERE MATCH(description) AGAINST('남자 +여자' IN BOOLEAN MODE);    

-- 딱 '남자' 가 포함된 행중에서 여자가 있으면 상위행으로, 남자만 있는것도 찾기
SELECT * FROM test_table 
WHERE MATCH(description) AGAINST('+남자 여자' IN BOOLEAN MODE);

-- 남자로 검색된 영화 중에서 여자가 들어간 영화는 제거하는 검색
SELECT * FROM test_table 
WHERE MATCH(description) AGAINST('남자* -여자*' IN BOOLEAN MODE);

쿼리 확장 검색

다음 두 단계에 걸쳐서 검색을 수행하는 방식이다. 자연어 검색을 먼저 수행한 후, 첫 검색 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두번째 문자열 검색을 수행한다. 1단계 검색에서 사용한 단어와 연관성 있는 단어가 1단계 검색에 매칭된 결과에 나타난다는 가정을 전제로 한다.

SELECT * FROM test_table
WHERE MATCH(description)
AGAINST('내용*' WITH QUERY EXPANSION);

검색 단어 제한 수 풀기

MySQL은 기본값으로 검색 가능 단어의 숫자가 3이다. 즉, 3글자 이상만 전체 텍스트 검색이 되고 두 글자 단어는 안된다는 말이다. 따라서 검색 가능 단어의 숫자를 확인하고 설정에서 수정해주어야 한다.

SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

my.ini 수정

아래 그림 경로의 my.ini 파일에서 맨 아래 innodb_ft_min_token_size 를 추가해주고 MySQL을 재시작해주면 적용이 완료된다.

중지 단어(stopwords)

전체 텍스트 인덱스는 긴 문장에 대해 인덱스를 생성하기 때문에 그 양이 커질 수밖에 없다. 그러므로 실제 검색에서 무시할 만한 단어들은 전체 텍스트 인덱스로 생성하지 않는 것이 좋다.

이번 행사는 아주 중요한 행사이므로 모두 필참 바랍니다.

위 문장을 전체 텍스트 인덱스를 통해 끊으면 다음과 같이 된다.

이번	행사는	아주	중요한	행사이므로	모두	필참	바랍니다.

여기서 ‘이번’,’아주’,’모두’ 등과 같은 부사는 사용자가 굳이 검색할 이유가 없으므로 인덱싱에서 제외하는게 효율적이다. 이런 검색에 필요없는 단어를 중지 단어라고 한다.

MySQL은 information_schema_innodb_ft_default_stopword 테이블에 36개의 중지 단어를 미리 가지고 있다. 필요하다면 사용자가 별도의 테이블에 중지 단어를 추가한 뒤 적용시킬 수도 있다.

중지 단어 만들기

전체 텍스트 인덱스 단어 확인

-- db와 테이블 이름 소문자로
SET GLOBAL innodb_ft_aux_table = 'db_name/table_name';

-- 테이블에서 만들어진 전체 텍스트 인덱스 보기(필요없는 검색 단어 존재)
SELECT word, doc_count, doc_id, position FROM information_schema_innodb_ft_index_table;

중지 단어 생성

-- 중지단어 만들기전에 만들어놓은 풀텍스트 인덱스는 삭제
drop index idx_all on test_table;

-- 중지단어를 위한 테이블 만들기
-- 테이블의 데이터는 반드시 value , 타입은 varchar -> 약속
CREATE TABLE user_stopword (value VARCHAR(30));

--중지 단어 만들기
insert into user_stopword values ('그는'), ('그리고'), ('극에') ; 

-- 중지 단어 테이블에 지금 만들 테이블을 추가하는 작업
-- 그러면 이제 user_stopword 테이블에 들어있는 단어로는 풀텍스트 인덱스를 만들지 않게 된다
set global innodb_ft_server_stopword_table = '디비명/user_stopword'; -- 모두 소문자

-- 만든 테이블이 들어갔는지 확인
show global variables like 'innodb_ft_server_stopword_table' ;

-- 중지단어 테이블 만들었으니, 이제 다시 풀텍스트 인덱스 생성.
create fulltext index idx_description on test_table ( description );

-- 그러면 중지단어에 등록한 단어를 제외한 인덱스 단어들이 생성됨 (갯수가 줄어들음)
select word, doc_count, doc_id, position 
from information_schema.innodb_ft_index_table ;

참고

• 풀텍스트 인덱스 사용법
• What is fulltext index and when should I use it
• 윈도우 MySQL 재시작 방법