내맘대로 정리하는 Real MySQL #13.1장) 프로그램 연동 (JDBC)

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

실제로 현장에서 가장 취약한 부분은 애플리케이션과 데이터베이스를 연결하는 인터페이스 부분이다. JDBC와 같은 인터페이스 부분은 애플리케이션과 MySQL 데이터베이스의 중간에 위치하고 있어서 양쪽의 특성을 잘 알지 못하면 최적의 옵션을 선택하기가 쉽지 않다.

Connector/J

• MySQL의 JDBC 이름

JDBC URL

MySQL 서버의 정보를 표준 포맷으로 조합한 문자열. 때로는 connection string이라고도 한다

• jdbc:mysql:// 은 고정된 값으로 MySQL 서버에 접속하기 위한 JDBC URL 임을 알려주는 프로토콜 역할을 한다
• 이 뒤에는 MySQL 서버의 IP 주소나 도메인 네임, 포트 번호, DB 명이 들어가게 된다
• ex) jdbc:mysql://localhost:3306/test_db

Statement

• JDBC를 사용하는 애플리케이션에서 모든 SQL과 DDL 문장을 실행하는데 필요한 객체

connection.setAutoCommit( )

• auto commit이 설정되어 있다면 매 쿼리가 실행될때 자동으로 트랜잭션이 commit 된다
  • auto commit을 설정하지 않았다면 기본이 true 이다
• 만약 한 트랜잭션으로 여러 개의 update, delete 문장을 묶어 실행하려면 auto commit 모드를 false로 실행해야 한다.

PreparedStatement

• 쿼리 분석이나 최적화의 일부 작업을 처음 한 번만 수행해 별도로 저장 해두고, 그 뒤 요청되는 쿼리는 저장된 분석 결과를 재사용하는 ㅂ아식
• 매번 쿼리를 실행할 때마다 거쳐야 하는 쿼리 분석이나 최적화의 일부 작업을 건너 뛰고 빠르게 처리할 수 있다.
• SQL 문장 자체가 네트워크로 전송되지 않고 바인딩할 변수 값만 전달되어 트래픽도 조금은 효율적이다
• 바이너리 프로토콜을 사용할 수 있다
• 그냥 PreparedStatement를 사용하게 된다면 Connector/J가 자체적으로 SQL 문장의 바인딩 변수에 값을 매핑해 하나의 완성된 SQL 문장으로 서버에 전송하는 방식이다 (클라이언트 Prepared Statement)
• JDBC URL 부분에 userServerPrepStmts=true 옵션을 추가해야 서버 Prepared Statement를 사용하게 된다

애플리케이션에서는 다음 문장으로 확인해볼 수 있다.

SELECT * FROM information_schema.global_status
WHERE variable_name IN ('Com_stmt_prepare', 'Com_stmt_execute', 'Prepared_stmt_count');

• Com_stmt_prepare : 서버 사이드 prepare statement에서 Connection.prepareStatement() 함수 호출에 의해 PreparedStatement 객체가 만들어진 횟수
• Com_stmt_execute : 서버 사이드 prepare statement에서 Connection.execute(), executeUpdate(), executeQuery() 함수 호출에 의해 PreparedStatement 쿼리가 실행된 횟수
• Prepared_stmt_count : MySQL 서버에 현재 만들어져 있는 prepare statement 객체의 개수

만약 iBatis나 Hibernate 등을 사용한다면 이런 정보를 분석해 정상적으로 서버 프리페어 스테이트먼트를 사용하는지 알아보고, 원하는 바대로 사용되도록 튜닝하는 것이 좋다.

배치 처리

rewriteBatchedStatements 옵션을 true로 설정하면 MySQL Connector/J가 addBatch() 함수로 누적된 레코드를 모아 구문을 실행한다. rewriteBatchedStatements 옵션이 활성화되면 useServerPrepStmts는 비활성화해야 한다.

JDBC가 데이터를 가져오는 세 가지 방식

클라이언트 커서 방식 (기본 방식)

• MySQL에서 정보를 모두 받아 JDBC가 가지고 있고, JDBC를 사용하는 측에서 일정 부분씩 가져오는 방식이다
• 상당히 빠르기 때문에 Connector/J의 기본 작동 방식으로 채택되어 있다.
• SELECT 쿼리의 결과가 너무 클 때는 JDBC가 데이터를 받는데 시간이 오래 걸리고 메모리도 많이 잡아 먹는다는 단점이 있다

스트리밍 방식

• JDBC가 데이터를 가지고 오지 않고, 사용자가 요청할때마다 MySQL로 부터 데이터를 가져와 전달하는 방식이다.
• 메모리가 부족한 경우에 사용해볼만 한다
• ResultSet이 닫히기 전에는 동일 커넥션에서 새로운 쿼리를 실행하지 못한다

서버 커서 방식

• Statement 수준의 옵션을 설정하는 것이 아니라 커넥션 자체를 생성할때 JDBC URL에 옵션을 설정해야 한다. useCursorFetch 옵션을 TRUE로 설정하고 defaultFetchSize 값을 반드시 0보다 큰 값으로 설정해야 한다.
• MySQL 서버에 임시테이블이 생겨 결과가 저장된다 (클라이언트 커서 방식의 JDBC역할을 MySQL이 하는 셈이다)
• 임시테이블은 ResultSet이 닫히기 전까지 계속 유지된다
• 서버 커서 방식은 동일 커넥션에서 새로운 쿼리를 실행할 수 있다.

JDBC 패킷 확인

traceProtocol 옵션을 사용해 Connector/J와 MySQL 서버가 주고받는 패킷을 볼 수 있다