[SQL] Full-Text Search란 무엇인가?

 Full-Text Search

• 인덱스 유형의 데이터
• InnoDB Storage Engine 이나 MyISAM 스토리지 엔진에서만 사용 가능
• ngram(엔그램) passer 플러그인을 제공 (2단어로 쪼개서 인덱스화) token size 2 (기본)
• 엔그램 설정안하고 그냥 인덱싱하는 경우 token size가 보통은 3이 기본임
• 테이블 생성시 인덱싱하여 인덱스를 저장할 수 있고 나중에 추가할 수 있음
• CREATE TABLEALTER TABLECREATE INDEX

 

# ngram passer로 인덱싱하여 테이블에 저장
# [AUTHR_NM_IDX]이 만들어질 테이블 이름
# (AUTHR_NM) 인덱싱할 컬럼 
alter table books add FULLTEXT index
    TITLE_NM_test1(TITLE_NM) WITH PARSER ngram;

#B트리 인텍스 (숫자정보는 FULLTEXT로 인덱스화할 수 없음)
CREATE INDEX idx_ISBN_THIRTEEN_NO ON test.books(ISBN_THIRTEEN_NO);

• 숫자로 된 자료는 B트리 인덱스로 만들어야함. ISBN이라던가 ID라던가 시퀀스 넘버는 숫자로 이루어져 있으므로 B트리 형식으로 인덱스로 만들어 검색에 활용할 수 있음
• 대규모 데이터가 있는 기존의 테이블에 인덱스 데이터를 만드는 것보다 새로운 테이블에 인덱스를 만들어 넣고 찾아오는 것이 훨씬 빠르다. 
•  

SELECT * FROM test.books WHERE MATCH(TITLE_NM) AGAINST ('자바의 정석' IN NATURAL LANGUAGE MODE);

• 텍스트 검색은 : MATCH() AGAINST()구문을 사용하여 수행

 

 Full-Text Search에서 사용되는 기능 

1. Tokenization(토큰화): 텍스트를 개별 단어, 구, 문장 등 작은 단위로 나눕니다. 이 작은 단위를 토큰이라고 합니다.
2. Stopword Removal(불용어 제거): 검색에 도움이 되지 않는 일반적인 단어(예: "the", "and", "is" 등)를 제거합니다.
3. Stemming(어간 추출): 단어의 원형을 찾아 검색의 정확성을 높입니다. 예를 들어, "running", "ran", "runner"와 같은 단어는 모두 "run"이라는 원형으로 처리됩니다.
4. Ranking(랭킹): 검색 결과를 중요도 또는 관련성에 따라 정렬합니다. 이를 위해 TF-IDF(Term Frequency-Inverse Document Frequency), BM25, Cosine Similarity 등의 알고리즘을 사용할 수 있습니다.

 

검색종류 세가지

1. 자연어 검색 (Natural Language Search): 자연어 검색은 사용자가 입력한 검색어를 자연스러운 언어 형식으로 처리하여 관련 문서를 찾는 검색 방식입니다. 이 검색 방식은 문장 구조, 동사 형태, 단어의 의미 등을 고려하여 문서와 검색어 간의 관련성을 평가합니다. 자연어 검색은 전문 검색 기능을 사용하여 구현할 수 있으며, 이를 통해 키워드의 중요도, 문맥적 의미 등을 분석할 수 있습니다.

#자연어 검색
select *from books
where match(TITLE_NM) against ('자바' IN NATURAL LANGUAGE MODE );

• 장점:
  • 사용자 친화적: 사용자가 일상 언어로 검색어를 입력할 수 있으며, 별도의 검색어 형식을 배울 필요가 없습니다.
  • 검색 결과의 관련성: 검색 엔진이 입력된 검색어와 관련된 문서를 찾아서 결과로 제공합니다. 이로 인해 검색 결과의 관련성이 높아집니다.
• 단점:
  • 덜 정확한 결과: 자연어 검색은 복잡한 쿼리나 정확한 검색어 일치를 지원하지 않기 때문에, 때로는 덜 정확한 결과를 반환할 수 있습니다.
  • 성능 문제: 일반적으로 자연어 검색은 부울 검색이나 다른 검색 방법에 비해 성능이 떨어질 수 있습니다. 이는 검색어의 의미를 해석하고 관련성을 판단하는 과정이 복잡하기 때문입니다.

 

 

2. 부울 검색 (Boolean Search): 부울 검색은 논리 연산자(AND, OR, NOT)를 사용하여 검색어를 조합하고, 해당 조건에 부합하는 문서를 검색하는 방식입니다. 부울 검색은 검색어 간의 관계를 명확하게 정의할 수 있어, 특정 조건에 따라 문서를 필터링할 수 있습니다. 예를 들어, "apple AND iphone"과 같은 검색어를 사용하면, "apple"과 "iphone"이 모두 포함된 문서를 검색할 수 있습니다.

# 자바 and 프로그래밍
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('+자바 +프로그래밍' IN BOOLEAN MODE);

# 자바 or 프로그래밍
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('자바 | 파이썬' IN BOOLEAN MODE);

# 자바 not JavaScript
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('+자바 -JavaScript' IN BOOLEAN MODE);

• 장점:
  • 정확한 결과: 사용자가 AND, OR, NOT 등의 논리 연산자를 사용하여 검색어를 조합할 수 있어, 정확한 검색 결과를 얻을 수 있습니다.
  • 성능: 부울 검색은 상대적으로 빠른 검색 속도를 제공합니다. 이는 논리 연산자를 사용해 검색 대상을 명확하게 지정하기 때문입니다.
• 단점:
  • 사용자 친화성이 낮음: 부울 검색을 사용하려면 논리 연산자의 사용 방법을 알아야 하며, 일반 사용자에게는 다소 어려울 수 있습니다.
  • 검색 결과의 관련성이 낮을 수 있음: 사용자가 입력한 검색어에 대한 부울 연산만 고려하기 때문에, 결과의 관련성이 자연어 검색에 비해 낮을 수 있습니다.

 

3. 쿼리 확장 검색 (Query Expansion Search): 쿼리 확장 검색은 사용자가 입력한 검색어를 기반으로 관련 단어나 구문을 자동으로 추가하여 검색 범위를 확장하는 방식입니다. 이 검색 방식은 동의어, 유의어, 철자 오류 등을 고려하여 검색 결과의 정확성과 완전성을 높이려고 합니다. 쿼리 확장 검색은 자동으로 관련 단어를 추출하거나, 사전에 정의된 동의어 목록을 사용하여 구현할 수 있습니다.

#쿼리 확장 검색
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('자바' WITH QUERY EXPANSION);

이렇게 검색하는 경우 [HY000][188] FTS query exceeds result cache limit 라는 문제가 생길 수 있다. MySQL에서 Full-Text Search를 수행할 때 결과 캐시의 제한을 초과했다는 것을 알려줍니다. 즉, 검색 결과가 너무 많아서 결과 캐시에 저장할 수 없을 정도로 메모리가 부족한 상황. 이런경우 해결방법 아래 링크 참고 

https://lookupandfly.tistory.com/4

 

#and 연산자 사용
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('+자바 +프로그래밍' IN BOOLEAN MODE);

#or 연산자 사용
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('자바 | 파이썬' IN BOOLEAN MODE);

# not 연산자 사용
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('+자바 -JavaScript' IN BOOLEAN MODE);

#Limit 절로 반환하는 데이터 제한하기
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('자바' WITH QUERY EXPANSION)
LIMIT 10;

 

• 장점:
  • 검색 결과의 포괄성: 사용자가 입력한 검색어와 관련된 추가 검색어를 자동으로 포함하여 검색 범위를 확장합니다. 이를 통해 더 많은 관련된 문서를 검색 결과에 포함시킬 수 있습니다.
  • 검색 결과의 관련성 향상: 입력된 검색어와 관련된 추가 검색어를 자동으로 포함하여 검색 결과의 관련성을 높입니다.
• 단점:
  • 메모리 부하: 쿼리 확장 검색의 검색 범위가 확장되면서 검색 대상이 되는 데이터의 양이 증가하기 때문에, 메모리 부하가 높을 수 있습니다. 이는 성능 저하를 초래할 수 있으므로, 쿼리 확장 검색을 사용할때는 메모리 사용량에 대한 고려가 필요합니다.
  • 덜 정확한 결과 : 쿼리 확장 검색은 사용자가 원하는 결과와 관련이 없는 추가 검색어를 포함할 수도 있습니다. 이로 인해 때로는 덜 정확한 결과를 반환 할 수 있습니다. 

 

 

참고문헌

SQL 공식문서 참고

https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

챗GPT 자료 참고