MySQL FULLTEXT 검색으로 LIKE '%키워드%' 대체하기: 게시판 검색 속도 개선

2020년 1월, MySQL 5.5~5.6 + MyISAM 환경에서 작성한 글이다. 2026년 기준으로 달라진 점은 다음과 같다.

• InnoDB도 MySQL 5.6부터 FULLTEXT 인덱스를 지원한다. 지금은 InnoDB가 기본이다.
• 한국어처럼 띄어쓰기만으로 단어를 나누기 어려운 언어는 MySQL 5.7.6부터 내장된 ngram 파서를 쓰는 것이 표준이다. 예: ALTER TABLE bbs ADD FULLTEXT INDEX ft_subject (subject) WITH PARSER ngram;
• ngram 파서를 쓰면 아래의 최소 글자 수 설정 대신 ngram_token_size(기본 2)가 적용된다.
• 현재 지원되는 LTS 버전 문서는 MySQL 8.4 Full-Text Search다.

목차

요약

• LIKE '%키워드%' 검색은 인덱스를 쓰지 못해서, 20만 행 게시판에서 검색 한 번에 몇 분이 걸리기도 했다.
• FULLTEXT 인덱스와 MATCH ... AGAINST ... IN BOOLEAN MODE 로 바꾸자 6초 걸리던 쿼리가 5ms, 600ms 걸리던 쿼리가 60ms로 줄었다.
• 다만 단어의 앞부분으로만 찾을 수 있다. 단어 중간에 있는 글자(예: «서울특별시»에서 «특별»)는 찾지 못한다.
• MATCH() 에 쓰는 컬럼 목록은 FULLTEXT 인덱스의 컬럼 목록과 정확히 같아야 한다. 다르면 오히려 LIKE보다 느려진다.

들어가며

MySQL의 텍스트 검색에 대한 글을 여러 편 읽어 보면, 형태소 분석 없이는 정확한 검색 결과를 내기 어렵다는 결론이 많다. 이 글은 검색 엔진 수준의 정확도가 아니라, 게시판의 제목과 내용을 LIKE보다 빠르게 찾는 것이 목적이다. 그래서 첫 단어나 띄어쓰기 뒤에 오는 단어로 검색하는 경우만 다룬다.

검색어를 처리하는 방식, 쿼리 작성 규칙, 인덱스 설정에 따라 결과와 속도가 달라진다. 적용할 서비스의 목적에 맞춰 FULLTEXT 검색의 장단점을 따져 보고 결정하는 것이 좋다.

문제 상황

• 커뮤니티 게시판 테이블의 검색이 느렸다.
• 테이블 크기는 약 400MB, 20만 행이었다.
• 키워드로 검색하면 5분이 걸릴 때도 있었다.
• 기존 검색 기능은 LIKE '%키워드%' 를 쓰고 있었다. MySQL 튜닝이나 LIKE를 대체할 방법이 필요했다.

해결 방향

외부 검색 엔진(Sphinx 등)을 설치하는 방법도 검토했지만, 지금 서버에서 쉽고 빠르고 가볍게 적용하는 것이 우선이었다. 그래서 MySQL에 내장된 FULLTEXT 검색으로 LIKE를 대체하기로 했다.

Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns. — MySQL 문서

FULLTEXT 검색을 도입한 과정

1. 처음에는 FULLTEXT 검색이 어려워 보여서 Sphinx 설치까지 고려했지만, 오히려 더 복잡했다.
2. 다시 FULLTEXT 검색을 조사했다.
   • MySQL 문서: Full-Text Search Functions
   • w3resource: MySQL Full text search
3. 당시 환경이 도입 조건을 만족하는지 확인했다.
   • MySQL 5.5 이상을 쓰고 있었다.
   • 게시판 테이블이 MyISAM이었다. (MySQL 5.5에서는 MyISAM만 FULLTEXT를 지원했다.)
   • 제목(subject), 태그(tag), 내용(content) 컬럼이 MEDIUMTEXT, VARCHAR 였다.
4. 인덱스를 만드는 데 시간이 걸릴 뿐, 도입은 가능했다.
5. 로컬 서버에서 테스트해 보니 성능이 크게 개선됐다.
   • 6초 걸리던 쿼리가 5ms로 줄었다.
   • 600ms 걸리던 쿼리가 60ms로 줄었다.
   • 쿼리에 따라 10배에서 1,000배 이상 빨라졌다. 차이는 데이터 크기와 쿼리에 따라 달라진다.

MySQL 설정

최소 인덱싱 글자 수 설정

my.cnf 의 [mysqld] 항목에 아래 내용을 추가하거나 변경한다.

[mysqld]
# MyISAM 테이블용
ft_min_word_len = 2
# InnoDB 테이블용
innodb_ft_min_token_size = 2

두 변수는 적용 대상 엔진이 다르다. ft_min_word_len 은 MyISAM, innodb_ft_min_token_size 는 InnoDB에 적용된다. 쓰는 엔진에 맞는 값만 설정해도 된다.

이 값을 «검색어 최소 글자 수»로 설명하는 글도 있지만, 정확히는 인덱스에 넣을 단어의 최소 글자 수다. 이 값보다 짧은 단어는 인덱스에 들어가지 않기 때문에, 그런 단어로 검색하면 해당 행이 있어도 결과가 나오지 않는다. 한국어는 두 글자 단어가 많아서 2로 설정했다.

macOS의 MAMP에서는 다음 순서로 설정한다(참고 링크).

1. 서버를 정지한다.
2. /Applications/MAMP/conf/ 에 my.cnf 를 만든다.
3. 위 내용을 넣고 저장한다.
4. 서버를 다시 시작한다.

설정 확인

설정이 적용됐는지 확인한 뒤에 인덱스를 만든다.

SHOW VARIABLES LIKE 'ft_min%';
SHOW VARIABLES LIKE 'innodb_ft_min%';

FULLTEXT 인덱스 생성

400MB, 20만 행 테이블에서 인덱스 하나를 만드는 데 MacBook Pro(Late 2013) 기준으로 평균 6분 정도 걸렸다.

ALTER TABLE `테이블명`
ADD FULLTEXT INDEX `인덱스명` (`컬럼명`);

-- 예 1: 제목 컬럼 하나
ALTER TABLE `bbs`
ADD FULLTEXT INDEX `subject` (`subject`);

-- 예 2: 제목과 태그를 묶은 인덱스
ALTER TABLE `bbs`
ADD FULLTEXT INDEX `subject_tag` (`subject`, `tag`);

예 2처럼 여러 컬럼을 묶을 때는 컬럼마다 백틱을 따로 감싸야 한다. (`subject, tag`) 처럼 한 번에 감싸면 «subject, tag» 라는 이름의 컬럼 하나로 해석되어 오류가 난다.

FULLTEXT 검색 쿼리

SELECT * FROM bbs
WHERE MATCH(subject) AGAINST('서울*' IN BOOLEAN MODE);

-- 두 단어가 모두 들어간 글
SELECT * FROM bbs
WHERE MATCH(subject, tag) AGAINST('+서울* +여행*' IN BOOLEAN MODE);

* 는 «이 글자로 시작하는 단어»를 뜻하고, + 는 «반드시 포함»을 뜻한다. 이번 작업은 첫 단어나 띄어쓰기 뒤의 단어로 검색하는 경우를 기준으로 했다. 간단한 사용법은 w3resource, 자세한 MATCH ... AGAINST 옵션은 Advanced text searching using full-text indexes를 참고하면 된다.

처음 적용했을 때는 기대와 달리 검색이 10배 이상 느려졌다. 원인은 아래 트러블슈팅에 정리했다.

트러블슈팅

인덱스를 만들었는데 오히려 느려지는 경우

MATCH() 에 쓴 컬럼 목록이 FULLTEXT 인덱스의 컬럼 목록과 다르면 인덱스를 쓰지 못한다. InnoDB에서는 오류가 나지만, MyISAM에서 IN BOOLEAN MODE 를 쓰면 오류 없이 인덱스 없이 전체를 검색한다. 그래서 LIKE보다도 느려질 수 있다.

• subject 만 인덱스로 만들고, subject 와 tag 를 함께 검색한 경우

  ALTER TABLE `bbs` ADD FULLTEXT INDEX `subject` (`subject`);
  
  SELECT * FROM bbs
  WHERE MATCH(subject, tag) AGAINST('서울*' IN BOOLEAN MODE);  -- 느림

• subject 와 tag 를 묶어 인덱스로 만들고, subject 만 검색한 경우

  ALTER TABLE `bbs` ADD FULLTEXT INDEX `subject_tag` (`subject`, `tag`);
  
  SELECT * FROM bbs
  WHERE MATCH(subject) AGAINST('서울*' IN BOOLEAN MODE);  -- 느림

검색할 컬럼 조합마다 같은 조합의 FULLTEXT 인덱스를 만들어야 한다.

검색 결과가 나오지 않는 경우

인덱스를 만든 뒤에 최소 인덱싱 글자 수를 바꾸면 기존 인덱스에는 자동으로 반영되지 않는다. 인덱스를 다시 만들어야 한다.

-- MyISAM
REPAIR TABLE `bbs` QUICK;

-- InnoDB: REPAIR TABLE은 동작하지 않으므로 인덱스를 지우고 다시 만든다
ALTER TABLE `bbs` DROP INDEX `subject`;
ALTER TABLE `bbs` ADD FULLTEXT INDEX `subject` (`subject`);

참고자료

• MySQL 8.4: Full-Text Search Functions
• Advanced text searching using full-text indexes
• Why full-text-search returns less rows than LIKE
• Don’t Waste Your Time With MySQL Full-Text Search
• [DBMS] MySQL FULLTEXT을 이용한 형태소 분석없는 한글검색 방법
• [SW기술] Mysql Fulltext Index 의 활용