[MySQL] CREATE FULLTEXT INDEX 완벽 가이드 사용법(전문 검색[Full-Text Search] 인덱스 활용)

MySQL에서 텍스트 검색을 효율적으로 수행하려면 FULLTEXT INDEX(전문 검색 인덱스)를 활용해야 합니다.
이는 일반적인 인덱스와 다르게, 자연어 검색(Natural Language Search) 및 Boolean 검색(Boolean Mode Search) 같은 고급 검색 기능을 제공합니다.

FULLTEXT INDEX란?

FULLTEXT INDEX(전문 검색 인덱스) 는 VARCHAR, TEXT 등의 문자형 컬럼에서 고속 검색을 지원하는 인덱스입니다.
일반적인 B-Tree 인덱스와 달리, 단어 단위로 인덱스를 생성하여 대량의 텍스트 데이터에서 빠르게 검색할 수 있도록 최적화됩니다.

FULLTEXT INDEX의 주요 특징

1. 텍스트 검색 최적화
   • SQL의 LIKE 연산자보다 훨씬 빠르게 대량의 텍스트 데이터를 검색 가능
2. 자연어 검색(Natural Language Mode)
   • MySQL이 자동으로 중요도를 계산하여 검색 결과를 반환
3. Boolean 검색(Boolean Mode)
   • +, -, * 등의 연산자를 사용하여 복잡한 검색 조건을 지원
4. IN BOOLEAN MODE, IN NATURAL LANGUAGE MODE 사용 가능
   • 일반적인 키워드 검색뿐만 아니라 AND, OR 등의 논리 연산 적용 가능
5. MyISAM 및 InnoDB 엔진 지원(MySQL 5.6 이상)
   • MySQL 5.6 이상부터 InnoDB도 FULLTEXT INDEX 지원

반응형

CREATE FULLTEXT INDEX 기본 문법

CREATE FULLTEXT INDEX index_name
ON table_name (column1, column2, ...);

• index_name: 생성할 인덱스의 이름
• table_name: 인덱스를 적용할 테이블
• column1, column2, ...: 인덱스를 적용할 컬럼(최대 16개 가능)

CREATE FULLTEXT INDEX 예제

1. 기본적인 FULLTEXT INDEX 생성

아래는 게시판 테이블에서 제목과 본문을 빠르게 검색하기 위한 인덱스 생성 예제입니다.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    FULLTEXT (title, body)
);

위 테이블을 만들면 title과 body 컬럼에 대해 FULLTEXT INDEX가 자동으로 생성됩니다.

2. 기존 테이블에 FULLTEXT INDEX 추가하기

이미 존재하는 테이블에 FULLTEXT INDEX를 추가하려면 CREATE FULLTEXT INDEX를 사용하면 됩니다.

CREATE FULLTEXT INDEX idx_articles
ON articles (title, body);

이제 title과 body 컬럼에서 고속 검색이 가능해집니다.

반응형

FULLTEXT INDEX를 활용한 검색 방법

1. 기본적인 FULLTEXT 검색 (NATURAL LANGUAGE MODE)

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('MySQL FULLTEXT');

MATCH() AGAINST()를 사용하면, LIKE '%MySQL FULLTEXT%' 보다 훨씬 빠른 검색이 가능합니다.

2. Boolean 검색 (IN BOOLEAN MODE 사용)

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

Boolean 검색 연산자:

• +MySQL: MySQL이 반드시 포함된 결과
• -Oracle: Oracle이 포함되지 않은 결과
• "database management": 정확한 문구 검색
• MySQL*: MySQL로 시작하는 모든 단어 검색

3. 검색 결과 중요도 출력

FULLTEXT 검색에서는 각 결과에 검색 중요도(Score)가 부여됩니다. 이를 활용하면 가장 연관성이 높은 결과부터 정렬할 수 있습니다.

SELECT id, title, MATCH(title, body) AGAINST('MySQL Optimization') AS score
FROM articles
ORDER BY score DESC;

반응형

FULLTEXT INDEX 활용 사례

1. 블로그, 게시판, 뉴스 사이트의 검색 기능
   • LIKE '%검색어%' 대신 FULLTEXT INDEX를 사용하면 속도가 대폭 향상됨
2. 상품 리뷰, 고객 피드백 분석
   • 키워드 기반으로 긍정/부정 리뷰를 빠르게 검색 가능
3. 도서 검색, 문서 검색 시스템
   • 대량의 텍스트 데이터에서 특정 키워드가 포함된 문서를 빠르게 찾을 수 있음

FULLTEXT INDEX 사용 시 주의할 점

1. 짧은 단어는 검색되지 않음
   • 기본적으로 4자 이하 단어는 무시됨(변경하려면 ft_min_word_len 설정 필요)
2. STOPWORDS 목록 적용됨
   • MySQL 기본 불용어(Stopwords) 목록이 있어서, 자주 사용되는 단어는 자동으로 제외됨
   • ft_stopword_file을 수정하면 변경 가능
3. 단순 검색에만 적합, 정렬 성능 저하 가능
   • FULLTEXT INDEX는 정렬(ORDER BY) 성능이 좋지 않음
   • 검색과 정렬을 함께 처리하려면 MATCH() AGAINST() AS score를 활용하여 정렬
4. 인덱스 크기 증가
   • 텍스트 컬럼이 클 경우 FULLTEXT INDEX 크기도 커지므로 디스크 사용량이 증가
5. MySQL 버전별 지원 차이
   • MySQL 5.6 이상부터 InnoDB에서 FULLTEXT 지원
   • MySQL 5.5 이하에서는 MyISAM에서만 지원

반응형

FULLTEXT INDEX 삭제하기

DROP INDEX idx_articles ON articles;

이렇게 하면 FULLTEXT INDEX가 삭제됩니다.

결론

MySQL CREATE FULLTEXT INDEX는 텍스트 검색 성능을 획기적으로 향상시키는 강력한 기능입니다.
특히 LIKE 연산자로 검색할 때보다 훨씬 빠르고 정확한 결과를 제공하며,
자연어 검색 및 Boolean 검색을 지원하여 다양한 검색 옵션을 제공합니다.

✅ FULLTEXT INDEX를 사용하면?
✔ 대용량 데이터에서도 빠른 검색 가능
✔ LIKE보다 훨씬 효율적인 텍스트 검색
✔ Boolean 검색을 활용한 고급 검색 가능

MySQL에서 전문 검색 기능을 최적화하려면, FULLTEXT INDEX를 적극 활용하는 것이 필수입니다!

참고 자료

공식 가이드 문서: https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html