[MySQL] 자연어 검색 (FULLTEXT)

https://www.reddit.com/r/ProgrammerHumor/comments/krt1ua/oursql_mysql/?rdt=61200

 

 

기본적으로 검색 쿼리를 작성할 때, 기본적을 와일드카드를 사용하여 LIKE 검색을 이용하는 것을 떠올릴 것이다. 이 방법은 가장 기본적이며 사용하기 쉬워 어렵지 않게 적용을 할 수 있으나, 데이터가 많아지면 무조건 성능 이슈를 맞이하게 된다. LIKE를 사용할 때 크게 고려해야 할 부분은 Index 설정에 대한 고려와 와일드카드의 쓰임 그리고 풀스켄에 대한 고려 등이 있을 것 같다. 이에 대한 대안이 될 수 있는 기능으로 MySQL의 Full-Text Search 기능이 있으며, 이 기능에 대한 간단한 배경, 검색 메커니즘, 활용법 등에 대한 내용을 다루려고 한다.

---

# Full-Text Search란?

Full-Text Search는 데이터베이스에서 텍스트 데이터를 검색하기 위한 기술로, 특정 키워드나 문장 구조에 따라 데이터를 필터링하고 결과를 반환한다. 이는 DBMS에서 제공하는 전문 검색 기능으로 정보 검색 이론 (IR: Information Retrieval)을 기반으로 한다고 한다.

 

## 정보 검색(IR) 의 핵심 요소

- 단어 분리(Tokenization): 입력 텍스트를 단어 단위로 분리하여 처리.
- 역색인(Inverted Index): 각 단어와 이를 포함하는 문서의 매핑을 저장.
- 검색 알고리즘: 사용자가 제공한 쿼리를 기반으로 관련 문서를 찾는 방법론.

Full-Text Search는 위와 같은 정보 검색 핵심 요소를 지키며 아래와 같은 특징을 제공하고 있다 설명하고 있다.

- 속도 향상: 역색인을 사용하여 대량 데이터에서도 빠르게 검색
- 유연한 검색 모드: 자연어 처리 및 Boolean 검색 지원
- 관련성 정렬: 검색 결과를 중요도 순으로 정렬

---

# Full-Text Search의 검색 모드

MySQL의 Full-Text Search는 여러 가지의 검색 모드를 지원한다. 대표적인 검색 모드는 자연어 검색(Natural Lagnuage Search)과 Boolean 검색(Boolean Full-Text Search)이 있다.

## 자연어 검색 (Natural Language Search)

자연어 검색은 사용자가 입력한 검색어를 인간이 작성한 문장처럼 해석한다. 불용어(Stopwords)와 같이 자주 등장하는 단어는 검색 과정에서 무시되며, 검색 결과는 관련도 검사(Relevance Score)에 따라 정렬된다.

### IF-IDF 알고리즘

자연어 검색의 점수 계산은 TF-IDF(Term Frequency-Inverse Document Frequency) 알고리즘을 활용한다.

- TF: 단어 빈도(Term Frequency)
- IDF: 역문서 빈도(Inverse Document Frequency), 특정 단어가 얼마나 드물게 등장하는지를 측정.

이 알고리즘은 검색어와 문서 간의 연관성을 정확히 평가하며, 검색 품질을 높이는 핵심 요소로 작용한다.

 

### 예제

SELECT * FROM articles
WHERE MATCH(title, body)
AGAINST('machine learning' IN NATURAL LANGUAGE MODE);

 

위 쿼리는 "machine learning"과 가장 관련성 높은 문서를 반환하며, 높은 TF-IDF 점수를 가진 문서가 우선적으로 반환된다.

자연어 검색의 유용성은 단순한 키워드 일치 이상으로, 문맥적 관련성을 평가하는 데 있다. 이는 검색 효율성뿐만 아니라 사용자 경험에도 큰 개선을 가져올 수 있다.

## Boolean 검색 (Boolean Full-Text Search)

Boolean 검색은 논리 연산자를 사용하여 고급 검색 쿼리를 구성할 수 있다. 이 검색 모드는 필수 조건, 제외 조건, 와일드카드와 같은 요소를 통해 정교한 검색을 가능하게 한다고 한다.

### 주요 연산자

1. ` + `: 반드시 포함되어야 하는 단어.
2. ` - `: 제외해야 하는 단어.
3. ` * `: 와일드카드(접두사 매칭).
4. ` " `: 정확한 구문 검색.

 

### 응용 사례와 확장성

Boolean 검색은 복잡한 데이터 분석 및 고급 검색 기능을 제공하며, 데이터 정규화나 로그 분석과 같은 고차원적 활용이 가능하다. 예를 들어 아래와 같은 사용 사례가 있다.

- 로그 분석: 특정 키워드와 로그 수준을 동시에 검색
- 데이터 정리: 포함 조건과 제외 조건을 결합하여 특정 패턴을 찾기
- 제품 필터링: 전자상거래 시스템에서 상세 검색 제공

 

### 예제

SELECT * FROM articles
WHERE MATCH(title, body)
AGAINST('+AI -artificial +deep*' IN BOOLEAN MODE);

 

위 쿼리는 "AI"를 반드시 포함하고, "artificial"을 제외하며, "deep"으로 시작하는 단어를 포함하는 데이터를 반환한다. 이러한 방식은 복잡한 검색 조건을 다룰 때 적합하며, 데이터의 정확한 분류와 필터링을 지원한다.

 

Boolean 검색은 단순한 텍스트 매칭을 넘어 논리적 구조를 기반으로 한 텍스트 필터링을 가능하게 하여, 데이터 활용 범위를 넓힐 수 있다.

 

## 쿼리 확장 (Query Expansion)

쿼리 확장은 초기 검색 결과에서 관련 키워드를 자동으로 추출하여 검색 범위를 확장한다. 이는 검색어가 모호하거나 짧을 때 특히 유용하다고 한다.

 

### 작동 방식

1. 초기 검색 결과에서 상위 문서의 키워드를 분석.
2. 추가 키워드를 사용해 새로운 검색 쿼리를 생성.
3. 확장된 결과를 반환.

 

### 이점과 한계

쿼리 확장은 데이터 분석 및 연구 활동에서 유용함을 제공한다. 사용자가 명확한 검색어를 입력하지 못했을 때도 관련 결과를 찾을 수 있게 해 준다. 다만, 검색 범위가 확장됨에 따라 관련성이 낮은 결과도 포함될 수 있다는 단점이 있다.

 

### 예제

SELECT * FROM articles
WHERE MATCH(title, body)
AGAINST('neural networks' WITH QUERY EXPANSION);

 

이 쿼리는 "neural networks"와 직접적인 관련이 없는 데이터도 포함하여 더 넓은 범위의 결과를 반환한다. 예를 들어 "deep learning"이나 "artificial intelligence"를 포함한 문서도 검색된다. 이 방식은 복잡한 키워드 간의 연관성을 탐색할 때 유용하다.

---

# Full-Text Index 데이터 타입

Full-Text Index는 다음과 같은 데이터 타입에서 사용이 가능하다.

- CHAR

- VARCHAR

- TEXT

---

# 검색 성능 최적화

대규모 데이터베이스에서 Full-Text Search의 성능을 유지하려면 최적화 전략이 필요하다.

 

##인덱스 최적화

### 인덱스 생성 시기

대량 데이터를 삽입한 후 인덱스를 생성하면 데이터 삽입 속도가 빨라진다.

ALTER TABLE articles ADD FULLTEXT INDEX(title, body);

### ngram 파서 활용

중국어, 일본어, 한국어와 같은 언어를 다룰 때는 ngram 파서를 사용하여 단어 경계 없이 텍스트를 검색한다.

CREATE FULLTEXT INDEX ft_index ON articles(title, body) WITH PARSER ngram;

## 단편화 방지

`OPTIMIZE TABLE` 명령어를 사용해 테이블 단편화를 최소화할 수 있습니다. 

OPTIMIZE TABLE articles;

이 명령은 데이터 삽입, 삭제 및 업데이트로 인해 비효율적으로 변한 인덱스를 재구성하여 성능을 유지한다.

---

# InnoDB와 MyISAM: Full-Text Search의 선택

MySQL에서 Full-Text Search는 InnoDB와 MyISAM 엔진에서 제공되며, 두 엔진의 동작 방식은 다르다.

 

## InnoDB

- 트랜잭션 지원: 데이터 일관성을 보장.
- Stopword 유연성: 사용자 정의 가능.
- 병렬 처리: 대규모 데이터에서 안정적인 성능 제공.

InnoDB는 확장성과 안정성이 중요한 애플리케이션에서 특히 유리하다. 트랜잭션 지원을 통해 데이터 일관성을 유지하며, 대규모 시스템에서 병렬 처리 기능을 통해 높은 성능을 제공한다. 또한 사용자 정의 Stopword 리스트를 통해 특정 도메인에서 최적화된 검색 설정이 가능하다.

예를 들어, 의료 데이터베이스에서 'patient', 'doctor'와 같은 일반적인 단어는 검색 범위에서 제외하는 Stopword로 설정하여 중요한 데이터의 검색 효율을 높일 수 있다.

 

## MyISAM

- 즉각적인 인덱싱 반영: 데이터가 추가 또는 수정될 때 FULLTEXT 인덱스 즉시 업데이트

- 경량 구조: 적은 리소스 사용, 단일 애플리케이션이나 비트랜잭션 데이터페이스에 유용

- 50% 규칙 적용: 특정 단어가 전체 데이터의 50% 이상에 포함된 경우 검색에서 무시

 

MyISAM은 비교적 단순한 데이터베이스에서 뛰어난 성능을 발휘하며, 트랜잭션 지원이 필요 없는 응용 프로그램에서 적합하다. 다만, 대규모 데이터베이스나 다중 사용자 환경에서는 InnoDB 보다 안정성이 떨어질 수 있다.

---

# 고급 활용 사례

## 다중 칼럼 검색

MySQL의 Full-Text Search는 단일 칼럼뿐만 아니라 다중 컬럼에서도 효과적으로 작동한다.

이 기능은 복합적인 데이터 구조를 다루는 시스템에서 유용하다.

SELECT * FROM articles
WHERE MATCH(title, body, tags)
AGAINST('database optimization');

 

위 예제는 "tile", "body", "tags" 컬럼에서 동시에 "database optimization"키워드를 검색한다.

이를 통해 데이터베이스 내 여러 필드에서 검색 결과를 통합할 수 있다.

 

## 이력 데이터 검색

Full-text Search는 로그 분석 및 이력 데이터 검색에서 뛰어난 성능을 발휘한다. 예를 들어, 특정 사용자의 활동 기록을 분석할 때 다음과 같은 쿼리를 사용할 수 있다.

SELECT * FROM user_logs
WHERE MATCH(action_description)
AGAINST('+login -error');

 

위 쿼리는 "login" 이 포함된 기록을 검색하며, "error"가 포함된 결과는 제외한다. 이를 통해 사용자의 성공적인 로그인 시도를 분석할 수 있다.

 

## 복합 쿼리와 성능 향상

Full-Text Search는 복합 쿼리와 조합하여 사용할 때 그 진가를 발휘한다고 한다. 예를 들어, 특정 날짜 범위와 키워드를 결합하여 검색하려면 다음과 같이 작성할 수 있다.

SELECT * FROM articles
WHERE MATCH(title, body)
AGAINST('performance tuning')
AND published_date BETWEEN '2024-01-01' AND '2024-12-31';

 

위 쿼리는 성능 튜닝과 관련된 기사 중 2024년에 게시된 기사만 검색한다. 복합 조건을 활용하면 더욱 세부적인 검색이 가능하다.

---

# Full-Text Search의 한계

- 지원 엔진: InnorDB와 MyISAM에서만 Full-Text Index를 지원한다. InnorDB는 MySQL 5.6 이후부터 Full-Text를 지원한다.

- 다국어 지원: 기본적으로 영어 기반으로 설계되어 있으며, 다른 언어에서는 추가 설정이 필요할 수 있다.

- 불용어와 최소 단어 길이: 기본적으로 불용어 목록과 최소 단어 길이 (3자 이상)가 적용된다. 이를 변경하려면 서버 설정을 수정해야 한다.

---

# Full-Text Search와 LIKE Search의 비교

위 내용과 같이 Full-Text Search에 대해 알아보았다. 정리된 내용을 기반으로 LIKE Search와의 비교를 정리해 보겠다.

	Full-Text Search	LIKE Search
검색 방식	특정 검색 알고리즘 사용	단순 패턴 매칭 사용
Index 사용	FULLTEXT Index 필요	일반 B-Tree Index 또는 비효율 Full Scan
검색 성능	큰 데이터셋에서 효율적	데이터셋이 클수록 비효율
기능	관련성 점수, 논리 연산자 사용 가능 등	간단한 패턴 일치만 가능

 

 

---

 

LIKE Search의 대안으로 사용할 수 있는 Full-Text Search에 대해 정리해보았다.

Full Scan을 피할수 없는 LIKE 대신 조건에 맞게 점수를 부여하여 검색을 하는 Full-Text Search 방식은 신기하였다. 간단한 구조, 혹은 데이터가 만지 않다면 LIKE를 사용해도 무관할 것 이다. 그러나 특정 구문으로 다량의 데이터에서 검색을 진행하려면 Full-Text는 필수로 선택해야될 것 이다. 또한 다양한 검색 방법을 지원하니 쓰임도 다양하게 할 수 있을 것 같다.

 

 

참고

MySQL :: MySQL 8.4 Reference Manual :: 14.9 Full-Text Search Functions