不管是日常業務數據處理中，還是數據庫的導入導出，都可能遇到需要處理大量數據的插入。插入的方式和數據庫引擎都會對插入速度造成影響，這篇文章旨在從理論和實踐上對各種方法進行分析和比較，方便以後應用中插入方法的選擇。
插入分析
MySQL中插入一個記錄需要的時間由下列因素組成，其中的數字表示大約比例：
如果我們每插入一條都執行一個SQL語句，那麼我們需要執行除了連接和關閉之外的所有步驟N次，這樣是非常耗時的，優化的方式有一下幾種：
每種方式執行的性能如下。
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有索引）
MyIASM引擎
MyISAM 是MySQL缺省存貯引擎。設計簡單，支持全文搜索。
測試環境
Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16
總數100W條數據
插入完後數據庫大小19.1MB無索引），38.6有索引）
總結
我測試的數據量不是很大，不過可以大概了解這幾種插入方式對於速度的影響，最快的必然是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";
- ?>
以上就是MySQL大量數據插入各種方法性能分析與比較，希望能幫到你。
博文出處：http://yansu.org/2014/04/16/insert-large-number-of-data-in-mysql.html
