[ Database - Intermediate ] - stored procedure

Stored procedure

이는 사용자가 정의한 프로시저입니다. 그리고 RDBMS에 저장되고 사용되는 프로시저로서 구체적인 하나의 태스크(task)를 수행합니다. 

 

바로 stored procedure의 예시를 한번 간단히 살펴보겠습니다. 아래는 두 정수의 곱셈 결과를 가져오는 프로시저를 작성해 본 결과입니다.

delimiter $$
CREATE PROCEDURE product(IN a int, IN b int, OUT result int)
BEGIN
    SET result = a * b;
END
$$
delimiter ;

 

이제 이를 실행해보면

call product(5, 7, @result);
select @result;
# 35

위와같은 결과가 나오게 됩니다. 여게에서의 특징은 IN 파라미터를 통해 인자를, OUT 파라미터를 통해 stored-procedure의 반환값을 지정해줄 수 있다는 점입니다.  그리고 바로 두 정수를 맡 바꾸는 프로시저를 작성해 보겠습니다.

 

delimiter $$
CREATE PROCEDURE swap(INOUT a int, INOUT b int)
BEGIN
    set @temp = a;
    set a = b;
    set b = @temp;
END
$$
delimiter ;

INOUT은 호출 시에 값을 넣을 수도, 값이 변경되어 반환도 된다는 소리입니다.

 

set @a = 5, @b = 7
call swap(@a, @b)
select @a, @b;

# 7, 5

위와같이 결과가 나타나게 됩니다. 바로 다음 예제로 각 부셔별 평균 연봉을 가져오는 프로시저를 작성해보겠습니다.

 

delimiter $$
CREATE PROCEDURE get_dept_avg_salary()
BEGIN
    select dept_id, avg(salary)
    from employee
    group by dept_id;
END
$$
delimiter ;

call get_dept_avg_salary()

이는 IN, OUT없이 그냥 위와같이 작성하고 호출만 하면 됩니다. 시시하니까 마지막으로 더 예제를 보면 사용자가 프로필 닉네임을 바꾸면 이전 닉네임을 로그에 저장하고 새 닉네임으로 업데이트하는 프로시저를 작성해 보겠습니다.

 

일단 위와같은 예시라고 해 봅시다. 유저의 정보, 로그의 정보가 남아있습니다.

 

delimiter $$
CREATE PROCEDURE change_nickname(user_id INT, new_nick varchar(30))
BEGIN
    insert into nickname_logs (
        select id, nickname, now() from users where id = user_id
    );
    update users set nickname = new_nick where id = user_id;
END
$$
delimiter ;

call change_nickname(1, 'ZIDANE');

이렇게 기본적으로 IN으로 주고 로직을 위와같이 짜면 되겠죠?? 이에 대한 결과를 보면

이에 대한 결과는 위와같게 됩니다.

 

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

 

stored procedure vs stored function

우선 create 문법, return 키워드로 값 반환 여부, 파라미터들을 통한 값의 반환, 값의 반환 필수 여부, SQL statement에서의 호출 가능여부, transaction에서의 사용 가능여부, 주된 사용 목적이 다 다릅니다. 저는 여기서의 핵심적인 내용은 STORED PROCEDURE가 결국에는 business logic에 주로 사용되고 SOTRED FUNCTION 주로 computation 목적 위주로 사용된다는 점 인것 같습니다.

 

이 외에도 다른 function/procedure를 호출 할 수 있는지, resultset(= table)을 반환할 수 있는지, precompiled execution plan을 만드는지, trt-catch를 사용할 수 있는지 등등.. 차이점이 많습니다.

 

3-tier architecture에서 stored procedure의 의미

저번에도 말했지만 오늘날의 IT 회사들은 일반적으로 client-server architecture의 한 종류인 three-tier architecture 모델로 서비스를 개발합니다.

위와같이 Logic tier에 많은 애플리케이션 서버가 있습니다. 대표적으로 Java Spring이 있는 것이고요. 우리가 살펴보았던 Stored procedure는 RDBMS에 저장되고 사용되는 프로시저로써 주된 사용 목적은 비지니스 로직 구현이라고 했습니다. 이 예기는 data tier에서 business logic이 있을 수 있다는 이야기입니다. 이제 이와 관련된 장점과 단점에 대해 살펴보도록 하겠습니다.

 

application에 transparent 하다

우선 logic tier에 Java Spring으로 작성된 4개의 WAS Instance가 있다고 해 보겠습니다. data tier로는 MySQL 1개가 있고요. 당연히 1개로는 실무에서는 안하고 AA같은 것을 두어 가용성을 확보하죠. 그리고 위 WAS들은 각각마다 동일한 business logic이 있습니다. 그리고 이를 수정해서 빌드하고 배포하려고 합니다. 이떄 이들을 동시에 업데이트 해서는 안됩니다. 그 이유는 당연히 트래픽을 처리하고 있을 수도 있기 때문입니다. 즉 나누어서 다양한 무중단 배포 기법을 사용해야 한다고 예전부터 배웠었습니다. 이 과정은 K8s에서도 가능한데 매우 번거로운 작업이 아닐 수가 없습니다.

 

하지만 위와같은 비지니스 로직이 data tier에 있다면, procedure를 호출만 해주고 있는 상황입니다. 이 logic을 바꾸면 MySQL의 로직만 바꿔주면 됩니다. 이를 매우 transparent하다고 하는데, 이는 수정을 해도 위의 단에서 수정을 거의 하지 않아도 됨을 의미합니다. 즉 application에 매우 transparent합니다!

 

 

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

위와같이 select문이 procedure안에서 호출될 때마다 네트워크 트레픽이 발생됩니다. 즉 비지니스 로직이 logic tier에 있을 때에는 여러개의 SQL문을 수행할 때마다 네트워크 트래픽이 발생하는데, 이를 data tier에 두면, 트레픽 1 번에 응답까지 받아올 수 있게 됩니다. 그래서 네트워크 트래픽이 줄어들어 응답 속도를 향상시킬 수 있게 됩니다.

 

여러 서비스에서 재사용 가능합니다

 같은 비지니스 로직을 다른 언어로 계속 바꿔서 작성해야 하는걸 생각하면 벌써 피곤합니다. 하지만 이 비지니스 로직을 data tier에 놓고 사용한다면 각각의 서비스는 단순히 로직을 호출만 해주기만 하면 되서 여러 서비스에서 재사용 가능하다는 점입니다.

 

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

즉 민감한 정보를 직접적으로 접근하는 것이 아니라 사용자가 stored procedure를 통해 간접적으로 접근할 수 있게 할 수 있습니다. 하지만 이에 대한 단점도 있습니다. 

 

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

이는 저번에도 언급했던 문제인데, logic tier, data tier에 비지니스 로직이 산발해 있으면 이를 유지보수, 관리하는데 비용이 커지게 됩니다. 그리고 이 두개의 버전관리를 따로 해주어야 합니다. 그리고 개발자들이 프로시저와 관련된 문법도 알아야 하고, 프로시저 코드를 변경하면 서비스에서 이를 적용시켜주고 재배포를 해줘야 하는 번거로움이 있습니다.

 

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

만약 트래픽이 급중하게 되면 서비스에 비해 data tier즉 RDBMS의 CPU 사용량이 매우 급증하게 됩니다. 따라서 우리는 RDBMS를 복제해주어야 합니다. 

 

하지만 복제도 쉬운 일은 아닙니다. 이전의 모든 데이터와 프로시저 코드를 옮겨야 하기 때문이죠. 하지만 이러한 로직 코드를 서비스 단에 두게 되면 아래와 같게 됩니다.

 

위와 동일한 상황이 발생했습니다. 그러면 logic tier의 서비스의 CPU 사용량이 급증하게 되고, 이에 반해 MySQL은 계산해야 하는게 없으므로 상대적으로 적어지게 됩니다. 이때는 이 애플리케이션을 추가해주면 되는데, 이는 요즘 클라우드 Auto-scale group이런게 너무 잘 되어 있어서 위의 상황과는 차원이 다르게 쉬운 일로 치환되게 됩니다.

 

위와같이 로드 밸런싱이 가능해지게 됩니다. 이러한 측면에서는 프로시저 코드를 두는 것이 실무에서의 걸림돌이 될 수 있다는 것이겠죠??

 

stored procedure가 언제나 transparent인건 아니다

또한 함수의 이름을 바꾸는 경우에는 transparent하지 않습니다. 그 이유는 logic tier에서도 영향을 받기 때문에 싹다 바꾼다음에 재배포 해야하는 매우 번거로운 작업을 수반할 수 있게 됩니다.

 

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

문제가 있었던 프로시저가 생기면 이를 롤백해야 합니다. 하지만 롤백을 한다해도 기존 버그가 있었던 프로시저에 사용자들은 피해를 일부분 입게 됩니다. 그래서 아래와 같은 방법을 사용하는데.

 

바로 간할적으로 일부분의 애플리케이션만 수정하고 버그가 발생하면 이만 다시 롤백하는 것이죠. 그럼 피해를 최소화 할 수 있게 됩니다. 즉 이와같이 비지니스 로직을 logic tier에 두게 되면 번거롭긴 해도 예상하지 못한 문제의 영향을 최소화할 수 있습니다. 이를 제가 알기론 캐너리 배포라고 합니다.

 

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

위와같이 만약 Service A가 무지막지하게 프로시저를 호출하면 MySQL즉 RDBMS의 CPU 사용량이 미친듯이 올라가서 다른 서비스에도 영향을 끼칠 수 있는 가능성이 다분해집니다. 그래서 우리는 포워드 프록시 를 사용해서 아키텍쳐를 구성해서 이 안에서 RestFul API를 통해 RDBMS로의 요청을 하는 것이 일반적입니다. 이렇게 하게되면 Service A가 미친듯이 호출을 해도 다른 서비스는 정상 작동할 수 있게 됩니다. 이렇게 하려면 비지니스 로직을 포워드 프록시 쪽에 두는 즉 logic tier에 두는 것이 바람직합니다.

 

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

기존에 순차적으로 SQL구문을 날려야 해서 엄청나게 네트워크 상으로 엄청나게 비효율적이라고 했습니다. 하지만 만약 병렬적으로 수행 가능하다면? 전통적 쓰레드 풀을 사용하거나 비동기-논블로킹 IO를 통해 위의 예시에서 insert, update SQL구문을 동시에 실행한다면 이는 네트워크 응답속도를 최대로 끌어올릴 수가 있게 됩니다.

 

그리고 다른 DB에서 조회할 때에는 무조건 순차적으로 진행해야 하는데, 이 또한 In-Memory 기반의 캐시 서버인 Redis같은 걸로 해결할 수 있습니다. 이를 위해서는 코드를 위와같이 조금 수정해야 합니다. 캐시에 적용해줄 값과 life-time을 추가해주게 되면, 로직이 더 느려질거 같이 보이지만, 그 이후의 트래픽에 대해서는 응답속도가 매우 향상시킬 수 있게 됩니다. 뿐만 아니라 DB 부하까지도 줄일 수 있습니다!!

 

stored procedure가 민감한 정보에 대한 접근을 완벽히 제한할 순 없다

프로시저를 사용하면 민감정보를 제한할 수 있다 했는데, 이를 완벽히 제한할 수 있는건 아닙니다. 또한 민감정보를 제한함으로써 담당자나 개발자가 이에 대한 접근을 하는데 제약이 생겨 서비스 대응이 지연될 수 있습니다.

 

그래서 우리는 담당자나 개발자에게만 DB 혹은 테이블 권한을 부여해야하고 민감한 정보는 암호화해서 저장해야 하고 보안서약서 등을 통해 정책적으로 보안을 강화해야 합니다.

 

그리고 이외에도 procedure로는 복잡하고 유연한 코드를 작성하기 어렵습니다. 오늘날의 프로그래밍 언어는 훨씬 다양하고 강력한 기능들을 제공합니다. 또한 procedure는 가독성이 떨어지며 디버깅또한 어렵다는 단점이 있습니다.