MySQL에서는 절차적인 처리를 위해 스토어드 프로그램을 이용할 수 있다. 스토어드 프로그램은 스토어드 루틴이라고도 하는데, 스토어드 프로시저와 스토어드 함수 그리고 트리거와 이벤트 등을 모두 아우르는 명칭이다. 스토어드 프로그램 가운데 스토어드 프로시저나 함수는 MySQL 5.0부터 추가된 기능이며, 스케줄러는 MySQL 5.1부터 추가된 기능이다. 스토어드 프로그램은 모두 똑같은 문법으로 작성할 수 있고, 서로 큰 차이가 없다.
스토어드 프로그램의 장단점
스토어드 프로그램은 절차적인 처리를 제공하긴 하지만 애플리케이션을 대체할 수 있을지 충분히 고려해 봐야 한다. 스토어드 프로그램을 사용하기로 했다면 어떤 기능에 주로 사용할 것인지도 고려해야 한다. 스토어드 프로그램의 용도를 정확하게 판단하려면 스토어드 프로그램의 장단점을 알아둘 필요가 있다. 이번에는 스토어드 프로그램의 장단점을 살펴보고, 언제 스토어드 프로그램을 사용하는 것이 효율적이고 언제 사용하면 안 되는지를 판단하는 기준을 알아보겠다.
1. 스토어드 프로그램의 장점
데이터베이스의 보안 향상
MySQL의 스토어드 프로그램은 자체적인 보안 설정 기능을 가지고 있으며, 스토어드 프로그램 단위로 실행 권한을 부여할 수 있다. 이런 보안 기능을 조합해서 특정 테이블의 읽기와 쓰기 또는 특정 칼럼에 대해서만 권한을 설정하는 등 세밀한 권한 제어가 가능하다. 애플리케이션의 모든 기능을 스토어드 프로그램으로 작성하기는 어렵겠지만 주요 기능을 스토어드 프로그램으로 작성한다면 SQL 인젝션과 같은 기본적인 보안 사고는 피할 수 있을 것이다. MySQL 서버의 스토어드 프로그램은 입력 값의 유효성을 체크한 후에야 동적인 SQL 문장을 생성하므로 SQL의 문법적인 취약점을 이용한 해킹은 어렵기 때문이다.
기능의 추상화
자바와 C/C++ 같은 객체지향 언어로 개발해본 경험이 있다면 이미 추상화라는 개념은 다 이해하고 있을 것이다. 주위에서 흔히 사용되는 추상화 예제를 한번 살펴보고, 스토어드 프로그램으로 어떻게 기능을 추상화할 수 있고 어떤 장점이 있는지도 함께 알아보자.
여러 테이블에 걸쳐 유일한 일련번호를 발급하되,
일련번호에 자체적인 헤더 값과 시간 정보를 덧붙여서 생성하려 한다.
여기서 필요한 일련번호의 생성 방식은 복잡해서, MySQL의 AUTO_INCREMENT를 이용할 수가 없다. 만약 애플리케이션에서 일련번호 생성용 모듈을 개발한다면 개발하는 언어별로 호환이 되지 않을뿐더러 직접 SQL 클라이언트에서는 사용할 수가 없다. 또한 일련번호 생성용 프로그램을 여러 가지 언어로 개발한다면 일관성이 없어지고 문제가 생길 가능성이 높다.
일련번호 생성용 프로그램을 MySQL 서버의 스토어드 프로그램으로 구현한다면 애플리케이션뿐 아니라 SQL 클라이언트에서도 쉽게 이용할 수 있다. 이뿐만 아니라 MySQL 서버에만 있으면 되기 때문에 동일한 기능을 이용하기 위해 여러 버전의 프로그램이 필요하지 않게 되므로 기능이 변경돼도 쉽게 대응할 수 있다. 각 애플리케이션에서는 일련번호가 어떻게 생성되고 어떤 구조인지 알 필요도 없으며, 그냥 스토어드 프로그램을 호출해서 값을 가져가기만 하면 된다.
네트워크 소요 시간 절감
일반적으로 애플리케이션과 데이터베이스 서버는 같은 네트워크 구간에 존재하므로 SQL의 실행 성능에서 네트워크를 경유하는 데 걸리는 시간은 그다지 중요하게 생각하지 않는다. 하지만 하나하나의 쿼리가 아주 가볍고 빠르게 처리될 수 있다면 네트워크를 경유하는 데 걸리는 시간이 문제가 될 것이다. 즉, 실행하는 데 1초가 걸리는 쿼리에서 0.1~0.3 밀리초 정도의 네트워크 경유 시간은 아무 문제가 되지 않는다. 하지만 0.01초 또는 0.001초 정도 걸리는 쿼리에서 0.1~0.3 밀리초는 무시할 수 없는 부분이다. 게다가 하나의 프로그램에서 이렇게 가벼운 쿼리를 100번 200번씩 실행해야 한다면 네트워크를 경유하는 시간은 횟수에 비례해 증가할 수밖에 없다. 만약 각 쿼리가 큰 데이터를 클라이언트로 가져와서 가공한 후, 다시 서버로 전송해야 한다면 더 큰 네트워크 경유 시간이 소모될 것이다. 하지만 하나의 프로그램에서 100건 200번씩 실행해야 하는 쿼리를 스토어드 프로그램으로 구현한다면 스토어드 프로그램을 호출할 때 한 번만 네트워크를 경유하면 되기 때문에 네트워크 소요 시간을 줄이고 성능을 개선할 수 있다.
절차적 기능 구현
DBMS 서버에서 사용하는 SQL 쿼리는 절차적인 기능을 제공하지 않는다. 즉, SQL 쿼리에서는 IF나 WHILE과 같은 제어 문장을 사용할 수 없다. 그에 반해 스토어드 프로그램은 DBMS 서버에서 절차적인 기능을 실행할 수 있는 제어 기능을 제공한다. 가끔 SQL 문장으로는 절대 처리할 수 없는 문제를 해결해야 할 때도 있다. 일반적으로 이런 상황에서는 데이터를 애플리케이션에서 가공한 후 다시 데이터베이스에 저장하는 형태로 개발을 진행한다. 하지만 이런 해결책은 결국 애플리케이션과 MySQL 서버 간의 네트워크 통신 횟수를 늘리고, 필요한 데이터를 클라이언트와 서버간에 주고받아야 하기 때문에 네트워크를 경유하는 데 시간이 소모된다. 스토어드 프로그램을 이용해 절차적인 기능을 구현하다면 최소한 네트워크 경유에 걸리는 시간만큼은 줄일 수 있으며, 더 노력한다면 불필요한 애플리케이션 코드도 많이 줄일 수 있다.
개발 업무의 구분
순수하게 애플리케이션 개발 조직과 SQL 개발 조직이 구분돼 있는 회사도 있다. 만약 순수하게 애플리케이션을 개발하는 조직과 DBMS 관련 코드(SQL이나 스토어드 프로그램)를 개발하는 조직이 별도로 구분돼 있다면 DBMS 코드를 개발하는 조직에서는 트랜잭션 단위로 데이터베이스 관련 처리를 하는 스토어드 프로그램을 만들어 API처럼 제공하고, 애플리케이션 개발자는 스토어드 프로그램을 호출해서 사용하는 형태로 역할을 구분해서 개발을 진행할 수도 있다.
2. 스토어드 프로그램의 단점
낮은 처리 성능
스토어드 프로그램은 MySQL 엔진에서 해석되고 실행된다. 하지만 MySQL 서버는 스토어드 프로그램과 같은 절차적 코드 처리를 주목적으로 하는 것이 아니라서 스토어드 프로그램의 처리 성능이 다른 프로그램 언어에 비해 상대적으로 떨어진다. 또한 다른 DBMS의 스토어드 프로그램과 비교해서도 MySQL의 스토어드 프로그램은 성능이나 최적화가 부족한 상태다. 아래 그림은 단순한 문자열 조직이나 숫자 계산 등의 연산을 수행하는 능력을 벤치마킹한 결과다.
위 그래프는 단위 시간당 연산 처리 능력을 보여주는데, 수치가 높을수록 처리가 빠르다는 것을 의미한다. 이 벤치마킹 결과에서도 알 수 있듯이 MySQL의 스토어드 프로그램보다 C/C++ 언어는 대략 80배, 자바 언어는 60배 정도 빠른 처리 성능을 보여준다. 또한 오라클의 PL/SQL도 MySQL의 스토어드 프로그램보다 대략 2배 정도 빠른 성능을 보여준다.
이 벤치마킹은 문자열 조직이나 숫자 연산과 같은 CPU 위주의 연산만 측정한 것이므로 실제 업무에 적용한다면 이 정도의 차이는 보이지 않을 것이다. 우리가 스토어드 프로그램을 사용하는 이유가 문자열이나 숫자 연산만 수십억 번 반복하려고 하는 것은 아니니까 말이다. 즉, 문자열 연산이나 숫자 연산에 스토어드 프로그램을 이용하는 것은 잘못된 선택인 것이다. 간단한 숫자나 문자열 연산 그리고 제어문을 이용하긴 하지만 한 번에 많은 쿼리를 실행해야 할 때 가장 효과가 높은 것이다.
애플리케이션 코드의 조각화
IT 서비스가 발전하면서 애플리케이션들은 복잡해지고 필요한 장비의 대수도 급격하게 늘고 있다. 즉, 애플리케이션의 설치나 배포 작업이 갈수록 복잡해지고 있다. 그런데 각 기능을 담당하는 프로그램 코드가 자바와 MySQL 스토어드 프로그램으로 분산된다면 애플리케이션의 설치나 배포가 더 복잡해지고 유지보수 또한 어려워질 수 있다.
참고
- Real MySQL
