[K-Traveler] 스토어드 프로시저를 프로젝트에 적용해보자💡

개요

스토어드 프로시저란?

일련의 쿼리를 모아 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

 

 

프로시저 vs  함수

프로시저 

- 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이며, 일련의 작업을 정리한 절차

- 보통 단독으로 실행해야 할 작업을 위임받았을 때 사용

 

함수

- 하나의 특별한 목적의 작업을 수행하기 위해 독립적으로 설계된 코드의 집합.

- 즉, 함수가 여러 작업을 위한 기능이라면 프로시저는 작업을 정리한 절차.

- 보통 로직을 도와주는 역할이며, 간단한 계산, 수치 등을 나타낼 때 사용.

 

 

프로시저의 장단점

장점

1. 하나의 요청으로 여러 SQL문을 실행할 수 있다. (네트워크 부하를 줄일 수 있다.)

2. 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어든다.

3. 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조 무결성 유지가 가능하게 된다.

 

단점

1. 코드 자산으로서의 재사용성이 나쁘다.(이 부분 때문에 실무에서는 거의 안 쓰는 편이다.)

2. 업무의 사양 변경 시 외부 응용 프로그램과 함께 프로시저의 정의를 변경할 필요가 있다.

 

 

언제 사용해야 할까?

프로시저는 테이블에서 데이터를 추출해 조작하고, 그 결과를 다른 테이블에 다시 저장하거나 갱신하는 일련의 처리를 할 때 주로 사용한다.

 

>> K-Travler 프로젝트의 여행 계획 등록 작업에 적용하면 딱이다!!!!

 

---

 

기존 코드

프로시저를 사용하지 않은 여행 계획 등록 동작은, 총 3+n번의 쿼리 호출이 필요했다.

①여행 계획 생성, ② 계획 썸네일 생성, ③ 여행 계획에서 날짜별로 관광할 여행지 등록

    @Override
    @Transactional
    public void createPlan(PlanCreationDto dto) throws SQLException {
        Map<String, Object> plan = new HashMap<>();
        addNewPlan(dto, plan);
        String planId = String.valueOf(plan.get("id"));
        addPlanThumbnail(dto, planId);
        for (PlanDateAttractionDto apd : dto.getPlanDateAttractionDtos()) {
            LocalDateTime date = apd.getDate();
            for (String contentId : apd.getContentId()) {
                addPlanAttraction(planId, date, contentId);
            }
        }
    }

여행 계획을 생성하고 나온 id 값으로 여행 계획의 썸네일, 여행계획-관광지정보 관계를 형성하는 구조다.

날짜별로 여행 계획을 생성하고 있기 때문에, 여행 일정이 길어지면 그만큼 쿼리를 여러번 호출해야 하는 문제가 있었다.

 

변환 (프로시저 적용)

날짜별로 방문하려고 하는 관광지를 content_id로 저장하는데, 이 부분의 구조가 약간 복잡하다.

plan_and_attraction 테이블

 

여행 계획이 있고, 그 안에서 날짜별로 방문할 관광지를 저장하는 방식이다.

@Getter
@NoArgsConstructor
@ToString
@Setter
public class PlanCreationDto {

    private String memberId;
    private String title;
    private PlanThumbnailDto planThumbnailDto;
    private List<PlanDateAttractionDto> planDateAttractionDtos; //날짜별로 방문할 관광지

    PlanCreationDto(String memberId, String title, List<PlanDateAttractionDto> planDateAttractionDtos) {
        this.memberId = memberId;
        this.title = title;
        this.planDateAttractionDtos = planDateAttractionDtos;
    }
}

@Getter
@NoArgsConstructor
@ToString
public class PlanDateAttractionDto {
	private LocalDateTime date;
	private String[] contentId;
}

dto 안에 dto 가 리스트 형태로 저장되어 있기 때문에,

    @Override
    @Transactional
    public void createPlanV2(PlanCreationDto dto) throws JsonProcessingException {
        Map<String, Object> params = createParamsMap(dto);
        params.put("attractions", convertAttractionsToJson(dto));
        planMapper.createPlanV2(params);
    }

attractions 부분만 String으로 변환해서 sql에서 파싱 해서 사용할 수 있게 넘겼다.

DELIMITER //

-- create_plan 저장 프로시저 정의.
CREATE PROCEDURE create_plan (
  -- 입력 파라미터 정의.
  IN p_memberId VARCHAR(255), -- 회원 ID
  IN p_title VARCHAR(255), -- 여행 계획 제목
  IN p_thumbnailFolder VARCHAR(45), -- 썸네일 저장 폴더
  IN p_thumbnailOrigin VARCHAR(50), -- 썸네일 원본 파일명
  IN p_thumbnailSave VARCHAR(50), -- 썸네일 저장 파일명
  IN p_attractions JSON -- 방문할 관광지 JSON 배열
)
BEGIN
  -- 로컬 변수 선언.
  DECLARE planId INT; -- 여행 계획 ID
  DECLARE idx INT DEFAULT 0; -- JSON 배열 인덱스
  DECLARE attraction JSON; -- 단일 관광지 JSON 객체
  DECLARE date TIMESTAMP; -- 방문 날짜
  DECLARE contentId INT; -- 관광지 ID
  DECLARE contentIdx INT; -- 관광지 ID 배열 인덱스
  DECLARE contentCount INT; -- 관광지 ID 배열 길이
  DECLARE singleContentId INT; -- 단일 관광지 ID

  -- 여행 계획을 추가한다.
  INSERT INTO plan (member_id, title) VALUES (p_memberId, p_title);
  -- 방금 추가된 여행 계획의 ID를 가져오기.
  SET planId = LAST_INSERT_ID();

  -- 썸네일 정보를 추가.
  IF p_thumbnailSave IS NOT NULL THEN
    INSERT INTO plan_file (save_folder, origin_file, save_file, plan_id)
    VALUES (p_thumbnailFolder, p_thumbnailOrigin, p_thumbnailSave, planId);
  END IF;

  -- JSON 배열에서 관광지 개수를 가져옴.
  SET contentCount = JSON_LENGTH(p_attractions);

  -- JSON 배열을 순회 (문자열로 반환되어 받음).
  WHILE idx < contentCount DO
    -- 현재 인덱스의 관광지 객체를 가져온다.
    SET attraction = JSON_EXTRACT(p_attractions, CONCAT('$[', idx, ']'));
    -- 관광지 객체에서 날짜를 추출한다.
    SET date = JSON_UNQUOTE(JSON_EXTRACT(attraction, '$.date'));
    -- 관광지 ID 배열의 길이를 가져온다.
    SET contentIdx = 0;
    SET contentCount = JSON_LENGTH(JSON_EXTRACT(attraction, '$.contentId'));

    -- 관광지 ID 배열을 순회한다.
    WHILE contentIdx < contentCount DO
      -- 관광지 ID 배열에서 단일 관광지 ID를 가져온다.
      SET singleContentId = JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(attraction, '$.contentId'), CONCAT('$[', contentIdx, ']')));
      -- 관광지 정보를 plan_and_attraction 테이블에 추가한다.
      INSERT INTO plan_and_attraction (plan_id, date, content_id) VALUES (planId, date, singleContentId);
      -- 다음 인덱스로 이동.
      SET contentIdx = contentIdx + 1;
    END WHILE;

    -- 다음 인덱스로 이동.
    SET idx = idx + 1;
  END WHILE;

END //

DELIMITER ;

3단계를 한 번에 수행하는 프로시저다.

3단계를 한 번에 수행하는 프로시저를 생성했다.

단 한번의 쿼리 호출로 여행 계획 생성이 끝난다.

---

결과

🟢 여행 일자 하루

여행 일자를 하루로 해서 테스트를 해봤다.

프로시저를 적용한 것과 하지 않은 것, 약간의 차이 밖에 없었다.

 

🟢 여행 일자 10일

위와 같이 보내면 날짜에 따른 여행지 등록이 총 10회인 것이다.

여행 계획이 하루일 때와 달리 상당히 큰 차이가 있었다.

 

날짜를 변경해가며 테스트를 이어나갔다..

프로시저를 사용했을 때는 여행 일자의 변화와 관계없이, 10초 후반 ~ 20초 초반을 유지했다. 

프로시저를 사용하지 않았을 때는 여행 일자가 많아지면 API 호출 시간은 계속 증가했다.

 

---

회고

프로시저의 단점을 확인해 보면, "유지보수가 힘들다"는 점 등이 있다.

프로시저를 작성하고 나서 보니, 여행 계획 등록 전체를 묶어서 처리해 버리니 프로시저가 길어지고, 어디서 어떻게 작업이 이뤄지는 건지 헷갈렸다.

 

①여행 계획 생성, ② 계획 썸네일 생성, ③ 여행 계획에서 날짜별로 관광할 여행지 등록

과 같은 3단계에서, 쿼리 요청이 많아질 수 있는 부분은 날짜별 방문할 관광지를 등록하는 부분이기 때문에, 이 부분만 프로시저로 처리하고, 1,2 단계는 따로 쿼리 요청을 해도 괜찮았을 것 같다.

그 편이 유지보수+성능+테스트 관점에서 가장 이점이 많았을 것이라는 생각이다.

 

요즘은 JPA를 많이 사용하기 때문에, 프로시저가 줄어드는 추세라고는 하지만..

MyBatis를 사용한 이번 프로젝트에서는 나름 적용해볼만한 가치가 있었던 것 같다.

 

 

🤨 프로시저를 사용하지 않는 이유 (간단하게)

1. DB 확장이 어렵다.

2. 디버깅이 어렵다. 

3. 요즘은 객체지향 방법론이 대세 (Spring의 JPA)

4. 클라우드 시스템의 대중화 (프로시저는 특정 기술에 의존적이기 때문에, 클라우드의 장점인 '플랫폼 변환을 쉽게 할 수 있다' 와 결이 맞지 않음. 시대의 역행이다.)

 

 

참고 블로그

- 프로시저란?

- 프로시저 vs 함수

- 프로시저에 업무 로직(비지니스로직)을 넣으면 안 되는 이유

- 프로시저를 사용하지 않는 이