不管是日常業務資料處理中，還是資料庫的匯入匯出，都可能遇到需要處理大量資料的插入。插入的方式和資料庫引擎都會對插入速度造成影響，這篇文章旨在從理論和實踐上對各種方法進行分析和比較，方便以後應用中插入方法的選擇。
插入分析
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方式。這種方式相對比較麻煩，因為涉及到了寫檔案，但是可以兼顧記憶體和速度。
測試程式碼
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
<?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"; ?> |