[DB/쉬운코드] stored program

stored program

stored program은 데이터베이스 내부에 저장되어 있는 프로그램을 의미한다.

• function
• procedure
• trigger

stored program은 데이터베이스에서 직접 실행하기 때문에 서버와 데이터베이스 간의 네트워크 트래픽을 줄여준다는 장점이 있다.
 

stored function

stored function은 사용자가 정의한 함수로, 연산 후 하나의 값을 반환한다. stored function을 정의하기 위해서는 `CREATE FUNCTION`문을 사용한다.

CREATE FUNCTION func_name(param1, param2)
  RETURN data_type [characteristics]
  BEGIN
    /* function body */
  END

🚨 `func_name`은 `[db_name.]func_name` 형식을 갖는다. 데이터베이스를 지정하지 않는 경우, 현재 사용하고 있는 데이터베이스에 생성된다.

 

• 생성

DELIMITER $$
CREATE FUNCTION company.dept_avg_salary(d_id int)
  RETURNS int NO SQL
  BEGIN
    DECLARE avg_salary int;
    SELECT AVG(salary) INTO avg_salary FROM employee WHERE dept_id = d_id;
  END
  $$
 DELIMITER ;

🚨 MySQL은 기본 delimiter로 `;`을 사용한다. 함수 정의 시 내부에서 `;`을 사용하기 때문에 임시로 delimiter를 다른 문자로 변경하여 함수 내부에서 `;`을 사용할 수 있도록 한다.

🚨 `DECLARE`는 변수를 선언하는 명령으로 `DECLARE var_name data_type [default value]` 형태로 사용한다. 기본 변수는 함수 내에서만 유효하다. 

• 호출

SELECT id, dept_avg_salary(id) FROM department;

• 삭제

DROP FUNCTION IF EXISTS dept_avg_salary;

• 조회

SHOW FUNCTION STATUS WHERE DB = 'company';
SHOW CREATE FUNCTION dept_avg_salary;

 

stored procedure

stored procedure는 사용자가 정의한 프로시저로, 구체적인 하나의 작업을 수행한다. stored function과 유사하게 `CREATE PROCEDURE`를 이용하여 정의한다.

CREATE PROCEDURE proc_name ([proc_param])
BEGIN
  /* procedure body */
END

/*
  proc_param : [IN | OUT | INOUT] param_name data_type
*/

 

• 생성

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

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

🚨 `SET`은 세션 변수를 선언한다. `SET @var_name = value`

• 호출

CALL product(5, 7, @result);
SELECT @result;

• 삭제

DROP PROCEDURE IF EXISTS [stored_program];

• 조회

SHOW PROCEDURE STATUS WHERE DB = '[db_name]';
SHOW CREATE PROCEDURE product;

 

stored function	stored procedure
`CREATE FUNCTION`	`CREATE PROCEDURE`
`RETURN` 키워드로 반드시 하나의 값을 반환해야 한다	반드시 값을 반환할 필요는 없지만, `OUTPUT` 파라미터를 이용하여 하나 이상의 값을 반환할 수 있다
SQL문에서 호출 가능	`CALL` 명령어를 통해 호출
대부분 트랜잭션 사용 불가	트랜잭션 사용 가능
연산 목적	비즈니스 로직 목적

 

stored procedure의 장단점

stored program은 데이터베이스에 저장되기 때문에 네트워크 트래픽이 감소하여 응답 속도가 향상된다는 장점이 있다. 그렇다면 실무에서 stored procedure를 사용하는 것이 좋을까?

🤔 비즈니스 로직을 어플리케이션 레벨에 두면서 응답 속도를 향상시킬 방법은 없을까?

• 쿼리를 동시에 실행할 수 있는 경우, 동시에 요청을 보낸다.
• 캐시를 사용하여 데이터베이스에 접근하는 시간을 줄인다.

 
stored procedure는 어플리케이션에 transparent하다는 장점이 있다. 데이터베이스에 서버가 4대 연결된 상황을 가정해보자. 비즈니스 로직이 어플리케이션에 위치한다면 로직이 수정될 때마 단계적으로 서버를 재시작 해줘야 한다.
하지만 비즈니스 로직이 procedure로 관리되는 경우에는 procedure만 수정해주면 된다. 즉, 비즈니스 로직이 있는 procedure가 수정되어도 이를 호출하는 어플리케이션 부분은 영향을 받지 않는다.

🚨 transparent가 반드시 좋은 것은 아니다. procedure에 치명적인 버그가 있다면 이를 사용하는 모든 서버에 영향을 주기 때문이다. 반면, 어플리케이션 레벨에만 비즈니스 로직을 구현하는 경우, 신규 버전을 모니터링 하여 오류를 파악할 수 있기 때문에 상대적으로 피해 비용이 적다.

 
또한, 여러 서비스에서 재사용할  수 있다. 각각의 서비스가 서로 다른 언어로 작성되어 있다면 같은 비즈니스 로직도 언어별로 작성되어야 한다. 하지만 procedure로 관리한다면 언어별로 구현할 필요없이 함수를 호출하기만 하면 된다.
 

🚨 procedure가 여러 서비스에서 호출되면 데이터베이스의 부하로 이어질 수 있다.

 
하지만 stored procedure는 유지보수 비용이 커진다는 단점이 있다. 비즈니스 로직이 일부는 어플리케이션에, 일부는 데이터베이스에 있기 때문에 번갈아가며 확인해야 하고, 각각 버전 관리를 해줘야 하며 프로시저 문법도 잘 알아야 한다.

트래픽이 증가하는 상황을 가정해보자. 비즈니스 로직이 어플리케이션에만 존재한다면, 오토 스케일링을 통해 서버 인스턴스를 늘려 트래픽을 분산시켜주면 된다.
하지만 procedure에 존재한다면 데이터베이스의 부하가 발생한다. 데이터베이스를 추가한다고 해도 새로운 데이터베이스에 데이터가 없기 때문에 데이터를 옮기지 않으면 아무 소용이 없다.

이외에도 프로그래밍 언어에 비해 복잡하고 유연한 코드를 작성하기 어려우며 가독성이 떨어지고 디버깅이 어렵다는 단점이 있다.
 

trigger

trigger는 데이터베이스에 특정 이벤트가 발생했을 때 자동적으로 실행되는 procedure를 의미한다. `CREATE TRIGGER`를 이용하여 정의할 수 있다.

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
[trigger_order]
BEGIN
  /* trigger body */
END

/*
  trigger_time : BEFORE | AFTER
  trigger_event : INSERT | UPDATE | DELETE
  trigger_order : FOLLOWS | PRECEDES
*/

DELIMITER $$
CREATE TRIGGER log_user_nickname
  BEFORE UPDATE
  ON users FOR EACH ROW
  BEGIN
    INSERT INTO users_log values(OLD.id, OLD.nickname, NOW());
  END
  $$
DELIMITER ;

🚨 `OLD`는 수정되기 전의 tuple을 가리킨다. 반대로 `NEW`는 수정된 tuple을 가리킨다. 

🚨 row에 이벤트가 발생할 때마다 trigger를 실행하면 너무 많은 trigger가 발생하므로 statement 단위로 trigger하는 것을 권장한다. (MySQL의 경우 `FOR EACH STATEMENT`가 존재하지 않는다.)

trigger는 소스 코드로는 발견할 수 없는 로직이기 때문에 어떤 동작이 일어나는지 파악하기 어렵고, 문제가 발생했을 때 대응하기도 어렵다.

😵 trigger 사용 시 주의사항

• 과도한 trigger는 DB에 부담을 주고 응답을 느리게 만든다.
• 디버깅이 어렵다. 따라서 문서 정리가 정말 중요하다.

---

참고자료

• https://www.youtube.com/watch?v=I1jjR58Rzic&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=10
• https://www.youtube.com/watch?v=m2jx18yg8EA&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=11
• https://www.youtube.com/watch?v=SOLm-GXFzG8&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=12
• https://www.youtube.com/watch?v=mEeGf4ZWQKI&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=13