數(shù)據(jù)庫(kù)優(yōu)化已經(jīng)成為web開發(fā)人員提高web應(yīng)用程序性能，從而改善用戶體驗(yàn)的關(guān)鍵。對(duì)一些人來說，這可能聽起來不太吸引人，但好處是值得的。如果正確地優(yōu)化數(shù)據(jù)庫(kù)，就可以提高性能、減少瓶頸并節(jié)省資源。

幸運(yùn)的是，有些優(yōu)化技術(shù)在sql查詢中不需要dba級(jí)別(數(shù)據(jù)庫(kù)管理員)的專業(yè)知識(shí)。

為了幫助你開始，我們將看看10個(gè)最佳實(shí)踐，為web開發(fā)者如何優(yōu)化你的數(shù)據(jù)庫(kù)。一旦您了解了這些，您就可以更深入地研究數(shù)據(jù)庫(kù)優(yōu)化。

一、在選擇查詢中使用EXPLAIN

使用EXPLAIN關(guān)鍵字是快速修復(fù)慢速查詢的好方法。它讓您了解如何執(zhí)行查詢，并幫助您找到查詢優(yōu)化的潛在位置。

只需在SELECT查詢前輸入EXPLAIN關(guān)鍵字。這不會(huì)觸發(fā)實(shí)際的查詢，相反，您將看到執(zhí)行計(jì)劃。

當(dāng)您仔細(xì)查看結(jié)果時(shí)，您將能夠確定潛在的瓶頸或任何其他問題，如缺少索引，并減少掃描行的數(shù)量。

有了EXPLAIN工具，您可以優(yōu)化查詢，如果有必要，甚至可以改進(jìn)表結(jié)構(gòu)。

二、向搜索列添加索引

如果您按特定列搜索表，則很可能通過索引該列來提高查詢性能。

通過向搜索列添加索引，可以減少響應(yīng)時(shí)間并優(yōu)化資源使用。盡管索引可能對(duì)所有查詢都沒有幫助，但在大多數(shù)情況下它絕對(duì)有用。

但是要記住，有索引的表比沒有索引的表需要更多的時(shí)間來更新。這是因?yàn)樗饕残枰?。因此，明智的做法是只為頻繁搜索的列創(chuàng)建索引，而不是為更新比讀取次數(shù)更多的表創(chuàng)建索引。

三、盡可能的使用標(biāo)識(shí)字段

在表中使用Identity字段作為主鍵有幾個(gè)好處。

首先，它更快—您可以在查詢中簡(jiǎn)單地使用整數(shù)，而不是更長(zhǎng)的字符串字段。這將為您節(jié)省一些內(nèi)存，因?yàn)檎麛?shù)可以更短。

其次，它更安全——使用應(yīng)用程序數(shù)據(jù)字段可能會(huì)導(dǎo)致許多問題。例如，如果您使用名稱或地址作為PRIMARY KEY，那么如果客戶端或用戶更改了他們的名稱、移動(dòng)了，甚至只是出現(xiàn)了拼寫錯(cuò)誤，那么您可能會(huì)遇到問題。

因此，為了加快查詢速度并提高工作效率，可以在每個(gè)表中添加一個(gè)Identity列，這樣就可以使用它作為一個(gè)PRIMARY KEY，并使用AUTO_INCREMENT和一個(gè)合適類型的INT變量。

四、默認(rèn)情況下避免使用 NULL 值

盡量使用NOT NULL代替NULL。

一般來說，選擇NOT NULL意味著更快的查詢，這得益于更有效地使用索引，并避免了專門測(cè)試每個(gè)值是否為NULL的需要。最重要的是，你使用更少的存儲(chǔ)空間，根據(jù)MySQL文檔，NULL列需要額外的空間。

節(jié)省空間是特別重要的，如果你使用一個(gè)網(wǎng)絡(luò)主機(jī)為你的數(shù)據(jù)庫(kù)，因?yàn)榧词挂恍┳詈玫木W(wǎng)絡(luò)主機(jī)服務(wù)也不能提供無限的存儲(chǔ)空間?，F(xiàn)在，每個(gè)專欄節(jié)省一個(gè)比特看起來并不是很多，但是如果你在經(jīng)營(yíng)一個(gè)電子商務(wù)商店，有成千上萬的產(chǎn)品，你會(huì)節(jié)省很多資源。

使用NOT NULL，您將能夠像處理任何變量一樣處理字段，同時(shí)避免使用NULL可能產(chǎn)生的某些邊緣情況。

當(dāng)然，仍然有一些情況下使用NULL更有益—但在大多數(shù)情況下，您可以使用NOT NULL實(shí)現(xiàn)相同的結(jié)果。

五、對(duì)查詢使用無緩沖模式

為了節(jié)省時(shí)間和內(nèi)存，可以使用無緩沖查詢。

默認(rèn)情況下，SQL查詢使用緩沖模式。這會(huì)增加您的等待時(shí)間并消耗資源，因?yàn)樵诓樵兺瓿芍安粫?huì)返回結(jié)果—而是存儲(chǔ)在內(nèi)存中。如果是較大的查詢，并且您有一個(gè)巨大的數(shù)據(jù)庫(kù)，那么使用緩沖查詢將需要大量?jī)?nèi)存。

相比之下，當(dāng)使用無緩沖查詢時(shí)，在執(zhí)行查詢之前不會(huì)自動(dòng)存儲(chǔ)結(jié)果。一旦檢索到第一行，您就可以開始處理它們。

但是請(qǐng)記住，在處理結(jié)果集時(shí)，未緩沖查詢不允許在同一個(gè)連接上發(fā)出進(jìn)一步的查詢。

六、使列緊湊

優(yōu)化磁盤空間對(duì)于保持?jǐn)?shù)據(jù)庫(kù)引擎良好運(yùn)行至關(guān)重要。確保不會(huì)影響性能的一種非常簡(jiǎn)單的方法是使用小而緊湊的列。

因此，您應(yīng)該始終選擇對(duì)您的應(yīng)用程序最有用的整數(shù)類型。例如，如果您知道您的表不會(huì)有大量的行，那么不要自動(dòng)使用INT作為主鍵—您實(shí)際上可能會(huì)從使用SMALLINT甚至TINYINT中受益。

對(duì)于DATE和DATETIME也是如此——如果您不特別需要時(shí)間部分，只需使用DATE即可。DATETIME數(shù)據(jù)類型占用8個(gè)字節(jié)，而DATE只占用3個(gè)字節(jié)—因此這樣可以節(jié)省5個(gè)字節(jié)。

七、保持表的靜態(tài)(固定長(zhǎng)度)

另一種優(yōu)化數(shù)據(jù)庫(kù)性能的方法是使用靜態(tài)表。

這意味著您的表不應(yīng)該包括任何長(zhǎng)度可變的列，如TEXT或BLOB。您可以使用CHAR、VARCHAR、BINARY和VARBINARY列，但是需要填充它們以匹配指定的列寬度。

使用固定長(zhǎng)度的表是有益的，因?yàn)樗鼈兏?，更容易緩存。除此之外，靜態(tài)表更安全——它們?cè)诒罎⒑蟾菀字亟ā?/p>

但是，靜態(tài)表在某些情況下可能比動(dòng)態(tài)格式的表需要更多的磁盤空間—特別是當(dāng)您使用CHAR和VARCHAR列時(shí)。但是性能上的改進(jìn)可能會(huì)超過對(duì)磁盤空間的任何關(guān)注。

八、安裝對(duì)象關(guān)系映射器(ORM)

使用ORM是優(yōu)化數(shù)據(jù)庫(kù)工作方式的另一種好方法。

首先，ORM有助于消除人為錯(cuò)誤因素，因?yàn)樗鼮槟隽撕芏嗍虑椤Ｄ槐刈约壕帉懩敲炊啻a，而且ORM會(huì)為您處理許多重復(fù)的任務(wù)，從而減少您的工作量。

ORM還可以提高系統(tǒng)的安全性，因?yàn)闇?zhǔn)備和清理查詢使SQL注入變得更加困難。

最重要的是，ORM將在內(nèi)存中緩存實(shí)體，這減少了數(shù)據(jù)庫(kù)和CPU的負(fù)載。

當(dāng)然，ORM有其優(yōu)點(diǎn)和缺點(diǎn)，可能并不完全適合您的使用。但是有一些簡(jiǎn)單的方法可以避免濫用對(duì)象-關(guān)系映射。此外，還有一些其他的性能調(diào)優(yōu)和優(yōu)化插件可能更適合您。

九、批量執(zhí)行DELETE和UPDATE查詢

刪除和更新數(shù)據(jù)——尤其是在非常大的表中——可能會(huì)很復(fù)雜。這可能會(huì)花費(fèi)很多時(shí)間，而且這兩個(gè)命令都作為單個(gè)事務(wù)執(zhí)行。這意味著，如果出現(xiàn)任何中斷，整個(gè)事務(wù)都必須回滾，這可能會(huì)更加耗時(shí)。

但是，如果您遵循了批量運(yùn)行DELETE和UPDATE查詢的良好實(shí)踐，您將能夠通過增加并發(fā)性和減少瓶頸來節(jié)省時(shí)間。

如果一次刪除和更新的行數(shù)較少，則可以在將批提交到磁盤時(shí)執(zhí)行其他查詢。而且您可能需要做的任何回滾都將花費(fèi)更少的時(shí)間。

十、使用程序分析()獲得更多提示

優(yōu)化數(shù)據(jù)庫(kù)的最后一個(gè)最佳實(shí)踐是使用內(nèi)置功能:PROCEDURE ANALYSE()。更具體地說，如果將該命令添加到某個(gè)SQL語句中，它將查看列，然后推薦最佳數(shù)據(jù)類型和數(shù)據(jù)長(zhǎng)度。

在將新數(shù)據(jù)導(dǎo)入到表中之后——或者甚至檢查現(xiàn)有表中是否存在不一致的地方——這可能特別有用。

如果您實(shí)現(xiàn)了這些建議，您可能會(huì)節(jié)省一些空間。然而，請(qǐng)記住，這些只是建議，你必須真正考慮它們是否適合你的特定目的。

結(jié)論

數(shù)據(jù)庫(kù)優(yōu)化有點(diǎn)棘手，但完全避免它會(huì)對(duì)您的web應(yīng)用程序產(chǎn)生巨大的影響，導(dǎo)致性能問題。

如果你遵循了這10個(gè)web開發(fā)者數(shù)據(jù)庫(kù)優(yōu)化的最佳實(shí)踐，你將開始改善用戶體驗(yàn)和資源管理。

一旦實(shí)現(xiàn)了這些方法，請(qǐng)進(jìn)一步了解如何提高數(shù)據(jù)庫(kù)性能的技巧。

原文鏈接：https://dzone.com/articles/10-database-optimization-best-practices-for-web-de?