提升MySQL查詢效率及查詢速度優(yōu)化的方法是什么

今天小編給大家分享一下提升MySQL查詢效率及查詢速度優(yōu)化的方法是什么的相關(guān)知識(shí)點(diǎn)，內(nèi)容詳細(xì)，邏輯清晰，相信大部分人都還太了解這方面的知識(shí)，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來(lái)了解一下吧。

一、利用EXPLAIN關(guān)鍵字來(lái)評(píng)估查詢語(yǔ)句中的缺陷

如下圖所示，現(xiàn)在筆者在數(shù)據(jù)庫(kù)中執(zhí)行了一條簡(jiǎn)單的Select查詢語(yǔ)句，從一個(gè)表格中查詢所有信息。現(xiàn)在數(shù)據(jù)庫(kù)管理員想知道，數(shù)據(jù)庫(kù)在執(zhí)行這條語(yǔ)句時(shí)，做了哪些工作或者說(shuō)想知道，這條查詢語(yǔ)句有沒(méi)有進(jìn)一步優(yōu)化的可能。如果要了解這個(gè)信息的話，就可以在查詢語(yǔ)句中加入一個(gè)Explain關(guān)鍵字。

通過(guò)Select查詢語(yǔ)句可以從數(shù)據(jù)庫(kù)中查詢某個(gè)表中的數(shù)據(jù)。但是這條語(yǔ)句執(zhí)行的效率如何是否還有優(yōu)化的余地這些內(nèi)容是無(wú)法從上面這個(gè)簡(jiǎn)單的查詢語(yǔ)句中獲得的。為了了解更加詳細(xì)的信息，需要加入Explain關(guān)鍵字。如下圖所示：

加入Explain關(guān)鍵字之后，系統(tǒng)并沒(méi)有查詢出表格中的數(shù)據(jù)，而只是顯示了查詢過(guò)程中的一些信息。這些信息對(duì)于我們后續(xù)進(jìn)行數(shù)據(jù)庫(kù)查詢優(yōu)化非常有幫助。從上面這個(gè)信息中我們可以看出，用戶只是進(jìn)行來(lái)一個(gè)簡(jiǎn)單的查詢。在這個(gè)查詢中，沒(méi)有用到任何索引、關(guān)鍵字等內(nèi)容，也沒(méi)有用到Where條件語(yǔ)句。為此這個(gè)查詢語(yǔ)句并不是很合理。雖然其可以找到最后正確的結(jié)果，不過(guò)其查詢效率可能并不是很明顯。為此數(shù)據(jù)庫(kù)專(zhuān)家可以根據(jù)上面顯示的信息來(lái)進(jìn)行優(yōu)化。如果我們現(xiàn)在在查詢語(yǔ)句中加入一條Where語(yǔ)句，那么又會(huì)有什么樣的結(jié)果呢如下圖所示。

此時(shí)在最后一個(gè)Extra字段中，系統(tǒng)就會(huì)顯示已經(jīng)使用了Where語(yǔ)句。在進(jìn)行數(shù)據(jù)庫(kù)優(yōu)化中，我們需要抓住結(jié)果中的NULL字段或者空白內(nèi)容的字段。這些地方往往是我們進(jìn)行優(yōu)化的重點(diǎn)。如上圖所示，我們可以給這條Select語(yǔ)句進(jìn)行如下的優(yōu)化：在表中設(shè)置關(guān)鍵字或者索引，來(lái)提高查詢的效率。

二、數(shù)據(jù)比較時(shí)采用相同類(lèi)型的列以提高查詢效率

在數(shù)據(jù)查詢時(shí)，有時(shí)候會(huì)在條件語(yǔ)句中加入判斷的條件。如現(xiàn)在有兩張表：用戶基本信息表和用戶權(quán)限表，兩者通過(guò)用戶編號(hào)作為關(guān)聯(lián)?，F(xiàn)在需要查詢出每個(gè)用戶對(duì)應(yīng)什么樣的權(quán)限，此時(shí)就要通過(guò)用戶編號(hào)作為查詢條件來(lái)進(jìn)行查詢?，F(xiàn)在假設(shè)用戶基本信息表中的用戶編號(hào)字段為CHAR類(lèi)型的;而用戶權(quán)限表中的用戶編號(hào)是VARCHAR類(lèi)型的。這兩個(gè)數(shù)據(jù)類(lèi)型雖然都是字符型，但是不是同一種類(lèi)型?，F(xiàn)在對(duì)這連個(gè)表執(zhí)行關(guān)聯(lián)查詢，其查詢的效率如何呢首先需要確定的一點(diǎn)是，雖然他們兩個(gè)是不同類(lèi)型的字符型數(shù)據(jù)，不過(guò)是相互兼容的。最后仍然可以得到正確的結(jié)果。明確了這一點(diǎn)之后，我們?cè)賮?lái)考慮，能否對(duì)這個(gè)查詢語(yǔ)句進(jìn)行優(yōu)化呢

我們?cè)偌僭O(shè)一下。現(xiàn)在這兩個(gè)表的用戶編號(hào)的數(shù)據(jù)類(lèi)型都是CHAR?，F(xiàn)在再對(duì)這兩個(gè)表進(jìn)行關(guān)聯(lián)查詢，得到的結(jié)果是否相同呢我們測(cè)試的結(jié)果是，查詢的結(jié)果是相同的，但是其所花費(fèi)的時(shí)間是不同的。而且隨著數(shù)據(jù)量的增加，兩個(gè)查詢所相差的時(shí)間會(huì)越來(lái)越長(zhǎng)。從這里可以知道，雖然這兩個(gè)查詢語(yǔ)句是等價(jià)的，但是其查詢的效率不同。

在MySQL數(shù)據(jù)庫(kù)中，雖然相互兼容的數(shù)據(jù)類(lèi)型可以進(jìn)行相互比較。但是其查詢的效率會(huì)有所影響。從提高數(shù)據(jù)庫(kù)查詢效率的角度出發(fā)，筆者建議在查詢條件語(yǔ)句中最好比較具有相同類(lèi)型的列。在同等條件下，相同的列類(lèi)型比不同類(lèi)型的列能夠提供更好的性能。特別是在數(shù)據(jù)量比較多的數(shù)據(jù)庫(kù)中，這尤其重要。

不過(guò)這個(gè)優(yōu)化需要涉及到數(shù)據(jù)表的列類(lèi)型。為此在數(shù)據(jù)表進(jìn)行設(shè)計(jì)時(shí)，就需要考慮這一點(diǎn)。如針對(duì)上面這個(gè)案例，我們可以在兩個(gè)表中專(zhuān)門(mén)設(shè)置一個(gè)用戶ID列。可以使用整數(shù)類(lèi)型的序列，讓系統(tǒng)進(jìn)行自動(dòng)編號(hào)。然后在查詢時(shí)通過(guò)這個(gè)用戶ID列來(lái)進(jìn)行比較，而不是通過(guò)原來(lái)的用戶編號(hào)列進(jìn)行比較。相對(duì)來(lái)說(shuō)，這么操作查詢的效率會(huì)更高。

三、在Like關(guān)鍵字的起始處通配符要謹(jǐn)慎使用

在實(shí)際工作中，筆者發(fā)現(xiàn)不少數(shù)據(jù)庫(kù)管理員有一個(gè)不好的習(xí)慣。他們?cè)谑褂肔ike等關(guān)鍵字時(shí)，通配符會(huì)亂用。如現(xiàn)在用戶需要查找所有以“LOOK”為前綴的產(chǎn)品信息。用戶在查詢時(shí)，會(huì)習(xí)慣性的使用下面的語(yǔ)句進(jìn)行查詢：like “%LOOK%”。這個(gè)條件語(yǔ)句會(huì)查詢出所有品名中有LOOK這個(gè)單詞的紀(jì)錄，而不是查詢出以LOOK為前綴的產(chǎn)品信息。

雖然最終的結(jié)果可能是相同的。但是兩者的查詢效率不同。其實(shí)這很大一部分原因是客戶端應(yīng)用程序設(shè)計(jì)不當(dāng)所造成的。如在客戶端應(yīng)用程序設(shè)計(jì)時(shí)，系統(tǒng)會(huì)默認(rèn)顯示一個(gè)%符號(hào)。如下圖所示。

這么設(shè)計(jì)的本意是好的，讓系統(tǒng)能夠支持模糊查詢。但是用戶在實(shí)際操作起來(lái)，就可以有問(wèn)題。如用戶在查詢時(shí)，不會(huì)在%號(hào)前面輸入LOOK這個(gè)單詞，而是在%后面輸入LOOK這個(gè)單詞。因?yàn)樵诓樵儠r(shí)，光標(biāo)會(huì)自動(dòng)定位到%號(hào)后面。通常情況下，用戶在輸入時(shí)不會(huì)再去調(diào)整光標(biāo)的位置。此時(shí)就出現(xiàn)了上面所說(shuō)的這種情況。

為此筆者建議，在Like等關(guān)鍵字后面如果需要用到通配符的話，要非常的謹(jǐn)慎。特別是從大量數(shù)據(jù)中查找紀(jì)錄時(shí)，這個(gè)通配符的位置一定要用對(duì)地方。在起始處能夠不同通配符的話，盡量不要使用通配符。

四、盡量使用其它形式來(lái)代替Like關(guān)鍵字

上面提到在使用Like關(guān)鍵字時(shí)需要注意通配符的位置。其實(shí)從查詢效率來(lái)看，我們不僅需要注意通配符的位置，而且能夠不用Like關(guān)鍵字最好就不用。其實(shí)在SQL語(yǔ)句中，可以利用其他方式來(lái)代替Like關(guān)鍵字。如現(xiàn)在有一個(gè)產(chǎn)品表，其編號(hào)為6位。現(xiàn)在需要查詢以9開(kāi)頭的產(chǎn)品編號(hào)。這該怎么操作呢

一是可以通過(guò)使用Like關(guān)鍵字，如LIKE “9%”。注意這個(gè)通配符的位置。這個(gè)條件語(yǔ)句可以查到所需要的結(jié)果。但是從性能優(yōu)化的角度看，這條語(yǔ)句不是很好的處理方式。我們還可以通過(guò)一些折中的方式來(lái)實(shí)現(xiàn)。

二是通過(guò)比較符號(hào)來(lái)實(shí)現(xiàn)。如可以使用Value>=900000 and Value<=999999這種方式來(lái)實(shí)現(xiàn)。雖然兩者的查詢的結(jié)果是相同的。但是查詢的時(shí)間這條語(yǔ)句要比上面這個(gè)采用Like符號(hào)的語(yǔ)句要短的多。

以上就是“提升MySQL查詢效率及查詢速度優(yōu)化的方法是什么”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會(huì)為大家更新不同的知識(shí)，如果還想學(xué)習(xí)更多的知識(shí)，請(qǐng)關(guān)注億速云行業(yè)資訊頻道。