Home Statement와 PreparedStatement
Post
Cancel

Statement와 PreparedStatement

Posted Aug 1, 2023
By devjun10
11 min read

데이터베이스의 Statement와 PreparedStatement의 차이점에 대해 학습, 정리한 글입니다. 학습 과정에서 작성된 글이기 때문에 잘못된 내용이 있을 수 있으며, 이에 대한 지적이나 피드백은 언제든 환영입니다.

image







1. Statement vs PreparedStatement

Statement 인터페이스는 JDBC에서 SQL을 실행하기 위한 인터페이스이며, PreparedStatement는 이를 확장한 형태로 미리 컴파일된 SQL을 처리합니다. 이들을 통해 매개 변수화된 SQL 쿼리를 실행하고, 이 쿼리에 매개변수를 추가할 수 있습니다. 둘의 주요한 차이점은 크게 캐싱과 파라미터 바인딩 두 가지인데요, 이에 대해 하나씩 살펴보겠습니다.

1
2
3
4
5
6
7
8
9
10

public interface Statement extends Wrapper, AutoCloseable {

    ResultSet executeQuery(String sql) throws SQLException;

    int executeUpdate(String sql) throws SQLException;

    void close() throws SQLException;

    ......


1
2
3
4
5
6
7
8
9
10
11

public interface PreparedStatement extends Statement {

    ResultSet executeQuery() throws SQLException;

    int executeUpdate() throws SQLException;

    void setNull(int parameterIndex, int sqlType) throws SQLException;
    
    ......

}

Statement, PreparedStatement 인터페이스의 구현체는 데이터베이스 별로 다른 방식으로 제공합니다.









1-1. 캐싱

MySQL에서 쿼리가 실행되기 위해서는 아래의 과정을 거칩니다. 즉 구문 분석, 컴파일, 실행 과정을 거쳐 수행되는데 Statement는 모든 과정을 매번 실행하지만, PreparedStatement는 쿼리가 같은 경우 컴파일을 수행하지 않고 캐싱 된 실행 계획을 이용하게 됩니다.

image

만약 쿼리가 캐시에 있고 실행 계획이 이미 준비되어 있다면 DBMS는 쿼리의 최적화나 추가적인 로우 소스 생성 과정 없이 즉시 해당 실행 계획을 재사용(소프트 파싱)합니다. 반면 쿼리가 캐시에 없거나 해당 쿼리에 대한 실행 계획이 없는 경우, DBMS는 쿼리 파싱 후 최적화 과정을 거쳐 새로운 실행 계획을 만들고 로우 소스를 생성(하드 파싱)해야 합니다.









PreparedStatement가 MySQL에 전달되면 내부에서는 먼저 pre-compile 단계를 거칩니다. 이 단계에서 구문 분석이 진행되며 SQL 쿼리가 Syntax Tree로 컴파일됩니다. 이후 MySQL은 실행에 필요한 Execution Plan을 작성하기 위해 드라이버에서 받은 파라미터를 Syntax Tree에 따라 바인딩합니다. 이 바인딩 된 Syntax Tree는 그 후 컴파일되어 Execution Plan을 생성합니다.

image

Executor는 이렇게 생성된 Execution Plan을 사용해 결과를 얻습니다.









여기에 관해 서버 측에 몇 가지 설정을 줄 수 있습니다. cachePrepStmts를 통해 해당 설정의 사용 유무를 결정하고, prepStmtCacheSize를 통해 캐싱할 쿼리의 수를 잡습니다. 이를 적절히 크게 잡아야 많은 쿼리들을 캐싱할 수 있기에 크기를 잘 조절해 줍니다.

1
2
3

useServerPrepStmts=true
cachePrepStmts = true
prepStmtCacheSize = 500

메모리는 한정돼 있으므로 너무 많은 크기를 잡지 않도록 주의합니다.









또한 MySQL에서 쿼리의 길이에 따라 이를 캐싱할지, 안 할지도 정할 수 있는데요, 기본값으로는 길이가 256보다 긴 SQL에 대해서는 이를 캐싱하지 않습니다.

1

prepStmtCacheSqlLimit = 1024









이 내용을 확인하고 적용된 통계를 보고 싶다면 아래와 같이 stmt가 들어간 변수명을 조회해서 조회할 필드를 찾습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

mysql> show global status like '%stmt%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Binlog_stmt_cache_disk_use            | 0     |
| Binlog_stmt_cache_use                 | 0     |
| Com_stmt_execute                      | 0     |
| Com_stmt_close                        | 0     |
| Com_stmt_fetch                        | 0     |
| Com_stmt_prepare                      | 0     |
| Com_stmt_reset                        | 0     |
| Com_stmt_send_long_data               | 0     |
| Com_stmt_reprepare                    | 0     |
| Mysqlx_stmt_create_collection         | 0     |
| Mysqlx_stmt_create_collection_index   | 0     |
| Mysqlx_stmt_disable_notices           | 0     |
| Mysqlx_stmt_drop_collection           | 0     |
| Mysqlx_stmt_drop_collection_index     | 0     |
| Mysqlx_stmt_enable_notices            | 0     |
| Mysqlx_stmt_ensure_collection         | 0     |
| Mysqlx_stmt_execute_mysqlx            | 0     |
| Mysqlx_stmt_execute_sql               | 0     |
| Mysqlx_stmt_execute_xplugin           | 0     |
| Mysqlx_stmt_get_collection_options    | 0     |
| Mysqlx_stmt_kill_client               | 0     |
| Mysqlx_stmt_list_clients              | 0     |
| Mysqlx_stmt_list_notices              | 0     |
| Mysqlx_stmt_list_objects              | 0     |
| Mysqlx_stmt_modify_collection_options | 0     |
| Mysqlx_stmt_ping                      | 0     |
| Prepared_stmt_count                   | 0     |
+---------------------------------------+-------+









이후 아래와 같이 사용된 변수의 통계를 확인합니다. 성능 개선에 대한 자료는 해당 아티클에 잘 나와 있으므로 한 번 참조해 보세요.

1
2
3
4
5
6
7

mysql> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME IN ('Com_stmt_execute', 'Com_stmt_reprepare', 'Prepared_stmt_count');
+---------------------+----------------+
| VARIABLE_NAME       | VARIABLE_VALUE |
+---------------------+----------------+
| Com_stmt_reprepare  | 0              |
| Prepared_stmt_count | 0              |
+---------------------+----------------+









1-2. 파라미터 바인딩

Statement와 PreparedStatement의 또 다른 점은 파라미터 바인딩입니다. Statement를 사용하여 SQL 쿼리를 실행하면 변수나 매개변수를 직접 문자열에 포함시켜야 합니다. 이런 변수 포함 방식을 문자열 연결(string concatenation) 또는 문자열 포맷팅(string formatting)을 사용하여 수행하는데, 즉 사용자 입력을 SQL 쿼리 문자열에 직접 포함시키는 것입니다.

1
2
3

String name = "Jun";
int age = 90;
"SELECT * FROM users WHERE name='" + name + "' AND age=" + age;









사용자 입력을 SQL 쿼리 문자열에 직접 포함시키는 경우, 악의적 사용자로 인해 원치 않는 결과를 초래할 수 있습니다. 예를 들어, 아래 쿼리의 name 변수에 "Jun'; DROP TABLE users;--"를 입력하면, 이로 인해 다음과 같이 테이블을 날려버리는 쿼리가 실행됩니다.

1

SELECT * FROM users WHERE name = 'Jun'; DROP TABLE users;--' AND age=30;









또한 SQL 쿼리 문자열의 구조는 사용자 입력에 의해 변형될 수도 있습니다. 예를 들면, 아래와 같이 age 변수에 "30 OR 1=1"을 입력할 경우 테이블의 모든 사용자를 조회하게 되며, 데이터가 많은 경우 데이터베이스에 부담이 갈 수 있습니다.

1

SELECT * FROM users WHERE name = 'Jun' AND age = 30 OR 1 = 1;

쿼리에는 AND 연산자와 OR 연산자가 있으며 AND 연산자가 OR 연산자보다 우선순위가 높으므로 먼저 실행됩니다.









반면 PreparedStatement는 위치 지정자(placeholder)를 사용하여 쿼리를 생성하며, 이를 통해 파라미터 값을 별도로 지정합니다. 따라서 사용자 입력이 쿼리 구조를 바꾸지 않습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

@Repository
class UserDao {
    private val log = LoggerFactory.getLogger(this::class.java)
    private val connectionUtils = DatabaseConnectionUtil

    fun save(user: User) {
        val sql = "INSERT INTO user(user_id, nickname) values(?, ?)"

        var connection: Connection? = null
        var preparedStatement: PreparedStatement? = null
        try {
            connection = connectionUtils.getConnection()
            preparedStatement = connection.prepareStatement(sql)
            
            // 바인딩
            preparedStatement.setLong(1, user.userId)
            preparedStatement.setString(2, user.nickname)
            preparedStatement.execute()
        } catch (exception: SQLException) {
            log.error("Database Error")
            exception.printStackTrace()
        } finally {
            close(connection, preparedStatement, preparedStatement?.resultSet)
        }
    }
    
    ......
    
}









이는 파라미터 바인딩 시 값이 단순히 문자열로 해석되지 않고, 자료형에 따라 적절하게 처리되기 때문인데, 이를 통해 SQL Injection 공격으로부터 안전한 쿼리를 실행할 수 있습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

// 부적절한 쿼리문
String name = "Jun'; DROP TABLE users;--";
int age = 30;

String sqlQuery = "SELECT * FROM users WHERE name = ? AND age = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery);

preparedStatement.setString(1, name);
preparedStatement.setInt(2, age);

ResultSet resultSet = preparedStatement.executeQuery();

    ......









2. 정리

Statement와 PreparedStatement의 차이점은 크게 캐싱과 파라미터 바인딩입니다. PreparedStatement는 Statement와 달리 한 번 사용된 쿼리에 대해서는 그 값을 캐싱 해 사용하며 동적으로 파라미터만 바인딩 해서 사용할 수 있습니다. 또한 파라미터를 바인딩 하는 과정에서 파라미터만 바인딩 해서 넣기 때문에 SQL Injectino으로부터 비교적 안전합니다. 따라서, PreparedStatement는 캐싱과 파라미터 바인딩을 통해 성능과 보안 면에서 Statement보다 우수하며, 일반적으로 반복적으로 실행되는 쿼리에는 PreparedStatement를 사용하는 것이 권장됩니다.

Database
This post is licensed under CC BY 4.0 by the author.
Contents