[쿠링] 검색 쿼리에 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초나 걸린다...

 

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

1) Full text index

2) 논문 리서치를 통해 n-Gram에서 적절한 n값 찾기

3) 검색 단어 제한

4) QueryDsl에 Dialect로 적용하기

 

추가로 고민한 지점

다 좋은데, Full Text Index를 통한 검색 요청을 동시에 많은 유저가 요청하면? 어떻게 될까? 지금의 서버가 버텨낼 수 있을까?

이에 대한 고민 또한 글 뒷부분에 남겨두었다. 5번 단락 확인 요망!!

 

1 - 1) 데이터 준비

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

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

 

2. Full Text Index 적용하기

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

 

전문 검색 인덱스는 다음과 같은 2가지 중요한 과정을 거쳐서 색인 작업이 수행된다.

• stop word 처리 : 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업.
• Stemming(어근 분석) : 검색어로 선정된 단어의 뿌리인 원형을 찾는 것

형태소 분석은 전문적인 알고리즘이라서 더 많은 시간이 필요하다 생각되어 → n-gram이 대안으로 도입 (키워드 인덱싱)

n은 인덱싱할 키워드의 최소글자 수

 

또한, 전문 검색 인덱스를 사용하려면 반드시 다음과 같이 MATCH (…) AGAINST (…) 구문으로 검색 쿼리를 작성해야 한다고 한다.

SELECT * FROM tb_shine
WHERE MATCH(doc_body) AGAINST('샤인' IN BOOLEAN MODE);

IN BOOLEAN MODE 지정 시 유사한 단어도 결과로 포함

 

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

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

 

2 - 1) 인덱싱할 키워드의 최소글자 수 n은?

이번 전문 검색 인덱스를 적용해 보기에 앞서 n-gram에서 인덱싱할 키워드의 사이즈인 n에 대한 고민이 있었다.

n은 몇으로 해야 적절할까? 이에 대한 고민으로 다음과 같은 점들이 생각났다

 

1. n이 너무 작으면 이를 저장하는 공간이 너무 커질 것 같은데?
2. n이 너무 크면 검색속도 향상에 도움이 안 될 것 같고...
3. "한국어"에 적절한 n은 몇으로 잡아야 할까?

 

위와 같은 고민은 했고, 그 해답을 논문을 리서치 하면서 확인해 볼 수 있었다. 확인해 본 논문은 다음과 같다.

Lee, Joo Young, Hyun-Yang Cho and Hyouk R. Park. “n-Gram-based indexing for Korean text retrieval.” 
Inf. Process. Manag. 35 (1999): 427-441.

 

위 논문에서는 단어별 사이즈, 즉 n을 1부터 5까지로 나누어 연구를 수행하였으며, 각 n별로의 성능을 확인해 볼 수 있었다.

위 Table-3를 통하여 WG-2 인덱싱이 단어 기반 인덱싱보다 복합 명사를 더 효과적으로 처리할 수 있음을 나타냅니다.

 

또한 위 Figure-1을 통해 각 n사이즈 별로 비교한 결과 n이 2인경우 가장 우수한 precision을 보여줌을 확인할 수 있었습니다.

따라서 우리 쿠링에도 n을 2로 적용하기로 결정하였습니다.

 

2 - 2) 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 - 3) Index 생성하기

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

CREATE FULLTEXT INDEX idx_notice_subject ON notice(subject);

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

 

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

SHOW INDEX FROM notice;

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

 

2 - 4) 검색해 보기

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

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

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

 

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

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

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

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

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

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

 

2 - 5) 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) 중지 단어 입력하기

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

 

(2024/02/03 : 추가적인 불용어들은 다음 글을 참고하였다.)

한국어 불용어 리스트 (Stopword)

 

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));

 

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

 

 

5. 고민거리

글 맨처음에 언급했듯, Full Text Index(FTS)를 통한 검색 요청을 동시에 많은 유저가 요청하면? 어떻게 될까? 지금의 서버가 버텨낼 수 있을까?

분명 사용자가 검색을 요청하면 스토리지 엔진으로부터 결과물을 메모리로 올리고, 이를 적절하게 필터링하여 반환해 줄 것이다.

그럼 동시에 많은 검색요청으로 인하여 메모리에 너무 많은 데이터가 적제 되면 문제가 생기는 것 아닐까?

당연히 적절한 수치의 제한이 필요할 것 같다는 생각을  하였다.

 

이때 도움이 된 키워드가 바로, ‘innodb_ft_result_cache_limit’였다.

https://mariadb.com/kb/en/innodb-system-variables/#innodb_ft_result_cache_limit

 

FTS에 있어 Thread 당 InnoDB FTS결과에 대한 Cache의 제한을 설정하는 것으로, 이 변수가 중요한 건 InnoDB에서 FTS 결과가 메모리에서 처리되기 때문이다. 즉, 이 값을 제한하여 적절한 메모리 임계치를 설정할 수 있었다!!

 

쿠링 또한 확인해 본 결과 기본적으로 2GB가 캐시로 할당된 것을 확인할 수 있었다.

문제는 우리 T3 인스턴스의 가용 메모리가 2GB이다.....

 

Cache 의경우 5~10% 정도가 적절하는 관련글을 찾아볼 수 있었고, 따라서 다음과 같이 150MB 정도를 할당하게 되었습니다.

SET GLOBAL innodb_ft_result_cache_limit = 157286400;