分享insert into語句優化的小技巧

MYSQL批次插入資料庫實作語句效能分析

假定我們的表格結構如下

程式碼如下   

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語句我們會這麼寫：

程式碼如下 

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

　mysql允許我們在一個sql語句中批次插入數據，如下sql語句：

#程式碼如下   

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

上面看起來沒什麼問題，下面我來用sql語句優化的小技巧，下面會分別進行測試，目標是插入一個空的資料表200W條資料

第一種方法：使用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個小時多!

第二種方法：使用交易提交，批次插入資料庫(每隔10W條提交下)最後顯示消耗的時間為：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(&#39;COMMIT&#39;);
$connect_mysql->query(&#39;BEGIN&#39;);
}
}
$connect_mysql->query(&#39;COMMIT&#39;);
echo date("H:i:s");

#第三種方法：使用最佳化SQL語句：將SQL語句進行拼接，使用insert into table () values (),(),(),()然後再一次插入，如果字串太長，

　則需要設定下MYSQL，在mysql 命令列中執行：set global max_allowed_pa​​cket = 2*1024*1024*10;消耗時間為：11:24:06 11:25:06;

　插入200W條測試資料僅花了1分鐘!程式碼如下：

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

最後總結下，在插入大批量資料時，第一種方法無疑是最糟糕的，而第二種方法在實際應用中就比較廣泛，第三種方法在插入測試資料或其他低要求時比較合適，速度確實快。

【相關推薦】

1. 分享一條insert語句批次插入多筆記錄教學

2. 解析mysql中常用的三種插入語句及其區別

以上是分享insert into語句優化的小技巧的詳細內容。更多資訊請關注PHP中文網其他相關文章！