MYSQL批量插入數(shù)據(jù)庫(kù)實(shí)現(xiàn)語(yǔ)句性能分析

假定我們的表結(jié)構(gòu)如下

代碼如下

CREATE TABLE example (
example_id INT NOT NULL,
name VARCHAR( 50 ) NOT NULL,
value VARCHAR( 50 ) NOT NULL,
other_value VARCHAR( 50 ) NOT NULL
)

　通常情況下單條插入的sql語(yǔ)句我們會(huì)這么寫(xiě)：

代碼如下

INSERT INTO example
(example_id, name, value, other_value)
VALUES
(100, 'Name 1', 'Value 1', 'Other 1');

　mysql允許我們?cè)谝粭lsql語(yǔ)句中批量插入數(shù)據(jù)，如下sql語(yǔ)句：

代碼如下

INSERT INTO example
(example_id, name, value, other_value)
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

如果我們插入列的順序和表中列的順序一致的話，還可以省去列名的定義，如下sql

代碼如下

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

上面看上去沒(méi)什么問(wèn)題，下面我來(lái)使用sql語(yǔ)句優(yōu)化的小技巧，下面會(huì)分別進(jìn)行測(cè)試，目標(biāo)是插入一個(gè)空的數(shù)據(jù)表200W條數(shù)據(jù)

第一種方法：使用insert into 插入，代碼如下：

$params = array('value'=>'50');
set_time_limit(0);
echo date("H:i:s");
for($i=0;$i<2000000;$i++){
$connect_mysql->insert($params);
};
echo date("H:i:s");

最后顯示為：23:25:05 01:32:05 也就是花了2個(gè)小時(shí)多!

第二種方法：使用事務(wù)提交，批量插入數(shù)據(jù)庫(kù)(每隔10W條提交下)最后顯示消耗的時(shí)間為：22:56:13 23:04:00 ，一共8分13秒 ，代碼如下：

echo date("H:i:s");
$connect_mysql->query('BEGIN');
$params = array('value'=>'50');
for($i=0;$i<2000000;$i++){ 
$connect_mysql->insert($params);
if($i%100000==0){
$connect_mysql->query('COMMIT');
$connect_mysql->query('BEGIN');
}
}
$connect_mysql->query('COMMIT');
echo date("H:i:s");

第三種方法：使用優(yōu)化SQL語(yǔ)句：將SQL語(yǔ)句進(jìn)行拼接，使用 insert into table () values (),(),(),()然后再一次性插入，如果字符串太長(zhǎng)，

　則需要配置下MYSQL，在mysql 命令行中運(yùn)行 ：set global max_allowed_packet = 2*1024*1024*10;消耗時(shí)間為：11:24:06 11:25:06;

　插入200W條測(cè)試數(shù)據(jù)僅僅用了1分鐘!代碼如下：

$sql= "insert into twenty_million (value) values";
for($i=0;$i<2000000;$i++){
$sql.="('50'),";
};
$sql = substr($sql,0,strlen($sql)-1);
$connect_mysql->query($sql);

最后總結(jié)下，在插入大批量數(shù)據(jù)時(shí)，第一種方法無(wú)疑是最差勁的，而第二種方法在實(shí)際應(yīng)用中就比較廣泛，第三種方法在插入測(cè)試數(shù)據(jù)或者其他低要求時(shí)比較合適，速度確實(shí)快。

【相關(guān)推薦】

1. 分享一條insert語(yǔ)句批量插入多條記錄教程

2. 解析mysql中常用的三種插入語(yǔ)句及其區(qū)別

以上就是分享insert into語(yǔ)句優(yōu)化的小技巧的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！