저장 프로시저(Stored Procedure)

procedure의 사전적 의미는 '절차' 라는 뜻이다. 원문 그대로 해석하면 저장되어 있는 절차를 의미한다. 그래서 SP에서 대해서 쉽게 생가하면 이미 정해져 있는 절차에 따라서 SQL를 실행하는 것 이라고 생각하면 된다.

이번 글에서는 저장 프로시저의 특징이 무엇이 있고 이것을 왜 쓰는지 그리고 단점이 무엇인지 살펴보도록 하겠다.

 

사용예시

DELIMITER //

CREATE PROCEDURE GetCustomerOrders(IN customerId INT)
BEGIN
    SELECT * FROM Orders WHERE CustomerID = customerId;
END //

DELIMITER ;

 

Delimiter(구획문자)를 // 또는 $$로 변경을 해준 뒤 메서드 형태로 선언해서 사용한다.

 

IN, OUT 사용 예시

매개변수에 IN, OUT 키워드가 있다. IN은 우리가 흔히 생각하는 매개변수고 OUT은 출력값이라고 볼 수 있다.

DELIMITER //

CREATE PROCEDURE GetCustomerOrderCount(
    IN customerId INT,
    OUT orderCount INT
)
BEGIN
    -- 주문 수를 계산하여 orderCount에 할당
    SELECT COUNT(*) INTO orderCount
    FROM Orders
    WHERE CustomerID = customerId;
END //

DELIMITER ;

-----------------------------------------------------------------------

-- OUT 매개변수를 받을 변수를 선언
SET @customerOrderCount = 0;

-- 저장 프로시저 호출
CALL GetCustomerOrderCount(1, @customerOrderCount);

-- 결과 출력
SELECT @customerOrderCount AS OrderCount;

프로시저의 사용 방법은 프로그래밍을 공부한 사람이라면 크게 어렵지 않다. 그렇다면 이러한 프로시저를 사용하는 이유에 대해서 알아보자

 

프로시저의 장점

1. SQL Server의 성능 향상
여러개의 쿼리를 한번에 실행 할 수 있다.
컴파일된 프로시저는 캐시메모리에 저장되어 빠르게 호출 할 수 있다.

 

2. 유지보수 및 재활용 측면에서 좋다.

WAS가 Spring boot, Django, Node.js 등 어느 was여도 상관이 없다. SQL로 개발이 되기 때문에 프로시저만 바꾸면 된다.

 

3. 보안강화

테이블의 권한을 건들필요 없이 프로시저에게만 권한을 주는 방식으로 보안을 강화 할 수 있다.

 

4. 네트워크 부하를 줄일 수 있다.

@Transactional
    public BaseResponse<PostMemberSignupRes> signup(PostMemberSignupReq req, MultipartFile profileImage) {
        ....
	// 회원가입을 위해 DB에 Write
        Member member = memberRepository.save(
                Member.createMember(req.getMemberId(), passwordEncoder.encode(req.getPassword()),
                req.getMemberName(), req.getDepartment(), req.getPosition()));
        if (profileImage != null) {
        // 프로필 이미지가 있으면 프로필 이미지 경로를 DB에 write
            profileImageService.registerProfileImage(member, profileImage);
        }
        return BaseResponse.successRes("MEMBER_001", true, "회원이 등록되었습니다.", PostMemberSignupRes.buildSignUpRes(member));
    }

WAS에서 작성한 회원가입 메소드이다. 보게 되면 총 두개의 INSERT문이 있는 것을 확인 할 수 있다. 이렇게 WAS에서 비즈니스 로직을 작성하게 되면 SQL 하나당 DB에 데이터를 네트워크로 보내게 된다. 만일 회원가입 요청이 많아지만 WAS와 DB 사이에 네트워크 트래픽이 증가 할 수 있으나 이것을 프로시저로 구현하면 네트워크 트래픽을 줄일 수 있다.

 

프로시저의 단점

사실 어떻게 보면 프로시저라는 놈은 비즈니스 로직을 SQL로 짜는 듯한 느낌이든다. 자 그러면 WAS에서 비즈니스 로직을 짜고 SQL에서도 비즈니스 로직을 짠다라고 생각을 해보자. 바로 유지보수다 

 

1. 유지보수가 안좋을 수도 있다.

위에서는 WAS의 개발언어에 의존적이지 않아서 유지보수가 좋다라고 했다. 하지만 이것은 반쪽짜리의 장점이다. 만일 프로시저의 이름이 A에서 B로 바뀐다라고 하면 WAS에서 전부 CALL A ->  CALL B로 바꿔줘야 해서 불편하다.

그리고 비즈니스 로직을 WAS가 아닌 DB에서 작성하게 된다면 개발자 입장에서는 두군데를 봐야 할 것이고 또한 프로시저의 문법이 좀 오래되어서 직관적이지 않아 많은 불편함이 있을 것이다.

 

2. DB의 과부하에 의한 의존된 WAS 붕괴

위와 같이 3개의 WAS가 DB의 프로시저에 의존한다라고 생각을 해보자. 만일 SpringBoot 쪽에서 Req가 많아서 DB가 과부하가 된다라고 하면 Node.js와 Django 서비스도 영향을 미치게 된다. 그렇다고 was 서버 마냥 DB를 autoScale 하는것도 쉽지가 않다. 이 큰 DB서버를 언제 복사 붙여넣기를 할 것인가.....

 

느낌점

아직 실무를 잘 알지 못해서 실무에서 프로시저가 정확히 어떻게 쓰일지는 모르겠지만 내가 만약에 처음부터 프로젝트를 진행한다라고 하면 가급적이면 프로시저를 활용하지 않을 것 같다. 프로시저의 장점은 WAS에서 SQL 10번 보낼것을 1번에 처리 할 수 있다라는 것인데, 이 부분도 sql문의 우선순위 같은것을 잘 생각해서 비동기로 처리하면 되지 않을까 싶다.

그러나 로마에 가면 로마의 법을 따르라는 말이 있듯이 가장 중요한건 실무 환경이니 이번 기회에 프로시저를 잘 공부 해놔야 겠다.