Comparison of the methods of exporting and importing MySQL large amount of data

Hardware: Intel (R) Xeon (r) CPU 5130 @ 2.00GHz * 2, 4G RAM, 564G SAS

Software: Red Hat Enterprise Linux as Release 4 (Nahant Update 4) 2.6.9 42.ELSMP (32-bit), MySQL 5.0.27-standard-log

Total records: 1016126, average size of 46822 per line

1. Export Test 1.1 exported as text 方法: SELECT * INTO OUTFILE '/backup/yejr.txt' FROM yejr;
耗时: 3252.15 秒 1.2 exported as. sql file方法: mysqldump -t -n --default-character-set=latin1 test yejr > /backup/yejr.sql
耗时: 2124 sec

Conclusion: Using MYSQLUDMP to export data is a relatively fast method.

2. Import Test 2.1 Import TXT file 方法: mysql test < /backup/yejr.txt
耗时: 3317.62 sec 2.2 Import SQL file方法: mysql test < /backup/yejr.sql
耗时: 4706.618 sec

Conclusion:

1. Load data is a faster method

2. In the case of large data, it is best to create a good table, and also to create a good related index. Although importing is faster when there is no index, the total time to create the index after the data import is complete is much more time-consuming than if you had created it in advance.

In addition, if it is a MyISAM table, it is best to disable the index of the table before you import it, or to enable it after you have finished, or to start without creating an index, and then to create it after you import it, it will be much faster than updating the index at the same time.