[[384082]]

【51CTO.com快譯】數據庫優化，對于開發人員提高Web應用程序的性能，進而改善用戶體驗是至關重要的。如果方法得當，目標數據庫的性能不但會得到提升，而且能夠減少業務能力瓶頸，以及節省系統資源的占用。

當然，除了查找和運用那些專業的DBA(數據庫管理員)級優化技術，Web開發人員完全可以試著采用如下十種有關SQL查詢的數據庫優化實踐。

1.對SELECT查詢使用EXPLAIN

通過深入了解應用程序如何執行查詢，進而找到潛在的可優化位置，可以說，使用EXPLAIN關鍵字是快速修復那些緩慢查詢的好方法。

我們只需要在SELECT查詢的前面鍵入EXPLAIN關鍵字，便可在并不觸發實際查詢的基礎上，深入了解具體的執行計劃。通過仔細分析執行結果，我們便可識別出那些潛在的瓶頸，以及相關的程序設計問題。例如：缺少索引，或是需要減少掃描的行數，甚至有必要改善數據表的結構。

2.將索引添加到搜索列中

如果您有需要按照特定列的方式去搜索數據表，那么就可以通過將索引添加到該列中，以減少響應時間，提高查詢的性能，并優化資源的使用。盡管我們所創建的索引，不一定能保證對所有的查詢都行之有效，但是在大多數情況下，還是能夠起到立竿見影的效果。

當然，凡事都有兩面性，索引表的建立要比未建立索引表更花費時間。畢竟索引也需要及時得到更新，以體現數據表的正確狀態。因此，一種較為明智的做法是：只為經常要用到搜索服務的列創建索引，而不必為那些需要頻繁更新的表建立索引。

3.盡量使用ID字段

總的說來，在數據表中使用“ID(Identity)”字段作為主鍵，有著如下優點：

首先是更快捷 -- 您可以在查詢中使用簡單的整型(integer)，而非長的字符串型(longer string)。顯然，由于整型短得多，因此執行起來更節省內存。

其次是更安全 -- 直接使用應用里的關鍵數據字段，往往存在著許多問題。例如：如果您使用名稱或地址作為主鍵，那么用戶一旦更改了其名稱、地址、甚至輸入了錯別字等情況，都可能導致原有對應關系的錯亂。

因此，為了加快查詢的速度并提高工作效率，請向每個數據表中添加一個Identity列，以便開發團隊將其用作帶有AUTO_INCREMENT(請參見--https://www.w3schools.com/sql/sql_autoincrement.asp)的主鍵，以及合適的INT變量類型。

4.在默認情況下避免出現NULL(空)值

根據MySQL文檔，NULL列需要占有額外的空間(請參見--https://dev.mysql.com/doc/refman/8.0/en/data-size.html)。因此為了減少數據庫對于可用存儲空間的占用，開發者通常會趨向于選用NOT NULL，讓索引得到更加有效地使用，并通過省去判斷、或測試每個值是否為NULL，以提高查詢的速度。

如果您在數據庫中使用到了虛擬主機，那么節省空間就顯得更加重要了。畢竟，即使是最好的虛擬機服務，也無法提供無限的存儲空間。雖然當前存儲設備和資源的獲取成本已大幅降低，但是如果您正在經營、或是目標成為一家擁有成千上萬種產品的電商網站，那么節省存儲資源是很有必要的。因此，通過使用NOT NULL，您將能夠像處理任何變量那樣，去使用各個字段，進而避免由NULL引發的各種問題。

5.對查詢使用無緩沖模式

您可以使用“無緩沖查詢(unbuffered queries)”(請參見--https://dev.mysql.com/doc/apis-php/en/apis-php-mysqlinfo.concepts.buffering.html)，來節省查詢需要的時間和內存。

默認情況下，SQL查詢使用的是緩沖模式。由于在查詢完成之前，程序不會返回任何結果，而且會將過程結果存儲到內存中，因此這會在無形中增加了程序的等待時間，并浪費了內存資源。顯然，如果查詢的數量較多，并且數據庫本身較為龐大的話，那么軟件應用就需要大量的內存，來進行緩沖查詢。

相反，對于無緩沖查詢來說，在執行查詢之前，其結果并不會自動存儲。當檢索到第一行后，您就可以開始使用它們了。

值得注意的是：在處理結果集時，無緩沖查詢并不允許在同一連接上，發出過多的更多查詢要求。

6.讓各個列更加緊湊

優化磁盤空間，對于保持數據庫引擎的正常運行，是至關重要的。而確保不產生性能障礙的一種簡單方法便是：使用小而緊湊的列。

為此，您應該始終選擇對應用程序最實用的整數類型。例如：如果您知道目標數據表將不會產生大量的行，那么請不要讓數據系統自動使用INT作為主鍵(請參見--https://www.w3schools.com/sql/sql_datatypes.asp)。您完全可以受益于使用SMALLINT，甚至TINYINT。

其實，DATE和DATETIME也是如此。如果您不需要用到時間部分，那么只需使用DATE即可。由于DATETIME在數據類型上占有8個字節，而DATE僅占有3個字節，因此您可以直接節省5個字節。

7.保持表格處于靜態(固定長度)

優化數據庫性能的另一種好方法是：使用靜態表。也就是說，數據表不應包含諸如TEXT或BLOB等可變長度的列。您可以使用CHAR、VARCHAR、BINARY和VARBINARY類型的列，但是需要對其進行填充，以匹配指定的列寬。固定長度的表不但運行得更加快速，而且更容易緩存。同時，靜態表更為安全，也更易于在崩潰后被重建。

當然在某些情況下，特別是在使用CHAR和VARCHAR列時，靜態表也可能比會動態格式表，需要更多的磁盤空間。這就需要您在性能提升和磁盤空間上，進行權衡比較了。

8.安裝對象關系映射器(Object-Relational Mapper，ORM)

由于ORM可以為您處理大量重復性任務，大幅減少代碼的編寫量，因此ORM可協助消除各種人為的錯誤因素。與此同時，由于ORM能夠及時對查詢進行清理，讓SQL注入變得更加困難，因此系統的安全性也能夠得以提升。此外，ORM還會將各種實體緩存在內存中，以減少數據庫和CPU的負載。

當然，ORM并非十全十美，為了避免濫用ORM(請參見--https://dzone.com/articles/object-relational-mapping-pitfalls)，您也可以使用一些性能調整，以及優化器類型的插件。

9.批量運行DELETE和UPDATE 

在大型數據表中，刪除和更新數據通常是作為同一事務被執行的，因此這兩種操作往往既復雜又耗時。而一旦發生了任何中斷的情況，我們在回滾整個事務時，會更加耗時耗力。如果您能夠采取批量運行DELETE和UPDATE(請參見--https://www.mssqltips.com/sqlservertip/5636/optimize-large-sql-server-insert-update-and-delete-processes-by-using-batches/)的做法，則可以通過增加并發性和減少瓶頸，來節省大量的時間。例如：您可以一次性刪除和更新較少的行數，進而在將批處理提交到磁盤的同時，執行其他類型的查詢。這些都可以讓您減少執行回滾所需要的時間。

10.使用PROCEDURE ANALYSE()，來獲得更多的提示

優化數據庫的最后一項實踐是：使用數據庫的內置功能--PROCEDURE ANALYSE()。通過將其添加到SQL語句中，我們可以全面查看數據列，發現那些最佳的數據類型和長度。而在將新的數據導入對應的數據表后，我們則可以及時檢查現有的表中，是否存在著任何不一致的情況。

小結

總的說來，數據庫優化是一個漫長的調試過程，需要Web應用開發和測試人員的協同努力。希望上面提到的十項優化建議，能夠為您手頭的軟件產品，帶來改善用戶體驗和資源管理的啟發和思路。

原文標題：10 Database Optimization Best Practices for Web Developers，作者: Kaarle Varkki

【51CTO譯稿，合作站點轉載請注明原文譯者和出處為51CTO.com】