MySQL에서 LIKE 연산자의 성능을 대체하기 위한 FULLTEXT 인덱스

LeeYulhee·2023년 11월 3일

sql

👉 LIKE를 사용할 때의 문제점

검색할 컬럼을 일반 INDEX로 설정
LIKE 단어% 를 사용하여 검색할 때는 부분 문자열 검색에 인덱스를 사용할 수 있
LIKE %단어나 LIKE %단어% 로 조회할 때는 인덱스가 적용되지 않고 테이블 Full Scan을 하게 됨
- 인덱스가 적용되지 않는 이유
  - DB 인덱스의 자료 구조는 대부분 B-TREE 구조로 되어 있음
  - 간단하게 설명하면, 종이 사전에서 단어를 찾을 때 첫 글자를 모르는 경우에 목차 순서로 찾을 수 없는 것과 같음

👉 해결 방안 : FULLTEXT INDEX

데이터베이스 내의 텍스트 컨텐츠를 검색하기 위해 설계된 특수한 타입의 인덱스
텍스트 문서나 문자열 데이터 내에서 단어나 구(phrase)를 검색할 때 효율적
텍스트 데이터를 토큰이라는 단위로 분해하고, 이 토큰들을 인덱스에 저장하여 검색할 수 있게 함
- 이를 통해 사용자가 검색 쿼리를 수행할 때, 데이터베이스는 각 단어의 위치를 빠르게 찾아낼 수 있게 됨
모든 데이터베이스가 FULLTEXT INDEX를 지원하는 것은 아니니 확인 필요

👉 텍스트 데이터 인덱싱 기법 : Parser

📌 Parser의 정의
- 텍스트 데이터를 인덱싱할 때 사용하는 분석 기법이나 알고리즘
📌 Parser의 종류
- 기본 파서(Default Parser) = Stop-word Parser
  - 설명
    - 기본적으로 사용되는 파서
    - 대부분의 알파벳 기반 언어(영어 등)에 적합
    - 미리 정의된 'stopwords' 목록을 기반으로 일반적으로 검색에 도움이 되지 않는 일반적인 단어들을 제외함
      - stopword 리스트는 ft_stopword_file 시스템 변수로 설정
  - 예시
    - the, is, at, which, on 등이 여기에 속함
- N-gram Parser
  - 설명
    - 주로 한국어, 일본어, 중국어와 같이 띄어쓰기로 단어를 구분하지 않거나 알파벳이 아닌 문자를 사용하는 언어를 위해 설계됨
    - 설정된 n 값에 따라 텍스트를 n 개의 연속된 문자로 구성된 토큰으로 분리
    - 각각의 조합은 인덱스에 저장되어, 검색 쿼리가 수행될 때 이러한 조합을 기반으로 문서를 빠르게 찾을 수 있게 됨
    - N-gram 파서를 사용하려면 인덱스를 생성할 때, 명시적으로 지정해야 함
      - 아래 쿼리로 조회했을 때, COMMENT 컬럼에 ngram이 있으면 적용된 것
        SELECT INDEX_NAME, INDEX_TYPE, COMMENT FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = '데이터베이스 이름' AND TABLE_NAME = '테이블 이름' AND INDEX_TYPE = 'FULLTEXT';
  - 예시
    - ngram 이라는 단어에 대한 2-gram은 ng, gr, ra, am이 됨

👉 FULLTEXT INDEX 조회 방식 : Search Type

📌 Search Type의 정의
- 검색 쿼리의 동작 방식
- 저장된 인덱스를 어떻게 검색할지에 대한 쿼리의 행위를 정의
📌 FULLTEXT INDEX를 사용하는 검색 쿼리 종류
- 💡 Natural Language Search(자연어 검색)
  - 설명
    - 기본 검색 유형으로, FULLTEXT 인덱스를 이용하여, 자연 언어 처리 기법을 적용한 검색을 수행하는 방식
    - 쿼리 문장이 자연어로 작성되었다고 가정하고, 일치하는 토큰의 빈도수에 따라 결과를 반환
      - = 전체 텍스트 내용에서 문맥적으로 가장 관련성이 높은 항목의 결과
  - 장점
    - 사용자가 복잡한 구문을 몰라도 키워드를 입력하기만 하면 되어서 편리함
    - 단어와 문구의 빈도수 및 상호 관계를 기반으로 결과를 제공하여 문맥을 인식한다고 볼 수 있음
    - 기본적으로 결과는 관련성이 높은 순서로 정렬되기 때문에 자동으로 관련성이 평가됨
  - 단점
    - 특정 키워드의 논리적 조합(AND, OR, NOT)을 사용하여 검색하는 데 한계가 있어서 정교함이 부족함
      - = 관련이 없는 데이터가 반환되기도 함
    - 사용자가 결과를 미세 조정할 수 있는 옵션이 제한적이라 복잡한 쿼리 불가
- 💡 Boolean Search
  - 설명
    - FULLTEXT 인덱스를 가진 필드에서 논리 연산자(AND, OR, NOT)와 같은 Boolean 구문을 사용하여 더 세밀하게 검색할 수 있게 함
    - 사용자는 키워드 사이에 논리 연산자를 사용하여, 검색 조건을 정확하게 지정할 수 있음
    - 검색어의 빈도수나 관련성을 고려하지 않고, 주어진 조건에 딱 맞는 결과를 찾아 줌
  - 장점
    - 사용자는 논리 연산자를 사용하여 검색 결과를 더욱 정확하게 조정할 수 있기 때문에 비교적 정교함
    - 복잡한 쿼리를 구성하여, 특정 조건을 만족하는 결과만 추출할 수 있음
    - 사용자가 검색의 정확도와 범위를 컨트롤할 수 있음
  - 단점
    - 사용자가 Boolean 구문을 알고 있어야 하며, 복잡한 쿼리를 작성할 수 있어야 함
    - 자연 언어 검색만큼 문맥에 민감하게 반응하지 않을 수 있음
    - 자연 언어 검색과는 달리, Boolean 검색 결과는 관련성에 따라 자동으로 순위가 매겨지지 않음
    - 시간이 오래 소요될 수 있음
- 💡 Query Expansion(쿼리 확장)
  - 설명
    - 검색 쿼리의 결과를 확장하여 더 많은 관련 결과를 포함시키는 방법
    - 자연어 검색을 수행한 후, 결과를 바탕으로 유사한 단어를 포함하여 검색 범위를 확장
    - 사용자가 제공한 검색어가 너무 제한적이거나 명확하지 않을 때 유용
    - 예를 들어, ‘사과 주스’라는 문구로 검색했을 때 Query Expansion을 활용하면 MySQL은 초기 검색 결과에서 자주 등장하는 다른 단어들을 찾아내어 검색어에 추가
      - ‘오렌지’, ‘과일’, ‘음료’와 같은 단어들이 추가될 수 있음
  - 장점
    - 사용자가 입력한 검색어에 추가적인 관련 키워드를 포함시킴으로써, 보다 광범위한 검색 결과를 얻을 수 있음
    - 사용자가 명확하지 않거나 애매한 쿼리를 입력했을 때, 관련있는 추가 키워드를 통해 검색의 정확도를 개선할 수 있음
    - 단어가 여러 의미를 가질 때, Query Expansion은 다양한 맥락에서 단어를 검색하여 사용자가 원하는 정보를 찾는데 도움을 줄 수 있음
  - 단점
    - 쿼리 확장에 의해 추가된 단어들이 사용자의 의도와 맞지 않을 경우, 불필요한 검색 결과를 초래하여 결과의 정확성을 떨어뜨릴 수 있음
    - 관련 없는 키워드로 인해 너무 많은 결과가 반환되어 사용자가 원하는 정보를 찾기 어렵게 만들 수 있음
    - 검색어가 복잡해지고 검색 범위가 넓어져 처리 시간이 늘어날 수 있음

👉 FULLTEXT INDEX 적용 방법

Stop-word Parser의 경우

테이블 생성과 동시에 설정

CREATE TABLE 테이블 이름(
	/* 컬럼 생성 쿼리 */
  , FULLTEXT 인덱스 이름 (컬럼 이름)
);

테이블 생성 후에 추가

CREATE FULLTEXT INDEX 인덱스 이름 ON 테이블 이름 (컬럼 이름);

N-gram Parser 적용하는 법

테이블 생성과 동시에 설정

CREATE TABLE 테이블 이름(
	/* 컬럼 생성 쿼리 */
  , FULLTEXT 인덱스 이름 (컬럼 이름) WITH PARSER ngram
);

테이블 생성 후에 추가

CREATE FULLTEXT INDEX 인덱스 이름 ON 테이블 이름 (컬럼 이름) WITH PARSER ngram;

👉 FULLTEXT INDEX 적용 여부 확인

SHOW INDEX 쿼리 사용

SHOW INDEX FROM `테이블 이름` WHERE Index_type = 'FULLTEXT';

INFORMATION_SCHEMA.STATISTICS 테이블 조회

SELECT *
  FROM INFORMATION_SCHEMA.STATISTICS
 WHERE TABLE_SCHEMA = '데이터베이스 이름'
   AND TABLE_NAME = '테이블 이름'
   AND INDEX_TYPE = 'FULLTEXT';

👉 FULLTEXT INDEX가 적용된 컬럼에서의 조회

📌 IN NATURAL LANGUAGE MODE
```
SELECT * FROM 테이블 이름
WHERE MATCH(컬럼 이름) AGAINST('검색어' IN NATURAL LANGUAGE MODE);
```
- 보다 일반적인 검색으로 자연스러운 언어 처리를 가능하게 하여 더 관련성 높은 결과를 반환하도록 함
📌 IN BOOLEAN MODE
```
SELECT * FROM 테이블 이름
WHERE MATCH(컬럼 이름) AGAINST('검색어' IN BOOLEAN MODE);
```
- 특정 FULLTEXT 검색 기능을 활용하고자 할 때 사용
- 예를 들어, 단어를 포함하거나 제외하는 것, 인접한 단어 검색, 더 많은 유연성을 위한 와일드카드 사용 등

👉 N-gram Parser와 Natural Language Search를 위한 설정 변경

N-gram Parser가 사용하는 토큰의 크기 설정
```
[mysqld]
ngram_token_size=1
```
- my.ini 파일이나 my.cnf 파일에서 설정
- 기본 값은 2로 되어 있음
Natural Language Search 모드에서 적용되는 최소 토큰의 크기 설정
```
[mysqld]
innodb_ft_min_token_size=2 /* InnoDB 엔진 */
```
```
[mysqld]
ft_min_word_len=2 /* MyISAM 엔진 */
```
- 이 설정보다 짧은 단어는 FULLTEXT INDEX에서 조회할 때 무시하기 때문에 필요에 따라 설정해줘야 함
- 검색되는 단어의 최소 길이는 3으로 기본 설정되어 있음
⇒ 해당 설정 후 MySQL 서버 재시작 & 기존의 FULLTEXT INDEX 삭제 후 재생성 해야 함

👉 FULLTEXT INDEX 사용 시, 성능 저하의 가능성

성능 저하의 이유
- 새로운 데이터가 삽입되거나 기존 데이터가 변경될 때마다 인덱스의 텍스트를 토큰화하고, 이를 색인 구조에 맞게 저장해야 하기 때문
- 대량의 데이터가 삽입되거나 자주 변경되는 데이터, 인덱스가 재구성되는 상황 등에서 성능 저하가 느껴질 수 있음
해결 방법
- 비동기 인덱싱
  - 데이터 삽입이나 수정이 일어날 때 즉시 인덱스를 업데이트하는 것이 아니라, 이 작업을 백그라운드 프로세스로 처리
  - DBMS나 검색 엔진에 따라 비동기 인덱싱을 지원하는 방법이 다름
    - MySQL이나 MariaDB
      - innodb_ft_enable_stopword, innodb_ft_num_word_optimize 등의 설정을 조정하여 인덱스 업데이트의 동작을 제어할 수 있음
- 배치 처리
  - 대량의 데이터를 처리할 때는 배치 작업을 통해 효율적으로 인덱싱할 수 있음
- 적절한 인덱스 관리
  - 인덱스를 주기적으로 최적화하고 불필요한 인덱스를 제거하여 관리
- 리소스 관리
  - 서버의 자원(CPU, 메모리, I/O)을 적절히 할당하여 인덱싱 작업이 시스템 전체의 성능에 미치는 영향을 최소화