MySQL大批量資料插入，PHP之for不斷插入時出現緩慢的解決方案及最佳化（轉載）

標籤：blog   http   ar   for   sp   檔案   資料   art   log   

     公司有一個項目，需要頻繁的插入資料到MySQL資料庫中，設計目標要求能支援平均每秒插入1000條資料以上。目前功能已經實現，不過一做壓力測試，探索資料庫成為瓶頸，每秒僅能插入100多條資料，遠遠達不到設計目標。 

     到MySQL官方網站查了查資料，發現MySQL支援在一條INSERT語句中插入多條記錄，格式如下： 
INSERT table_name (column1, column2, ..., columnN) 
VALUES (rec1_val1, rec1_val2, ..., rec1_valN), 
(rec2_val1, rec2_val2, ..., rec2_valN), 
... ... 
(recM_val1, recM_val2, ..., recM_valN); 

按MySQL官方網站，用這種方法一次插入多條資料，速度比一條一條插入要快很多。在一台開發用的膝上型電腦上做了個測試，果然速度驚人。 

測試環境：DELL Latitude D630， CPU T7250 @ 2.00GHz， 記憶體 2G。Windows XP Pro中文版SP2，MySQL 5.0 for Windows。 

MySQL是新安裝的，建立了一個名為test的資料庫，在test資料庫建了一個t_integer表，共兩個欄位：test_id和test_value，兩個欄位都是INTEGER類型，其中test_id是Primary Key。 

準備了兩個SQL指令檔（寫了個小程式產生的），內容分別如下： 

-- test1.sql 
TRUNCATE TABLE t_integer; 
INSERT t_integer (test_id, test_value) 
VALUES (1, 1234), 
(2, 1234), 
(3, 1234), 
(4, 1234), 
(5, 1234), 
(6, 1234), 
... ... 
(9997, 1234), 
(9998, 1234), 
(9999, 1234), 
(10000, 1234); 
-- test2.sql 
TRUNCATE TABLE t_integer; 
INSERT t_integer (test_id, test_value) VALUES (1, 1234); 
INSERT t_integer (test_id, test_value) VALUES (2, 1234); 
INSERT t_integer (test_id, test_value) VALUES (3, 1234); 
INSERT t_integer (test_id, test_value) VALUES (4, 1234); 
INSERT t_integer (test_id, test_value) VALUES (5, 1234); 
INSERT t_integer (test_id, test_value) VALUES (6, 1234); 
... ... 
INSERT t_integer (test_id, test_value) VALUES (9997, 1234); 
INSERT t_integer (test_id, test_value) VALUES (9998, 1234); 
INSERT t_integer (test_id, test_value) VALUES (9999, 1234); 
INSERT t_integer (test_id, test_value) VALUES (10000, 1234); 

以上兩個指令碼通過mysql命令列運行，分別耗時0.44秒和136.14秒，相差達300倍。 

基於這個思路，只要將需插入的資料進行合并處理，應該可以輕鬆達到每秒1000條的設計要求了。 

補充：以上的測試都是在InnoDB表引擎基礎上進行的，而且是AUTOCOMMIT=1，對比下來速度差異非常顯著。之後我將t_integer表引擎設定為MyISAM進行測試，test1.sql執行時間為0.11秒，test2.sql為1.64秒。 

補充2：以上的測試均為單機測試，之後做了跨機器的測試，測試用戶端（運行指令碼的機器）和伺服器是不同機器，伺服器是另一台筆記本，比單機測試時配置要好些。做跨機器的測試時，發現不管是InnoDB還是MyISAM，test1.sql速度都在0.4秒左右，而test2.sql在InnoDB時且AUTOCOMMIT=1時要80多秒，而設定為MyISAM時也要20多秒。 

方法一：用PHP構造一次插入多條，並多次以for插入，像裝車一樣，一車一車的把sql運過去。 

<?phpfor($i=0;$i<10;$i++){mysql_query("insert into table_name (name,age) values (‘name1‘,‘age1‘),(‘name2‘,‘age2‘); //一車：i=1，第二車：i=2}?>

方法二：如果是網路和迴圈太慢，則建議用C來做這個事情，會提高三倍左右的速度，相對PHP可能會有相當的提升。 
方法三：用隊列來做，分多個進程實現，可能有一定程式的時間上提高，同時要注意binlog的影響及索引的影響。BY：Jack 

 

 引自：http://www.tuicool.com/articles/zQV7zi

 

部落格聲明： 

　　本部落格中的所有文章，除標題中註明“轉載”字樣外，其餘所有文章均為本人原創或在查閱資料後總結完成，引用非轉載文章時請註明此聲明。—— 部落格園-pallee

MySQL大批量資料插入，PHP之for不斷插入時出現緩慢的解決方案及最佳化（轉載）