데이터베이스 개론 & SQL - 8

 

stored procedure

자주 사용되는 쿼리문을 저장해두고 필요할 때마다 호출해서 사용하기 위해 제공하는 기능 중 하나이다.

여러 쿼리문을 한번에 처리할 때 주로 사용하게 되는 쿼리문들의 집합이라 생각하면 쉬운데, 이렇게 사용함으로써 쿼리문의 재사용도 가능하고 저장 프로시저를 실행하게 되면서 발생하는 최적화와 컴파일 단계가 캐싱되어 여러 번 재사용될 때 실행 속도도 빨라지게 되는 이점이 있어서 사용된다고 한다. 즉 일회용 쿼리가 아닌 이상 일반 쿼리를 반복해서 실행하는 것보단 저장 프로시저를 반복해서 실행하는 게 빠를 수 있다는 의미다. (이론상) 

 

3 tier architecture 모델

클라이언트-서버 아키텍처의 한 종류로써 전 게시글에서도 한번 언급한 적이 있다. 크게 Presentation tier, Login tier, Data tier 로 구성된 아키텍처로 일반적인 IT 회사에서 사용되는 구조이다. 

• Presentation tier : 웹 / 앱 / 태블릿 등
• Logic tier : 백엔드 서버 (node/spring ...)
• Data tier : DBMS

이 얘기가 나온 이유는 stored procedure 는 비즈니스 로직을 구현하기 위해 주로 사용되기 때문에 Data tier 임에도 불구하고 Logic tier 에 간섭을 하게 되는 것. 각 티어마다 비즈니스 로직이 존재한다면?

 

stored procedure의 장단점

이런 관점에서 stored procedure 를 사용하게 될 때의 장점 중 하나는 application 에 transparent 하다는 것이다. 이 의미는 하나의 DBMS 서버에 연결된 백엔드 서버들의 비즈니스 로직이 바뀌게 되면 순차적으로 하나씩 백엔드 서버를 배포해줄 수밖에 없다. 그 이유는 한번에 모든 백엔드 서버들을 재배포해주면 이미 서비스 중이던 서버까지 문제가 생길 수 있기 때문이다. 이렇게 백엔드 서버를 하나씩 빌드 배포를 하는 것이 비효율적일 수 있다.

그런데 만약 백엔드딴에선 stored procedure 를 사용하여 불러오는 코드만 있고 DBMS 내부적으로만 프로시저의 비즈니스 로직을 수정해준다면 위와 같은 상황이 발생하지 않을 것이다. 이 경우 보안적으로도 좋은데, 민감한 정보에 대한 접근을 제한해줄수 있다는 이점도 있다. 

그리고 netwrok traffic 을 줄여, 응답 속도를 향상시킬 수 있다는 장점도 존재한다. 클라이언트 상에서 쿼리문을 전부 문자열로 보내게 되면 각 쿼리문마다 순차적으로 수행되며 네트워크에 부하가 발생하게 된다. 이때 저장프로시저를 사용하면 저장 프로시저의 이름, 매개변수 등의 정보만 보내면 한번에 쿼리문이 수행되므로 매번 쿼리문을 보내주는 것보다 트래픽이 줄기에 부하를 줄일수 있다.

마지막으로 DBMS 에 연결된 여러 백엔드 서비스에서 재사용이 가능해진다. 비즈니스 로직이 백엔드 언어마다 달라질 수 있지만 stored procedure 를 사용하면 백엔드 언어가 다르더라도 동일한 결과를 낼 수 있게 된다.

 

그렇다면 반대로 stored procedure 를 사용함으로써 발생하는 단점도 존재한다. 가장 먼저 유지 관리 보수 비용이 커질 수 있다는 점이다. 그 이유는 백엔드 딴에서 해당 비즈니스 로직을 확인하려면 DBMS 를 확인해줘야 하고 그에 따른 수고로움이 존재할 것이다. 그리고 백엔드와 DBMS 관리자가 서로 다른 경우, 두 언어를 알아야 하므로 교육 비용이 증가할 것이다. 버전 관리 문제에서도 단점이 생길 수 있는데, 백엔드 보다 상대적으로 DBMS의 버전 관리가 더 열악하므로 그에 따른 유지보수 비용이 증가한다고 한다. 

이처럼 순간적으로 DBMS가 부하가 몰려서 트래픽 감당을 못하게 되면 아무래도 임시 방편으로 서버를 늘릴 것이다. 하지만 새로 늘린 서버엔 데이터가 없기 때문에 문제가 발생할 수 있다. 그렇다고 기존의 서버는 이미 트래픽 때문에 문제가 발생하고 있으니 데이터 복제를 시도할 수 없을 것이다. 하지만 비즈니스 로직을 백엔드딴에 분리해둔 상태라면 아래의 이미지처럼 백엔드 서버를 늘리기는 쉽기 때문에 대처가 쉬워진다. 이렇게 비즈니스 로직을 DBMS에 두면 빠르게 대응이 불가하다는 단점이 존재한다.

그리고 프로시저가 항상 transparent 한 것은 아니며, 소스코드에 로직이 있는 것보다 손이 많이 갈 수 있다고 한다. 프로시저를 수정할 때 기존의 서비스가 돌아가는 것을 감안하면 결국 새로운 프로시저를 만들고 백엔드딴에 해당 프로시저로 변경을 해주면 매 백엔드마다 빌드와 배포를 다시 해줘야 하기 때문이다. 추가적으로 transparent가 좋은 것만은 아닌데, 그 이유는 DBMS 에 프로시저를 수정하면 모든 백엔드들에 적용되므로, 문제가 생겼을 때 그 문제를 롤백하기 전까지 생겼던 문제 상황이 겉잡을 수 없이 커질 수 있다. 하지만 백엔드 비즈니스 로직 중 하나만 시범적으로 수정했을 때 문제 상황을 핸들링 하기 훨씬 쉬워지게 된다.

재사용이 가능하다는 점은 프로시저의 장점으로 소개했지만 이건 단점이 될 수도 있다. 여러 백엔드 언어 기반 서버에서 재사용이 가능하나, 특정 백엔드 서버에서 과부하를 준다면 DBMS가 먹통이 될 것이다. 이런 경우엔 데이터 서비스를 위한 별도의 서버를 두고 특정 서비스의 트래픽을 중단시켜서 중간에 중재할 수 있게끔 처리하는 것이 좋다. 

 

그럼 네트워크 트래픽 문제는 프로시저의 장점이라고 소개했다. 하지만 소스코드 상에서 비즈니스로직을 처리하더라도 응답 속도를 늘릴 수 있는 방법이 존재한다고 한다. (쓰레드 풀, 논블락 등)

그외에도 캐싱을 사용하여 응답 속도를 늘릴 수도 있다고 한다. 백엔드 서버와 DBMS 사이에 별도의 캐시 서버를 두어 매번 DB에서 처리될 법한 것을 최초의 실행만 시키고 저장시켜두었다가 다시 호출되면 그 저장된(캐싱된) 정보를 반환해주는 것이다.

마지막으로 프로시저가 민감한 정보를 제한하여 보안을 높일 수 있다고 했지만, 사실상 마음만 먹으면 민감한 정보를 직접적으로 프로시저에 반환되게 만들 수 있기 때문에 완벽한 제한이 되는것은 아니다. 오히려 DB나 테이블에 접근을 막아버리면 개발/CS 업무에 신속함이 떨어지게 되어 문제 발생 대처가 힘들다. 

보안상의 문제라면 담당자나 개발자에게만 DB, 테이블 권한을 부여하여 민감한 정보는 별도로 암호화하는 것이 좋다고 한다. 추가적으로 보안서약서 등을 통해 정책적으로 보안을 강화하는 것을 추천한다고 한다. 

 

일반 SQL 과 저장 프로시저 동작 방식

 최초 일반 SQL 동작 방식
구문분석(오류 확인) -> 개체명 확인(데이터베이스, 테이블, 컬럼 확인) -> 사용자 권한 확인 -> 최적화(인덱스 등 성능을 고려한 경로 결정) -> 컴파일 및 실행계획 등록 -> 캐싱 -> 실행

캐싱 되기 위해선 한 글자라도 다르지 않고 동일해야 캐싱된 쿼리문을 가져올 수 있다.

 캐싱된 일반 SQL 동작 방식
실행 계획 -> 메모리 캐시 확인 -> 실행

위의 최초 과정과 달리 많은 과정이 생략되어 당연히 다음 명령의 실행 속도가 빨라질 것이다.

 

 최초 저장 프로시저 저장 방식
구문 분석 -> 지연된 이름 확인(해당 테이블의 존재 여부와 무관하게 정의가 가능하나 컬럼명이 틀리면 오류가 발생) -> 프로시저 생성 권한 확인 -> 시스템테이블에 등록(저장 프로시저의 이름과 코드가 관련 시스템 테이블/카탈로그 뷰에 등록)  

 최초 저장 프로시저 동작 방식
저장 프로시저 첫 실행 -> 카탈로그 뷰에서 개체 이름 확인 -> 사용 권한 확인 -> 최적화 -> 컴파일 및 실행계획 등록 -> 캐싱 -> 실행

저장 단계에서 구문 분석(오류분석)이 끝났기에 따로 그 부분을 제외하곤 일반 SQL문의 동작 순서와 유사하다. 그리고 개체 이름 확인 단계는 해당 개체가 존재하는지만 확인하게 된다.

 저장된 저장 프로시저 동작 방식
실행 계획 -> 메모리 캐시 확인 -> 실행

 

위처럼 방식을 본다면, 저장 프로시저를 사용할 때 성능이 향상되어야 할 것이다. 하지만 실제로 대부분의 경우 성능이 향상되지 않는다고 한다.

저장 프로시저는 처음 실행 시 최적화가 이루어져 인덱스 사용 여부가 결정되어 버리는데, 이때 데이터를 조금 가져오도록 설정한다면 그 상황에 맞는 인덱스를 사용하도록 최적화되어 컴파일 됐을 것이다. 그러나 이후의 실행에서 많은 데이터를 가져올 수 있게끔 바뀌어도 일반 쿼리문처럼 최적화되어 컴파일 되지 않고 전의 컴파일된 그대로를 실행시켜 버려 성능에 문제가 발생한다고 한다.

그렇다면 이를 방지하기 위해선 저장 프로시저를 다시 컴파일 해주는 로직이 추가로 필요하다. 이때 인덱스 사용여부가 불분명하면 저장 프로시저를 생성한 시점에서 실행 시마다 다시 컴파일이 되도록 설정해버린다.

 

 

stored procedure 와 stored function의 차이

• 저장 프로시저
  • 일련의 작업의 쿼리를 함수로 만들어 놓은 것
  • 리턴값이 많거나 없을수도 있음
  • 서버에서 실행되므로 속도가 빠른 편
  • 실행, 처리 등의 비즈니스 로직을 수행 할 때 주로 사용
  • SQL 문 안에서 호출 불가, 별도로 call 키워드를 사용하여 호출해줘야 함
  • 프로시저 안 트랜잭션 사용 가능
• 함수
  • 여러 작업을 위한 기능
  • 리턴값이 꼭 들어가야 하며 하나의 값만 리턴 (return)
  • 클라이언트에서 실행되므로 느린 편
  • 간단한 계산이나 수치 결과에 사용
  • SQL 문 안에서 호출 가능
  • 함수 안 트랜잭션 사용 불가

큰 차이점을 들면 위와 같고, RDBMS 에 따라 조금씩 다룰 순 있다고 한다. 위에 기재된 내용 외적으로, 알아둘 것은

1. 다른 프로시저나 함수 안에서 프로시저나 함수를 호출할 수 있는가
2. 테이블을 반환할 수 있는가
3. precompiled execution plan을 만드는가 (미리 컴파일해서 실행 계획을 만드는지의 여부)
4. try catch 를 사용할 수 있는가

등의 차이점을 RDBMS 마다 확인하는 것이 좋다고 한다.

 

 

delimiter $$
create procedure 프로시저명(in/out 매개변수 타입)
begin
	declare 변수명 타입;

	-- 프로시저 내용
end
$$
delimiter ;

stored function 과 크게 다를 바 없이 정의가 가능하다. 하나 다른 점은 함수에선 returns 키워드를 사용하여 리턴되는 타입을 적어줬으나 프로시저는 리턴값을 파라미터에 out 키워드와 함께 적어준다.

저장 프로시저는 파라미터 변수에 in/out/inout 키워드를 추가로 넣어줘서 의미를 확장 시킬 수 있다.

• In (기본값)
  • 프로시저에 값을 전달하고 내부에서 값을 수정할 수 있음 (내부에선 원본값의 복사본을 활용하는 것)
  • 원본값은 프로시저가 끝난 이후에도 유지
  • 호출자가 수정 불가
• Out
  • 프로시저의 값을 호출자에게 반환
  • 초기값은 프로시저 내 Null
  • 프로시저가 반환될 때 새로운 값이 호출자에게 반환되고 프로그램이 시작될 때 Out 파라미터의 초기값에 접근 불가함
• InOut
  • 호출자에 의해 변수가 초기화되고 프로시저에 의해 수정되는 in+out
  • 프로시저가 반환될 때 프로시저가 변경한 사항은 호출자에게 반환

 

stored function 에서도 그랬지만 DBeaver 에서 프로시저도 저장되지 않는 이슈가 있는지 mysql cmd 을 사용하여 프로시저를 정의했다.

위의 예제는 두 수를 입력 받고 두 수의 곱셈을 반환하는 프로시저이다. 

 

call 프로시저명(파라미터);

프로시저의 호출은 함수와 유사하나, 앞에 call 키워드를 붙여줘야 한다. 이렇게 call 을 사용하여 호출해주면 데이터베이스 카탈로그에서 프로시저명을 찾아 컴파일되고 메모리 캐시에 저장한 다음 프로시저를 실행시키는 것이다.

out 으로 지정해준 반환값은 @와 함께 변수에 담아줄 수 있고, 해당 변수를 확인하고자 한다면 위처럼 써주면 된다.

 

아래의 프로시저는 입력받은 두 매개변수를 서로 바꿔주는 프로시저이다. 이때 inout 키워드를 넣어서 입력 받을 때도, 출력 받을 때도 사용되는 매개변수임을 나타내준다.

 

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() 로 호출해준다.

 

delimiter $$
create procedure change_nickname(user_id int, new_nick varchar(10))
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
$$

강의에서 나온 사용자가 프로필 닉네임을 변경하면 이전 닉네임을 로그에 저장한 뒤 새 닉네임으로 업데이트 하는 프로시저이다. insert into 에 values 값을 저렇게 서브쿼리 형식으로 넣을 수 있구나 처음 알았다. 나는 @를 사용하여 변수에 원본 닉네임을 넣고 아래처럼 처리해주었다.

delimiter $$
create procedure update_new_nickname(in user_id int, in new_nn varchar(10))
	begin
		select nickname into @origin_nn from users where id = user_id;
		insert into nickname_logs(prev_nickname) values (@origin_nn);
		update users set nickname = new_nn where id = user_id;
	end
$$
delimiter ;

call update_new_nickname(2, 'Third');
call update_new_nickname(2, 'Fourth');
call update_new_nickname(2, 'HaHa');

내가 원하는 값이 잘 나왔다.

그밖에도 stored procedure은 조건문을 통해 분기처리나 반복문, 에러 핸들링 등의 로직을 정의하여 사용이 가능하다.

 

프로시저 목록

show procedure status;

만들어준 프로시저 목록을 확인 가능하다. 

 

프로시저 관련 정보 확인

show create procedure 프로시저명;

 

drop procedure 프로시저명
[if exists 프로시저명];

 

프로시저가 대강 뭔지는 알고 있었지만 이렇게 따지고 보니 CS적으로 많이 성장한 것 같다. 실무에서 사용하게 될 때 한번 잘 고려해봐야겠다.