안녕하세요. 오늘은 디클 프로젝트에서 검색을 위한 쿼리 성능을 개선해 보겠습니다.

보통 원하는 키워드가 포함된 게시글을 검색하는 쿼리를 다음처럼 작성하곤 합니다.

SELECT * FROM posts WHERE content LIKE '%키워드%'

검색이 안되는 것은 아니지만 content 컬럼에 인덱스가 있더라도 제대로 인덱스를 사용하지 못합니다. content 내용이 정렬되어 있는 것과는 상관없이, 일일이 단어가 포함되어있는지 전부 확인해야하기 때문입니다. 아래는 실제로 프로젝트에서 사용했던 쿼리입니다.

SELECT *
FROM post p1_0
JOIN community c1_0
    ON c1_0.deleted = 0
    AND p1_0.community_id = c1_0.id
JOIN users u1_0
    ON p1_0.user_id = u1_0.id
WHERE p1_0.deleted = 0
  AND (
        p1_0.title LIKE '%파%' ESCAPE ''
        OR p1_0.content LIKE '%파%' ESCAPE ''
      )
ORDER BY p1_0.id DESC
LIMIT 10;

-> Limit: 10 row(s)  (cost=52060 rows=1.05) (actual time=10097..10097 rows=0 loops=1)
    -> Nested loop inner join  (cost=52060 rows=1.05) (actual time=10096..10096 rows=0 loops=1)
        -> Nested loop inner join  (cost=26031 rows=2.1) (actual time=10096..10096 rows=0 loops=1)
            -> Filter: ((p1_0.deleted = 0) and ((p1_0.title like '%파%' escape '') or (p1_0.content like '%파%' escape '')))  (cost=1.83 rows=2.1) (actual time=10096..10096 rows=0 loops=1)
                -> Index scan on p1_0 using PRIMARY (reverse)  (cost=1.83 rows=20) (actual time=2.79..6342 rows=1e+6 loops=1)
            -> Single-row index lookup on u1_0 using PRIMARY (id=p1_0.user_id)  (cost=0.25 rows=1) (never executed)
        -> Filter: (c1_0.deleted = 0)  (cost=0.25 rows=0.5) (never executed)
            -> Single-row index lookup on c1_0 using PRIMARY (id=p1_0.community_id)  (cost=0.25 rows=1) (never executed)

더미 데이터를 100만 건을 넣고 실행하면 위 실행 계획을 얻을 수 있습니다.

처리 순서를 살펴보면 order by로 인해 post를 역순으로 살펴봅니다. 그리고 조건을 만족하는지 Filter로 일일이 검사하는 것을 확인할 수 있습니다. 따라서 이 실행 계획에서 가장 큰 시간 10초를 차지하게 됩니다. 이 문제를 해결하기 위해 전문 검색 인덱스를 사용해 보겠습니다.

전문 검색 인덱스

일반적인 인덱스는 전체 일치 또는 좌측 일부 일치와 같은 검색만 가능합니다. 따라서 문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문 검색에는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없고, 전문 검색 인덱스를 사용해야합니다. 이번 시간에는 간단하게 적용 가능한 n-gram을 사용해 보겠습니다.

n-gram은 본문을 n글자씩 잘라서 인덱싱을 하는 방법입니다.

To be or not to be. That is the question

와 같은 문장이 있다면 to, be, or, no, ot, th, ha, at … 처럼 글자를 자른 후 인덱스에 저장합니다. 이제 기존 테이블에 이 인덱스를 추가하고 성능을 다시 확인해 보겠습니다. 전문 검색을 사용하기 위해선 아래 두 가지가 필요합니다.

테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스를 보유
쿼리 문장이 전문 검색을 위한 문법을 사용한다. (MATCH … AGAINST …)

// 기존 테이블에 전문 인덱스를 추가한다
ALTER TABLE post ADD FULLTEXT INDEX idx_contents (content) with parser ngram;

MATCH ... AGAINST 는 여러가지 모드로 검색할 수 있습니다. 그 중에서도 BOOLEAN MODE 는 토큰 사이즈가 2라면 두 글자씩 끊어서 검색을 하게 됩니다.

## 원래 쿼리
mysql> SELECT * FROM test WHERE match(j) against ('abc' in boolean mode);

## 변환된 쿼리
mysql> SELECT * FROM test WHERE match(j) against ('"ab bc"' in boolean mode);

검색 순서도 성능에 영향을 미치므로 만약 성능이 저하된다면 이러한 문제도 고려해야합니다. 10만 건의 데이터를 넣고 확인을 해보겠습니다. content에 모든 내용은 Dummy Post Content로 일치합니다.

cot 경우는 co, ot 둘 다 토큰이 전부 존재하므로 확인에 더 많은 시간이 필요한 것을 알 수 있습니다. 이 외에도 대소문자 및 특수문자 검색과 같은 문제가 존재합니다.

이제 원래 해결하고자 하는 문제로 돌아와 100만 건의 더미 데이터를 넣고 확인해 보겠습니다.

10097ms -> 176ms 으로 Filter 속도가 개선되었음을 확인할 수 있습니다. 하지만 jpql에서는 MATCH ... AGAINGST 를 지원하지 않으므로 kotlin-jdsl 라이브러리를 사용해 쿼리를 작성하는 것은 어렵습니다. 필요하다면 네이티브 쿼리를 통해 전문 검색 인덱스를 사용할 수 있을 것 같습니다.

데이터베이스 쿼리 성능 최적화 - 와일드카드(%)

전문 검색 인덱스

참고자료