提升MySQL查詢效率及查詢速度最佳化的方法是什麼

一、利用EXPLAIN關鍵字來評估查詢語句中的缺陷

筆者在資料庫中執行了一條簡單的Select查詢語句，以獲取一個表格中的所有信息，如下圖所示。現在資料庫管理員想知道，資料庫執行這條語句時，做了哪些工作或是說想知道，這條查詢語句有沒有進一步優化的可能。如果要了解這個資訊的話，就可以在查詢語句中加入一個Explain關鍵字。

透過Select查詢語句可以從資料庫中查詢某個表格中的資料。從這個簡單的查詢語句中無法得出執行效率和最佳化餘地這些內容。為了了解更加詳細的信息，需要加入Explain關鍵字。如下圖所示：

加入Explain關鍵字之後，系統並沒有查詢出表格中的數據，而只是顯示了查詢過程中的一些資訊。這些資訊對於我們後續進行資料庫查詢優化非常有幫助。從上面這個資訊我們可以看出，使用者只是進行來一個簡單的查詢。在此查詢中，未使用任何索引、關鍵字或Where條件語句。為此這個查詢語句並不是很合理。雖然其可以找到最後正確的結果，不過其查詢效率可能並不是很明顯。為此資料庫專家可以根據上面顯示的資訊來進行最佳化。 What would be the result if we add a WHERE statement to our query now, as shown in the following image?。

當在最後一個Extra欄位中使用Where語句時，系統將顯示已經使用。在進行資料庫最佳化中，我們需要抓住結果中的NULL欄位或空白內容的欄位。這些地方往往是我們進行最佳化的重點。可以透過在表中設定關鍵字或索引來優化這條Select語句，提高查詢效率，如圖所示。

二、資料比較時採用相同類型的欄位以提高查詢效率

在資料查詢時，有時會在條件語句中加入判斷的條件。現在有兩個表格：使用者基本資訊表格和使用者權限表格，它們透過使用者編號進行關聯。要查詢每位使用者的權限訊息，需要以使用者編號作為查詢條件進行查詢。現在假設使用者基本資訊表中的使用者編號欄位為CHAR類型的;而使用者權限表中的使用者編號是VARCHAR類型的。這兩個資料型別雖然都是字元型，但是不是同一種類型。現在對這連個表執行關聯查詢，其查詢的效率如何呢首先需要確定的一點是，雖然他們兩個是不同類型的字符型數據，不過是相互兼容的。最後仍然可以得到正確的結果。在明確了這一點之後，讓我們再考慮一下是否可以優化這個查詢語句

我們再假設一下。現在這兩個表的使用者編號的資料型別都是CHAR。現在再對這兩個表進行關聯查詢，得到的結果是否相同呢我們測試的結果是，查詢的結果是相同的，但是其所花費的時間是不同的。而且隨著資料量的增加，兩個查詢相差的時間會越來越長。從這裡可以知道，雖然這兩個查詢語句是等價的，但是其查詢的效率不同。

雖然資料類型在MySQL資料庫中相互相容，但仍可以進行比較。但是其查詢的效率會有所影響。為了提高資料庫查詢效率，筆者建議在查詢條件語句中最好比較具有相同類型的欄位。在同等條件下，相同的列類型比不同類型的列能夠提供更好的效能。特別是在資料量比較多的資料庫中，這尤其重要。

不過這個最佳化需要涉及到資料表的欄位類型。為此在資料表進行設計時，就需要考慮這一點。為了上述案例，我們可以在這兩個表格中特別新增一個使用者ID欄位。可以使用整數類型的序列，讓系統進行自動編號。然後在查詢時透過這個用戶ID列來進行比較，而不是透過原來的用戶編號列來比較。相對來說，這麼操作查詢的效率會更高。

三、在Like關鍵字的起始處通配符要謹慎使用

在實際工作中，筆者發現不少資料庫管理員有一個不好的習慣。他們在使用Like等關鍵字時，通配符會亂用。如現在用戶需要尋找所有以「LOOK」為前綴的產品資訊。查詢時，使用者通常會習慣使用類似以下的語句：使用 like “%LOOK%”。此條件語句將取得所有品名中包含LOOK單字的記錄，而非僅取得以LOOK為前綴的產品資訊。

雖然最終的結果可能是相同的。但是兩者的查詢效率不同。其實這很大一部分原因是因為客戶端應用程式設計不當所造成的。如在客戶端應用程式設計時，系統會預設顯示一個%符號。如下圖所示。

這麼設計的本意是好的，讓系統能夠支援模糊查詢。但是使用者在實際操作起來，就可以有問題。如果使用者在查詢時沒有在%前輸入單字LOOK，而是在%後輸入單字。因為在查詢時，遊標會自動定位到%號後面。通常情況下，使用者在輸入時不會再去調整遊標的位置。此時就出現了上面所說的這種情況。

為了避免意外狀況，筆者強烈建議在使用Like等關鍵字後跟通配符時要十分謹慎。特別是從大量數據中尋找紀錄時，這個通配符的位置一定要用對地方。在起始處能夠不同通配符的話，盡量不要使用通配符。

四、盡量使用其它形式來取代Like關鍵字

上面提到在使用Like關鍵字時需要注意通配符的位置。在查詢效率方面，我們需要注意通配符的位置，並盡可能避免使用「Like」關鍵字。其實在SQL語句中，可以利用其他方式來取代Like關鍵字。如現在有一個產品表，其編號為6位。現在需要查詢以9開頭的產品編號。這該怎麼操作呢

一是可以透過使用Like關鍵字，如LIKE “9%”。注意這個通配符的位置。這個條件語句可以查到所需要的結果。但是從效能優化的角度來看，這條語句不是很好的處理方式。我們也可以透過一些折衷的方式來實現。

二是透過比較符號來實現。這句話可以重新表達為：可以實現透過使用Value在900000到999999之間的方法。雖然兩者的查詢的結果是相同的。但是查詢的時間這條語句要比上面這個採用Like符號的語句要短的多。

以上是提升MySQL查詢效率及查詢速度最佳化的方法是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！