몇가지 테스트를 해보았다.


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 <


이전글에서 생성한 6.6천만건의 데이터를 DB에 넣고 쿼리 테스트를 해본다.

예상되는 결과는 int 형이 압승일것 같은데, 일단 해보자.


실제 데이터가 들어 있는 테이블을 가정하기 위하여 

auto increment 를 넣고, 문자열, datetime 과 int type으로 테이블을 생성 하였다.


테이블 스크립트는 다음과 같다.

(일단 인덱스 없이 테스트하고, 추후 인덱스를 추가해서 얼마나 차이나는지를 확인 해보겠다.)

CREATE TABLE `performance_test` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `str100byte` varchar(100) NOT NULL,

  `date_type_date` datetime NOT NULL,

  `int_type_date` int(11) unsigned NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8  


데이터는 2000년 1월 1일 ~ 2001년 1월 1일 까지 데이터를 생성 하였다.

unix time으로는   946684800 ~ 978343199 (GMT +9 기준으로.)

MariaDB [performance_test]> select max(date_type_date), min(date_type_date) from performance_test;

+---------------------+---------------------+

| max(date_type_date) | min(date_type_date) |

+---------------------+---------------------+

| 2001-01-01 09:59:59 | 2000-01-01 00:00:00 |

+---------------------+---------------------+

1 row in set (2 min 9.18 sec)



검색기간 : 2001-06-01 ~ 2000-06-30


1. datetime 필드를 이용하여 검색(인덱스 없이)

MariaDB [performance_test]> 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'

    -> ;

+-----------+

| COUNT(id) |

+-----------+

|   5399995 |

+-----------+

1 row in set (2 min 12.10 sec)


MariaDB [performance_test]> 




2. unixtime 필드를 이용하여 검색(인덱스 없이)

MariaDB [performance_test]> 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')

    -> ;

+-----------+

| COUNT(id) |

+-----------+

|   5400000 |

+-----------+

1 row in set (2 min 11.18 sec)


MariaDB [performance_test]>  

어라.. 큰차이가 없다. (갯수가 다른것은 실은 조건식이 조금 잘못 됫다.^^)



[ 인덱스 추가 ]

MariaDB [performance_test]> ALTER TABLE `performance_test`

    -> ADD INDEX `date_type_date` (`date_type_date`),

    -> ADD INDEX `int_type_date` (`int_type_date`);

Stage: 1 of 1 'altering table'   99.8% of stage done


Query OK, 0 rows affected (13 min 3.47 sec)

Records: 0  Duplicates: 0  Warnings: 0


MariaDB [performance_test]>

데이터가 많긴 많데, 필드 2개를 인덱싱하는데 13분이나.. 

어찌됫것.. 다시 테스트..



3. date 필드를 이용하여 검색 (인덱스 사용)

MariaDB [performance_test]> 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'

    -> ;

+-----------+

| COUNT(id) |

+-----------+

|   5399995 |

+-----------+

1 row in set (1.79 sec)


MariaDB [performance_test]> 


4. unixtime 필드를 이용하여 검색 (인덱스 사용)

MariaDB [performance_test]> 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')

    -> ;

+-----------+

| COUNT(id) |

+-----------+

|   5400000 |

+-----------+

1 row in set (1.30 sec)


MariaDB [performance_test]> 


음... 생각보다 크진않네.;;

여러번 돌려봤는데, 

날짜형으로 비교 했을때는 1.7초대, 

숫자형으로 비교 했을때는 1.3초대. 약 0.4초 정도의 차이를 갖고 있다.

int 가 약 23%정도의 속도 향상이 있다.


좀 더 많은 데이터를 넣었을때 동일한 비율로 높아지는지 한번더 확인 해봐야 겟다.

최종결론은 1.5억건을 넣고 비교한 후 결론을 정리 하도록 하겠다.



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


오늘은 그동안 궁금했던

MYSQL 의 날짜 비교에 대하여 대량데이터를 기반으로 작성해본다.


DATETIME 형태와  Unix time을 int 형태로 디비에 저장하고,

속도 비교를 해본다.


먼저 대량 데이터를 만들기 위해서,

간단히 PHP Script 로 데이터를 생성 하였다.


1억건 이상의 데이터를 만들기 위해서

초당5건씩 1년치 데이터를 생성해 보았다.

5 * 3600sec * 24h * 365day = 157,680,000 (약 1.5억건)



MariaDB [performance_test]> LOAD DATA LOCAL INFILE '/root/testdata.txt'

    -> REPLACE INTO TABLE performance_test

    -> CHARACTER SET utf8

    -> FIELDS

    ->     TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

    -> LINES

    ->     TERMINATED BY '\n'

    -> IGNORE 1 LINES;

Stage: 2 of 2 'End bulk insert'      0% of stage done


Query OK, 66060000 rows affected, 65535 warnings (23 min 57.18 sec)

Records: 66060000  Deleted: 0  Skipped: 0  Warnings: 66060000

 


엇. 그런데 6.6천만건만 들어갓다..;;

데이터가 잘못 만들어진것 같다.


뭐 어찌 됫건, 6천만건도 적은 데이터는 아니기 때문에, 그냥 6천만건으로 진행 해본다.


참고로 PHP로 Raw data file 를 생성하는데 이 데이터를 생성하는것도 싱글프로세스로 하게 되면,

상당히 오랜 시간이 걸리기 때문에 fork를 이용해서 멀티 쓰레드로 구현해봤다.

(참고로 fork는 Unix/Linux 계열에서만 지원하는 것이기 때문에 윈도우php에서는 안될것이다.)


검색하다가 CPU 갯수만큼 쓰래드를 생성시켜서 돌리는 소스를 갖어다가 만들었는데,

검색 창이 닫혀서 출처를 찾을 수가 없네.;


멀티프로세스에 관심있거나, 직접 테스트를 해보고 싶은 사람은 참고해 본다. 

flat 데이터 생성 프로그램은 다음과 같다



int 와 datetime 성능의 상대적인 성능차를 확인 하기 위한 테스트이기 때문에

테스트 머진 성능은 중요하지 않으나, 다음과 같다.

- Maria DB 10.x 

- CentOS 7 64 bit

- Memory 8GB

- Core : 4 Core


MySQL ini 파일은  my-innodb-heavy-4G.cnf을 변경없이 그대로 사용하였다.



실제 DB 쿼리 테스트는 다음 글에서.....

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




+ Recent posts