Stored Procedure - 저장 프로시저

오늘은 Stored Procedure ( 이하 SP ) - 저장 프로시저에 대해 알아보자.

회사에서 SP를 사용하기에 궁금증이 생겼었다. (여기선 MSSQL / T-SQL 의 기준으로 용어들을 설명한다.)

 

SP는 무엇인가 ?

MS Docs 에 SP에 대한 정의로

"SQL Server의 저장 프로시저는 하나 이상의 Transact-SQL 문 그룹이거나

Microsoft .NET Framework CLR(공용 언어 런타임) 메서드에 대한 참조입니다." 라고 나온다.

솔직히 CLR 메서드에 대한 참조는 무슨 말인지 모르겠고, 단순히 여러 SQL문이 존재하는 공간(파일)이라고 보겠다.

 

SP와 다른 프로그래밍 언어의 유사점

1. SP를 호출하는 프로그램으로부터 입력 매개 변수를 받아 여러 출력 값을 반환한다.

2. DB에서 프로그래밍 언어의 문법을 사용할 수 있다. (다른 프로시저도 호출할 수 있다.)

3. 호출 프로그램에 상태 값을 반환해서 성공/실패 원인을 나타낸다.

 

SP의 이점

1. 서버/클라이언트 네트워크 트래픽 감소

 

SP의 명령은 단일 일괄 처리 코드로 실행된다. 즉 한번에 여러 개를 실행한다는 것이다. 이는 SP가 제공하는 코드 캡슐화로 인해 가능한 것인데, 이게 없으면 모든 개별 코드 줄이 네트워크를 통해서 요청/응답해야한다.

사용되는 경우가 워낙 광범위해서 예를 들기가 조금 애매한데, 단순히 이해를 위해서 억지로 예를 들어 보겠다.

예를 들어, 클라이언트가 게시물을 조회한다고 가정하자.

그럼 게시물 내용은 게시물 테이블에서, 유저 정보는 유저 테이블에서, 댓글은 댓글 테이블에서, 추천 글은 추천 테이블에서 등등 가져와야한다.

이러려면 모든 테이블을 join 해서 가져올 수도 있겠지만... 생각만 해도 무언가 머리가 아파온다. 

차선책으로 각각의 테이블을 조회하는 쿼리를 만들면 된다. 4개의 쿼리! 이러면 다만 DB에 접근하는 트래픽이 많아진다.

여기서 SP를 사용한다고 하면, 해당 화면과 관련된 SP를 만들고, 안에서 각각의 쿼리 실행하고 결과를 반환하면 된다.

물론 DB 안에서 쿼리를 실행하는 것은 같지만 DB에 접근하는 트래픽은 1번이었던 것이다.

이로써, 네트워크 트래픽이 감소되었다.

 

* 오해하기 쉬운데, 데이터베이스도 서버다. 그러니 여기서 서버/클라이언트 얘기는 진정한 Frontend를 말할 수도 있겠지만, Backend의 서버와 여기서 접근하는 DB를 말할 수도 있다. 우리가 통상 아는 웹서버가 클라이언트가 되고, DB가 서버가 되는 것.

 

2. 보안 강화

 

SP를 사용하면 여러 가지 측면에서 보안이 강화된다고 한다.

데이터베이스 개체에 대한 사용 권한을 조정할 수 있다고 하는데, 이는 잘 이해가 되지 않는다.

다만, SP를 통하지 않으면 데이터베이스 개체 수준에서 이를 통제해줘야 하지만 SP를 통하면 그럴 필요가 없어서 보안 계층이 간소화된다는 장점도 있다고 한다.

 

다른 장점으로는 네트워크를 통해 SP를 호출하면, 실행할 호출만 표시가 된다. 따라서 사용자가 악의적으로 테이블 및 데이터베이스 개체 이름을 보거나 T-SQL 문을 포함하거나 중요한 데이터를 검색할 수 없다.

쉽게 말하면 SQL을 실행하기 위해 SP만 호출되기 때문에, 서버 코드에서 DB 테이블이 어떤 구조로 되었는지, SQL문을 강제로 넣는다거나, 데이터를 본다거나 등이 안된다.

 

또한 SP에 파라미터를 사용하면, SQL injection을 행사할 수 없다. 파라미터 입력은 실행 코드가 아니라 리터럴 값으로 처리되므로 T-SQL 문에 명령을 삽입해서 보안을 손상시키기 어렵다.

 

마지막으로 SP를 암호화해서 원본 코드를 난독 처리할 수도 있다. 이는 SQL Server Encryption 에 대해 알아보자.

 

3. 코드 재사용

 

반복적으로 사용되는 데이터베이스의 작업 코드는 SP에서 캡슐화해서 사용할 수 있다. 이는 SP가 SP를 호출할 수 있기 때문에 가능한 것으로 보인다.

 

4. 간편한 유지 관리

 

클라이언트 애플리케이션에서 SP를 호출하고 데이터베이스 작업을 데이터 계층에 유지하면 기본 데이터베이스의 모든 변경 내용에 대해 SP만 업데이트하면 된다. 애플리케이션 계층은 별도의 계층으로 유지되므로 데이터베이스 레이아웃, 관계 또는 프로세스에 대한 변경 내용을 알 필요가 없다.

이 말인 즉슨, 서버에서는 SP를 호출하면서 파라미터를 넘기고 반환값을 받아서 사용한다. 그러니까 데이터베이스에서 파라미터를 잘 받아서 결과값만 잘 반환해주면, 데이터베이스 단에서 수정사항이 있는건 서버에서 그렇게 신경 쓸 것이 없다는 것이다.

 

5. 성능 향상

 

기본적으로 SP는 처음 실행될 때 컴파일되며, 이후 실행에 다시 사용되는 실행 계획을 만든다. 쿼리 프로세서는 새 계획을 만들 필요가 없으므로 일반적으로 처리 시간이 줄어든다.

 

---

위의 내용은 MS의 공식문서를 참조해서 다소 애매모호한 설명이 많다. (직관적으로 바로 이해되는 것들도 있지만..)

그래서 이것 외에 많은 개발자 분들이 경험과 테스트에 의해 설명한 부분을 참고하면 좋겠다.

본인은 이 글이 인상 깊었다.

https://12bme.tistory.com/54

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