MySQL - 전체 텍스트 인덱스

전체 텍스트 인덱스(FULLTEXT Index)란 ?

전체 텍스트 인덱스(FULLTEXT Index)란 텍스트로 구성된 문자열 데이터의 내용을 가지고 생성한 인덱스입니다. 예를 들어, 텍스트로된 자기소개나 신문기사, 레시피, 포털 사이트 데이터 같은 경우에 텍스트 내용의 일부만 가지고 검색을 한다고 가정하면, 일반 인덱스로는 적절하지 않을 것 이고, SELECT할 때 전체 페이지를 전부 검색한다면 시간이 굉장히 오래 걸릴 것입니다. 그래서 MySQL은 전체 텍스트 인덱스를 제공합니다.

 

MySQL에서 전체 텍스트 인덱스와 일반적인 인덱스의 차이점은 아래와 같습니다.

• InnoDB와 MyISAM 테이블만 지원한다.
• 자료형이 CHAR, VARCHAR, TEXT인 열에만 생성이 가능하다.
• 여러개의 열에 FULLTEXT는 인덱스는 
• 힌트의 사용이 일부 제한된다. - 힌트(Hint)는 SQL 튜닝의 핵심 부분이며, 성능 향상을 위한 일종의 지시 구문입니다.

 

전체 텍스트 인덱스 생성, 삭제

-- 1
CREATE TABLE table1(
    column1 VARCHAR(16),
    FULLTEXT idx1(column1)
);

-- 2
CREATE TABLE table1(
    column1 VARCHAR(16),
);
	-- 2.1
ALTER TABLE table2 ADD FULLTEXT(column1);
	-- 2.2
CREATE FULLTEXT INDEX idx1 ON table2 (column1);

기본적인 전체 텍스트 인덱스를 생성하는 세 가지 방법입니다. 1은 테이블 생성시에 인덱스를 생성하는 방법이고, 2.1은 ALTER TABLE로 인덱스를 생성하고, 2.2는 CREATE FULLTEXT INDEX로 인덱스를 생성합니다.

 

전체 텍스트 인덱스가 생성되면, INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE이라는 테이블이 생기고, 전체 텍스트 인덱스가 생성된 열에 있는 내용들을 기반으로 테이블에 단어들이 입력되고, 단어들이 몇번 사용되었는지 등이 저장되서 INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE을 기반으로 인덱스가 효율적으로 이용될 수 있도록 합니다.

 

ALTER TABLE table1 DROP INDEX FULLTEXT(column1);

DROP INDEX idx1 ON table1

인덱스를 삭제할 때는 예시의 두 가지 형식을 사용합니다.

 

검색

SELECT * FROM newsTable WHERE MATCH(article) AGAINST('사고');

특별히 옵션을 지정하지 않거나 IN NATURAL LANGUAGE MODE를 명시하면, 자연어 검색을 합니다. 자연어 검색은 AGINST의 단어가 정확히 일치하거나 단어의 일부와 일치하는 데이터만을 MATCH의 열에서 검색합니다.

 

AGINST절의 내용이 '사고'라면, '화재사고', '교통사고', '안전사고' 등은 검색되지 않습니다. 만약 '화재 사고', '교통 사고', '안전 사고'를 입력한다면 무리없이 검색됩니다.

 

AGINST절의 내용이 '화재 사고'라면, '화재'나 '사고' 중 하나만 포함된 데이터도 검색됩니다. 

 

SELECT * FROM newsTable WHERE MATCH(article) AGINST('*사고' IN BOOLEAN MODE);

IN BOOLEAN MODE를 명시하면, 불린 모드 검색을 합니다. 불린 모드 검색은 단어나 문장이 정확히 일치하지 않는 데이터도 검색할 수 있습니다.

 

예시에서는 부분 검색을 위한 '*' 연산자를 사용했는데 '화재사고', '안전사고' 등이 포함된 데이터가 검색되고, 만약 '*사고'가 아닌 '사고*'이라면 '사고는', '사고를', '사고예방' 등이 포함된 데이터를 검색할 것입니다.

SELECT * FROM newsTable WHERE MATCH(article) AGINST('사고 +기생충' IN BOOLEAN MODE);

'+' 연산자는 필수 요소를 추가합니다. 예시에서는 '사고'라는 단어가 있는 데이터 중에서 '기생충'이라는 내용이 포함된 데이터만을 검색합니다.

SELECT * FROM newsTable WHERE MATCH(article) AGINST('가수 -남자' IN BOOLEAN MODE);

'-' 연산자는 특정 요소를 제외합니다. 예시에서는 '가수'라는 단어가 있는 데이터 중에서 '남자'의 내용이 포함된 데이터는 제외하고 검색합니다.

 

*AGAINST절의 단어가 여러 개라면, 매치된 단어의 데이터가 많은 데이터가 먼저 정렬됩니다. 예를 들어, AGAINST절을 '수능 만점자'로 하고 검색합니다.

 

'수능'이라는 단어와 '만점자'라는 단어가 함께 포함된 데이터가 우선으로 정렬되고, '수능'이라는 단어와 '만점자'라는 단어가 함께 포함된 데이터를 다 정렬한 후, 둘 중 한 단어라도 포함된 데이터가 다음으로 정렬됩니다.

 

중지 단어

전체 텍스트 인덱스는 생성된 열의 텍스트 내용에 대해 인덱스를 생성하기 때문에 열의 텍스트 내용이 커질수록, 인덱스의 크기도 매우 커지게 됩니다.

 

따라서, 실제로 검색하는데 사용되지 않을 내용들은 인덱스로 생성하지 않는 편이 좋습니다. 예를 들면, '매우', '중요한', '맛있는', '아주', '저는', '꼭', '왜냐하면', '이것은', '그리고' 등은 인덱스에 추가되어도 쓸모 없는 데이터가 될 가능성이 큽니다. 이런 키워드를 인덱스에서 제외시키는 것이 중지 단어입니다.

 

MySQL에서 기본적으로 가지고 있는 중지 단어가 MySQL 5.7 기준으로 36개 정도 있는데, 'a', 'an', 'about', 'is', 'are' 등이 있습니다. 여기서 사용자가 별도의 테이블에 중지 단어를 추가한 후에 적용시킬 수 있습니다. 그러면, 전체 텍스트 인덱스는 설정한 중지 단어들을 제외합니다. 그 결과, 인덱스의 크기가 작아지므로 성능이 향상될 수 있습니다.

 

CREATE TABLE stopWordTable (value VARCHAR(50));

중지 단어를 저장할 테이블을 만들 때에는 테이블 이름은 정할 수 있지만, 열이름은 'value'로 지정해야 하고, 타입은 VARCHAR로 지정해야 합니다.

INSERT INTO stopWordTable VALUES ('그리고'), ('매우'), ('왜냐하면');

그리고 중지 단어를 INSERT로 테이블에 입력합니다.

SET GLOBAL innodb_ft_server_stopword_table = 'newsDB/stopWordTable';
SHOW GLOBAL VARIABLES LIKE 'innodb_ft_server_stopword_table';

위의 형식으로 데이터베이스명과 정지 단어 테이블로 사용할 테이블을 지정해주면 됩니다. 그리고 전체 텍스트 인덱스 테이블의 내용을 조회해보면 정지 단어로 입력한 '그리고', '매우', '왜냐하면'과 같은 단어들이 사라져 있습니다.