大數(shù)據(jù)量的情況下MySQL插入方法的性能比較

這篇文章主要講解了“大數(shù)據(jù)量的情況下MySQL插入方法的性能比較”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“大數(shù)據(jù)量的情況下MySQL插入方法的性能比較”吧！

插入分析

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

• 連接：（3）

• 發(fā)送查詢給服務(wù)器：（2）

• 分析查詢：（2）

• 插入記錄：（1x記錄大?。?/p>

• 插入索引：（1x索引）

• 關(guān)閉：（1）

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

1. 在每個(gè)insert語(yǔ)句中寫入多行，批量插入

2. 將所有查詢語(yǔ)句寫入事務(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è)計(jì)目標(biāo)是處理大容量數(shù)據(jù)庫(kù)系統(tǒng)，它的 CPU 利用率是其它基于磁盤的關(guān)系數(shù)據(jù)庫(kù)引擎所不能比的。在技術(shù)上，InnoDB 是一套放在 MySQL 后臺(tái)的完整數(shù)據(jù)庫(kù)系統(tǒng)，InnoDB 在主內(nèi)存中建立其專用的緩沖池用于高速緩沖數(shù)據(jù)和索引。

測(cè)試環(huán)境

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

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

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

• 無(wú)索引單條插入 總耗時(shí)：229s 峰值內(nèi)存：246KB

• 有索引單條插入 總耗時(shí)：242s 峰值內(nèi)存：246KB

• 無(wú)索引批量插入 總耗時(shí)：10s 峰值內(nèi)存：8643KB

• 有索引批量插入 總耗時(shí)：16s 峰值內(nèi)存：8643KB

• 無(wú)索引事務(wù)插入 總耗時(shí)：78s 峰值內(nèi)存：246KB

• 有索引事務(wù)插入 總耗時(shí)：82s 峰值內(nèi)存：246KB

• 無(wú)索引Load Data插入 總耗時(shí)：12s 峰值內(nèi)存：246KB

• 有索引Load Data插入 總耗時(shí)：11s 峰值內(nèi)存：246KB

MyIASM引擎

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

測(cè)試環(huán)境

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

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

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

• 無(wú)索引單條插入 總耗時(shí)：82s 峰值內(nèi)存：246KB

• 有索引單條插入 總耗時(shí)：86s 峰值內(nèi)存：246KB

• 無(wú)索引批量插入 總耗時(shí)：3s 峰值內(nèi)存：8643KB

• 有索引批量插入 總耗時(shí)：7s 峰值內(nèi)存：8643KB

• 無(wú)索引Load Data插入 總耗時(shí)：6s 峰值內(nèi)存：246KB

• 有索引Load Data插入 總耗時(shí)：8s 峰值內(nèi)存：246KB

總結(jié)

我測(cè)試的數(shù)據(jù)量不是很大，不過(guò)可以大概了解這幾種插入方式對(duì)于速度的影響，最快的必然是Load Data方式。這種方式相對(duì)比較麻煩，因?yàn)樯婕暗搅藢懳募?，但是可以兼顧?nèi)存和速度。

測(cè)試代碼

<?php $dsn = 'mysql:host=localhost;dbname=test'; $db = new PDO($dsn,'root','',array(PDO::ATTR_PERSISTENT => true)); //刪除上次的插入數(shù)據(jù) $db->query('delete from `test`'); //開(kāi)始計(jì)時(shí) $start_time = time(); $sum = 1000000; // 測(cè)試選項(xiàng) $num = 1;  if ($num == 1){ // 單條插入 for($i = 0; $i < $sum; $i++){ $db->query("insert into `test` (`id`,`name`) values ($i,'tsetssdf')"); } } elseif ($num == 2) { // 批量插入，為了不超過(guò)max_allowed_packet，選擇每10萬(wàn)插入一次 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) { //平常只有在這個(gè)情況下才插入 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 "總耗時(shí)", ($end_time - $start_time), "秒\n"; echo "峰值內(nèi)存", round(memory_get_peak_usage()/1000), "KB\n";  ?>

感謝各位的閱讀，以上就是“大數(shù)據(jù)量的情況下MySQL插入方法的性能比較”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)大數(shù)據(jù)量的情況下MySQL插入方法的性能比較這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！