🧠 Fulltext Index
MySQL에서 대량의 텍스트 데이터를 빠르게 검색하려면 FULLTEXT INDEX가 효과적입니다. 이 글에서는 Fulltext 인덱스의 개념, 사용법, 검색 모드, Boolean 연산자 등을 예시와 함께 정리합니다.
🔍 Fulltext Index란?
FULLTEXT INDEX는 문자열 컬럼에 대한 자연어 기반 검색을 빠르게 수행할 수 있도록 지원하는 전용 인덱스입니다.
- 적용 가능한 컬럼:
CHAR,VARCHAR,TEXT - 지원 엔진: InnoDB, MyISAM
- MySQL 5.6+부터 InnoDB에서도 사용 가능
🛠️ 생성 방법
-- 테이블 생성 시
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(100),
body TEXT,
FULLTEXT(title, body)
);
-- 기존 테이블에 추가
ALTER TABLE articles ADD FULLTEXT(title, body);🔍 기본 사용법
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database');MATCH(...): 검색 대상 컬럼 지정AGAINST('검색어'): 찾을 문자열 지정
⚙️ 검색 모드
AGAINST('검색어' IN NATURAL LANGUAGE MODE);
AGAINST('검색어' IN BOOLEAN MODE);
AGAINST('검색어' WITH QUERY EXPANSION);| 모드 | 설명 |
|---|---|
| NATURAL LANGUAGE MODE | 기본값. 불용어(stopwords) 제외, 연산자 미지원 |
| BOOLEAN MODE | +, -, "...", * 등 연산자 사용 가능 |
| WITH QUERY EXPANSION | 첫 검색 결과를 바탕으로 자동으로 키워드를 확장하여 재검색 |
✅ BOOLEAN MODE 연산자 정리
| 연산자 | 의미 | 예시 | 설명 | ||
|---|---|---|---|---|---|
+ | 필수 포함 (AND) | +mysql +index | 두 단어 모두 포함 | ||
- | 제외 (NOT) | mysql -oracle | oracle이 없는 문서만 | ||
* | 접두사 검색 | dev* | developer, devops 등 | ||
" | 정확히 일치하는 구문 | "database index" | 해당 구문 그대로 포함 | ||
~ | 관련성 낮춤 | ~oracle | 포함해도 되지만 점수 낮게 | ||
> / < | 중요도 조절 | >mysql <oracle | mysql 더 중요하게 평가 | ||
| ` | ` | OR 조건 | `mysql | oracle` | 둘 중 하나 포함 |
() | 그룹 조건 | +(mysql oracle) | mysql 또는 oracle 필수 포함 |
💡 예제
SELECT * FROM articles
WHERE MATCH(title, body)
AGAINST('+database -"oracle cloud"' IN BOOLEAN MODE);database는 반드시 포함"oracle cloud"는 제외- 정교한 검색이 가능해짐
🚧 주의사항
- 단어 길이가 3자 이하이거나 **불용어(stopwords)**는 검색되지 않을 수 있음
*는 접두사만 지원 (뒤에만 붙일 수 있음)- 관련성 점수는
MATCH ... AGAINST에만 적용 가능
📚 마무리
FULLTEXT INDEX는 대량의 텍스트 데이터에서 빠르고 정교한 검색이 필요한 서비스(예: 게시판, 문서 검색)에 매우 유용합니다. 특히 BOOLEAN MODE의 조합을 이해하고 잘 활용하면, SQL에서 강력한 검색 기능을 구현할 수 있습니다.
궁금한 점이나 실습 예제가 필요하다면 댓글로 알려주세요 😊
Happy Querying!
필요하시면 이 글에 추가로 실습 예제나 이미지도 만들어드릴 수 있어요. JSON 검색과의 연계도 가능하니 원하시면 알려주세요!
⚙️ InnoDB Fulltext Index 튜닝 및 내부 정보 조회
MySQL의 InnoDB 스토리지 엔진에서 Fulltext 인덱스를 보다 정밀하게 제어하거나 디버깅하려면 아래와 같은 시스템 변수 및 정보 스키마 테이블을 활용할 수 있습니다.
🔧 innodb_ft_aux_table 설정
SET GLOBAL innodb_ft_aux_table = 'testdb/Notice';- InnoDB의 Fulltext 인덱스를 분석하거나 조사할 때 사용하는 보조 테이블 설정입니다.
'DB명/테이블명'형식으로 지정해야 합니다.- 단일 테이블만 지정 가능 (복수 설정은 불가능)
이 설정을 통해 다음과 같은 테이블들을 조회할 수 있게 됩니다:
SELECT * FROM information_schema.innodb_ft_index_table;
SELECT * FROM information_schema.innodb_ft_index_cache;📦 Fulltext Index 테이블 정보 조회
SHOW VARIABLES LIKE 'innodb_ft_aux%';SELECT * FROM information_schema.innodb_ft_index_table;이 명령어들을 통해 현재 Fulltext 인덱스에 등록된 단어, 문서 ID, 내부 구조 등을 확인할 수 있습니다.
🧹 Fulltext Index 최적화 (Rebuild)
Fulltext 인덱스를 정리하거나 재구성하려면 다음과 같은 절차를 따릅니다:
-- 1. 최적화 모드 활성화
SET GLOBAL innodb_optimize_fulltext_only = ON;
-- 2. 인덱스 최적화 실행
OPTIMIZE TABLE testdb.Notice;
-- 3. 모드 비활성화
SET GLOBAL innodb_optimize_fulltext_only = OFF;OPTIMIZE TABLE실행 시innodb_optimize_fulltext_only가ON이면 Fulltext 인덱스만 최적화합니다.- 삭제된 단어, 중복된 토큰 등을 제거하여 인덱스 크기 감소 및 성능 향상에 도움됩니다.
📝 요약
| 기능 | 설명 |
|---|---|
innodb_ft_aux_table | Fulltext 인덱스 내부 구조를 조회할 테이블 지정 |
innodb_ft_index_table, innodb_ft_index_cache | 현재 인덱스에 저장된 토큰과 구조를 확인 |
innodb_optimize_fulltext_only | Fulltext 인덱스만 대상으로 OPTIMIZE 수행 가능 |
이러한 고급 기능들은 검색 품질 개선, 성능 디버깅, 인덱스 재정비에 매우 유용합니다. 대규모 게시판이나 문서 검색 시스템에서는 정기적인 점검 루틴에 포함시키는 것도 좋은 전략입니다.
필요하다면 위 기능들을 포함한 Fulltext Index 점검 스크립트도 만들어드릴 수 있습니다. 원하시나요?
