幾種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-&gt;query('delete?from?`test`');  //開始計時  $start_time?=?time();  $sum?=?1000000;  //?測試選項  $num?=?1;  if?($num?==?1){  ????//?單條插入  ????for($i?=?0;?$i?query("insert?into?`test`?(`id`,`name`)?values?($i,'tsetssdf')");  ????}  }?elseif?($num?==?2)?{  ????//?批量插入,為了不超過max_allowed_packet,選擇每10萬插入一次  ????for?($i?=?0;?$i?query("insert?into?`test`?(`id`,?`name`)?values?$values");  ????????????}?else?{  ????????????????$values?.=?",($i,?'testtest')";  ????????????????$db-&gt;query("insert?into?`test`?(`id`,?`name`)?values?$values");  ????????????}  ????????????break;  ????????}  ????????if?($i%100000?==?0)?{?//平常只有在這個情況下才插入  ????????????if?($i?==?0){  ????????????????$values?=?"($i,?'testtest')";  ????????????}?else?{  ????????????????$db-&gt;query("insert?into?`test`?(`id`,?`name`)?values?$values");  ????????????????$values?=?"($i,?'testtest')";  ????????????}  ????????}?else?{  ????????????$values?.=?",($i,?'testtest')";????  ????????}  ????}  }?elseif?($num?==?3)?{  ????//?事務插入  ????$db-&gt;beginTransaction();?  ????for($i?=?0;?$i?query("insert?into?`test`?(`id`,`name`)?values?($i,'tsetssdf')");  ????}  ????$db-&gt;commit();  }?elseif?($num?==?4)?{  ????//?文件load?data  ????$filename?=?dirname(__FILE__).'/test.sql';  ????$fp?=?fopen($filename,?'w');  ????for($i?=?0;?$i?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),?"KBn";

這里簡單寫一下 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);

? 版權聲明
THE END
喜歡就支持一下吧
點贊7 分享