MySQL Full-text Search가 항상 LIKE보다 뛰어난 성능을 내는가?

mysql

MySQL Full-text Search가 항상 LIKE보다 뛰어난 성능을 내는가?

downfa11 2025. 6. 13. 16:37

부제 - 은탄환은 없다

알다시피, SQL문에서 LIKE 쿼리의 성능은 처참하다.

%keyword% 형태의 문자열의 경우는 인덱스를 활용할 수 도 없어서 Full Table Scan을 해야한다.

당연히 결과값 데이터가 많을수록 성능이 더 떨어진다.

아직 결과 수가 많지 않아서 LIKE 연산으로 괜찮지만 충분히 개선의 여지가 남아있다.

복잡한 텍스트 검색시 사용되는 ElasticSearch는 현재 서비스 성격상 맞지 않으며, 우리는 운영비 제로를 목표로 t2.micro를 쥐어짜내고 있는 상황이다.

MySQL에서 Full-text Search라는 기능을 제공한다.

Full-text Index를 사용해서 텍스트 기반 검색에 대해 더 빠르게 검색할 수 있다.

Full-text Search 개요

데이터베이스나 검색 시스템에서 사용자가 검색어와 관련된 텍스트 데이터를 빠르게 찾아주는 기능

주로 대규모 텍스트 데이터에 대해 효율적인 검색을 제공하기 위해 설계

단어 단위로 인덱싱하여 단복수, 동사 변화 등의 형태 변형도 인식할 수 있다.

검색에서 자주 사용되는 단어들(a, the, of, and 등)을 불용어(stopwords)로 간주하고 이를 무시한다.

스토리지 엔진은 기본적으로 InnoDB나 MyISAM 필요(MySQL 5.6 이상 지원)

단어 길이는 default 3글자 미만은 검색되지 않는다. (ft\_min\_word\_len 조정 가능)

한글 검색에 대해서는 n-gram parser 설정을 고려

Full-text Search의 장점

역색인 구조의 인덱스를 이용한 빠른 검색 성능
LIKE보다 높은 검색 품질
형태소 분석 및 자연어 처리

Full-text Search의 단점

추가적인 관리 비용
리소스 추가 소모 (인덱스 크기, DB의 저장 공간, ngram의 메모리 소모)
검색어의 정확도 문제 - 검색어 분석과 결과 순위 계산 필요

MATCH … AGAINST 구문을 통한 전체 텍스트 검색

match는 쉼표로 구분되며 검색할 열을 지정, against는 검색할 문자열과 수행할 검색 유형을 타나내는 search modifier를 사용한다.

SELECT *
FROM article
WHERE MATCH(content) AGAINST('covid -virus' in boolean mode);

Condition : FULLTEXT 인덱스는 CHAR, VARCHAR 혹은 TEXT 타입의 컬럼에서만 생성
Parser : 내장된 n-gram parser는 중국어와 일본어, 한글(CJK)를 지원
Load Large Data : 대규모 데이터의 경우는 인덱스 걸고 로드하는 것보다 FULLTEXT 없는 상태로 로딩하고 나서 인덱스 거는게 빠르다.

Full-text Search의 특징

자동 정렬 조건

FULLTEXT 인덱스를 사용
ORDER BY절 없어야함
조인시 인덱스가 가장 왼쪽에 있는 non-constnat 테이블이어야함

SELECT절에서 match(title,body) against ('database') 를 통해서 매치율을 조회할 수 있다.

대소문자 구분

자연어 검색은 기본적으로 대소문자를 구분하지 않는 방식으로 검색한다.

구분하고자 하면, 전체 텍스트 검색을 수행하는 binary collation을 사용할 수 있다.

user 열에 user\_bin을 할당해서 대소문자를 구분하도록 설정

무시되는 검색어

길이가 기준보다 짧거나, 불용어(StopWord)는 Full Text Search에서 무시된다.

ft\_min\_word\_len 확인 : show variables like '%ft\_min%';
stopword 확인 : select \* from INFORMATION\_SCHEMA.INNODB\_FT\_DEFAULT\_STOPWORD;

FULLTEXT 검색 방식

자연어 검색(Natural Language Searchs)

검색 문자열을 단어 단위(token\_size)로 분리하여 하나라도 포함되는 행을 탐색

명시하지 않아도 default로 지정되는 검색 모드

select *
from articles
where match(title,body) against ('database' in natural language mode);

불린 모드 검색(Boolean Searchs)

IN BOOLEAN MODE : 문자열을 단어 단위로 분리한 후, 추가적인 검색 규칙을 적용해서 단어가 포함되는 행을 탐색한다.

select *
from articles
where match(title,body) against ('+hello -hell' IN BOOLEAN MODE);

위의 검색은 ‘+hello -hell’ 라는 검색 규칙을 적용해서 탐색한다.

“hello”라는 문자는 추가하되, “hell”문자는 포함하지 않는 검색 규칙을 적용

InnoDB 엔진은 MATCH() 식의 모든 열에 FULLTEXT 인덱스가 필요하지만, MyISAM 검색 인덱스에 대한 Boolean 쿼리는 인덱스 없어도 작동한다. 대신 검색 속도가 느리다.

쿼리 확장 검색(Query Expansion Searchs)

WITH QUERY EXPANSION : 자연어 검색을 확장하여 2단계에 걸쳐 검색을 수행하는 방식이다. (OR IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)

자연어 검색 수행 후, 매칭된 결과에 기반해 검색 문자열을 재구성하여 2차 검색을 수행한다.

검색 구문이 아주 짧은 경우 유용하게 쓰이며, 암묵적인 지식으로 keyword와 다른 관련 내용이나 오타도 고쳐서 찾아준다.

Parser의 종류

어근 분석기 - Stop-word Parser

공백이나 Tab, 문장 기호, 또는 사용자가 정의한 문자열을 기준으로 토큰을 나누는 기법

ex) 아빠가 방에 들어갔다. → 아빠가 / 방에 / 들어갔다.

n-gram Parser

n-gram 기법을 사용하여 할당한 토큰의 크기 n만큼씩 데이터를 인덱스로 파싱해두었다가 사용하는 기법

ex) 아빠가 방에 들어갔다. → 아빠 / 빠가 / 방에 / 들어 / 어갔 / 갔다.

한글 검색에 있어서 n-gram 파싱을 이용해야 한글 띄어쓰기를 인식한다.

더 작은 단위로 text의 인덱스들을 많이 생성하기에, 더 많은 메모리를 사용한다는 점을 명심하자.

아주 많은 text에 대해 n-gram parser의 token 사이즈를 작게 잡으면 db에서도 인덱스 사용시 disk에 접근할 가능성이 높아져서 성능 저하로 이어질 수 있다.

LIKE문과 FULLTEXT 탐색 성능 비교 실험

FULLTEXT 인덱스 추가

ALTER TABLE boards
ADD FULLTEXT INDEX idx_fulltext_title_content (title, contents)
WITH PARSER ngram;

검색시 사용되는 게시글의 title, contents에 대해서 인덱스 추가

WITH PARSER ngram을 통해서 한글 쪼개기(연속된 글자 조각 분리)

MySQL 설정을 바꿔서 n-gram 설정과 최소 글자수 1글자로 바꿔야한다.

my.cnf 수정

[mysqld]
innodb_ft_min_token_size=1
ft_min_word_len=1

재시작 후 ALTER TABLE - sudo systemctl restart mysql

LIKE 기반 vs FULLTEXT 기반 성능 비교

먼저 title 컬럼에 FULLTEXT 인덱스가 존재해야한다.

거기에 더미로 Board 데이터를 1만개 주입했다. 각 연관관계에 속하는 댓글 엔티티도 5개씩 넣었다.

SET SESSION cte_max_recursion_depth = 10000;

INSERT INTO boards (title, type, contents, user_id, created_at, updated_at)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT 
  CONCAT('Title ', n), 1, CONCAT('Contents ', n), 1, NOW(), NOW()
FROM seq;

이렇게 title1,2,… contents 1,2,.. 로 더미데이터를 만들어버리면 검색에 유의미한 결과를 낼 수 없었다.

“keyword” 라고 검색하기 위해서, 일부 title이나 contents 값에서만 keyword를 포함한 문자열이 나오도록 하겠다.

SET SESSION cte_max_recursion_depth = 10000;

INSERT INTO boards (title, type, contents, user_id, created_at, updated_at)
SELECT 
  CASE 
    WHEN MOD(n, 10) = 0 THEN CONCAT('Title with keyword ', n, ' ', SUBSTRING(MD5(RAND()), 1, 5))
    ELSE CONCAT('Title ', n, ' ', SUBSTRING(MD5(RAND()), 1, 5))
  END,
  0,
  CASE
    WHEN MOD(n, 20) = 0 THEN CONCAT('Contents with keyword ', n, ' ', SUBSTRING(MD5(RAND()), 1, 5))
    ELSE CONCAT('Contents ', n, ' ', SUBSTRING(MD5(RAND()), 1, 5))
  END, 1, NOW(), NOW()
FROM (
  WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10000
  )
  SELECT n FROM seq
) AS seq;

이제 title은 10개중에 1개꼴로 keyword를 포함하고, contents에서는 20개에서 1개꼴로 포함하게 된다.

측정을 위한 SQL문 작성

RESET QUERY CACHE;
FLUSH TABLES;
FLUSH PROFILES;


SET PROFILING = 1;

SELECT * FROM boards
WHERE type = 'FREE' 
AND title LIKE '%keyword%';

EXPLAIN SELECT * FROM boards
WHERE type = 'FREE' 
AND title LIKE '%keyword%';

SHOW PROFILES;
-------------------------------------------------------------
SET PROFILING = 1;

SELECT * FROM boards
WHERE type = 'FREE' 
AND MATCH(title, contents) AGAINST ('keyword');

EXPLAIN SELECT * FROM boards
WHERE type = 'FREE' 
AND MATCH(title, contents) AGAINST ('keyword');

SHOW PROFILES;

1차 측정

LIKE의 경우(Full Table Scan) - 1000 rows in set, 1 warning (0.01 sec)

Profiles 결과 duration=0.00633625, explain duration=0.01710075

FULLTEXT MATCH의 경우(FULLTEXT Index Search) - 1000 rows in set, 1 warning (0.00 sec)

Profiles 결과 duration=0.00697375, explain duration=0.00714475

즉슨, LIKE문의 수행 결과가 10% 정도 빠르다고 나왔다.

2차 측정

LIKE의 경우(Full Table Scan)

mysql> EXPLAIN SELECT * FROM boards
    -> WHERE type = 'FREE' 
    -> AND title LIKE '%keyword%';
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | boards | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10022 |     1.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 2 warnings (0.01 sec)

mysql> 
mysql> SHOW PROFILES;
+----------+------------+------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                        |
+----------+------------+------------------------------------------------------------------------------+
|        1 | 0.00706925 | SELECT * FROM boards
WHERE type = 'FREE' 
AND title LIKE '%keyword%'         |
|        2 | 0.00032550 | EXPLAIN SELECT * FROM boards
WHERE type = 'FREE' 
AND title LIKE '%keyword%' |
+----------+------------+------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

FULLTEXT MATCH의 경우(FULLTEXT Index Search)

mysql> EXPLAIN SELECT * FROM boards
    -> WHERE type = 'FREE' 
    -> AND MATCH(title, contents) AGAINST ('keyword');
+----+-------------+--------+------------+----------+----------------------------+----------------------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table  | partitions | type     | possible_keys              | key                        | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+--------+------------+----------+----------------------------+----------------------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | boards | NULL       | fulltext | idx_fulltext_title_content | idx_fulltext_title_content | 0       | const |    1 |    10.00 | Using where; Ft_hints: sorted |
+----+-------------+--------+------------+----------+----------------------------+----------------------------+---------+-------+------+----------+-------------------------------+
1 row in set, 2 warnings (0.01 sec)

mysql> 
mysql> SHOW PROFILES;
+----------+------------+--------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                            |
+----------+------------+--------------------------------------------------------------------------------------------------+
|        1 | 0.01120325 | SELECT * FROM boards
WHERE type = 'FREE' 
AND MATCH(title, contents) AGAINST ('keyword')         |
|        2 | 0.00756500 | EXPLAIN SELECT * FROM boards
WHERE type = 'FREE' 
AND MATCH(title, contents) AGAINST ('keyword') |
+----------+------------+--------------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

실험 결과 - 일단 LIKE문을 도입하되, FULLTEXT로 확장 가능성

FULLTEXT가 약 58% 더 오래 걸렸다. 즉, LIKE문이 약 1.58배까지 더 빠르게 나왔더

항목	FULLTEXT MATCH	LIKE 검색
실행 쿼리	`MATCH(title, contents) AGAINST ('keyword')`	`title LIKE '%keyword%'`
EXPLAIN 결과	인덱스 `idx\_fulltext\_title\_content` 사용	ALL (Full Table Scan)
rows	1 (FULLTEXT 매칭)	10022 (테이블 전체 스캔)
프로파일링	0.01120325 sec	0.00706925 sec

이외에도 추가적인 실험을 통해서, 1개의 쿼리에 대해서는 FULLTEXT search가 좀 더 빠르지만 여러 검색 조건이 중첩되어 여러 번의 조회를 할 경우, Full Table Scan하는 LIKE 검색이 더 빠르게 나왔다.

type, title, contents

검색시 여러 검색 조건이 생기지 않기에 메모리만 따라준다면 도입을 고려할 법도 하다.

하지만, 메모리 문제로 인해 현재 프로젝트 상황(t2.micro 하의 운영비 제로 목적)에서는 당장 실사용자도 적어서 LIKE에 의한 검색을 이용하기로 했다.

그리고 당장 서비스에서 사용할 쿼리문을 비교했을때 LIKE문이 더 빠르게 나오지 않았는가?

게시글이 주류인 서비스도 아니고, 1만개를 넘어갈 일은 보기 힘들거 같다고 생각해서 당장은 도입할 생각이 없다.

여담 - 현업에서의 FULLTEXT Index

첨언하자면, DBA 입장에서는 관리상 불편함을 이유로 FULLTEXT Index 생성을 꺼려한다고 들었다.

그 이유에 대해서 찾아봤는데, 이 FULLTEXT 인덱스 조회에서 Boolean 조건절을 복합 조건으로 만드는 경우 속도가 떨어지는게 맞다고 한다.

더불어 데이터 크기가 많아질 경우, 성능은 계속해서 떨어져서 애초에 “MySQL은 대용량, 엔터프라이즈 수준의 데이터베이스를 효율적으로 처리하도록 설계되지 않았다.”는 내용의 아래 글을 소개하고자 한다.

MySQL Full-Text Search Limitations and Alternative Solutions

Find out about the limits of MySQL Full-Text Search and discover other full-text search alternative solutions. Improve your data search efficiency.

data-sleek.com

출처 및 인용.

https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

https://gngsn.tistory.com/163

저작자표시 비영리 동일조건 (새창열림)

'mysql' 카테고리의 다른 글

MySQL 8.0의 GTID 복제(Replication) 및 mode 변경 실습 (0)	2025.06.18
Master-Slave 노드간 복제(Replication) 실습 (feat. MySQL 8.0) (1)	2025.06.18
Entity 계층 구조에 따른 상속 관계에 대하여 알아보자 (0)	2025.04.01
데이터베이스 인덱스는 왜 B-tree를 사용하는가? (0)	2025.03.12
토이프로젝트 - 트랜잭션 관리를 통한 베타락(Exclusive Lock) 구현 (0)	2024.11.22

현재글MySQL Full-text Search가 항상 LIKE보다 뛰어난 성능을 내는가?

성장을 꾸준히 기록하는 공간

무소의 뿔처럼 나아가자

개발일지, WarGame, transaction, kafka, spring, docker, axon framework, 인디게임, CNCF, reactor, kubernetes, Spring WebFlux, SIEM, mysql, apache kafka, argoProj, MSA, iocp, WebFlux, argocd,

Today :
Yesterday :

성장을 꾸준히 기록하는 공간