[MySql] LIKE 대신 FULLTEXT Index로 검색기능을 구현해보자!

사내 프로젝트 고도화 중 우편번호 검색을 맡게 되었다.

보통의 경우 주소 API를 쓰겠지만

사내 DB에 매달 업데이트 되는 전국주소테이블을 써야했기에 쿼리를 만들어 테스트해봤다.

 

헌데..

이게 무슨 일이고???

필요한 칼럼들 like or like or ... 하니 3.9초나 걸리네??

이건 도저히 못 써먹는 수준 아닌가..

심지어 인덱싱 걸려있는 칼럼들도 있는데 ㅠㅠㅠ

 

도저히 안 되겠다 싶어서 우리의 친구 구글에 이것저것 검색해보니

FULLTEXT가 나와서 적용해보기로 했다!

 

1. FULLTEXT 만드는 법!

ALTER TABLE 테이블명 ADD FULLTEXT INDEX 인덱스명 (컬럼명) WITH PARSER ngram;

 

사용할 테이블에 위의 방식대로 생성하게 되면 FULLTEXT 인덱스가 만들어진다!

 

만약 나처럼 여러개의 칼럼을 같이 검색하고 싶다면

 

ALTER TABLE 테이블명 ADD FULLTEXT INDEX 인덱스명 (컬럼명, 컬럼명, 컬럼명 .....) WITH PARSER ngram;

 

() 안에 넣고 싶은 칼럼들을 넣으면 생성이 완료된다!

 

ex)

-aaa테이블에 aos라는 칼럼을 bbb라는 인덱스이름으로 만들고 싶을 때

ALTER TABLE aaa ADD FULLTEXT INDEX bbb (aos) WITH PARSER ngram;

-aaa테이블에 aos, bos, cos라는 칼럼을 ccc라는 인덱스이름으로 만들고 싶을 때

ALTER TABLE aaa ADD FULLTEXT INDEX ccc (aos, bos, cos) WITH PARSER ngram;

 

 

 

1-1) WITH PARSER ngram > ??

 

ngram은 한글이 지원하며 2글자 단위로 토큰화 해준다!

     ex) 탈개를 꿈꾸다 > ["탈개", "개를", "꿈꾸", "꾸다"]

 

한글 지원해주니 꼭 이거 써주면 된다 ㅎㅎ

기본값이 2여서 2글자 단위로 토큰화 해주는데

만약 3글자, 4글자부터 시작하는 검색을 구현하고 싶으면 따로 설정 해주면 된다!

- 단 이 경우 3글자 미만인 글자들은 파싱 안해주니 주의!

ex) "함께 가즈아"를 3글자 단위로 토큰화하면 '함께'는 파싱 안해줌

 

2. FULLTEXT 쓰는 법!

바쁜 현대사회인을 위해 다 생략하고 바로 적용하기 좋은 걸 알려주자면

 

select * from 테이블명 where match(풀텍스트인덱스를 한 칼럼명) against ('검색어' in boolean mode);

 

이 방식대로 쓰면 된다!

 

ex)

- 예시로 들었던 bbb인덱스 사용법

select * from aaa where match(aos) against ('검색어' in boolean mode);

- 예시로 들었던 ccc인덱스 사용법

select * from aaa where match(aos, bos, cos) against ('검색어' in boolean mode);

 

 

ngram이 2글자 단위로 토큰화 되니

먼저 2글자로 검색해보면!

 

무려 71ms로 단축됐다!!

 

똑같은 두글자로 검색했는데

약 3.9s에서 71ms로 단축됐으니.. 이 얼마나 엄청난 성능차이인지 ㅠㅠㅠ

헌데 여기서 끝나면 안된다!!

 

2-1) 검색어가 3글자 이상일 때 처리하는 방법!

특정 검색어가 3글자 이상일 경우 느려지는 현상이 있을 것이다.

이건 ngram이 2글자 단위로 파싱해줘서 생긴 문제다.

이것에 대한 해결법은

- 검색어가 "풀텍스트 검색"일 경우

select * from aaa where match(aos) against ('+풀텍 +텍스+ +스트 +검색' in boolean mode);

 

이렇게 하면 다시 빠릿하게 검색되는 게 느껴질 것이다!

검색어를 저렇게 넘기는 건 알고리즘을 짜주면 된다

필자의 경우 for문과 substring을 활용해서 넘겨주었다 ㅎㅎ

 

3. FULLTEXT 주의사항!

위에 예시로 들었던

 

ALTER TABLE aaa ADD FULLTEXT INDEX ccc (aos, bos, cos) WITH PARSER ngram;

 

ccc 인덱스를 아래와 같이

 

select * from aaa where match(aos, bos) against ('검색어' in boolean mode);

써도 될까?!

정답은 X다!

 

ccc 인덱스에 aos,bos,cos 칼럼을 걸어놨으니 무조건

(aos,bos,cos) 3개를 다 써야한다!

만약 aos, bos만 쓰고 싶다면 aos,bos를 쓰는 FULLTEXT Index를 새로 만들어주면 된다 ㅎㅎㅎ

 

 

 

 

 

 

 

출처 및 글 인용 --

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%ED%92%80%ED%85%8D%EC%8A%A4%ED%8A%B8-%EC%9D%B8%EB%8D%B1%EC%8A%A4Full-Text-Index-%EC%82%AC%EC%9A%A9%EB%B2%95
https://kabkee.github.io/mysql/mysql-full-text-search/
https://m.blog.naver.com/jjdo1994/222348191751
https://gongzza.github.io/database/mysql-fulltext-search/