[이슈 #8] 1:N:M 관계 INSERT 시 N + M번의 쿼리 발생을 리팩토링하기

개요

 

DelFood의 주문 로직을 작성하던 도중 발생하는 1 : N : M의 쿼리 이슈를 해결해 보려고 합니다.

 

주문 진행 로직

 

1. 고객이 메뉴를 선택합니다.
2. 해당 메뉴에 대한 옵션을 선택합니다.
3. 여러 메뉴를 선택한 고객이 주문을 진행합니다

 

이 과정에서 주문 - 주문 메뉴 - 주문 메뉴 옵션은 1 : N : M의 관계를 가집니다.

이 관계를 다중 for문을 돌며 쿼리를 호출하게 된다면 많은 DB요청이 발생하게 됩니다. 처음 만든 다중 for문을 단 3번의  insert문을 호출하는 것으로 변경해보겠습니다.

 

 

 

개선 방향

1. 데이터베이스 스키마상 문제가 있는 테이블을 수정할 것입니다.

관계가 있는 테이블 입력 시 (주문메뉴 - 주문메뉴 옵션) 해당 부모 데이터의 PK가 필요합니다. 하지만 변경 전 DB 테이블은 입력하기 전까지는 PK를 알 수 없는 auto-increment PK입니다.

그렇기 때문에 PK를 직접 주입해 줄 수 있도록 변경할 것입니다

 

2. 반복적인 DB 콜을 제거할 것 입니다.

사용자가 적을 때는 반복문을 돌며 DB를 호출하여도 큰 문제가 없습니다. 하지만 사용자가 많아진다면 DB를 한번 콜할때의 리소스를 아끼는 것이 중요해집니다. 그렇기 때문에 최소한의 DB콜을 유지하기 위해 insert는 각 테이블당 한번씩 총 3번만 진행할 예정입니다.

 

 

개선이 필요한 코드

 

위 로직을 코드로 반영한 결과는 다음과 같습니다.

'주문 메뉴 옵션'을 insert 할 때 <foreach> 태그를 사용하여 약간의 개선을 하였지만 아직 문제가 많은 로직입니다. insert시마다 pk를 받아와야 하기 때문입니다.

이로 인해 메뉴 N개의 Insert 시 '주문 PK'와 N개의 '주문 메뉴 PK'를 조회 하기 위한 N+1 개의 SELECT 쿼리가 발생하였습니다.

 

참고 : item은 '주문 메뉴'와 '주문 메뉴 옵션들'을 모두 가지고 있는 데이터를 통칭하는 용어입니다.

/**
   * 주문 테이블에 insert를 진행한다.
   * 주문 메뉴, 주문 옵션이 추가된다.
   * 
   * @param memberId 고객 아이디
   * @param items 주문할 아이템들
   * @return
   */
  @Transactional
  private Long preOrder(String memberId, List<OrderItemDTO> items) {
    OrderDTO order = new OrderDTO();
    MemberDTO memberInfo = memberService.getMemberInfo(memberId);
    
    // 주문 기본 정보 세팅
    order.setMemberId(memberId);
    order.setAddressCode(memberInfo.getAddressCode());
    order.setAddressDetail(memberInfo.getAddressDetail());
    
    // insert 진행시 mybatis에서 selectKey로 PK를 받아와 객체의 id필드에 세팅해주도록 설정해 놓았음
    orderMapper.addOrder(order);
    Long orderId = order.getId();
    
    // 문제가 되는 for문 시작
    for (OrderItemDTO item : items) {
      // 주문 테이블의 pk 세팅
      item.setOrderId(orderId);
      // insert 후 selectKey를 통해 pk를 가져온다
      orderMapper.addOrderItem(item);
      Long orderItemId = item.getId();
      log.debug("order item id : {}", orderItemId);
      
      // 주문 메뉴 옵션을 insert한다
      orderMapper.addOrderItemOptions(item.getOptions(), orderItemId);
    }
    return order.getId();
  }

    <insert id="addOrderItem" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO ORDERS_ITEM
        (menu_id, order_id, count)
        VALUES
        (#{menuId}, #{orderId}, #{count})
    </insert>    

    <insert id="addOrderItemOptions">
        INSERT INTO ORDERS_ITEM_OPTION
        (option_id, order_item_id)
        VALUES
        <foreach collection="options" item="item" separator=",">
            (#{item.optionId}, #{orderItemId})
        </foreach>
    </insert>

 

개선이 필요한 DB Key

 

DelFood DB 스키마는 사장님, 고객의 아이디

를 제외하고는 모두 기본키가 auto-increment 되는 구조였습니다. 

 

해당 테이블에 데이터를 입력하기 위해서는 다음과 같은 로직이 필요합니다. auto-increment PK의 단점 중 하나가 insert 하기 전까지는 id를 알 수 없다는 것이기 때문입니다.

 

1. '주문' 테이블에 주문 정보를 입력한 후 해당 데이터의 PK를 조회한다. 2. '주문 메뉴' 데이터에 '주문' 테이블 정보를 입력한다. 3. '주문 메뉴'를 하나씩 insert 하여 PK를 받아온다. 4. '주문 메뉴 옵션' 데이터에 주문 메뉴 PK를 입력한다 5. '주문 메뉴 옵션'데이터를 insert 한다.

 

 

DB 스키마 리팩터링

 

가장 문제가 되는 것은 PK를 INSERT 하기 전까지는 알 수 없다는 문제였습니다. 그렇기 때문에 해당 스키마의 PK는 auto-increment를 진행하지 않고 직접 만들기로 결정하였습니다. 

현재 시간과 회원의 id 등을 조합하여 PK를 만든다면 밀리초 단위로 같은 회원이 완전히 똑같은 요청을 하지 않는다면 같은 PK가 발생하지 않을 것입니다.  만약 같은 PK가 만들어진다면 그것은 정상적인 요청이 아니니 예외처리를 진행해도 괜찮을 것입니다.

 

N, M개의 쿼리가 발생하는 '주문 메뉴', '주문 메뉴 옵션' 스키마의 PK를 'BIGINT' -> 'VARCHAR'로 변경하였습니다.

'주문'테이블의 PK는 단 한 번만 select를 하면 됨으로 그대로 두기로 하였습니다.

 

 

 

 

코드 리팩터링

 

PK가 자동으로 만들어지지 않으므로 PK를 전문적으로 만드는 유틸 클래스를 우선 제작하였습니다.

public class OrderUtil {

  private OrderUtil() {}

  public static String generateOrderItemKey(String memberId, long idx) {
    return memberId + ":" + idx + ":" + System.currentTimeMillis();
  }

  public static String generateOrderItemOptionKey(String memberId, long itemIdx, long optionIdx) {
    return memberId + ":" + itemIdx + ":" + optionIdx + ":" + System.currentTimeMillis();
  }
}

 

그리고 문제가 되었던 for문 insert는 테이블당 한 번씩, 총 3번의 insert만 진행되도록 변경되었습니다.

 /**
   * 주문 테이블에 insert를 진행한다.
   * 주문 메뉴, 주문 옵션이 추가된다.
   * 
   * @param memberId 고객 아이디
   * @param items 주문할 아이템들
   * @return
   */
  @Transactional
  private Long preOrder(String memberId, List<OrderItemDTO> items) {
    OrderDTO order = new OrderDTO();
    MemberDTO memberInfo = memberService.getMemberInfo(memberId);
    order.setMemberId(memberId);
    order.setAddressCode(memberInfo.getAddressCode());
    order.setAddressDetail(memberInfo.getAddressDetail());
    
    // '주문' 데이터 INSERT
    orderMapper.addOrder(order);
    Long orderId = order.getId();
    
    // '주문 메뉴 옵션'은 모두 여기에 담아서 한번에 벌크 INSERT를 진행한다
    List<OrderItemOptionDTO> options = new ArrayList<OrderItemOptionDTO>();

	// 데이터 가공
    for (int i = 0; i < items.size(); i++) {
      OrderItemDTO item = items.get(i);
      item.setId(OrderUtil.generateOrderItemKey(memberId, i));
      item.setOrderId(orderId);
      for (int j = 0; j < item.getOptions().size(); j++) {
        OrderItemOptionDTO option = item.getOptions().get(j);
        option.setId(OrderUtil.generateOrderItemOptionKey(memberId, i, j));
        option.setOrderItemId(item.getId());
        options.add(option);
      }
    }
    
    // '주문 메뉴' 벌크 INSERT
    orderMapper.addOrderItems(items);
    
    // '주문 메뉴 옵션' 벌크 INSERT
    orderMapper.addOrderItemOptions(options);

    return order.getId();
  }

 

 

 

Mybatis 코드는 모두 벌크 INSERT를 진행하도록 바꾸었습니다.

 

    <insert id="addOrderItems">
        INSERT INTO ORDERS_ITEM
        (id, menu_id, order_id, count)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.id}, #{item.menuId}, #{item.orderId}, #{item.count})
        </foreach>
    </insert>

    <insert id="addOrderItemOptions">
        INSERT INTO ORDERS_ITEM_OPTION
        (id, option_id, order_item_id)
        VALUES
        <foreach collection="list" item="option" separator=",">
            (#{option.id}, #{option.optionId}, #{option.orderItemId})
        </foreach>
    </insert>

 

 

결과

 

이로써 INSERT 진행 시 '주문 메뉴'의 개수만큼 발생하는 SEELCT 문과 '주문 메뉴' N개와 '주문 메뉴 옵션' M개로 인해 발생하는 N + M번의 INSERT 문이 단 3번의 INSERT 쿼리로 진행할 수 있도록 변경되었습니다.

 

DB 서버에 쿼리를 최대한 적게 날리는 방향으로 delfood를 설계하도록 앞으로도 계속 리팩터링이 진행될 예정입니다.