[Real MySQL] 13. 프로그램 연동

자바

JDBC와 같은 인터페이스는 애플리케이션과 MySQL DB 중간에 위치하고 있어서 양쪽의 특성을 잘 알지 못하면 최적의 옵션을 선택하기가 쉽지 않다.
JDBC 드라이버의 작동 방식을 변경하기 위한 옵션이나 주의할 점을 잘 알고 있어야 상황에 맞게 최적화하여 사용할 수 있다.

MySQL Connector/J

자바에서 제공하는 JDBC는 껍데기와 같은 인터페이스이고, 실제 알맹이인 구현체는 각 DBMS 제조사에서 제공하는 JDBC 드라이버이다.

Connector/J를 이용해 MySQL 서버에 접속하려면 JDBC URL이라는 개념을 알아야 한다.
여기서의 URL은 일반적인 HTTP, FTP에서 사용하는 URL이 아니라 MySQL 서버의 정보를 표준 포맷으로 조합한 문자열이다.
이를 커넥션 스트링이라고 한다.

public Connection getConnection() throws SQLException {
    String driver = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/test_db";
    String uid = "userId";
    String pwd = "password";

    Class.forName(driver).newInstance();
    return DriverManager.getConnection(url, uid, pwd);
}

"jdbc:mysqlL//"은 고정된 값으로 JDBC URL임을 알려주는 프로토콜 역할을 한다.
그 뒤에는 접속하고자 하는 MySQL 서버의 ip주소(도메인), 포트 번호, DB명이 따라온다.
또한 별도의 Connector/J 옵션을 사용하고자 한다면 해당 URL 끝에 "?" 를 붙이고 쿼리스트링 형식으로 옵션 변수를 넘길 수도 있다.

Statement와 PreparedStatement의 차이

MySQL 서버가 쿼리를 수행하는 각 태스크는 다음과 같다.

• 쿼리 분석
• 최적화
• 권한 체크
• 쿼리 실행

이 때 Statement로 실행되는 쿼리는 위의 모든 단계를 매번 거쳐서 쿼리가 실행되는데, 쿼리 분석이나 최적화 같은 작업은 상대적으로 시간이 걸리는 작업이다.
하지만 PreparedStatement를 사용하면 쿼리 분석이나 최적화의 일부 작업을 처음 한 번만 수행해 저장해 놓고, 다음부터 요청되는 쿼리는 저장된 분석 결과를 재사용한다.

SELECT * FROM sample_tbl WHERE col1=?;

위와 같이 "?"인 바인딩 변수를 사용해 쿼리를 템플릿화하여 캐싱하는데, 실제 사용할 때에는 변수 대신 상수를 대입해야 한다.
이런 변수를 사용하는 쿼리를 PreparedStatement 혹은 바인딩 쿼리라고 하고, 바인딩 변수 없이 상수만 사용하는 쿼리를 동적 쿼리, 다이나믹 쿼리라고 한다.

PreparedStatement pstmt = conn.prepareStatement("SELECT ...");

conn.prepareStatement 함수를 호출하면 Connector/J가 주어진 SQL 문장을 서버로 전송해서 쿼리를 분석하고 그 결과를 저장해둔다.
그리고 MySQL 서버는 쿼리 분석 결과의 포인터와 같은 해시 값을 Connector/J로 반환한다.
Connector/J는 반환받은 해시 값을 이용해 PreparedStatement 객체를 생성하게 된다.

PreparedStatement의 장점은 다음과 같다.

• 한 번 실행된 쿼리는 매번 쿼리 분석 과정을 거치지 않고 처음 분석된 정보를 재사용한다.
• 바이너리 프로토콜을 사용하여 쓸데없는 별도의 타입 변환을 수행하지 않는다.
  • Connector/J 초기 버전에서는 문자열 기반의 프로토콜을 사용하여 사용자가 타입을 지정하더라도 전송 과정에서 문자열로 변환해야 했다.
• 이스케이프 문자 처리를 Connector/J에서 대신 처리해 주기 때문에 SQL 인젝션 등의 보안 위험에 기본적으로 대비할 수 있다.

PreparedStatement의 종류

Connector/J의 PreparedStatement에는 두 가지 종류가 있다.

• 클라이언트 PreparedStatement
  • 바인딩 변수가 있는 쿼리에 Connector/J가 자체적으로 값을 매핑해 SQL 문장을 완성하여 서버에 전송하는 방식이다.
  • 이 방식은 애플리케이션 개발자가 PreparedStatement를 사용한다고 느끼지만 실제로는 매번 쿼리 문장을 분석하는 과정을 거친다.
• 서버 PreparedStatement
  • 일반적인 다른 DBMS가 지칭하는 PreparedStatement이다. 클라이언트가 SQL 문장에 바인딩할 변수 값만 MySQL 서버로 전송한다.

JDBC 표준에 PreparedStatement 기능이 도입됐을 때 MySQL 서버에는 해당 처리 기능이 없었다.
그래서 JDBC 표준의 PreparedStatement를 지원하기 위해 만들어진 것이 클라이언트 PreparedStatement이다.

서버 PreparedStatement를 사용하기 위해서는 JDBC URL에 "useServerPrepStmts=true" 옵션을 추가해야 한다.

Connector/J 설정 옵션

주요 설정만 간단하게 살펴보면 다음과 같다.

• useCompression
  • 애플리케이션과 MySQL 서버 사이에 전송되는 데이터를 압축할지 선택하는 옵션인데, 압축을 위한 CPU 작업이 크기 때문에 같은 네트워크 대역 내에 있다면 사용하지 않는 것이 좋다.
• useCursorFetch
  • Connector/J에서 일반적으로 사용하는 클라이언트 커서 대신 서버 커서를 사용하도록 설정한다.
  • 이를 활성화하려면 아래의 defaultFetchSize 옵션도 0보다 큰 값으로 설정해야 한다.
• defaultFetchSize
  • 서버 커서를 사용할 때 MySQL 서버로부터 한 번에 몇 개씩 레코드를 읽어올지를 설정한다.
• useServerPrepStmts
  • 서버 PreparedStatement를 사용할지 말지 결정하는 옵션이다.
• traceProtocol
  • Connector/J가 MySQL 서버와 통신하기 위해 주고받는 패킷을 Log4J를 이용해 로깅할 수 있다.

대량 데이터 가져오기

많은 레코드를 가져와야 하는 배치 프로그램에서 Connector/J는 기본적으로 다음과 같은 과정을 거친다.

사용자가 Connector/J를 사용해 SELECT 쿼리를 실행하면 Connector/J는 해당 결과를 MySQL 서버로부터 모두 내려받아 Connector/J가 관리하는 캐시 메모리 영역에 그 결과를 저장한다.
이후 애플리케이션에서 ResultSet을 조회하는 함수가 호출되면 MySQL 서버까지 요청이 가지 않고, Connector/J가 캐시해 둔 값을 애플리케이션 쪽으로 반환한다.
이를 클라이언트 커서 방식이라고 하는데 상당히 빠른 방식이다.

이는 대신 쿼리 결과가 너무 클 때는 다운로드하는 데에 많은 시간이 걸린다는 단점이 있다.
또한 메모리가 부족해서 Out of Memory 에러가 날 수도 있다.

셋 스트리밍(ResultSet Streaming) 이라는 방식은 한 번에 쿼리의 결과를 모두 다운로드하지 않고 MySQL 서버에서 한 건 단위로 읽어서 가져가게 할 수 있는 방식이다.

Connection conn = getConnection();
Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, // (1)
java.sql.ResultSet.CONCUR_READ_ONLY); // (2)

stmt.setFetchSize(Integer.MIN_VALUE); // (3)

먼저 Statement 생성 시 ResultSet이 읽기 전용이며 (2번), ResultSet을 앞쪽 진행 방향으로만 읽을 것 (1번)이라는 옵션을 설정한다.
그 다음 stmt.setFetchSize()라는 함수에 fetch 크기를 예약된 값(3번)으로 설정해 주면 MySQL 서버가 결과 셋을 한 건씩 다운로드하리라는 것을 알고 결과 셋을 준비해준다.
그래서 클라이언트에서 ResultSet.next() 함수가 호출될 때마다 한 건씩 클라이언트로 내려 보내게 된다.
이 때 Integer.MIN_VALUE는 특별한 의미를 가지는 값은 아니고 그냥 지정된 값일 뿐이다.

이러한 스트리밍 방식은 매번 레코드 단위로 MySQL 서버와 통신해야 하므로 Connector/J의 기본 처리 방식에 비해 상당히 느리다.

쿼리 결과가 아주 대용량일 때 접근할 수 있는 또 다른 방법이 있다.
서버 커서 방식은 커넥션 자체를 생성할 때 JDBC URL에 옵션을 설정해야 한다.
이 방식은 서버 커서를 이용하는 방식으로 MySQL 서버에서 결과 셋에 상응하는 크기의 임시 테이블을 만든다.

String url = "jdbc:mysql://localhost:3306/db1?useCursorFetch=true&defaultFetchSize=1000";

URL에서 useCursorFetch 설정 옵션을 TRUE로 변경하고 defaultFetchSize를 0보다 큰 값으로 설정하면 서버 커서 방식을 사용할 수 있다.
서버 커서 방식은 서버 PreparedStatement 방식으로 처리되어야 하기 때문에 useCursorFetch가 TRUE로 설정되면 useServerPrepStmts 설정 옵션도 자동으로 TRUE가 된다.

결과 셋 스트리밍 방식과 서버 커서 방식의 가장 큰 차이는 MySQL 서버가 작업 결과물을 담아둘 임시 테이블을 사용하는지의 여부다.
서버 커서 방식은 명시된 fetchSize 만큼 Connector/J의 캐시 메모리 영역에 내려받아 애플리케이션으로 제공하게 된다.

---

참고

위 내용은 이성욱님의 Real MySQL (위키북스) 에서 일부 내용을 간단하게 정리한 것입니다.
세부적인 내용은 책을 직접 구입하셔서 읽어보시는 것을 추천드립니다.