目錄
[toc]

1.為什么查詢速度變慢

在嘗試編寫快速的查詢之前,需要清楚一點(diǎn),真正重要是響應(yīng)時(shí)間龙致。如果把查詢看作是一個(gè)任務(wù)，那么他由一系列子任務(wù)組成顷链，每個(gè)子任務(wù)都會(huì)消耗一定的時(shí)間目代。如果要優(yōu)化查詢，實(shí)際上要優(yōu)化其子任務(wù)嗤练，要么消除其中一些子任務(wù)榛了，要么減少子任務(wù)的執(zhí)行的次數(shù)，要么讓子任務(wù)運(yùn)行得更快煞抬∷螅　　
MySQL在執(zhí)行查詢的時(shí)候有哪些子任務(wù)。哪些子任務(wù)運(yùn)行的速度很慢,這里很難給出完整的列表革答，通常來說查詢的生命周期大致可以按照順序來看：從客戶端战坤，到服務(wù)器遮婶，然后再服務(wù)器上進(jìn)行解析，生成執(zhí)行計(jì)劃湖笨，執(zhí)行旗扑，并返回結(jié)果給客戶端。其中“執(zhí)行”可以認(rèn)為是整個(gè)生命周期中最重要的階段慈省，這其中包括了大量為了檢索數(shù)據(jù)到存儲(chǔ)引擎的調(diào)用以及調(diào)用后的數(shù)據(jù)處理臀防，包括排序、分組等边败「ぶ裕　　　　
在完成這些任務(wù)的時(shí)候，查詢需要在不同的地方花費(fèi)時(shí)間笑窜，包括網(wǎng)絡(luò)致燥，CPU計(jì)算，生成統(tǒng)計(jì)信息和執(zhí)行計(jì)劃排截、鎖等待（互斥等待）等操作嫌蚤，尤其是向底層存儲(chǔ)引擎檢索數(shù)據(jù)的調(diào)用操作，這些調(diào)用需要在內(nèi)存操作断傲、CPU操作和內(nèi)存不足時(shí)導(dǎo)致的I/O操作上消耗時(shí)間脱吱，根據(jù)引擎不同，可能還會(huì)產(chǎn)生大量的上下文切換以及系統(tǒng)調(diào)用认罩∠潋穑　　
在每一個(gè)消耗大量時(shí)間的查詢案例中，我們都能看到一些不必要的額外操作垦垂、某些操作被額外地重復(fù)了很多次宦搬、某些操作執(zhí)行得太慢等。優(yōu)化查詢的目的就是減少和消除這些操作所花費(fèi)的時(shí)間劫拗。

2.慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)庫訪問

2.1 是否想服務(wù)器請(qǐng)求了不需要的數(shù)據(jù)：

請(qǐng)求過量的數(shù)據(jù)或者重復(fù)請(qǐng)求數(shù)據(jù)

2.2 MYSQL是否在掃描額外的記錄

最簡(jiǎn)單衡量查詢開銷的三個(gè)指標(biāo)響應(yīng)時(shí)間：分為服務(wù)時(shí)間和排隊(duì)時(shí)間　　　　　　　　　　　　
服務(wù)時(shí)間：是指數(shù)據(jù)庫處理這個(gè)查詢真正花了多長(zhǎng)時(shí)間　　　　　　　　　　　　
排隊(duì)時(shí)間：是指服務(wù)器因?yàn)榈却承┵Y源而沒有真正執(zhí)行查詢的時(shí)間——坑內(nèi)是等I/O操作完成间校，也可能使行鎖等等掃描的行數(shù)返回的行數(shù)　　　　　　　　　　　
在EXPLAIN語句中的type列反應(yīng)了訪問的類型。訪問類型有很多種杨幼，從全表掃描到索引掃描撇簿、范圍掃描、唯一索引查詢差购、常數(shù)引用等。這里列的這些汉嗽，速度是從慢到快欲逃，掃描的行數(shù)也是小到大。你不需要記住這些訪問類型饼暑，但是要明白掃描表稳析，掃描索引洗做，范圍訪問和單值訪問的概念。如果查詢沒有辦法找到合適的訪問類型彰居，那么最好的辦法通常就是增加一個(gè)合適的索引诚纸。　　　　
一般MySQL能夠使用如下三種應(yīng)用WHERE條件陈惰，從好到壞依次為：
在索引中使用WHERE條件來過濾不匹配的記錄畦徘。這是在存儲(chǔ)引擎層完成的。使用索引覆蓋掃描來返回記錄抬闯，直接從索引中過濾不需要的記錄并返回命中的結(jié)果井辆。這是在MySQL服務(wù)器層完成的，但無須在回表查詢記錄溶握。從數(shù)據(jù)表中返回?cái)?shù)據(jù)杯缺，然后過濾不滿足條件的記錄。這是在MySQL服務(wù)器層完成睡榆，MySQL需要先從數(shù)據(jù)表讀出記錄然后過濾萍肆。　　　　
如果說發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)但只返回少數(shù)的行胀屿，那么通池遗福可以嘗試下面的技巧去優(yōu)化它：使用索引覆蓋掃描，把所有需要用到的列都放到索引中碉纳，這樣存儲(chǔ)引擎無須回表獲取對(duì)應(yīng)行就可以返回結(jié)果改變庫表結(jié)構(gòu)勿负。例如使用單獨(dú)的匯總表重寫這個(gè)復(fù)雜的查詢，讓MySQL優(yōu)化器能夠以更優(yōu)化的方式執(zhí)行這個(gè)查詢

3.重構(gòu)查詢方式

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

3.2 切分查詢：

將大查詢切分成小查詢劳曹，每個(gè)查詢完全一樣奴愉，只完成一小部分，每次只返回一小部分查詢結(jié)果铁孵。

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

對(duì)每一個(gè)表進(jìn)行一次單表查詢锭硼，然后再應(yīng)用程序中進(jìn)行關(guān)聯(lián)，例如

mysql> SELECT* FROM tag　JOIN tag_post ON   tag_post.tag_id=tag.id 　JOIN pos ON tag_post.post_id=post.id WHERE  tag.tag='mysql'　

可以分解成下面的語句來代替

mysql> SELECT *  FROM tag WHERE tag='mysql'
mysql> SELECT *  FROM tag_post WHERE tag_id=1234
mysql> SELECT *   FROM post WHERE post.id in (123,456,567,9098,8904);　

使用分解關(guān)聯(lián)查詢的方式重構(gòu)查詢有如下的優(yōu)化：讓緩存的效率更高蜕劝。將查詢分解后檀头，執(zhí)行單個(gè)查詢可以減少鎖的競(jìng)爭(zhēng)。在應(yīng)用層做關(guān)聯(lián)岖沛，可以更容易的對(duì)數(shù)據(jù)庫進(jìn)行拆分暑始，更容易做到高性能和可擴(kuò)展。查詢本身效率也可能會(huì)有所提升婴削±染担可以減少冗余記錄的查詢。這樣做相當(dāng)于在應(yīng)用中實(shí)現(xiàn)了哈希關(guān)聯(lián)唉俗，而不是使用MySQL的嵌套循環(huán)關(guān)聯(lián)嗤朴。

4.查詢執(zhí)行的基礎(chǔ)

查詢執(zhí)行路徑　　　　[圖片上傳失敗...(image-33ee56-1594651325216)]

步驟：　　　　　　　　
（1）客服端發(fā)送一條查詢給服務(wù)器　　　　　　　　
（2）服務(wù)器先檢查查詢緩存配椭，如果命中緩存，則立刻返回存儲(chǔ)在緩存中的結(jié)果雹姊。否則進(jìn)入下一個(gè)階段股缸。　　　　　　　　
（3）服務(wù)器端進(jìn)行SQL解析吱雏、預(yù)處理敦姻，在由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃】脖常　　　　　　　　
（4）MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃替劈，調(diào)用存儲(chǔ)引擎的API來執(zhí)行查詢　　　　　　　　
（5）將結(jié)果返回給客戶端

4.1 MySQL客戶端/服務(wù)器通信協(xié)議

（1）MySQL客戶端和服務(wù)器之間的通訊是”雙半工“的，這意味著得滤，在任何一個(gè)時(shí)刻陨献，要么是由服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是由客戶端向服務(wù)器發(fā)送數(shù)據(jù)懂更，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生眨业。　　　　
（2）查詢狀態(tài)：對(duì)于一個(gè)MySQL連接沮协，或者說一個(gè)線程龄捡，任何時(shí)刻都有一個(gè)狀態(tài)，表示MySQL當(dāng)前在做什么慷暂。我們使用最簡(jiǎn)單的SHOW FULL PROCESSLIST命令（該命令返回結(jié)果中的Command列就表示當(dāng)前的狀態(tài)）來查詢聘殖。
下面將這些狀態(tài)列出來，并做一個(gè)簡(jiǎn)單的解釋：　　　
a. Sleep：線程正在等待客戶端發(fā)送新的請(qǐng)求行瑞。
b. Query：線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端奸腺。　　
c. Locked：在MySQL服務(wù)器層血久，該線程正在等待表鎖突照。
d. Analyzing and statistics : 線程正在收集存儲(chǔ)引擎的統(tǒng)計(jì)信息，并生成查詢的執(zhí)行計(jì)劃氧吐。
e. Coping to tmp table [on disk]：線程正在執(zhí)行查詢讹蘑，并且將其結(jié)果都復(fù)制到一個(gè)臨時(shí)表中，這種狀態(tài)一般要么是在做GROUP BY操作筑舅，要么是文件排序操作座慰，或者是UNION操作。如果這個(gè)狀態(tài)后面還有標(biāo)記豁翎，那表示MySQL正在講一個(gè)內(nèi)存臨時(shí)表放到磁盤上
f. Sorting result：線程正在對(duì)結(jié)果集進(jìn)行排序
g. Sending data：這表示多種情況：線程可能在對(duì)多個(gè)狀態(tài)之間傳輸數(shù)據(jù)角骤，或者而在生成結(jié)果集，或者在向客戶端返回?cái)?shù)據(jù)心剥。

4.2 查詢緩存

（1）在解析一個(gè)查詢語句之前邦尊，如果查詢緩存是打開的，那么MYSQL會(huì)優(yōu)先檢查這個(gè)查詢是否命中查詢緩存中的數(shù)據(jù)优烧〔踝幔　　　　
（2）這個(gè)檢查是通過一個(gè)對(duì)大小寫敏感的哈希查找的。查詢和緩存中的查詢即使只有一個(gè)不同畦娄，也不會(huì)匹配緩存結(jié)果又沾。　　　　
（3）如果命中緩存熙卡，那么在但會(huì)結(jié)果前MySQL會(huì)檢查一次用戶權(quán)限杖刷，有權(quán)限則跳過其他步驟直接返回?cái)?shù)據(jù)

4.3 查詢優(yōu)化處理

查詢的生命周期的下一步是將一個(gè)SQL轉(zhuǎn)換成執(zhí)行計(jì)劃，MySQL再依照這個(gè)執(zhí)行計(jì)劃和存儲(chǔ)引擎進(jìn)行交互驳癌。

4.3.1 語法解析器和預(yù)處理

MySQL解析器將使用MySQL語法規(guī)則驗(yàn)證和解析查詢滑燃。例如驗(yàn)證是否使用錯(cuò)誤的關(guān)鍵字、關(guān)鍵字順序颓鲜、引號(hào)前后是否匹配等　　　　　　　預(yù)處理器則根據(jù)一些MySQL 規(guī)則進(jìn)一步解析樹是否合法表窘，例如檢查數(shù)據(jù)表和數(shù)據(jù)列是否存在，解析名字和別名是否有歧義等

4.3.2 查詢優(yōu)化器

一條查詢可以有很多種執(zhí)行方式甜滨，最后都返回相同的結(jié)果乐严。優(yōu)化器的作用就是找到其中最好的執(zhí)行計(jì)劃有很多中原因?qū)е翸ySQL優(yōu)化器選擇錯(cuò)誤的計(jì)劃，如下所示：統(tǒng)計(jì)信息不準(zhǔn)確：MySQL依賴存儲(chǔ)引擎提供的統(tǒng)計(jì)信息來評(píng)估成本衣摩，但是有的存儲(chǔ)引擎提供的信息偏差有點(diǎn)大昂验，例如InnoDB因?yàn)槠銶VCC的架構(gòu)，并不能維護(hù)一個(gè)數(shù)據(jù)表的行數(shù)的精確統(tǒng)計(jì)信息執(zhí)行計(jì)劃中的成本估算不等于實(shí)際的操作成本MySQL的最優(yōu)可能和你想的最優(yōu)不一樣MySQL從不考慮其他并發(fā)執(zhí)行的查詢MySQL也并不是任何時(shí)候都是基于成本的優(yōu)化MySQL不會(huì)考慮不受其控制的操作成本艾扮。例如執(zhí)行存儲(chǔ)過程或者用戶自定義函數(shù)的成本優(yōu)化器有時(shí)間無法估算所有可能的執(zhí)行計(jì)劃　　　　　　
MySQL的查詢優(yōu)化器使用很多策略來生成一個(gè)最優(yōu)的執(zhí)行計(jì)劃既琴。　　　　　　
優(yōu)化策略可以簡(jiǎn)單的分為兩種　　　　　　
靜態(tài)優(yōu)化： 靜態(tài)優(yōu)化可以直接對(duì)解析樹進(jìn)行分析栏渺，并完成優(yōu)化呛梆。例如優(yōu)化器可以通過簡(jiǎn)單的代數(shù)變化將WHERE條件轉(zhuǎn)換成另外一種等價(jià)形式，靜態(tài)優(yōu)化在第一次完成后就一直有效磕诊，即使使用不同的參數(shù)重復(fù)執(zhí)行查詢也不會(huì)變化填物。可以認(rèn)為是一種”編譯時(shí)優(yōu)化“　　　　　　
動(dòng)態(tài)優(yōu)化：和查詢的上下文有關(guān)霎终，也可能和其他因素有關(guān)滞磺，例如WHERE中取值、索引中條目對(duì)應(yīng)的數(shù)據(jù)行數(shù)等莱褒。這需要在每次查詢的時(shí)候重新評(píng)估击困，可以讓那位u是”運(yùn)行時(shí)優(yōu)化“。　　　　　　
MySQL能夠處理的優(yōu)化類型：（部分）重新定義關(guān)聯(lián)表順序?qū)⑼膺B接轉(zhuǎn)化成內(nèi)連接使用等價(jià)變換規(guī)則優(yōu)化COUNT() 阅茶、MIN() 蛛枚、 MAX()預(yù)估并轉(zhuǎn)換為常數(shù)表達(dá)式覆蓋索引掃描子查詢優(yōu)化提前終止查詢等值傳播列表IN()的比較

4.3.3 數(shù)據(jù)和索引的統(tǒng)計(jì)信息

在服務(wù)器層有查詢優(yōu)化器，卻沒有保存數(shù)據(jù)和索引的統(tǒng)計(jì)信息脸哀。統(tǒng)計(jì)信息由存儲(chǔ)引擎實(shí)現(xiàn)蹦浦，不同的存儲(chǔ)引擎可能會(huì)存儲(chǔ)不同的統(tǒng)計(jì)信息，有的引擎根本不存儲(chǔ)任何統(tǒng)計(jì)信息撞蜂，例如Archive引擎盲镶。因?yàn)榉?wù)器層沒有任何統(tǒng)計(jì)信息，所有MySQL查詢優(yōu)化器在生成查詢的執(zhí)行計(jì)劃時(shí)蝌诡，需要向存儲(chǔ)引擎獲取相應(yīng)的統(tǒng)計(jì)信息溉贿，優(yōu)化器根據(jù)這些信息來選擇一個(gè)最優(yōu)的執(zhí)行計(jì)劃。

4.3.4 MySQL如何執(zhí)行關(guān)聯(lián)查詢

MySQL中“關(guān)聯(lián)”認(rèn)為任何一個(gè)查詢都是一次“關(guān)聯(lián)”浦旱，并不僅僅是一個(gè)查詢需要到兩個(gè)表匹配才叫關(guān)聯(lián)宇色。素以在MySQL中，每一個(gè)查詢闽寡，每一個(gè)片段（包括子查詢代兵，甚至于單表的SELECT）都可能是關(guān)聯(lián)。MySQL關(guān)聯(lián)查詢的策略很簡(jiǎn)單：MySQL對(duì)任何關(guān)聯(lián)都執(zhí)行嵌套循環(huán)關(guān)聯(lián)操作爷狈，即MySQL先在要給表中循環(huán)取出單條數(shù)據(jù)植影，然后再嵌套循環(huán)到下一個(gè)表中尋找匹配的行，依次下去涎永，直到找到所有表中匹配的行為止思币。然后根據(jù)各個(gè)表的行，返回查詢中需要的各個(gè)列羡微。

4.3.5 執(zhí)行計(jì)劃

和很多其他關(guān)系數(shù)據(jù)庫不同谷饿，MySQL并不會(huì)生成查詢字節(jié)碼來執(zhí)行查詢。MySQL生成查詢的一顆指令樹妈倔，然后通過存儲(chǔ)引擎執(zhí)行完成這顆樹并返回結(jié)果

4.3.6 關(guān)聯(lián)查詢優(yōu)化器

如果優(yōu)化器給出的并不是最優(yōu)的關(guān)聯(lián)順序博投，這時(shí)可以使用STRAIGHT_JOIN關(guān)鍵字重寫查詢，讓優(yōu)化器按照你認(rèn)為最優(yōu)的關(guān)聯(lián)順序執(zhí)行——不過老實(shí)說盯蝴，人的判斷很難那么精準(zhǔn)毅哗。絕大多數(shù)時(shí)候，優(yōu)化器做出的選擇都比普通人的判斷更精準(zhǔn)捧挺÷敲啵　　　　　　
如果超過N個(gè)表的關(guān)聯(lián)，那么需要檢查N的階乘種關(guān)聯(lián)順序闽烙。我們稱之為所有可能的執(zhí)行計(jì)劃的“搜索空間”翅睛，當(dāng)搜索空間非常大的時(shí)候，優(yōu)化器選擇使用”貪婪“搜索方式查找”最優(yōu)’的關(guān)聯(lián)順序。當(dāng)關(guān)聯(lián)的表超過optimizer_search_depth的限制的時(shí)候捕发，就會(huì)選擇“貪婪”搜索模式了疏旨。

4.3.7 排序優(yōu)化

排序優(yōu)化：無論如何排序都是一個(gè)成本很高的操作，所以從性能角度考慮爬骤，應(yīng)盡可能避免排序或者盡可能避免對(duì)大量數(shù)據(jù)進(jìn)行排序充石。盡量通過索引進(jìn)行排序莫换。當(dāng)不能使用索引生成排序結(jié)果的時(shí)候霞玄，MySQL需要自己　進(jìn)行排序，如果數(shù)據(jù)量小則在內(nèi)存中進(jìn)行拉岁，如果數(shù)量大則需要使用磁盤坷剧，不過MySQL將這個(gè)過程統(tǒng)一稱為文件排序，即使完全是內(nèi)存排序不需要任何磁盤文件時(shí)也是如此喊暖。
MySQL有如下兩種排序算法：
a. 兩次傳輸排序(舊版本使用)：讀取行指針和需要排序的字段惫企，對(duì)其進(jìn)行排序，然后再根據(jù)排序結(jié)果讀取所需要的數(shù)據(jù)行陵叽。需要進(jìn)行兩次傳輸狞尔，即需要從數(shù)據(jù)表中讀取兩次數(shù)據(jù)，第二次讀取數(shù)據(jù)的時(shí)候巩掺，因?yàn)槭亲x　 取排序列進(jìn)行排序后的所有記錄偏序。這回產(chǎn)生大量的隨機(jī)IO∨痔妫　　
b. 單次傳輸排序(新版本使用)：先讀取查詢所需要的所有列研儒，然后在根據(jù)給定列進(jìn)行排序，最后直接返回排序結(jié)果独令。效率更高端朵，但占用內(nèi)存更大。如果查詢中有LIMIT的話燃箭，LIMIT也會(huì)在排序之后應(yīng)用的冲呢，所以即使需要返回較少的數(shù)據(jù)，臨時(shí)表和需要排序的數(shù)據(jù)量仍然后非常大招狸。貌似5.6版本有所改進(jìn)敬拓，會(huì)先拋棄不滿足條件的記錄，然后再進(jìn)行排序瓢颅。

4.4 查詢執(zhí)行引擎

在解析和優(yōu)化階段恩尾，MySQL將生成查詢對(duì)應(yīng)的執(zhí)行計(jì)劃，MySQL的查詢執(zhí)行引擎則根據(jù)這個(gè)執(zhí)行計(jì)劃來完成整個(gè)查詢挽懦。這里執(zhí)行計(jì)劃是一個(gè)數(shù)據(jù)結(jié)構(gòu)翰意，而不是和很多其他的關(guān)系型數(shù)據(jù)庫那樣會(huì)生成對(duì)應(yīng)的字節(jié)碼。

4.5 返回結(jié)果給客戶端

即使查詢不需要返回結(jié)果集給客戶端，MySQL仍然會(huì)返回這個(gè)查詢的一些信息冀偶，如查詢影響到的行數(shù)醒第。如果查詢可以被緩存，那么MySQL在這個(gè)階段也會(huì)將結(jié)果存放到緩存中进鸠〕砺　　　　
MySQL將結(jié)果集返回客戶端是一個(gè)增量、逐步返回的過程客年。開始生成第一條結(jié)果時(shí)霞幅，MySQL就開始向客戶端逐步返回結(jié)果集了。

5. MySQL查詢優(yōu)化器的局限性：

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

MySQL的子查詢實(shí)現(xiàn)非常糟糕(5.6版本以后有改進(jìn))量瓜。最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語句司恳。? ? ? ? ?
1). 因?yàn)槭褂肐N()加子查詢，性能經(jīng)常會(huì)非常糟绍傲，所以通常建議使用EXISTS()等效的改寫查詢來獲取更好的效率扔傅。? ? ? ? ?
2). 一般建議使用左外連接(LEFT OUTER JOIN)代替子查詢(?)。? ??

5.2 UNION的限制：

MySQL無法將限制條件從外層"下推"到內(nèi)層烫饼，這使得原本能夠限制部分返回結(jié)果的條件無法應(yīng)用到內(nèi)層查詢的優(yōu)化上猎塞。 例如如果希望UNION的各個(gè)子句能夠根據(jù)LIMIT只去部分結(jié)果集，或者希望能夠先排好序再合并結(jié)果集的話杠纵，就需要在UNION的各個(gè)子句中分別使用這些語句荠耽。

 (SELECT first_name,last_name FROM sakila.actor ORDER BY last_name) UNION ALL (SELECT first_name ,last_name FROM sakila.customer ORDER BY last_name) LIMIT 20;

優(yōu)化后：

(SELECT first_name,last_name FROM sakila.actor ORDER BY last_name LIMIT 20) UNION ALL (SELECT first_name ,last_name FROM sakila.customer ORDER BY last_name LIMIT 20) LIMIT 20;

5.3 當(dāng)WHERE子句包含多個(gè)復(fù)雜條件的時(shí)候

MySQL能夠訪問單個(gè)表的多個(gè)索引以合并和交叉過濾的方式來定位需要查找的行。? ?

5.4 等值查詢：

某些時(shí)候淡诗，等值查詢會(huì)帶來一些意想不到額外消耗骇塘。例如：有一個(gè)非常大的IN()列表，而MySQ優(yōu)化器發(fā)現(xiàn)存在WHERE韩容、ON或者USING的子句款违。? ??

5.5 并行執(zhí)行：

MySQL無法利用多核特性來并行執(zhí)行查詢(貌似5.6以后有改進(jìn))。? ?

5.6 哈希關(guān)聯(lián)：

MySQL不支持哈希關(guān)聯(lián)群凶。? ?

5.7 松散索引掃描：

MySQL并不支持松散索引掃描插爹，也就無法按照不連續(xù)的方式掃描一個(gè)索引。通常请梢，MySQL的索引掃描需要先定義一個(gè)起點(diǎn)和終點(diǎn)赠尾，即使需要的數(shù)據(jù)只是這段索引中的很少幾個(gè)，MySQL仍需掃描這段索引中? ? ? ? ? 的每一個(gè)條目毅弧。? ?

5.8 最大值和最小值優(yōu)化：

對(duì)于MIN()和MAX()查詢气嫁，MySQL的優(yōu)化做的并不好。例如 SELECT MIN(actor_id) FROM sakila.actor WHERE first_name='PENELOPE'? 因?yàn)閒irst_name上沒有索引够坐，所以會(huì)進(jìn)行全表掃描寸宵。如果MySQL能夠進(jìn)行主鍵掃描崖面，那么理論上，當(dāng)MySQL讀到第一個(gè)滿足條件的記錄的時(shí)候梯影，就是我們需要的最小值巫员，因?yàn)橹麈I是嚴(yán)格按照actor_id大小字段排序的。;一個(gè)曲線優(yōu)化的辦法是移除MIN(IMIT來將查詢重寫甲棍。

5.9 在同一個(gè)表上查詢和更新：

MySQL不允許對(duì)同一張表同時(shí)進(jìn)行查詢和更新简识。

6. 查詢優(yōu)化器的提示(hint)：

如果對(duì)優(yōu)化器選擇的執(zhí)行計(jì)劃不滿意，可以使用優(yōu)化器提供的幾個(gè)提示(hint)來控制最終的執(zhí)行計(jì)劃感猛。

7. 優(yōu)化特定類型的查詢? ??

7.1 優(yōu)化COUNT()查詢;

1). COUNT()是一個(gè)特殊的函數(shù)七扰，有兩種非常不同的作用：它可以統(tǒng)計(jì)某個(gè)列值的數(shù)量，也可以統(tǒng)計(jì)行數(shù)唱遭。在統(tǒng)計(jì)列值的時(shí)候要求列值是非空的(不統(tǒng)計(jì)NULL)戳寸。如果COUNT()的括號(hào)中指定了列或者列的表達(dá)式，則統(tǒng)計(jì)的就是這個(gè)表達(dá)式有值的結(jié)果數(shù)拷泽。最簡(jiǎn)單的就是我們使用count( * ) 的時(shí)候，這種情況下通配符 * 并不會(huì)向我們猜想的那樣擴(kuò)展所有的行袖瞻，實(shí)際上司致，它會(huì)忽略所有的值而直接統(tǒng)計(jì)所有的行數(shù)。
2). 使用近似值：有時(shí)候某些業(yè)務(wù)場(chǎng)景并不要求完全精確的COUNT值聋迎，此時(shí)可以用近似值來代替脂矫。? ? ? ? ?
3). 更復(fù)雜的優(yōu)化：覆蓋索引，增加匯總表等霉晕。? ??

7.2 優(yōu)化關(guān)聯(lián)查詢：

1). 確保ON或者USING子句中的列上有索引庭再。在創(chuàng)建索引的時(shí)候就要考慮到關(guān)聯(lián)的順序。當(dāng)表A和表B用到列C關(guān)聯(lián)的時(shí)候牺堰，如果優(yōu)化器關(guān)聯(lián)順序是B拄轻、A，那就不需要在B表的對(duì)應(yīng)列上建立索引伟葫。沒有用到的索引只會(huì)帶來額外的負(fù)擔(dān)恨搓。一般來說，除非有其他理由筏养，否則只需要在關(guān)聯(lián)順序中的第二個(gè)表的相應(yīng)列上創(chuàng)建索引斧抱。
2). 確保任何的GROUP BY 和ORDER BY中的表達(dá)式只涉及到一個(gè)表中的列。這樣MySQL才有可能使用索引來優(yōu)化這個(gè)過程

7.3 優(yōu)化子查詢：

關(guān)于優(yōu)化子查詢我們給出的最重要的優(yōu)化建議就是盡可能使用關(guān)聯(lián)查詢代替渐溶，至少當(dāng)前MySQL版本需要這樣辉浦。

7.4 優(yōu)化GROUP BY和DISTINCT：

1). 它們都可以使用索引來優(yōu)化，這也是最有效的方法茎辐。
2). 在MySQL中宪郊，當(dāng)無法使用索引的時(shí)候眉睹，GROUP BY使用兩種策略來完成：使用臨時(shí)表或文件排序來做分組。對(duì)于任何查詢語句废膘，這兩種策略的性能都有可以提升的地方竹海。可以通過使用提示SQL_BIG_RESULT和SQL_SMALL_RESULT來讓優(yōu)化器按你希望的方式運(yùn)行丐黄。
3). 如果需要對(duì)關(guān)聯(lián)查詢分組(GROUP BY)斋配，并且是按照查找表中的某個(gè)列進(jìn)行分組，那么通常采用查找表的標(biāo)識(shí)列分組的效率比其他列更高灌闺。
4). 如果沒有通過ORDER BY子句顯式地指定排序列艰争，當(dāng)查詢使用GROUP BY 子句的時(shí)候，結(jié)果集會(huì)自動(dòng)按照分組的列進(jìn)行排序桂对。如果不關(guān)心結(jié)果集的順序甩卓，而這中默認(rèn)排序又導(dǎo)致了需要文件排序，則可以使用ORDER BY NULL蕉斜，讓MySQL文件不再進(jìn)行排序逾柿。也可以在GROUP BY子句中直接使用DESC或者ASC關(guān)鍵字，使分組的結(jié)果集按照需要的方向排序宅此。
;5). 優(yōu)化GROUP BY WITH ROLLUP：分組查詢的一個(gè)變種思想就是要求MySQL對(duì)返回的分組結(jié)果再做一次超級(jí)聚合机错。最好的辦法盡可能的將WITH ROLLUP 功能轉(zhuǎn)移到應(yīng)用程序中處理。

7.5 優(yōu)化LIMIT分頁：

1). 使用索引? ? ? ? ?
2). 要優(yōu)化這種查詢父腕，要么是在頁面中限制分頁的數(shù)量弱匪，要么是優(yōu)化大偏移量的性能。? ? ? ? ??
3). 盡肯能的使用索引覆蓋? ? ? ? ?
4). 延遲關(guān)聯(lián)? ? ? ? ??
5). 有時(shí)候也可以將LIMIT查詢轉(zhuǎn)換為已知位置的查詢璧亮，讓MySQL通過范圍掃描找到對(duì)應(yīng)的結(jié)果萧诫。? ? ? ? ??
6). 其他優(yōu)化辦法還包括使用預(yù)先計(jì)算的匯總表，或者關(guān)聯(lián)一個(gè)冗余表枝嘶，冗余表只包含主鍵列和需要做排序的數(shù)據(jù)列帘饶。? ?

7.6 優(yōu)化SQL_CALC_FOUND_ROWS：

分頁的時(shí)候，另一個(gè)常用的技巧是在LIMIT語句中加上SQL_CALC_FOUND_ROWS提示(hint)躬络，這樣就可以獲得去掉LIMIT以滿足條件的行數(shù)尖奔，因此可以作為分頁的總數(shù).用業(yè)務(wù)的手段解決：下一頁，獲取更多數(shù)據(jù)等穷当。? ?

7.7 優(yōu)化UNION查詢：

1). MySQL總是通過創(chuàng)建填充臨時(shí)表的方式來執(zhí)行UNION查詢提茁。因此很多優(yōu)化策略在UNION查詢中都沒法很好地使用。經(jīng)常需要手工地將WHERE,LIMIT,ORDER BY等子句"下推"到UNION的各個(gè)子查詢中馁菜，以便優(yōu)化器可以充分利用這些條件進(jìn)行優(yōu)化茴扁。
2). 除非確實(shí)需要服務(wù)器消除重復(fù)的行，否則就一定要使用UNION ALL汪疮，這一點(diǎn)很重要峭火。如果沒有ALL關(guān)鍵字毁习，MySQL會(huì)給臨時(shí)表加上DISTINCT選項(xiàng)，這回導(dǎo)致對(duì)臨時(shí)表做唯一性檢查卖丸。這樣做的代價(jià)非常高纺且，即使有ALL關(guān)鍵字，MySQL仍然會(huì)使用臨時(shí)表存儲(chǔ)結(jié)果稍浆。事實(shí)上载碌，MySQL總是經(jīng)結(jié)果放入臨時(shí)表，然后再讀出衅枫，再返回給客戶端嫁艇。

7.8 靜態(tài)查詢分析：

Percona Toolkit中的pt-query-advisor 能夠解析查詢?nèi)罩尽⒎治霾樵兡Ｊ较伊茫缓笤俳o出所有可能存在的潛在問題的查詢步咪，并給出足夠詳細(xì)的建議。這像是給MySQL所有的查詢做一次全面的健康檢查益楼，它能檢測(cè)出很多問題猾漫。