MySQL에 나노초가 이상하게 저장되네? 발생할 수 있는 나노초 관련 이슈를 알아보자.

왜 저장 순서와 다르게 조회가 되지..? (MySQL 시간 타입의 정밀도)

현재 진행하고 있는 프로젝트에서 저장된 순서데로 조회가 되길 기대했던 기능이 있었다.

저장된 순서는 아래의 createdAt 필드를 사용해 오름차순으로 구분했다.

알림 1을 저장하고, 알림 2를 저장하면 createdAt 기준으로 조회시 [알림1, 알림2] 순서로 조회가 되야 하는데

의도와 다르게 [알림2, 알림1]이 조회되고 있었다.

뭐가 문제였을까 ?

 

기본적으로 나노초가 포함되어 시간이 저장될 것을 기대했지만 초까지만 저장되고 있었고

저장된 순서는 나노초 단위로는 구분이 되더라도 초는 같아서 의도와 다르게 조회되고 있었던 것이다.

 

저장 순서데로 조회가 가능하도록 해보자

MySQL의 공식문서를 살펴본 결과 시간 타입에 (fsp) 정밀도를 생략하면 0으로 되고 이는 소수 초를 포함하지 않는다고 한다.

 

그럼 이 문제를 해결하기 위해 정밀도를 설정해보자.

MySQL에서는 최대 6자리의 정밀도(마이크로 초)까지 지원하고 있으므로 최대 정밀도인 6으로 설정을 했다.

 

이제 마이크로 초로 구분이 되어 저장 순서데로 조회가 가능해졌다.

---

LocalDateTime의 나노초 출력의 비밀과 MySQL의 반올림 문제

아래와 같은 요구사항이 있다고 해보자.

회원에게 발급된 쿠폰은 발급일 포함 7일 동안 사용 가능하다. 만료 일의 23:59:59.999999 까지 사용할 수 있다

 

요구사항 그대로 코드를 작성하면 다음과 같다.

 

그럼 이 MemberCoupon을 조회하면 다음과 같이 출력이 된다.

 

하지만 MySQL에서 MemberCoupon 테이블을 조회하면 또 다르게 조회가 된다.

 

미친 것 같다. 왜 이럴까 ?

각각 내부 동작 방식에 비밀이 있다.

LocalDateTime이 000999999로 출력된 비밀과 MySQL에서 expriedd_at이 반올림되어 저장된 이유를 순서대로 알아보자.

 

LocalDateTime의 나노초 출력의 비밀

MySQL 데이터를 넘길 때, Java의 객체를 넘기면 MySQL이 해석을하지 못하기 때문에 해석가능한 문자열로 변환하여 보낸다.

이 부분에서 시간이 넘길 때 toString()을 사용해 문자열로 변환하는 상황에서 아래 로직을 통해 위와 같은 현상이 발생한다.

 

아래는 LocalTime의 toString() 메서드이다.

expiredAt의 withNano가 999999 6자리로 설정되었을 때의 경우를 살펴보면 다음과 같다.

 

nanoValue를 계산하는 로직에서 999,999가 1,000,000 또는 1000으로 나누어 떨어지지 않기 때문에

마지막 else 구문으로 넘어간다.

else 구문에서 999,999와 1,000,000,000을 더한다. => 1,000,999,999

그 다음 subString(1)으로 첫 번째 index부터 잘라낸다. => 000,999,999

이러한 과정으로 withNano(999999)로 설정한 시간이 아래와 같이 출력된 것이다.

 

MySQL의 반올림 문제

LocalDateTime의 나노초가 조정되는 것은 이해했다.

그렇다면 000999999의 나노초가 MySQL에 저장될 때는 왜 반올림되어 001000으로 저장되는 것일까 ?

 

MySQL JDBC 드라이버에서 SQL 쿼리의 파라미터 바인딩 과정에서 시간 관련 데이터베이스 작업이 수행될 때 일어나게 된다.

반올림이 발생하는 mysql-connector 라이브러리의 일부 코드를 살펴보면 다음과 같다.

(다른 로직들도 있지만 이 상황에 크게 관여하는 코드들은 없어서 생략했다.)

먼저 Java의 객체를 MySQL이 이해할 수 있는 문자열 형식으로 변환하는 getString() 메서드가 실행되고

가장 처음 adjustNanosPrecision() 메서드로 날짜 타입 조정에 들어간다.

 

먼저 변수를 살펴보면

ts는 Java의 시간 타입이 MySQL이 이해할 수 있는 시간 타입으로 변환된 것

fsp는  원하는 정밀도 (소숫점 자릿 수)인데 DATETIME 타입의 정밀도를 6으로 지정해 6이라는 값이 들어간다.

그리고 serverRoundFracSecs는 MySQL에 따로 설정을 하지 않으면 기본 값인 true로 설정된다.

serverRoundFracSecs가 true면 반올림, false면 정밀도이하의 값을 절삭한다.

 

로직을 간단히 설명하면 다음과 같은 과정으로 반올림이 일어난다. (serverRoundFracSecs가 기본 값인 true인 상황)

이해하기 편하도록 핵심 로직을 포함한 테스트 코드를 작성해보았다.

핵심만 살펴보면 다음과 같다.

1. fsp 정밀도에 맞춰 잘라낼 자릿수를 구한다 => fsp는 6이기에 뒤에 3자리를 잘라내기 위해 tail을 1000으로 초기화한다.

2. getNanos() => 000,999,999 초를 나노초로 표현 => 000,999,999 * 1,000,000,000 => 999,999

3. 나노초를 tail로 나누고 소수점이하부분을 반올림한다. => Math.round(999,999 / 1000) = 1000

4. 반올림된 값에 tail을 곱하여 nanos를 초기화한다. => 1,000 * 1,000 = 1,000,000

5. setNanos(1,000,000)로 나노초를 설정 시 초로 표현되면 1,000,000 / 1,000,000,000 = 0.001초와 같이 표현된다.

소수점은 0.001로 출력됐지만, MySQL에 저장될 때 정밀도에 맞춰 0이 추가된다.

 

이제 반올림되는 것 까지는 이해했다.

그렇다면 반올림으로 문제가 될 수 있는 상황을 보자.

나노초가 반올림되어 다음날로 넘어가는 문제 

만약 아래와 같이 withNano를 999,999,999로 설정한다면??

 

이 경우 LocalDateTime의 toString() 로직에서 이미 나노 초에 맞게 자릿수가 정해져있어 나노초가 조정되진 않는다.

하지만 if문을 탄다는 점이 조금 다르다.

 

999,999,999인 nanos가 반올림되어 1,000,000,000이 되고 if문을 타게 된다.

if문에서 nanos를 1,000,000,000으로 나눈 나머지로 초기화하고 1초를 올리게 된다. 

그렇기 때문에 LocalDateTime.now().withHour(23).withMinute(59).withSecond(59).withNano(999999999);

에서 1초가 올라가 다음날로 MySQL에 저장된 것이다.

 

MySQL 반올림 문제해결하기

그렇다면 이 반올림 문제를 어떻게 해결할 수 있을까 ?

아래와 같이 반올림을 하지 않도록 애플리케이션 전역적으로 해결할 수 있는 방법이 있다.

MySQL에서 SQL 모드 활성화 쿼리 실행

SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');

 

DataSource url에 serverRoundFracSecs 쿼리 파라미터 추가

serverRoundFracSecs를 false로 설정함으로써 반올림 로직이 실행하지 않고 정밀도 이하 자릿수를 절삭한다.

(저장될 때는 fsp 정밀도에 맞춰 0이 채워진다.)

jdbc:mysql://hostname:3306/database?serverRoundFracSecs=false

 

하지만 위 방법들은 애플리케이션 전체에 영향을 줄 수 있는 방법이기 때문에 의도치 않은 사이드 이펙트가 발생할 수도 있다.

 

withNano를 알맞게 설정하기

따라서 개인적으로는 아래와 같이 데이터베이스 설정과 독립적이게 Java 코드로 설정하여 필요한 곳에만 선택적으로 적용했다.

하지만 이 방식을 적용하기 위해선 위에서 설명한 이슈들을 잘 파악해야할 것이다.

 

withNano를 999,999,000으로 설정하게 되면 LocalDateTime에서 toString() 호출 시

이미 나노 초에 맞게 자릿수가 정해져있어 나노초가 조정되진 않는다.

 

핵심만 살펴보면 다음과 같다.

1. fsp 정밀도에 맞춰 잘라낼 자릿수를 구한다 => fsp는 6이기에 뒤에 3자리를 잘라내기 위해 tail을 1000으로 초기화한다.

2. getNanos() => 999,999 초를 나노초로 표현 => 999,999 * 1,000,000,000 => 999,999,000

3. 나노초를 tail로 나누고 소수점이하부분을 반올림한다. => Math.round(999,999,000 / 1000) = 999,999 (반올림할 소숫점이 없음)

4. 반올림된 값에 tail을 곱하여 nanos를 초기화한다. => 999,999 * 1,000 = 999,999,000

5. setNanos(999,999,000)로 나노초를 설정 시 초로 표현되면 999,999,000 / 1,000,000,000 = 0.999,999초와 같이 표현된다.

이 방식에선 나노초를 tail로 나눌 때 소숫점 이하가 없어 반올림이 되지 않기 때문에 반올림 문제를 해결할 수 있다.

MySQL 테이블에도 데이터가 의도한데로 잘 저장된 것을 확인할 수 있다.

---

TIMESTAMP와 DATETIME의 차이

시간 타입을 설명하는 김에 좀 더 알아보고자 TIMESTAMP와 DATETIME의 차이에 대해 알아보고자 한다.

	TIMESTAMP	DATETIME
저장 범위	1970-01-01 00:00:01 UTC 부터 ~ 2038-01-19 03:14:07 UTC 까지	1000-01-01 00:00:00 부터 ~ 9999-12-31 23:59:59 까지
저장 형식	UTC로 내부적으로 저장 (숫자 형태로 저장)	입력된 그대로 날짜와 시간을 저장 (문자 형태로 저장)
조회 시	UTC를 DBMS의 TIME_ZONE으로 변환하여 반환	저장된 그대로의 값을 반환
용량	4바이트	8바이트

 

정리하자면 저장 공간을 좀더 절약하고, 국제적인 시간 변환이 필요하다면 TIMESTAMP가 적절하고,

2038년 이후의 날짜를 다뤄야하고, 날짜와 시간을 그대로 저장하고 조회하고 싶다면 DATETIME을 사용하는 것이 적절하다.