Overview

정말 오랜만에 포스팅합니다. 그동안 개인적인 일이 조금 있어서.. 조금 소홀이 했었네요. ^^

오늘은 묵시적인 형 변한에 대해서 설명을 드릴까 합니다. 서비스 쿼리를 작성하다보면, 이상이 없는 데 성능이 이상하게 안좋은 경우가 있습니다. 명시적으로 쿼리 내용을 볼 수 있다면 괜찮겠지만, 어플리케이션에서 변수를 바인딩하여 SQL을 실행하는 경우는 더욱 찾기가 어렵습니다.

묵시적 형 변환은 단순히 MySQL 뿐만 아니라 Oracle, MS-SQL 등 다른 DBMS에서도 반드시 주의를 해야하는 사항입니다.

묵시적 형 변환이란?

묵시적 형 변환이란 조건절 데이터 타입이 다르면 “우선 순위가 있는 쪽”으로 “형 변환”이 내부적으로 발생하는 것을 말합니다.

예를 들어 정수 값과와 문자열을 비교하는 경우, 숫자가 문자열보다 우선 순위에 있기 때문에 문자열은 정수값으로 자동 형 변환됩니다.

묵시적인 형 변환은 예외를 DBMS 내부적으로 처리하기 위함이므로, 서비스 도중 언제든지 발생할 수 있습니다. 하지만 만약 묵시적 형 변환이 일어나는 곳이 인덱스 칼럼이라면 어떨까요? 인덱스가 있을 지라도 조건 절을 처리하기 위해 모든 데이터를 묵시적으로 형 변환합니다. 이 경우 인덱스는 무의미하며 풀 테이블 스캔이 발생하죠.

자~! 직접 보시죠^^

테스트 환경 구성

1) 테이블 생성

## 테이블 생성
create table test(
  i int unsigned not null auto_increment,
  j int unsigned not null,
  s varchar(64) not null,
  d datetime not null,
  primary key(i)
);

## 인덱스 추가
alter table test add key (j), add key(s), add key(d);

2) 데이터 생성

스키마를 생성하였으니, 이제 데이터를 넣도록 하겠습니다. MySQL의 rand()와 crc32() 함수를 사용하여 정수, 문자열 그리고 날짜 데이터를 생성하여 위 테스트 테이블에 넣습니다.

먼저 test 테이블에 한 건 데이터를 다음과 같이 넣습니다.

insert into test (j, s, d)
values
(
    crc32(rand()), 
    crc32(rand())*12345,
    date_add(now(), interval -crc32(rand())/5 second)
);

그리고 다음과 같이 쿼리를 17번 실행하면 테이블에는 131072 건의 데이터가 저장됩니다. 물론 더 많은 데이터 환경에서 테스트를 하고자 한다면 몇 번 더 실행하면 되겠죠. ^^

insert into test (j, s, d)
select
    crc32(rand()), 
    crc32(rand())*12345,
    date_add(now(), interval -crc32(rand())/5 second)
from test;

이제 테스트 환경은 간단하게 구성되었습니다. 저는 위처럼 테스트 데이터를 간단하게 생성하였지만, 위 방법말고도 얼마든지 테스트 데이터를 생성할 수 있습니다.

빠른 테스트 환경을 구성하는 것이 제일 좋은 것 같아요. ^^ 더욱 안 귀찮은 방법이 있으면 저좀 알려주세요. (사실 저것도 귀찮다능~! ㅋ)

묵시적 형 변환 테스트

그리고 테스트를 할 데이터를 한 건 무작위로 가져와서 대상으로 정합니다.

select * from test order by rand() limit 1;

저는 다음 데이터를 기준으로 테스트를 진행하도록 하겠습니다. 무작위로 데이터를 생성하였기 때문에 위 쿼리 실행 결과과 다음과 같지 않을 수도 있습니다.

+--------+------------+----------------+---------------------+
| i      | j          | s              | d                   |
+--------+------------+----------------+---------------------+
| 122416 | 3835333344 | 17424528721665 | 2000-01-16 13:59:59 |
+--------+------------+----------------+---------------------+

정수 칼럼을 “문자열” 로 검색

위 테이블에서 j 는 정수형입니다. j 칼럼 기준으로 데이터를 검색할 경우 정수형이 아닌 문자열로 조건을 주었을 때 내부적인 처리입니다.

explain 
select * from test
where j = '3835333344';

+----+-------+------+------+---------+------+-------+
| id | table | type | key  | key_len | rows | Extra |
+----+-------+------+------+---------+------+-------+
|  1 | test  | ref  | j    | 4       |    1 |       |
+----+-------+------+------+---------+------+-------+

우선 순위가 정수형이 높기 때문에 문자열 조건 값은 묵시적으로 정수형으로 형 변환 되므로 실행 시 문제가 되지 않습니다.

문자열 칼럼을 “숫자”로 검색

s 칼럼은 문자열입니다. 이번에는 칼럼이 문자열인 경우에 정수 형 데이터 값으로 조회하는 경우를 테스트해보겠습니다.

explain 
select * from test 
where s = 17424528721665;

+----+-------+------+------+---------+--------+
| id | table | type | key  | key_len | rows   |
+----+-------+------+------+---------+--------+
|  1 | test  | ALL  | NULL | NULL    | 131276 |
+----+-------+------+------+---------+--------+

s 필드에 인덱스나 존재하나 묵시적인 형 변환으로 인하여 풀 테이블 스캔이 발생합니다. 위 테이블 데이터가 크지 않기 때문에 수행 속도가 느리지 않으나, 만약 천만건 이상의 데이터 환경에서 테스트하는 경우라면 엄청난 성능 저하가 발생합니다.

만약 파라메터 바인딩을 사용해서 문자열 칼럼에 정수형을 넣는 경우라면, 소스 상에서는 여간 찾기가 힘듭니다. 물론 MySQL에서 show processlist 명령을 수행하면 바인딩된 변수 값까지 나오지만, 경우에 따라서는 상당히 찾아내기 난해한 경우가 있습니다. 다음과 같은 경우를 예를 들 수 있습니다.

String sql = "select * from test where s = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 2406525903);

Conclusion

사실 이것저것 생각하기 싫다면 MySQL에서 무조건 문자열 조건으로 질의해도 큰 문제는 없습니다. 어차피 정수형이 문자열보다 우선 순위에 있기 때문에 문자열은 묵시적 형변환되기 때문이죠. 하지만 추후 다른 DBMS 로 데이터 이관하는 경우에는 문제가 생길 수 있습니다. 예를 들어 Oracle to PostgreSQL로 테스트 이관을 한 적이 있는데 DB 상의 데이터 타입과 바인딩되는 데이터 타입이 맞지 않으면 예외 처리할 수도 있습니다. (참고로 DB가 아닌 클라이언트 쪽에서 발생한 예외입니다.)

게다가 때로는 예기치 않는 결과 값이 발생할 수도 있습니다. 특히나 문자열 사이에 보이지 않는 문자가 포함된 경우에는.. 그러므로 묵시적 형 변환 함정에 빠지지 말고 반드시 조건절에는 칼럼 타입을 맞춰서 질의하는 것을 강력하게 권고 드립니다.

다른 재미있는 사례로 다시 포스팅하겠습니다.

감사합니다. ^^

SQL 작성 시 묵시적 형 변환 함정에 빠지지 말자!!