為Web開(kāi)發(fā)人員準(zhǔn)備的七項(xiàng)數(shù)據(jù)庫(kù)優(yōu)化技巧
譯文【51CTO.com快譯】通常，諸如MySQL等時(shí)下流行的數(shù)據(jù)庫(kù)管理系統(tǒng)(DBMS)，都是由Web托管方(如云服務(wù)平臺(tái))提供的。這些數(shù)據(jù)庫(kù)往往被設(shè)置成為默認(rèn)、或通用的運(yùn)行模式，且不一定適合用戶系統(tǒng)的真實(shí)運(yùn)行環(huán)境。為此，我們有必要對(duì)其進(jìn)行適當(dāng)?shù)膬?yōu)化。
不過(guò)，一提到數(shù)據(jù)庫(kù)優(yōu)化，您也許會(huì)馬上想到更高的查詢效率、更高的整體性能等方面。其實(shí)，優(yōu)化的好處遠(yuǎn)不至于此。在具體實(shí)現(xiàn)方法上，數(shù)據(jù)庫(kù)管理人員往往也需要與Web開(kāi)發(fā)團(tuán)隊(duì)通力合作，根據(jù)目標(biāo)系統(tǒng)的實(shí)際情況，更改相應(yīng)的配置策略和規(guī)則。本文將為Web開(kāi)發(fā)人員列出七項(xiàng)數(shù)據(jù)庫(kù)優(yōu)化的常見(jiàn)技巧，以方便參考與實(shí)踐。
1.刪除未使用的表
通常，當(dāng)您在應(yīng)用中刪除或停用了某個(gè)插件后，與之對(duì)應(yīng)的數(shù)據(jù)庫(kù)表并未隨之自動(dòng)消除。而且，它們會(huì)保留全量的用戶信息、默認(rèn)選項(xiàng)、以及其他數(shù)據(jù)。這些被遺留下來(lái)的數(shù)據(jù)集，不但是系統(tǒng)受到各種攻擊的安全隱患，而且很可能會(huì)拖慢服務(wù)器與系統(tǒng)的整體性能。
如果您使用的是WordPress，那么可以通過(guò)安裝一個(gè)名為“插件垃圾收集器”(Plugins Garbage Collector)的插件，來(lái)掃描并發(fā)現(xiàn)目標(biāo)數(shù)據(jù)庫(kù)中任何未在使用的數(shù)據(jù)表，以供您選擇并刪除它們。
當(dāng)然，如果您更喜歡命令行操作的話，則可以使用如下圖所示的UPDATE_TIME字符串，直接查找那些非活躍的數(shù)據(jù)表。
StackOverflow中的UPDATE_TIME字符串示例。來(lái)源：StackOverflow。
不過(guò)，某些插件在訪問(wèn)數(shù)據(jù)集后，可能無(wú)法更新目標(biāo)數(shù)據(jù)表，因此您需要在刪除數(shù)據(jù)庫(kù)表之前，再三確認(rèn)它們是否的確不再被使用、或沒(méi)有被某處所調(diào)用到。而且作為一項(xiàng)預(yù)防性的辦法，在做任何修改之前，您最好事先手動(dòng)創(chuàng)建目標(biāo)數(shù)據(jù)庫(kù)的一個(gè)備份。當(dāng)然，托管類(lèi)型的云端服務(wù)通常都會(huì)提供針對(duì)服務(wù)器的自動(dòng)化備份，您只需事先了解如何從中進(jìn)行恢復(fù)便可。
2. 創(chuàng)建一個(gè)執(zhí)行計(jì)劃
執(zhí)行計(jì)劃(execution plan)的主要功能是：展示出在創(chuàng)建和執(zhí)行某個(gè)查詢時(shí)，所涉及到的各種檢索數(shù)據(jù)的方法，其中包含：它查詢了哪些表，先查詢的是哪張表，后查詢的又是哪張表，是否使用了索引，以及查詢是否高效等信息。因此，典型的執(zhí)行計(jì)劃包括以下方面：
- 操作的類(lèi)型
- 操作的排序
- 可使用的索引
- 通過(guò)統(tǒng)計(jì)來(lái)估算行數(shù)
- 通過(guò)結(jié)果來(lái)估算行數(shù)
下圖是一個(gè)ApexSQL執(zhí)行計(jì)劃的圖形化示例：
ApexSQL的執(zhí)行計(jì)劃示例。來(lái)源：ApexSQL。
可見(jiàn)，只有當(dāng)您獲得一個(gè)適當(dāng)?shù)膱?zhí)行計(jì)劃，才能構(gòu)建出實(shí)用的索引，進(jìn)一步優(yōu)化目標(biāo)數(shù)據(jù)庫(kù)，同時(shí)也為后續(xù)的優(yōu)化打下基礎(chǔ)。
3. 適當(dāng)?shù)乃饕?/strong>
從概念上說(shuō)，索引能夠允許您更快地訪問(wèn)數(shù)據(jù)庫(kù)，并加速查詢。相反，如果您未能合理地使用索引，那么查詢的處理過(guò)程就會(huì)變得緩慢。當(dāng)然，過(guò)分地索引(over-indexing)數(shù)據(jù)庫(kù)，是不會(huì)給系統(tǒng)帶來(lái)任何好處的。
目前，Web開(kāi)發(fā)人員經(jīng)常使用兩種類(lèi)型的數(shù)據(jù)庫(kù)索引：聚合(clustered index)和非聚合索引(non-clustered index)。
來(lái)源：DataSchool。
聚合方式使用主鍵來(lái)組織表中的數(shù)據(jù)。也就是說(shuō)，在主鍵被定義后，索引將會(huì)被自動(dòng)地創(chuàng)建出來(lái)。
定義主鍵。來(lái)源：DataSchool。
非聚合索引的主要目的是：通過(guò)創(chuàng)建能夠更易于搜索的列，進(jìn)而加快查詢的效率。
創(chuàng)建索引。來(lái)源：DataSchool。
4. 避免通過(guò)索引訪問(wèn)臨時(shí)表
根據(jù)MySQL的官方文檔，創(chuàng)建臨時(shí)表的一個(gè)條件是：對(duì)語(yǔ)句中包含的ORDER BY子句和不同的GROUP BY子句進(jìn)行評(píng)估。然而，您可以通過(guò)使用“索引訪問(wèn)(index access)”，避免使用ORDER BY子句來(lái)創(chuàng)建臨時(shí)表。使用這種索引的一個(gè)先決條件是：所有GROUP BY列都必須從相同的索引處引用不同的屬性。而且，該索引必須按照順序存儲(chǔ)它們的鍵。
目前，我們可以在MySQL中使用兩種類(lèi)型的索引訪問(wèn)：松索引掃描(Loose Index Scan )和緊索引掃描(Tight Index Scan)。其中，松索引掃描只考慮索引鍵的一小部分，而并不能滿足查詢中的每一個(gè)WHERE條件。如果WHERE子句中包含了范圍謂詞，那么松索引掃描會(huì)首先在每一組中，查找滿足范圍條件的第一個(gè)鍵，然后再去讀取最小數(shù)量的鍵。
當(dāng)然，一些為數(shù)據(jù)表預(yù)定的條件可以直接使用松索引掃描。當(dāng)松索引掃描適合某個(gè)查詢時(shí)，EXPLAIN的輸出會(huì)展示那些在額外列(Extra column)中，為group-by使用的索引。
下面的查詢示例就是使用了松索引掃描訪問(wèn)：
查詢列表示例：在表t1 (c1，c2，c3，c4)上的idx (c1，c2，c3)。來(lái)源：MySQL。
如果目標(biāo)數(shù)據(jù)表的條件不支持使用松索引掃描，您可以選用緊索引掃描方式。當(dāng)然，根據(jù)實(shí)際查詢的需求，您也可以在此基礎(chǔ)上，選用完整的、或一定范圍的緊索引掃描。
此類(lèi)索引訪問(wèn)的基礎(chǔ)是：當(dāng)一定范圍條件的所有鍵被發(fā)現(xiàn)后，數(shù)據(jù)庫(kù)將不會(huì)針對(duì)GROUP BY子句，生成一個(gè)臨時(shí)的數(shù)據(jù)表，來(lái)滿足該查詢。
如下查詢示例雖然不適合使用松索引掃描，但是我們可以采用緊索引掃描的方式：
查詢列表示例：在表t1 (c1，c2，c3，c4)上的idx (c1，c2，c3)。來(lái)源：MySQL。
5. 避免編碼循環(huán)
一個(gè)SQL查詢?nèi)绻枰贿\(yùn)行多次，那么該系統(tǒng)不但低效，而且可能會(huì)導(dǎo)致不必要的性能問(wèn)題。而對(duì)于大型數(shù)據(jù)集而言，此類(lèi)問(wèn)題會(huì)迅速積累，讓系統(tǒng)最終不堪重負(fù)。目前，業(yè)界有多種不錯(cuò)的解決方案。從本質(zhì)上說(shuō)，這些方法都會(huì)要將查詢移出循環(huán)，以確保只執(zhí)行一次。
如下示例展示了，如何使用JOIN和GROUP BY從多個(gè)表中選擇數(shù)據(jù)，并使數(shù)據(jù)庫(kù)通過(guò)單個(gè)查詢來(lái)執(zhí)行計(jì)數(shù)。此方法對(duì)于多個(gè)查詢(包括COUNT和MAX子句)來(lái)說(shuō)，特別有效。
使用Join和Group By。來(lái)源：CodeUtopia。
當(dāng)然，您還可以采用子選擇，即：在SELECT子句中嵌套使用SELECT子句。由于此類(lèi)查詢的執(zhí)行過(guò)程需要較少的資源，因此它對(duì)于合并查詢非常實(shí)用。
子選擇的示例。來(lái)源：CodeUtopia。
6. 擺脫相關(guān)子查詢
從本質(zhì)上說(shuō)，相關(guān)子查詢(Correlated subqueries)就是一種編碼循環(huán)。也就是說(shuō)，子查詢通過(guò)逐行運(yùn)行，直至滿足父語(yǔ)句為止。當(dāng)輸出主要依賴于多部分的答案驗(yàn)證(multi-part answer validation)時(shí)，該處理方法十分有效。
關(guān)聯(lián)子查詢流程圖。來(lái)源：GeeksforGeeks。
您可以通過(guò)使用JOIN子句來(lái)避免相關(guān)子查詢，進(jìn)而提高查詢的運(yùn)行效率。實(shí)際上，該方法替換了WHERE，并消除了前端請(qǐng)求分別為每一行執(zhí)行子查詢的必要性。下圖展示了該方法的工作過(guò)程：
Example of JOIN子句示例。來(lái)源：Ubitsoft。
7. 避免*式查詢
每個(gè)查詢的最終目標(biāo)都是為了高效地檢索到相關(guān)數(shù)據(jù)。但是，在創(chuàng)建查詢時(shí)，如果采用的是SELECT *子句，則通常會(huì)導(dǎo)致檢索各種并不相關(guān)的大量數(shù)據(jù)。如果目標(biāo)數(shù)據(jù)集的體量較小，此類(lèi)影響并不明顯;而在處理大型數(shù)據(jù)集時(shí)，該影響則會(huì)非常巨大。因此，為了優(yōu)化查詢速度，并減少系統(tǒng)資源的消耗，我們應(yīng)盡量減少查詢的數(shù)據(jù)量。通常，您可以使用如下代碼段中的LIMIT子句，來(lái)限制查詢結(jié)果的輸出。當(dāng)然，如果確實(shí)需要檢索并查詢整個(gè)數(shù)據(jù)集，您仍然可以使用SELECT *的方式。
LIMIT子句的示例。來(lái)源：TechontheNet
小結(jié)
對(duì)Web開(kāi)發(fā)人員來(lái)說(shuō)，優(yōu)化數(shù)據(jù)庫(kù)并不簡(jiǎn)單，而且往往無(wú)法一蹴而就。不過(guò)，通過(guò)反復(fù)的試驗(yàn)與調(diào)試，相信您一定能夠通過(guò)上述給出的七項(xiàng)技巧，提高目標(biāo)數(shù)據(jù)庫(kù)的性能和查詢效率。當(dāng)然，值得注意的是：在采取任何調(diào)優(yōu)之前，請(qǐng)您做好數(shù)據(jù)庫(kù)的備份工作，以便按需恢復(fù)到先前的狀態(tài)。
原文標(biāo)題：7 Database Optimization Hacks for Web Developers，作者：Kristina Tuvikene
【51CTO譯稿，合作站點(diǎn)轉(zhuǎn)載請(qǐng)注明原文譯者和出處為51CTO.com】
