MysqlDB - Where Like '%%' 텍스트 검색 성능 개선 (n-gram)

* 사용하는 환경은 AWS Aurora Mysql8.0 DB입니다.

 

LIKE 절에서 '%'를 앞뒤로 사용하면 인덱스를 사용할 수 없기 때문에 성능 저하가 발생합니다. 이 문제를 해결하기 위해 다음과 같은 몇 가지 방법을 고려해 볼 수 있습니다.

 

1. Full-text 검색 사용: MySQL은 전체 텍스트 검색 기능을 지원합니다. 이를 사용하면 대량의 텍스트 데이터에서 빠르게 검색할 수 있습니다. FULLTEXT 인덱스를 생성하고, MATCH() ... AGAINST() 구문을 사용하여 검색할 수 있습니다.

 

예시)

CREATE FULLTEXT INDEX idx_fulltext_column_name ON table_name(column_name);
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('abc' IN NATURAL LANGUAGE MODE);

 

2. 데이터 정규화: 데이터의 중복을 제거하고, 유사한 데이터를 별도의 테이블에 저장하여 조인을 통해 검색 성능을 향상할 수 있습니다. 이 방법은 데이터베이스 설계를 변경해야 하는 단점이 있지만, 장기적으로 검색 성능 향상에 도움이 됩니다.

3. n-gram 인덱싱 사용: n-gram 인덱스를 사용하면 문자열의 부분집합을 기반으로 인덱싱하여 검색 성능을 향상시킬 수 있습니다. MySQL에서는 n-gram 인덱스를 직접 지원하지 않으므로, 응용 프로그램 레벨에서 구현해야 합니다. n-gram 인덱스 테이블을 생성하고, 원본 테이블과 조인하여 검색할 수 있습니다.

4. 응용 프로그램 레벨에서의 필터링: 데이터를 가져온 후 응용 프로그램에서 필터링하는 방식을 고려할 수 있습니다. 이 방법은 데이터베이스에서 검색 성능을 향상하지 않지만, 전체 시스템 성능에 도움이 될 수 있습니다.

 

* n-gram 이란?

n-gram 인덱스는 문자열 데이터를 고정 길이의 연속된 문자 조합으로 분리하여 인덱싱하는 방식입니다. n-gram 인덱스는 텍스트 검색에서 유용하게 사용되며, 부분 문자열 검색에 특히 효과적입니다. 여기서 n은 n-gram의 길이를 나타내며, 일반적으로 2 또는 3을 사용합니다.

 

MySQL은 기본적으로 n-gram 인덱스를 지원하지 않으므로, 응용 프로그램 레벨 또는 트리거를 사용하여 n-gram 인덱스를 생성하고 관리해야 합니다. n-gram 인덱싱 절차는 다음과 같습니다.

 

1. n-gram 인덱스 테이블 생성: 원본 테이블의 각 문자열에 대해 n-gram 데이터를 저장할 테이블을 생성합니다. 이 테이블은 원본 테이블의 기본 키와 n-gram 데이터를 포함해야 합니다.

 

예시)

CREATE TABLE ngram_index (
  id INT AUTO_INCREMENT PRIMARY KEY,
  original_id INT NOT NULL,
  ngram VARCHAR(3) NOT NULL,
  FOREIGN KEY (original_id) REFERENCES original_table(id)
);

 

2. n-gram 데이터 삽입: 원본 테이블에 데이터가 삽입될 때마다 n-gram 인덱스 테이블에 해당하는 n-gram 데이터를 삽입합니다. 이 작업은 응용 프로그램 레벨에서 처리하거나, 데이터베이스 트리거를 사용하여 자동화할 수 있습니다.

3. 검색: n-gram 인덱스를 사용하여 검색을 수행하려면, 검색하려는 문자열의 n-gram을 생성한 후 n-gram 인덱스 테이블에서 일치하는 항목을 찾습니다. 일치하는 항목이 있는 원본 테이블의 행을 반환합니다. 이 작업은 다음과 같은 SQL 쿼리로 수행할 수 있습니다.

 

예시) 

SELECT original_table.*
FROM original_table
JOIN ngram_index ON original_table.id = ngram_index.original_id
WHERE ngram_index.ngram IN ('검색하려는 문자열의 n-gram 목록')
GROUP BY original_table.id
HAVING COUNT(*) >= 일치해야 하는 n-gram 개수;

 

n-gram 인덱싱은 부분 문자열 검색 성능을 향상하지만, 인덱스 테이블 크기가 커질 수 있다는 단점이 있습니다. 따라서 저장 공간과 검색 성능 간의 균형을 고려해야 합니다. n-gram 인덱싱의 몇 가지 주요 고려 사항은 다음과 같습니다.

 

1. n 값 선택: n 값이 작을수록 인덱스 크기가 커지고, 검색 속도가 빨라집니다. 반대로 n 값이 클수록 인덱스 크기가 작아지고, 검색 속도가 느려집니다. 일반적으로 n = 2 또는 3을 사용하며, 검색 정확도와 인덱스 크기 간의 균형을 찾아야 합니다.

2. 인덱스 관리: n-gram 인덱스 테이블은 원본 테이블과 동기화되어야 합니다. 트리거, 스토어드 프로시저 또는 응용 프로그램 레벨에서 원본 테이블의 변경 사항을 n-gram 인덱스 테이블에 반영해야 합니다.

3. 검색 정확도: n-gram 인덱싱은 부분 문자열 검색에 높은 정확도를 제공하지만, 모든 경우에 완벽한 결과를 보장하지는 않습니다. 검색 정확도를 높이려면 여러 가지 검색 전략을 함께 사용하는 것이 좋습니다. 예를 들어, n-gram 인덱싱과 전체 텍스트 검색을 조합하여 사용할 수 있습니다.

4. 성능 최적화: n-gram 인덱스를 사용할 때는 쿼리 성능을 최적화하는 것이 중요합니다. 인덱스 테이블과 원본 테이블 간의 조인과 필터링 조건을 효율적으로 사용하여 검색 속도를 높일 수 있습니다.

 

n-gram 인덱싱은 부분 문자열 검색 성능을 크게 향상할 수 있는 강력한 기법입니다. 하지만 저장 공간, 검색 정확도, 인덱스 관리 등의 고려 사항을 염두에 두고 사용해야 합니다. 이러한 고려 사항을 충족하는 방식으로 n-gram 인덱싱을 구현하면, 원하는 검색 성능을 얻을 수 있습니다.