[MySQL] 전문 검색 기능 : FullText Search

🚨 이슈 상황

회사 내부 ERP 시스템에서 검색 기능을 구현하였다. ㅜㅜ

데이터가 많이 쌓이면서 검색이 느리다는 이슈 사항이 있었다.

처리를 하기에 앞서 어떻게 바꾸면 좋을 것인지 생각하면서 정리를 해 본 글이다. 

 

초반에는 Elasticsearch를 이용해서 구현을 해보려고 목표를 잡았으나, 주어진 작업시간이 짧고 리소스가 많이 드는 기술이기 때문에 다른방법을 찾다가 FullText Search에 대해 알게 되었다. 

 

🔎 검색 기능을 구현하기 위한 몇가지 방안 

1. 검색 엔진 Elasticsearch

💡 Elasticsearch 란 ? 

Elasticsearch 는  Apache Lucene( 아파치 루씬 ) 기반의 Java 오픈소스 분산 검색 엔진입니다. 

Elasticsearch를 통해 루씬 라이브러리를 단독으로 사용할 수 있게 되었으며, 방대한 양의 텍스트, 숫자, 위치 기반 정보, 정형 및 비정형 데이터(정해진 규칙이 없는 데이터) 등 모든 유형의 데이터를 신속하게 검색할 수 있습니다. 

 

쉽게 말하면, 애플리케이션에서 사용할 수 있는 '검색 및 분석 엔진'이라고 볼 수 있다. 

 

Elasticsearch는 검색을 위해 단독으로 사용되기도 하며 ELK  Elasticsearch / Logstatsh / Kibana )스택으로 함께 사용되기도 합니다.

 

• Logstatsh 
  • 다양한 소스(DB, csv파일 등)의 로그 또는 트랜잭션 데이터를 수집, 집계, 파싱하여 Elasticsearch 전달 합니다.
• Elasticsearch
  • Logstatsh로부터 받은 데이터를 검색 및 집계를 하여 필요한 관심 있는 정보를 획득합니다.
• Kibana
  • Elasticsearch의 빠른 검색을 통해 데이터를 시각화 및 모니터링하는 도구로서 사용합니다.  

 

💡 장점

• 거의 실시간 검색 수준으로 작동하여 빠르며, FullText 검색에 뛰어나다.
• 오픈소스이다.
• index로 검색하는 색인과 더불어 그 반대인 역색인 검색도 가능하다. 역색인이란, '문서 내의 문자와 같은 내용물'의 매핑 정보를 색인해 놓은 것으로, 색인이 책의 목차라면 역색인은 책 가장뒤의 단어 별 색인페이지와 같다. 
• Kibana를 이용해 비정형 로그 데이터를 수집하고 한곳에 모아 통계 분석을 할 수 있다.
• 검색할 필드명으로 여러 개의 인덱스를 한번에 조회할 수 있다.  

💡 단점

• 실시간 수준으로 작동하지만 완전한 실시간은 아니다. 1초 정도의 시간이 소요된다고 한다. 
• 전체적인 클러스터의 성능 향상을 위해 시스템적으로 비용 소모가 큰 트랜잭션 롤백을 지원하지 않는다. 
• 업데이트 대신 기존 문서를 삭제하고 다시 생성하는 방식으로 업데이트를 제공하지 않는다. 
  업데이트에 비해 많은 비용이 들지만, 이를 통해서 불변성(Immutable)이라는 이점을 취한다. 

 

2. LIKE 키워드 사용 및 한계

MySQL에서 문자열을 찾는 쿼리를 날릴 때 가장 먼저 떠오르는 것은 LIKE 이다.

LIKE 키워드와 와일드카드(%) 를 함께 사용한다. 

하지만, LIKE 는 와일드카드(%) 사용할 때 항상 인덱스를 이용하는 것이 아니다.

 

SELECT * FROM T_BOARD WHERE address LIKE '%A';
SELECT * FROM T_BOARD WHERE address LIKE '%A%';

 

위와 같이 와일드카드가 키워드의 좌측에 붙은 경우에는 어떤 문자로 시작하는지 알 수 없기 때문에 Full Table Scan 으로 검색한다.

 

실행계획(EXPLAIN)으로 확인해보자.

 

실행계획(EXPLAIN)
조회(SELECT)를 할 때, 그 성능이 어느 정도인 지 확인할 수 있는 키워드이다.
type 컬럼의 값을 보고 성능을 판단할 수 있다.

 

💡 성능이 나쁜 순으로 EXPLAIN의 type 나열 

Type	Description
all	테이블 Full Scan 테이블 전체를 조회한다. 성능 개선 필수
index	테이블 Full Scan 테이블 전체를 조회한다. 성능 개선 필수
range	인덱스를 사용한 범위 검색
fulltext	MATCH AGAINST 구문을 사용했을 때 실행
ref	테이블 간의 JOIN에서 PK 또는 Unique Key가 이용되었으며, 데이터가 2건 이상일 때
eq_ref	ref와 같으나 데이터가 1건일 때
const	PK 또는 Unique Key로 조회되었으며 데이터가 단 1건일 때
system	데이터가 없거나 한 개만 있는 경우

 

다음은 테이블에서 address 컬럼에 LIKE 키워드 사용했을 때 데이터를 찾는 실행 계회 조회 쿼리이다. 

explain select * from product where address like '경기도 남양주시*';

결과

위에 결과를 보면 type이 all로 모든 테이블을 조회하는 걸 확인할 수 있다. 성능이 좋지 않다는 뜻이다. 

DB에 데이터가 적을때는 크게 상관없지만 많아진다면 LIKE  키워드를 사용하는 것은 성능상 좋지 않은 방법이라는 것을 볼 수 있다. 

 

 

3. FullText Search 

• 단어나 구문에 대한 검색을 지원하고자 제공되는 방식이다.
• 검색하고자 하는 column에 FullText Index를 설정해주면, 문자열이 정해진 방법으로 분리되어 인덱스를 생성하고, 이를 빠르게 검색할 수 있다. 
• 검색 키워드와 관련성이 높은 순으로 정렬할 수 있고, 추가적인 검색 규칙을 적용할 수 있다. 

 

💡 MATCH AGAINST 사용

MySQL InnoDB 5.6 버전 부터 전문 검색을 위한 MATCH AGAINST 키워드가 추가되었다.

해당 키워드를 사용하기 위해서는 테이블에 FULLTEXT INDEX가 추가 되어야한다.

 

ALTER TABLE product ADD FULLTEXT INDEX idx_ft_address(address);

결과

 

인덱스 추가 후 검색 쿼리를 실행해보자 

SELECT id, name, address FROM product 
WHERE MATCH(address) AGAINST('경기도 남양주시 다산동' IN NATURAL LANGUAGE MODE);

 

🚨 문제점 

위 쿼리로 검색시 검색이 잘 되는 것 같다 

하지만, 여기엔 문제가 있다. FullText Index는 생성시 공백을 기준으로 단어를 저장해두기 때문에 검색하는 단어가 정확히 일치해야만 결과를 받을 수 있다. 

또 하나의 문제는 '경기도 남양주시 다산동'의 검색 결과만 나오는 것이 아니라 '경기도'와 '남양주시', '다산동' 의 검색 결과가 나온다 

즉, 검색의 정확도에 따라 내림차순으로 정렬되어 결과가 표시되고 있다. 

 

결과적으론, 정확히 일치하는 단어가 아니어도 검색이 가능하다는 점이다. 

 

 

💡 N-gram PARSER 사용

FullText Index를 생성할 때 NGRAM PARSER를 함께 추가해주면 위 문제점을 해결할 수 있다. 

NGRAM PARSER 는 기본값인 2글자 단위로 단어가 쪼개서 저장한다.

 

ALTER TABLE product ADD FULLTEXT INDEX idx_ft_address(address) WITH PARSER NGRAM;

테스트를 하기 위에 위 코드를 작성하여 기존 인덱스 삭제 후 인덱스를 추가해준다. 

 

WITH PARSER NGRAM을 추가하지 않으면 Built-in parser로 FullText index 를 기본적으로 만든다. 

Built-in parser는 공백 포함 stop-word등 NGRAM랑 좀 다르다 하지만 한글이 들어가면 NGRAM이 더 유리하다. 

 

SHOW GLOBAL VARIABLES like '%ngram_token_size%'

NGRAM은 토큰 크기에 따라 단어를 잘라서 인덱싱하여 보관하고 있따. 위와 같이 글로벌 변수를 조회하면 

ngram_token_size 사이즈가 default 값으로 '2'로 설정되어있는 걸 볼 수 있다. 

 

즉 토큰사이즈가 '2' 이기 때문에 "화곡역 최고맛집 야생고기" 와 같이 게시판 제목을 저장한다면

- 화곡

- 곡역

- 최고

- 고맛

- 맛집

- 야생

- 생고

- 고기

위와같은 ngram word 인덱스가 생성이 된다. (기본적으로 공백은 무시됨)

그럼 해당 게시판은 위와같은 단어 인덱스에 포함 되어있으므로

우리가 실제 '최고' 혹은 '야생', '고기' 와 같은 단어를 검색한다면 해당 게시판이 검색 결과에 포함되어 진다.

 

그렇다면 4로 설정하면 4단위로 끊어지는 것을 생각할 수 있다. 

공백을 포함한 토큰사이즈는 무시됨으로 실제로 저장되는 토근 단어는 

-최고맛집

-야생고기

두가지 밖에 저장되지 않는다. 

 

 

👉 성능 테스트 

SELECT * 
FROM T_BOARD 
WHERE TITLE LIKE '%12%'
// Count : 49,401 , fetched 804ms


SELECT * 
FROM T_BOARD
WHERE MATCH(TITLE) AGAINST('12' IN BOOLEAN MODE)
// Count : 49,401 , fetched 72ms

새로운 테이블 생성 후 더미 데이터 넣은 후 테스트를 해보았다.

 

'12'를 포함한 데이터를 조회해본 결과 단순 like 검색 결과 10배 빠른 성능이 나왔다. 

 

그러나, 문제점이 있다. token size가 2라서 두개 단어 단위를 쪼개어진 단어 검색만 가능하다는 것이다. 

즉, 다시말해 '1234'를 검색한다고 하면

 

SELECT * 
FROM T_BOARD
WHERE TITLE LIKE '%1234%'
// Count : 300 , fetched 741ms

SELECT * 
FROM T_BOARD
WHERE MATCH(TITLE) AGAINST('1234' IN BOOLEAN MODE)
// Count : 300 , fetched 2412ms

오히려 3.5배 더 느려졌다. 

 

물론 해당 boolean mode를 natural language mode로 변경한다면 200ms로 훨씬 빨라지지만 

우리가 원하는 결과를 가져다 주지 않는다. 

 

natural language mode는 검색단어를 token으로 쪼개어 검색하는 성질이 있어서 

 

AGAINST('1234' IN NATURAL LANGUAGE MODE)
=
AGAINST('12 23 34' IN BOOLEAN MODE)

와 같은 결과 값을 가져 오게 된다. 

즉, '1234'를 검색하는게 아니라 '12', '23', '34'를 포함한 결과값을 합집합으로 가져다준다. 

 

 

💡 BOOLEAN MODE 해결 방안

위와같은 문제점은 검색단어를 '하남고기맛집'으로 6글자 단어로 검색하게 되면 

하남 or 고기 or 맛집에 해당하는 모든 검색 조건을 반환하게 된다. 

그렇게 되면 돼지고기 소고기 등등 원치않는 정보들까지 결과에 포함하게 된다. 

 

하지만 사용자가 '하남고기맛집'을 검색한다면 like 검색이라 하더라도

정확한 '하남고기맛집' 6글자 단어를 포함한 결과만 나오게 된다. 

그렇게 된다면 공백(ex. '하남 고기 맛집')을 포함하거나 조사(~에, ~의, ~의해서)가 포함된(ex. '하남고기의맛집') 검색 결과는 사용자에게 가져다 주지 않을 것이다.

 

하지만 실제 사용자가 원하는 건 

'하남' '고기' '맛집'에 해당하는 키워드들의 적잘한 조합이 포함된 결과를 원할 것이고 

그것을 해결하기 위해서는 조화된 단어들의 교집합 검색 결과를 반환 해주어야 한다. 

 

다시 돌아가서 성능 테스트 '1234' 결과를 제대로 사용하려면

AGAINST('+12 +23 +34' IN BOOLEAN MODE) 와 같은 구문을 사용 해야한다. 

즉 title 내용에 '12' '23' '34' 내용이 모두 포함된 결과를 반환해야한다. 

 

SELECT * 
FROM T_BOARD
WHERE MATCH(TITLE) AGAINST('+12 +23 +34' IN BOOLEAN MODE);
// Count : 418 , fetched 127ms

 

like '%1234%' 구문의 조회수가 300이였는데 위와 같이 조회하니 418건이 조회가 됐다. 

속도도 빠르고 오히려 사용자 입장에서는 같은 맥락에 더 많은 결과를 반환하니 더 좋은 거 같다. 

 

그렇다면 418건에 해당하는 내용중 혹여나 like 검색으로 된 결과값이 누락된건 없을까?

 

SELECT 
  COUNT(1)
FROM (
			SELECT *
			FROM T_BOARD 
			WHERE TITLE LIKE '%1234%'
		  ) T1 INNER JOIN (
			SELECT *
			FROM T_BOARD
			WHERE MATCH(TITLE) AGAINST('+12 +23 +34' IN BOOLEAN MODE)
		  ) T2 ON T1.BOARD_SEQ = T2.BOARD_SEQ 
// Count : 300

 

당연히 없다! 

 

단순 숫자단어를 통해 보면 이해가 안될 수 있지만 저렇게 418건의 데이터가

더 많이 나오는 이유는 '하남고기맛집'으로 6글자 단어를 포함한 정보만을 반환 해주는 것이 아니라

'흑돼지 고기 맛집' '고기 맛집 팀색' '하남 맛집 탐장' 등등 

이렇게 '하남' '고기' '맛집' 이라는 키워드를 순서에 상관없이 연관성 만으로도 결과를 빠르게 반환 해줄 수 있다는 장점이 있다. 

 

그렇다면 실제 서비스에서는 운영해서 사용해본다고 가정을 한다면

사용자 조회 조건으로 '하남시청 돼지고기 맛집'와 같은 요청을 받는다면 서버단에서 해당 단어를 파싱하는 

알고리즘을 적용하여 '+하남' '+시청' '+돼지' '+고기' '+맛집'에 해당하는 연관된 정보를 조회 할 수 있는 쿼리문을 만들어 낼 수 있다. 

 

 

💡 NATURAL LANGUAGE MODE와 BOOLEAN MODE 의 차이점

• 검색의 정확도에 따라 결과가 정렬되지 않는다.
• 구문 검색이 가능하다.
• 필수(+), 예외(-), 부분(*) 등의 연산자를 사용할 수 있다. 

 

 

💡 BOOLEAN MODE의 연산자 목록

Operator	Description
+	AND, 반드시 포함하는 단어
-	NOT, 반드시 제외하는 단어
>	포함하며, 검색 순위를 높일 단어 ’+mysql >tutorial’ → mysql과 turorial가 포함하는 행을 찾을 때, tutorial이 포함되면 검색 순위가 높아짐
<	포함하며, 검색 순위를 낮출 단어 ’+mysql <tutorial’ → mysql과 turorial가 포함하는 행을 찾을 때, tutorial이 포함되면 검색 순위가 낮아짐
()	하위 표현식으로 그룹화 (포함, 제외, 순위 지정 등) ’+mysql +(>tutorial <training)’
~	- 연산자와 비슷하지만 제외 시키지는 않고 검색 조건을 낮춤
*	와일드카드
""	구문 정의

 

그런데 만일 우리가 원하는 정확한 데이터만 조회하도록 한다면 

BOOLEAN MODE 에서 검색어를 쌍따옴포("")로 묶어서 검색해보면 된다. 

 

SELECT * FROM T_BOARD
WHERE MATCH (TITLE) AGAINST('"1234"' IN BOOLEAN MODE);

 

💡 단점

• 특수문자 검색이 되지 않음
• text, binary char, varchar 타입만 가능

---

 

데이터가 얼마 없을 때에는 LIKE 를 고려해보는 것도 나쁘지는 않을것 같지만 

데이터 수가 많아질수록 MATCH검색이 더 빠르다는 결과를 나타내고 있었다.

 

완전한 검색엔진 보다는 성능이 우수하지는 않지만 DB자체 내부적으로 위와같은 인덱스를 제공해주니 좋은거 같다!👍

 

 

 

 

참고

https://blog.naver.com/PostView.nhn?blogId=jjdo1994&logNo=222348191751

https://annajin.tistory.com/218#1.%20%EA%B2%80%EC%83%89%20%EC%97%94%EC%A7%84%20Elasticsearch-1

https://www.essential2189.dev/db-performance-fts