千萬筆資料寫入DB測試

　　前陣子找工作的空檔一時興起就寫了個威力彩的出號程式，和一般的亂數出號來說，我多加了一些統計分析的方式，再去計算出最有中獎相的幾組號碼出來做為參考。

　　前兩天突然想要擴大比對的範圍，如果全部的組合都拿來比對會如何？所以產生了一個全部組合的需求出來，於是用了PHP很簡單的寫了個威力彩的全部組合程式，非常暴力的用了七層For迴圈來產生號碼。

　　然後又想說得把這些數據放到DB去，所以就寫了語法要把資料插入MySQL中，結果第一次跑下來，一個小時跑了400多萬筆，全部2200多萬筆跑完估計要五六個小時，於是我就中止了程式，然後上網找資料，後來把Table的index和PK先拿掉，自動增號也取消改成直接寫入序號的方式，最大的改善是在迴圈中把原本一筆一個insert的語法改成先組合先好幾個Values的語法後再insert，這下時間縮到51分鐘左右，有進步，但因為在找資料的過程中有看到一些討論，所以我就想再精進一下。

　　於是我發現我原本直接在迴圈中做insert並不聰明，因為迴圈會逐層少1來跑，造成每次insert語法後面的values個數都不同，我試著把inser搬到不同層的迴圈去執行，用意是讓inser 一次的values數目可以多一些，但樣做沒有解決根本的問題，所以最多只能縮到22分鐘而已。



　　然後我又改了一下程式，多了幾個計數的變數去判斷目前串接的values量是否有到一定的數量，再去做inser，這樣就可以用穩定的values數量來做insert，實際的速度可以縮到最短14分鐘。

　　然後我後來終於找到一篇關於InnoDB和MyIsam兩種不同儲存引擎的討論，仔細檢查了一下，因為新的MySQL預設是使用InnoDB，所以我一直都是在使用InnoDB在跑，於是就有了下表的測試比較：

	10筆	100筆	1000筆	2000筆
innodb	35分14秒	18分33秒	14分28秒	24分17秒
myisam	7分39秒	4分18秒	12分28秒	22分30秒

　　其中的筆數是表示一個Inser語句中帶的Values量，就寫入速度來說差異非常明顯，但這其中還有一個語法的問題，並不是寫入的Values量多就快，也不是量少就慢，當中需要一些調校，還包括了my.ini中參數的影響，不過太細節的東西我也沒時間去研究。

　　我後來也很好奇如果InnoDB是預設的建議儲存引擎，效能差這麼大真的可以嗎？所以又查了一下資料，發現關鍵在於是否採用了交易的方式，於是小小研究一下後，又改了程式中關於寫入的語法，於是有了下表的測試比較：

	10筆	100筆	1000筆	2000筆
1次	35分14秒	18分33秒	14分28秒	24分17秒
10次	31分7秒	10分3秒	15分32秒	25分43秒
100次	9分27秒	6分18秒	13分55秒	24分15秒

　　其中的次數指的是執行了多少次的insert語句才做一次commit實際寫入硬碟的動作，這是InnoDB才有的行為，可以看得到寫入資料的速度已經有了明顯的差異，但是並不是次數提高速度就會變快，只能說是調校的好，速度真的有差，以上的測試都還沒有去動到my.ini中的設定，看一些神人的討論，如果my.ini調得好，效能可以再高個幾級。

　　最後我去找了一些關了InnoDB和MyIsam的差異，大致上的差異在於MyIsam簡單快速，適合小型資料庫的運作(本來就是為了這個目的才有MySQL啊)，InnoDB強調的是安全性，有完整的交易紀錄檔可以很容易就恢復資料或保證寫入資料的正確性，所以InnoDB的重點不在效能上，而是在安全性上。

　　當然，還有很多的寫入方式可以把速度再提升，留著以後再去研究和實作，因為自己找資料發現中文的相關資料真的很零碎，所以自己找到什麼就趕快整理一下然後記下來，免得以後忘了。