1. 문제 상황
- 3백만건의 데이터가 들어있는 Post 테이블이 있고 해당테이블에 "결과없음"을 포함하는 게시글은 하나도 없는 상태입니다.
- 기본적으로 like %word 같은 검색은 인덱스를 탈수있지만 like %word% 같은경우 인덱스를 태울수 없습니다.
- 아래의 Like %word%로 검색 시 "결과없음"이라는 단어를 포함한 결과를 찾기위해 테이블 전체를 찾게되고 응답시간은 5~6초입니다.
- limit으로 인해 full table scan으로 20개의 결과만 찾기 때문에 크게 문제가 안될수 있지만 해당경우 전체 로우를 스캔합니다.
select * from post where title like '%결과없음%' or contents like '%결과없음%' limit 0,20;
- 실행계획은 풀스캔(Type: All)에 Using where인것을 확인
2. 결정 과정
1. post테이블에서 비율을 많이 차지하고있는 인기검색어들을 별도의 인기검색어 테이블을 만들어 분리하기
- 현재 300만건의 데이터가 테이블에 있었고 교육 플랫폼 특성상 자바나 스프링같은 검색어들에 대한 검색결과는 많을것으로 예상되기에 이들을 테이블을에서 분리하는 방법을 고려했었습니다.
- 테이블 데이터 300만건기준 table full scan은 5~6초, 100만건기준 1.8초 정도 되기때문에 테이블에 포함된 인기검색어의 비율이 50%이상이라면 괜찮은 방법이라고 판단했으나, 실제 인기검색어의 비율을 알수없기때문에 조금더 자연스러운 방법을 선택하기로했습니다.
2. fulltext search
- fulltext search는 매번 테이블을 full scan하는것이 단어들의 index를 특정 메커니즘으로 카탈로그에 저장하고있다가 fulltext 쿼리를 실행하면 그때서야 레코드를 찾아 결과를 뿌려줍니다.
- 엘라스틱 서치같은 검색엔진을 바로 도입하기 전 조금더 보수적으로 문제를 해결할 수 있지 않을까 생각이 들어 직접 테스트해보기로 판단했습니다.
3. 검색 엔진
- 엘라스틱 서치와같은 검색엔진은 학습 & 관리비용이 크다고 판단해 최후의 방법으로 고려했습니다.
FullText Search 검색 모드 선택 - 자연어 검색 vs 불린모드 검색
MySQL의 full text search는 2가지 모드중 하나의 방법으로
자연어검색
- 검색문자열을 "단어" 단위로 분리후 해당단어중 하나라도 포함되는행이 있으면 찾게됩니다
불린모드 검색
- 검색 문자열을 단어 단위로 분리 -> 해당 단어가 포함되는 행을 찾는 규칙을 추가적으로 적용하여 해당 규칙에 매칭되는 행을 찾게됩니다.
여기서 속도와 정확도의 트레이드 오프가 발생하는데 자연어 검색으로도 충분한 정확도를 보장한다고 생각했기떄문에 + 불리언모드는 테스트결과 너무 느려서(모든 검색 경우 3초이상) 도저히 쓸수없다고 판단했기에 자연어검색을 선택합니다(느린 속도를 해결하는 여러해결방법도 있었으나 정밀함이 필요한 작업은 아니었기에 간단한 자연어 검색 방법을 선택합니다)
FullText Search 검색 모드 선택 - parser
stop-word parser
- 공백이나 Tab, 문장 기호, 또는 사용자가 정의한 문자열을 기준으로 토큰을 나누는 기법
ex> 아빠가 방에 들어갔다. → 아빠가 / 방에 / 들어갔다.
N-gram parser
- n-gram 기법을 사용하여 할당한 토큰의 크기 n만큼씩 데이터를 인덱스로 파싱해두었다가 사용하는 기법
ex> 아빠가 방에 들어갔다. → 아빠 / 빠가 / 방에 / 들어 / 어갔 / 갔다.
-
기본값(n)은 2로 설정되어있으며, 1부터 10까지 설정할수있지만 사이즈에 따라 검색어의 길이제약이 생겨 일반적으로 기본값을 사용한다.
-
기본값이 2인 특성때문에 검색어는 2글자 이상이어야하며 1글자 검색어는 결과를 가져올수없다.
like 쿼리와 비슷하게 검색할수 있으며 , 좀더 정밀한 검색 정확도를 위해 n-gram parser로 선택합니다.
like%% vs fulltext search
검색결과가 0건인 경우
- fulltext : 0.000~0.110초
- like %word% : 5.0초 ~ 7.0초
검색결과가 77만건정도 되는경우
- fulltext : 1.7초~ 2초
- like %word% : 0.453초~1.78초
검색결과가 100만건정도 되는경우
- fulltext : 1.9~2.1초
- like %word% : 0.15초
3. 문제점 & 한계
- 실제 서비스를 하게 된다면 검색 결과들의 분포가 어떤 특성을 갖게 될지 어려웠습니다. 테이블 크기의 30% 이상의 해당하는 결과를 검색하는 일이 더 잦다면, 오히려 like%word% 방식이 좋을 수도 있습니다.
- 다만, 검색 결과가 0건인 최악의 경우(6초 이상) 보다는 평균적으로 1~2초 내에 검색이 가능한 방식이라는 점에서 좀 더 자연스러운 최선의 방법이라 생각하였습니다.
- fulltext search는 limit 절이 있더라도 검색 데이터를 DB 캐시에 올려두고 최종 출력을 제한할 뿐이기 때문에 검색데이터 자체가 늘어나면 캐시 한도를 초과해 FTS query exceeds result cache limit 오류가 발생할 수 있습니다. 현재
이 설정을 4GB로 수정해서 해결 중이지만, 테이블 데이터가 더 늘어나고 검색 데이터 자체가 늘어나면 다른 방법을 모색해야 합니다.
