Full Text Search를 이용한 DB Optimization

2025.02.13 - [Backend/Database] - JPA에서 Index 설정하는 방법 (feat. 쿼리 속도 향상 시키기) - 1편

JPA에서 Index 설정하는 방법 (feat. 쿼리 속도 향상 시키기) - 1편

2025.02.13 - [Backend/Database] - JPA에서 Index 설정하는 방법 (feat. 쿼리 속도 향상 시키기) - 2편

JPA에서 Index 설정하는 방법 (feat. 쿼리 속도 향상 시키기) - 2편

 

저번 포스팅에서 Index를 사용해서 쿼리 속도를 개선하는 작업을 했다.

 

사용자에게 알레르기를 유발할 수 있는 재료의 재료명과 정확히 '일치'하거나 '포함'하는 레시피를 필터링을 할 때 예를 들어, "우유" 알레르기를 가진 사용자에게는 "우유", "저지방우유", "시금치우유소스"를 포함하는 레시피를 제외하고 보여주어야 했다.

 

기존에는 이를 위해 LIKE, CONCAT을 사용하여 아래와 같이 처리했다.

LIKE CONCAT('%', i.ingredient, '%')

 

하지만 LIKE 연산자는 전체 문자열을 스캔하기 때문에 데이터 양이 많아질 경우 성능 저하로 이어지게 된다.
쿼리 속도 향상을 위해 인덱스 외에도 다양한 기법들을 찾아보던 중 Full Text Search의 n-gram parser를 알게되었는데 이는 문자열을 작은 단위(토큰)로 분해해서 검색어의 일부만 일치해도 해당 데이터를 빠르게 찾을 수 있는 도구다.

 

만약 토큰의 크기가 2라면,

ex) "저지방우유" -> "저지", "지방", "방우", "우유"

ex) "시금치우유소스" -> "시금", "금치", "치우", "우유", "유소", "소스"

이런식으로 쪼개지게된다. 다른 예시로 "시금치"의 경우, "시금"과 "금치"라는 토큰이 나오기 때문에 이 두 토큰이 모두 존재하는 "시금치우유소스"는 검색 결과에 포함된다.

MySQL은 기본적으로 n-gram parser을 제공하고 있기 때문에 옵션으로 명시하면 된다. 그리고 기본적으로 ngram_token_size의 기본값은 2로 설정되어있어서 따로 설정을 바꿔줄 필요없이 바로 사용하도록 하겠다.

---

앞선 포스팅에서 @Index를 사용해 인덱스를 적용했던 방식과 달리, Full Text Search의 n-gram parser를 사용하려면 FULLTEXT INDEX를 직접 데이터베이스에 생성해주어야 한다.

 

resources 아래에 db 패키지를 만들고, 그 안에 sql 파일을 만들었다.

다음 코드를 data.sql에 작성하고 실행하면, 각각의 Index들이 만들어진다.

data.sql

ALTER TABLE recipe ADD FULLTEXT INDEX ft_recipe_name (recipe_name) WITH PARSER ngram;
ALTER TABLE ingredient ADD FULLTEXT INDEX ft_ingredient_name (ingredient) WITH PARSER ngram;

 

기존의 JPQL은 FULLTEXT SEARCH를 지원하지 않기 때문에 SQL문으로 코드를 바꾸어주었다.

 

RecipeRepository

@Query(value = """
        SELECT *
        FROM recipe
        WHERE MATCH(recipe_name)
              AGAINST(:query IN NATURAL LANGUAGE MODE)
        """, nativeQuery = true)
List<Recipe> findByQuery(@Param("query") String query);

@Query(value = """
        SELECT r.*
        FROM recipe r
        WHERE NOT EXISTS (
                 SELECT 1
                 FROM recipe_ingredient ri
                 JOIN ingredient i ON ri.ingredient_id = i.ingredient_id
                 JOIN user_ingredient ui ON ui.user_id = :userId
                 JOIN ingredient ing ON ui.ingredient_id = ing.ingredient_id
                 WHERE ri.recipe_id = r.recipe_id
                  AND (
                      i.ingredient_id = ing.ingredient_id
                      OR MATCH(i.ingredient) AGAINST(:allergyIngredients IN NATURAL LANGUAGE MODE)
                  )
           )
    """, nativeQuery = true)
    List<Recipe> findAllFilteredRecipes(@Param("userId") Long userId, @Param("allergyIngredients") String allergyIngredients);

 

RecipeService

public class RecipeService {
public List<RecipeListResponseDTO> getAllFilteredRecipes(Long userId) {

        // 사용자 알레르기 재료명 목록 가져오기
        List<String> allergyIngredient = userIngredientRepository.findAllergyIngredientsByUserId(userId);

        String allergyIngredients = allergyIngredient.stream()
                .map(String::trim)
                .collect(Collectors.joining(" ")); // "계란 우유" 형태로 변환

        // 필터링된 레시피 가져오기
        List<Recipe> recipes = recipeRepository.findAllFilteredRecipes(userId, allergyIngredients);
	}
}

 

저번에 Index를 사용한 필터링 약 202ms의 실행 시간보다 10ms 정도 빨라진 것을 확인할 수 있다.

 

다만 MATCH AGAINST에서는 String만 지원하기 때문에 사용자 알레르기 재료를 allergyIngredients로 받아와서 (ex. "+우유 +계란") 하나의 단일 문자열로 만들어서 파라미터로 넘겨줬다. 이 과정이 생각보다 시간이 소요되어서 그런지 비약적으로 많이 줄어들지는 않은 것으로 예상된다.

 

그래서 MySQL 워크벤치에서 전/후 코드를 각각 실행해보았다.

파랑색이 Index만을 사용한 쿼리, 빨강색이 FULLTEXT SEARCH를 사용한 쿼리이다.

0.01s -> 0.006s 으로 줄어든 것을 확인할 수 있었다.

 

생각보다 리팩토링하는 과정이 오래 걸리기도 하고 중간에 버그 고치느라 시간을 많이 잡아먹었는데 이렇게 가시적으로 확인할 수 있는 결과를 도출하게 되니 뿌듯하다. 

 

사실 작성한 JPQL이 생각대로 잘 안될 때 SQL문으로 workbench에서 직접 쿼리를 돌려가면서 데이터가 제대로 나오는지 확인을 했었다. 스프링부트로 개발을 시작하면서 JPQL만 쓰고 SQL은 안 쓸줄 알았는데 여기서 이렇게 유용하게 쓸 줄 몰랐다 허허..  앞으로 SQL도 계속해서 공부해야겠다는 생각이 들었다.