幾種MySQL大量資料插入或修改的方法比較

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

插入分析

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

連接：（3）
發送查詢給伺服器：（2）
分析查詢：（2）
插入記錄：（1x記錄大小）
插入索引：（1x索引）
關閉：（1）

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

（1 ）在每個insert語句中寫入多行，批量插入

（2）將所有查詢語句寫入事務中

（3）利用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 = &#39;mysql:host=localhost;dbname=test&#39;;
$db = new PDO($dsn,&#39;root&#39;,&#39;&#39;,array(PDO::ATTR_PERSISTENT => true));
//删除上次的插入数据
$db->query(&#39;delete from `test`&#39;);
//开始计时
$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,&#39;tsetssdf&#39;)");
    }
} elseif ($num == 2) {
    // 批量插入，为了不超过max_allowed_packet，选择每10万插入一次
    for ($i = 0; $i < $sum; $i++) {
        if ($i == $sum - 1) { //最后一次
            if ($i%100000 == 0){
                $values = "($i, &#39;testtest&#39;)";
                $db->query("insert into `test` (`id`, `name`) values $values");
            } else {
                $values .= ",($i, &#39;testtest&#39;)";
                $db->query("insert into `test` (`id`, `name`) values $values");
            }
            break;
        }
        if ($i%100000 == 0) { //平常只有在这个情况下才插入
            if ($i == 0){
                $values = "($i, &#39;testtest&#39;)";
            } else {
                $db->query("insert into `test` (`id`, `name`) values $values");
                $values = "($i, &#39;testtest&#39;)";
            }
        } else {
            $values .= ",($i, &#39;testtest&#39;)";    
        }
    }
} elseif ($num == 3) {
    // 事务插入
    $db->beginTransaction(); 
    for($i = 0; $i < $sum; $i++){
        $db->query("insert into `test` (`id`,`name`) values ($i,&#39;tsetssdf&#39;)");
    }
    $db->commit();
} elseif ($num == 4) {
    // 文件load data
    $filename = dirname(__FILE__).&#39;/test.sql&#39;;
    $fp = fopen($filename, &#39;w&#39;);
    for($i = 0; $i < $sum; $i++){
        fputs($fp, "$i,&#39;testtest&#39;\r\n");    
    }
    $db->exec("load data infile &#39;$filename&#39; into table test fields terminated by &#39;,&#39;");
}
$end_time = time();
echo "总耗时", ($end_time - $start_time), "秒\n";
echo "峰值内存", round(memory_get_peak_usage()/1000), "KB\n";

這裡簡單寫一下SQL 語句插入和修改語句的拼接形式：

（1）insert 語句拼接：

INSERT INTO user (id, name) VALUES (1, '張三');

INSERT INTO user (id, name) VALUES (2, '李四');
INSERT INTO user (id, name) VALUES ( 3, '王五');

可拼接為：

INSERT INTO user (id, name) VALUES 

(1, '張三'), 
(2 , '李四'), 
(3, '王五');

（2）update 語句拼接：

update user set name='張三' where id= '1';

update user set name='李四' where id='2';
update user set name='王五' where id='3';

可拼接為：

update user 

set name = CASE id 
when 1 then '張三' 
when 2 then '李四' 
when 3 then '王五' 
#end 
where id IN (1,2,3);

以上就是幾種MySQL大量資料插入或修改的方法比較，希望對大家有幫助。

相關教學：

mysql大量資料插入探討（量變引起質變）_MySQL

求解：資料插入不到資料庫。解決方案

php沒辦法將資料插入mysql

以上是幾種MySQL大量資料插入或修改的方法比較的詳細內容。更多資訊請關注PHP中文網其他相關文章！