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

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

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

是否在掃描額外的記錄

最簡(jiǎn)單的衡量查詢開銷的指標(biāo)。

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

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

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

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

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

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

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

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

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

一個(gè)復(fù)雜查詢還是多個(gè)簡(jiǎn)單查詢？

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

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

切分查詢

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

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

因此可以

分解關(guān)聯(lián)查詢

簡(jiǎn)單說，就是對(duì)每個(gè)表進(jìn)行一次單表查詢，然后將結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)。例如

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

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

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

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

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

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

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

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

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

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

排序優(yōu)化

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

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

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

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

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

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

關(guān)聯(lián)子查詢

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

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

mysql會(huì)將查詢改成這樣

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

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

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

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

并不是所有的關(guān)聯(lián)子查詢性能都會(huì)很差。寫好之后，先測(cè)試，然后做出自己的判斷。有時(shí)候，子查詢也會(huì)快些，例如當(dāng)返回結(jié)果中只有一個(gè)表的某些列時(shí)，假設(shè)要返回所有包含同一個(gè)演員參演的電影，因?yàn)橐粋€(gè)電影會(huì)有很多演員參演，所以可能會(huì)返回些重復(fù)記錄。

使用DISTINCT和GROUP BY移除重復(fù)的記錄

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

測(cè)試，看哪種寫法快點(diǎn)

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

最值優(yōu)化

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

mysql不能夠進(jìn)行主鍵掃描，只有全表掃描了。這時(shí)可以用LIMIT重寫查詢。

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

優(yōu)化group by和distinct

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

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

下面的效率更高

這個(gè)查詢利用了演員姓名和id直接相關(guān)的特點(diǎn)，所以改寫后的結(jié)果不受影響。

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

實(shí)在較真的話，寫成這樣

不過這樣成本有點(diǎn)高。因?yàn)樽硬樵冃枰獎(jiǎng)?chuàng)建和填充臨時(shí)表，而創(chuàng)建的臨時(shí)表是沒有任何索引的。

優(yōu)化LIMIT分頁

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

如果這個(gè)表非常大，最好改寫成這樣

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

有時(shí)候也可以將limit查詢轉(zhuǎn)換為已知位置的查詢，讓mysql通過范圍掃描獲得結(jié)果。例如

在一個(gè)位置列上有索引，并且預(yù)先計(jì)算出了邊界值。

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

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

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