為Web開發人員準備的七項資料庫最佳化技巧

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

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

1. 刪除未使用的表

通常，當您在應用中刪除或停用了某個外掛後，與之對應的資料庫表並未隨之自動消除。而且，它們會保留全量的使用者資訊、預設選項、以及其他資料。這些被遺留下來的資料集，不但是系統受到各種攻擊的安全隱患，而且很可能會拖慢伺服器與系統的整體效能。

如果您使用的是WordPress，那麼可以透過安裝一個名為“外掛垃圾收集器”（Plugins Garbage Collector）的外掛，來掃描並發現目標資料庫中任何未在使用的資料表，以供您選擇並刪除它們。

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

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

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

2. 建立一個執行計劃

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

操作的型別

操作的排序

可使用的索引

透過統計來估算行數

透過結果來估算行數

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

ApexSQL的執行計劃示例。來源：ApexSQL。

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

3. 適當的索引

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

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

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

定義主鍵。來源：DataSchool。

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

建立索引。來源：DataSchool。

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子句）來說，特別有效。

使用Join和Group By。來源：CodeUtopia。

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

子選擇的示例。來源：CodeUtopia。

6. 擺脫相關子查詢

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

關聯子查詢流程圖。來源：GeeksforGeeks。

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

Example of JOIN子句示例。來源：Ubitsoft。

7. 避免*式查詢

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

LIMIT子句的示例。來源：TechontheNet

小結

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