五個細節入手 幫助提升數據插入效率
插入數據，是MySQL數據庫的基本任務。不過不要小看這個插入數據的動做。在數據庫性能優化上，能夠在這上面作很大的文章。若是利用MySQL做爲一些即時信息化管理軟件的後臺數據庫，如ERP系統。因爲其數據插入的做業很是頻繁，並且對性能的要求也比較高。此時數據庫管理員就須要採起措施來提升數據插入的效率。針對這塊內容，筆者認爲數據庫工程師在應用程序與數據庫設計時，須要注意以下幾個細節問題。
細節一：同時插入多行記錄時，宜採用一條Insert語句
在數據插入時，每每須要同時往一個表中插入多條數據。如以ERP系統的採購訂單爲例。用戶在前臺客戶端錄入採購訂單時，有可能須要向同一個供應商同時採購多個原材料。此時在數據庫中，就須要往一個表格中同時插入多條記錄。在插入數據時，能夠經過兩個方式實現。一是採用多條Insert語句，每一個Insert語句插入一條記錄。另一種方式是隻採用一條Insert語句，插入多條語句。採起這兩種方式，有什麼差別呢?
從語言編寫的角度看，第一種方式比較清楚明瞭。可是從數據插入速度的角度來看，第二種方式性能比較好。特別是一次性插入一兩百條記錄時，二者所須要的時間會相差一倍以上。以下圖所示，就是使用一條Insert語句插入多條記錄的舉例。
總之在數據插入時，須要注意使用帶有多個值列表的Insert語句一次插入多行記錄要比使用一個單行插入語句快不少。並且隨着行數的增長，這個差別會愈來愈大。在數據庫設計時，若是有設計到同時插入多條數據的紀錄，如經過客戶端導入基礎數據、經過前置單據自動生成相關單據，這些做業都會遇到向某個表中一次性插入多條記錄的狀況。此時從提升數據庫性能的角度考慮，筆者建議採用一個Insert語句同時插入多條記錄的方式。雖說編寫語言的時候會增長複雜程度，可是這點投資與數據庫性能優化相比而言，是值得的。 細節二：批量插入記錄時，創建採用Load Date Infile語句 有時候可能須要往Mysql數據庫中批量導入數據。如在ERP系統初始化過程當中，須要將產品數據導入到系統中。實施顧問通常會要求用戶根據他們的格式準備好相關的數據，而後利用導入做業，將這些數據一次性導入到數據庫中。雖然這個導入做業是經過前臺客戶端來完成的，可是最終仍然是反映在數據庫中。 針對這種批量數據的導入，數據庫又提供了兩種方式。一是上面所介紹的，採用一個Insert語句插入多行記錄的方式;另一種就是採用Load Date Infile的方式。這兩種方式有什麼差別呢?Load Date Infile顧名思義，就是從一個文件中導入數據。爲此若是採用這種方式的話，用戶須要預先準備一個固定格式的文件。而Insert語句的話，沒有這個要求，其能夠直接利用其餘做業中得到的數據。從這個角度講，若是插入的紀錄數比較少(如在一百條左右)或者經過系統交互來得到數據，此時比較適合使用Insert語句。從性能的角度考慮，一般狀況下使用Load Date Infile語句的方式插入數據要比使用Insert語句速度快10到20倍。爲此若是插入的數據量比較多，如期初導入數據時可能會有超過上萬條的記錄，此時使用Load Date Infile的方式可以取得比較好的性能。 細節三：插入延遲，提升插入操做對系統的不利影響 在某些狀況下，用戶對插入數據的即時性要求可能並非很高，此時就能夠考慮經過插入延遲特性，來減小插入操做對系統資源的耗用。筆者再以ERP系統爲例。在ERP系統中，有一個安全庫存管理的做業。若是啓用了這個做業的話，那麼系統會在天天一個固定的時刻，分析帳上的庫存數據與安全庫存之間的關係。若是發現庫存數量低於安全庫存時，系統就會自動生成一張請購單，將差別的數據插入到後臺的數據庫表中。顯然，對於這些記錄，用戶並不須要等待數據的插入完成。如在前臺調用這個做業時，能夠將這個做業放在後端執行。等到執行完畢後，再通知用戶便可。 在遇到這種狀況時，就可使用插入延遲的功能。當數據庫工程師啓用插入延遲功能時，服務器會馬上返回，而不會在那邊等着數據插入完成。若是表中沒有被其餘線程所調用的狀況，那麼記錄會排隊等待被插入。使用這個插入延遲特性，還可以帶來另一個好處。當多個做業同時往同一個表中插入數據的時候，這些進程會被記錄在同一個Block中。這就好像將多個單獨的Insert語句合併成一條Insert語句來執行，其速度會快許多。 細節四：在插入大量數據以前，能夠先將表鎖定 在數據庫庫中，某些表只有特定的用戶才使用。並且每次使用時可能會同時插入多條數據。如在ERP系統中導入期初數據時，就會碰到這種狀況。從前臺客戶端導入產品數據時，一般狀況下中間會有一個過渡表。用戶能夠經過這個過渡表來覈對數據是否準確。若是沒有問題的話，再將其導入到產品信息表中。採起這種方式，用戶還能夠查詢到有哪些記錄沒有被導入到系統中。 因爲期初數據的導入，通常是有系統管理員來完成的。此時這張過渡表只有一個用戶使用。爲了提升數據插入的效率，數據庫工程師能夠考慮，在插入以前先將表鎖定。這能夠提升數據插入的性能。這主要是由於索引緩衝區只是在全部的插入語句完成以後纔對磁盤進行一次性刷新。也就是說，有多個插入語句就會有多少次索引緩衝區刷新。爲此在數據插入以前，將數據表進行鎖定，就能夠大幅度的提升數據插入的效率。 細節五：插入數據以前先對數據進行排序 衆所周知，在對記錄進行排序時，須要耗費必定的系統資源。若是在插入數據時，就可以考慮到排序問題，那麼在後續記錄查詢時，就能夠避免從新排序所形成的二次開銷。故筆者建議，在可行的狀況下，在對數據插入以前先對記錄根據必定的規則進行排序。 如在採購訂單行表中，同時插入多條語句(從物料申購單轉換爲採購訂單)。在採購訂單行中插入多條記錄以前，能夠根據用戶的使用習慣，如按照產品的編號進行排序。用戶在後續查詢時，就能夠直接使用查詢的結果(默認狀況下是根據記錄插入的前後順序來顯示結果的)。顯示時就不須要再進行一次排序做業，從而能夠提升數據後續查詢的效率。 上面筆者提到的這個些內容，其實都是插入做業中的一些細節問題。可是這些細節內容，對於提升數據庫的插入效率有很大的幫助。有時候單純的經過提升硬件配置來改善數據庫的性能，還不可以提到這麼明顯的效果。
細節一：同時插入多行記錄時，宜採用一條Insert語句
在數據插入時，每每須要同時往一個表中插入多條數據。如以ERP系統的採購訂單爲例。用戶在前臺客戶端錄入採購訂單時，有可能須要向同一個供應商同時採購多個原材料。此時在數據庫中，就須要往一個表格中同時插入多條記錄。在插入數據時，能夠經過兩個方式實現。一是採用多條Insert語句，每一個Insert語句插入一條記錄。另一種方式是隻採用一條Insert語句，插入多條語句。採起這兩種方式，有什麼差別呢?
從語言編寫的角度看，第一種方式比較清楚明瞭。可是從數據插入速度的角度來看，第二種方式性能比較好。特別是一次性插入一兩百條記錄時，二者所須要的時間會相差一倍以上。以下圖所示，就是使用一條Insert語句插入多條記錄的舉例。
總之在數據插入時，須要注意使用帶有多個值列表的Insert語句一次插入多行記錄要比使用一個單行插入語句快不少。並且隨着行數的增長，這個差別會愈來愈大。在數據庫設計時，若是有設計到同時插入多條數據的紀錄，如經過客戶端導入基礎數據、經過前置單據自動生成相關單據，這些做業都會遇到向某個表中一次性插入多條記錄的狀況。此時從提升數據庫性能的角度考慮，筆者建議採用一個Insert語句同時插入多條記錄的方式。雖說編寫語言的時候會增長複雜程度，可是這點投資與數據庫性能優化相比而言，是值得的。 細節二：批量插入記錄時，創建採用Load Date Infile語句 有時候可能須要往Mysql數據庫中批量導入數據。如在ERP系統初始化過程當中，須要將產品數據導入到系統中。實施顧問通常會要求用戶根據他們的格式準備好相關的數據，而後利用導入做業，將這些數據一次性導入到數據庫中。雖然這個導入做業是經過前臺客戶端來完成的，可是最終仍然是反映在數據庫中。 針對這種批量數據的導入，數據庫又提供了兩種方式。一是上面所介紹的，採用一個Insert語句插入多行記錄的方式;另一種就是採用Load Date Infile的方式。這兩種方式有什麼差別呢?Load Date Infile顧名思義，就是從一個文件中導入數據。爲此若是採用這種方式的話，用戶須要預先準備一個固定格式的文件。而Insert語句的話，沒有這個要求，其能夠直接利用其餘做業中得到的數據。從這個角度講，若是插入的紀錄數比較少(如在一百條左右)或者經過系統交互來得到數據，此時比較適合使用Insert語句。從性能的角度考慮，一般狀況下使用Load Date Infile語句的方式插入數據要比使用Insert語句速度快10到20倍。爲此若是插入的數據量比較多，如期初導入數據時可能會有超過上萬條的記錄，此時使用Load Date Infile的方式可以取得比較好的性能。 細節三：插入延遲，提升插入操做對系統的不利影響 在某些狀況下，用戶對插入數據的即時性要求可能並非很高，此時就能夠考慮經過插入延遲特性，來減小插入操做對系統資源的耗用。筆者再以ERP系統爲例。在ERP系統中，有一個安全庫存管理的做業。若是啓用了這個做業的話，那麼系統會在天天一個固定的時刻，分析帳上的庫存數據與安全庫存之間的關係。若是發現庫存數量低於安全庫存時，系統就會自動生成一張請購單，將差別的數據插入到後臺的數據庫表中。顯然，對於這些記錄，用戶並不須要等待數據的插入完成。如在前臺調用這個做業時，能夠將這個做業放在後端執行。等到執行完畢後，再通知用戶便可。 在遇到這種狀況時，就可使用插入延遲的功能。當數據庫工程師啓用插入延遲功能時，服務器會馬上返回，而不會在那邊等着數據插入完成。若是表中沒有被其餘線程所調用的狀況，那麼記錄會排隊等待被插入。使用這個插入延遲特性，還可以帶來另一個好處。當多個做業同時往同一個表中插入數據的時候，這些進程會被記錄在同一個Block中。這就好像將多個單獨的Insert語句合併成一條Insert語句來執行，其速度會快許多。 細節四：在插入大量數據以前，能夠先將表鎖定 在數據庫庫中，某些表只有特定的用戶才使用。並且每次使用時可能會同時插入多條數據。如在ERP系統中導入期初數據時，就會碰到這種狀況。從前臺客戶端導入產品數據時，一般狀況下中間會有一個過渡表。用戶能夠經過這個過渡表來覈對數據是否準確。若是沒有問題的話，再將其導入到產品信息表中。採起這種方式，用戶還能夠查詢到有哪些記錄沒有被導入到系統中。 因爲期初數據的導入，通常是有系統管理員來完成的。此時這張過渡表只有一個用戶使用。爲了提升數據插入的效率，數據庫工程師能夠考慮，在插入以前先將表鎖定。這能夠提升數據插入的性能。這主要是由於索引緩衝區只是在全部的插入語句完成以後纔對磁盤進行一次性刷新。也就是說，有多個插入語句就會有多少次索引緩衝區刷新。爲此在數據插入以前，將數據表進行鎖定，就能夠大幅度的提升數據插入的效率。 細節五：插入數據以前先對數據進行排序 衆所周知，在對記錄進行排序時，須要耗費必定的系統資源。若是在插入數據時，就可以考慮到排序問題，那麼在後續記錄查詢時，就能夠避免從新排序所形成的二次開銷。故筆者建議，在可行的狀況下，在對數據插入以前先對記錄根據必定的規則進行排序。 如在採購訂單行表中，同時插入多條語句(從物料申購單轉換爲採購訂單)。在採購訂單行中插入多條記錄以前，能夠根據用戶的使用習慣，如按照產品的編號進行排序。用戶在後續查詢時，就能夠直接使用查詢的結果(默認狀況下是根據記錄插入的前後順序來顯示結果的)。顯示時就不須要再進行一次排序做業，從而能夠提升數據後續查詢的效率。 上面筆者提到的這個些內容，其實都是插入做業中的一些細節問題。可是這些細節內容，對於提升數據庫的插入效率有很大的幫助。有時候單純的經過提升硬件配置來改善數據庫的性能，還不可以提到這麼明顯的效果。