LIKE

• MySql 에서는 테이터 검색 %'word'%구문이 사용
• LIKE 명령어 Table Full Scan 방식 => 많은 데이터 검색 시 비효율적
• 이를 극복하기 위해 FULLTEXT 방식 사용

FULLTEXT

정의 : % 연산자를 사용하여 LIKE 패턴 연산을 지원할 뿐 아니라, 단어 또는 구문에 대한 검색을 지원

• 조회 시 인덱스를 타도록 하여 Table Full Scan을 방지하여 성능을 개선
• VARCHAR, TEXT, CHAR 데이터 유형이 텍스만 가능
• 검색하고자하는 컬럼에 INDEX 생성
• 여러개의 열에 인덱스 지정 가능하다
• 검색어가 짧으면 결과 X => 최소 검색어 길이 값을 수정(my.cnf 설정 파일을 열어 ft_min_word_len 변수 값을 변경)
• MATCH() AGAINST() 함수 같이 사용

MATCH()	AGAINST()
콤마로 구별된 리스트를 받으며 검색할 컬럼의 이름	검색할 문자열 이며 선택적 수정자를 통해 수해할 검색의 형식을 지정

• 중지 단어 존재 => 중지 단어 추가도 가능

INDEX 생성 및 삭제

CREATE TABLE table_name( // 생성 1
  column_name datatype,
  FULLTEXT index_name (column_name)
);
  
CREATE FULLTEXT INDEX index_name ON table_name (column_name); // 생성 2
  
ALTER TABLE table_name ADD FULLTEXT (column_name); // 생성 3

ALTER TABLE table_name DROP INDEX FULLTEXT (column_name); // 삭제

INDEX ARGORITHM

1. Stop-word parser (형택소 분석)

   문장의 공백과 같은 띄워쓰기 단위로 단어를 분리하고, 각 단어의 조사를 제거해서 명사 또는 어근을 찾아서 인덱싱하는 알고리즘

| TEXT_TEBLE |

IDX	TEXT
1	사과
2	나는 사과를 좋아한다.
3	사과는 맛있어.

SELECT * FROM TEXT_TEBLE
WHERE MATCH (TEXT) AGAINST ('사과');

IDX	TEXT
1	사과

FULL TEXT INDEX 생성 시 공백을 구분자로 단어 단위로만 저장 -> 완전히 일치한 단어만 검색 결과에 포함

2. N-gram parser
   → 문장 자체에 대한 이해 없이 공백과 같은 띄워쓰기 단위로 단어를 분리하고, 그 단어를 단순하게 주어진 길이로 쪼개어 인덱싱하는 알고리즘
   → 문자열 분석시 공백 제거

N	난사과다
1	"난", "사", "과", "다"
2	"난사", "사과", "과다"
3	"난사과","사과다
4	"난사과다"

n-gram 에서 n 은 숫자를 의미하며, ngram-token-size 시스템 변수로 변경 가능

ngram-token-size 시스템 변수의 기본값은 2이며, 1부터 10까지 설정
n 이 1 이면 uni-gram, 2이면 bi-gram, 3 이면 tri-gram

FULLTEXT 3가지 검색 방식

1. 자연어 검색 (natural search) 옵션이 없을 때 기본
   → 검색 문자열을 단어 단위로 분리한 후, 해당 단어 중 하나라도 포함되는 행을 찾는다.
   → 검색의 정확도(score)에 따라 내림차순 정렬되어 결과가 표시
   → Ngram Parser로 나눠진 토큰의 합집합으로 계산하기 때문에 의도치 않은 결과값이 추가 될 수 도 있다.

select * from match table_name 
where match('column_name') against ('word' in natural language mode); 

2. 불린 모드 검색 (boolean mode search)

   → 검색 문자열을 단어 단위로 분리한 후, 해당 단어가 포함되는 행을 찾는 규칙을 추가적으로 적용하여 해당 규칙에 매칭되는 행을 찾는다.
   → 검색의 정확도(score)에 따라 결과가 정렬 X
   → 필수(+), 예외(-), 부정(~), 부분("*"), 구문(" ") 연산자 사용

select * from table_name
where match(column_name) against ('word' in boolean mode);

연산자	구문
+	필수 : SELECT * FROM newspaper WHERE MATCH(article) AGAINST('사과 + 바나나' IN BOOLEAN MODE);
의미	'사과'를 찾되 '바나나'가 들어가 있는 열
-	제외 : SELECT * FROM newspaper WHERE MATCH(article) AGAINST('사과 - 바나나' IN BOOLEAN MODE);
의미	'사과'를 찾되 '바나나'가 안 들어가는 열
~	부정 : SELECT * FROM newspaper WHERE MATCH(article) AGAINST('사과 ~ 바나나' IN BOOLEAN MODE);
의미	‘사과’를 찾되 ‘바나나’가 없는 열보다 ‘바나나’가 있는 열이 아래 순위
*	부분 : SELECT FROM newspaper WHERE MATCH(article) AGAINST('사과' IN BOOLEAN MODE);
의미	‘사과를’, ‘사과와’, ‘사과는’ 등
" "	구분 :SELECT * FROM newspaper WHERE MATCH(article) AGAINST("맛있는 사과" IN BOOLEAN MODE);
의미	“맛있는 사과”, “맛있는 사과가” 등 “맛있는 빨간 사과”, “맛있는 초록 사과” 불가

3. 쿼리 확장 검색 (query extension search)
   → 2단계에 걸쳐서 검색을 수행
   → 첫 단계에서는 자연어 검색을 수행한 후, 첫 번째 검색의 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두 번째 검색을 수행
   → 이는 1단계 검색에서 사용한 단어와 연관성이 있는 단어가 1단계 검색에 매칭된 결과에 나타난다는 가정을 전제

select * from table_name
where match(column_name) against('content*' WITH QUERY EXPANSION);