본문 바로가기
프로젝트/개인 프로젝트 V3

Full Text Search를 이용한 DB 성능 개선

by Thumper 2025. 3. 6.

상품 검색 기능 개선

like의 한계

LIKE 검색은 인덱스를 활용하지 못하기 때문에 대량의 데이터에서 성능 저하를 유발한다고 한다.
검색 성능 테스트를 하면서, 실제로 데이터 수가 40 → 200만으로 증가했을 때, 검색 속도가 0.4초 → 4.5초로 느려짐을 확인할 수 있었다.

검색 기능 개선방향: MySQL의 FullText N-gram, ElasticSearch

현재 구현한 방식인, LIKE '%검색어%' 구문은 문자열의 중간 검색을 수행하므로 B-tree 인덱스를 사용하지 못하고 Full Table Scan을 해야 한다.
보통 like 키워드 검색 성능 최적화를 위해 MySQL의 FullText N-gram이나 ElasticSearch으로 해결한다고 한다.
나의 경우는 Like에서 FullText으로 변경했고, 두 방식의 성능 비교를 정리하려고 한다.

개인 프로젝트 경우, 가장 널리 알려진 무료 검색엔진 elasticsearch 와 같은 ELK를 도입해서 사용한다면 제일 적합한거 같다.
Docker에 ELK 패키지를 설치해서 사용하는 방식도 좋을 것 같다.

📌 성능 테스트

LIKE 방식과 MySQL의 FullText N-gram 방식의 검색 시간과 검색 정확도를 비교해보자.
성능 테스트를 작성할 때, 검색 정확도를 비교하기 위해서 테스트 데이터를 200만 item을 넣었다.

검색 정확도 비교: 연관성 높은 결과인지?

산리오 굿즈에서 "헬로키티 마켓 조각 스티커"를 검색어(상품명)로 입력했을 때, 검색 결과에 적절한 키워드가 포함되는지 확인하고 연관성을 비교하려 한다.
검색어에서 사용자가 원하는 키워드들의 적절한 결과가 포함되었는지 테스트 해보려고 한다.
검색어가 `헬로키티 마켓 조각 스티커"일 경우에는 "헬로키티" "마켓" "조각" "스티커" 키워드가 순서에 상관없이 연관성 있는 결과를 반환받는지 확인하려고 한다.
적절한 예시는 다음과 같다.

  • 후루카와 x 산리오 후르츠 마켓 시리즈 조각 스티커 : 헬로키티
  • 헬로키티 과일마켓 마스킹테이프
  • 산리오 미즈이로 헬로키티 조각 스티커

테스트 데이터 넣기

200만 개의 item 데이터를 삽입하여 성능 테스트를 진행했다. 아래와 같이 MySQL DB에 데이터를 추가했다.

Item Table

Item 테이블은 위와 같으며, 테이블의 컬럼에 맞추어 데이터를 추가했다.

image



sql

use sanrio_love;

DELIMITER $$

DROP PROCEDURE IF EXISTS INSERT_ITEM_TEST_DATA$$

CREATE PROCEDURE INSERT_ITEM_TEST_DATA()
BEGIN
    DECLARE V_START_CNT INT DEFAULT 1;
    DECLARE V_END_CNT INT DEFAULT 100000;
    DECLARE V_NAME_KOR VARCHAR(255);
    DECLARE V_DESCRIPTION VARCHAR(255);
    DECLARE V_PRICE INT;
    DECLARE V_MAIN_CATEGORY VARCHAR(255);
    DECLARE V_SUB_CATEGORY VARCHAR(255);
    DECLARE V_SANRIO_CHARACTER VARCHAR(255);
    DECLARE V_UPLOADER_ID BIGINT;

    WHILE V_START_CNT <= V_END_CNT DO
        -- 데이터 생성: 상품 이름, 설명, 가격, 카테고리 등
        SET V_NAME_KOR = CONCAT('상품', V_START_CNT);
        SET V_DESCRIPTION = CONCAT('상품 설명', V_START_CNT);
        SET V_PRICE = (V_START_CNT * 100);  -- 예시 가격 설정
        SET V_MAIN_CATEGORY = 'ACCESSORIES';  -- 예시로 고정
        SET V_SUB_CATEGORY = 'ACRYLIC_STAND';  -- 예시로 고정
        SET V_SANRIO_CHARACTER = 'HELLO_KITTY';  -- 예시로 고정
        SET V_UPLOADER_ID = 561;  -- 예시로 업로더 ID를 561로 고정

        -- item 테이블에 더미 데이터 삽입
        INSERT INTO item (name_kor, description, price, main_category, sub_category, sanrio_characters, uploader_id, create_at, like_count)
        VALUES (V_NAME_KOR, V_DESCRIPTION, V_PRICE, V_MAIN_CATEGORY, V_SUB_CATEGORY, V_SANRIO_CHARACTER, V_UPLOADER_ID, NOW(), 0);

        -- 카운터 증가
        SET V_START_CNT = V_START_CNT + 1;
    END WHILE;
END$$

DELIMITER ;


CALL INSERT_ITEM_TEST_DATA;
image



테스트 데이터에 item 상품명을 '상품1','상품2' ... '상품2000000' 이러한 식으로 넣었다.

그리고 산리오 검색비교를 위해서 아래처럼 헬로키티 굿즈 상품도 추가했다.

image





☑️ Like 검색

기존 방식(Like 키워드 검색)에서는 '%검색어%'으로 처리했었다.
QueryDSL로 쿼리를 작성했는데, where()절에 BooleanBuilder를 사용해 해당 검색 조건을 추가했다.

image

 

image



입력한 상품명(검색어)에서 공백을 기준으로 키워드를 쪼개서 검색되도록 작성했다.

검색어("헬로키티 마켓 스티커") -> %헬로키티%, %마켓%, %스티커%

상품명(itemName, name_kor) 검색조건

  • 사용자가 입력한 검색어(itemName)를 공백을 기준으로 나누어 검색결과에 포함할 키워드를 만든다.
    예를 들어, "헬로키티 스티커"라면 "헬로키티"와 "스티커" 두 개의 키워드를 만든다.
  • 각 키워드마다 LIKE 조건을 추가하여, 상품 목록에서 상품명이 해당 키워드를 포함하는지 확인한다.
  • toLowerCase()를 사용하여 대소문자 구분 없이 검색을 처리.

쿼리는 아래와 같이 실행된다.

image






테스트 코드

검색어가 "헬로키티"인 경우와 "헬로키티 마켓 스티커"인 경우로 나누어 검색 시간과 검색 정확도를 비교했다.
아래 코드와 같이 실행 시간과 조회 건수를 로그로 출력해 확인했다.

image




✔️ 1. 검색어 : "헬로키티"

검색어를 '헬로키티'로 설정했을 때 결과는 아래와 같다.

wiki_like 검색_헬로키티


해당 검색어 "헬로키티"가 포함된 상품 10개가 조회되었다.

  • 산리오 헬로키티 키링 + 마켓 에디션
  • 헬로키티 마켓 한정판 스티커북
  • 헬로키티와 친구들 스티커팩
  • 조각 마켓 헬로키티 스티커
  • 헬로키티 마켓 한정판 스티커북
  • 산리오 헬로키티 키링 + 마켓 에디션
  • 헬로키티와 친구들 스티커팩
  • 후루카와 x 산리오 후르츠 마켓 시리즈 조각 스티커 : 헬로키티
  • 헬로키티 과일마켓 마스킹테이프
  • 산리오 미즈이로 헬로키티 조각 스티커



✔️ 2. 검색어 : "헬로키티 마켓 스티커"

검색어를 '헬로키티 마켓 스티커'로 설정했을 때 결과는 아래와 같다.

wiki_like 검색_헬로키티 마켓 스티커


해당 검색어에는 "헬로키티" "마켓" "스티커"로 키워드가 있으니, 해당 키워드가 포함되는 상품 4개가 조회되었다.

  • 조각 마켓 헬로키티 스티커
  • 헬로키티 마켓 한정판 스티커북
  • 후루카와 x 산리오 후르츠 마켓 시리즈 조각 스티커 : 헬로키티



☑️ MySQL의 FullText N-gram 검색

FullText 인덱스 설정

MySQL DB에서 item의 nameKor(상품명)을 FULLTEXT 인덱스로 설정했다.

# f-t 인덱스 설정

use sanrio_love;

-- ngram 파서를 활용한 FULLTEXT 인덱스 생성
ALTER TABLE item ADD FULLTEXT INDEX FT_NAME_KOR (name_kor) WITH PARSER ngram;

JPA Repository에서 MATCH() 함수를 활용한 전체 텍스트 검색(Full-Text Search)을 작성하고, BOOLEAN MODE를 사용해 검색 조건을 설정했다.

image



테스트 코드

검색어가 "헬로키티"인 경우와 "헬로키티 마켓 스티커"인 경우로 나누어 검색 시간과 검색 정확도를 비교했다.
아래 코드와 같이 실행 시간과 조회 건수를 로그로 출력해 확인했다.

image

 

조회 쿼리가 아래와 같이 실행되는 것을 확인할 수 있다.

image




✔️ 1. 검색어 : "헬로키티"

검색어를 '헬로키티'로 설정했을 때 결과는 아래와 같다.

wiki_fulltext 검색_헬로키티


해당 검색어 "헬로키티"가 포함된 상품 10개가 조회되었다.

  • 산리오 헬로키티 키링 + 마켓 에디션
  • 헬로키티 마켓 한정판 스티커북
  • 헬로키티와 친구들 스티커팩
  • 조각 마켓 헬로키티 스티커
  • 헬로키티 마켓 한정판 스티커북
  • 산리오 헬로키티 키링 + 마켓 에디션
  • 헬로키티와 친구들 스티커팩
  • 후루카와 x 산리오 후르츠 마켓 시리즈 조각 스티커 : 헬로키티
  • 헬로키티 과일마켓 마스킹테이프
  • 산리오 미즈이로 헬로키티 조각 스티커



✔️ 2. 검색어 : "헬로키티 마켓 스티커"

검색어를 '헬로키티 마켓 스티커'로 설정했을 때 결과는 아래와 같다.

wiki_fulltext 검색_헬로키티 마켓 스티커


상품 12개가 조회되었다.

  • 산리오 헬로키티 키링 + 마켓 에디션
  • 헬로키티 마켓 한정판 스티커북
  • 헬로키티와 친구들 스티커팩
  • 조각 마켓 헬로키티 스티커
  • 헬로키티 마켓 한정판 스티커북
  • 산리오 헬로키티 키링 + 마켓 에디션
  • 헬로키티와 친구들 스티커팩
  • 후루카와 x 산리오 후르츠 마켓 시리즈 조각 스티커 : 헬로키티
  • 헬로키티 과일마켓 마스킹테이프
  • 산리오 미즈이로 헬로키티 조각 스티커
  • 산리오 포차코 마켓 스티커
  • 산리오 포차코 조각 스티커

MATCH(name_kor) AGAINST('헬로키티 마켓 스티커' IN BOOLEAN MODE)

위 쿼리에서는 "헬로키티" "마켓" "스티커"가 각각 포함된 상품이 모두 조회된다. 이 쿼리는 OR 조건처럼 동작하므로, 세 단어 중 하나라도 포함되면 결과에 포함된다.



Like vs FullText

검색 조건 LIKE (결과 수, 실행 시간) FULL-TEXT (결과 수, 실행 시간)
헬로키티 10개, 3,762ms 10개, 107ms
헬로키티 마켓 스티커 4개, 3,718ms 12개, 12ms

"헬로키티"를 포함한 데이터를 조회해본 결과 LIKE 검색보다 FULL-TEXT 검색이 35배 더 빠른 성능을 보였다. (실행 시간: 3,762ms → 107ms)
"헬로키티 마켓 스티커" 검색어의 경우, FULL-TEXT 검색은 빠른 성능을 보였지만, 검색 정확도에서 LIKE 검색이 더 높은 연관성을 보였다.

MATCH(name_kor) AGAINST('헬로키티 마켓 스티커' IN BOOLEAN MODE) 쿼리에서는 "헬로키티" "마켓" "스티커"가 포함된 모든 상품을 반환하기 때문에,
"헬로키티" "마켓" "스티커" 중 하나라도 포함된 상품이 결과로 나타난다.
그로 인해, 연관성이 낮은 상품들(예: "산리오 포차코 마켓 스티커", "산리오 포차코 조각 스티커")도 결과에 포함되었다.



📌 결론 : item 검색 시 full text search 적용

기존 검색 방식의 문제점 : LIKE '%키워드%' 구문

LIKE '%키워드%' 구문을 사용하여 검색 정확도를 높일 수 있지만, 데이터 양이 증가하면 검색 속도가 크게 느려진다.
Full Scan 방식이기 때문에, 데이터 수가 40 → 200만으로 증가했을 때 검색 속도가 0.4초 → 4.5초로 느려짐을 확인할 수 있었다.

LIKE

MySQL의 LIKE는 와일드카드(%)를 사용할 때 항상 인덱스를 이용하는 것이 아니다.

SELECT * FROM item WHERE name_kor Like '마켓%';

위와 같이 와일드카드가 키워드의 우측에만 있는 경우에는 인덱스를 이용할 수 있어서 Index Range Scan으로 검색한다. 

SELECT * FROM item WHERE name_kor Like '%마켓';
SELECT * FROM item WHERE name_kor Like '%마켓%';

반면에 위와 같이 와일드카드가 키워드의 좌측에 붙은 경우에는 어떤 문자로 시작하는지 알 수 없기 때문에 Full Table Scan으로 검색해야 한다.

개선방향 : item 검색 시 native query로 full text search 적용

MySQL에서 문자열 검색을 위해 LIKE 외에 FullText Search를 제공한다.
검색하고자 하는 column에 FullText Index를 설정해주면, 문자열이 정해진 방법으로 분리되어 인덱스를 생성하고, 이를 빠르게 검색할 수 있다.
검색 키워드와 관령성이 높은 순으로 정렬할 수 있기 때문에 LIKE 대신 FullText Search를 이용해 검색 기능을 제공하기로 결정했다.

FullText Search

검색 방식으로 IN Boolean MODE, IN NATURAL LANGUAGE MODE 2가지를 제공한다.
나의 경우는 IN NATURAL LANGUAGE MODE을 사용하기로 결정했다.
검색어 '과일마켓'을 입력하면 '과일', '마켓' 키워드로 조회되어 '마켓 스티커', '마켓 한정판'과 같은 유사한 결과도 반환되기 때문이다.

Boolean Full-Text Searches

image image

Natural Language Full-Text Searches

image image




회고

LIKE에서 Full-Text 방식으로 변경하면서 검색어가 짧거나 데이터가 적은 경우에는 Full-Text 방식이 35배 빠른 성능이 나왔다.
검색어 길이가 길어질 수록 검색단어를 쪼개어 검색하는 성질이 있어서 연관성이 낮은 결과도 반환되는 단점도 있었다.

검색어> "1234"일 경우

AGAINST('1234' IN NATURAL LANGUAGE MODE)


== 

AGAINST('12 23 34' IN BOOLEAN MODE)

사용자 검색 상황을 고려해봤을 때, 사용자가 원하는 건
누락되는 편보다 연관 키워드 검색결과를 포함해서 받는 쪽이라고 생각했다.
그래서, Full-Text의 IN NATURAL LANGUAGE MODE를 사용하기로 했다.

IN NATURAL LANGUAGE MODE 했을 경우
검색어 : 과일마켓

연관 검색 > "과일" & "마켓" 검색도 포함시킨다.
IN Boolean MODE 했을 경우

'과일마켓'이 포함된 상품만 찾는다.

'프로젝트 > 개인 프로젝트 V3' 카테고리의 다른 글

검색 쿼리 리팩토링  (1) 2024.12.10
거래요청에 대한 동시성 테스트  (2) 2024.12.06
다중 토큰: Refresh 토큰과 생명 주기  (0) 2024.12.04
1:N 조인에서 발생한 중복 데이터 및 누락 문제 해결  (0) 2024.08.10

관련글

댓글

티스토리툴바