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