[MySQL] 프로시저(스토어드 프로그램)의 장단점

1. 스토어드 프로그램이란?

MySQL에서는 절차적인 처리를 위해 스토어드 프로그램을 이용할 수 있습니다. 스토어드 프로그램은 스토어드 루틴이라고도 하는데, 스토어드 프로시저와 스토어드 함수 그리고 트리거와 이벤트 등을 모두 아우르는 명칭입니다.

스토어드 프로그램 가운데 스토어드 프로시저나 함수는 MySQL 5.0부터 추가된 기능이며, 스케줄러는 MySQL 5.1부터 추가된 기능입니다.

그럼 스토어드 프로그램(프로시저, 함수, 트리거)의 장점과 단점을 살펴보도록 하겠습니다.

1-1. 스토어드 프로그램의 장점

- 데이터베이스의 보안 향상

MySQL의 스토어드 프로그램은 자체적인 보안 설정 기능을 가지고 있으며, 스토어드 프로그램 단위로 실행 권한을 부여할 수 있습니다. 이러한 보안 기능을 조합해서 특정 테이블의 읽기와 쓰기 또는 특정 컬럼에 대해서만 권한을 설정하는 등 세밀한 권한 제어가 가능합니다.

주요 기능을 스토어드 프로그램으로 작성한다면 SQL 인젝션과 같은 기본적인 보안 사고는 피할 수 있을 것 입니다.

- 기능의 추상화

복잡한 일련번호 생성 방식(대리점번호 + 시간정보 + 일련번호)의 경우 생상방식 복잡하여, MySQL의 Auto_increment를 이용할 수가 없습니다. 만약 애플리케이션에서 일련번호 생성용 모듈을 개발한다면 개발하는 언어별로 호환이 되지 않을 뿐더러 직접 SQL 클라이언트에서는 사용할 수 가 없습니다. 일련번호 생성용 프로그램을 MySQL 서버의 스토어드 프로그램으로 구현한다면 애플리케이션뿐 아니라 SQL 클라이언트에서도 쉽게 이용할 수 있습니다.

각 애플리케이션에서는 일련번호가 어떻게 생성되고 어떤 구조인지 알 필요도 없으며, 그냥 스토어드 프로그램을 호출해서 값을 가져가기만 하면 됩니다.

- 네트워크 소요 시간 절감

일반적으로 애플리케이션과 데이터베이스 서버는 같은 네트워크 구간에 존재하므로 SQL의 실행 성능에서 네트워크를 경유하는 데 걸리는 시간은 그다지 중요하게 생각하지 않습니다. 하지만 하나하나의 쿼리가 아주 가볍고 빠르게  처리될 수 있다면 네트워크를 경유하는데 걸리는 시간이 문제가 될 것입니다. 하나의 프로그램에서 100번, 200번씩 실행해야 하는 쿼리를 스토어드 프로그램으로 구현한다면 스토어드 프로그램을 호출할 때 한번만 네트워크를 경유하면 되기 때문에 네트워크 소요 시간을 줄이고 성능을 개선할 수 있습니다.

- 절차적 기능 구현

DBMS 서버에서 사용하는 SQL 쿼리는 절차적인 기능을 제공하지 않습니다. 즉, SQL 쿼리에서는 IF나 WHILE과 같은 제어 문장을 사용할 수 없습니다. 그에 반해 스토어드 프로그램은 DBMS 서버에서 절차적인 기능을 실행할 수 있는 제어 기능을 제공합니다. 가끔 SQL 문장으로는 절대 처리할 수 없는 문제를 해결해야 할 때도 있습니다. 일반적으로 이런 상황에서는 데이터를 애플리케이션에서 가공한 후 다시 데이터베이스에 저장하는 형태로 개발을 진행합니다. 스토어드 프로그램을 이용해 절차적인 기능을 구현한다면 최소한 네트워크 경유에 걸리는 시간만큼은 줄일 수 있으며, 더 노력한다면 불필요한 애플리케이션 코드도 많이 줄일 수 있습니다.

- 개발 업무의 구분

순수하게 애플리케이션을 개발하는 조직과 DBMS 관련 코드(SQL이나 스토어드 프로그램)를 개발하는 조직이 별도로 구분돼 있다면 DBMS 코드를 개발하는 조직에서는 트랜잭션 단위로 데이터베이스 관련 처리를 하는 스토어드 프로그램을 만들어 API처럼 제공하고, 애플리케이션 개발자는 스토어드 프로그램을 호출해서 사용하는 형태로 역할을 구분해서 개발을 진행할 수도 있습니다.

1-2. 스토어드 프로그램의 단점

- 낮은 처리 성능

스토어드 프로그램은 MySQL 엔진에서 해석되고 실행됩니다. 하지만 MySQL 서버는 스토어드 프로그램과 같은 절차적 코드 처리를 주목적으로 하는 것이 아니라서 스토어드 프로그램의 처리 성능이 다른 프로그램 언어에 비해 상대적으로 떨어집니다. 또한 다른 DBMS의 스토어드 프로그램과 비교해서도 MySQL의 스토어드 프로그램은 성능이나 최적화가 부족한 상태입니다.

위 벤치마킹은 문자열 조작이나 숫자 연산과 같은 CPU 위주의 연산만 측정한 것이므로 실제 업무에 적용한다면 이 정도의 차이를 보이지는 않을 것입니다. 하지만, 문자열 연산이나 숫자 연산에 스토어드 프로그램을 이용하는 것은 잘못된 선택입니다. 간단한 숫자나 문자열 연산 그리고 제어문을 이용하긴 하지만 한 번에 많은 쿼리를 실행해야 할 때 가장 효과가 높은 것입니다.

- 애플리케이션 코드의 조각화

애츨리케이션의 설치나 배포 작업이 갈수록 복잡해지고 있습니다. 각 기능을 담당하는 프로그램 코드가 자바와 MySQL 스토어드 프로그램으로 분산된다면 애플리케이션의 설치나 배포가 더 복잡해지고 유지보수 또한 어려워질 수 있습니다.

스토어드 프로그램의 권한이나 보안 및 예외 핸들링에 대해서는 다음번에 포스팅하겠습니다.

출처: https://12bme.tistory.com/54?category=682920 [길은 가면, 뒤에 있다.]