[MySQL] PreparedStatement(Binding Query)

  

 

Statement

Java 에서 쿼리문을 사용할 때 preparedStatement 를 사용해보신 경험이 있을 것 입니다.

PreparedStatement는 java.sql 패키지에 있는 Statement 를 상속받은 interface 인데요!

Statement 는 SQL을 실행할 때 사용하는 interface 입니다.

 

PreparedStatement

Binding Query

PreparedStatement 는 Binding Query 라고 많이 알려져 있습니다.

Java 의 jdbc를 이용해서 코딩하는 경우 아래처럼 PreparedStatement 객체를 이용하여 코드를 작성할 수 있습니다.

pstmt = connection.prepareStatement("select * from matt where id = ?");
pstmt.setInt(1, 1234);
rs = pstmt.executeQuery();

 

장점

• SQL injection 방지
• 쿼리 파싱 비용 감소(두 번째 이후 실행)
  • 동일 preparedStatement 객체를 이용해서 2번째 실행 이후부터 파싱 비용 절감

 

단점

• 메모리 사용량 증가
  • preparedStatement 가 사용하는 parse tree 를 캐싱하는 메모리 공간이 추가적으로 필요
• 2번의 network round-trip 필요(첫 번째 실행)
  • 첫 번째 쿼리 실행시에는 실제 쿼리 실행 이전에 먼저 preparedStatement 단계가 필요하기 때문에 MySQL 서버로의 통신이 한 번 더 필요
  • Statement 를 사용하면 1번만 전송함
• execution-plan 은 캐시되지 않음, parse-tree 만 캐시
• 🚨 캐시된 preparedStatement 는 커넥션내에서만 공유
  • 100개의 커넥션에서 동일한 쿼리를 preparedStatement 방식으로 실행한다고 해도 MySQL 서버에 생성되는 preparedStatement 객체를 1개가 아니라 100개 필요

 

SQL Injection 방지

Statement를 사용하면 아래와 같은 문법으로 작성하게 됩니다.

String sql = "select * from user where id ="+id+" and password = "+password;

 

만약에 id에 1 or 1=1 -- 를 입력하고 password에 1을 입력하게 되면 아래와 같은 SQL 문이 실행될 것 입니다.

select * from user where id = 1 or 1=1 -- and password = 1;

 

-- and password = 1 부분은 주석으로 처리되기 때문에 password 조건은 무시하게 됩니다.

이러한 이유로 statement 는 SQL Injection 에 취약합니다…!

 

반면에 PreparedStatement 를 사용하게 되면 아래와 같은 형식으로 작성하게 되는데

String sql = "select * from user where id = ? and password = ?";

 

여기에서 ? 를 필드값으로 인식하고 하나의 필드 값으로 인식하게 됩니다.

따라서 개발자가 의도한 바와 동일하게 아래와 같은 SQL 문이 실행됩니다.

select * from user where id = "1 or 1 --" and password = 1;

 

PreparedStatement의 비밀 🤫

MySQL서버의 preparedStatement 는 다른 DBMS에는 없는 몇 가지 비밀들이 존재합니다..!

 

MySQL의 PreparedStatement

• client-side preparedStatement
  • server가 preparedStatement 기능을 제공하지 않았던 시절에 JDBC 표준이 preparedStatement 기능을 Emulation 하기위한 기능이었음
  • 실제 개발자들이 기대했던 preparedStatement 기능은 아니였음을 의미
• server-side preparedStatement
• 모두 SQL Injection 방지(preparedStatement만 사용한다면)

 

JDBC Server-side PreparedStatement

• useServerPrepStemts=TRUE 인 경우에만 작동
  • useServerPrepStemts=FALSE 가 기본 값
• ORM 에서는 TRUE로 기본 설정되는 경우가 많음

 

PreparedStatement 예시

MySQL 서버에서 PreparedStatement 객체를 처음 생성한 이후에 MySQL 서버에서 캐시된 parse tree 를 재활용하기 위해서는 클라이언트에서 생성된 PreparedStatement 객체를 삭제하지 않고 계속 가지고 있다가 필요할 때 PreparedStatement 를 이용해서 쿼리를 실행해야 합니다.

 

예시 1)

for(int i = 0; i < 100; i++) {
    // 계속 prepareStatement 호출...!
    PreparedStatement pstmt = conn.prepareStatement("select ... where id = ?");
    pstmt.setInt(1, userIds[i]);
    pstmt.executeQuery();
    ...
}

 

prepareStatement 함수 호출 결과를 한번 사용하고 매번 loop 를 반복할 때마다 prepareStatement 를 새롭게 호출하고 있습니다.

 

이러한 형태로 prepareStatement 를 실행하게 되면 MySQL 클라이언트에서는 매번 반복 loop 를 실행할 때마다 2번씩 서버랑 통신하게 되고 MySQL 서버는 100번 호출할 때 마다 매번 쿼리를 parse tree 에 캐시하게 됩니다.

 

즉, MySQL 서버에서는 parse tree 가 100번 캐시되지만, 한 번도 hit 할 수 없는 구조가 됩니다…!

따라서 예시2 처럼 작성해야 합니다!!

 

매번 반복 loop 를 실행할 때마다 2번씩 서버랑 통신하게 되고?

• 첫 번째 쿼리 실행시에는 실제 쿼리 실행 이전에 먼저 preparedStatement 단계가 필요하기 때문에 MySQL 서버로의 통신이 한 번 더 필요합니다.

 

예시 2)

PreparedStatement pstmt = conn.prepareStatement("select ... where id = ?");
for(int i = 0; i < 100; i++) {
    pstmt.setInt(1, userIds[i]);
    pstmt.executeQuery();
    ...
}

 

PreparedStatement와 Connection Pool의 관계

MySQL 서버(server-side)의 PreparedStatement 는 하나의 Connection 내에서만 유효 합니다.

이말은 하나의 Connection 안에서만 PreparedStatement 캐시가 재사용 될 수 있다는 의미 입니다.

 

PreparedStatement는 쿼리가 계속 파싱되는 비용을 최소화하는 장점을 가지고 있지만,

PreparedStatement는 Connection 단위로만 캐시될 수 있기 때문에, Connection이 많으면 많을 수록 더더더더 많은 PreparedStatement 객체가 필요해 집니다!!(PreparedStatement α Connection)

 

또한, Connection Pool의 Connection 이 새롭게 생성되면 생성될수록 매번 새롭게 파싱을 해야하기 때문에 쿼리 파싱 비용도 계속 증가하게 됩니다.

 

따라서 Connection 갯수를 최소화하고 수명 주기를 최대한 길게 해야 PreparedStatement가 실제적으로 도움을 줄 수 있습니다.

 

Connection이 많고 쿼리 패턴이 다양한 경우

Cache Eviction 이 발생하고 쿼리 파싱 횟수는 계속 증가하게 되는 문제점만 생길 수 있습니다.

 

따라서 적절한 쿼리 파싱 횟수와 PreparedStatement 캐시 갯수를 확인해서 max_prepared_stmt_count 변수를 설정해야 합니다.^^

 

전체 Connection이 5,000개이고, 필요한 쿼리 패턴이 100개인 경우

• 5000 * 100 개의 PreparedStatement 객체가 MySQL 서버에 저장(캐싱)되어야 합니다.
  • max_prepared_stmt_count 변수를 통해서 제어 가능(default: 16382)

 

쿼리 복잡도

• 쿼리가 복잡한 경우 PreparedStatement 가 이득
• 쿼리가 단순한 경우 PreparedStatement 장점이 경감
  • 일반적인 OLPT(Online Transaction Processing) 환경의 MySQL 서버에서 사용되는 쿼리들은 매우 단순!

 

AWS RDS의 낮은 사양의 서버들

db.t3.meduim, db.r5.large … 는 일반적으로 메모리가 항상 부족합니다..

그래서 PreparedStatement 가 캐시를 위해서 사용할 수 있는 메모리 공간을 확보하기에 조금 어려울 수 있습니다.

오히려 PreparedStatement parse tree 를 저장할 공간을 InnoDB 버퍼풀로 전환한다면 더 효율적으로 관리할 수 있습니다.

 

정리

• server-side PreparedStatement 는 부작용이 심한 경우가 많음
  • 예상하는 것 보다 성능을 크게 높여주지는 않음
  • 메모리를 꽤 많이 소비(OOM 유발..)
  • max_prepared_stmt_count 부족 시, 쿼리 파싱 경감 효과 떨어짐
• client-side PreparedStatement 를 권장!

 

참고

• https://jaehoney.tistory.com/179
• https://www.inflearn.com/courses/lecture?courseId=333931

https://www.inflearn.com/courses/lecture?courseId=333931&unitId=226571