Prepared Statement

 

 

1. 들어가며

Prepared Statement는 주로 SQL Injection이나 쿼리 파싱 비용을 줄이기 위해 사용한다. 정도로 어렴풋하게 알고 있는 경우가 많은데요. 오늘은 Prepared Statement에 대해 더 자세히 알아보고 잘 활용하는 방법에 대해 써보고자 합니다!

 

 

2. Prepared Statement

정의

Prepared Statement라는 단어를 해석해보면 '준비된 문장' 정도로 해석이 가능합니다. 그런데 무엇이 준비되었다는 뜻일까요? 바로 쿼리가 준비되었다는 의미입니다.

이미 쿼리실행계획 분석과 컴파일이 완료되어서 DBMS의 캐시에 준비되어있는 쿼리를 사용한다는 뜻입니다.

 

pstmt = connection.prepareStatement("SELECT * FROM matt WHERE id=?");
pstmt.setInt(1, 1234);
rs = pstmt.executeQuery();

 

장단점

장점

• SQL Injection 방지
• 쿼리 파싱 비용 감소 (2번째 이후 실행)

단점

• 메모리 사용량 증가 (파싱을 하기 때문)
• 2번의 Network round-trip 필요 (첫번째 실행)
• Execution-plan은 캐시되지 않음, Parse-Tree만 캐시됨
• 캐시된 PreparedStatement는 컨넥션내에서만 공유됨

저는 주로 Spring을 사용하므로 Java JDBC를 사용하는 경우를 살펴보겠습니다. Java JDBC의 경우 prepare 단계를 먼저 수행하고 변수에 있는 SQL을 실행합니다.

 

구분

MySQL의 PreparedStatement에는 Client Side와 Server Side로 나뉘는데요.

• client-side는 mySQL prepared statement가 없을 시 jpa에서 임의로 구현한 것입니다.
• 물론 둘 다 SQL-Injection은 막을 수 있습니다.
• server-side prepared statement는 default로는 사용하지 않습니다.
• ORM에서는 해당 값을 true로 설정해주는 경우가 많습니다.

모범 사용 예시 및 실익

두 사례를 보고 PreparedStatement를 사용하는 모범 용례에 대해 살펴보겠습니다.

케이스 1)

for(int idx=0; idx<100; idx++) {
    PreparedStatement pstmt = conn.prepareStatement("SELECT .. WHERE id=?");
    pstmt.setInt(1, targetUserIds[idx]);
    pstmt.executeQuery();
    ...
}

케이스 2)

PreparedStatement pstmt = conn.prepareStatement("SELECT .. WHERE id=?");
for(int idx=0; idx<100; idx++) {
    pstmt.setInt(1, targetUserIds[idx]);
    pstmt.executeQuery();
    ...
}

 

두 코드의 차이는 반복문 안에서 preparedStatement를 선언하는 것과 외부에서 선언하는 것의 차이인데요. 물론 대부분 두 번째 처럼 하겠지만(GC나 heap 선언의 비효율성) preparedStatement에는 그 외에도 다른 차이가 있습니다.

• case1: 사용 결과를 한번 사용하고 버리는 형태 → 매번 실행 때마다 2번씩 실행하여 매번 parse하여 메모리에 저장해야 됩니다. 즉 재사용되는 효과를 아예 얻을 수 없습니다. 당연한 거 아닌가 싶겠지만 ORM로 하다보면 이렇게 실행될 때가 많습니다. 커넥션 풀 사용하기 때문에 비효율적일 때도 많아서 본인의 프레임워크에서 어떤지 잘 살펴봐야 합니다.
• case2: 즉 이와 같이 만들어야 합니다.

preparedStatement vs Connection Pool

preparedStatement는 Connection Pool과 요구 사항이 상충될 수 있는데요. 둘의 관계가 어떤지 살펴보겠습니다.

MySQL 서버의 PreparedStatement는:

• 하나의 Connection 내에서만 공유됩니다.
• 이를 통해 Re-parsing 비용 최소화할 수 있습니다.
• 다만 모든 PreparedStatement는 Connection 단위로 캐시되어야 한다는 제약을 가집니다.

따라서 전체 커넥션이 5000개이고, 필요한 쿼리 패턴이 100개인 경우:
500,000개의 PS객체가 MySQL 서버에 저장되어야 합니다. (max_prepared_stmt_count=16382)

쿼리의 복잡도에 따라서:

• 매우 복잡하면 PreparedStatement가 도움되지만
• 단순하면 PreparedStatement의 장점이 경감됩니다. (파싱이 단순하기 때문)

 

메모리 사용량 vs CPU 사용량:

• AWS RDS는 매우 소규모 서버들 사용 (db.t3.medium & db.r5.large)
• 일반적으로 메모리 적음

따라서 메모리가 적은 경우 캐시 설정이 주로 16382개이기 때문에, 메모리 부족으로 LRU로 계속 캐시가 갱신되며 비효율이 초래될 수 있습니다. 커넥션 풀이 새로 생성될 수록 쿼리 파싱 비용이 늘어납니다.

커넥션이 많고 쿼리 패턴이 많아질수록 캐싱 비용만 늘어날 수 있습니다. 쿼리가 복잡하면 파싱 비용이 크기 때문에 도움이 되는 것은 사실입니다.  하지만 실무에서 대부분의 쿼리는 매우 단순할 때가 많습니다. 따라서 장점이 경감될 수 있습니다.

요즘 낮은 사양의 rds를 사용하기 때문에 메모리가 부족할 수 있습니다.

→ prepared statement의 파싱 트리 사용할 걸 innoDB의 버퍼풀로 전환하면 훨씬 효율적이었을 것입니다.

 

결론

MySQL 서버에서는 Server-Side PreparedStatement가 부작용이 심한 경우가 많기에 Client-Side만 사용하는 것을 권장합니다. 예상하는 것처럼 성능을 크게 높여주지도 않고, 반면 메모리를 꽤 많이 소비하게 됩니다.