[Mysql] Prepared Statement

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)

 

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 기능과는 다르다.

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

 

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가 실질적인 도움을 줄 수 있다.

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

 

정리

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