[쿠링] 검색 쿼리에 Full Text Index 적용하기

해당 글은 개인 프로젝트를 개선해 나가면서 내용을 정리하는 글입니다.

1. 문제 되는 상황

현 쿠링은 검색어를 입력받으면 like절을 사용하여 원하는 text를 필터링하고 있다.

SELECT * FROM notice WHERE notice.contents LIKE '%장학금%'

explain을 통해 실핼계획을 보면 다음과 같다.

예상했듯.... full scan을 하고 있다...

그래도 스토리지 엔진에서 데이터를 불러온 후, 100% filtering 하고있는걸 보면 조건절이 스토리지 엔진에 전달되어 필요한 데이터만 가져왔음을 알 수 있다.

 

이러한 방식은 검색한 text의 내용이 많아지면 어마어마하게 성능이 저하된다.

몇년치 데이터에서 like로 검색을 한다 생각하면.... 과부하가 발생하고, response time이 느려질 것이 뻔하다.

 

예를 들어 다음과 같이 정규표현식으로 '%봉사%'를 검색한다고 해보자! (공지 500만건 기준)

select *
from notice
where notice.subject like '%봉사%';

결과는 다음과 같다. 무려 11초나 걸린다...

 

이를 해결하기 위해서 다음 3가지를 도입해서 해결할 것이다.

1) Full text index

2) 검색 단어 제한

3) QueryDsl에 Dialect로 적용하기

 

1 - 1) 데이터 준비

공지 데이터 500만 건을 insert 하여 실험을 진행하게 되었습니다.

csv 파일 용량만 1.14GB가 되더군요...

 

2. Full Text Index 적용하기

전체 텍스트 검색은 첫 글자뿐 아니라, 중간의 단어나 문장으로도 인덱스를 생성해 주기 때문에 지금과 같은 상황에서도 index를 생성하여 순식간에 검색 결과를 얻을 수 있다.

 

이러한 Index는 일반적인 MySql, Mariadb의 index와는 몇 가지 차이점이 있다고 합니다.

1. 전체 택스트 인덱스는 InnoDB와 MyISAM 테이블만 지원한다.
2. 전체 텍스트 인덱스는 char, varchar, text의 열에만 생성이 가능하다.
3. 인덱스 힌트의 사용이 일부 제한된다.
4. 여러 개 열에 FULLTEXT 인덱스를 지정할 수 있다.

 

2 - 1) min_token_size 2로 변경하기

MySQL이나 Mariadb는 기본적으로 3글자 이상만 전체 텍스트 인덱스로 생성한다.

따라서 이 설정을 2로 변경하여 2글자까지 전체 텍스트 인덱스가 생성되도록 시스템변수를 변경하자.

기본은 3글자

MySQL의 환경 설정 파일인 my.cnf 파일의 [mysqld] 아래쪽 아무 곳에나 다음 행을 추가하고, 재시작한다.

 

다음 grep 명령어를 통해 경로를 찾자!

innodb_ft_min_token_size=2

 

다음과 같이 my.cnf 파일을 vim으로 열어 맨 끝에 한 줄 추가해 준다!

 

이후 재부팅하면 2로 정상적으로 변경되어 있다!

2 - 2) Index 생성하기

Full Text Index를 다음 쿼리를 통해 만들어주자!

CREATE FULLTEXT INDEX idx_notice_subject ON notice(subject);

인덱스 생성하는데만 50초나 걸렸다...

 

다음 쿼리를 통해 index가 생성되었는지 확인해 보자!

SHOW INDEX FROM notice;

내가 만든 "idx_notice_subject"가 FULLTEXT로 잘 생성되었다!

 

2 - 3) 검색해 보기

이전과 동일하게 "봉사"가 포함된 행을 검색해 보자!

SELECT *
FROM notice
WHERE MATCH(subject) AGAINST ( '*봉사*' );

검색 쿼리 자체도 이전과는 달라졌다. WHERE절에 MATCH를 사용해 주었으며, AGAINST에 찾을 대상 문자열을 지정해 주었다.

 

시간이 무려 591ms로 개선되었다!

11s 59ms -> 591 ms로 개선!

이 정도만 해도 엄청난 성과라 생각된다!

실행 계획도 fulltext로 변경되었다!

추가로 IN BOOLEAN MODE를 지정해 줄 수 도 있는데, 이는 뒤에서 적용해 보자!

(간략히 먼저 말하면, 딱 "봉사"만 포함된 것이 아닌, 이와 어느 정도 유사한, "봉사를", "봉사가", "봉사인" 이 들어간 모든 결과를 찾아주는 방식이다.

 

2 - 4) Index가 생성된 단어 확인하기

다음 쿼리문으로 전체 텍스트 인덱스로 만들어진 단어를 확인해 보자!

SET GLOBAL innodb_ft_aux_table = 'kuring/notice';

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

word는 index로 생성된 단어나 문구를 말하고, doc_count는 몇 번이나 나왔는지를 의미한다.

 

단어로 생성된 문구만 거의 3천만 개이다...

이는 사실, 필요 없는 "그리고", "그래서", "따라서", 등과 같은 단어까지 모두 index를 위한 단어로 사용되기 때문이다...

이 또한 제거하여 성능을 더욱 향상해 보자!

 

이를 위해서 "중지단어"를 사용할 것이다!

 

3. 중지 단어

3 - 1) 이전 인덱스 제거하기

일단 이전에 생성해 둔 index를 제거하자!

DROP INDEX idx_notice_subject ON notice;

 

3 - 2) 중지 단어 테이블 생성하기

사용자가 추가할 중지 단어를 저장할 테이블을 만들자!

주의할 점은 테이블 이름은 아무거나 상관없지만, 데이터 형식은 VARCHAR 이여만 한다.

CREATE TABLE notice_stop_word (value VARCHAR(30));

 

3 - 3) 중지 단어 입력하기

우선 접속사 들만 제거하도록 해보자!

3 - 4) 중지 단어용 테이블 등록하기

중지 단어용 테이블을 시스템 변수 innodb_ft_server_stopword_table에 설정하자.

SET GLOBAL innodb_ft_server_stopword_table = 'kuring/notice_stop_word';
SHOW GLOBAL VARIABLES LIKE 'innodb_ft_server_stopword_table';

 

3 - 5) 다시 전체 인덱스 만들기

CREATE FULLTEXT INDEX idx_notice_subject ON notice (subject);

이렇게 적용하면 위에서 중지단어로 지정한 조사는 제외하고 Full Text Index테이블을 만들게 된다!

 

4. QueryDsl에 Dialect로 적용하기

index를 적용했지만, 아직 쿼리에는 변화가 없다.

QueryDsl의 쿼리 또한 변경하여 만들어 주자!

 

4 - 1) Custom 방언에 match 메서드 등록하기

다음과 같이 기존의 사용하던 MariaDB 방언을 상속하면서, 우리가 사용할 match함수를 추가 등록한 CustomMariaDbDialect를 만들어주자!

public class CustomMariaDbDialect extends MariaDB103Dialect {

    public CustomMariaDbDialect() {
        super();
        registerFunction("match", new SQLFunctionTemplate(StandardBasicTypes.DOUBLE, "match(?1) against (?2 in boolean mode)"));
    }
}

 

4 - 2) QueryDsl 수정

다음과 같이 BooleanBuilder를 생성한 후, Expressions를 사용해 우리 만든 'match'함수를 호출하도록 한다.

BooleanBuilder booleanBuilder = new BooleanBuilder();

NumberTemplate booleanTemplate = Expressions.numberTemplate(Double.class, "function('match',{0},{1})", notice.subject, "*" + containedName + "*");
booleanBuilder.or(booleanTemplate.gt(0));

 

이렇게 하면 성공적으로 반영된 모습을 확인할 수 있게 되었다!!!