[TIL] 07/25 항해99 78일차 - MySQL 검색기능 : FullText Search

 

들어가며

프로젝트에 음식점 검색 기능을 구현해야했다.

 

초반에는 Elasticsearch를 이용해서 구현을 해보려고 목표를 잡아두었었는데, Elasticsearch의 러닝커브가 높고 리소스도 많이 드는 기술이기 때문에 다른 방법을 찾다가 FullText Search에 대해 알게 되었다.

 

 

오늘의 TIL은 Elasticsearch에 대해 간단하게 알아보고, MySQL에서 제공하는  FullText Search에 대해 정리하고자 한다.

 

---

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

1. 검색 엔진 Elasticsearch

Elasticsearch란?

Elasticsearch는 루씬(Lucene)을 기반으로 텍스트, 숫자, 위치 기반 정보, 정형 및 비정형 데이터 등 모든 유형의 데이터를 위한 무료 검색 및 분석 엔진이다.

 

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

 

일반적으로 Logstash와 Kibana와 함께 사용되며,

Logstash는 데이터를 집계하고 처리하여 Elasticsearch로 전송하는데 사용되고 Kibana는 Elasticsearch를 시각화하고 관리하기 위한 도구로서 사용된다.

 

 

비정형 데이터 - 정해진 규칙이 없는 데이터

 

 

장점

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

 

단점

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

 

 

 

2. LIKE 키워드 이용하기

MySql에서는 LIKE 를 이용하여 문자열을 검색할 수 있다. LIKE 키워드는 와일드카드(%)와 함께 사용된다.

 

하지만 LIKE 키워드는 인덱스가 추가되어 있더라도 속도가 느리다. 특히 한국어 검색에서는 더욱 성능이 떨어진다.

 

 

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

 

 

실행 계획(EXPLAIN)
조회(SELECT)를 할 때, 그 성능이 어느 정도인 지 확인할 수 있는 키워드이다.
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	데이터가 없거나 한 개만 있는 경우

 

 

다음은 store 테이블에서 name 컬럼이‘실크로드'인 데이터를 찾는 실행 계획 조회 쿼리이다.

explain select * from store
where address like '서울시 강남구%';

 

결과

 

 

 

type은 all으로 모든 테이블을 조회하는 걸 확인할 수 있다. 성능이 매우 좋지 않다는 말이다.

 

지금은 DB에 데이터가 적지만 앞으로 더 많은 데이터가 추가될 것을 생각하면 LIKE 키워드를 사용하는 건 성능상 매우 좋지 않은 방법인 것 같다.

 

 

 

3. FullText Search - MATCH AGAINST 키워드 이용하기

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

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

 

 

FULLTEXT INDEX 추가하기

 

ALTER TABLE store ADD FULLTEXT INDEX idx_ft_name (name);
ALTER TALBE store ADD FULLTEXT INDEX idx_ft_address (address);

 

결과

 

 

 

인덱스를 추가하였다면 검색 쿼리를 날려보자

SELECT id, name, address FROM store
WHERE MATCH (name) AGAINST('실크로드' IN NATURAL LANGUAGE MODE);

 

 

결과

 

 

 

언뜻 보면 검색이 잘 되는 것 같다.

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

 

 

name 컬럼으로 검색하는 건 검색 결과가 적으므로 이번에는 address 컬럼을 이용해서 검색을 해보자

 

 

SELECT id, name, address FROM store
WHERE MATCH (address) AGAINST('서울시 강남구' IN NATURAL LANGUAGE MODE);

 

 

결과

 

 

 

 

‘서울시 강남구'가 포함된 문자열이 잘 검색된 걸 확인할 수 있다.

 

결과에서 또 하나의 문제를 발견할 수 있는데, ‘서울시 강남구'의 검색 결과만 나오는게 아니라 ‘서울시'와 ‘강남구'의 검색 결과가 나온다. 즉, 검색의 정확도(score)에 따라 내림차순으로 정렬되어 결과가 표시되고 있다.

 

 

이 문제는 뒤에서 다루기로 하고 이번에는 정확히 일치하는 단어가 아닌 ‘서울시 강남'으로 검색을 해보자.

 

 

SELECT id, name, address FROM store
WHERE MATCH (address) AGAINST('서울시 강남' IN NATURAL LANGUAGE MODE);

 

 

결과

 

 

 

얼핏보면 잘 검색된 것 같지만 잘 보면 위의 검색 결과와 다르다.

우리가 원하는 대로 공백으로 구분된 단어인 ‘서울시'로만 검색된 걸 확인할 수 있다.

 

 

우선 첫번째 문제인 ‘정확히 일치하는 단어가 아니어도 검색'이 가능하도록 해보자

 

 

NGRAM PARSER

FullText index를 생성할 때 ngram parser를 함께 추가해주면 이 문제를 해결할 수 있다.

ngram parser는 기본값인 2글자 단위로 단어를 쪼개서 저장한다.

 

 

ngram parser를 이용하여 index를 다시 생성해보자.

 

// 기존 index 삭제
ALTER TABLE store DROP INDEX idx_ft_name;
ALTER TABLE store DROP INDEX idx_ft_address;

// ngram parser 추가
ALTER TABLE store ADD FULLTEXT INDEX idx_ft_name(name) WITH NGRAM PARSER;
ALTER TABLE store ADD FULLTEXT INDEX idx_ft_address(address) WITH NGRAM PARSER;

 

 

이제 다시 ‘서울시 강남'으로 검색을 해보자

 

 

결과

 

 

 

원하는 대로 잘 검색이 되는 걸 확인할 수 있다.

 

 

 

이제 두번째 문제인 ‘정확히 일치하는 검색 결과’ 만 표시하도록 해보자.

 

 

기존에 사용한 자연어 모드 검색인 NATURAL LANGUATE MODE는 ‘서울시 강남구' 검색어를 공백으로 구분해서 ‘서울시'와 ‘강남구'로 검색하고, 검색의 정확도(score)에 따라 데이터를 내림차순 정렬하여 결과를 표시하는 반면 불린 모드 검색인 BOOLEAN MODE를 사용하면 검색의 정확도를 높일 수 있다.

 

 

자연어 모드 검색과 구분되는 불린 모드 검색의 차이점은 다음과 같다.

 

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

 

불린 모드 연산자 목록

 

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

 

 

이번에는 ‘서울시 강남구'가 정확히 포함된 데이터만 조회하도록 해보자.

이번에는 BOOLEAN MODE 로 변경하고, 검색어를 쌍따옴표("") 로 묶어서 검색해보자.

 

 

SELECT id, name, address FROM store
WHERE MATCH (address) AGAINST('"서울시 강남구"' IN BOOLEAN MODE);

 

 

결과

 

 

우리가 원하는 검색 결과가 표시된다.

 

 

쌍따옴표("") 외에도 다음과 같이 검색이 가능하다.

 

‘강남’으로 검색을 해보면 ‘강남구'외에도 다른 검색 결과도 표시된다.

SELECT id, name, address FROM store
WHERE MATCH (address) AGAINST('강남' IN BOOLEAN MODE);

 

 

결과

 

 

 

여기서 ‘서초구'가 포함된 검색 결과를 제외하고 싶다면 검색 키워드에 - 를 붙여주면 된다.

SELECT id, name, address FROM store
WHERE MATCH (address) AGAINST('강남 -서초구' IN BOOLEAN MODE);

 

 

 

결과

 

---

마치며

MATCH AGAIN와 LIKE를 테스트 해보던 중에, 검색어가 짧거나 데이터가 적은 경우에는 LIKE 검색이 4배정도 빠르다는 걸 확인했다.

 

초반에 데이터가 100개정도밖에 없을 때 이런 결과를 나타냈는데, 데이터 수가 10000개 가까이 될 수록 MATCH 검색이 더 빠른 결과를 나타내고 있었다. 

 

LIKE가 성능이 더 안좋은 건 확실하지만 이렇게 데이터가 적은 경우에는 LIKE 검색을 고려해보는 것도 나쁘지 않을까 라는 생각을 하며 이번 TIL을 마친다.

 

---

출처 및 참고

 

[Elastic Search] 기본 개념과 특징(장단점)

 

Elasticsearch란?