Mysql n-gram을 이용한 검색

보통은 검색엔진 elastic search로 검색기능을 개발합니다.

다만, 백오피스 or 사용자가 적음 or 검색엔진 도입하기에는 ROI가 맞지 않는 경우가 생각보다 많습니다.

이런 경우 개인적으로는 mysql n-gram으로 검색 기능을 많이 만듭니다.(일반적으로 RDB는 서비스에서 거의 필수로 사용)

 

관련하여 간단히 내용을 정리해둡니다.

• 개인 메모 목적으로 작성하는 글이라서 생략되는 부분이 많습니다. 구글을 검색해보면 저보다 더 자세히 정리해둔 분들도 많으니 누락된 부분은 해당글을 확인하거나 mysql 공식 문서를 확인해보세요.

 

1. N-gram이란?
   1. 전문 검색에서 보통 많이 쓰며 문자열에서 n개의 연속적인 단어 나열
   2. 예) abcd라는 문자
      1. N이 2라면 ab, bc, cd라는 인덱스
      2. N이 3이라면 abc, bcd라는 인덱스
2. 테이블 생성
   

   -- 테이블 생성
   CREATE TABLE `articles` (
     `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
     `title` VARCHAR(200) NOT NULL COMMENT '제목',
     `body` TEXT COMMENT '본문',
     PRIMARY KEY (`id`,`title`),
     FULLTEXT KEY `title` (`title`,`body`)
   ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='글'​

3. 사용 예
   

   -- 테스트 데이터 insert
   INSERT INTO articles(
   	title,
   	body
   ) 
   VALUES(
   	'국민연금 기금 적립금 900조원 돌파',
   	'25일 국민연금에 따르면 올 2분기 기금 적립금은 908조 3000억 원으로 집계됐다. 연금보험료(655조 4000억 원)와 운용 수익금(502조 3000억 원)을 합산한 금액에 연금급여(239조 6000억 원)와 관리 운영비(9조 8000억 원)를 제외한 게 적립금이다'
   )
   ;
   
   
   -- 쿼리 플랜 확인
   EXPLAIN
   SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('연금' IN NATURAL LANGUAGE MODE);​

쿼리 결과 확인

 

1. n-gram token size
   1. InnoDB에서 n-gram의 최소 토큰 사이즈는 2
      

      

      mysql n-gram 토큰 사이즈 확인

   2. 중국어와 같이 1글자도 처리하고 싶으면 1로 변경
      1. 단, DB에 부하가 올라가겠죠.
2. STOPWORD에 대해서
   1. 영어를 ngram으로 적용하였을 경우, a,for, to와 같은 검색결과 퀄리티를 떨어트리는 단어는 제외됨. 이를 stopword라고 함(참고 링크)
   2. 비 활성화 하고자 하면 innodb_ft_enable_stopword OFF
3. 추가 팁
   1. AWS 오로라 DB는 ngram_token_size 수정 불가 -> 그냥 mysql rds사용
      1. 개인적으로는 오로라 DB가 좋아서 여러 서비스에서 사용 중
      2. 오라라 DB의 엔진이 업데이트되어서 현재는 수정 가능해졌을 수도 있음