[CS]프로시저란 무엇인가요?

프로시저 VS 사용자정의함수

프로시저

프로시저는 자주 실행하는 SQL 쿼리 문장들을 저장해주는 기능

• 절차형 SQL을 활용하여 특정 기능을 수행할 수 있는 트랙잭션 언어이다.
• 해당 프로시저 호출하면, 많은 작업들을 쭉 수행하여 작업을 완료한다
• 보통 데이터조작어(DML)를 수행하는 것이 일반적이다.
• 시스템에서 매일 반복되는 작업, 일련의 배치 작업 등을 프로시저를 사용하여 관리하고 주긱적으로 수행하기도 한다.

사용자 정의함수

• 데이터베이스 내에 저장해 놓은 명령문의 집합(SUM, COUNT, SUBSTRING 등의 함수)은 DBMS에서 미리 만들어둔 내장 함수이고, 이런 함수를 사용자가 별도로 만들 수 있다.
• 리턴값은 반드시 있다.(하나의 값을 반드시 되돌려 받는다.)

c.f. 프로시저는 리턴값이 있을 수도 있고, 없을 수도 있으며, 여러 개의 리턴값을 받을 수 있다.

 

저장 프로시저 (Stored Procedure)

3-tier architecture에서 저장 프로시저 (Stored Procedure)의 의미 

3 tier

Stored procedure

* RDBS에 저장되고 사용되는 프로시저

*주된 사용 목적은 비즈니스 로직 구현(data tier에 business loginc!!)

Stored procedure 장점

1. applciation에 transparent 하다

비즈니스 로직을 수정해야 할 경우를 예로 들어볼 수 있습니다.

• 비즈니스 로직이 어플리케이션의 로직 티어(Logic Tier)에 서버로 구현? 비즈니스 로직을 수정해야 할 일이 생겨서 로직을 변경하고 변경된 로직을 적용하기 위해서는 해당 인스턴스를 내리고 새로운 인스턴스를 올려야 할 수 있습니다.
• Stored Procedure에 비즈니스 로직두면? 변경 시 서버 인스턴스 교체 없이 DB 내에서 즉시 반영 가능하므로 데이터베이스 내부의 비즈니스 로직을 효율적으로 관리할 수 있다는 특징이 application에 transparent 하다는 장점으로 드러납니다.

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

• 비즈니스 로직이 어플리케이션의 로직 티어(Logic Tier)에 서버로 구현? 웹 어플리케이션 서버(Web Application Server) 내에 비즈니스 로직이 구현되어 있는 상황을 가정해보겠습니다. 이런 경우 비즈니스 로직 안에서 여러 개의 SQL 문을 수행할 때마다, 각 SQL 문의 실행에 따라 네트워크 트래픽이 발생하게 됩니다.
• Stored Procedure에 비즈니스 로직두면?  어플리케이션 서버는 해당 프로시저를 호출하게 됩니다. 이 때 프로시저 내부에는 여러 개의 SQL 문이 처리되며, 결과적으로 웹 어플리케이션 서버와 데이터베이스 사이의 네트워크 트래픽은 단 한 번만 발생하게 됩니다.

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

데이터베이스에 사용자 정보에 대한 프로시저(Stored Procedure)가 존재한다고 가정해봅시다. 

A, B, C가 각각 다른 언어(Java, Django, Node.js 등)로 구현되어 있더라도 비즈니스 로직이 프로시저로 추상화되어 있으면 여러 서비스에서 동일한 프로시저를 호출하여 사용자 정보를 얻을 수 있습니다. 각 서비스는 단순히 해당 프로시저를 호출하기만 하면 되므로, 중복된 코드를 작성하지 않아도 되며 유지보수가 용이해집니다

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

Database의 직접 접근을 막고 프로시저를 통해서 로직을 작성할 수 있도록 허용합니다.

 

사용자 신용카드, 주민등록번호 접근을 막는다. Stored Procedure 사용해 Database에 접근하고 개발자는 Stored Procedure에 접근함으로서 로직을 작성합니다.

stored procedure 단점 & 실무에서 쓰기 조심스러운 이유

1. stored procedure를 사용하면 유지 관리 보수 비용이 커진다.

• 비즈니스 로직이 일부는 소스 코드로 관리되고 일부는 프로시저로 관리되기 때문에 소스 코드와 프로시저 모두를 버전 관리해야 하므로 작업의 복잡성이 증가합니다.
• 기존 언어 외에 프로시저 작성에 필요한 문법과 규칙을 숙지해야 합니다.
• 신규 기능을 개발하면 소스 코드와 프로시저 간의 일관성을 유지하고 버전 관리가 어려워 팀 전체의 협업을 더 어렵게 만들 수 있습니다.

2. DB 서버를 추가하는 것은 간단한 작업이 아니다

stored procedure를 사용하여 비즈니스 로직을 구현한 경우, traffic이 프레젠테이션 티어를 통과하여 로직 티어로 이동하면 대부분의 트래픽이 DB 서버까지 전달될 것입니다. 이로 인해 웹 애플리케이션 서버의 CPU 사용량보다 DB 서버의 CPU 사용량이 증가할 가능성이 있습니다. 만약 트래픽이 갑자기 몰리는 상황이 발생한다면, DB 서버의 CPU 사용량이 급격하게 상승하여 요청 처리량이 줄어들 수 있습니다. 특히 RDBMS의 CPU 사용량이 90% 이상으로 증가하는 상황이라면 서비스에 치명적인 위험을 초래할 수 있습니다.

이런 상황에서 개발팀은 문제를 해결하기 위해 RDBMS 서버의 확장을 고려할 수 있습니다. 그러나 새로운 서버는 초기에 데이터가 없기 때문에 트래픽을 적절히 분산 처리할 수 없습니다. 기존 RDBMS의 데이터를 새로운 서버로 복제해야 합니다.

따라서 유사 시 긴급하게 대응하는 것이 어려울 수 있습니다.

 

비즈니스 로직을 로직 티어에서 소스 코드를 통해 관리한다면 로직 티어는 데이터를 들고 있지 않기 때문에 애플리케이션 서버를 추가하는 것이 비교적 용이합니다.

 

3.stored procedure가 언제나 transparent인 것은 아니다

stored procedure를 사용하여 비즈니스 로직을 구현한 경우? 로직 티어의 웹 애플리케이션 서버를 재시작해야만 변경된 프로시저가 적용될 수 있습니다. 이런 변경 과정은  불필요한 작업과 중단이 발생할 수 있습니다.

 

stored procedure의 이름이 변경되는 상황

기존의 프로시저 이름을 "A"로 설정했다고 가정합니다. 어떤 이유로 인해 해당 프로시저의 이름을 "B"로 변경해야 하는 상황이 발생할 수 있습니다. 이 때, 프로시저의 이름을 바꾼다면 로직 티어에 있는 소스 코드에서는 여전히 기존의 "A"를 호출하고 있을 것입니다. 따라서 로직 티어의 소스 코드를 수정하여 "B"를 호출하도록 변경해야 합니다. 

 

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

 

변경 사항을 적용한 뒤 배포를 진행했는데 예상치 못한 버그가 발생한다고 가정해봅시다. 이럴 경우 빠르게 이전 버전으로 롤백하여 버그를 해결하게 될 것입니다.

 

그러나 문제가 있었던 프로시저를 사용하고 있던 모든 웹 애플리케이션 서버에서는 해당 문제가 발생한 시간 동안 잘못된 로직이 담긴 프로시저를 사용하고 있었습니다. 이로 인해 해당 서버에서 발생한 트래픽들은 잘못된 로직을 사용하고 있어 안 좋은 영향을 받았을 것입니다.

 

로직 티어에서 소스 코드를 통해 비즈니스 로직을 관리할 경우?

• 변경 사항을 적용한 뒤 모든 웹 애플리케이션 서버를 한 번에 재시작하는 것이 아니라 서버를 하나씩 변경된 로직이 적용된 상태로 재시작할 수 있습니다.
• 문제가 발생한 경우, 해당 서버만 롤백하고 재시작할 수 있습니다. 

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

서비스 A에서 프로시저를 호출하는 양이 많아지면서 DBMS의 CPU 사용량이 90% 이상 증가하는 상황에 놓였다고 생각해봅시다.

이렇게 되면 DB의 프로시저를 사용하는 모든 서비스들에 악영향을 미칠 수 있습니다. 

 

이러한 문제를 방지하기 위해서는 서비스 계층과 DB 계층 사이에 데이터 서비스(Data Service)를 도입하여 서비스 계층이 직접적으로 DB 계층에 접근하는 것을 방지하는 구조를 만들어야 합니다.

 

데이터 서비스는 RESTful API와 같은 인터페이스를 제공하여 서비스 계층이 API를 호출하는 형태로 데이터에 접근할 수 있도록 합니다. 이런 상황에서 서비스 A가 데이터 서비스를 과도하게 호출하는 상황이 발생하면 데이터 서비스는 서비스 A의 호출을 제한하여 다른 서비스들에게 문제가 전파되지 않도록 예방할 수 있습니다. (모든 서비스에 문제가 발생하는 상황을 막을 수 있습니다)

 

이러한 접근 방식은 데이터 서비스 계층을 관리하는 팀에서 비즈니스 로직을 어떻게 관리해야 할 지 고민하게 되며, 결국 로직 계층에서 비즈니스 로직을 관리하게 될 것입니다. 이를 통해 재사용성과 시스템의 안정성을 모두 확보할 수 있습니다.

 

이러한 접근 방식은 데이터 서비스 계층을 관리하는 팀에서 비즈니스 로직을 어떻게 관리해야 할 지 고민하게 되며 로직 계층에서 비즈니스 로직을 관리하게 될 것입니다.

 

비즈니스 로직을 소스코드에 두고도 응답속도를 향상 시킬 수 있다

프로시저를 사용하는 가장 큰 장점 중 하나는 네트워크 트래픽을 줄여 응답 속도를 향상시킬 수 있다는 점입니다.

그러나 동시에 수행 가능한 insert와 update 작업이 있다면 이를 순차적으로 호출할 필요 없이 동시에 호출하여 응답 속도를 향상시킬 수 있습니다.

 

비즈니스 로직에는 여러 개의 SQL 문이 순차적으로 호출될 수 있습니다. 이 경우에는 서비스 계층과 DB 계층 간의 네트워크 오버헤드로 인해 응답 속도에 악영향을 미칠 수 있습니다. 하지만 만약 동시에 실행 가능한 작업들이 있다면 이를 병렬로 처리함으로써 응답 속도를 향상시킬 수 있습니다.

 

select문은 반드시 순차적으로 처리해야하는 로직입니다.이럴 경우에는 어떻게 응답속도를 향상 시킬 수 있을까요?

cache를 사용해 응답속도를 향상 시킬 수 있습니다. 

cache를 사용하면 이전에 처리한 결과를 저장해두고, 동일한 요청이 들어올 경우에는 저장된 결과를 반환함으로써 DB 부하를 줄이고 응답 속도를 향상시킬 수 있습니다.

 

 

 

출처

https://www.youtube.com/watch?v=-OlQiyG0uIc

https://www.youtube.com/watch?v=SOLm-GXFzG8