【51CTO.com快譯】通常，諸如MySQL等時下流行的資料庫管理系統(DBMS)，都是由Web託管方(如雲服務平臺)提供的。這些資料庫往往被設定成為預設、或通用的執行模式，且不一定適合使用者系統的真實執行環境。為此，我們有必要對其進行適當的最佳化。

年輕時尚的商人

不過，一提到資料庫最佳化，您也許會馬上想到更高的查詢效率、更高的整體效能等方面。其實，最佳化的好處遠不至於此。在具體實現方法上，資料庫管理人員往往也需要與Web開發團隊通力合作，根據目標系統的實際情況，更改相應的配置策略和規則。本文將為Web開發人員列出七項資料庫最佳化的常見技巧，以方便參考與實踐。

當然，如果您更喜歡命令列操作的話，則可以使用如下圖所示的UPDATE_TIME字串，直接查詢那些非活躍的資料表。

StackOverflow中的UPDATE_TIME字串示例。來源：StackOverflow。

不過，某些外掛在訪問資料集後，可能無法更新目標資料表，因此您需要在刪除資料庫表之前，再三確認它們是否的確不再被使用、或沒有被某處所呼叫到。而且作為一項預防性的辦法，在做任何修改之前，您最好事先手動建立目標資料庫的一個備份。當然，託管型別的雲端服務通常都會提供針對伺服器的自動化備份，您只需事先了解如何從中進行恢復便可。

2. 建立一個執行計劃

執行計劃(execution plan)的主要功能是：展示出在建立和執行某個查詢時，所涉及到的各種檢索資料的方法，其中包含：它查詢了哪些表，先查詢的是哪張表，後查詢的又是哪張表，是否使用了索引，以及查詢是否高效等資訊。因此，典型的執行計劃包括以下方面：

操作的型別操作的排序可使用的索引透過統計來估算行數透過結果來估算行數

下圖是一個ApexSQL執行計劃的圖形化示例：

可見，只有當您獲得一個適當的執行計劃，才能構建出實用的索引，進一步最佳化目標資料庫，同時也為後續的最佳化打下基礎。

3. 適當的索引

從概念上說，索引能夠允許您更快地訪問資料庫，並加速查詢。相反，如果您未能合理地使用索引，那麼查詢的處理過程就會變得緩慢。當然，過分地索引(over-indexing)資料庫，是不會給系統帶來任何好處的。

目前，Web開發人員經常使用兩種型別的資料庫索引：聚合(clustered index)和非聚合索引(non-clustered index)。

聚合方式使用主鍵來組織表中的資料。也就是說，在主鍵被定義後，索引將會被自動地創建出來。

非聚合索引的主要目的是：透過建立能夠更易於搜尋的列，進而加快查詢的效率。

4. 避免透過索引訪問臨時表

根據MySQL的官方文件，建立臨時表的一個條件是：對語句中包含的ORDER BY子句和不同的GROUP BY子句進行評估。然而，您可以透過使用“索引訪問(index access)”，避免使用ORDER BY子句來建立臨時表。使用這種索引的一個先決條件是：所有GROUP BY列都必須從相同的索引處引用不同的屬性。而且，該索引必須按照順序儲存它們的鍵。

目前，我們可以在MySQL中使用兩種型別的索引訪問：松索引掃描(Loose Index Scan )和緊索引掃描(Tight Index Scan)。其中，松索引掃描只考慮索引鍵的一小部分，而並不能滿足查詢中的每一個WHERE條件。如果WHERE子句中包含了範圍謂詞，那麼松索引掃描會首先在每一組中，查詢滿足範圍條件的第一個鍵，然後再去讀取最小數量的鍵。

當然，一些為資料表預定的條件可以直接使用松索引掃描。當松索引掃描適合某個查詢時，EXPLAIN的輸出會展示那些在額外列(Extra column)中，為group-by使用的索引。

下面的查詢示例就是使用了松索引掃描訪問：

查詢列表示例：在表t1 (c1，c2，c3，c4)上的idx (c1，c2，c3)。來源：MySQL。

如果目標資料表的條件不支援使用松索引掃描，您可以選用緊索引掃描方式。當然，根據實際查詢的需求，您也可以在此基礎上，選用完整的、或一定範圍的緊索引掃描。

此類索引訪問的基礎是：當一定範圍條件的所有鍵被發現後，資料庫將不會針對GROUP BY子句，生成一個臨時的資料表，來滿足該查詢。

如下查詢示例雖然不適合使用松索引掃描，但是我們可以採用緊索引掃描的方式：

查詢列表示例：在表t1 (c1，c2，c3，c4)上的idx (c1，c2，c3)。來源：MySQL。

5. 避免編碼迴圈

一個SQL查詢如果需要被執行多次，那麼該系統不但低效，而且可能會導致不必要的效能問題。而對於大型資料集而言，此類問題會迅速積累，讓系統最終不堪重負。目前，業界有多種不錯的解決方案。從本質上說，這些方法都會要將查詢移出迴圈，以確保只執行一次。

如下示例展示了，如何使用JOIN和GROUP BY從多個表中選擇資料，並使資料庫透過單個查詢來執行計數。此方法對於多個查詢(包括COUNT和MAX子句)來說，特別有效。

當然，您還可以採用子選擇，即：在SELECT子句中巢狀使用SELECT子句。由於此類查詢的執行過程需要較少的資源，因此它對於合併查詢非常實用。

6. 擺脫相關子查詢

從本質上說，相關子查詢(Correlated subqueries)就是一種編碼迴圈。也就是說，子查詢透過逐行執行，直至滿足父語句為止。當輸出主要依賴於多部分的答案驗證(multi-part answer validation)時，該處理方法十分有效。

您可以透過使用JOIN子句來避免相關子查詢，進而提高查詢的執行效率。實際上，該方法替換了WHERE，並消除了前端請求分別為每一行執行子查詢的必要性。下圖展示了該方法的工作過程：

7. 避免*式查詢

每個查詢的最終目標都是為了高效地檢索到相關資料。但是，在建立查詢時，如果採用的是SELECT *子句，則通常會導致檢索各種並不相關的大量資料。如果目標資料集的體量較小，此類影響並不明顯;而在處理大型資料集時，該影響則會非常巨大。因此，為了最佳化查詢速度，並減少系統資源的消耗，我們應儘量減少查詢的資料量。通常，您可以使用如下程式碼段中的LIMIT子句，來限制查詢結果的輸出。當然，如果確實需要檢索並查詢整個資料集，您仍然可以使用SELECT *的方式。

小結

對Web開發人員來說，最佳化資料庫並不簡單，而且往往無法一蹴而就。不過，透過反覆的試驗與除錯，相信您一定能夠透過上述給出的七項技巧，提高目標資料庫的效能和查詢效率。當然，值得注意的是：在採取任何調優之前，請您做好資料庫的備份工作，以便按需恢復到先前的狀態。