몇가지 테스트를 해보았다.
1. Explain
MariaDB [performance_test]> EXPLAIN SELECT SQL_NO_CACHE COUNT(id) -> FROM performance_test -> WHERE date_type_date > '2000-06-01 00:00:00' -> AND date_type_date < '2000-07-01 00:00:00' -> ; +------+-------------+------------------+-------+----------------+----------------+---------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------------+-------+----------------+----------------+---------+------+----------+--------------------------+ | 1 | SIMPLE | performance_test | range | date_type_date | date_type_date | 8 | NULL | 25207266 | Using where; Using index | +------+-------------+------------------+-------+----------------+----------------+---------+------+----------+--------------------------+ 1 row in set (0.00 sec)
MariaDB [performance_test]> EXPLAIN SELECT SQL_NO_CACHE COUNT(id) -> FROM performance_test -> WHERE int_type_date > UNIX_TIMESTAMP('2000-06-01 00:00:00') -> AND int_type_date < UNIX_TIMESTAMP('2000-07-01 00:00:00') -> ; +------+-------------+------------------+-------+---------------+---------------+---------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------------+-------+---------------+---------------+---------+------+----------+--------------------------+ | 1 | SIMPLE | performance_test | range | int_type_date | int_type_date | 4 | NULL | 24998328 | Using where; Using index | +------+-------------+------------------+-------+---------------+---------------+---------+------+----------+--------------------------+ 1 row in set (0.00 sec)
MariaDB [performance_test]>
|
두가지 형태를 explain을 해본결과 인덱싱은 동일하게 타지만, key_len이 달랐다.
datetime은 8byte 이고, int는 4byte 이다. (음.. 그럼 bigint 로 하게되면 유사한 결과가 나올지. 나중에 한번 더 해봐야 겟다.)
아무래도 포현범위가 크게 되면 메모리사용량이 많아 지게 되고 I/O도 그에 준하게 발생 할것이여서 가능하면 대용량 데이터를 컨트롤 할때는 숫자형이라도 tinyint ~ bigint 까지 적절히 사용하는것이 좋겠다.
unixtime을 int 형태로 표현 할 수 있는 이유는
"1970-01-01 00:00:00 GMT" 에서 부터 1초씩 증가해서 "2038-01-19 03:14:07" 까지 이고
해당 값음 ,
(1970-01-01 이전 날짜는 -로 표현 된다. )
이유는 32비트 int 형태의 정수표현범위 -2147483646 ~ 2147483647 까지 이기 때문이다,
따라서, 위의 시간이 지나게 되면, 이론적으로는 다시 -2147483646로 표현된다.
그러면 과거 첫날짜가 되야 되는데.. MySQL은 Null 이라고 하네. 희얀한데... 학교에서는 다시 꺼구로 돌아가는것으로 배웠는데.
이유는 signed int 2147483646는 이진수로 "0111 1111 1111 1111 1111 1111 1111 1111"인데, 여기에 1을 더하면
"1000 0000 0000 0000 0000 0000 0000 0000" 이 되어야 하는데 제일 앞 bit는 Sign bit 임으로 1이면 - 가 된다.(맞나?) ㅋㅋ
(이게 Signed 일때와 unSign 일때 처리 방법이 달라지는데, 너무 오래 전에 배운것이라 기억이 정확하진 않지만,
여하튼 숫자 범위가 넘어가면 더 이상 증가 하지 않는다. 강제로 증가를 시키게 되면 overflow 가 발생 한다.)
unixtime은 2038년 이후를 고려하지 않아도 될 경우에만 사용하도록 한다.
보통 S/W lifecycle 은 5년임으로 최소한 2033년 까지는 걱정 안해도 되지 않을까? ^^(이기적인 생각)
내 나이 50이 넘어서까지 코딩을 하고 있을면...........
그리고 이 세상에 천재같은 사람들이 Y2K때 처럼 기가 막힌 방법을 만들어 낼것이기 때문에 크게 걱정할 필요가 없다.
2. BETWEEN
결론부터 말하면 생각하지 못했던 결과가 나왔다
그 동안 책에서 읽어서, 경험적으로 구간 검색은 between을 사용하는게 효율이 좋다는것을 알고 있었으나
아래 결과로 확실한 결과가 나왔다.
MariaDB [performance_test]> SELECT SQL_NO_CACHE COUNT(id) -> FROM performance_test -> WHERE date_type_date between '2000-06-01 00:00:00' AND '2000-07-01 00:00:00' -> ; +-----------+ | COUNT(id) | +-----------+ | 12960005 | +-----------+ 1 row in set (3.55 sec)
MariaDB [performance_test]> SELECT SQL_NO_CACHE COUNT(id) -> FROM performance_test -> WHERE int_type_date BETWEEN UNIX_TIMESTAMP('2000-06-01 00:00:00') AND UNIX_TIMESTAMP('2000-07-01 00:00:00') -> ; +-----------+ | COUNT(id) | +-----------+ | 12960005 | +-----------+ 1 row in set (3.35 sec)
MariaDB [performance_test]> |
datetime 을 산술연산자로 비교하는 것보다 between을 사용하는것이 효율이 훨씬 좋왔다.
condition |
datetime / > , < |
datetime / between |
unixtime / between |
unixtime / > , < |
Result |
13.61s |
3.55s |
3.35s |
3.14s |
여러번 수행하면 약간씩의 차이는 있으나, 전체적인 검색 우위에 영향을 주는 범위는 아니였다.
결론. datetime을 대소 비교로 구간 검색을 할 경우 데이터가 누적 될수록 속도가 현저히 느려지는 현상이 발생 한다.
datetime형 필드를 구간 검색을 할때는 반드시 between을 사용하는 것을 추천한다.
오랜만에 이런걸 해보니 작성하는데 한참 걸리네 ㅎ
이상.
2018/02/27 - [DBMS/MySQL] - MySQL InnoDB DATETIME vs Unixtime (int type) #1
2018/02/27 - [DBMS/MySQL] - MySQL InnoDB DATETIME vs Unixtime (int type) #2
2018/02/27 - [DBMS/MySQL] - MySQL InnoDB DATETIME vs Unixtime (int type) #3 (1.5억건)
2018/02/27 - [DBMS/MySQL] - MySQL InnoDB DATETIME vs Unixtime (결론) #4 <