MySQL on MyISAM, InnoDB BULK INSERT Experience Summary

Today to do a function encountered data bulk insertion problem, test the next few cases of simple comparison, although the test method is not comprehensive but also can draw the basic results, it does not waste time.

Several key points for bulk INSERT testing:

1. Engine: MyISAM and InnoDB
2. SQL Syntax:
   A:insert into TABLE filed1, filed2 ... VALUES (Val1, Val2, ...)
   B:insert into TABLE filed1, filed2 ... VALUES (Val1, Val2, ...), (Val1, Val2, ...), (Val1, Val2, ...) ...
3. Enabling transactions and not enabling transactions under InnoDB drivers
4. Drive mode: PDO uses precompiled placeholder mode

The specific process is not good to write, directly summed up the following:

1. The fastest way.

Engine: The choice engine, of course, is MyISAM;

syntax: above SQL syntax b mode. An INSERT statement follows the VALUES section followed by N each row of data to be inserted;

I'm using the PDO placeholder method, and all VALUES are followed by a question mark form placeholder.
For placeholders, it seems that the maximum number of PDO is 65,535, so the number of rows per insert is different, depending on the number of fields in the INSERT statement.
In my test example, there are 17 fields in each line with placeholders, so you can enter up to 3855 rows of data at a time, and if you don't use a placeholder, it doesn't seem to be a limit (I haven't tried it).

2. Places that are prone to mistakes

Use the InnoDB engine, but bulk insert in the normal way, do not start the transaction to commit

In particular, using the above SQL syntax a way, each row with the field name, inserting 4000 rows about 144 seconds, slow to die.
With a transaction or using SQL Syntax B, the speed is reduced to about 0.5 seconds, or 288 times times the difference.

3. Paste the code below

Source: Can not be directly used to see the meaning of understanding the line

    /** * Generate fields for inserting data and corresponding placeholders and bind data * @param null|array $data * @param int $rows * @retu RN String * /    protected  function getinsertfieldvalues(array $data, $rows) {        $data&&$this->data ($data);$data=$this->getdata ();$fields=$holders=$bind=Array();foreach($data  as $k=$v) {$fields[] =$k;$holders[] ='? '; }$vals=', ('. Implode (', ',$holders) .' ) ';return ' ('. Implode (', ',$fields) .') VALUES '. LTrim (Str_repeat ($vals,$rows),', '); }

    /** * Share multiple rows of data with an INSERT statement for bulk insertion * The fastest, but only a large block of elements when the insertion fails, but not to a single row * Second, the total number of insert placeholders can not exceed 65,535 each time, the program will automatically determine the placeholder Number and pagination of the list Insert * This method does not vary much between the InnoDB engine and the MyISAM engine speed * @param Array $list * @return Int|array Successful Returns the number of rows, failing to return all elements that contain the wrong part (but not exactly one row) * @throws Exception */    protected  function addallmoreinrow(array $list) {        $rowsNum= Count ($list);if(isset($list[0]))$row=$list[0];Else{$row= Array_shift ($list); Array_unshift ($list,$row); }//number of placeholders per row        $holdersNum= Count ($row);//Calculate the maximum number of elements per block for an array split        $chunkSize= Min (Array(50000, Floor (65535/$holdersNum)));//Get BULK INSERT statement template and pre-compile        $query[0] =' INSERT into ';$query[1] =$this->gettable ();$query[2] =$this->getinsertfieldvalues ($row,$chunkSize);$this->prepare ($query);//Save bound data that has been set before        $defaultBind=$this->bind?$this->bind:Array();//Array by page group        $list= Array_chunk ($list,$chunkSize,true);//Last page index        $last= Count ($list) -1;//Divide the array into separate pieces to bind data and then insert by page        foreach($list  as $p=$block) {//Last page due to inconsistent quantity you need to reset the precompiled template            if($p==$last) {$this->reset ();$query[2] =$this->getinsertfieldvalues ($row, COUNT ($block));$this->prepare ($query); }$this->bind =$defaultBind;foreach($block  as $r=$data) {foreach($data  as $k=$v) {$this->bind[] =$v; }            }$result=$this->execute ($this->bind);if(!$result)return $block; }$this->reset ();return $rowsNum; }

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced. Http://blog.csdn.net/zhouzme

MySQL on MyISAM, InnoDB BULK INSERT Experience Summary