티스토리 뷰

개발 노트/MySql

대량 데이터 MySql Insert 하기

오리지날초이 2013. 7. 3. 15:40

1. MySql 에서 단건 insert 와 load data 성능 비교

 


내가 하려는 일은 10대의 Oracle 에서 특정일자 데이터를 추출, 정제 후 다시 통계자료로 MySql 에 입력하려는 일이다.


우선 쉘스크립트를 사용해서 mysql 에 insert 테스트를 해봤다.

테스트 데이터는 1대 DB 의 하루치 데이터 80,737 건을 기준으로 삼았다.


쉘스크립트를 사용한 오라클 데이터 추출, 정제는 시간이 거의 걸리지 않지만 

mysql 에 8만건을 전부 isnert 해보니 6분 55초 가 걸렸다.


1대를 처리하는데 7분이면 10대를 처리하려면 70분이 걸릴 것이고 위와 같은 성능으로는 문제가 있어 보인다.

그렇다면 mysql load data 를 활용해보자.


동일한 80,737 건에 대해 0.3초가 채 안걸렸다


mysql> load data infile '/mywork/test.txt' into table my_table (my_column);

Query OK, 80737 rows affected (0.27 sec)

Records: 80737  Deleted: 0  Skipped: 0  Warnings: 0

 

 

 


 

2. Mysql load data 사용하기

 

데이터 insert 를 하기 위해서는 아래와 같은 구문을 사용해야 한다.
- insert into mytable (column1, column2, column3 ) values on ( value1, value2, sysdate() ); 

mysql load data 로 sysdate 를 활용하여 데이터 insert 하기 위해서 시도한 과정을 순서대로 나열한다.

우선 load data 용 텍스트 파일을 작성하고 명령을 수행해보자
[sample.txt]
value10, value20, value30
value11, value21, value31
value12, value22, value32

mysql> load data infile '/mywork/sample.txt' into table my_table ( col1, col2, col3 );
Query OK, 80737 rows affected, 65535 warnings (0.34 sec)
Records: 80737  Deleted: 0  Skipped: 0  Warnings: 161474

mysql> select * from my_table limit 3;
+---+-------------------------+-------+--------+
| id |                 col1              | col2    | col3      |
+ --+-------------------------+-------+--------+
| 1 | value10, value20, value30 | null     | null     |
| 2 | value11, value21, value31 | null     | null     |
| 3 | value12, value22, value32 | null     | null     |
+ --+-------------------------+-------+--------+
3 rows in set (0.00 sec)

col1 에 "value10, value20, value 30" 이 col1 에 전부 들어가 있다.

따라서 아래와 같이 fields terminated by ',' 옵션을 추가해줘야 하는데 생각과는 달리 에러가 발생한다.
mysql> load data infile '/mywork/sample' into table my_table ( col1, col2, col3 ) fields terminated by ',';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fields terminated by ','' at line 1

이를 해결하기 위해서 구분자를 , 가 아니라 \t 으로 변환해서 sample.txt 를 작성해보자
[sample.txt]
value10    value20    value30
value11    value21    value31
value12,   value22,   value32

mysql> load data infile '/mywork/sample.txt' into table my_table ( col1, col2, col3 );
Query OK, 80737 rows affected, 65535 warnings (0.34 sec)
Records: 80737  Deleted: 0  Skipped: 0  Warnings: 161474

mysql> select * from my_table limit 3;
+---+--------+--------+---------+
| id |  col1    |   col2   |   col3    |
+ --+--------+--------+---------+
| 1 | value10 | value20 | value30 |
| 2 | value11 | value21 | value31 |
| 3 | value12 | value22 | value32 |
+--+ --------+--------+---------+
3 rows in set (0.00 sec)


fields terminated by ',' 옵션이 유효하지 않은 원인은 찾지 못했지만 구분자를 '\t' 으로 대체해서 문제를 해결할 수 있을 것 같다
이제 원래의 목적으로 돌아가 sample.txt 의 col3 부분을 sysdate() 로 바꿔본 후 테스트해보자

[sample.txt]
value10    value20    sysdate()
value11    value21    sysdate()
value12    value22    sysdate()

mysql> load data infile '/mywork/sample.txt' into table my_table ( col1, col2, col3 );
Query OK, 80737 rows affected, 65535 warnings (0.34 sec)
Records: 80737  Deleted: 0  Skipped: 0  Warnings: 161474

mysql> select * from my_table limit 3;
+---+--------+--------+--------------------+
| id |  col1    |   col2   |           col3             |
+ --+--------+--------+--------------------+
| 1 | value10 | value20 | 0000-00-00 00:00:00 |
| 2 | value11 | value21 | 0000-00-00 00:00:00 |
| 3 | value12 | value22 | 0000-00-00 00:00:00 |
+--+ --------+--------+--------------------+
3 rows in set (0.00 sec)


sample.txt 에 sysdate() 를 입력해두어도 제대로 동작하지 않음을 확인할 수 있다.
하지만 set 옵션을 통해 문제를 해결할 수 있었다.

[sample.txt]
value10    value20    
value11    value21    
value12    value22    

mysql> load data infile '/mywork/sample.txt' into table my_table ( col1, col2 ) set col3 = sysdate();
Query OK, 80737 rows affected, 65535 warnings (0.34 sec)
Records: 80737  Deleted: 0  Skipped: 0  Warnings: 161474

mysql> select * from my_table limit 3;
+---+--------+--------+--------------------+
| id |  col1    |   col2   |           col3             |
+ --+--------+--------+--------------------+
| 1 | value10 | value20 | 2013-07-05 17:15:33 |
| 2 | value11 | value21 | 2013-07-05 17:15:33 |
| 3 | value12 | value22 | 2013-07-05 17:15:33 |
+--+ --------+--------+--------------------+
3 rows in set (0.00 sec)


 

 






728x90
반응형
공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
«   2024/03   »
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
글 보관함