소스 코드 👉🏻 https://github.com/gitchan-Study/2023-sql-sample/pull/12/files
feat: stored procedure by gitchannn · Pull Request #12 · gitchan-Study/2023-sql-sample
github.com
Computer Science 모아보기 👉🏻 https://github.com/seoul-developer/CS
GitHub - seoul-developer/CS: 주니어 개발자를 위한 전공 지식 모음.zip
주니어 개발자를 위한 전공 지식 모음.zip. Contribute to seoul-developer/CS development by creating an account on GitHub.
github.com
💋 Stored Procedure란?
✔️ 개념
- stored function과 유사하게, 사용자가 정의한 프로시저이다.
- RDBMS에서 저장하고 사용되는 프로시저
- 구체적인 하나의 태스트를 수행한다.
✔️ 예시 1: 두 정수의 곱셈 결과를 가져오는 프로시저
create procedure product(in a int, in b int, out result int)
begin
set result = a * b;
end;
- input 파라미터는 In, output 파라미터는 out이라고 명시한다.
- 생략시 In으로 기본 설정된다.
- output 파라미터에 저장하게 되면 자동으로 해당 값이 파라미터를 통해 반환된다.
이제 실행해보면,
call product(5, 7, @result);
select @result;
✔️ 예시 2: 각 부서별 평균 연봉을 가져오는 프로시저
create procedure get_dept_avg_salary()
begin
select dept_id, avg(salary)
from employee
group by dept_id;
end;
call 명령어를 통해 호출하면,
call get_dept_avg_salary();
💋 Stored Procedure VS Stored Function
💋 Stored Procedure의 장단점
대부분의 애플리케이션은 presentation layer, buisness layer, data layer로 구성되어 있고, 이런 구조를 레이어드 아키텍처라고 한다.
stored procedure에는 반드시 비즈니스 로직이 들어가게 되는데, 이 프로시저는 데이터 계층에 속한다.
⇒ data 레이어에 비즈니스 로직이 들어가게 된다!
지금부터 설명하는 장단점은 모두 데이터 레이어에 비즈니스 로직이 들어가는 것의 장단점이라고 이해하면 쉽다.
✔️ 장점
- 비즈니스 로직이 변경되었을 때, stored procedure만 변경하면 기존의 코드를 그대로 가져가서 새롭게 배포하지 않고도 사용할 수 있다.
- 비즈니스 로직이 바뀌었는데도 애플리케이션을 그대로 쓸 수 있으면 application에 transparent하다고 한다.
- 네트워크 트래픽을 줄여서 응답 속도를 향상시킬 수 있다. ⇒ 가장 큰 장점
- 한 로직에서 select, insert, update를 해야 한다면 기존에는 애플리케이션 서버가 db.서버를 3번 호출하지만, 로직을 모두 Stored Procedure에 넣어버린다면 sql문을 모두 Stored Procedure에 넣어버려서 단 1번만 호출해도 된다.
- 여러 서비스에서 재사용이 가능하다.
- 동일한 로직을 서로 다른 언어를 사용하는 3개의 부서에서 구현한다면 3번 구현하는 대신 단순 Stored Procedure를 호출하기만 하면 된다.
- 민감 정보에 대한 접근을 제한할 수 있다.
- 애플리케이션 코드는 Stored Procedure를 호출하고 결과를 받는데 집중할 뿐, Stored Procedure가 어떻게 동작하는지에 대한 세부 사항은 알 필요가 없다.
✔️ 단점
- 유지보수 비용이 커진다.
- 비즈니스 로직이 비즈니스 레이어와 데이터 레이어에 흩어져 있어서 로직 파악에 시간이 오래 걸린다
- 버전 관리도 2배로 들어간다.
- 소스코드 관리는 편하지만, 프로시저 관리는 어렵다.
- 개발자들이 프로시저 문법을 추가로 알아야 한다.
- 새로운 프로시저를 만들면 프로시저도 바꾸고, 소스 코드에서도 해당 프로시저를 호출해야 해서 복잡해진다.
- DB 서버에 부하가 늘어나는데, DB 서버를 늘리는 건 더 어렵다.
- DB가 프로시저를 통해서 비즈니스 로직을 사용해 트래픽이 늘어났을 때, cpu 사용량이 크게 증가한다.
- 신규 서버를 투입하려고 해도, 데이터를 복제하던지, 일부를 옮기던지 해야 하기 때문에 어렵다.
- 비즈니스 로직을 처리하는 서버(예. 톰캣 서버)의 투입은 비교적으로 매우 간단하다.
- stored procedure가 언제든 transparent한 것은 아니다.
- 프로시저의 이름을 바꾸었다면 기존 소스 코드는 과거 이름을 호출하고 있기 때문에 소스 코드도 변경해야 한다.
- 이때 무중단으로 하려면 몇 단계에 걸쳐서 소스 코드를 수정해야 해서 오히려 손이 더 많이 갈 수도 있다.
- transparent하다고 무조건 좋은 것도 아니다.
- 프로시저에 버그가 존재하던 순간에 들어왔던 트래픽은 모두 잘못될 수 있다.
- 애플리케이션 서버는 상대적으로 여러 개 있으면서 새로운 로직을 순차적으로 배포하기 때문에, 중간 모니터링을 통해서 일부만 잘못되어, 예상치 못한 버그의 영향을 줄일 수 있다.
- 재사용 가능하다는 것도 늘 좋은 것은 아니다.
- 여러 언어에서 동일한 프로시저를 호출하면 DBMS의 cpu 사용량이 커질 수 있다.
- 통제되지 않은 사용으로, 모든 서버에 문제가 발생할 수 있다.
- 앞단에 다른 서버를 두고, api를 통해서 db 서버에 가는 요청을 조절할 수 있어야 한다.
- 비즈니스 코드를 소스 코드에 두고도 응답 속도를 향상시킬 수 있다.
- 쓰레드 풀, unblock io, 캐시 등을 사용해서 응답 속도를 향상시킬 수 있다.
- 이렇게 하면 응답 속도를 향상시키면서 db 부하까지 줄일 수 있다.
- stored procedure가 민감한 정보에 대해서 접근을 완벽하게 제한할 수 있는 것은 아니다.
- 담당자, 개발자에게만 db 혹은 테이블 권한을 주고 민감한 정보는 암호화해서 관리하는 것으로 예방할 수 있다.
- 보안서약서 등으로 정책적으로 보안을 강화하는 것도 좋은 방법이다.
- 프로그래밍 언어에 비해서 복잡하고, 유연한 코드를 작성하기가 어렵다.
- 가독성이 떨어지고, 디버깅이 어렵다.
💋 참고자료
- https://www.youtube.com/watch?v=m2jx18yg8EA&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=11
- https://www.youtube.com/watch?v=SOLm-GXFzG8&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=12 ⇒ 짱재밌음!
도움이 되었다면, 공감/댓글을 달아주면 깃짱에게 큰 힘이 됩니다!🌟
비밀댓글과 메일을 통해 오는 개인적인 질문은 받지 않고 있습니다. 꼭 공개댓글로 남겨주세요!
