五個細(xì)節(jié)入手 幫助提高數(shù)據(jù)插入效率
導(dǎo)讀：插入數(shù)據(jù)，是MySQL數(shù)據(jù)庫的基本任務(wù)，不過不要小看這個插入數(shù)據(jù)的動作，在數(shù)據(jù)庫性能優(yōu)化上，可以在這上面做很大的文章。如果利用MySQL作為一些即時信息化管理軟件的后臺數(shù)據(jù)庫，如ERP系統(tǒng)。由于其數(shù)據(jù)插入的作業(yè)非常頻繁，而且對性能的要求也比較高。此時數(shù)據(jù)庫管理員就需要采取措施來提高數(shù)據(jù)插入的效率。針對這塊內(nèi)容，筆者認(rèn)為數(shù)據(jù)庫工程師在應(yīng)用程序與數(shù)據(jù)庫設(shè)計時，需要注意如下幾個細(xì)節(jié)問題。
細(xì)節(jié)一：同時插入多行記錄時，宜采用一條Insert語句
在數(shù)據(jù)插入時，往往需要同時往一個表中插入多條數(shù)據(jù)。如以ERP系統(tǒng)的采購訂單為例。用戶在前臺客戶端錄入采購訂單時，有可能需要向同一個供應(yīng)商同時采購多個原材料。此時在數(shù)據(jù)庫中，就需要往一個表格中同時插入多條記錄。在插入數(shù)據(jù)時，可以通過兩個方式實現(xiàn)。一是采用多條Insert語句，每個Insert語句插入一條記錄。另外一種方式是只采用一條Insert語句，插入多條語句。采取這兩種方式，有什么差異呢?
從語言編寫的角度看，第一種方式比較清楚明了。但是從數(shù)據(jù)插入速度的角度來看，第二種方式性能比較好。特別是一次性插入一兩百條記錄時，兩者所需要的時間會相差一倍以上。如下圖所示，就是使用一條Insert語句插入多條記錄的舉例。
總之在數(shù)據(jù)插入時，需要注意使用帶有多個值列表的Insert語句一次插入多行記錄要比使用一個單行插入語句快很多。而且隨著行數(shù)的增加，這個差異會越來越大。在數(shù)據(jù)庫設(shè)計時，如果有設(shè)計到同時插入多條數(shù)據(jù)的紀(jì)錄，如通過客戶端導(dǎo)入基礎(chǔ)數(shù)據(jù)、通過前置單據(jù)自動生成相關(guān)單據(jù)，這些作業(yè)都會遇到向某個表中一次性插入多條記錄的情況。此時從提高數(shù)據(jù)庫性能的角度考慮，筆者建議采用一個Insert語句同時插入多條記錄的方式。雖然說編寫語言的時候會增加復(fù)雜程度，但是這點投資與數(shù)據(jù)庫性能優(yōu)化相比而言，是值得的。
細(xì)節(jié)二：批量插入記錄時，建立采用Load Date Infile語句
有時候可能需要往Mysql數(shù)據(jù)庫中批量導(dǎo)入數(shù)據(jù)。如在ERP系統(tǒng)初始化過程中，需要將產(chǎn)品數(shù)據(jù)導(dǎo)入到系統(tǒng)中。實施顧問一般會要求用戶根據(jù)他們的格式準(zhǔn)備好相關(guān)的數(shù)據(jù)，然后利用導(dǎo)入作業(yè)，將這些數(shù)據(jù)一次性導(dǎo)入到數(shù)據(jù)庫中。雖然這個導(dǎo)入作業(yè)是通過前臺客戶端來完成的，但是最終仍然是反映在數(shù)據(jù)庫中。
針對這種批量數(shù)據(jù)的導(dǎo)入，數(shù)據(jù)庫又提供了兩種方式。一是上面所介紹的，采用一個Insert語句插入多行記錄的方式;另外一種就是采用Load Date Infile的方式。這兩種方式有什么差異呢?Load Date Infile顧名思義，就是從一個文件中導(dǎo)入數(shù)據(jù)。為此如果采用這種方式的話，用戶需要預(yù)先準(zhǔn)備一個固定格式的文件。而Insert語句的話，沒有這個要求，其可以直接利用其他作業(yè)中獲得的數(shù)據(jù)。從這個角度講，如果插入的紀(jì)錄數(shù)比較少(如在一百條左右)或者通過系統(tǒng)交互來獲得數(shù)據(jù)，此時比較適合使用Insert語句。從性能的角度考慮，通常情況下使用Load Date Infile語句的方式插入數(shù)據(jù)要比使用Insert語句速度快10到20倍。為此如果插入的數(shù)據(jù)量比較多，如期初導(dǎo)入數(shù)據(jù)時可能會有超過上萬條的記錄，此時使用Load Date Infile的方式能夠取得比較好的性能。
細(xì)節(jié)三：插入延遲，提高插入操作對系統(tǒng)的不利影響
在某些情況下，用戶對插入數(shù)據(jù)的即時性要求可能并不是很高，此時就可以考慮通過插入延遲特性，來減少插入操作對系統(tǒng)資源的耗用。筆者再以ERP系統(tǒng)為例。在ERP系統(tǒng)中，有一個安全庫存管理的作業(yè)。如果啟用了這個作業(yè)的話，那么系統(tǒng)會在每天一個固定的時刻，分析賬上的庫存數(shù)據(jù)與安全庫存之間的關(guān)系。如果發(fā)現(xiàn)庫存數(shù)量低于安全庫存時，系統(tǒng)就會自動生成一張請購單，將差異的數(shù)據(jù)插入到后臺的數(shù)據(jù)庫表中。顯然，對于這些記錄，用戶并不需要等待數(shù)據(jù)的插入完成。如在前臺調(diào)用這個作業(yè)時，可以將這個作業(yè)放在后端執(zhí)行。等到執(zhí)行完畢后，再通知用戶即可。
在遇到這種情況時，就可以使用插入延遲的功能。當(dāng)數(shù)據(jù)庫工程師啟用插入延遲功能時，服務(wù)器會立刻返回，而不會在那邊等著數(shù)據(jù)插入完成。如果表中沒有被其他線程所調(diào)用的情況，那么記錄會排隊等待被插入。使用這個插入延遲特性，還能夠帶來另外一個好處。當(dāng)多個作業(yè)同時往同一個表中插入數(shù)據(jù)的時候，這些進(jìn)程會被記錄在同一個Block中。這就好像將多個單獨的Insert語句合并成一條Insert語句來執(zhí)行，其速度會快許多。
細(xì)節(jié)四：在插入大量數(shù)據(jù)之前，可以先將表鎖定
在數(shù)據(jù)庫庫中，某些表只有特定的用戶才使用。而且每次使用時可能會同時插入多條數(shù)據(jù)。如在ERP系統(tǒng)中導(dǎo)入期初數(shù)據(jù)時，就會碰到這種情況。從前臺客戶端導(dǎo)入產(chǎn)品數(shù)據(jù)時，通常情況下中間會有一個過渡表。用戶可以通過這個過渡表來核對數(shù)據(jù)是否準(zhǔn)確。如果沒有問題的話，再將其導(dǎo)入到產(chǎn)品信息表中。采取這種方式，用戶還可以查詢到有哪些記錄沒有被導(dǎo)入到系統(tǒng)中。
由于期初數(shù)據(jù)的導(dǎo)入，一般是有系統(tǒng)管理員來完成的。此時這張過渡表只有一個用戶使用。為了提高數(shù)據(jù)插入的效率，數(shù)據(jù)庫工程師可以考慮，在插入之前先將表鎖定。這可以提高數(shù)據(jù)插入的性能。這主要是因為索引緩沖區(qū)只是在所有的插入語句完成之后才對磁盤進(jìn)行一次性刷新。也就是說，有多個插入語句就會有多少次索引緩沖區(qū)刷新。為此在數(shù)據(jù)插入之前，將數(shù)據(jù)表進(jìn)行鎖定，就可以大幅度的提高數(shù)據(jù)插入的效率。
細(xì)節(jié)五：插入數(shù)據(jù)之前先對數(shù)據(jù)進(jìn)行排序
眾所周知，在對記錄進(jìn)行排序時，需要耗費(fèi)一定的系統(tǒng)資源。如果在插入數(shù)據(jù)時，就能夠考慮到排序問題，那么在后續(xù)記錄查詢時，就可以避免重新排序所造成的二次開銷。故筆者建議，在可行的情況下，在對數(shù)據(jù)插入之前先對記錄根據(jù)一定的規(guī)則進(jìn)行排序。
如在采購訂單行表中，同時插入多條語句(從物料申購單轉(zhuǎn)換為采購訂單)。在采購訂單行中插入多條記錄之前，可以根據(jù)用戶的使用習(xí)慣，如按照產(chǎn)品的編號進(jìn)行排序。用戶在后續(xù)查詢時，就可以直接使用查詢的結(jié)果(默認(rèn)情況下是根據(jù)記錄插入的先后順序來顯示結(jié)果的)。顯示時就不需要再進(jìn)行一次排序作業(yè)，從而可以提高數(shù)據(jù)后續(xù)查詢的效率。
上面筆者提到的這個些內(nèi)容，其實都是插入作業(yè)中的一些細(xì)節(jié)問題。但是這些細(xì)節(jié)內(nèi)容，對于提高數(shù)據(jù)庫的插入效率有很大的幫助。有時候單純的通過提高硬件配置來改善數(shù)據(jù)庫的性能，還不能夠提到這么明顯的效果。希望以上降到的內(nèi)容對大家能有所幫助。
【編輯推薦】
