현재 상황
-
기존에 name 컬럼에 일반 B-Tree 인덱스를 설정했으나,
'%유한%'과 같이 와일드카드가 앞쪽에 붙는 부분 일치 검색(wildcard 앞부분) 은 B-Tree 인덱스의 좌측 정렬 특성상 인덱스 활용이 불가능합니다. -
이 경우 MySQL은 풀 테이블 스캔(Full Table Scan) 또는 풀 인덱스 스캔(Full Index Scan) 을 수행해 성능 저하가 발생합니다.
-
이를 개선하기 위해 MySQL의 브랜드 이름은 Full-Text 인덱스 + ngram parser 방식을 아이템 이름은 Full-Text 인덱스를 적용했습니다.
1. 아이템 이름에 일반 Full-Text 인덱스 선택한 이유
아이템 이름은 그 특성상 주로 단어 단위의 명확한 검색에 사용됩니다. 예를 들어, '스마트폰', '노트북', '헤드폰'과 같이 특정 제품명을 검색하는 경우가 대부분입니다.
MySQL의 기본 Full-Text 인덱스는 이러한 단어 단위를 기준으로 토큰화하여 인덱스를 생성합니다. 이는 전체 단어 매칭 검색 시 매우 효율적이며, 인덱스 자체의 크기 관리나 운영 측면에서도 이점을 가집니다. 따라서 아이템 이름 필드에는 이러한 데이터 특성을 고려하여 일반 Full-Text 인덱스를 적용하는 것이 적합하다고 판단했습니다.
2. 브랜드 이름에 Ngram Full-Text 인덱스 선택한 이유
브랜드 이름은 아이템 이름과 달리 비교적 짧고 고유명사 형태가 많습니다. 특히 한글 브랜드명의 경우, 검색 시 검색어가 단어의 중간에 포함되는 경우가 빈번하게 발생합니다.
MySQL의 기본 Full-Text 인덱스는 기본적으로 단어의 '접두사'만을 인덱싱하기 때문에, '%유한%'과 같이 단어의 중간 부분을 검색할 때는 인덱스를 효율적으로 사용하기 어렵다는 한계가 있습니다.
이러한 문제를 해결하기 위해 Ngram Parser를 활용한 Full-Text 인덱스를 적용했습니다. ngram_token_size=2와 같이 설정하면, 브랜드 이름을 2글자 단위로 토큰화하여 인덱싱하게 됩니다. 이를 통해 브랜드 이름의 중간에 위치하는 검색어에 대해서도 효과적으로 인덱스를 탐색할 수 있게 되어 검색 성능을 향상시킬 수 있습니다.
1. Docker 컨테이너 내 MySQL 설정 변경
ngram_token_size 설정
vim /home/ubuntu/my.cnf- 설정 파일: /home/ubuntu/my.cnf
- 적용 대상: master/slave 모두
[mysqld]
ngram_token_size=2적용 방법
docker restart master
docker restart slave- 컨테이너를 재시작하면 자동으로
my.cnf설정이 반영됩니다.
2. Full-Text 인덱스 생성 (w.JPA)
- JPA 표준 스펙에는 Full-Text 인덱스 생성 기능이 없으므로, SQL 스크립트를 통해 직접 생성합니다.
application.yml
jpa:
defer-datasource-initialization: true
sql:
init:
mode: always
encoding: UTF-8
schema-locations: classpath:index.sql| 설정 항목 | 설명 |
|---|---|
jpa.defer-datasource-initialization: true | hibernate 초기화 이후 SQL 초기화 스크립트(index.sql 등)가 실행되도록 변경합니다. |
sql.init.mode: always | 애플리케이션이 실행될 때 항상 SQL 초기화 스크립트(index.sql 등)를 실행하도록 설정합니다. |
sql.init.encoding: UTF-8 | SQL 스크립트 파일의 인코딩을 UTF-8로 설정하여 한글/특수문자 인코딩 문제를 방지합니다. |
sql.init.schema-locations: classpath:index.sql | 초기화에 사용할 SQL 스크립트의 경로를 지정합니다. resources 디렉토리 하위의 index.sql을 실행 대상으로 사용하게 됩니다. |
index.sql
/resources/index.sql생성- 이미 인덱스 있을 경우 방지하고자 조건부 인덱스 설정
-- Brand 인덱스 조건부 생성
SET @index_exists := (
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = DATABASE()
AND table_name = 'Brand'
AND index_name = 'idx_brand_name_fulltext'
);
SET @create_index_brand := IF(
@index_exists = 0,
'ALTER TABLE Brand ADD FULLTEXT INDEX idx_brand_name_fulltext (name) WITH PARSER ngram;',
'SELECT "Index already exists for Brand"'
);
PREPARE stmt_brand FROM @create_index_brand;
EXECUTE stmt_brand;
DEALLOCATE PREPARE stmt_brand;
-- Item 인덱스 조건부 생성
SET @index_exists := (
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = DATABASE()
AND table_name = 'Item'
AND index_name = 'idx_item_name_fulltext'
);
SET @create_index_item := IF(
@index_exists = 0,
'ALTER TABLE Item ADD FULLTEXT INDEX idx_item_name_fulltext (item_name);',
'SELECT "Index already exists for Item"'
);
PREPARE stmt_item FROM @create_index_item;
EXECUTE stmt_item;
DEALLOCATE PREPARE stmt_item;
3. QueryDSL 커스텀 함수 적용
- Hibernate + QueryDSL는 기본적으로 MySQL MATCH ... AGAINST 함수를 지원하지 않으므로, 사용자 정의 함수로 등록해야 합니다.
사용자 정의 함수 생성
public class NgramFullTextMatchFunctionContributor implements FunctionContributor {
@Override
public void contributeFunctions(FunctionContributions functionContributions) {
functionContributions.getFunctionRegistry().registerPattern(
"ngram_match",
"MATCH(?1) AGAINST(?2 IN NATURAL LANGUAGE MODE)",
functionContributions.getTypeConfiguration()
.getBasicTypeRegistry()
.resolve(StandardBasicTypes.DOUBLE)
);
}
}
public class FullTextMatchFunctionContributor implements FunctionContributor {
@Override
public void contributeFunctions(FunctionContributions functionContributions) {
functionContributions
.getFunctionRegistry()
.registerPattern("fulltext_match", "MATCH(?1) AGAINST(?2)",
functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve(BOOLEAN));
}
}
META-INF 등록
resources/META-INF/services/org.hibernate.boot.model.FunctionContributor파일에 등록
store.myproject.onlineshop.global.config.FullTextMatchFunctionContributor
store.myproject.onlineshop.global.config.NgramFullTextMatchFunctionContributorQueryDSL에 함수 사용
private BooleanExpression ngramFullTextSearchBrandName(String brandName) {
if (!StringUtils.hasText(brandName)) {
return null;
}
// MATCH(b.name) AGAINST ('keyword' IN NATURAL LANGUAGE MODE)
return numberTemplate(Double.class,
"function('ngram_match', {0}, {1})",
brand.name, brandName)
.gt(0);
}
private BooleanExpression fullTextSearchItemName(String itemName) {
if (!StringUtils.hasText(itemName)) {
return null;
}
return Expressions.booleanTemplate("function('fulltext_match', {0}, {1})",
item.itemName, itemName);
}인덱스 전/후
- 다른 조건에서는 큰 차이는 없지만 아이템 조건만으로 검색했을 경우는 대폭 감소되었습니다.
| 구분 | 인덱스 적용 전 평균 (ms) | 인덱스 적용 후 평균 (ms) |
|---|---|---|
| 아이템 조건 + 브랜드 조건 검색 | 762.5 | 731.3 |
| 아이템 이름만 검색 | 1,182.5 | 168.0 |
| 브랜드 이름만 검색 | 177.5 | 181.0 |
| 조건 없이 | 278.5 | 276.7 |
Reference
