[[384082]]

【51CTO.com快譯】數(shù)據(jù)庫優(yōu)化，對于開發(fā)人員提高Web應(yīng)用程序的性能，進而改善用戶體驗是至關(guān)重要的。如果方法得當(dāng)，目標(biāo)數(shù)據(jù)庫的性能不但會得到提升，而且能夠減少業(yè)務(wù)能力瓶頸，以及節(jié)省系統(tǒng)資源的占用。

當(dāng)然，除了查找和運用那些專業(yè)的DBA(數(shù)據(jù)庫管理員)級優(yōu)化技術(shù)，Web開發(fā)人員完全可以試著采用如下十種有關(guān)SQL查詢的數(shù)據(jù)庫優(yōu)化實踐。

1.對SELECT查詢使用EXPLAIN

通過深入了解應(yīng)用程序如何執(zhí)行查詢，進而找到潛在的可優(yōu)化位置，可以說，使用EXPLAIN關(guān)鍵字是快速修復(fù)那些緩慢查詢的好方法。

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

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

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

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

3.盡量使用ID字段

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

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

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

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

4.在默認(rèn)情況下避免出現(xiàn)NULL(空)值

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

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

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

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

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

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

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

6.讓各個列更加緊湊

優(yōu)化磁盤空間，對于保持?jǐn)?shù)據(jù)庫引擎的正常運行，是至關(guān)重要的。而確保不產(chǎn)生性能障礙的一種簡單方法便是：使用小而緊湊的列。

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

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

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

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

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

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

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

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

9.批量運行DELETE和UPDATE 

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

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

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

小結(jié)

總的說來，數(shù)據(jù)庫優(yōu)化是一個漫長的調(diào)試過程，需要Web應(yīng)用開發(fā)和測試人員的協(xié)同努力。希望上面提到的十項優(yōu)化建議，能夠為您手頭的軟件產(chǎn)品，帶來改善用戶體驗和資源管理的啟發(fā)和思路。

原文標(biāo)題：10 Database Optimization Best Practices for Web Developers，作者: Kaarle Varkki

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