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

插入分析

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

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

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

1. 在每個insert語句中寫入多行，批量插入
2. 將所有查詢語句寫入事務(wù)中
3. 利用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ù)和索引。

測試環(huán)境

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

總數(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)存：246KB

MyIASM引擎

MyISAM 是MySQL缺省存貯引擎。設(shè)計簡單，支持全文搜索。

測試環(huán)境

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

總數(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)存和速度。

測試代碼

 
 
 
 

  
  
  
  
  
  
  
  
$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'\r\n"); 
  
  
  
  
} 
  
  
  
  
$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), "KB\n"; 
  
  
  
  
 
  
  
  
  
?> 
 
 
 
 

以上就是MySQL大量數(shù)據(jù)插入各種方法性能分析與比較，希望能幫到你。

博文出處：http://yansu.org/2014/04/16/insert-large-number-of-data-in-mysql.html

分享標(biāo)題：大數(shù)據(jù)量下MySQL插入方法的性能比較
當(dāng)前鏈接：http://uogjgqi.cn/article/cogecej.html