關(guān)于數(shù)據(jù)庫(kù)編碼優(yōu)化的一些看法的討論
2024-07-21 02:37:50
供稿：網(wǎng)友
有關(guān)數(shù)據(jù)庫(kù)的優(yōu)化這個(gè)問(wèn)題，平常一般我們接觸的比較少，所以有時(shí)沒(méi)有引起足夠的重視，但是在某些時(shí)候，這將關(guān)系到整個(gè)軟件的成敗。
一般來(lái)說(shuō)，我們考慮要做數(shù)據(jù)庫(kù)的優(yōu)化的時(shí)候，是建立在設(shè)計(jì)已經(jīng)定型的基礎(chǔ)上的，往往設(shè)計(jì)此時(shí)已經(jīng)很難更改或者根本無(wú)法更改，所以對(duì)于太糟糕的設(shè)計(jì)，我們往往是無(wú)能為力的。比如需要提取的某個(gè)信息的時(shí)候，需要輾轉(zhuǎn)多個(gè)大表，且返回記錄數(shù)較多，那么這種情況在設(shè)計(jì)的時(shí)候我們希望能予以盡可能的關(guān)注。數(shù)據(jù)庫(kù)的設(shè)計(jì)應(yīng)該和實(shí)際業(yè)務(wù)（功能）結(jié)合起來(lái)選擇一個(gè)設(shè)計(jì)的平衡點(diǎn)，不能一味的追求范式級(jí)別是怎么的高，有時(shí)甚至需要通過(guò)一些數(shù)據(jù)冗余來(lái)提高查詢(xún)效率。那么在所有這些成為事實(shí)，我們已經(jīng)進(jìn)入編程階段的時(shí)期之后，對(duì)于我們具體的數(shù)據(jù)庫(kù)編程人員來(lái)講，要考慮的就是以下幾類(lèi)問(wèn)題：
一：網(wǎng)絡(luò)流量
二：磁盤(pán)I/O
三：資源的使用
四：代碼效率
網(wǎng)絡(luò)流量
通常我們?cè)趦煞N情況下要考慮這個(gè)問(wèn)題：
1． 繁發(fā)送的SQL請(qǐng)求
比如web數(shù)據(jù)庫(kù)的訪問(wèn)，這種情況下sql語(yǔ)句的傳送都有可能給網(wǎng)絡(luò)造成沉重的負(fù)擔(dān)，可以考慮采用存儲(chǔ)過(guò)程或者函數(shù)的方式來(lái)緩解問(wèn)題。一般來(lái)說(shuō)，這種情況下應(yīng)用服務(wù)器應(yīng)該提供pool和數(shù)據(jù)庫(kù)建立常連接供調(diào)用，因?yàn)閷?duì)數(shù)據(jù)庫(kù)的連接請(qǐng)求和數(shù)據(jù)庫(kù)的響應(yīng)是一個(gè)比較耗時(shí)的過(guò)程，這往往成為數(shù)據(jù)庫(kù)瓶頸。
2．一次性返回記錄過(guò)多
使用者未必需要看所有記錄，或者他一次可以看一部分記錄，這時(shí)就可以通過(guò)前臺(tái)采用翻頁(yè)等措施，來(lái)緩解網(wǎng)絡(luò)的壓力。而后臺(tái)則可以通過(guò)多種方式來(lái)實(shí)現(xiàn)，比如游標(biāo)，比如利用中間結(jié)果集一次性返回的方式等。
總之，對(duì)于網(wǎng)絡(luò)流量的考慮，也是一個(gè)根據(jù)具體需求和環(huán)境衡量的結(jié)果，假如沒(méi)有必要讓我們來(lái)考慮這個(gè)問(wèn)題，那么，你就大膽的做吧，在LAN中一次返回500k的數(shù)據(jù)大家都還可以接受。順便提一下，有時(shí)可能要網(wǎng)絡(luò)傳送的包的大小問(wèn)題，因?yàn)榘傆幸恍┕潭ù笮〉臉?biāo)記，所以包太小則對(duì)于業(yè)務(wù)無(wú)用的信息太多，包太大而又可能造成經(jīng)常發(fā)送失敗。
磁盤(pán)I/O一般來(lái)說(shuō)這個(gè)問(wèn)題跟數(shù)據(jù)庫(kù)治理員或者設(shè)計(jì)人員的關(guān)系更密切，但我們需要做的是，盡可能的利用索引，對(duì)于太大的表，利用表數(shù)據(jù)分區(qū)存儲(chǔ)的特點(diǎn)（當(dāng)然這需要你首先明白什么是表分區(qū)和它可以帶來(lái)什么好處）。利用索引能提高速度是因?yàn)閮蓚€(gè)原因，一是索引通常比數(shù)據(jù)占用的空間小，每次從磁盤(pán)讀入內(nèi)存的時(shí)候更快；二是索引的存儲(chǔ)結(jié)構(gòu)是一種平衡的樹(shù)的形式，它不是順序的查找的，根據(jù)樹(shù)的特點(diǎn)，我們可以估算出其查找次數(shù)大致應(yīng)該為n=logn(x), x為記錄數(shù)，若不利用索引則許查找x次（當(dāng)然這好象跟磁盤(pán)I/O已經(jīng)沒(méi)有關(guān)系了）。再提醒一點(diǎn)，若記錄返回?cái)?shù)大于20%，則數(shù)據(jù)庫(kù)將可能做全表掃描，此時(shí)索引反而降低了數(shù)據(jù)庫(kù)的效率。
資源的使用其實(shí)我們最關(guān)心的資源，除了網(wǎng)絡(luò)，就是內(nèi)存和cpu。要在這方面作出優(yōu)化，必須明白數(shù)據(jù)庫(kù)的體系結(jié)構(gòu)和數(shù)據(jù)庫(kù)對(duì)于sql到底是怎么解析執(zhí)行的。對(duì)于發(fā)送的sql語(yǔ)句，數(shù)據(jù)庫(kù)先解析若發(fā)現(xiàn)有完全和該語(yǔ)句相同的已經(jīng)解析好的執(zhí)行代碼存在于緩沖區(qū)中，則直接執(zhí)行緩沖區(qū)中代碼。
要利用這點(diǎn)，要求我們編寫(xiě)代碼具有良好的風(fēng)格和習(xí)慣，所謂完全相同的sql語(yǔ)句的意思是該語(yǔ)句所有字符甚至空格和換行都完全一樣。想想存儲(chǔ)過(guò)程的快主要就是因?yàn)樗墙馕龊玫拇a存儲(chǔ)在數(shù)據(jù)庫(kù)中而可以直接調(diào)用。關(guān)于cpu的利用的明顯的地方就是多處理器的情況下，應(yīng)該在查詢(xún)中指定并行查詢(xún)，利用多個(gè)cpu并行的能力。關(guān)于內(nèi)存的使用，這是數(shù)據(jù)庫(kù)治理員和我們編程人員應(yīng)該共同關(guān)心的問(wèn)題。因?yàn)閿?shù)據(jù)庫(kù)緩沖區(qū)是為了保存已經(jīng)執(zhí)行或者使用過(guò)的數(shù)據(jù)留下的信息以為下次執(zhí)行使用。這樣既降低了磁盤(pán)I/O，也減少了一些重復(fù)的執(zhí)行步驟。
首先要明白數(shù)據(jù)庫(kù)緩沖區(qū)采用的是先淘汰最近使用最少的數(shù)據(jù)的策略。當(dāng)然，其中還有優(yōu)先級(jí)別等問(wèn)題。對(duì)于通過(guò)索引讀入的數(shù)據(jù)和索引本身，在數(shù)據(jù)庫(kù)中保留的時(shí)間總是比較長(zhǎng)，若這樣的數(shù)據(jù)充斥了緩沖區(qū)，那帶來(lái)的結(jié)果是我們不愿意看到的。還有一點(diǎn)，若臨時(shí)表或者中間結(jié)果過(guò)大，也會(huì)有類(lèi)似的影響。這些在編程的時(shí)候可能會(huì)碰到。代碼效率大多數(shù)情況下，這才是編程人員關(guān)心的重點(diǎn)。
要使代碼效率高，我們當(dāng)然希望它占用的內(nèi)存小，耗用的時(shí)間短。但某些情況下，這2點(diǎn)可能成為一對(duì)矛盾。比如，我們要提取符合條件的從100000條到100050條的記錄，我們決定采用游標(biāo)的方式。那么一種可能，我們打開(kāi)一個(gè)游標(biāo)，然后順序移動(dòng)到第100000條記錄處，開(kāi)始提取數(shù)據(jù)，結(jié)束后關(guān)閉游標(biāo)。一種可能，我們采用中間結(jié)果集或者臨時(shí)表等方式，先找出符合條件的記錄中的100000條到100050條，然后在這個(gè)基礎(chǔ)上定義游標(biāo)，那么這樣我們的游標(biāo)就避免了100000次的移動(dòng)。但是代價(jià)是這個(gè)過(guò)程中利用了更多的內(nèi)存。不過(guò)，我們編程，本就沒(méi)有一個(gè)完美的解決方案，總是根據(jù)具體情況做出一個(gè)比較合適的選擇的。
其實(shí)，更多的，我們應(yīng)該關(guān)心SQL語(yǔ)句在數(shù)據(jù)庫(kù)中到底是怎么解析并執(zhí)行的。從這個(gè)層次
上明白了，寫(xiě)出的sql語(yǔ)句就至少不會(huì)太差了。當(dāng)然，一開(kāi)始，我們可以記住一些通用規(guī)則，比如查詢(xún)中盡可能的利用索引，能過(guò)濾掉最多數(shù)據(jù)的條件寫(xiě)在where子句的末尾，在使用子查詢(xún)的時(shí)候要慎重考慮，對(duì)于in要仔細(xì)衡量，not in要盡量避免使用。表連接的時(shí)候要盡量利用索引字段，多表連接的時(shí)候要注重連接順序。還有比如like的使用，不要輕易使用like%s%這樣的條件（like s%是可以利用索引的），這種匹配的算法再快也有影響。
多了解sql編碼中的一些技巧。
多積累經(jīng)驗(yàn)、多學(xué)習(xí)，在已經(jīng)知道的東西中根據(jù)需要靈活的創(chuàng)造出自己的用法，這時(shí)你心里就應(yīng)該清楚自己的代碼的執(zhí)行效率了，不至于N個(gè)月后運(yùn)行的時(shí)候忽然發(fā)現(xiàn)需要返工，那將是一件很痛苦的事情。
最后再?gòu)?qiáng)調(diào)一點(diǎn)，要做到對(duì)自己編寫(xiě)的代碼心中有數(shù)，很明白它有多大的承受能力，你
就必須理解數(shù)據(jù)庫(kù)的體系結(jié)構(gòu)，明白SQL代碼的執(zhí)行過(guò)程，知道這個(gè)執(zhí)行過(guò)程中大約要消耗多少內(nèi)存，有那些更具體的步驟，它會(huì)給數(shù)據(jù)庫(kù)帶來(lái)什么樣的影響，這個(gè)影響可以忽略么？只要咱們朝著這個(gè)方向努力，最終會(huì)玩轉(zhuǎn)這個(gè)內(nèi)容并不太多的東東的。(<b>biti_rainy</b>)
對(duì)于1的看法：
包、存儲(chǔ)過(guò)程函數(shù)等是針對(duì)計(jì)算密集型和后臺(tái)數(shù)據(jù)處理密集型的業(yè)務(wù)邏輯。而一點(diǎn)從 App Server 層傳的 sql 就能造成網(wǎng)絡(luò)沉重負(fù)擔(dān)？提交的表單數(shù)據(jù)都比 SQL 字節(jié)多！而對(duì)于連接，一般都用 connection pool，即使用 tomcat 也自己寫(xiě) pool 了，況且連接請(qǐng)求和SQL請(qǐng)求完全是兩碼事嘛。
對(duì)于2的看法：
分頁(yè)是理所當(dāng)然的，可是沒(méi)有必要用什么游標(biāo)吧？（我想你指的是服務(wù)端游標(biāo)吧，其實(shí)客戶(hù)端結(jié)果集本身就打開(kāi)了客戶(hù)端游標(biāo)），難道為一個(gè)分頁(yè)就寫(xiě)一個(gè)存儲(chǔ)過(guò)程？利用兩次 rownum 結(jié)合就搞定了。
其實(shí)網(wǎng)絡(luò)流量的問(wèn)題，只要程序中不出現(xiàn)返回幾千、萬(wàn)行記錄的“地雷”程序就沒(méi)問(wèn)題，除非你用10M或100M網(wǎng)卡，服務(wù)器都應(yīng)該用 1000M 以上的網(wǎng)絡(luò)設(shè)備了。
對(duì)于磁盤(pán) I/O
最有效的辦法是對(duì)數(shù)據(jù)庫(kù)分區(qū)：對(duì)表分區(qū)、對(duì)索引分區(qū)，以及結(jié)合二者，然后對(duì) SQL 進(jìn)行優(yōu)化，使之利用分區(qū)。SQL優(yōu)化比較煩人，需要花一些時(shí)間，而且還要用到 hint，Oracle 有一個(gè)文檔，什么 performance 什么里面講很清楚。如何寫(xiě) SQL 都很有學(xué)問(wèn)，讀清楚那本書(shū)就 ok 了假如分區(qū)和SQL優(yōu)化做得好，CPU利用率不會(huì)太高。相對(duì)而言數(shù)據(jù)庫(kù)服務(wù)器吃?xún)?nèi)存比較狠。應(yīng)用服務(wù)器用 CPU 比內(nèi)存重要些。(<b>singledream</b>)
