這篇說下mysql查詢語句優(yōu)化

是否請求了不需要的數(shù)據(jù)

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

是否在掃描額外的記錄

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

響應數(shù)據(jù)掃描的行數(shù)返回的行數(shù) 訪問類型

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

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

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

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

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

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

如果發(fā)現(xiàn)查詢中掃描大量的數(shù)據(jù)卻只返回少量的行。可以嘗試下面方法優(yōu)化。

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

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

在傳統(tǒng)實現(xiàn)中，總是強調數(shù)據(jù)庫層完成盡可能多的工作，這樣做的邏輯在于以前總是認為網(wǎng)絡通信，查詢解析，優(yōu)化是一件代價很高的事。

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

切分查詢

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

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

因此可以

分解關聯(lián)查詢

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

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

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

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

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

下面看下mysql如何執(zhí)行關聯(lián)查詢。

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

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

可以看到查詢是從actor表開始的,這是mysql關聯(lián)查詢優(yōu)化器自動做的選擇。現(xiàn)在用STRAIGHT_JOIN關鍵字，不讓mysql自動優(yōu)化關聯(lián)。

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

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

排序優(yōu)化

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

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

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

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

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

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

關聯(lián)子查詢

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

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

mysql會將查詢改成這樣

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

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

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

如何用好關聯(lián)子查詢

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

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

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

測試，看哪種寫法快點

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

最值優(yōu)化

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

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

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

優(yōu)化group by和distinct

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

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

下面的效率更高

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

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

實在較真的話，寫成這樣

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

優(yōu)化LIMIT分頁

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

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

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

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

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

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

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

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