史丹利愛碎念

MySQL 插入校能優化與測試

最近在實作的時候遇到

PHP在操作SQL的插入語法時

如果使用迴圈的方式，會耗掉不少時間和效能

尤其昰透過Excel匯入大量資料的時候更加明顯

因此今天介紹ㄧ個效能增加的方法

跟大家分享

1. 首先創建兩個結構相同的資料表 'sqltest_a'(優化前語法使用) 和 'sqltest_b'(優化後語法使用)

2. 開始寫存入的測試程式

//直接先把要存入的資料設定好，而且兩種存入方式都使用相同的資料，這樣才夠精準

$time1 = time();

$uid = "stanley543";

$cid = "43";

$twice = "4";

$classname = "英文";

//優化前的語法，ㄧ般我們都會這樣寫，把插入語法放在迴圈內

for($i=0; $i<100000; $i++){

 $sql = "INSERT INTO  `sqltest_a` (uid ,cid ,time ,twice ,classname)VALUES (  '".$uid."',  '".$cid."',  '".$time1."',  '".$twice."',  '".$classname."')";

 $query = mysql_query($sql);

}

 $time2 = time();

 $time3 = $time2-$time1 ;

 echo "100000次";

 echo "開始時間 = ".$time1."<br>";

 echo "執行結束時間 = ".$time2."<br>";

 echo "使用時間 = ".$time3."秒<br>";

//首先我們先看看優化前的結果

//接著，我們用同一組變數嘗試用優化後的寫法

$userdata = array();

 for($j=0; $j<100000; $j++){

 $userdata[] = '("'.$uid.'","'.$cid.'","'.$time1.'","'.$twice.'","'.$classname.'")';

}

 $sql_2 = "INSERT INTO  `sqltest_b` (uid ,cid ,time ,twice ,classname)VALUES ".implode(",",$userdata);

 $query_2 = mysql_query($sql_2);

//同樣的我們再來看看優化後的執行時間，這邊就不在重複時間計數程式了

你砍看~你砍看~~這樣美妙的數字到哪裡找呢 ?

尤其是大筆的資料匯入，只要欄位變多、PHP和Mysql伺服器分離或者資料再更多ㄧ些，ㄧ定會更明顯

原理在於

優化前PHP將資料存入資料庫時，必須：

( 打開資料庫 -> 連線 -> 找資料表 -> 把資料放進去 ) * 10萬次
 
優化後的程式則是：

( 打開資料庫 -> 連線 -> 找資料表 -> ) + 把資料放進去  * 10萬次

相信用這樣的方法，就能理解為什麼優化後的做法可以變得那麼快了吧...

最後再提供幾個數據讓大家參考參考

如果覺得對你有幫助的話. 請幫小弟按個讚吧～

 My SQL 相關文章：

MySql 基本語法(查詢、插入、更新) 

MySql 異地同步(MySQL Replication)  

SQL Injection 範例(登入範例)