程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MYSQL入門知識 >> MySQL大量數據插入各種方法性能分析與比較

MySQL大量數據插入各種方法性能分析與比較

編輯:MYSQL入門知識
 

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

插入分析

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 < $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) {
// 事務插入
$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 "峰值內存", round(memory_get_peak_usage()/1000), "KB\n";

?>

 
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved