Speed up mysql data insertion _ MySQL

To increase the speed of mysql data insertion, bitsCN.com needs to insert 20 million records in mysql. the insert statement is very fast, and hundreds of records are inserted every second. this is also the speed of running on the hadoop cluster, it may be a problem with the database. the insert speed of SQL server and oracle is not very fast on the Internet. A simple optimization method is as follows: 1. insert multiple records in an INSERT statement [SQL] insert INTO tablename (field0, field1 ,...) VALUES (value0, value1 ,...), (value0, value1 ,...), (value0, value1 ,...),... (value0, value1 ,...) in this way, the insertion speed can be improved many times, but it is still not enough. for 20 million records, the insertion speed of 1000 or 2000 records per second is still too slow. 2. importing data from text files mysql can directly import records from text files, but text files must be row records, in addition, each field is separated by the same character, and each line is also separated by the same character. After writing a program to process the text file format, you can use the following statement on the mysql client to import data: [SQL] mysql> LOAD DATA LOCAL INFILE 'filename' INTO TABLE 'tablename' FIELDS TERMINATED BY '/t' LINES TERMINATED BY'/N '; '/t' and'/N' are the separators of fields and rows, which may be different under different circumstances.
In this way, I feel that the import speed is mainly related to the file size, and the number of records is not very relevant (it may be that there are not enough 20 million records ..) It takes 3 minutes to import a 20 million MB text file (rows) on a single machine for preprocessing, it took 7 minutes to import the database (the machine configuration was i5-2400CPU, 8 GB memory, hard disk reading speed was about 90 MB/S) and then it took another 11 GB of text files, which was estimated to run in the cluster. Author bhq2010bitsCN.com