프로젝트 erd
개인 맞춤형 선물 큐레이션 서비스 웹사이트였기 때문에 (카카오톡 선물하기와 비슷한 도메인) item 을 중심으로 keyword와 filtering을 할 수 있는 관계형 데이터베이스 구조입니다.
마케팅팀에서 넘겨주신 위와 같은 csv 파일을 받아서
@Profile("put-data")의 DataParser class를 만들어 알맞은 table column 에 insert 해줬습니다.
@ConditionalOnResource(resources = "data.csv")
@Profile("put-data")
@Slf4j
@RequiredArgsConstructor
@Component
public class DataParser {
private final ItemRepository itemRepository;
private final CategoryRepository categoryRepository;
private final ItemCategoryRepository itemCategoryRepository;
private final ItemUrlRepository itemUrlRepository;
private final ItemJobRepository itemJobRepository;
private final FilterRepository filterRepository;
private final ItemFilterRepository itemFilterRepository;
@Transactional(rollbackOn = RuntimeException.class)
public void read() throws RuntimeException {
try (InputStream inputStream = new ClassPathResource("data.csv").getInputStream();
CSVReader csvReader = new CSVReader(new InputStreamReader(inputStream))) {
List<DataRow> dataList = csvReader.readAll().stream()
.map(DataRow::of)
.toList();
for (DataRow row : dataList) {
// item table 저장된것 조회
int price;
int minAge;
int maxAge;
try {
price = Integer.parseInt(row.getPrice());
} catch (NumberFormatException e) {
price = 0;
}
try {
minAge = Integer.parseInt(row.getMinAge());
} catch (NumberFormatException e) {
minAge = 0;
}
try {
maxAge = Integer.parseInt(row.getMaxAge());
} catch (NumberFormatException e) {
maxAge = 100;
}
Item item = itemRepository.save(
Item.builder()
.id(row.getId())
.name(row.getItem())
.brand(row.getBrand())
.description(row.getDescription())
.price(price)
.minAge(minAge)
.maxAge(maxAge)
.build()
);
// category 있는지 조회 후 없으면 저장
Category category = categoryRepository.findByName(row.getCategory())
.orElseGet(() -> categoryRepository.save(
Category.builder()
.name(row.getCategory())
.build()
));
// 이미 매핑된 item_category의 경우 제외하고 저장
if (!itemCategoryRepository.existsByItemAndCategory(item, category)) {
itemCategoryRepository.save(
ItemCategory.builder()
.item(item)
.category(category)
.build()
);
}
//2차 카테고리 작업
for (String childCategoryName : row.getCategoryChild()) {
Category childCategory = categoryRepository.findByName(childCategoryName)
.orElseGet(() -> categoryRepository.save(
Category.builder()
.parent(category)
.name(childCategoryName)
.build()
));
if (!itemCategoryRepository.existsByItemAndCategory(item, childCategory)) {
itemCategoryRepository.save(
ItemCategory.builder()
.item(item)
.category(childCategory)
.build()
);
}
}
// 브랜드 url insert
if (StringUtils.isNotBlank(row.getBrandUrl()) && !row.getBrandUrl().equals("-")) {
itemUrlRepository.findBrandUrlByItem(item)
.ifPresentOrElse(itemUrl -> itemUrlRepository.save(
ItemUrl.builder()
.id(itemUrl.getId())
.item(item)
.url(row.getBrandUrl())
.platform(ItemUrlPlatforms.BRAND)
.build()
), () -> itemUrlRepository.save(
ItemUrl.builder()
.item(item)
.url(row.getBrandUrl())
.platform(ItemUrlPlatforms.BRAND)
.build()
));
}
// kakao url
if (StringUtils.isNotBlank(row.getKakaoUrl()) && !row.getKakaoUrl().equals("-")) {
itemUrlRepository.findKakaoUrlByItem(item)
.ifPresentOrElse(itemUrl -> itemUrlRepository.save(
ItemUrl.builder()
.id(itemUrl.getId())
.item(item)
.url(row.getKakaoUrl())
.platform(ItemUrlPlatforms.KAKAO)
.build()
), () -> itemUrlRepository.save(
ItemUrl.builder()
.item(item)
.url(row.getKakaoUrl())
.platform(ItemUrlPlatforms.KAKAO)
.build()
));
}
// coupang url
if (StringUtils.isNotBlank(row.getCoupangUrl()) && !row.getCoupangUrl().equals("-")) {
itemUrlRepository.findCoupangUrlByItem(item)
.ifPresentOrElse(itemUrl -> itemUrlRepository.save(
ItemUrl.builder()
.id(itemUrl.getId())
.item(item)
.url(row.getCoupangUrl())
.platform(ItemUrlPlatforms.COUPANG)
.build()
), () -> itemUrlRepository.save(
ItemUrl.builder()
.item(item)
.url(row.getCoupangUrl())
.platform(ItemUrlPlatforms.COUPANG)
.build()
));
}
// naver url
if (StringUtils.isNotBlank(row.getNaverUrl()) && !row.getNaverUrl().equals("-")) {
...
}
// 1차 직업군 insert
for (String jobName : row.getJob()) {
if (!itemJobRepository.existsByItemAndName(item, jobName)) {
itemJobRepository.save(
ItemJob.builder()
.item(item)
.name(jobName)
.step(ItemJob.Step.PRIMARY_STEP)
.build()
);
}
}
// 2차 직업군 insert
for (String jobChildName : row.getJobChild()) {
if (!itemJobRepository.existsByItemAndName(item, jobChildName)) {
itemJobRepository.save(
ItemJob.builder()
.item(item)
.name(jobChildName)
.step(ItemJob.Step.SECONDARY_STEP)
.build()
);
}
}
// situation filter id = 2
long situationId = 2L;
for (String situation : row.getSituation()) {
if (itemFilterRepository.findItemFilterByNameAndFilterId(situation, situationId, item.getId()).isEmpty()) {
itemFilterRepository.save(
ItemFilter.builder()
.item(item)
.name(situation)
.filter(filterRepository.findById(situationId).get())
.build()
);
}
}
long emotionId = 3L;
for (String emotion : row.getEmotion()) {
...
}
long genderId = 4L;
for (String gender : row.getGender()) {
...
}
long preferenceId = 5L;
for (String preference : row.getPreference()) {
...
}
long typeId = 6L;
for (String type : row.getType()) {
...
}
long relationId = 7L;
for (String relation : row.getRelation()) {
...
}
}
} catch (IOException | CsvException ex) {
throw new RuntimeException(ex);
}
}
@PostConstruct
public void test() {
try {
read();
} catch (Exception e) {
log.error(e.getMessage());
}
}
}
, (콤마) 중심으로 row를 파싱하는 DataParser class 입니다.
개선하고자 했던 이슈 상황 1
1. 관계형 db에 data insert 하는데 498.17 seconds → 약 8분 18 초가 걸리는 상황이었고,
2. Job Entity 는 C12열 (필터링을 걸기 위한 직업 1차 분류), C13열 (직업 2차 분류) 에 해당하는 계층형 구조를 가지고 있었습니다.
private Job parent;
private List<Job> children = new ArrayList<>();
하지만 같이 계층형 구조를 가지는 Category Entity 는
뷰티라는 parent 밑에
립스틱, 틴트, 파운데이션, ... 의 children 이 들어가서 parent_id 로 인덱스를 걸거나 하는 등의 용이성이 있었지만,
Job Entity 같은 경우 C12열 1차 분류가
전문직, 사무직, 판매/서비스직, 학생, 전업주부, 무직, 기타
C13열 2차 분류가
의사, 변호사, 검사, 변리사, 법무사, 노무사, 회계사, 세무사, 관세사, 교수, 마케터, 영업, 기획, 홍보, 인사, 경영지원, 총무, 호텔리어, 학원강사, 행사기획자, 게이머, 치위생사, 물리치료사, 상담사, 공무원, 심판, 여행사, 소방관, 경호원, 경비원, 경찰관, 중학생, 초등학생, 고등학생, 대학생, 대학원생
이런식으로 다대다관계를 가지고 있었기 때문에 ItemJob 에만 무수히 많은 row 가 insert 되고 있었습니다.
개선방안 1
Job children 에 콤마(,) 기준으로 split 하지 않고 의사, 변호사, 검사, 변리사, 법무사, 노무사, 회계사, 세무사, 관세사, 교수, 마케터, 영업, 기획, 홍보, 인사, 경영지원, 총무, 호텔리어, 학원강사, 행사기획자, 게이머, 치위생사, 물리치료사, 상담사, 공무원, 심판, 여행사, 소방관, 경호원, 경비원, 경찰관, 중학생, 초등학생, 고등학생, 대학생, 대학원생 통채로 저장할까 생각해봤지만
유지보수 측면에서
- 데이터의 정규화를 포기하는 것은 데이터 일관성을 유지하는 데 어려울 수 있고
- 아무래도 csv 파일을 파싱해서 insert 하는 거다 보니, insert 하려는 데이터가 이미 해당 item Id의 데이터에 존재하는 데이터인지 수정된 데이터인지 Repository에 exist 쿼리를 날려서 중복된 데이터를 체크하는 쿼리 효율성이 더 나쁠 수 있겠다는 생각으로
최종적으로 Job Entity는 삭제를 하되 ,
ItemJob Entity 안에 ItemJob.Step (Primary Step , Secondary Step) Enum 컬럼으로 관리하기로 하였습니다.
498.17ms → 336.417ms 로 insert 하는데 시간이 많이 줄었음을 확인하였습니다.
개선하고자 했던 이슈 상황 2
@RequiredArgsConstructor
public class ItemFilterRepositoryImpl implements ItemFilterRepositoryCustom{
private final QueryDslConfig queryDslConfig;
@Override
public Page<ItemSearchResponseDto> searchItem(ItemSearchCondition searchCondition, Member member, Pageable pageable) {
BooleanExpression isLiked = member != null ? new CaseBuilder().when(itemLike.member.eq(member)).then(true).otherwise(false) : Expressions.asBoolean(false);
List<ItemSearchResponseDto> content = queryDslConfig.jpaQueryFactory()
.selectDistinct(Projections.constructor(ItemSearchResponseDto.class,
item.id, item.name, item.brand, item.description, item.price, item.imgUrl, isLiked.as("isLiked"), item.views, item.modifiedDate))
.from(item)
.leftJoin(item.itemFilters, itemFilter)
.leftJoin(item.itemJobs, itemJob)
.leftJoin(item.itemCategories, itemCategory)
.leftJoin(itemFilter.filter, filter)
.leftJoin(itemJob.job, job)
.leftJoin(itemCategory.category, category)
.leftJoin(itemLike).on(item.id.eq(itemLike.item.id).and(member != null ? itemLike.member.id.eq(member.getId()) : null))
.leftJoin(itemLike.member, QMember.member)
.where(getSearchCondition(searchCondition))
.orderBy(getOrderSpecifier(searchCondition.getSort())) // 기본 정렬
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
JPAQuery<Long> total = queryDslConfig.jpaQueryFactory()
.select(item.id.countDistinct())
.from(item)
.leftJoin(item.itemFilters, itemFilter)
.leftJoin(item.itemJobs, itemJob)
.leftJoin(item.itemCategories, itemCategory)
.leftJoin(itemFilter.filter, filter)
.leftJoin(itemJob.job, job)
.leftJoin(itemCategory.category, category)
.leftJoin(itemLike).on(item.id.eq(itemLike.item.id).and(member != null ? itemLike.member.id.eq(member.getId()) : null))
.leftJoin(itemLike.member, QMember.member)
.where(getSearchCondition(searchCondition));
return PageableExecutionUtils.getPage(content, pageable, total::fetchFirst);
}
private OrderSpecifier<?> getOrderSpecifier(final String sort) {
if (StringUtils.isEmpty(sort)) {
return new OrderSpecifier<>(Order.ASC, item.id);
}
return switch (sort) {
case ItemSortConstants.MOST_RECOMMENDED -> new OrderSpecifier<>(Order.ASC, item.id);
case ItemSortConstants.MOST_POPULAR -> new OrderSpecifier<>(Order.DESC, item.views);
case ItemSortConstants.LATEST -> new OrderSpecifier<>(Order.DESC, item.modifiedDate);
case ItemSortConstants.MOST_EXPENSIVE -> new OrderSpecifier<>(Order.DESC, item.price);
case ItemSortConstants.LEAST_EXPENSIVE -> new OrderSpecifier<>(Order.ASC, item.price);
default -> throw new IllegalArgumentException("Sort error");
};
}
private BooleanBuilder getSearchCondition(ItemSearchCondition searchCondition) {
BooleanBuilder booleanBuilder = new BooleanBuilder();
if (isNotEmpty(searchCondition.getKeyword())) {
String keyword = searchCondition.getKeyword();
booleanBuilder = booleanBuilder.or(
item.name.contains(keyword)
.or(item.brand.contains(keyword))
.or(category.name.contains(keyword))
.or(itemCategory.category.name.contains(keyword))
.or(job.name.contains(keyword))
.or(itemJob.job.name.contains(keyword))
.or(filter.name.contains(keyword))
.or(itemFilter.name.contains(keyword))
);
}
// 필터링 조건 추가
if (searchCondition.getCategoryName() != null && !searchCondition.getCategoryName().isEmpty()) {
BooleanExpression categoryExpression = null;
for (String categoryName : searchCondition.getCategoryName()) {
if (categoryExpression == null) {
categoryExpression = category.name.contains(categoryName);
} else {
categoryExpression = categoryExpression.or(category.name.contains(categoryName)); }
}
booleanBuilder.and(categoryExpression);
}
if (isNotEmpty(searchCondition.getJobName())) {
booleanBuilder.and(job.name.containsIgnoreCase(searchCondition.getJobName()));
}
List<String> conditions = new ArrayList<>();
if (isNotEmpty(searchCondition.getSituationName())) {
conditions.add(searchCondition.getSituationName());
}
if (isNotEmpty(searchCondition.getGender())) {
conditions.add(searchCondition.getGender());
}
if (isNotEmpty(searchCondition.getEmotionName())) {
conditions.add(searchCondition.getEmotionName());
}
if (!conditions.isEmpty()) {
List<Long> list = queryDslConfig.jpaQueryFactory().select(itemFilter.item.id)
.from(itemFilter)
.where(itemFilter.name.in(conditions))
.groupBy(itemFilter.item.id)
.having(itemFilter.name.countDistinct().goe(conditions.size()))
.fetch();
booleanBuilder.and(item.id.in(list));
}
return booleanBuilder;
}
private BooleanExpression itemFilterNameEq(String itemFilterName) {
return hasText(itemFilterName) ? itemFilter.name.containsIgnoreCase(itemFilterName) : null;
}
private BooleanExpression filterIdEq(Long filterId) {
return hasText(String.valueOf(filterId)) ? filter.id.eq(filterId) : null;
}
private BooleanExpression ageGoe(Integer ageGoe) {
return ageGoe != null ? item.maxAge.goe(ageGoe) : null;
}
private BooleanExpression ageLoe(Integer ageLoe) {
return ageLoe != null ? item.minAge.loe(ageLoe) : null;
}
}
기존의 search Query 입니다.
explain select
distinct i1_0.id,
i1_0.name,
i1_0.brand,
i1_0.description,
i1_0.price,
i1_0.img_url,
case
when (m2_0.id=1) then cast(true as boolean)
else false
end,
i1_0.views,
i1_0.modified_date
from
item i1_0
left join
item_filter i2_0
on i1_0.id=i2_0.item_id
left join
item_job i3_0
on i1_0.id=i3_0.item_id
left join
item_category i4_0
on i1_0.id=i4_0.item_id
left join
category c1_0
on c1_0.id=i4_0.category_id
left join
item_like i5_0
on i1_0.id=i5_0.item_id
and i5_0.member_id=1
left join
member m2_0
on m2_0.id=i5_0.member_id
where
c1_0.name like '%뷰티%' escape '!'
and lower(i3_0.name) like '%사무직%' escape '!'
order by
i1_0.price desc offset 0 rows fetch first 20 rows only
해당 쿼리를 explain 문으로 날려봤을때
default 로 메인 쿼리에 Left Join 문이 너무 많아서 Nested Loop Join 이 많이 돌고 있어서
ItemSearchCondition에 null이 아닌 필드만 동적으로 조인하도록 join 문을 동적으로 바꿔보도록 하겠습니다.
개선방안 2
@RequiredArgsConstructor
public class ItemFilterRepositoryImpl implements ItemFilterRepositoryCustom{
private final QueryDslConfig queryDslConfig;
@Override
public Page<ItemSearchResponseDto> searchItem(ItemSearchCondition searchCondition, Member member, Pageable pageable) {
BooleanExpression isLiked = member != null ? new CaseBuilder().when(itemLike.member.eq(member)).then(true).otherwise(false) : Expressions.asBoolean(false);
BooleanBuilder booleanBuilder = new BooleanBuilder();
JPAQuery<ItemSearchResponseDto> mainSearchQuery = queryDslConfig.jpaQueryFactory()
.selectDistinct(Projections.constructor(ItemSearchResponseDto.class,
item.id, item.name, item.brand, item.description, item.price, item.imgUrl, isLiked.as("isLiked"), item.views, item.modifiedDate))
.from(item)
.leftJoin(itemLike).on(item.id.eq(itemLike.item.id).and(member != null ? itemLike.member.id.eq(member.getId()) : null))
.leftJoin(itemLike.member, QMember.member)
.where(booleanBuilder);
JPAQuery<Long> total = queryDslConfig.jpaQueryFactory()
.select(item.id.countDistinct())
.from(item)
.leftJoin(itemLike).on(item.id.eq(itemLike.item.id).and(member != null ? itemLike.member.id.eq(member.getId()) : null))
.leftJoin(itemLike.member, QMember.member)
.where(booleanBuilder);
위에서 보여드렸던 search Query 와는 다르게
mainSearchQuery 의 기본 Join 문을 itemLike 와의 관계만 가지도록 설정했습니다.
ItemSearchResponseDto 의 default column이 item Entity 필드와 ItemLike Entity 필드이기 때문입니다.
if (searchCondition.getCategoryName() != null && !searchCondition.getCategoryName().isEmpty()) {
mainSearchQuery = mainSearchQuery
.leftJoin(item.itemCategories, itemCategory)
.leftJoin(itemCategory.category, category)
.where(category.name.in(searchCondition.getCategoryName()));
}
if (isNotEmpty(searchCondition.getJobName())) {
mainSearchQuery = mainSearchQuery
.leftJoin(item.itemJobs, itemJob)
.where(itemJob.name.in(searchCondition.getJobName()));
}
if (isNotEmpty(searchCondition.getSituationName()) || isNotEmpty(searchCondition.getGender()) || isNotEmpty(searchCondition.getEmotionName())) {
List<String> conditions = new ArrayList<>();
if (isNotEmpty(searchCondition.getSituationName())) {
conditions.add(searchCondition.getSituationName());
}
if (isNotEmpty(searchCondition.getGender())) {
conditions.add(searchCondition.getGender());
}
if (isNotEmpty(searchCondition.getEmotionName())) {
conditions.add(searchCondition.getEmotionName());
}
List<Long> list = queryDslConfig.jpaQueryFactory()
.select(itemFilter.item.id)
.from(itemFilter)
.where(itemFilter.name.in(conditions))
.groupBy(itemFilter.item.id)
.having(itemFilter.name.countDistinct().goe(conditions.size()))
.fetch();
mainSearchQuery.where(item.id.in(list));
total.where(item.id.in(list));
}
if (isNotEmpty(searchCondition.getKeyword())) {
String keyword = searchCondition.getKeyword();
booleanBuilder.or(
item.name.contains(keyword)
.or(item.brand.contains(keyword))
);
// 키워드가 category에 속한 경우 조인 수행
if (itemCategory.category.name.contains(keyword) != null || category.name.contains(keyword) != null) {
mainSearchQuery = mainSearchQuery
.leftJoin(item.itemCategories, itemCategory)
.leftJoin(itemCategory.category, category);
booleanBuilder.or(itemCategory.category.name.contains(keyword))
.or(category.name.contains(keyword));
}
// 키워드가 itemJob에 속한 경우 조인 수행
if (itemJob.name.contains(keyword) != null) {
mainSearchQuery = mainSearchQuery
.leftJoin(item.itemJobs, itemJob);
booleanBuilder.or(itemJob.name.contains(keyword));
}
// 키워드가 itemFilter에 속한 경우 조인 수행
if (itemFilter.name.contains(keyword)!= null) {
mainSearchQuery = mainSearchQuery
.leftJoin(item.itemFilters, itemFilter)
.leftJoin(itemFilter.filter, filter);
booleanBuilder.or(itemFilter.name.contains(keyword));
}
}
List<ItemSearchResponseDto> content = mainSearchQuery
.orderBy(getOrderSpecifier(searchCondition.getSort())) // 기본 정렬
.where(booleanBuilder)
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
return PageableExecutionUtils.getPage(content, pageable, total::fetchFirst);
}
private OrderSpecifier<?> getOrderSpecifier(final String sort) {
if (StringUtils.isEmpty(sort)) {
return new OrderSpecifier<>(Order.ASC, item.id);
}
return switch (sort) {
case ItemSortConstants.MOST_RECOMMENDED -> new OrderSpecifier<>(Order.ASC, item.id);
case ItemSortConstants.MOST_POPULAR -> new OrderSpecifier<>(Order.DESC, item.views);
case ItemSortConstants.LATEST -> new OrderSpecifier<>(Order.DESC, item.modifiedDate);
case ItemSortConstants.MOST_EXPENSIVE -> new OrderSpecifier<>(Order.DESC, item.price);
case ItemSortConstants.LEAST_EXPENSIVE -> new OrderSpecifier<>(Order.ASC, item.price);
default -> throw new IllegalArgumentException("Sort error");
};
}
private BooleanExpression itemFilterNameEq(String itemFilterName) {
return hasText(itemFilterName) ? itemFilter.name.containsIgnoreCase(itemFilterName) : null;
}
private BooleanExpression filterIdEq(Long filterId) {
return hasText(String.valueOf(filterId)) ? filter.id.eq(filterId) : null;
}
private BooleanExpression ageGoe(Integer ageGoe) {
return ageGoe != null ? item.maxAge.goe(ageGoe) : null;
}
private BooleanExpression ageLoe(Integer ageLoe) {
return ageLoe != null ? item.minAge.loe(ageLoe) : null;
}
}
그리고 해당 if문으로 ItemSearchCondition 에 어떤 필드가 NotEmpty 인지 확인하여
해당 검색 조건에 필요한 다른 table 과의 join 을 실행하였습니다.
개선하고자 했던 이슈 상황 3
실행 계획을 보니 현재 left join 문들이 nested loop left join 과 hash join으로 이루어지고 있었는데
Nested Loop Join 고려 :
- Nested Loop Join은 두 개의 테이블 간의 조인을 위해 하나의 테이블을 순차적으로 스캔하고, 각 로우에 대해 다른 테이블을 조건에 맞게 스캔하는 방식입니다.
- 해당 쿼리에서는 item 테이블과 item_category 테이블 간의 조인이 item 테이블이 기준 테이블이 되며, item_category 테이블은 각 로우별로 스캔되고 있는 Nested Loop Join으로 이루어지고 있습니다.
- Nested Loop Join은 기준 테이블에 조인 조건에 사용되는 컬럼에 인덱스를 추가하는 것이 중요한데 Item 테이블의 id 컬럼은 PK라 이미 인덱스가 생성되어 있어서 넘어가면 될 것 같습니다.
- Hash Join은 조인 조건에 맞는 로우들을 해시 함수를 사용하여 해시 테이블에 저장한 후, 다른 테이블의 값과 비교하는 방식입니다. Item_like 테이블이 해시 테이블이 되며, Member 테이블은 해시 조인의 오른쪽 입력으로 사용되고 있습니다.
- Item_like 테이블에서 member_id = 1인 행들을 필터링하기 위해 Seq Scan이 사용되고 있습니다.
- Item_like 테이블과 category 테이블에서 Seq Scan 발생 -> 인덱스가 적절하게 사용되지 않고 있다는 것을 의미
- Item_like 테이블에서 (item_id, member_id) 복합 인덱스를 생성해서 Item_like 테이블의 조인 조건에서 사용되는 필드들에 대한 인덱스를 추가하여 성능 개선을 해보겠습니다.
개선방안 3
< Item_like 테이블에서의 member_id와 item_id를 함께 사용하는 복합 인덱스 생성 >
category 테이블에서의 인덱스
name 필드에 인덱스를 생성하여 WHERE 절에서 사용되는 필드에 대한 검색을 효율적으로 수행합니다.
category 테이블에서 name 필드 즉 WHERE 절에 사용된 필드에 대한 인덱스를 추가하니 Index Scan을 사용하지만,
keyword와 같이 일부 문자열만 포함해도 결과에 반영되어야 할 경우에 대한 개선방안도 고려해보았습니다.
개선방안 4
< full-text search 와 pg_bigm 모듈 사용 고려 >
ItemSearchCondition 에서 받는 requestParam 들은 위와 같이 지정된 itemFilter 명이 있어서 like 대신 equals = 로 판단하면 되기 때문에 category name 에 index를 거는게 효율적라는 판단이 들었지만,
keyword는 제품명의 일부만 쳐도 검색 결과에 포함되어야 했기 때문에
한국어를 지원해주는 mecab 을 활용해서 test를 진행해보다가 2-gram 기반의 pg_bigm 모듈을 발견하였습니다.
mecab-ko 는 주어진 문장을 형태소 단위로 분할하고 각 형태소의 품사를 태깅하는 형태소 분석 오픈 소스 형태소 분석기입니다.
pg_bigm
pg_bigm 모듈을 활용하면 빠른 전체 검색 텍스트 검색을 위해 2-gram 인덱스를 만들 수 있습니다. 여기서 2-gram 인덱스는 문자열의 연속된 2개 문자마다 인덱스 항목을 만드는 것을 의미합니다(bigm이라는 모듈의 이름 또한 bi-gram의 약자입니다).
pg_bigm 모듈은 PostgreSQL 13.2 이상에서 사용할 수 있다고 하는데, 저는 AWS RDS를 활용한 PostgreSQL 15 버전이어서 사용 가능하였습니다.
3-gram(trigram) 모델을 이용한 전체 텍스트 검색 기능을 제공하는 pg_trgm과 이를 기반으로 하는 pg_bigm은 다음과 같은 차이점이 있습니다.
| 기능 | pg_trgm | pg_bigm |
| 전체 텍스트 검색에 필요한 최소 단어수 | 3단어 | 2단어 |
| 사용 가능한 인덱스 | GIN, GiST | GIN |
| 사용 가능한 검색 연산자 | like, ilike | like |
| 한국어 지원 여부 | X | O |
| 1-2자 키워드로 전체 텍스트 검색시 성능 | 느림 | 빠름 |
| 유사성 검색 | O | O (1.1버전부터) |
| 최대 인덱스 컬럼 사이즈 | ~228MB | ~102MB |
출처 : https://overcome-the-limits.tistory.com/856
기존의 pg_trgm은 한국어가 지원이 안되었다고 합니다.
그렇기 때문에 한글 텍스트 컬럼의 전체 텍스트 검색 성능 향상을 위해 pg_bigm 사용했습니다. 하지만 pg_bigm의 가장 큰 단점은 알파벳의 대/소문자를 동일하게 보지 못합니다. 즉, ilike로 대/소문자를 모두 묶어서 검색하던 방식으로는 pg_bigm을 사용할 수는 없습니다.
B-tree 인덱스와 Gin-Index
GIN Index는 Full text 검색 속도를 높이는 데 사용 가능한 index입니다. GIN(Generic Inverted Index) index는 B-tree index와는 다른 구조를 가지고 있습니다.
PostgreSQL의 경우 12 버전 기준으로 B-tree, Hash, GiST, SP-Gist, GIN, BRIN 타입 인덱스를 지원하고 있습니다.
위 인덱스 타입들 중 가장 일반적으로 사용되는 타입은 B-tree 이며, PostgreSQL는 특별히 타입을 지정하지 않고 인덱스를 생성하는 경우 B-tree 인덱스를 생성합니다.
B-tree 인덱스는 데이터의 삽입과 삭제에 대해 항상 균형 트리를 유지하는 자료 구조를 이용해, 순차 비교에 대해 최악의 경우에도 탐색의 시간복잡도가 O(log N)이 되도록 합니다.
한편, B-tree 인덱스는 인덱스를 적용하는 컬럼의 값을 변형하지 않고 원래의 값을 이용합니다.
따라서 = 연산과 같은 값 자체에 대한 탐색 (single value search)에는 효과적이지만 %LIKE% 연산과 같이 검색어가 데이터 값에 포함 되었는지 여부를 확인하는 것에는 적용되기 어렵습니다.
반면, GIN (Generalized Inverted Index) 인덱스는 인덱스를 적용하는 컬럼의 값을 일정한 규칙에 따라 쪼개고(split), 이렇게 쪼갠 요소들을 사용합니다.
이에 따라 포함 여부를 확인하는 경우 보다 효과적으로 동작할 수 있습니다.
또한 값을 쪼개는 방법 및 값의 타입에 따라 적합한 여러가지 operator class들이 있는데, PostgreSQL의 경우 12 버전 기준으로 array_ops, tsvector_ops, jsonb_ops, jsonb_path_ops 의 operator class를 built-in으로 지원하고 있으며, 그 외에도 확장 모듈(extension)을 통해 pg_trgm을 사용할 수 있습니다.
GIN index는 역 인덱스(inverted index) 구조를 사용하여, 키워드(keyword)나 토큰(token)과 같은 작은 단위의 단어(word)를 인덱싱 합니다. GIN은 항상 항목 값이 아닌 키를 저장하고 검색합니다. 이러한 작은 단위의 단어는 텍스트 검색을 위해 자주 사용되는데, 이를 효과적으로 인덱싱 하면 텍스트 검색의 속도를 높일 수 있습니다.
B-tree index는 데이터를 정렬된 상태로 저장해서, 키-값 쌍을 효과적으로 탐색하는 데 사용되는 반면, GIN index는 역 인덱스 구조를 사용하여 단어를 인덱싱 하고, 이를 사용하여 데이터를 조회합니다. GIN Index는 B-tree index보다 키워드 검색과 같은 텍스트 검색에서 훨씬 효과적이며 대량의 텍스트 데이터를 처리할 때 성능 향상을 제공합니다.
pg_bigm (gin) 인덱스가 잘 생성되었음을 확인했음에도,
해당 실행계획에서 옵티마이저가 pg_bigm (gin) 인덱스를 태우지 않는걸 보면
아마 Item 테이블에 충분한 데이터 row가 없어서 gin index를 활용하는 것이 효율적이지 않는 것 같습니다.
INSERT INTO item (id, created_date, modified_date, brand, description, img_url, max_age, min_age, name, price, views)
SELECT
10000 + generate_series AS id,
CURRENT_DATE - (random() * 365 * 10)::int AS created_date,
CURRENT_DATE - (random() * 365 * 10)::int AS modified_date,
CASE
WHEN random() < 0.1 THEN '브랜드' || (random() * 100)::int
ELSE 'Brand' || (random() * 100)::int
END AS brand,
CASE
WHEN random() < 0.1 THEN '한국어 더미 설명 ' || generate_series
ELSE 'Description' || (random() * 100)::int
END AS description,
'http://example.com/img' || (random() * 100)::int AS img_url,
(random() * 10)::int AS max_age,
(random() * 10)::int AS min_age,
CASE
WHEN random() < 0.1 THEN '상품명' || (random() * 100)::int
ELSE 'Item' || (random() * 100)::int
END AS name,
(random() * 1000)::int AS price,
(random() * 10000)::int AS views
FROM
generate_series(1, 10000);
따라서 해당 sql문으로 10000개의 더미 데이터를 insert 해주었습니다.
이제 해당 실행계획에서 < Bitmap Index Scan > 이 적용되는 것을 볼 수 있습니다.
Execution Time 은 0.054 ms 입니다.
이제 pg_bigm (gin) 인덱스를 drop 하고 < 기존 일반 인덱스만 타는 경우 > 의
Exectution TIme 은 1.660 ms 입니다.
1.660ms -> 0.054 ms 약 96.74% 향상되었음을 볼 수 있습니다.
아마 몇백만건의 데이터가 있었다면 더 유의미한 차이를 볼 수 있지 않을까 생각이 듭니다.
아 참고로,
B Trees and B+Trees How They are Useful In Databases 라는 유튜브 영상인데
데이터, 인덱스 등이 디스크에 어떻게 적재되는지 하나하나 예시를 통해 자세히 보여주셔서 B Trees 개념 체화에 도움이 되었던 영상이라 첨부해봅니다 :)
쿼리를 개선해보며 데이터베이스 관련 기초적인 지식의 중요성을 다시 공부해보며 깨달을 수 있는 시간이었습니다.
수정해야 할 부분이나 더 좋은 개선방안이 있다면 언제든지 댓글로 알려주시면 감사하겠습니다 :)
글을 이만 마치겠습니다.
감사합니다 !
reference
PostgreSQL GIN 인덱스를 통한 LIKE 검색 성능 개선
안녕하세요, 뷰노 SW 개발팀의 김병묵입니다.
medium.com
[Project] 프로젝트 삽질기50 (feat PostgreSQL 검색)
들어가며 NestJS와 TypeORM을 활용하여 프로덕트를 만들고 있습니다. 특정 유저를 멘션 하는 기능을 만들기 위해, 특정 유저의 닉네임을 검색하는 시스템을 구축해야 했습니다. 검색 시스템을 구축
overcome-the-limits.tistory.com
'project' 카테고리의 다른 글
| [spring] Websocket Stomp 두번의 프로젝트 회고 (0) | 2023.09.15 |
|---|---|
| 중고거래 플랫폼 (당근마켓 클론) 개인 프로젝트 20일 챌린지 [0] (1) | 2023.01.31 |
| [main-project] 회고 (0) | 2022.12.13 |
| [pre-project] 1일차 (0) | 2022.10.24 |
