[MySQL] Full-Text 인덱스 검색 쿼리 개선

몽상현 2024. 5. 24. 02:28

최근 키워드를 조건으로 검색하는 쿼리 조회 속도가 느려진 이슈가 발생하여 문제 원인을 찾아보던 도중 조건문에 걸려있는 컬럼이 눈에 밟혔다. 사용자가 키워드를 입력했을때 해당 키워드를 조건으로 필터링하여 조회하는 부분의 개선이 필요했다.

기존 Like 방식보다 Full-Text 인덱싱 검색이 성능개선에 좋다고 하여 적요하면서 공부했던 내용을 정리하려 한다.

Full-Text 인덱스란?

텍스트 타입의 컬럼 데이터의 내용을 사용하여 크게 두가지 기법으로 인덱싱하여 검색 조건을 개선할 수 있도록 하는 인덱스.

필요 조건

MySQL 5.7 이상 버전 필요
컬럼 타입 CHAR, VARCHAR, TEXT

자 그럼 Full-Text 인덱스를 적용할 때 데이터를 인덱싱하는 방법을 알아보자. 위에서 설명할 때 크게 두가지 기법이 있다고 이야기 했는데 해당 기법에 대해 알아보자.

검색 쿼리

Full-Text 검색 쿼리는 MATCH ... AGAINST 구문으로 명령어가 실행된다.

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

MATCH는 쉼표로 구분되고 검색할 컬럼을 지정하며,

AGAINST는 검색할 문자열과 수행할 검색 유형을 나타내는 search modifier를 사용한다.

주요 seach modifier는 다음과 같다.

IN NATURAL LANGUAGE MODE
- 수정자가 지정되지 않거나 IN NATURAL LANGUAGE MODE 입력 시 자연어 모드

[예시]

SELECT title, MATCH (title) AGAINST ('subject project' IN NATURAL LANGUAGE MODE) AS relevance 
FROM project_tb
WHERE MATCH (title) AGAINST ('subject project' IN NATURAL LANGUAGE MODE);

[결과]

+----+----------------------------------+----------------------+
| id | title | relevance |
+----+----------------------------------+----------------------+
|  1 | Subject is important        | 0.753771901130676 |
|  2 | Subject Project Title. | 1.386294361114502 |
|  5 | This Project is Cool | 0.753771901130676. |
+-----+----------------------------------+----------------------+

IN BOOLEAN MODE
- 불린 모드 검색은 문자열을 단어 단위로 분리한 후, 추가적인 검색 규칙을 적용되어서 단어가 포함되는 행을 찾는다.
- 아래 쿼리는 'subject'는 포함하지만 'content'는 포함하지 않는 검색 규칙을 적용해 해당하는 모든 컬럼을 조회한다.

[예시]

SELECT * FROM project_tb
WHERE MATCH (title)
AGAINST ('+subject -content' IN BOOLEAN MODE);

[결과]

+----+----------------------------------+
| id | title |
+----+----------------------------------+
| 1 | Subject is important |

| 2 | Subject Project Title. |
+----+----------------------------------+

[OPERATOR]

연산자	설명	예시
+	AND, 검색에 반드시 포함되는 단어	+project
-	NOT, 검색에 반드시 제외되는 단어	-project
>	포함, 검색 순위를 높임	+project >title
<	포함, 검색 순위를 낮춤	+project <content
()	그룹화된 하위 표현식	+project +(>title <content)
*	wildcard	pro*

WITH QUERY EXPANSION
- 쿼리 확장 검색은 자연어 검색을 확장한 것으로, 총 2단계로 나눠 검색을 수행한다.
- 첫번째, 자연어 검색 수행
- 두번째, 첫번째 검색 결과를 기반으로 검색 문자열을 재구성해 검색

SELECT title, MATCH (title) AGAINST ('subject project' WITH QUERY EXPANSION) AS relevance 
FROM project_tb
WHERE MATCH (title) AGAINST ('subject project' WITH QUERY EXPANSION);

[결과]

| 3 | This Subject is Title | 0.753771901130676 |
| 5 | This Project is Cool | 0.753771901130676 |
+-----+----------------------------------+----------------------+

인덱싱 기법

Stop-word

토큰을 나눌 때 Stop-word(공백[default], Tab, 문장기호, 사용자 설정) 기준으로 나눔
- Ex) 나의 재미있는 프로젝트 -> 나의 / 재미있는 / 프로젝트
검색하는 단어가 정확히 일치해야만 조회 가능
- Ex) SELECT * FROM project_tb WHERE MATCH(project_name) AGAINST("곰프로젝트");
- project_name이 "곰프로젝트" 와 정확히 일치하는 데이터만 조회

N-gram

할당항 토큰 크기 n만큼 파싱하여 사용
- Ex) 너의 재미없는 프로젝트 -> 너의 / 재미 / 미없 / 없는 / 프로 / 로젝 / 젝트
검색하는 단어가 포함 되어도 조회 가능
- Ex) SELECT * FROM project_tb WHERE MATCH(project_name) AGAINST("곰프로젝트");
- project_name에 "곰프로젝트" 단어가 포함된 것 이외에도 "곰 프로젝트" 와 같이 띄어쓰기가 포함된 데이터도 조회

최소 인덱싱 글자수 설정

my.cnf 설정 또는 AWS 파라미터에 설정
설정하지 않으면 ft_min_word_len은 기본 4로 지정
길이가 기본보다 짧거나 Stopword보다 짧으면 검색에서 제외됨

[mysqld]
ft_min_word_len = 2
innodb_ft_min_token_size = 2