요약

데이터베이스로 SQL Server를 사용하는 고객이 심각한 성능 문제를 경험하고 있습니다. 문제는 다음과 같습니다.

UUID 형식 문자열(VARCHAR)을 주키(Primary Key)로 사용하는 테이블을 주키 칼럼으로 조회했을 때 약 10% 비율로 풀스캔(Full Scan)합니다. 값에 따라 항상 색인을 이용하거나 풀스캔을 하거나 합니다.

단, 자바에서 PreparedStatement을 사용했을 때만 나타나는 현상입니다. 검색 조건을 결합하여 SQL을 만들 때는 성능 저하 문제가 없습니다.

현재까지 확인한 유일한 해결책은 PreparedStatement을 사용하지 않고 SQL에 검색 조건을 결합하는 겁니다. SQL 삽입(SQL Injection) 공격 등의 부작용도 처리해야 합니다.

재현

다음 테이블이 있습니다.

CREATE TABLE MY_TABLE (
  ID VARCHAR(16) PRIMARY KEY
);

이 테이블에는 ID 칼럼만이 있습니다. 소문자 알파벳과 숫자로 구성된 16 글자 문자열로 주키 칼럼입니다. 8 바이트 데이터를 16진수로 표현한 겁니다.

다음은 ID를 만드는 자바 코드입니다.

import java.security.SecureRandom;

public class UUID {

    private static volatile SecureRandom numberGenerator = new SecureRandom();

    private final String id;

    public UUID() {
        byte[] randomBytes = new byte[8];
        numberGenerator.nextBytes(randomBytes);
        StringBuilder sb = new StringBuilder(2 * randomBytes.length);
        for (int i = 0; i < randomBytes.length; i++) {
            String hex = Integer.toHexString(randomBytes[i] & 0xFF);
            if (hex.length() == 1) {
                sb.append('0');
            }
            sb.append(hex);
        }
        id = sb.toString();
    }

    @Override
    public String toString() {
        return id;
    }

}

백만건의 데이터를 입력했습니다. 앞 그림은 모두 '00'으로 시작하지만 전체적으로 앞 2자를 기준으로 균등합니다. 다음 쿼리로 확인할 수 있습니다.

SELECT C, COUNT(*)
  FROM (SELECT LEFT(ID, 2) AS C FROM MY_TABLE) T
GROUP BY C

다음은 자바에서 PreparedStatement로 각 레코드를 조회하는 코드입니다.

void load(String id) {
  try (Connection con = Sqls.getConnection()) {
    String sql = "SELECT * FROM MY_TABLE WHERE ID = ?";
    PreparedStatement pstmt = con.prepareStatement(sql);
    pstmt.setString(1, id);
    pstmt.executeQuery();
  }
}

임의의 만건을 실행했을 때 걸리는 시간은 다음과 같습니다.

값에 따라 항상 느리거나 항상 빠릅니다. 예를 들어 다음 값으로 조회하면 항상 느립니다.

03ce3717dee82df0
1d3936b2ddd718a9
24f7de006b3fa8c0
57cabb4fb61787e9

주키로 검색을 했음에도 약 10%의 성능 저하 현상이 나타납니다. PreparedStatement을 사용하지 않은 코드입니다.

void load(String id) {
  try (Connection con = Sqls.getConnection()) {
    String sql = String.format("SELECT * FROM MY_TABLE WHERE ID = '%s'", id);
    Statement stmt = con.createStatement();
    stmt.executeQuery(sql);
  }
}

결과를 보면 만건 중 한번을 제외하면 문제가 없습니다. PreparedStatement 사용 여부에 따라 결과가 크게 다릅니다.

해결책

현재까지는 PreparedStatement를 사용하지 않는다가 유일한 해결책입니다. 몇 가지 다른 시도는 해결책이 아니었습니다.

JDBC 드라이버 교체

다음 드라이버에서 테스트했으며 차이가 없습니다.

• Microsoft JDBC Driver 8.4 for SQL Server 8.4.1.0

• Microsoft JDBC Driver 6.0 for SQL Server 6.0.8112.200

• Microsoft JDBC Driver 4.2 for SQL Server 4.2.8112.100

파라미터 스니핑(Parameter Sniffing)

확인 중입니다.

힌트 사용

다음과 같이 색인 힌트를 주었지만 효과가 없었습니다.

SELECT * FROM MY_TABLE WITH(INDEX(MY_TABLE_ID_PK)) WHERE ID = ?