대량 데이터 MySql Insert 하기

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)