MySQL on MyISAM, InnoDB BULK INSERT Experience Summary

Source: Internet
Author: User
Tags bulk insert prepare

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.