풀텍스트 인덱스 — MATCH AGAINST와 자연어 검색

일반 인덱스:  행 → 데이터
역색인:      단어 → 해당 단어가 포함된 행 목록

원본 데이터:
  행1: "MySQL은 오픈소스 데이터베이스입니다"
  행2: "PostgreSQL도 오픈소스입니다"
  행3: "MySQL 성능 최적화 가이드"

역색인:
  "MySQL"       → [행1, 행3]
  "오픈소스"     → [행1, 행2]
  "데이터베이스" → [행1]
  "성능"        → [행3]
  "최적화"      → [행3]
  ...

-- 테이블 생성 시
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    FULLTEXT INDEX ft_idx (title, content)
) ENGINE=InnoDB;

-- 기존 테이블에 추가
ALTER TABLE articles ADD FULLTEXT INDEX ft_title (title);

-- 또는
CREATE FULLTEXT INDEX ft_content ON articles(content);

-- 기본 사용
SELECT id, title,
       MATCH(title, content) AGAINST('MySQL 성능') AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 성능');

-- IN NATURAL LANGUAGE MODE는 기본값이므로 생략 가능
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 성능' IN NATURAL LANGUAGE MODE);

-- 최소 토큰 크기 확인 (InnoDB 기본: 3)
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

-- 불용어 목록 확인
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

-- + : 반드시 포함
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL +성능' IN BOOLEAN MODE);

-- - : 제외
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -PostgreSQL' IN BOOLEAN MODE);

-- * : 와일드카드 (접두사 매칭)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('optim*' IN BOOLEAN MODE);

-- " " : 구문 검색 (정확한 구문)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('"성능 최적화"' IN BOOLEAN MODE);

-- > < : 관련도 가중치 조절
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL +(>성능 <기초)' IN BOOLEAN MODE);
-- 성능이 포함된 문서가 기초보다 높은 관련도

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('데이터베이스' WITH QUERY EXPANSION);

n=2 (bigram)일 때 "MySQL 성능 최적화":
→ "My", "yS", "SQ", "QL", "성능", "능 ", " 최", "최적", "적화"

-- n-gram 파서로 풀텍스트 인덱스 생성
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (title, content) WITH PARSER ngram
) ENGINE=InnoDB;

-- n-gram 토큰 크기 설정 (기본 2, 서버 시작 시 설정)
-- my.cnf: ngram_token_size = 2
SHOW VARIABLES LIKE 'ngram_token_size';

-- 한글 검색
SELECT * FROM posts
WHERE MATCH(title, content) AGAINST('데이터베이스' IN BOOLEAN MODE);

-- n=2일 때 '데이터베이스'는 다음 토큰으로 분리됩니다:
-- "데이", "이터", "터베", "베이", "이스"
-- 이 토큰 중 하나라도 포함된 행이 검색됩니다

┌─────────────────────────────────┐
│         원본 테이블               │
│  (articles)                      │
├─────────────────────────────────┤
│    FTS Index Cache (메모리)      │
│    (최근 변경 사항 버퍼링)         │
├─────────────────────────────────┤
│    6개의 보조 테이블 (디스크)      │
│    (역색인 데이터)                │
├─────────────────────────────────┤
│    FTS_DOC_ID                    │
│    (문서 식별자)                  │
└─────────────────────────────────┘

-- 명시적으로 FTS_DOC_ID 컬럼을 추가하면 성능이 향상됩니다
CREATE TABLE articles (
    FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    title VARCHAR(200),
    content TEXT,
    PRIMARY KEY (FTS_DOC_ID),
    FULLTEXT INDEX ft_idx (title, content)
);

-- FTS 캐시 크기 (기본 8MB)
SHOW VARIABLES LIKE 'innodb_ft_cache_size';

-- 수동 동기화
SET GLOBAL innodb_optimize_fulltext_only = ON;
OPTIMIZE TABLE articles;
SET GLOBAL innodb_optimize_fulltext_only = OFF;

-- 1. 불용어 커스텀 (불필요한 단어 제거)
-- my.cnf에서 설정
-- innodb_ft_server_stopword_table = 'my_db/my_stopwords'

-- 2. 최소 토큰 크기 조절
-- innodb_ft_min_token_size = 2 (한글 2글자 검색 허용)

-- 3. 인덱스 최적화 주기적 실행
OPTIMIZE TABLE articles;

-- 4. 삭제된 문서 정리
-- InnoDB FTS는 DELETE 시 즉시 인덱스를 정리하지 않음
SHOW VARIABLES LIKE 'innodb_ft_num_word_optimize';