不管是日常業(yè)務(wù)數(shù)據(jù)處理中，還是數(shù)據(jù)庫的導(dǎo)入導(dǎo)出，都可能遇到需要處理大量數(shù)據(jù)的插入。插入的方式和數(shù)據(jù)庫引擎都會對插入速度造成影響，這篇文章旨在從理論和實踐上對各種方法進行分析和比較，方便以后應(yīng)用中插入方法的選擇。

插入分析

MySQL中插入一個記錄需要的時間由下列因素組成，其中的數(shù)字表示大約比例：

連接：（3）發(fā)送查詢給服務(wù)器：（2）分析查詢：（2）插入記錄：（1x記錄大小）插入索引：（1x索引）關(guān)閉：（1）

如果我們每插入一條都執(zhí)行一個SQL語句，那么我們需要執(zhí)行除了連接和關(guān)閉之外的所有步驟N次，這樣是非常耗時的，優(yōu)化的方式有一下幾種：

在每個insert語句中寫入多行，批量插入將所有查詢語句寫入事務(wù)中利用Load Data導(dǎo)入數(shù)據(jù)

每種方式執(zhí)行的性能如下。

Innodb引擎

InnoDB 給 MySQL 提供了具有事務(wù)(commit)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行鎖(locking on row level)以及外鍵約束(FOREIGN KEY constraints)。

InnoDB 的設(shè)計目標(biāo)是處理大容量數(shù)據(jù)庫系統(tǒng)，它的 CPU 利用率是其它基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能比的。在技術(shù)上，InnoDB 是一套放在 MySQL 后臺的完整數(shù)據(jù)庫系統(tǒng)，InnoDB 在主內(nèi)存中建立其專用的緩沖池用于高速緩沖數(shù)據(jù)和索引。

總數(shù)100W條數(shù)據(jù)

插入完后數(shù)據(jù)庫大小38.6MB（無索引），46.8（有索引）

無索引單條插入 總耗時：229s 峰值內(nèi)存：246KB有索引單條插入 總耗時：242s 峰值內(nèi)存：246KB無索引批量插入 總耗時：10s 峰值內(nèi)存：8643KB有索引批量插入 總耗時：16s 峰值內(nèi)存：8643KB無索引事務(wù)插入 總耗時：78s 峰值內(nèi)存：246KB有索引事務(wù)插入 總耗時：82s 峰值內(nèi)存：246KB無索引Load Data插入 總耗時：12s 峰值內(nèi)存：246KB有索引Load Data插入 總耗時：11s 峰值內(nèi)存：246KBMyIASM引擎

MyISAM 是MySQL默認(rèn)存貯引擎。設(shè)計簡單，支持全文搜索。

總數(shù)100W條數(shù)據(jù)

插入完后數(shù)據(jù)庫大小19.1MB（無索引），38.6（有索引）

無索引單條插入 總耗時：82s 峰值內(nèi)存：246KB有索引單條插入 總耗時：86s 峰值內(nèi)存：246KB無索引批量插入 總耗時：3s 峰值內(nèi)存：8643KB有索引批量插入 總耗時：7s 峰值內(nèi)存：8643KB無索引Load Data插入 總耗時：6s 峰值內(nèi)存：246KB有索引Load Data插入 總耗時：8s 峰值內(nèi)存：246KB總結(jié)

我測試的數(shù)據(jù)量不是很大，不過可以大概了解這幾種插入方式對于速度的影響，最快的必然是Load Data方式。這種方式相對比較麻煩，因為涉及到了寫文件，但是可以兼顧內(nèi)存和速度。

測試代碼

<?php$dsn = ’mysql:host=localhost;dbname=test’;$db = new PDO($dsn,’root’,’’,array(PDO::ATTR_PERSISTENT => true));//刪除上次的插入數(shù)據(jù)$db->query(’delete from `test`’);//開始計時$start_time = time();$sum = 1000000;// 測試選項$num = 1;if ($num == 1){ // 單條插入 for($i = 0; $i < $sum; $i++){$db->query('insert into `test` (`id`,`name`) values ($i,’tsetssdf’)'); }} elseif ($num == 2) { // 批量插入，為了不超過max_allowed_packet，選擇每10萬插入一次 for ($i = 0; $i < $sum; $i++) {if ($i == $sum - 1) { //最后一次 if ($i%100000 == 0){$values = '($i, ’testtest’)';$db->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) { // 事務(wù)插入 $db->beginTransaction(); for($i = 0; $i < $sum; $i++){$db->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 < $sum; $i++){fputs($fp, '$i,’testtest’rn');} $db->exec('load data infile ’$filename’ into table test fields terminated by ’,’');}$end_time = time();echo '總耗時', ($end_time - $start_time), '秒n';echo '峰值內(nèi)存', round(memory_get_peak_usage()/1000), 'KBn';?>