MySQL large data import and export method comparison

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 Export as Text

Method: SELECT * into outfile '/backup/yejr.txt ' from Yejr;

Time consuming: 3252.15 seconds

1.2 exported as. sql files

Method: Mysqldump-t-n--default-character-set=latin1 test Yejr >/backup/yejr.sql

Time Consuming: 2124 sec

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

2. Import Test

2.1 Import TXT file

Method: MySQL Test </backup/yejr.txt

Time Consuming: 3317.62 sec

2.2 Importing SQL files

Method: MySQL Test </backup/yejr.sql

Time Consuming: 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.