高性能的MySQL（6）查詢慢與重構查詢

隻有好的庫表結構、合理的索引還不夠，我們還需要合理的設計查詢，齊頭并進，一個不少才能充分發揮MySQL的優勢。

一、查詢為什麼會慢？

每一個查詢由一系列的子任務組成，每個子任務都會消耗一定的時間。這個我們在之前的單個查詢分析時已經簡單介紹了，當然還有額外的因素，比方說包括網絡，CPU計算，統計資訊，執行計劃，鎖等待等操作，或者底層引擎在調用記憶體，CPU操作，I/O操作等上的消耗時間。、

優化查詢的目的就是減少和消除這些操作所花費的時間。

查詢性能低下的最基本原因是通路的資料太多，大部分的性能低下的查詢可以通過減少通路的資料量進行優化，一般有2個簡單的分析方法：

1、确認應用程式是否傳回了大量超過需要的資料，這就是說通路了太多的行，也有時候是因為通路了太多的列，這會增加很多額外的開銷，包括，網絡，CPU，記憶體等。

一些常見的例子：

a、查詢不需要的記錄

一個常見的錯誤是常常會以為MySQL會隻傳回需要的資料，實際上卻是先傳回全部的查詢結果再進行計算，一個簡單有效的解決方法是在查詢後面加上LIMIT。

b、多表關聯時傳回全部列

比如，要查找所有在電影hreo中出現的演員，不要這樣去寫

1

2

3

4

<code>select</code> <code>* </code><code>from</code> <code>actor</code>

<code>inner</code> <code>join</code> <code>film_actor using(actor_id)</code>

<code>inner</code> <code>join</code> <code>film using(film_id)</code>

<code>where</code> <code>film.title = </code><code>"hreo"</code><code>;</code>

這會傳回三個表的全部列，應該隻傳回需要的列

<code>select</code> <code>actor.* </code><code>from</code> <code>actor</code>

c、不要總是取出全部的列

<code>select</code> <code>* </code><code>from</code> <code>actor....</code>

d、重複查詢相同的資料

例如在使用者評論的地方需要查詢使用者的頭像，如果使用者多次評論，可能會反複查詢這個資料，可以先緩存起來，這樣會更好。

2、确認MySQL服務層是否在傳回前檢索大量超過需要的資料行。

如果查詢為了傳回結果掃描過多的資料，那麼就不合适了，一般看3個名額：

a、響應時間

響應時間分為服務時間和排隊時間。這個很難細分，如果是在一個合理的值，那就可以接受。

b、掃描的行和傳回的行

這個在一定程度上能夠說明該查詢找到需要的資料效率怎麼樣。理想的情況下，掃描的行和傳回的行是相同的，不過實際中這是很難的，特别是做關聯查詢時。

c、掃描的行和通路類型

在explain語句中的type列反應了通路類型。從全表掃描到索引掃描，範圍掃描，唯一索引查詢，常數引用等，速度從慢到快，掃描的行從大到小。一般我們增加一個合适的索引就可以很高效了。

5

6

7

8

9

10

11

12

<code>CREATE</code> <code>TABLE</code> <code>`emp5` (</code>

<code>  </code><code>`id` </code><code>int</code><code>(11) </code><code>NOT</code> <code>NULL</code> <code>DEFAULT</code> <code>'0'</code><code>,</code>

<code>  </code><code>`</code><code>name</code><code>` </code><code>varchar</code><code>(100) </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`job` </code><code>varchar</code><code>(100) </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num1` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num2` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num3` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`job_num` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`d` </code><code>date</code> <code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>PRIMARY</code> <code>KEY</code> <code>(`id`),</code>

<code>  </code><code>KEY</code> <code>`job_num` (`job_num`)</code>

<code>) ENGINE=MyISAM </code><code>DEFAULT</code> <code>CHARSET=utf8;</code>

<a href="http://blog.51cto.com/attachment/201310/174912329.png" target="_blank"></a>

這裡說明使用了索引類型，如果去掉了索引

<a href="http://blog.51cto.com/attachment/201310/175032172.png" target="_blank"></a>

通路類型為全表掃描（ALL）

一般MySQL能夠使用如下三種方式應用where,從好到壞依次是

a、在索引中使用where過濾不比對的資料，引擎層完成。

b、使用索引覆寫掃描，在Extra中出現了Using index,直接從索引中過濾掉不需要的記錄，服務層完成，不需回表取資料。

c、從資料表傳回資料，然後過濾，在Extra中出現了Using where ，在服務層完成。

二、重構查詢

一個複雜查詢還是多個簡單查詢，是否需要将一個複雜的查詢分成多個簡單的查詢，這是一個需要好好衡量的問題了。

1、切分查詢

删除資料就是一個很好的例子。定期的清楚大量的資料，可能需要鎖住大量的資料，占滿整個事務，耗盡資源，阻塞很多小的查詢，切分是一個很好的辦法。

比方說：把如下的句子

<code>delete</code> <code>from</code> <code>message </code><code>where</code> <code>create_time &lt; date_sub(now(),interval 3 </code><code>month</code><code>);</code>

換成如下：

<code>rows_affected=0</code>

<code>do{</code>

<code>    </code><code>rows_affected = do_query(</code>

<code>    </code><code>"delete from message where create_time &lt; date_sub(now(),interval 3 month limit 10000"</code><code>)</code>

<code>    </code><code>)</code>

<code>}while rows_affected &gt; 0</code>

一次删除10000行，影響就會很小，壓力就會分擔開來了。

2、分解關聯查詢

把關聯查詢進行分解，例如下面的查詢：

<code>select</code> <code>* </code><code>from</code> <code>tag</code>

<code>join</code> <code>tag_post </code><code>on</code> <code>tag_post.tag_id = tag_id</code>

<code>join</code> <code>post </code><code>on</code> <code>tag_post.post_id = post.id</code>

<code>where</code> <code>tag.tag = </code><code>'hreo'</code><code>;</code>

分解為：

<code>select</code> <code>* </code><code>from</code> <code>tag </code><code>where</code> <code>tag = </code><code>'hreo'</code><code>;</code>

<code>select</code> <code>* </code><code>from</code> <code>tag_post </code><code>where</code> <code>tag_id=1234;</code>

<code>select</code> <code>* </code><code>from</code> <code>post </code><code>where</code> <code>id </code><code>in</code> <code>(123,546,432);</code>

乍一看，我們好像複雜化了，但是分解後還是有很多好處的，有的時候我們的卻是需要這樣做的 。

a、讓緩存的效率更高，如果第一個查詢的結果已經緩存了，那麼就可以跳過第一個查詢，另外對一MySQL的查詢緩存Query Cache來說，如果關聯的表發生了修改，就無法使用緩存了，拆分後，那麼一個表的改變不會影響其他表的緩存。

b、單個查詢可以減少鎖的競争。

c、在應用層做關聯，有更好的擴充性。

d、可以減少備援記錄的查詢，因為資料庫關聯查詢時，可能需要重複的通路一部分資料。

e、這個相當于實作了哈希關聯，而不是MySQL的嵌套循環關聯，某些時候哈希關聯效率高很多，這點以後會有介紹。

這個效果，在負載均衡，或者資料分布在不同的資料庫是更明顯。

本文轉自shayang8851CTO部落格，原文連結：http://blog.51cto.com/janephp/1314620，如需轉載請自行聯系原作者