검색 쿼리 개선 (LIKE -> Full Text Search)

LIKE

현재 Ori 서비스의 검색 기능은 다음과 같은 쿼리로 짜여져 있다.

SELECT new CodePostResponseDto(c,m) FROM CodePost c 
INNER JOIN Member m ON c.writerId = m.id 
WHERE c.title LIKE %:keyword% 
ORDER BY c.id DESC

기본적으로 검색 쿼리를 구현한다고 하면, 가장 먼저 생각할 수 있는게 LIKE 문이다.

많은 양의 데이터가 있는 테이블이 아니고, 검색도 빈번하게 발생하는게 아니라면 별 문제없겠지만,

데이터가 100만건, 1000만건 또는 억 단위가 되면 문제가 발생할 것이다.

LIKE 문의 동작 방식과 Index와의 관계

---

Like 구문을 사용한 검색 쿼리 성능 이슈

100명이 동시에 1초동안 검색을 하는 상황을 가정하고 테스트를 진행한다.

post 10000개를 미리 생성해 두고 여러 keyword 들로 테스트를 해보았다.

성능 테스트를 위해 페이지네이션 미적용 상태로 진행을 했다.

테스트이기 때문에 현재 모든 post의 title은 "Sample Title (1~40 랜덤 숫자)"이다.

 

keyword = 'Sample'(접두사)

해당 쿼리는 모든 데이터 조회이다. 

http_req_duration에 P(90)과 P(95)가 2.x초인 것을 볼 수 있다.

아무리 접두사 검색은 인덱싱 되어있다 해도 전체 검색이기 때문에 응답 속도가 느린 것을 볼 수 있다.

---

keyword = 'title 10'

특정 포스트를 검색할 경우 P(90) = 723.78ms, P(95) = 929.4ms 로 전체적으로 성능 개선이 필요하다는 것을 확인했다.

 

테이블 풀스캔을 통해서 조건에 맞는 데이터를 찾는 수 밖에 없기 때문에 위와 같은 응답 속도가 나온 것이다.

그렇기 때문에 데이터 양이 많은 테이블에 대해서 검색 기능을 구현하기 위해서 Full-text search 기능을 적용해 보겠다.

---

Full-Text Search

 

Full-Text Index 및 Search에 관하여

Full-Text Search 사용법

 

InnoDB에서 기본적으로 가지고 있는 Parser 중에서 ngram parser 라는 것이 있다.

위의 링크에서 언급했듯이, ngram parser를 통해서 문장을 파싱하면 어절 단위로 파싱하는 것이 아니라 최소 Token 사이즈 만큼 조각내어서 파싱을 하게 된다.

 

결론적으로, %LIKE% 문을 활용하는 것과 유사한 검색 기능을 구현하고자 한다면, 이렇게 ngram parser로 Index를 만들어야한다.

post 테이블에 ngram full-text index 생성

 

성능 테스트를 하기 전에 하나 알아두어야 하는 것이 있다.

기존 쿼리에서는 SELECT new Dto().... 방식으로 JPQL 에서만 가능한 DTO 매핑 방식을 사용하고 있는데

MATCH() 와 AGAINST()는 native query이기 때문에 사용할 수 없어서 쿼리를 수정해 주겠다.

 

@Query(value = "SELECT * FROM code_post c WHERE MATCH(c.title) AGAINST(:keyword IN BOOLEAN MODE)",
        countQuery = "SELECT COUNT(*) FROM code_post c WHERE MATCH(c.title) AGAINST(:keyword IN BOOLEAN MODE)",
        nativeQuery = true)
Page<CodePost> search(@Param("keyword") String keyword, Pageable pageable);

Page<CodePostThumbnailResponseDto> codePostPage = codePostRepository.search(keyword, pageable)
        .map(post -> {
            Member member = memberCommand.findById(post.getWriterId());
            return new CodePostThumbnailResponseDto(post, member);
        });

---

 

Full-Text Search 성능 테스트

keyword = 'Sample' (접두사 검색, ngram parser)

http_req_duration에 P(90)과 P(95)가 6~7초인 것을 볼 수 있다.

Full-Text로 모든 데이터를 조회하면 성능이 매우 안좋은 것을 볼 수 있다.

이유를 알아보니 검색 keyword가 접두사이기 때문에 LIKE 구문에서 좀 더 나은 성능을 보여준 것도 있고,

FULL-TEXT Search 같은 경우에는 ngram으로 파싱했기 때문에 매칭 범위가 커져서 검색 성능이 떨어질 수도 있다.

 

ngram parser는 주로 복잡한 단어 조합이나 의미 기반 검색에서 유리하기 때문에 부분적인 단어 매칭이나 형태소 분석에 쓰이지만, Ori 서비스에서 검색할 키워드를 생각해보자면 알고리즘 이름이나, 문제를 푼 사이트 이름(ex: 백준,BOJ,Backjoon,프로그래머스 등..) 단순한 단어들이기 때문에 ngram parsing은 불필요한 옵션인 것 같다.

 

그렇다면 built-in parser에서는 어떨까?

기존에 생성한 인덱스를 삭제하고 built-in parser 인덱스를 새로 생성해서 테스트 해보겠다.

built-in parser로 생성된 인덱스로 성능 테스트해보니 LIKE 구문과 비슷한 결과가 나왔다.

하지만 LIKE 구문도 접두사로 검색할 경우 index를 통해 조회되기 때문에 성능이 비슷한 것이고 중간 키워드를 검색해보면 다를 것이다.

---

 

keyword = 'title 10'(중간 키워드,ngram parser)

특정 포스트를 검색할 경우 P(90) = 689.06ms, P(95) = 730.49ms 의 응답 속도를 가진다.

 

keyword = 'title 10'(중간 키워드,built-in parser)

특정 포스트를 검색할 경우 P(90) = 579.03ms, P(95) = 749ms 의 응답 속도를 가진다.

ngram parser 옵션보다 미세하지만 성능 면에서 우세하다는 것을 볼 수 있다.

---

LIKE 와 Full-Text Search 성능 차이

Percentile	LIKE	FULLTEXT	성능 향상 (ms)	성능 향상 (%)
P90	723.78 ms	579.03 ms	144.75 ms	약 20.0% 빠름
P95	929.4 ms	749.0 ms	180.4 ms	약 19.4% 빠름

GPT 생성 그래프

이처럼 RDB에서 할 수 있는 검색 성능 향상에 대해서 알아보았다.

물론 또다른 방법으로 검색엔진을 사용하는 방법도 있겠지만, Ori 서비스는 티켓팅과 같이 검색이 자주 일어나는 서비스는 아니기 때문에 오버 스펙이라 판단되어 포스팅은 여기서 마치도록 하고 간단하게 Elasticsearch가 무엇인지만 남겨두고 가겠다.

---

Elasticsearch

Elasticsearch란?

---

2025-04-25 추가내용

기존에 했던 테스트들에서의 키워드들이 부적절하다 생각되어 다시 한번 테스트를 진행하였다.

db에 좀 더 다양한 데이터를 넣어서 테스트 해보았다.

테스트 데이터

Full Text Index 로 접두사인 '백준'으로 검색할 경우  LIKE 구문보다 0.6초 더 늦는걸 볼 수 있다.

FullText는 내부적으로 더 많은 처리 과정이 있기 때문일 수 있습니다.

• 텍스트 토큰화 (단어 분리)
• 불용어(stopword) 제거
• 어간 추출(stemming) 또는 유의어 확장

그래서 인덱스를 사용하더라도, 대용량 데이터에서 처리량이 많아져서 오히려 느릴 수 있습니다.