연관글
MySQL 검색 기능 개선하기 : Full-text search
들어가며
최근 영상에 데이터 라벨링을 하기 위한 REST API와 WAS를 구축하고 있습니다. 이전에 여러번 진행했던 토이 프로젝트들과는 다르게 서비스를 구축하다보니 기술 스택 설정부터 모호한 서비스 요구사항 등등.. 이런저런 새로운 도전들을 하게되었습니다.
가장 새로운 도전 중 하나는 그동안 제 2의 고향과 같았던 🥹 MySQL을 떠나 다른 RDB를 사용하게 된 것인데요. 같은 RDB라 크게 달라질 것이 없다고 생각했지만.. 역시 세상에는 거저 지나가는 것들이 없었습니다.
💡 여담으로 제대로 PostgreSQL의 장점을 사용해보지 못한 상태에서, MySQL과 PostgreSQL의 가장 큰 차이점으로 느끼는 것은 auto_increment로 PK 생성할 때의 기본 전략입니다. MySQL은 LAST_INSERT_ID(); 로 마지막에 삽입된 레코드 PK를 가져오지만 PostgreSQL 은 sequence 변수가 존재하여 이를 증가시키며 PK를 정합니다.
- 관련해서 겪은 에러 : Spring data JPA: Hibernate가 Sequence를 못찾아요
- 참고글 : Access last inserted row in MySQL?
이번에는 검색 요구사항에 맞닥뜨리게 되었습니다. 회원 이름으로 회원에게 지급할 금액과 작성한 내역을 검색하는 것이었습니다. 회원이 천명 단위가 되지 않을 소규모 애플리케이션으로 예상되기 때문에 별도의 검색엔진을 구현하는 것보다 간단하게 DB에서 처리해주려고 하였습니다. 간단하게 검색 기능을 구현하는 가장 간단한 방법으로 PostgreSQL에서 Full text Search를 구현해보았습니다.
PostgreSQL의 모듈 pg_bigm
PostgreSQL은 Full text Search를 지원하는 pg_bigm 모듈이 있습니다. 이 모듈을 사용하면 사용자가 더 빠른 검색을 할 수 있도록 bigram(2gram) 색인을 생성할 수 있습니다.
pg_trgm과 pg_bigm
pg_bigm은 pg_trgm을 기반으로 개발된 모듈입니다. 알파벳이 아닌 언어에 대해 지원하고, 구문 일치 범위가 3gram에서 2gram으로 좁아져서 더 작은 단어도 빠르게 검색할 수 있습니다.
다음은 공식문서에서 참고한 비교표입니다.
| 기능 및 특징 | pg_trgm | pg_bigm |
|---|---|---|
| 구문 일치 방법 | 3그램 | 2그램 |
| 사용 가능한 인덱스 | GIN, GiST | 진만 해당 |
| 사용 가능한 텍스트 검색 연산자 | LIKE(~~), LIKE(~~*), ~, ~* | LIKE only |
| 알파벳이 아닌 언어 (한국어, 일본어 등) | 지원되지 않음(*1) | 지원됨 |
| 1~2자 키워드 | 느림(index full scan) | 빠름 |
| 유사성 검색 | 지원됨 | 지원됨(버전 1.1 이상) |
| 최대 인덱스 열 크기 | 238,609,291바이트(~228MB) | 107,374,180바이트(~102MB) |
Amazon RDS for PostgreSQL에서 구현
install / Uninstall
모듈 활성화를 위해 다음의 쿼리를 실행합니다.
-- 모듈 활성화
CREATE EXTENSION pg_bigm;
-- 모듈 삭제를 원할 때
DROP EXTENSION pg_bigm CASCADE;모듈활성화 내역을 확인할 수 있습니다.
SELECT * FROM pg_extension
Name | Version | Schema | Description
---------+---------+--------+---------------------------------------
pg_bigm | 1.1 | public | text index searching based on bigrams
Index 생성
1개의 컬럼만 사용할 때의 쿼리입니다.
CREATE INDEX 인덱스명 ON pg_tools USING gin (컬럼명 gin_bigm_ops);
CREATE INDEX pg_tools_idx ON pg_tools USING gin (description gin_bigm_ops);만약 여러 개의 컬럼에 대해 인덱스를 생성하고 싶다면 다음과 같이 쿼리를 실행하면 됩니다.
CREATE INDEX 인덱스명 ON pg_tools USING gin (컬럼1명 gin_bigm_ops, 컬럼2명 gin_bigm_ops) WITH (FASTUPDATE = off);
CREATE INDEX pg_tools_multi_idx ON pg_tools USING gin (tool gin_bigm_ops, description gin_bigm_ops) WITH (FASTUPDATE = off);
-- 생성되는 인덱스를 미리 확인할 수 있는 쿼리
SELECT show_bigm('안녕하세요');
-- 생성되는 인덱스
{녕하,세요,안녕,요 ,하세, 안}그리고 원래 쿼리대로 검색했을 때 조건(검색어가 2글자 이상)을 만족하면 쿼리 실행에 인덱스를 사용하는 것을 확인할 수 있습니다.
-- index가 없을 때
+--------------------------------------------------------+
|QUERY PLAN |
+--------------------------------------------------------+
|Seq Scan on member (cost=0.00..10.12 rows=1 width=4152)|
| Filter: ((name)::text ~~ '%이름%'::text) |
+--------------------------------------------------------+
그런데 예상치못한 상황이 발생했습니다. 테이블 row가 너무 적어서인지 full scan을 하고 있습니다. 이럴땐 full scan이 더 빠르기 때문일까요? full scan시에는 DB에 random access를 하지 않으니까요? (가설)
- 주의할 점
- 너무 테이블 row가 적어서인지 index가 있어도 사용하지 않습니다.
CREATE INDEX full_index_video ON public.label_video USING gin (title gin_bigm_ops);
EXPLAIN ANALYZE SELECT * FROM public.label_video WHERE label_video.title LIKE '%LH%';
+-------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+-------------------------------------------------------------------------------------------------------+
|Seq Scan on label_video (cost=0.00..4.19 rows=95 width=114) (actual time=0.010..0.033 rows=95 loops=1)|
| Filter: ((title)::text ~~ '%LH%'::text) |
|Planning Time: 0.073 ms |
|Execution Time: 0.055 ms |
+-------------------------------------------------------------------------------------------------------+
데이터 100건일 때, 인덱스를 사용하지 않습니다.
성능 개선 확인
데이터를 1만건 랜덤으로 넣고 실행하니 다음과 같이 index가 반영되었음을 알 수 있습니다.
- index생성하지 않았을 때
+--------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+--------------------------------------------------------------------------------------------------+
|Seq Scan on member (cost=0.00..369.04 rows=1 width=156) (actual time=0.008..1.195 rows=1 loops=1)|
| Filter: ((name)::text ~~ '%이름%'::text) |
| Rows Removed by Filter: 10002 |
|Planning Time: 0.184 ms |
|Execution Time: 1.211 ms |
+--------------------------------------------------------------------------------------------------+
- index를 생성한 후
+---------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+---------------------------------------------------------------------------------------------------------------------------+
|Bitmap Heap Scan on member (cost=12.01..16.02 rows=1 width=156) (actual time=0.013..0.014 rows=1 loops=1) |
| Recheck Cond: ((name)::text ~~ '%이름%'::text) |
| Heap Blocks: exact=1 |
| -> Bitmap Index Scan on full_index_work_log (cost=0.00..12.01 rows=1 width=0) (actual time=0.009..0.010 rows=1 loops=1)|
| Index Cond: ((name)::text ~~ '%이름%'::text) |
|Planning Time: 0.164 ms |
|Execution Time: 0.039 ms |
+---------------------------------------------------------------------------------------------------------------------------+
성능 개선을 확인한 결과 실행시간 1.211ms 에서 0.039ms로 약 95% 개선된 것을 확인할 수 있었습니다.
