[MySQL/MariaDB] MATCH AGAINST

📌 Full‑Text Index (풀텍스트 인덱스) 란?

일반적인 텍스트 검색(LIKE '%텍스트%')은 테이블 풀스캔(full scan) 을 유발해 데이터가 많아질수록 속도가 크게 떨어집니다. Full‑Text Index는 이런 문제를 해결하기 위해 텍스트 전체를 대상으로 인덱스를 생성하고, 이를 이용해 빠르게 검색할 수 있도록 해 줍니다.

• char / varchar / text 타입 컬럼에 적용 가능
• InnoDB / MyISAM 엔진에서 지원됨
• 여러 컬럼에 중복 적용 가능
• db버전을 반드시 확인해주도록 합니다.
• MySQL : MyISAM엔진에서는 기본지원, InnoDB에서는 5.6버전 이상부터 지원
• MariaDB : 버전10 InnoDB에서 기본지원

---

💡 Full‑Text Index 생성
인덱스는 테이블 생성 시 또는 기존 테이블에 추가할 수 있습니다.
인덱스를 수정해야 한다면 삭제 후 생성해줍니다.
인덱스 생성하게되면 analyze를 꼭 실행하도록 합니다.

-- 테이블 생성 시
CREATE TABLE MyTbl (
    id INT PRIMARY KEY,
    content TEXT,
    FULLTEXT (content)
);

-- 기존 테이블에 추가
ALTER TABLE MyTbl ADD FULLTEXT (content);

-- 별도 인덱스 생성
CREATE FULLTEXT INDEX idx_content ON MyTbl (content);

-- 인덱스 확인
SHOW INDEX FROM MyTbl;

---

🔍 Full‑Text 검색 방법

Full‑Text 검색은 일반적인 WHERE LIKE 대신 MATCH() … AGAINST() 문법을 사용해야 인덱스가 활용됩니다.

🧠 1) 자연어 검색 (Natural Language Mode)

• 특별히 옵션을 지정하지 않거나 뒤에 in natural language mode를 붙이면 자연어 검색을 합니다.
• 자연어 검색은 단어가 정확한 것을 검색해줍니다.

SELECT * FROM MyTbl WHERE MATCH(content) AGAINST('검색어');
SELECT * FROM MyTbl WHERE MATCH(content) AGAINST('검색어' in natural language mode);
SELECT * FROM MyTbl WHERE MATCH(content) AGAINST('검색1 검색2');
-- ‘검색1’ 또는 ‘검색2’ 두 단어 중 하나가 정확히 포함된 content 검색

⚙️ 2) 불린 모드 검색 (Boolean Mode)

• LIKE연산자에서 %를 쓰듯이 불린 모드 검색기능을 사용할 수 있습니다.
• 단어나 문장이 정확히 일치하지 않는 것도 검색할 수 있습니다.
• 뒤에 in boolean mode 옵션을 붙여주면 적용됩니다.
• 검색 단어 간 조합, 필수 포함, 제외, 와일드카드 등을 설정할 수 있습니다.
• 연산자의미

  +	반드시 포함
  -	포함하지 않음
  *	와일드카드 (뒤에만 사용)
  "	정확 구문 검색
  ~	부정적인 기여 (순위 낮춤)

SELECT * FROM MyTbl
WHERE MATCH(content) AGAINST('검색일 +검색이' IN BOOLEAN MODE);
-- '검색일'을 찾되 반드시 '검색이'가 들어가 있는 데이터

SELECT * FROM MyTbl
WHERE MATCH(content) AGAINST('검색일 -검색이' IN BOOLEAN MODE);
-- '검색일'을 찾되 '검색이'는 안들어가 있는 데이터

SELECT * FROM MyTbl
WHERE MATCH(content) AGAINST('검색일 ~검색이' IN BOOLEAN MODE);
-- ‘검색일’을 찾되 ‘검색이’가 없는 열보다 ‘검색이’가 있는 열이 아래 순위

SELECT * FROM MyTbl
WHERE MATCH(content) AGAINST('검색*' IN BOOLEAN MODE);
-- ‘검색을’, ‘검색이’, ‘검색은’ 등 모두 검색

SELECT * FROM MyTbl
WHERE MATCH(content) AGAINST("유익한 검색" IN BOOLEAN MODE);
-- “유익한 검색”, “유익한 검색이” 등
-- “유익한 한국어 검색”, “유익한 영어 검색” 불가

✨ 3) 쿼리 확장 검색 (WITH QUERY EXPANSION)

• 자연어 모드 기반, 검색 결과에서 연관 단어를 추출하여 재검색 합니다.
• 첫 단계에서는 자연어 검색을 수행, 첫 번째 검색의 결과에 매칭된 행을 기반으로 검색 문자열 재구성 하여 두번째 검색 수행
• 1단계 검색에서 사용한 단어와 연관성이 있는 단어가 1단계 검색에 매칭된 결과에 나타난다는 가정을 전제로 한다.

---

🔹 최소 토큰 길이 (Minimum Token Size)

• FULLTEXT 인덱스는 기본적으로 일정 길이 이상의 단어만 인덱싱합니다.
• MySQL / MariaDB 기본값
  
  • MyISAM: ft_min_word_len = 4 (기본 4글자 이상)
  • InnoDB: innodb_ft_min_token_size = 3 (기본 3글자 이상)
  • 즉, 2글자의 단어는 인덱싱을 하지 않습니다.
• 조정 방법:
  • 만약 조정이 필요하다면 설정 변경 후 FULLTEXT 인덱스 재생성이 필요합니다.

-- 몇 글자 이상부터 검색 가능한지 확인
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

조정 필요할경우 my.ini 수정
innodb_ft_min_token_size=2

---

여기부터는 심화내용 입니다 :D

🧩 4) 파서(Parser)와 Stopword 처리

• 파서(Parser): 텍스트를 단어 단위로 쪼개고, 인덱스를 생성하는 핵심 기능
  • 토큰화, Stopword 적용, 형태소 분석, 인덱스 생성
• Stopword 란?
  • 검색 시 무시되는 단어를 의미합니다.
  • 인덱스에 포함되지 않고 검색 결과에 영향을 거의 주지 않는 단어를 말합니다.
  • 예 ) the, a, of, is / 은, 는, 이, 가, 의
• Stopword 적용하려면 최소한 단어 단위로 텍스트를 분리할 수 있는 파서( N-gram , MeCab 등)이 필요합니다.
• 주의 : MariaDB는 N-gram 파서 미지원
• MariaDB의 경우 LIKE검색과 같이 써야한다
• 예 ) MATCH(content) AGAINST(CONCAT('+검색*') IN BOOLEAN MODE)
              OR content LIKE CONCAT('%검색%')

[ 전체 텍스트 인덱스 단어 확인 ]
--db와 테이블 이름은 소문자 써야된다.
set global innodb_ft_aux_table = '디비명/테이블명';

--테이블에서 만들어진 전체텍스트 인덱스 보기 (필요없는 검색 단어가 있음)
select word, doc_count, doc_id, position from information_schema.innodb_ft_index_table ;

-- 중지단어 만들기전에 만들어놓은 풀텍스트 인덱스는 삭제
drop index idx_all on FulltextTbl ;

-- 중지단어를 위한 테이블 만들기
-- 테이블의 데이터는 반드시 value , 타입은 varchar
CREATE TABLE user_stopword (value VARCHAR(30));

--중지 단어 만들기
insert into user_stopword values ('그는'), ('그리고'), ('후에') ;

-- 중지 단어 테이블에 지금 만들 테이블을 추가하는 작업
-- 그러면 이제 user_stopword 테이블에 들어있는 단어로는 풀텍스트 인덱스를 만들지 않게 된다
set global innodb_ft_server_stopword_table = '디비명/user_stopword';

-- 만든 테이블이 들어갔는지 확인
show global variables like 'innodb_ft_server_stopword_table' ;

-- 중지단어 테이블 만들었으니, 이제 다시 풀텍스트 인덱스 생성.
create Fulltext index idx_description on FulltextTbl ( description );

-- 그러면 중지단어에 등록한 단어를 제외한 인덱스 단어들이 생성됨 (갯수가 줄어들음)
select word, doc_count, doc_id, position from information_schema.innodb_ft_index_table ;

---

참고 : https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%ED%92%80%ED%85%8D%EC%8A%A4%ED%8A%B8-%EC%9D%B8%EB%8D%B1%EC%8A%A4Full-Text-Index-%EC%82%AC%EC%9A%A9%EB%B2%95

[MYSQL] 📚 풀텍스트 인덱스(Full-Text Index) 사용법

https://dogcowking.tistory.com/78

MySQL Full-Text 검색 / match against