MySQL FULLTEXT 검색 완벽 가이드

글쓴이 /

MySQL

MySQL에서 제공하는 FULLTEXT 검색은 텍스트 데이터를 효율적으로 검색할 수 있는 강력한 기능입니다. 이 글에서는 FULLTEXT의 개념부터 실제 활용 방법까지 상세히 알아보겠습니다.

FULLTEXT 이해하기

FULLTEXT 인덱스는 텍스트 기반 검색을 위한 특별한 형태의 인덱스입니다. 일반적인 인덱스와 달리 텍스트의 의미론적 검색을 가능하게 해줍니다. MySQL 5.6 이상부터는 InnoDB 엔진에서도 사용할 수 있게 되었습니다.

환경 설정

우선 테스트를 위한 MySQL 환경을 Docker를 통해 구성해보겠습니다.

docker run -p 3306:3306 --name test-db -e MYSQL_ROOT_PASSWORD=dbpass -d mysql:5.7
docker exec -it test-db mysql -u root -p

데이터베이스 및 테이블 설정

검색 테스트를 위한 데이터베이스와 테이블을 만들고 샘플 데이터를 입력합니다.

-- 데이터베이스 생성
CREATE DATABASE test;

-- 데이터베이스 선택
USE test;

-- 테이블 생성 (FULLTEXT 인덱스 포함)
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT idx_ft_title_and_body (title,body)
) ENGINE=InnoDB;

-- 샘플 데이터 삽입
INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

기존 테이블에 FULLTEXT 인덱스 추가하기

이미 생성된 테이블에도 FULLTEXT 인덱스를 추가할 수 있습니다.

-- 방법 1
CREATE FULLTEXT INDEX idx_ft_title_and_body on articles(title, body);

-- 방법 2
ALTER TABLE articles ADD FULLTEXT INDEX idx_ft_title_and_body(title, body);

FULLTEXT 검색 방법

기본 검색 – NATURAL LANGUAGE MODE

NATURAL LANGUAGE MODE는 자연어 검색을 위한 기본 모드입니다.

SELECT * FROM articles 
WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);

결과:

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

검색 안되는 단어 – 최소 단어 길이 문제

‘data’와 같은 짧은 단어는 기본 설정으로는 검색되지 않습니다.

SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('data' IN NATURAL LANGUAGE MODE);

이 쿼리는 아무 결과도 반환하지 않습니다. 이는 MySQL의 기본 최소 단어 길이 설정 때문입니다.

인덱스 내부 확인하기

FULLTEXT 인덱스에 어떤 단어들이 저장되어 있는지 확인할 수 있습니다.

-- 내부 테이블 확인 설정
SET GLOBAL innodb_ft_aux_table = 'test/articles';
SET GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE articles;

-- 저장된 단어 확인
SELECT WORD, DOC_COUNT, DOC_ID, POSITION 
FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

NGRAM 파서로 인덱스 재구축

기본 파서 대신 NGRAM 파서를 사용하여 인덱스를 재구축할 수 있습니다. NGRAM 파서는 단어를 여러 개의 작은 부분(n-그램)으로 나누어 인덱싱합니다.

-- 기존 인덱스 삭제
DROP INDEX idx_ft_title_and_body ON articles;

-- NGRAM 파서로 인덱스 추가
ALTER TABLE articles ADD FULLTEXT INDEX idx_ft_title_and_body(title, body) WITH PARSER NGRAM;

Stopwords 관리

Stopwords는 ‘이’, ‘그’, ‘the’, ‘and’와 같이 검색에서 제외되는 일반적인 단어들입니다.

-- 기본 Stopwords 확인
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

Stopwords 비활성화 방법

사용자 정의 빈 Stopwords 테이블을 생성하여 Stopwords를 비활성화할 수 있습니다.

-- 빈 Stopwords 테이블 생성
CREATE TABLE stopwords(value VARCHAR(30)) ENGINE = INNODB;

-- 사용자 정의 Stopwords 테이블 지정
SET GLOBAL innodb_ft_server_stopword_table = 'test/stopwords';

-- 인덱스 재구축
DROP INDEX idx_ft_title_and_body ON articles;
ALTER TABLE articles ADD FULLTEXT INDEX idx_ft_title_and_body(title, body) WITH PARSER NGRAM;

설정이 재시작 후에도 유지되게 하려면 mysql.cnf 파일에 다음을 추가해야 합니다:

[mysqld]
innodb_ft_server_stopword_table='test/stopwords'

검색 모드 비교

NATURAL LANGUAGE MODE vs BOOLEAN MODE

NATURAL LANGUAGE MODE는 관련성을 기준으로 검색 결과를 반환하지만, BOOLEAN MODE는 좀 더 정확한 검색이 필요할 때 사용합니다.

-- NATURAL LANGUAGE MODE (관련성 기반)
SELECT * FROM articles 
WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);

-- BOOLEAN MODE (정확한 검색)
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('database' IN BOOLEAN MODE);

BOOLEAN MODE에서는 다음과 같은 연산자를 사용할 수 있습니다:

  • +: 반드시 포함
  • -: 제외
  • >: 관련성 증가
  • <: 관련성 감소
  • *: 와일드카드
  • "...": 정확한 구문 검색

예시:

-- 'MySQL'을 포함하고 'root'를 제외하는 검색
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('+MySQL -root' IN BOOLEAN MODE);

성능 최적화 팁

  1. 적절한 칼럼 선택: 필요한 칼럼만 FULLTEXT 인덱스에 포함시키세요.
  2. 인덱스 크기 고려: 너무 많은 칼럼을 포함시키면 인덱스 크기가 커져 성능이 저하될 수 있습니다.
  3. 최소 단어 길이 설정: innodb_ft_min_token_size 파라미터를 조정하여 짧은 단어도 검색되게 할 수 있습니다.
  4. 정기적인 인덱스 최적화: OPTIMIZE TABLE 명령으로 인덱스를 정기적으로 최적화하세요.

결론

MySQL의 FULLTEXT 검색은 텍스트 데이터를 효율적으로 검색할 수 있는 강력한 기능입니다. 적절한 인덱스 설정과 검색 모드 선택을 통해 애플리케이션에 강력한 검색 기능을 추가할 수 있습니다.

다양한 옵션과 파라미터를 통해 FULLTEXT 검색을 커스터마이징할 수 있으므로, 애플리케이션의 요구사항에 맞게 적절히 설정하시기 바랍니다.

댓글 달기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다

위로 스크롤