저장 프로시저

이상훈·2023년 5월 13일

Stored Procedure?

여러 SQL 문을 하나의 SQL 문처럼 정리하여 CALL ✕ ✕라는 명령으로 실행할 수 있게 만든 것을 저장 프로시저(Stored Procedure)라고 합니다.
사용자가 정의한 프로시저
RDBMS에 저장되고 사용되는 프로시저
구체적인 하나의 task를 수행한다.

Stored function 예제 1

두 정수의 곱셉 결과를 가져오는 프로시저를 작성하자

delimiter $$ : 저장 프로시저 시작
$ $ : 끝표시
IN : 인풋 파라미터 (안쓰면 IN으로 디폴트)
OUT : 아웃풋 파라미터 (프로시저 결과를 가져오는 역할)

call + 프로시저 이름(1, 2, @result); => 프로시저 실행
@ = 사용자가 정의한 변수

Stored function 예제 2

두 정수를 맞바꾸는 프로시저를 작성하자
INOUT = 값을 파라미터로 전달받을수도 최종적으로 값을 반환할 수도 있다.

Stored function 예제 3

각 부서별 평균 연봉을 가져오는 프로시저를 작성하자

Stored function 예제 4

사용자가 프로필 닉네임을 바꾸면 이전 닉네임을 로그에 저장하고 새 닉네임으로 업데이트하는 프로시저를 작성하자

Stored procedure 활용

이외에도 조건문을 통해 분기처리를 하거나
반복문을 수행하거나
에러를 핸들링하거나 에러를 일으키는 등의 다양한 로직을 정의할 수 있다.

Stored procedure과 Stored function의 차이

전제 : mysql, oracle, ms sql server, pstageSQL의 공통적인부분임

트랜잭션 사용 : 여러개의 sql문들을 하나로 묶어서 사용을 하고 싶을때 그 여러 sql문들이 성공을 하면 그 결과는 반영하고 아니라면 아무일없던것처럼 하는것

이외에도

다른 function/procedure를 호출할 수 있는지
resultset(=table)을 반환할 수 있는지
precomplied execution plan을 만드는지
try-catch를 사용할 수 있는지

3-tier architecture에서 stored procedure의 의미

오늘날의 IT회사들은 일반적으로 client-server architecture의 한 종류인 tree-tier architecure 모델로 서비스를 개발한다.

로직 티어 : 사용자로부터 받은 요청을 처리함
로직티어에서 비즈니스 로직을 담당하는데 여러개의 웹어플리케이션 서버를 띄우게 된다.

stored procedure를 사용한다는것은 로직티어에도 데이터티어에도 비즈니스 로직이 존재할 수 있다는 것이다.

Stored procedure의 장점

application에 transparent하다

로직티어에 4개의 자바 스프링을 사용하는 웹어플리케이션 서버가 있다. 데이터티어에는 mysql하나로 가동한다고 가정한다.(실무에서는 여러대임)

로직티어에 소스코드로 비즈니스로직을 관리한다면
4개의 인스턴스들은 같은 코드를 기반으로 동작을 하기 때문에 같은 소스코드 기반으로 동작을 한다.

수정을 해야한다면 코드 수정하고 배포를 해줘야한다. 컴파일하고 배포파일 만들고 기존의 인스턴스를 내리고 새로운 인스턴스를 올려야한다.

그작업을 하려면 트래픽을 계속해서 받고 있기 때문에 동시에 바꿔주면 안된다. 하나씩 재기동을 해줘야한다.
수정이 있을때마다 하나하나 작업하는데 번거로움이 있을 수 있다.

하지만 그 비즈니스 로직이 프로시저로 관리가 된다면 새로운 로직으로 바꿔줄때 어떻게 해야할까
프로시저로 비즈니스 로직을 관리하면 로직티어에서는 프로시저를 호출하는 형태이다.

프로시저의 이름이나 파라미터 말고 바디부분만 수정이 있다면 그냥 프로시저만 바꿔주면 로직티어에 적용된다.

network traffic을 줄여서 응답 속도를 향상시킬 수 있다.

로직티어에서 비즈니스 로직을 담당하고 있다면
select를 하면 네트워크 트래픽이 생긴다.

비즈니스 로직이 프로시저에 있다면 네트워크 트래픽이 줄어 응답속도가 줄어든다.

여러 서비스에서 재사용이 가능하다.

DB를 사용하는 서버가 3개있다.

로직티어에서 비즈니스로직을 구현한다면 각각 다른 언어로 똑같이 구현한다.

하지만 프로시저에서 로직을 관리하면 각 서버에서는 호출만 하면 되기 때문에 번거롭게 각자 구현하지 않아도 된다.

민감한 정보에 대한 접근을 제한할 수 있다.

회사에서 개인정보등 민감한 정보는 개발자의 접근에 우선 제한을 둔다.
이때 프로시저를 통해 DB에 접근하도록하여 프로시저를 통해서 로직을 작성하도록한다.
직접적인 접근은 막고 프로시저를 통해 개발을 하게 하는것

Stored procedure 단점과 실무에서 쓰기 조심스러울 수 있는 주관적 의견

유지 관리 보수 비용이 커진다.

일부는 소스코드상에 구현되어있고 일부는 프로시저에서 구현되어있다보니까
버전관리를 둘 다 해줘야한다.
왔다갔다봐야하니 가독성이 떨어진다.
프로시저관련 문법을 알아야한다.
DB서버를 추가하는 것은 간단한 작업이 아니다.

로직티어에 온 트래픽이 db까지 이어진다. 그러면 RDBMS의 cpu사용량이 웹어플리케이션 서버보다 많아진다.

이때 갑자기 트래픽이 늘면 웹어플리케이션에서 프로시저를 호출하고 프로시저가 많이 동작하게된다.
그러면 사용자가 불편을 겪게 된다. (느려진다.)

DB 서버를 늘리려면 데이터 복제를 해야하기 때문에 쉽사리 늘려지지 않는다.

저장 프로시저를 사용하면 유사시에 트래픽이 몰릴때 DB에 메모리사용량이나 cpu사용량이 증가하고 긴급하게 대응을 할 수 없게된다.

로직 티어에 어플리케이션 서버 투입은 간단하다.

DB에 cpu 사용량이 안정적이고 서버가 부하를 받을때 신규서버를 투입하기 쉽다.
stored procedure가 언제나 transparent인건 아니다.

프로시저의 로직의 이름이 수정이 되면 서버에서 호출할때 이름이랑 다르기때문에 문제가 생긴다.

그래서 그냥 바꿔주면 안되고 기존의 프로시저를 두고 새로운이름의 프로시저를 만든다. 그리고 서버에서 호출하는 로직을 변경해준다.

그러면 바꿔진 코드로 빌드를하고 하나하나 재시작을 해주고 기존의 프로시저를 지운다. 상황에 따라 오히려 손이 많이 갈 수 있다.

transparent하다고 무조건 좋은 것도 아니다.

프로시저의 바디부분을 변경을 했는데 예상치 못한 버그가 있었다.
이때 이 버그를 롤백할동안에 들어온 트래픽들은 문제가 생기고 사용자의 피해로 돌아간다.

하지만 로직티어에서 관리한다면 하나하나 변경을 하고 모니터링할수있고 예상치 못한 에러를 발견해도 4대의 서버중 1대만 문제가 있는것이기 때문에 장애를 최소화할수 있다.

재사용이 가능하다는 것도 양날의 검이 될 수도 있다.

1개의 서버가 무리한 호출로 DB에 영향을 미치면 그 DB를 사용하는 다른 서버들도 문제가 발생할 수 있다.

그래서 이렇게 관리를 해야한다. 각각의 서비스들은 프로시저를 직접적으로 호출하는 것이 아닌 api를 사용해서 호출한다.
이러면 호출이 무리하게 많아졌을때 그 서버의 호출을 중단하고 다른서버는 문제가 생기지 않고 사용될 수 있다.
비즈니스 로직을 소스 코드에 두고도 응답 속도를 향상 시킬 수 있다.
응답속도가 빠르다는 장점이 있었다.

동시호출로 응답속도를 향상시킨다.
순차적인 호출이 요구될때 캐쉬를 사용해서 응답 속도를 향상 시킨다.
민감한 정보에 대한 접근을 완벽히 제한할 순 없다.
개발자가 마음만 먹으면 프로시저를 통해 민감한 정보를 리턴하게 할수도있다.
DB 혹은 테이블 접근을 막으면 개발 및 CS업무의 신속함이 떨어진다.

참고

https://velog.io/@donghoim/MySQL-%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80-Stored-Procedure
https://www.youtube.com/watch?v=m2jx18yg8EA
https://www.youtube.com/watch?v=SOLm-GXFzG8

면접 질문

저장 프로시저가 무엇인지 설명해주세요
데이터베이스에 저장된 SQL명령문을 하나의 함수처럼 실행하기 위한 Query(쿼리)의 집합이다. 같은 쿼리를 반복할 필요가 없어 속도가 빠르고 에러 확률을 감소시킨다.
저장 프로시저를 사용하면 어떤 장점이 있나요?

반복적인 작업을 피할 수 있다. 하지만 저장 프로시저에 예상치못한 에러가 발생하면 피해를 저장 프로시저를 호출하는 모든 곳에서 받는다는 단점이 있다.
개발 언어에 비의존적이다.
SQL문이 서버에 이미 저장되어 있기 때문에 클라이언트와 서버 간 네트워크 상 트래픽이 감소된다.
프로시저 내에서 참조 중인 테이블의 접근을 막을 수 있아 보안성이 향상된다.