MySQL Full-Text Search 도입기

 

💣 배경 및 문제 상황

초기에는 단순히 사용자들이 "제목" 또는 "설명"에 포함된 키워드로 LIKE '%keyword%' 검색을 수행하도록 설계했지만 문제가 발생

🔸 방식

SELECT * FROM table_A WHERE title LIKE '%keyword%' OR description LIKE '%keyword%';

• 실제 사용한 쿼리는 아니지만 이런 식으로 사용

 

🔍 LIKE 문 동작 방식 분석

 

1️⃣ 예시 테이블 생성

CREATE TABLE table_A (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  author_id BIGINT,
  title VARCHAR(255),
  description VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
);

 

2️⃣ 더미 데이터 삽입

DELIMITER $$

CREATE PROCEDURE insert_dummy_table_A(IN count INT)
BEGIN
  DECLARE i INT DEFAULT 0;

  WHILE i < count DO
    INSERT INTO table_A (title, description)
    VALUES (
	    FLOOR(1 + RAND() * 1000),
      CONCAT('Sample Title ', i),
      CONCAT('Sample Description ', i)
    );
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;
---------------------------------------------------
CALL insert_dummy_table_A(10000); # 10,000 개 생성

 

3️⃣  %{keyword}% 를 이용한 검색

EXPLAIN 
SELECT * FROM table_A WHERE title LIKE '%10%' OR description LIKE '%11%';

 

👋 결과

 

항목	의미
type	ALL : 테이블 풀 스캔
key	NULL 이면 인덱스 사용 안함
rows	읽은 예상 row 수
Extra	SQL문을 어떻게 수행할 것인지 알려준다. - Using index condition, Using where, Using fulltext 등 확인

 

 

⚠️ 인덱스 설정을 해서 해결할 수 있나?

인덱스 크기 문제 (Index Size Overhead)

• VARCHAR 컬럼에 인덱스를 걸면 → 인덱스 자체가 굉장히 커짐
• 특히 InnoDB는 인덱스에 해당 데이터의 실제 값까지 포함 → 디스크 I/O가 증가하고, 성능에 악영향

 

선형 스캔으로 fallback하는 경우가 많음

• LIKE '%abc%' 처럼 앞에 % 붙는 경우 → 인덱스 사용이 안 되므로 결국 풀스캔됨
• 그런데도 인덱스를 만들어두면 ? → 읽기도 느리고, 쓰기도 느려지고, 디스크 공간도 낭비됨

 

쓰기 작업에 불리함

• 인덱스는 INSERT, UPDATE, DELETE마다 갱신이 필요
• title, description처럼 수정 빈도가 있거나 텍스트가 길면 → 인덱스 유지 비용이 커짐

 

⚠️ 보통 검색 기능을 구현시 특정 키워드가 데이터에 포함되어 있는지의 여부에 따라 필터링

• LIKE '%abc%' 형태의 쿼리문을 수행한다.
• 이렇게 Full Table Scan을 통해서 조건에 맞는 데이터를 찾는 수 밖에 없다.

 

이러한 문제를 해결하기 위해 MySQL Full-Text Index 기반 검색 도입을 검토하고 향후 ElasticSearch로 확장 가능한 구조를 목표로 삼았다.

 

✅ 선택한 해결책: MySQL Full-Text Index 적용

💡 전문(Full Text) 검색

📖 전문 검색

• 문서의 내용 전체를 인덱스화 해 특정 키워드가 포함된 문서를 검색
• InnoDB나 MyISAM 스토리지 엔진에서 제공하는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다.

 

📍 인덱스 알고리즘

• 문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘을 전문 검색 인덱스(Full Text search)라 한다.
• 사용자가 검색하게 될 키워드를 분석해 내고 빠른 검색용으로 사용할 수 있게 이러한 키워드로 인덱스를 구축
• 인덱싱 하는 기법에 따라 크게 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분

 

🤔 어근 분석

• 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업
• 오픈소스 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 사용할 수 있도록 지원

 

🧐 n-gram 알고리즘

• 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘
• 본문을 몇 글자씩 잘라서 인덱싱 (일반적으로는 2글자 단위)

 

✅ 이유

• RDB 내부 기능을 사용해 빠른 Full-Text 검색 가능
• 별도의 인프라(E.S) 없이도 적용 가능

 

🔧 적용 과정

 

전문 검색 인덱스를 사용하려면 두 가지 조건을 갖춰야 한다.

1. 쿼리 문장이 전문 검색을 위한 문법을 사용한다 (MATCH … AGAINST …)
   • 전문 검색 인덱스를 구성하는 칼럼들은 MATCH절의 괄호 안에 모두 명시되어야 한다.
2. 테이블이 전문 검색 대상 칼럼에 대해 전문 인덱스 보유

 

🖥️ 스키마 변경

ALTER TABLE table_A ADD FULLTEXT INDEX idx_fulltext_title_desc (title, description);

• MySQL 8.0 이상 InnoDB 엔진에서 FULLTEXT 인덱스 지원함

 

🧑🏻‍💻 Full Text Index를 이용한 SQL

EXPLAIN 
SELECT * FROM table_A WHERE MATCH(title, description) AGAINST('여행' IN BOOLEAN MODE);

 

👋  결과

 

항목	의미
type	fulltext: FULLTEXT 인덱스를 사용
key	실제 사용된 인덱스 이름(FULLTEXT 인덱스 이름)

 

 

💣 기본 FULLTEXT Parser를 사용했을 때 문제

아래 두 쿼리의 결과가 다르게 나온다.

SELECT * FROM table_A WHERE title LIKE '%여행%' OR description LIKE '%여행%';

------- 결과 
id    | author_id | title | description | created_at
10001 |        2  | 여행을 갔다 | 여행을 갔다 | 2025-04-14 12:42:00

 

SELECT * FROM table_A WHERE MATCH(title, description) AGAINST('여행' IN BOOLEAN MODE);

-------- 결과
id    | author_id | title | description | created_at

 

🤔 왜 이런 문제가 발생할까??

 

FULL TEXT index를 설정시 별도 설정을 하지 않았을 경우 기본 FULLTEXT Parser을 사용

• 한글 지원이 거의 안 됨
• 형태소 분석도 없고 단어 구분도 못해서 "여행을 갔다"에서 "여행" 검색이 안 될 수 있음
• LIKE '%검색어%' 보다는 빠르지만 실제 원하는 결과를 못 찾는 경우 많음

 

💡 n-gram 알고리즘 설정

CREATE FULLTEXT INDEX idx_fulltext_title_desc ON table_A(title, description) WITH PARSER ngram;

 

👋  결과

SELECT * FROM table_A WHERE MATCH(title, description) AGAINST('여행' IN BOOLEAN MODE);

-------- 결과 
id    | author_id | title | description | created_at
10001 |        2  | 여행을 갔다 | 여행을 갔다 | 2025-04-14 12:42:00

 

🆚 최종 비교

------ 전체 데이터 개수
SELECT COUNT(*) FROM table_A;

-> 100001

---- LIKE %_%
EXPLAIN ANALYZE 
SELECT * FROM table_A WHERE title LIKE '%여행%' OR description LIKE '%여행%';

-> Filter: ((table_a.title like '%여행%') or (table_a.`description` like '%여행%'))  (cost=10138 rows=20989) (actual time=6.05..48.7 rows=1 loops=1)
    -> Table scan on table_A  (cost=10138 rows=100016) (actual time=0.103..25.2 rows=100001 loops=1)

----- FULL TEXT INDEX
EXPLAIN ANALYZE
SELECT * FROM table_A WHERE MATCH(title, description) AGAINST ('여행' IN BOOLEAN MODE);

-> Filter: (match table_a.title,table_a.`description` against ('여행' in boolean mode))  (cost=0.35 rows=1) (actual time=0.167..0.182 rows=1 loops=1)
    -> Full-text index search on table_A using idx_fulltext_title_desc (title='여행')  (cost=0.35 rows=1) (actual time=0.134..0.148 rows=1 loops=1)

 

→ FULLTEXT INDEX를 통해 검색어 매칭된 소수의 row만 빠르게 조회

 

🍅 결론

LIKE 검색의 성능 문제를 해결하고자 MySQL Full-Text Index 기반 검색을 도입하였다.

추후 ElasticSearch 도입이나 검색 고도화도 한 번 도전해보고 싶다.

 

ElasticSearch는 자연어 처리 기반으로 훨씬 정교한 검색 기능 제공

• 형태소 분석
• 정확도 기준 정렬
• 복수 조건, 유사도 검색

 

📚 참고자료

https://www.yes24.com/product/goods/103415627

https://dev.mysql.com/doc/refman/8.4/en/innodb-fulltext-index.html

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

https://velog.io/@juhyeon1114/MySQL-검색-성능-개선하기-like와-full-text-search

https://inpa.tistory.com/entry/MYSQL-📚-풀텍스트-인덱스Full-Text-Index-사용법

https://velog.io/@leehanju408/검색-성능-개선-3-Full-Text-Index