不管是日常業務數據處理中，還是數據庫的導入導出，都可能遇到需要處理大量數據的插入。插入的方式和數據庫引擎都會對插入速度造成影響，這篇文章旨在從理論和實踐上對各種方法進行分析和比較，方便以后應用中插入方法的選擇。
插入分析
mysql中插入一個記錄需要的時間由下列因素組成，其中的數字表示大約比例：
-
連接：（3）
-
發送查詢給服務器：（2）
-
分析查詢：（2）
-
插入記錄：（1x記錄大小）
-
插入索引：（1x索引）
-
關閉：（1）
如果我們每插入一條都執行一個SQL語句，那么我們需要執行除了連接和關閉之外的所有步驟N次，這樣是非常耗時的，優化的方式有一下幾種：
-
在每個insert語句中寫入多行，批量插入
-
將所有查詢語句寫入事務中
-
利用Load Data導入數據
每種方式執行的性能如下。
Innodb引擎
InnoDB 給 MySQL 提供了具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行鎖(locking on row level)以及外鍵約束(FOREIGN KEY constraints)。
InnoDB 的設計目標是處理大容量數據庫系統，它的 CPU 利用率是其它基于磁盤的關系數據庫引擎所不能比的。在技術上，InnoDB 是一套放在 MySQL 后臺的完整數據庫系統，InnoDB 在主內存中建立其專用的緩沖池用于高速緩沖數據和索引。
測試環境
Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16
總數100W條數據
插入完后數據庫大小38.6MB（無索引），46.8（有索引）
-
無索引單條插入 總耗時：229s 峰值內存：246KB
-
有索引單條插入 總耗時：242s 峰值內存：246KB
-
無索引批量插入 總耗時：10s 峰值內存：8643KB
-
有索引批量插入 總耗時：16s 峰值內存：8643KB
-
無索引事務插入 總耗時：78s 峰值內存：246KB
-
有索引事務插入 總耗時：82s 峰值內存：246KB
-
無索引Load Data插入 總耗時：12s 峰值內存：246KB
-
有索引Load Data插入 總耗時：11s 峰值內存：246KB
MyIASM引擎
MyISAM 是MySQL缺省存貯引擎。設計簡單，支持全文搜索。
測試環境
Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16
總數100W條數據
插入完后數據庫大小19.1MB（無索引），38.6（有索引）
-
無索引單條插入 總耗時：82s 峰值內存：246KB
-
有索引單條插入 總耗時：86s 峰值內存：246KB
-
無索引批量插入 總耗時：3s 峰值內存：8643KB
-
有索引批量插入 總耗時：7s 峰值內存：8643KB
-
無索引Load Data插入 總耗時：6s 峰值內存：246KB
-
有索引Load Data插入 總耗時：8s 峰值內存：246KB
總結
我測試的數據量不是很大，不過可以大概了解這幾種插入方式對于速度的影響，最快的必然是Load Data方式。這種方式相對比較麻煩，因為涉及到了寫文件，但是可以兼顧內存和速度。
測試代碼
<?php $dsn = 'mysql:host=localhost;dbname=test'; $db = new PDO($dsn,'root','',array(PDO::ATTR_PERSISTENT =>?true)); //刪除上次的插入數據 $db->query('delete?from?`test`'); //開始計時 $start_time?=?time(); $sum?=?1000000; //?測試選項 $num?=?1; if?($num?==?1){ ????//?單條插入 ????for($i?=?0;?$i?query("insert?into?`test`?(`id`,`name`)?values?($i,'tsetssdf')"); ????} }?elseif?($num?==?2)?{ ????//?批量插入，為了不超過max_allowed_packet，選擇每10萬插入一次 ????for?($i?=?0;?$i?query("insert?into?`test`?(`id`,?`name`)?values?$values"); ????????????}?else?{ ????????????????$values?.=?",($i,?'testtest')"; ????????????????$db->query("insert?into?`test`?(`id`,?`name`)?values?$values"); ????????????} ????????????break; ????????} ????????if?($i%100000?==?0)?{?//平常只有在這個情況下才插入 ????????????if?($i?==?0){ ????????????????$values?=?"($i,?'testtest')"; ????????????}?else?{ ????????????????$db->query("insert?into?`test`?(`id`,?`name`)?values?$values"); ????????????????$values?=?"($i,?'testtest')"; ????????????} ????????}?else?{ ????????????$values?.=?",($i,?'testtest')";???? ????????} ????} }?elseif?($num?==?3)?{ ????//?事務插入 ????$db->beginTransaction();? ????for($i?=?0;?$i?query("insert?into?`test`?(`id`,`name`)?values?($i,'tsetssdf')"); ????} ????$db->commit(); }?elseif?($num?==?4)?{ ????//?文件load?data ????$filename?=?dirname(__FILE__).'/test.sql'; ????$fp?=?fopen($filename,?'w'); ????for($i?=?0;?$i?exec("load?data?infile?'$filename'?into?table?test?fields?terminated?by?','"); } $end_time?=?time(); echo?"總耗時",?($end_time?-?$start_time),?"秒n"; echo?"峰值內存",?round(memory_get_peak_usage()/1000),?"KBn"; ?>
以上就是MySQL大量數據插入各種方法性能分析與比較，更多相關內容請關注PHP中文網（www.php.cn）！
