Comparison of importing and exporting large data volumes in MySQL _ MySQL

Comparison of MySQL big data import and export methods hardware: Intel (R) Xeon (R) CPU 5130 @ 2.00 GHz * 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 number of records: 1016126, the average size of each line is 46822

1. export test

1.1 export to text

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

Duration: 3252.15 seconds

1.2 export data to a. SQL file

Method: mysqldump-t-n -- default-character-set = latin1 test yejr>/backup/yejr. SQL

Time consumption: 2124 sec

Conclusion: using mysqludmp to export data is a relatively fast method.

2. import test

2.1 import a txt file

Method: mysql test </backup/yejr.txt

Time consumption: 3317.62 sec

2.2 import SQL files

Method: mysql test </backup/yejr. SQL

Time consumption: 4706.618 sec

Conclusion:

1. using load data is a fast method

2. in the case of large data volumes, it is best to create a table and related indexes. although it is faster to import data without indexes, it takes a lot of time to create an index after the data is imported.

In addition, if it is a myisam table, it is best to disable the index of the table before importing, and enable it after importing; or do not create an index at the beginning, and then create after importing, it will be much faster than updating indexes at the same time of import.