How to improve the speed of mysql inserting data

This article introduces the relevant knowledge of "how to improve the speed of mysql inserting data". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

20 million records need to be inserted in mysql. The insertion speed with insert statement is very limited, hundreds per second. Running on hadoop cluster is the same speed. It may be a problem, and the insert speed of sql server and oracle on the Internet is not very fast. The simpler optimization methods are 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,...)

The insertion speed can be increased many times, but it is still not enough. For 20 million records, the insertion speed of one or two thousand records per second is still too slow.

2. Import data from a text file

Records can be imported directly from a text file, provided that the text file is a line record, and each field is separated by the same character, and each line is separated by the same character.

After writing a program to process the format of the text file, you can import data on the mysql client using the following statement:

[sql]

Mysql > LOAD DATA LOCAL INFILE 'fileName' INTO TABLE' tableName' FIELDS TERMINATED BY'\ t 'LINES TERMINATED BY'\ n'

Where'\ t' and'\ n' are separators for fields and rows, respectively, and may be different in different situations.

In this way, it feels that the speed of import is mainly related to the size of the file, but not to the number of records (maybe 20 million records are not enough. )

Import a 800MB text file (20 million lines). It takes 3 minutes to preprocess on a single machine and 7 minutes to import the database (machine configuration is i5-2400CPU, 8GB memory, hard disk read speed is about 90MB/S)

This is the end of the content of "how to improve the speed of mysql data insertion". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!