Mysql插入大数据

Question

for now im trying to optimize some codes..

What is a better way to insert a large data into table?

Consider this code is running.

$arrayOfdata = execute query (SELECT c.id FROM table1 ) 

getting all the data from table1 storing it to array and inserting it on the table.

 private function insertSomeData($arrayOfdata, $other_id){
      foreach($arrayOfdata as $data){
                INSERT INTO table (
                    other_id,
                    other2_id,
                    is_transfered
                ) VALUES ('.$other_id.', '.$data['id'].', 0)'
            }
    }

i know if it have 500k of data in table1 this code is very slow. so i tried something like this.. i put all in one sql query

INSERT INTO 
table (
    other_id,
    other2_id,
    is_transfered
) 
SELECT 
    "other_id", c.id, 0 
FROM table1 

I read that to much large of data to insert cause the mysql to slow down or timeout. i tried this code on 500k of data in my local computer and it runs well..

is there any way that it will cause a problem if large data will be insert? other ways for faster insert that would not cause the server to use to0 much resources?

Answer 1

Assuming that you are using InnoDB engine (which is default in most recent MySQL versions), you should simply use transactions: wrap your insert loop into BEGIN; ... COMMIT; block.

By default, every statement is run as transaction, and server must make sure that data makes it safely to disk before continuing to next statement. If you start transaction, then do many inserts, and then commit transaction, only then server must flush all the data onto the disk. On modern hardware, this could amount only to few disk operations instead of 500k of them.

Another consideration is to use prepared statements. Server has to parse every SQL statement before executing it. This parsing does not come for free, and it is not unusual that parsing time could be more expensive than actual query execution time. Usually, this parsing is done every single time, and for your case it is done 500k times. If you use prepared statements, parsing/preparation is done only once, and cost to execute statement is only disk write (which is enhanced further if you are within active transaction, because server can batch that write by delaying it until transaction commits).

Total improvement from using these methods could be dramatic - I have seen situations when using transactions reduced total run time from 30 minutes to 20 seconds.

This answer should give some idea how to use transactions in PHP with MySQL.