從四個細節出發做好MySQL查詢優化

眾所周知，在任何一個資料庫中，查詢優化都是不可避免的一個話題。 對於資料庫工程師來說，優化工作是最有挑戰性的工作。 MySQL開源資料庫也不例外。 其實筆者認為，資料庫優化並沒有大家所想像的那麼苦難。 通常情況下，大家可以從以下四個細節出發來做好MySQL資料庫的查詢優化工作。

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

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

通過Select查詢語句可以從資料庫中查詢某個表中的資料。 但是這條語句執行的效率如何?是否還有優化的餘地?這些內容是無法從上面這個簡單的查詢語句中獲得的。 為了瞭解更加詳細的資訊，需要加入Explain關鍵字。 如下圖所示：

加入Explain關鍵字之後，系統並沒有查詢出表格中的資料，而只是顯示了查詢過程中的一些資訊。 這些資訊對於我們後續進行資料庫查詢優化非常有説明。 從上面這個資訊中我們可以看出，使用者只是進行來一個簡單的查詢。 在這個查詢中，沒有用到任何索引、關鍵字等內容，也沒有用到Where條件陳述式。 為此這個查詢語句並不是很合理。 雖然其可以找到最後正確的結果，不過其查詢效率可能並不是很明顯。 為此資料庫專家可以根據上面顯示的資訊來進行優化。 如果我們現在在查詢語句中加入一條Where語句，那麼又會有什麼樣的結果呢?如下圖所示。

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

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

在資料查詢時，有時候會在條件陳述式中加入判斷的條件。 如現在有兩張表：使用者基本資訊表和使用者許可權表，兩者通過使用者編號作為關聯。 現在需要查詢出每個使用者對應什麼樣的許可權，此時就要通過使用者編號作為查詢準則來進行查詢。 現在假設使用者基本資訊表中的使用者編號欄位為CHAR類型的;而使用者許可權表中的使用者編號是VARCHAR類型的。 這兩個資料類型雖然都是字元型，但是不是同一種類型。 現在對這連個表執行關聯查詢，其查詢的效率如何呢?首先需要確定的一點是，雖然他們兩個是不同類型的字元型資料，不過是相互相容的。 最後仍然可以得到正確的結果。 明確了這一點之後，我們再來考慮，能否對這個查詢語句進行優化呢?

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

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

不過這個優化需要涉及到資料表的列類型。 為此在資料表進行設計時，就需要考慮這一點。 如針對上面這個案例，我們可以在兩個表中專門設置一個使用者ID列。 可以使用整數類型的序列，讓系統進行自動編號。 然後在查詢時通過這個使用者ID列來進行比較，而不是通過原來的使用者編號列進行比較。 相對來說，這麼操作查詢的效率會更高。

三、在Like關鍵字的起始處萬用字元要謹慎使用

在實際工作中，筆者發現不少資料庫管理員有一個不好的習慣。 他們在使用Like等關鍵字時，萬用字元會亂用。 如現在使用者需要查找所有以「LOOK」為首碼的產品資訊。 使用者在查詢時，會習慣性的使用下面的語句進行查詢：like 「%LOOK%」。 這個條件陳述式會查詢出所有品名中有LOOK這個單詞的紀錄，而不是查詢出以LOOK為首碼的產品資訊。

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

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

為此筆者建議，在Like等關鍵字後面如果需要用到萬用字元的話，要非常的謹慎。 特別是從大量資料中查找紀錄時，這個萬用字元的位置一定要用對地方。 在起始處能夠不同萬用字元的話，儘量不要使用萬用字元。

四、儘量使用其它形式來代替Like關鍵字

上面提到在使用Like關鍵字時需要注意萬用字元的位置。 其實從查詢效率來看，我們不僅需要注意萬用字元的位置，而且能夠不用Like關鍵字最好就不用。 其實在SQL語句中，可以利用其他方式來代替Like關鍵字。 如現在有一個產品表，其編號為6位。 現在需要查詢以9開頭的產品編號。 這該怎麼操作呢?

一是可以通過使用Like關鍵字，如LIKE 「9%」。 注意這個萬用字元的位置。 這個條件陳述式可以查到所需要的結果。 但是從性能優化的角度看，這條語句不是很好的處理方式。 我們還可以通過一些折中的方式來實現。

二是通過比較符號來實現。 如可以使用Value>=900000 and Value<=999999這種方式來實現。 雖然兩者的查詢的結果是相同的。 但是查詢的時間這條語句要比上面這個採用Like符號的語句要短的多。