[이것이 MYSQL이다] Full Text Index정리

본 포스트는 2021년에 업로드된 포스트입니다. 개념이 아직 정리안된 나이에 업로드한 포스트이기에 수정사항이 있으면 연락주세요.

Full Text Index

소개

전체 텍스트 검색인덱스는 인덱스의 확장개념이며 "전체 텍스트 인덱스(FULLTEXT Index)란 텍스트로 구성된 문자열 데이터의 내용을 가지고 생성한 인덱스"라고 정의되어 있다.

 

따라서 인덱스로 할수 없는 인덱스 검색을 지원한다.

 

인덱스의 기본개념을 다시 생각해보자 인덱스는 클러스터형 인덱스와 보조 인덱스가 있다고 했었고 대용량의 데이터를 검색하는데 사용한다고 하였다.

 

전체 텍스트 인덱스를 설명하기 위해 영화 정보를 저장하고 있는 테이블을 한번 생각해보자.

 

위와 같은 데이터가 있을것이다.

 

여기서 만약에 키워드 검색기능을 만든다고 생각해보면 description 컬럼에 인덱스를 적용해주어야 할 것이다. (영화 데이터가 엄청 많다고 가정함)

 

만약에 id 5번의 행을 description 행으로 검색을 하려면 아래와 같이 하면 된다.

SELECT * FROM movieTBL WHERE description = "사람은 모든 것을 파괴한다! 한 ..." -- 생략한다

 

위와 같이 검색을 하게 된다면 description에 있는 인덱스를 잘 사용하게 될 것이다.

 

하지만 이렇게 다 검색할 필요없이 주요 단어만 검색하여 결과를 찾을 것이다.

SELECT * FROM movieTBL WHERE description = "사람%" -- 생략한다

 

이렇게되면 id 5번은 검색되겠지만 사람과 관련된 정보를 원하는 사람들은 의도했던 결과가 아닐 것이다. 하지만 범위 검색을 하였기 때문에 여전히 인덱스는 사용된다.

 

이제 아래와 같은 쿼리문을 입력해서 다시 조회해보자.

SELECT * FROM movieTBL WHERE LIKE = "%남자%";

 

위와 같이 검색을 하게 된다면 인덱스로 검색이 되지 않을것이다.

 

이유는 범위 지정 인덱스는 위와 같은 쿼리문을 수행하려면 결국 하나의 행의 description 파트에 남자가 들어가는 단어를 모두 탐색해야할 것이다.

 

따라서 MYSQL에서는 인덱스보다 더 효율적인 FULL SEARCH를 사용했을것이다. (또한 인덱스를 사용할 수도 에초에 없기 때문에 FULL SEARCH를 사용하게 된다.)

 

지금은 예시로 3개의 데이터를 들었지만 만약에 저 테이블에 데이터가 몇백만개라면 검색을 하기까지에는 엄청난 시간의 소요와 시스템의 과부하가 예상될 것이다.

FULL TEXT INDEX의 장점과 사용되는 곳

이러한 시스템의 과부하를 방지하기 위하여 MYSQL에서는 전체 텍스트 검색 인덱스라는 키워드 검색 인덱스를 지원하고 있다.

 

따라서 어떠한 문장의 특정한 단어로 데이터를 검색할때 사용하는 인덱스라고 할 수 있다.

 

FULL TEXT INDEX의 장점은 아래와 같다

• 키워드 검색을 할때 매우 유용하게 동작한다.
• 대량의 테이블에서 문장 검색이 용이하다.
• 검색 기능을 메우 간편하게 구현할 수 있다.

 

정리를 해보면 위와 같다.

 

장점에서 설명했듯이 FULL TEXT 인덱스는 검색 기능을 구현할때 정말 유용하게 사용될 수 있는 기능들을 지원하고 있다.

 

이러한 FULL TEXT INDEX는 아래와 같은 문장을 단어로 쪼개서 인덱스를 하나씩 생성한다.

 

위의 사진을 비롯해서 생성된 단어들은 검색할때 사용될 키워드가 된다.

 

이렇게 키워드별로 인덱스를 생성하기 때문에 검색을 하더라도 바로바로 찾을 수 있는 역할을 할 수 있는 이유가 바로 이 FULL TEXT INDEX 때문이라고 할 수 있다.

중지 단어 (STOP WORD)

중지 단어는 FULL TEXT 인덱스와 함께 사용되는 기능이다.

 

전체 텍스트 인덱스 검색을할떄 "왜", "그래서", "또한", "그러함으로"와 같은 단어도 같이 하나의 인덱스로 만들어서 검색을 수행한다.

 

하지만 이러한 단어들은 문장과 문장을 이어주는 성분으로만 사용되기 때문에 검색을 할일이 거의 없다.

 

따라서 위의 단어들은 인덱스를 만들때 제거해주어야 할 옵션이 된다.

 

이렇게 FULL TEXT INDEX에서 특정한 단어의 인덱스 생성을 막고 싶을때 사용되는 용어를 중지 단어라고 한다.

 

중지 단어를 지정하는 방법은 아래에서 설명을 하도록 하겠다.

FULL TEXT INDEX 사용

FULL TEXT 인덱스의 생성방법은 기본적으로 총 3가지이다.

• CREATE문을 사용해서 인덱스 생성
• ALTER문을 이용해서 인덱스 생성
• CREATE FULLTEXT INDEX 문을 사용해서 인덱스 생성

 

일반적인 INDEX를 생성하는 방식과 비슷하다고 할 수 있다.

CREATE TABLE test1 (
	test varchar(100),
	FULLTEXT idx1(test) 
); -- create

ALTER TABLE test1 ADD FULLTEXT(test); -- alter

CREATE FULLTEXT INDEX idx1 ON test1 (test); -- create fulltext index

 

위와 같이 fulltext 인덱스는 당연하겠지만 varchar, char 등과 같은 문장을 표현할 수 있는 타입에서만 생성된다. (숫자 등의 문자를 표현할 수 없는 타입에서는 사용이 불가능하다.)

 

이제 FULL TEXT 인덱스를 사용해서 인덱스를 검색하는 방법을 설명하겠다.

 

기본적으로 FULLTEXT INDEX를 사용해서 검색을 하는 방법은 아래와 같다.

SELECT * FROM test1 WHERE MATCH(test) AGAINST('개발');

기본적으로 FULLTEXT를 사용하려면 LIKE를 사용하는것이 아닌 MATCH 함수와 AGAINST 함수를 사용해서 검색을 한다.

 

MATCH 함수는 말그대로 인덱스와 메칭되는 컬럼을 지정하는것이므로 방금전에 생성했던 test라는 컬럼을 지정한다.

 

AGAINST 라는 함수로는 이제 검색할단어를 입력하게 된다.

 

따라서 위의 쿼리문을 사용하게 된다면 test1 테이블에서 '개발'단어가 들어가 있는지 test라는 컬럼에서 찾아서 일치하는 행을 반환한다라는 것으로 해석이 가능해진다.

IN BOOLEAN MODE (블린 모드 검색)

브라우저에서 "단어"를 입력하면 큰 따옴표로 묶은 단어들은 꼭 검색에 들어가야한다는 용도로 사용된다는 것을 알고 있을 것이다.

블린 모드 검색은 이러한 검색에 용이한 다양한 +, -, * 등의 연산자를 지원하고 있다.

 

따라서 브라우저에서 이러한 검색옵션을 지원하는것이 가능한 이유도 바로 BOOLEAN MODE라는 모드가 있기 때문에 가능한 일이다.

블린모드검색을 사용하는 방법은 아래와 같다.

SELECT * FROM test1 WHERE MATCH(test) AGAINST('개발*', IN BOOLEAN MODE);

 

위와 같이 * 연산자를 이용하게 된다면 '개발'이라는 단어만 검색되는것이 아닌 '개발하고'와 같은 뒤에 붙는 단어도 검색이 가능해지는 역할을 수행한다.

• 연산자는 위에서 설명했듯이 브라우저에서 꼭 들어가야 한다는 필수단어 검색에 사용되는 옵션이다.

SELECT * FROM test1 WHERE MATCH(test) AGAINST('개발 +안녕', IN BOOLEAN MODE);

• 연산자뒤에 단어를 붙여서 boolean mode로 검색을하게 될 시 + 연산자뒤에 붙은 단어는 무조건 필수로 검색되어야 하는 행에 들어가 있어야 한다는 뜻을가지게 된다.
• 연산자를 정리하자면 개발이 들어가고 안녕이라는 단어는 필수로 들어간 문장의 행을 검색하는 역할을 수행한다고 할 수 있다.

• 연산자는 반대로 검색에서 필수로 제외되어야 한다는 특성을 가지고 있다.

• 연산자와 같게 - 뒤에 검색에서 제거해야할 단어를 설정하면된다.

SELECT * FROM test1 WHERE MATCH(test) AGAINST('개발 -안녕', IN BOOLEAN MODE);

위의 쿼리문을 보게 된다면 +와 반대로 필수로 들어가는 것이 아닌 개발이라는 단어가 들어가야 하지만 안녕이라는 단어는 문장에서 제외되어야 한다라는 특성을가지고 있는 연산자이다.

중지 단어의 사용방법

중지단어를 지정하고 있는 테이블은 innodb_ft_server_stopword_table 쪽이다.

중지단어를 지정하기 위해 중지단어만 모여있는 테이블을 생성한다.

CREATE TABLE StopWordTBL (value varchar(50));

INSERT INTO StopWordTBL VALUES ("왜"), ("그래서"), ("그러함으로"), ("또한");

위와 같이 중지단어를 설정할 테이블을 생성한다음 중지할 단어들을 INSERT로 삽입해준다.

이제 해당 테이블의 데이터를 중지 단어를 관리하고 있는 변수에 저장한다.

SET GLOBAL innodb_ft_server_stopword_table = 'newDB/StopWordTBL';

SHOW GLOBAL VARIABLES LIKE 'innodb_ft_server_stopword_table';

위와 같이 전역 변수에 데이터베이스이름/중지단어테이블이름 형식으로 작성해서 저장한다음

해당 변수를 variables 라는 변수에 최종적으로 저장한다.

이제 DROP INDEX문으로 인덱스를 지우고 다시 인덱스를 생성해보자.

SELECT word, doc_count, doc_id, position 
	FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

위의 쿼리문을 입력하면 중지단어로 설정된 단어가 잘 제거되었음을 알 수 있다.

추가 내용 : 생성할 FULLTEXT 인덱스에 최소 단어 길이 설정하기

위는 추가로 다루는 내용이다.

 

FULLTEXT에서 단어를 검색할때 최소 검색할 수 있는 단어의 길이가 있다.

 

해당 변수의 값은 기본적으로 3인데, 예를 들어 "개발"이라는 단어를 검색하고 싶은데 해당 단어의 길이는 2글자 이므로 검색할 수가 없다.

 

보통 2글자로 검색을하는 경우가 많기 때문에 이를 수정할 수 있는 방법을 다뤄보겠다.

 

C:\ProgramData\MySQL\MySQL Server 8.0 해당 경로로 접속한다.

 

위와 같은 파일들이 있는데 여기서 my.ini 파일을 메모장으로 연다.

 

위와 같은 내용들이 존재할텐데 여기서 FULLTEXT의 길이를 설정하는 변수는 Innodb 쪽이므로

 

위의 문장이 있는 innodb 전용 옵션을 설정하는 곳에서 변수를 설정한다.

 

기본적인 설정이 안되있으면 FULLTEXT의 변수 옵션이 존재하지 않을것이다.

 

파란색으로 되어있는 내용을 위에서 설명했던 innodb 옵션 아무데나 추가한다 (위치는 영향을 받지않지만 추후에 구분을 위하여 따르도록 한다.)

 

마지막으로 MYSQL 서비스를 재시작하면 최종적으로 SHOW GLOBAL inno_ft_min_token_size; 쿼리문을 입력하여 잘 적용되었는지 확인하고 종료한다.

정리

FULLTEXT 인덱스 검색은 검색 기능을 구현해야할때 검색해야할 데이터의 양이 대용량일때 유용하게 사용된다.

 

하지만 일반적인 인덱스와 마찬가지로 대용량이 아니라면 굳이 FULLTEXT 인덱스를 사용할 필요는 없다고 할 수 있다.

 

따라서 사용해야할 정확한 위치에 인덱스를 사용해야 좋은 효과를 볼 수 있을 것이다.