MySQL批量數(shù)據(jù)插入或修改的幾種方法比較

本文主要給大家介紹MySQL批量數(shù)據(jù)插入或修改的幾種方法比較，文章內(nèi)容都是筆者用心摘選和編輯的，具有一定的針對(duì)性，對(duì)大家的參考意義還是比較大的，下面跟筆者一起了解下MySQL批量數(shù)據(jù)插入或修改的幾種方法比較吧。                                                            

插入分析

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

連接：（3）
發(fā)送查詢給云服務(wù)器：（2）
分析查詢：（2）
插入記錄：（1x記錄大?。?br/>插入索引：（1x索引）
關(guān)閉：（1）

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

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

（2）將所有查詢語(yǔ)句寫(xiě)入事務(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 利用率是其它基于磁盤(pán)的關(guān)系數(shù)據(jù)庫(kù)引擎所不能比的。在技術(shù)上，InnoDB 是一套放在 MySQL 后臺(tái)的完整數(shù)據(jù)庫(kù)系統(tǒng)，InnoDB 在主內(nèi)存中建立其專(zhuān)用的緩沖池用于高速緩沖數(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)樯婕暗搅藢?xiě)文件，但是可以兼顧內(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";

這里簡(jiǎn)單寫(xiě)一下 SQL 語(yǔ)句插入和修改語(yǔ)句的拼接形式：

（1）insert 語(yǔ)句拼接：

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 語(yǔ)句拼接：

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);

看完以上關(guān)于MySQL批量數(shù)據(jù)插入或修改的幾種方法比較，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業(yè)知識(shí)信息 ，可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。