데이터베이스 프로시저 (Database Procedure)

이번 포스팅에선 데이터베이스 프로시저에 대해서 알아보도록 하겠습니다. 

 

면접 봤는데 해당 내용이 나왔고 대답을 못했습니다. 다음 면접을 위해 정리해두는 느낌으로 포스팅 해보도록 하겠습니다. 

 

 

데이터베이스 프로시저

프로시저란 무엇일까요? 

 

간단하게 쿼리 묶음이라고 생각하시면 됩니다. 

 

만약 자주 사용하는 SQL문이 있다면 그것들을 정리해둔 것이죠. 한번 간단한 예시를 상정해보고 코드 예제까지 보여드리겠습니다. 

 

1. "books"라는 테이블에 새로운 책을 입력할 것입니다. 
2. "books"테이블에 특정 책의 수량을 변경할 것입니다. 
3. 데이터가 바뀐 책의 상세 데이터를 요청해보겠습니다. 

 

DELIMITER //

CREATE PROCEDURE ManageBook(IN bookTitle VARCHAR(255), IN bookAuthor VARCHAR(255), IN bookStock INT, OUT bookDetails TEXT)
BEGIN
    DECLARE bookId INT;

    -- 책이 존재하지 않는다면 새로운 책을 삽입합니다.
    INSERT INTO books (title, author, stock)
    SELECT * FROM (SELECT bookTitle, bookAuthor, bookStock) AS tmp
    WHERE NOT EXISTS (
        SELECT title FROM books WHERE title = bookTitle
    ) LIMIT 1;

    -- 책의 ID를 가져옵니다.
    SELECT id INTO bookId FROM books WHERE title = bookTitle;

    -- 책의 수량을 업데이트합니다.
    UPDATE books SET stock = bookStock WHERE id = bookId;

    -- 책의 세부 사항을 가져옵니다.
    SELECT CONCAT('Title: ', title, ', Author: ', author, ', Stock: ', stock)
    INTO bookDetails FROM books WHERE id = bookId;
END //

DELIMITER ;

 

참고로 이 프로시저는 MySQL언어로 되어있습니다. 

 

이렇게 프로시저를 만들었다면 프로시저를 호출해보겠습니다. 

 

CALL ManageBook('The Great Gatsby', 'F. Scott Fitzgerald', 10, @bookDetails);
SELECT @bookDetails;

 

뭔가 프로그래밍 언어에서 함수를 만든 것 같지 않나요? 파라미터 넘겨주는 것도 그렇고 리턴 값을 받아오는 것도 비슷하네요. 

 

프로시저의 장점

1. 쿼리를 한방에 보낼 수 있어서 많이 사용하는 쿼리를 넣어둘 수 있다. 
2. 기본적으로 많은 쿼리를 한방에 처리하는 것보다 성능이 좋다. 
3. 네트워크 트래픽(데이터베이스 내부 네트워크 I/O를 말하는 것입니다.)을 줄일 수 있어서 오버헤드가 적게 발생한다. 
4. 파라미터도 넣을 수 있어서 함수처럼 사용할 수 있다. 리턴 값도 정해줄 수 있다. 
5. 모든 쿼리는 내부적으로 데이터베이스 엔진(MySQL의 경우 InnoDB 스토리지 엔진)을 통해 실행되기 때문에 트랜잭션 처리도 가능하다.

 

 

여기까지 프로시저에 대한 내용은 끝! 표면적인 얘기는 여기까지입니다. 여기서 뒤로 가기를 누르셔도 상관 없습니다. 이제부턴 조금 딥한 내용을 다뤄볼까합니다. 

 

표면적인 내용만 공부하고 땡치는건 제 공부 스타일이 아니라서요 ㅎㅎ

 

 

프로시저 심화

 

프로시저를 JPA에서?

프로시저를 딱 보고 든 생각은 "저건 죽었다 깨어나도 저렇게는 사용 안할거다"라는 것이었습니다. 분명 개발자가 쉽게 쓰기위한 트리거가 있을 것이고 그것이 조금 궁금했습니다. 

 

알아보니 JPA에서 프로시저를 사용할 수 있는 방법을 제공하고 있었습니다. 

 

바로 @NamedStoredProcedureQuery라는 어노테이션입니다. 

 

사용 예시만 보시면 어떻게 사용하는지 느낌이 오실겁니다. 

 

@Entity
@NamedStoredProcedureQuery(
    name = "UpdateUserStatus", 
    procedureName = "updateUserStatus", 
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "userId", type = Long.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "status", type = String.class)
    }
)
public class User {
    // ... fields, getters and setters
}

 

이렇게 프로시저 이름이랑 파라미터에 어떤 값이 들어올건지 미리 세팅을 해두면 EntityManager를 이용해서 호출할 수 있습니다. 

 

StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("UpdateUserStatus");
query.setParameter("userId", 1L);
query.setParameter("status", "active");
query.execute();

 

이렇게말이죠. 

 

어? 겁나 편해보이는데?

 

이렇게 겁나 편한건 항상 고려사항이 있습니다. 

 

주의사항!

보통 프로시저를 호출하는 작업의 경우 특정 ORM에 종속되는 경우가 많습니다. 자바에선 JPA의 구현체인 Hibernate와 EclipseLink같은 것들이 바로 그것이죠. 

 

또한, JPA를 통해서 프로시저를 다루는 것은 에러 핸들링이 굉장히 중요합니다. 우리의 자바 코드 안에서 데이터베이스가 내뱉는 Exception들을 적절히 처리해줘야 한다는 특징이 있습니다. 

 

프로시저는 가끔 성능에 있어서 병목현상이 있을 수 있습니다. 때문에, 최적화를 해야하는데요, 이 부분에 대해서는 뒤에서 더 자세히 다뤄보겠습니다. 

 

 

프로시저의 단점

특정 DBMS에 종속

프로시저는 특정 SQL dialect나 DBMS에 종속되는 구조를 가지고 있습니다. 

 

이는 데이터베이스가 바뀌면 프로시저도 같이 바꾸어야 한다는 것이죠. 자바식으로 말하면 OCP에 위배된다는 것입니다. 

 

그리고 또 치명적인 문제가 있습니다. 

 

유지보수

DELIMITER //

CREATE PROCEDURE ManageBook(IN bookTitle VARCHAR(255), IN bookAuthor VARCHAR(255), IN bookStock INT, OUT bookDetails TEXT)
BEGIN
    DECLARE bookId INT;

    INSERT INTO books (title, author, stock)
    SELECT * FROM (SELECT bookTitle, bookAuthor, bookStock) AS tmp
    WHERE NOT EXISTS (
        SELECT title FROM books WHERE title = bookTitle
    ) LIMIT 1;

    SELECT id INTO bookId FROM books WHERE title = bookTitle;

    UPDATE books SET stock = bookStock WHERE id = bookId;

    SELECT CONCAT('Title: ', title, ', Author: ', author, ', Stock: ', stock)
    INTO bookDetails FROM books WHERE id = bookId;
END //

DELIMITER ;

 

위의 예제를 다시 가져왔습니다. 

 

이 코드 읽기에 어떤가요? 일단 제 눈엔 굉장히 난잡하고 읽기 힘듭니다. 

 

이 문제도 프로시저를 유지보수 하는데 큰 문제가 생깁니다. 

 

차라리 코드였으면 직관적인 변수명, 직관적인 메서드 명으로 읽는 사람 눈에 편하게라도 할 수 있었을텐데 프로시저는 그런 기능따윈 존재하지 않기때문에 관리하기가 여간 까다로운 게 아닙니다. 

 

테스트 (거의) 불가능

또한, 테스트하기 굉장히 힘들다는 문제도 있습니다. 

 

하지만 척 보기엔 테스트가 가능해보이죠? 왜냐하면 JPA를 사용하면 통합 테스트는 할 수 있을 것 같거든요. 

 

여기서 말하는 테스트는 통합 테스트가 아닌 단위 테스트입니다. 프로시저는 여러개의 연산을 하나로 묶어놓은 것인데 하나하나의 연산을 테스트 한다는 것 자체가 모순입니다. 

 

때문에 단위 테스트는 (거의) 불가능합니다. 

 

성능 이슈

프로시저는 다양한 성능 문제에 봉착할 수 있습니다. 쿼리 연산이 많기 때문이죠. 

 

그럼 인덱싱으로 해결할 수 있나? 

 

인덱싱은 부분적으로 해결할 수 있습니다. 추가적인 최적화가 필요합니다. 예를 들어서 올바른 데이터 타입을 정한다거나, 프로시저 안에서 쓸데없는 데이터 변환을 줄인다거나, 락킹 이슈를 피하기 위해서 사용한 트랜잭션을 잘 관리한다던가 등이 있습니다. 

 

데이터베이스 자원 소모가 심하다

보통 실제 환경에선 애플리케이션 서버와 데이터베이스 서버가 분리되어있습니다. 

 

프로시저는 여러개의 쿼리 연산이 들어가기 때문에 데이터베이스 서버의 CPU와 메모리를 많이 잡아먹습니다. 

 

혹여 프로시저를 무분별하게 사용한다면 데이터베이스 서버가 죽어버릴 수도 있습니다. 

 

 

 

여기까지! 데이터베이스 프로시저에 대해서 알아봤습니다. 이제 면접 질문으로 정리하고 마무리하면 될 것 같습니다. 

 

데이터베이스에 대해서는 신입 기준 많이 알고 있다고 생각했는데 아직도 알아야 할 것이 많이 남아있는 듯 합니다. 더 열심히 공부해야겠네요. 

 

긴 글 읽어주셔서 감사합니다. 오늘도 즐거운 하루 되세요~

 

 

출처

Chat GPT 4.0 - OpenAI