무럭무럭 STUDY - PREPARED STATEMENT (MySQL)

PreparedStatement

Prepared statement는 orm 도구에서 많이 사용되는 것으로 binding query라고도 불린다. 이는 하기와 같이 값이 바인딩 되는 변수에 물음표를 사용해서 SQL 문장을 작성하고 prepare 단계를 먼저 수행한 후 변수의 값을 바인딩해서 쿼리를 실행하는 형태이다.

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

 

이처럼 prepared statement를 사용하여 쿼리를 작성하는 경우 빠르고 간단하게 sql injection 방지 처리를 구현할 수 있다. 그리고 쿼리 파싱 비용을 줄인다. 예를 들어 동일한 prepared statment 객체를 이용하는 경우 1번째 실행에서 쿼리의 parse-tree를 생성해서 cache하기 때문에 2번째 실행 이후부터는 쿼리 파싱 비용이 감소하는 것이다.(execution-plan은 cache되지 않음.) 하지만 parse-tree를 caching하는 메모리 공간이 추가로 필요하기 때문에 메모리 사용량이 조금 더 높아지고 1번째 쿼리 실행 시에는 실제 쿼리 실행 이전에 prepared statment 단계가 필요하기 때문에 mysql 서버로부터 통신이 한 번 더 필요해진다.(network round-trip)

tip)
prepare가 아닌 일반적인 statment를 실행할 때는 mysql server로 요청이 1번만 전송되지만, prepared statment는 2번의 호출이 필요하다.

 

MySQL Server에서의 Prepared Statment

mysql server는 cache된 prepared statment는 하나의 커넥션 내에서만 공유가 되기 때문에 각각 100개의 커넥션에서 동일한 쿼리를 수행해도 prepared statment 객체는 1개가 아닌 100개 필요하다. 그리고 mysql server의 prepared statment는 클라이언트 측과 서버 측 prepared statment로 구분이 된다. 둘 모두 sql injection을 예방하는 효과는 있지만 client-side prepared statment는 실제 생각하는 prepared statment 기능과는 다르다.

tip)
client-side prepared statment는 mysql server가 prepared statment를 제공하지 않던 시절에 jdbc 표준인 prepared statment 기능을 emulation하기 위한 기능이었다.

 

mysql server의 java connection에서 실질적인 prepared statment인 server-side prepared statment는 기본값으로 비활성화 되어 있다. 즉 useServerPreStmts 값을 true로 활성화해야 사용할 수 있게 된다. 

tip) 
기본적으로 java connection에서는 기본값으로 비활성화 되어 있기 때문에, 코드에서 prepared statment 객체를 사용한다고 해도 사용하는 것이 아닌 경우가 있으니 주의하자. 물론 특정 orm에선 useServerPreStmts 옵션을 기본적으로 활성화되도록 하는 orm들이 있다. 

 

MySQL에서 PreparedStatment와 ConnectionPool의 관계

mysql server의 preparedStatment는 하나의 Connection 내에서만 공유된다. 즉 하나의 connection 안에서만 preparedStatment Cache가 재사용될 수 있는 것이다. 때문에 connection이 많으면 많을수록 더 많은 preparedStatment 객체가 필요해진다. 예를 들어 mysql server의 전체 connection 갯수가 1,000개이고 unique한 query pattern이 100개를 프로그램에서 사용한다고 가정하면 mysql server에서는 약 100,000개 정도의 preparedStatment 객체를 메모리에 캐시해야 한다.

 

또 mysql server는 약 16,000개 정도의 preparedStatment까지만 캐시할 수 있도록 설정되어 있다. 즉 16,000개의 캐시를 넘어서면 mysql server는 LRU 패턴을 이용해 캐시를 계속 정리하게 되고 클라이언트에서는 다시 preparedStatment 함수를 호출해서 쿼리 파싱 요청을 하게 된다. 그리고 connection pool이 자주 새로 생성되면 생성될수록 새로 파싱을 해야하기 때문에 쿼리 파싱 비용도 계속 높아질 것이다. 따라서 connection 갯수를 최소화하고 connecton 생명 주기를 최대한 길게 가져가야 preparedStatment가 실질적인 도움을 줄 수 있다.

tip)
LRU 패턴은 캐시 제거;cache eviction 정책 중 하나로 오랫동안 사용되지 않은 데이터를 우선적으로 제거하는 캐시 교체 알고리즘이다.

 

결론적으로 connection이 많아지고 쿼리 패턴이 다양해지면 cache eviction이 발생하고 쿼리 파싱 횟수는 계속 증가하게 되므로 적절히 쿼리 파싱 횟수랑 preparedStatment 캐시 횟수를 확인해서 적절한 Max preparedStatment Count 변수를 설정하는 것이 필요하다. 그리고 쿼리가 복잡하면 복잡할수록 파싱 비용을 절약할 수 있는 preparedStatment가 도움이 되겠지만 일반적인 OLTP 환경은 쿼리들이 단순하기 때문에 preparedStatment의 장점이 줄어들 수 있다.

tip)
OLTP 환경은 실시간 트랜잭션을 효율적으로 처리하기 위한 시스템으로 대규모 사용자들이 데이터를 읽고 쓰는 작업을 수행한다. 이는 짧고 빈번한 트랜잭션, 실시간 처리, 데이터 무결성 보장 등이 특징이고 주로 은행 시스템, 온라인 쇼핑몰 등에서 사용된다.

 

정리

server-side preparedStatment는 예상한 것처럼 성능을 크게 높여주지 않는다. 오히려 메모리 사용량만 높이고 필요한 preparedStatment 갯수 대비 max preparedStatment count가 너무 부족하면 쿼리 파싱 효율도 많이 떨어진다.  이처럼 mysql server에서는 server-side preparedStatment가 부작용이 심한 경우가 많다. 하지만 client-side preparedStatment는 server-side의 부작용을 가지지 않으면서 sql injection을 예방할 수 있는 효과도 있다. 

tip)
일반적으로 AWS RDS 같은 소규모 서버들은 일반적으로 메모리가 적기 때문에 preparedStatment가 캐시를 위해 사용할 수 있는 메모리 공간을 확보하기가 어렵다. 오히려 preparedStatment의 parses-tree를 저장할 공간을 innoDB 버퍼풀로 전환하면 더 효율적인 부분이 될 수 있다.

 

---

reference

Real MySQL - 이성욱님, 백은빈님