MySQL8.0 에서 부분 문자열을 포함한 데이터 조회 기능에 성능 저하 문제를 해결한 과정에 대한 글입니다.(온몸을 비틀어서 개선한..)

결과적으로 약 30만 건의 데이터가 있는 테이블에서의 검색 성능이 약 18배 개선되었습니다.
(870 ms -> 48 ms)

1. 문제 상황

부분 문자열을 포함한 데이터 조회 기능에 성능 저하 문제가 발생해습니다.
SQL의 where절에 ‘%검색할문자%’와 같이 조건을 사용해 인덱스가 활용되지 않고 있어 발생한 문제였습니다.

WHERE goods_name LIKE '%검색할문자열%'

최근에는 ElasticSearch나 ApachSolr와 같은 검색에 특화된 엔진을 함께 활용해 검색 성능을 확보하는 경우가 많습니다.
하지만 해당 툴을 바로 사용할 수 없는 상황(학습이 필요한 경우 등..) 이었습니다.

2. 개선 방법

N-gram과 MySQL8.0의 Full-Text Index를 사용해 문자열 검색 성능을 개선했습니다.

1) N-gram이란?

N-gram은 문자열을 일정 크기(N)로 쪼개서 연속된 문자열 조각들을 추출하는 방법을 말합니다.

문자열: "검색할문자열"
3-gram일 때: "검색할", "색할문", "할문자", "문자열"

이처럼 N-gram은 검색어를 작은 단위로 쪼개어 인덱스를 구성하므로,

다음과 같은 장점이 있습니다:

• 형태소 분석(띄어쓰기가 애매한 언어)에 유용
  • 한글, 일본어, 중국어처럼 공백 구분이 어려운 언어에서도 비교적 단순하게 적용 가능
• 접두사/접미사와 같은 부분 일치 검색에 유리
  • 예: "검색할문자열"라는 단어가 들어간 곳에 “검색할”, “할문자” 등 일부만 가지고도 검색 가능

단점으로는, 문자열이 길어질수록 조각이 매우 많아지기 때문에 인덱스 크기가 커질 수 있습니다.

예: 문자열 길이가 10인 경우, 2-gram으로 분해하면 9개의 조각이 생성됨

2) Full-Text Index란?

MySQL의 Full-Text Index는 내부적으로 역색인(Inverted Index) 방식을 사용하여 텍스트 검색을 최적화합니다. 전통적으로 MyISAM 스토리지 엔진에만 지원되던 풀텍스트 인덱스가, MySQL 5.6 이후로 InnoDB에서도 지원되며, MySQL 8.0에서는 성능과 확장성 면에서 다양한 기능이 추가되었습니다.

Full-Text 검색이란, 테이블에 있는 문자열 열(TEXT, VARCHAR 등)에 대하여 단어 기반으로 빠르게 검색할 수 있게 해주는 기능입니다. 기본적으로는 영어 단어처럼 공백이나 구두점을 분리 기준으로 삼아 토큰화를 하지만, 한글이나 일본어처럼 공백 구분이 명확하지 않은 언어에서는 별도의 파서(parser) 또는 N-gram 방식을 사용해야 합니다.

3) N-gram + Full-Text Index를 통한 검색 성능 개선

위의 N-gram과 Full-Text Index를 함께 활용해 검색 성능을 개선했습니다.

N-gram 방식은 단순히 문자열을 연속된 N개의 문자로 쪼개는 방식이라 비교적 쉽게 구현할 수 있고, 이 때 N개의 문자로 쪼개진 문자열에 Full-Text Index를 적용만 하면 되는 방식입니다.

특히 주의할 점은, 자주 Update 되는 값에 적용하면 Update 할 때 마다 N-gram을 새로 생성해야 하기 때문에 Update 성능에 문제가 발생할 수 있습니다.
따라서 자주 바뀌지 않는 값(e.g. 상품명 : 거의 안 바뀜)에 적용했습니다.

3. 고민한 대안

아래는 적용을 고민했지만, 결과적으로 적용하지 않은 대안들입니다.

1. 역색인(Inverted Index) 테이블 구축
   • 설명: 각 단어와 해당 단어를 포함하는 문서 ID를 매핑하는 별도의 인덱스 테이블을 생성해 사용하는 방법
   • 추가적인 테이블을 구축하고, 구현이 복잡함
   • 추가로 생성된 테이블을 Join해 사용해야 하므로 추가적인 성능 저하가 발생할 수 있음
2. 정규표현식(REGEXP) 사용
   • 인덱스를 사용할 수 없고, DB CPU 사용률이 증가해 전체 시스템의 성능에 영향을 미칠 수 있음
   • 입력 값에 따라 정규표현식의 실행 시간이 비정상적으로 늘어나는 ReDoS(Regular Expression Denial of Service) 공격에 취약함

4. 구현 방법

1) 선택하지 않은 방법(비교적 쉬움)

해당 방법은 MySQL 서버에 N-gram Parser Plugin을 설치해 사용하는 방법입니다.
MySQL이 N-gram을 만들어주는 방식이라 비교적 쉽게 구현 가능합니다.
하지만, 추후에 ElasticSearch 등의 전문 검색 엔진을 도입하게 되면 N-gram 방식은 없앨 예정이라 MySQL 서버에 Plugin을 설치하는 것에 부담을 느꼈고,
DB가 N-gram을 생성할 때 DB에 추가적인 부하를 줄 수 있다고 생각해 선택하지 않았습니다.

아래는 N-gram Parser Plugin을 설치해 사용하는 방법입니다.

1. N-gram Parser Plugin 설치

INSTALL PLUGIN ngram SONAME 'ngramparser.so';

2. 풀텍스트 인덱스 생성 시 파서 지정

CREATE TABLE goods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name TEXT,
    
    FULLTEXT KEY ngram_idx (name) WITH PARSER ngram
) ENGINE=InnoDB;

이렇게 인덱스를 설정하면, N-gram으로 잘린 결과가 인덱스에 저장됩니다.
하지만, N-gram으로 잘린 결과를 확인할 수 없습니다.

3. 필요한 경우 N-gram 파라미터 설정

-- ngram_token_size: 몇 글자 단위로 자를지 지정(기본값: 2)
SET GLOBAL innodb_ft_aux_table = 'mydb/goods';
SET GLOBAL ngram_token_size=2;

4. 검색 쿼리 예시
   MySQL에서 풀텍스트 검색은 MATCH ... AGAINST 구문을 사용합니다

SELECT *
FROM goods
WHERE MATCH(name) AGAINST('검색할상품명' IN NATURAL LANGUAGE MODE);

2) 선택한 방법(추가적인 작업이 필요)

저는 N-gram을 생성하는 로직을 직접 구현하는 방식을 선택했습니다.
이유는, 추후에 ElasticSearch와 같은 전문 검색 엔진을 도입했을 때, N-gram과 관련된 로직은 폐기할 예정이고, 이 때 간단히 관련 로직을 제거할 수 있기 때문입니다.
또한, N-gram을 사용하는 동안 관련 로직을 유지보수 하는데에도 유리할 것이라 판단했습니다.

아래는 구체적인 구현 방법입니다.

(1) 엔터티 클래스에 Ngram 생성 로직 추가

N-gram의 길이를 설정하고, 조합을 생성합니다. 저는 3-gram을 선택했습니다.

실제로는, Ngram 생성 로직을 외부 UtilClass에 정의해 TestCode를 작성할 수 있게 했습니다.
여기에선 설명 편의상 Entity 내부에 작성했습니다.

@Entity
@Table(name = "goods")
public class Goods {

    @Id
    @Column(name = "goods_id")
    private String goodsId;

    @Column(name = "goods_name")
    private String goodsName;

	@Lob
    @Column(name = "goods_name_ngrams")
    private String goodsNameNgrams;

    // 기타 필드 생략

    // Getter와 Setter
    public String getGoodsName() {
        return goodsName;
    }

	// 해당 기능은 Entity의 생성자에 넣어도 됩니다.
    public void setGoodsName(String goodsName, maxGramLength) {
        this.goodsName = goodsName;
        generateNgrams(goodsName, maxGramLength); // 상품명 설정 시 Ngram 생성
    }

    // Ngram 생성 메소드 -> 실제로는 TestCode 작성을 위해 NgramUtilClass에 구현했습니다.
	private void generateNgrams(String input, int maxGramLength) {
		StringBuilder result = new StringBuilder();
        if (input != null && input.length() >= maxGramLength) {
            for (int i = 0; i <= input.length() - maxGramLength; i++) {
                result.append(input, i, i + maxGramLength).append(" ");
            }
        }
        
        this.goodsNmNgrams = result.toString().stripTrailing();
	}
}

1. maxGramLength 설정:
   • maxGramLength는 Ngram의 길이를 설정합니다. 여기서는 예시로 3-gram을 생성하도록 설정하였습니다.
   • 만약 상품명의 길이가 N보다 짧다면, 상품명의 길이만큼 설정합니다.
2. for 루프를 사용한 Ngram 생성:
   • 생성된 Ngram들을 공백으로 구분하여 goodsNameNgrams에 저장합니다.

(2) 서비스 클래스에서 저장 및 수정 시 Ngram 생성 유지

서비스 클래스에서는 변경된 사항은 없습니다.(아래 예시에선 특별히 조회 기능은 추가함)
Goods 엔터티의 setGoodsName() 메서드에서 자동으로 Ngram이 생성되므로, 동일한 로직으로 저장 및 수정할 때 자동으로 Ngram이 반영됩니다.

@Service
@RequiredArgsConstructor
public class GoodsService {

    private final GoodsRepository goodsRepository;
	
    @Transactional
    public Goods saveGoods(GoodsSaveDto goodsDto) {
    	...
        // 실제로는 Entity 생성할 때(생성자) 들어감
        goods.setGoodsName(goodsDto.getGoodsName()); 
        ...
        return goodsRepository.save(goods);
    }

    @Transactional
    public Goods updateGoods(Goods goods, String name) {
        Goods existingGoods = goodsRepository.findById(goods.getGoodsId())
                .orElseThrow(() -> new RuntimeException("Goods not found"));
        existingGoods.setGoodsName(name); // 상품명 변경 시 자동으로 Ngram 생성
        return goodsRepository.save(existingGoods); // JPA 변경감지 사용하면 없어도 됨  
    }

	@Transactional(readOnly = true)
    public List<Goods> searchGoods(String keyword) {
        return goodsRepository.searchByGoodsNameNgrams(keyword);
    }
}

(3) JPA와 NativeQuery

public interface GoodsRepository extends JpaRepository<Goods, Long> {

    @Query(
      value = "SELECT * FROM goods " +
              "WHERE MATCH(goods_name_ngrams) " +
              "AGAINST(CONCAT('+', :keyword, '*') IN BOOLEAN MODE)",
      nativeQuery = true
    )
    List<Goods> searchByGoodsNameNgrams(@Param("keyword") String keyword);

}

(4) Table에 인덱스 추가 및 SQL 설명

1. goods_name_ngrams 컬럼에 대해 FULLTEXT 인덱스를 생성합니다.

ALTER TABLE goods ADD COLUMN goods_name_ngrams TEXT;

ALTER TABLE goods
  ADD FULLTEXT INDEX idx_goods_name_ngrams (goods_name_ngrams);

2. 검색 쿼리
   MATCH ... AGAINST를 사용합니다.

SELECT * FROM goods
WHERE MATCH(goods_name_ngrams) AGAINST('이름' IN BOOLEAN MODE);

BOOLEAN MODE란?

MATCH ... AGAINST를 Boolean 모드로 사용하면, +, -, *와 같은 연산자를 사용하여 검색 조건을 더 구체화할 수 있습니다.

SELECT * FROM goods
WHERE MATCH(goods_name_ngrams) AGAINST('+사과*' IN BOOLEAN MODE);

• +(플러스 기호): 해당 단어가 반드시 포함되어야 함을 의미합니다.
• *(별표): 와일드카드처럼 사용되며, 부분 문자열을 찾을 수 있도록 합니다.

5. 적용 시 주의사항

• N-gram을 적용할 Column의 값을 Update할 때, 성능 문제가 발생할 수 있음

  • 위의 예시처럼 자주 바뀌지 않을 값에 적용하는 것이 좋음

• 인덱스 크기 증가

  • N-gram 특성상 쪼개는 단위가 많아지므로 인덱스가 커지고, 생성/갱신 시간이 길어질 수 있음

• 정확도 vs. 성능

  • N의 값이 작을수록 더욱 세분화된 검색이 가능해지지만, 그만큼 인덱스 크기 부담이 커짐
  • 적절한 N을 선택해야 함(한국어, 일본어의 경우 일반적으로 2~4-gram 선호)

• 불필요한 토큰 필터링

  • 중복 문자열(반복되는 구두점 등)이나 stopword(짧은 조사 등)를 효율적으로 필터링하는 전략이 필요할 수 있음