這篇說下mysql查詢語句優化

是否請求了不需要的數據

典型案例：查詢不需要的記錄，多表關聯時返回全部列，總是取出全部列，重復查詢相同的數據。

是否在掃描額外的記錄

最簡單的衡量查詢開銷的指標。

響應數據掃描的行數返回的行數 訪問類型

在評估查詢開銷時，需要考慮下從表中找到某一行數據的成本，mysql有好多種方式可以查找并返回一行結果。有些訪問方式可能需要掃描很多行才能返回一行結果，也有些方式可能無須掃描就能返回結果。

在EXPLAIN語句中type列反應了訪問類型。訪問類型有很多種，從全表掃描到索引掃描，范圍掃描，唯一索引查詢，常數引用等。這里列的這些，速度是從慢到快，掃描的行數也是從小到大。

因此，要盡力避免讓每一條sql做全表掃描。

如果查詢沒辦法找到合適的訪問類型，那么解決的最好方式通常就是增加一個合適的索引，這個上一篇里說到過。索引讓mysql以最高效，掃描行數最少的方式找到需要的記錄。

一般mysql有三種方式應用where條件。從好到壞依次為

在索引中使用where條件過濾不匹配的記錄，這是在存儲引擎層中完成。使用索引覆蓋掃描（在extra列中出現using index）來返回記錄，直接從索引中過濾不需要的記錄并返回命中的結果。這是在mysql服務層完成的，但不用再回表查詢記錄。從表中返回數據，然后過濾不滿足條件的記錄（在extra列中出現where）,這是在mysql服務層完成的,mysql需要先從數據表中讀取記錄然后過濾。

如果發現查詢中掃描大量的數據卻只返回少量的行。可以嘗試下面方法優化。

使用索引覆蓋掃描，把所有需要用到的列都放到索引中，這樣存儲引擎不用回表獲取對應行就可以返回結果了。改變表的結構，例如使用單獨的匯總表重寫這個復雜的查詢，讓mysql優化器以更優化的方式執行這個查詢重構查詢方式

一個復雜查詢還是多個簡單查詢？

在傳統實現中，總是強調數據庫層完成盡可能多的工作，這樣做的邏輯在于以前總是認為網絡通信，查詢解析，優化是一件代價很高的事。

但是這樣的想法對于mysql并不適用，mysql從設計上讓連接和斷開連接都很輕量，在返回一個小的查詢結果方面很高效。另外，現在的網絡速度比以前快的多，無論是寬帶還是延遲。在某些版本的mysql上，即便在一個通用的服務器上，也能運行每秒超過10萬的查詢。即使是一個千兆網卡也能輕松滿足每秒超過2000次的查詢。

切分查詢

即所謂的分而治之，將大查詢切分成小查詢，每個查詢功能完全一樣，每次只返回一小部分結果。

刪除舊的數據就是個很好的例子，定期的清理大量數據時，如果用一個大語句一次性完成的話，則可能一次鎖住很多數據，占滿整個事務日志，耗盡系統資源，阻塞很多小的但很重要的查詢。

因此可以

分解關聯查詢

簡單說，就是對每個表進行一次單表查詢，然后將結果在應用程序中進行關聯。例如

可以將其分解成下面查詢來替代

乍一看，這樣做沒有好處。事實上，有下面這些優勢

讓緩存效率更高。許多應用程序可以方便的緩存單表查詢對應的結果對象。將查詢分解后，執行單個查詢可以減少鎖的競爭。在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能，可擴展。查詢本身效率也會有所提升。在這個例子中，使用in代替關聯查詢，可以讓mysql按照id順序進行查詢，這可能比隨機的關聯更高效。可以減少冗余記錄的查詢。做關聯查詢時，可能需要重復訪問一部分數據。從這點看，這樣的重構還可能減少網絡和內存的消耗。實現了哈希關聯，而不是使用mysql的嵌套循環關聯。某些場景，哈希關聯的效率要高很多。 mysql如何執行關聯查詢

mysql中“關聯”一詞所包含的意義比一般理解上要更廣泛。總的來說，mysql認為任何一個查詢都是一次“關聯”，并不僅僅是一個查詢需要到兩個表匹配才叫關聯。所以，在mysql中，每個查詢，每個片段（包括子查詢，甚至基于單表的select）都可能是關聯。

下面看下mysql如何執行關聯查詢。

先看union查詢。mysql先將一系列的單個查詢結果放到一個臨時表中，然后再重新讀取臨時表數據完成union查詢。在mysql概念中，每個查詢都是一次關聯，所以讀取結果臨時表也是一次關聯。

mysql對任何關聯都執行嵌套循環關聯策略，即mysql先在一個表中循環取出單條數據，然后再嵌套循環到下一個表中尋找匹配的行，依次下去，直到所有表中匹配的行為止。然后根據各個表匹配的行，返回查詢中所需要的各個列。

可以看到查詢是從actor表開始的,這是mysql關聯查詢優化器自動做的選擇。現在用STRAIGHT_JOIN關鍵字，不讓mysql自動優化關聯。

這次的關聯順序倒轉過來，可以看到，倒轉后第一個關聯表只需要掃描很少的行數。而且第二個，第三個關聯表都是根據索引查詢，速度都很快。

最后，確保任何的group by,order by中的表達式只涉及到一個表中的列，這樣mysql才有可能使用索引優化這個過程。

排序優化

無論如何排序都是一個成本很高的操作。所以從性能角度考慮，應盡可能避免排序或避免對大量數據進行排序。

上一篇說到了如何通過索引排序。當不能使用索引生成排序結果時，mysql需要自己進行排序，如果數據量小，就在內存中進行，數據量大，則需要使用磁盤。mysql統一將這一過程稱為文件排序（filesort）。

在關聯查詢時如果需要排序，mysql會分兩種情況處理文件排序。

1.如果order by子句中的所有列都來自關聯的第一個表，mysql在關聯處理第一個表時就進行文件排序。如果是這樣，在EXPLAIN結果中的Extra字段會有Using filesort.

2.除此之外的所有情況，mysql都會先將關聯的結果存放到一個臨時表中，然后在所有的關聯結束后再進行文件排序。如果是這樣，在EXPLAIN結果中的Extra字段會有Using temporary;Using filesort.如果查詢中有LIMIT的話，LIMIT也會在排序之后應用。所以即使需要返回較少的行數，臨時表和需要排序的數據量仍然會非常大。

mysql5.6在這里做了很多重要的改進。當只需要返回部分排序結果的時候，例如，使用LIMIT子句，mysql不再所有結果排序，而是根據實際情況，選擇拋棄不滿足條件的結果，然后再排序。

關聯子查詢

mysql的子查詢實現非常糟糕，最糟糕的一類查詢是where條件中包含in的子查詢語句。

mysql對in()列表中的選項有專門的優化策略，一般會認為，mysql會先執行子查詢。但是，很不幸，mysql會先將相關的外層表押到子查詢中。例如

mysql會將查詢改成這樣

可以看到，mysql會先對film進行全表掃描，然后根據返回的film_id逐個執行子查詢。如果外層表是個非常大的表，那這個查詢的性能會非常糟糕。當然很容易重寫這個查詢，直接用關聯就可以了。

另一個優化方法是使用函數GROUP_CONCAT()在IN()中構造一個由逗號分隔的列表。

另外，通常建議用EXISTS()等效的改寫IN()子查詢。

如何用好關聯子查詢

并不是所有的關聯子查詢性能都會很差。寫好之后，先測試，然后做出自己的判斷。有時候，子查詢也會快些，例如當返回結果中只有一個表的某些列時，假設要返回所有包含同一個演員參演的電影，因為一個電影會有很多演員參演，所以可能會返回些重復記錄。

使用DISTINCT和GROUP BY移除重復的記錄

如果用EXISTS的話，就不需要使用DISTINCT和GROUP BY，也不會產生重復的結果集。我們知道一旦使用DISTINCT和GROUP BY，那么在執行過程中，通常會參數臨時中間表。

測試，看哪種寫法快點

可以看到在這個案例中，子查詢速度要快些。

最值優化

對于MIN(),MAX()，mysql的優化做的并不好，例如

mysql不能夠進行主鍵掃描，只有全表掃描了。這時可以用LIMIT重寫查詢。

這樣可以讓mysql掃描盡可能少的表

優化group by和distinct

它們都可以使用索引優化，這也是最有效的辦法。當無法使用索引時，group by使用兩種策略完成：使用臨時表或文件排序來做分組。

對關聯查詢分組，通常用查找表的標識符分組的效率比其他列更高。例如

下面的效率更高

這個查詢利用了演員姓名和id直接相關的特點，所以改寫后的結果不受影響。

如果不相關的話，可以用MIN(),MAX().繞過這種限制。但一定要清楚，select后面出現的非分組列一定是直接依賴分組列的，并且在每個組內的值是唯一的。

實在較真的話，寫成這樣

不過這樣成本有點高。因為子查詢需要創建和填充臨時表，而創建的臨時表是沒有任何索引的。

優化LIMIT分頁

最簡單的辦法是盡可能使用索引覆蓋掃描，而不是查詢所有的列。然后根據需要做一次關聯操作，再返回所需的列。例如

如果這個表非常大，最好改寫成這樣

這里的”延遲關聯“將大大提升效率，讓mysql掃描盡可能少的頁面，獲取需要訪問的記錄后再根據關聯列回原表查詢需要的所有列。這個也可以用來優化關聯查詢里面的limit.

有時候也可以將limit查詢轉換為已知位置的查詢，讓mysql通過范圍掃描獲得結果。例如

在一個位置列上有索引，并且預先計算出了邊界值。

另外，limit和offset的問題，會導致mysql掃描了大量不需要的行然后在拋棄掉，比如select .... limit 1000,20.

這時可以有變通方法，例如圖書館按照租借記錄翻頁，獲取第一頁。

因為rental_id是遞增的，而查看記錄的時候都是從離當前時間最近的地方開始的。后面的頁就可以用類似于下面的查詢實現