FULLTEXT 인덱스 개요, 특징과 작동원리

데이터베이스에서 인덱스란? 왜 필요한가?

 

FULLTEXT 인덱스

개요: MySQL(5.6버전 이상)

- 양방향 LIKE 연산자를 사용해야 하는 경우엔 인덱스를 사용하기 어렵다.

- 하지만 FULLTEXT 인덱스를 사용하여 자연어 검색을 최적화하는 방법이 있다.

 

- FULLTEXT 인덱스는 텍스트 데이터를 빠르게 검색할 수 있는 인덱스로, 전체 테이블 스캔으로 인한 성능 저하를 피할 수 있다.

- 자연어 검색과 불완전한 문자열 검색에 적합하며, 일반적인 인덱스와 달리 양방향 와일드카드 검색에도 효과적이다.

 

- MySQL에서 MyISAM 및 InnoDB 스토리지 엔진에서 사용할 수 있다.

- 컬럼 데이터 타입은 반드시 Text, Binary Char, Varchar 타입을 가져야 한다.

- 한글 데이터를 검색하려면 테이블 인코딩으로 utf8을 사용해야 한다.

특징

- 자연어 검색 지원: 일치하는 단어의 빈도와 위치를 기반으로 검색 결과의 관련성을 계산하여 결과를 정렬

- 양방향 와일드카드 검색 지원: 일반 인덱스와 달리 FULLTEXT 인덱스는 양방향 와일드카드 검색에 효과적

- 빠른 텍스트 검색: FULLTEXT 인덱스는 텍스트 데이터를 빠르게 검색할 수 있도록 최적화

 

작동 원리(토큰화-> 인덱싱 -> 검색)

토큰화

- FULLTEXT 인덱스는 텍스트 데이터를 공백, 구두점 등을 기준으로 토큰(단어)으로 분리한다.

- 이 과정에서 불용어(stopwords)라고 하는 의미 없는 단어(예: "a", "an", "the" 등)는 인덱싱에서 제외된다.

 

인덱싱:

- 각 토큰은 데이터베이스 내 해당 텍스트 컬럼에 대한 인덱스 테이블에 저장된다.

- 인덱스 테이블은 토큰과 해당 토큰이 포함된 행의 ID(참조)를 매핑한다.

 

검색

- FULLTEXT 인덱스를 사용한 검색 시 데이터베이스는 인덱스 테이블을 사용하여 해당 키워드(또는 키워드 집합)와 일치하는 행을 빠르게 찾는다.

 

 

다양한 검색 모드 종류

자연어 검색 모드: IN NATURAL LANGUAGE MODE

- 검색 문자열을 단어 단위로 분리한 후, 해당 단어 중 하나라도 포함되는 행을 찾는다.

- 자연어 검색 모드에서는 일반적인 텍스트 검색에 가장 적합한 방식으로 검색을 수행한다.

- 이 모드에서는 일치하는 단어의 빈도와 위치를 기반으로 관련성(relevance)을 계산하여 결과를 정렬한다.

 

불린 검색 모드 : IN BOOLEAN MODE

- 검색 문자열을 단어 단위로 분리한 후, 해당 단어가 포함되는 행을 찾는 규칙을 추가적으로 적용하여 해당 규칙에 매칭되는 행을 찾는다

- 불린 검색 모드에서는 더 정교한 검색을 수행할 수 있으며, 사용자는 와일드카드, 불린 연산자(AND, OR, NOT 등) 및 필수 및 선택적 단어를 사용하여 검색 쿼리를 정의할 수 있다.

 

쿼리 확장 모드 : IN QUERY EXPANSION MODE

- 2단계에 걸친 검색을 수행

- 쿼리 확장 모드에서는 검색어를 사용하여 관련 단어를 찾은 다음, 이 관련 단어를 포함한 결과를 반환한다.

- 이 검색 모드는 사용자가 입력한 검색어와 관련이 높은 결과를 찾는 데 도움이 된다.

- 먼저 자연어 검색을 수행한 후, 결과에서 가장 높은 관련성을 가진 행의 단어를 가져와 쿼리에 추가하여 두 번째 검색을 수행한다.

 

 

MySQL 활용 예시

- 양방향 LIKE 연산자와 유사한 결과를 제공하는 동시에, 검색 성능을 향상함

 

다음과 같이 `FULLTEXT` 인덱스를 생성할 수 있다.

CREATE FULLTEXT INDEX users_name_fulltext_idx ON users(name);

 

이 경우  `MATCH ... AGAINST` 구문을 사용하여 자연어 검색을 수행할 수 있다.

SELECT * FROM users WHERE MATCH(name) AGAINST ('John' IN NATURAL LANGUAGE MODE);

 

FULLTEXT 인덱스 - Nest.js, TypeORM(MySQL) 활용 예시

- 양방향 검색이 가능하면서도 인덱스를 활용하여 검색 성능을 향상시킬 수 있다.

// user.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, Index } from 'typeorm';

@Entity()
@Index('users_name_fulltext_idx', ['name'], { fulltext: true }) //'name' 컬럼에 FULLTEXT 인덱스를 생성.
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  @Column()
  phone: string;
}

 

// user.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User)
    private userRepository: Repository<User>,
  ) {}

  async searchUsersWithFulltextIndex(searchTerm: string): Promise<User[]> {
    const queryBuilder = this.userRepository.createQueryBuilder('user');
    
    queryBuilder.where(`MATCH(user.name) AGAINST(:searchTerm IN NATURAL LANGUAGE MODE)`, { searchTerm });

    return queryBuilder.getMany();
  }
}

https://kmongcom.wordpress.com/2014/03/28/mysql-%ED%92%80-%ED%85%8D%EC%8A%A4%ED%8A%B8fulltext-%EA%B2%80%EC%83%89%ED%95%98%EA%B8%B0/

MySQL 풀 텍스트(FULLTEXT) 검색하기