코코딩딩

임계값이란 무엇인가?

• 자연어 검색 임계값(Natural Language Search Threshold) : 전문 검색 시스템에서 사용되는 설정값이다. 이 임계값은 검색 결과의 관련성(relevance)에 영향을 준다. 자연어 검색 시스템은 이 임계값을 사용하여 쿼리와 일치하는 문서의 최소 관련성 수준을 결정한다고 한다.
• 임계값보다 낮은 관련성 점수를 가진 문서는 검색 결과에서 제외된다. 이렇게 함으로써 검색 결과의 품질을 향상시키고, 사용자에게 더 관련성이 높은 결과를 제공할 수 있다.  임계값을 설정하거나 조정함으로써 검색 결과의 정확성과 관련성을 최적화할 수 있다.
• 기본 임계값은 최소4, 최대 84

임계값에 대한 고찰

• 임계값(Threshold)이란 쉽게 말해서 어떤 기준을 말한다.
• 특정 조건이 충족되거나 불충족되면 다른 처리가 이루어진다.
  • 예를들어 어떤 프로세스에서 데이터를 처리하는데 걸리는 시간이 일정 시간을 넘어갈 경우 경고 메세지를 출력하는 것과 같은 상황에 사용된다. 
• MY SQL에서 임계값은 검색 결과의 관련성에 영향을 미친다. 임계값이 너무 높으면 검색 결과가 적게 나올 수 있고, 너누 낮으면 덜 관련성있는 결과가 많이 나올 수 있다. 임계값을 조절함으로써 검색 결과의 정확성과 관련성을 개선할 수 있다.
• 데이터 검색 민감도라고 생각하면 편할것 같다. 
• 토큰길이가 곧 임계값이라고 생각할 수 있다. 이 값에 따라 검색 인덱스와 검색 결과의 질이 달라진다. 
  • 토큰 길이가 길면 검색 정확도가 높아지지만 색인 크기와 검색 속도에 부정적인 영향을 미칠 수 있다.
  • 반면 토큰 길이가 짧으면 색인 크기와 검색 속도는 개선되지만 검색 정확도가 떨어질 수 있다.
  • 따라서 검색 결과의 질을 유지하면서 검색 속도를 높일 수 있는 임계값 설정이 중요하다

확인하는 방법 : 콘솔에서 아래와 같이 적는다. 

#자연어 임계값 확인
SHOW VARIABLES LIKE 'ft_min_word_len';
SHOW VARIABLES LIKE 'ft_max_word_len';

임계값 변경는 방법 : my.cnf 파일에 아래처럼 추가한다. 

ft_min_word_len = 4

변경후 SQL재실행 -> 인덱싱 재작업

• parser에도 임계값이 있다. 

# 임계값 보기
SHOW VARIABLES LIKE 'innodb_ft_ngram_size';

# 임계값 변경
SET GLOBAL innodb_ft_ngram_size = 3;
SET SESSION innodb_ft_ngram_size = 3;

• 전역(global) 또는 세션(session) 범위의 변수 값을 변경한다.
• 값이 변경된 후에는 새로 인덱싱해야 설정된 값이 변경된다.
• 기존 인덱스에는 변경되지 않으므로 다시 인덱싱 해서 인덱스를 만들어 줘야한다. 

검색 결과의 질을 높이려면 임계값 설정을 잘 해야한다고 생각한다. 

특징

특징 1. InnoDB와 MyISAM 스토리지 엔진만 Full-Text 인덱스를 지원함

특징 2. char,varchar,text 컬럼에서만 사용할 수 있음

int,Long 컬럼에서는 B-tree방식을 사용해야 함. 

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

직접 테이블, 필드를 만들어 인덱스 추가하는 경우

#직접 테이블과 필드를 만들어에 FULLTEXT 인덱스를 추가
CREATE TABLE author (
                       id INT AUTO_INCREMENT PRIMARY KEY,
                       title VARCHAR(255) NOT NULL,
                       author VARCHAR(255) NOT NULL,
                       FULLTEXT (author)
) ENGINE=InnoDB;

기존에 존재하는 테이블 정보를 인덱스화 하는 경우 

alter table books add FULLTEXT index
    AUTHR_NM_IDX(AUTHR_NM) WITH PARSER ngram;

ngram parser를 원하지 않으면 with parser ngram을 빼고 실행시키면 된다.

이미 존재하는 books 테이블에 대한 인덱싱을 하게된다. 

특징3.  항상 전체 컬럼을 대상으로 하며, 접두사 인덱싱은 지원하지 않는다. 

 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 자료 참고

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

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

방법 1. and, or, not 연산자 사용하여 검색 범위 줄여주기

#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);

1. 검색 쿼리 수정: 검색 쿼리를 더 구체적으로 수정하여 검색 결과의 수를 줄여보세요. 예를 들어, 부울 검색을 사용하여 필요한 키워드만 포함하도록 조건을 추가하거나 제외할 수 있습니다.
2. 결과 캐시 제한 늘리기: MySQL 설정에서 ft_result_cache_limit 값을 늘려 검색 결과 캐시의 제한을 증가시킬 수 있습니다. 이렇게 하면 더 많은 검색 결과를 캐시에 저장할 수 있습니다. 하지만 이 방법은 서버의 메모리 사용량이 증가하므로, 시스템의 메모리가 충분한지 확인해야 합니다.

방법2. Limit 절 추가하여 반환되는 행의 수 제한하기

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

방법1이 더 안전한것 같다. 또한 쿼리 확장의 경우 인덱스를 사용했음에도 불구하고 검색 범위가 넓어짐과 동시에 검색에 대한 품질도 떨어지기 때문에 원하는 데이터에 대한 자료가 부족한데 찾아야하는 경우가 아니면 사용하지 않을것 같다. 

쿼리 확장 검색을 사용하는 이유

1. 검색 결과의 관련성 향상: 사용자가 입력한 검색어와 관련된 추가 검색어를 자동으로 포함하여 검색 결과의 관련성을 높입니다.
2. 검색 결과의 포괄성 향상: 검색 범위를 확장함으로써 더 많은 관련된 문서를 검색 결과에 포함시킵니다.

쿼리 확장 검색의 단점

쿼리 확장 검색의 단점 중 하나는 메모리 부하가 높을 수 있다는 점이다. 이는 검색 범위가 확장되면서 검색 대상이 되는 데이터의 양이 증가하기 때문이다. 따라서 쿼리 확장 검색을 사용할 때는 성능과 메모리 사용량에 대한 고려가 필요하다.