MySQL優(yōu)化一般是需要索引優(yōu)化娶靡、查詢優(yōu)化璧微、庫表結(jié)構(gòu)優(yōu)化三駕馬車齊頭并進(jìn)。
本章節(jié)開始講查詢優(yōu)化痒留。

一贯涎、為什么查詢速度會(huì)慢

可以把查詢當(dāng)作一個(gè)任務(wù)听哭，它由一系列子任務(wù)組成，每個(gè)子任務(wù)都會(huì)消耗一定的時(shí)間塘雳。如果要優(yōu)化查詢陆盘，實(shí)際上是優(yōu)化其子任務(wù)，要么消除其中一些子任務(wù)败明，要么減少子任務(wù)的執(zhí)行次數(shù)隘马，要么讓子任務(wù)運(yùn)行得更快。
MySQL在執(zhí)行查詢的時(shí)候有哪些子任務(wù)妻顶，這個(gè)是有一定的方法進(jìn)行剖析的酸员，具體方法下回單獨(dú)拿一個(gè)章節(jié)來分析。
通常來說讳嘱，查詢的生命周期大致可以按照順序來看：從客戶端幔嗦，到服務(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í)間患朱。
在每一個(gè)消耗大量時(shí)間的查詢案例中鲁僚，都能看到一些不必要的額外操作、某些操作被額外地重復(fù)了很多次裁厅、某些操作執(zhí)行得太慢等冰沙。優(yōu)化查詢的目的就是減少和消除這些操作所花費(fèi)的時(shí)間。

一执虹、首選要優(yōu)化數(shù)據(jù)訪問

查詢性能底下最基本的原因是訪問的數(shù)據(jù)太多拓挥。所以，對(duì)于低效的查詢袋励，一般通過兩個(gè)步驟來分析：

1. 確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)侥啤。這通常意味著訪問了太多的行，但有時(shí)候也可能是訪問了太多的列茬故。
2. 確認(rèn)MySQL服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行盖灸。

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

在訪問數(shù)據(jù)庫時(shí)磺芭，應(yīng)該只請(qǐng)求需要的行和列赁炎，請(qǐng)求多余的行和列會(huì)消耗MySQL服務(wù)器的CPU和內(nèi)存資源，并增加網(wǎng)絡(luò)開銷钾腺。
1甘邀、在處理分頁時(shí)，應(yīng)該使用LIMIT限制MySQL只返回需要的數(shù)據(jù)垮庐，而不是向應(yīng)用程序返回全部數(shù)據(jù)后，再由應(yīng)用程序過濾不需要的行坞琴。
2哨查、多表關(guān)聯(lián)時(shí)，或獲取單表數(shù)據(jù)時(shí)剧辐，盡量避免不加思考地使用SELECT *
3寒亥、當(dāng)一些數(shù)據(jù)被多次使用時(shí)可以考慮將數(shù)據(jù)緩存起來邮府，避免每次使用都要到MySQL查詢。

1.2溉奕、MySQL是否在掃描額外的記錄褂傀，應(yīng)該讓MySQL使用最合適的方式查詢數(shù)據(jù)

對(duì)于MySQL，最簡單的衡量查詢開銷有三個(gè)指標(biāo)：響應(yīng)時(shí)間加勤、掃描的行數(shù)和返回的行數(shù)仙辟。這里主要考慮提高掃描的方式，即查詢數(shù)據(jù)的方式鳄梅。
查詢數(shù)據(jù)的方式有全表掃描叠国、索引掃描、范圍掃描戴尸、唯一索引查詢粟焊、常數(shù)引用等。這些查詢方式孙蒙，速度從慢到快项棠，掃描的行數(shù)也是從多到少】媛停可以通過EXPLAIN語句中的type列反應(yīng)查詢采用的是哪種方式香追。
通常可以通過添加合適的索引改善查詢數(shù)據(jù)的方式浑测，使其盡可能減少掃描的數(shù)據(jù)行翅阵，加快查詢速度。
例如迁央，當(dāng)發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)行但只返回少數(shù)的行掷匠，那么可以考慮使用覆蓋索引，即把所有需要用到的列都放到索引中岖圈。這樣存儲(chǔ)引擎無須回表獲取對(duì)應(yīng)行就可以返回結(jié)果了讹语。

二、重構(gòu)查詢的方法

設(shè)計(jì)查詢的時(shí)候需要考慮是否需要把一個(gè)復(fù)雜的查詢分成多個(gè)簡單的查詢蜂科。在我的印象中顽决，曾經(jīng)無數(shù)次聽到一個(gè)經(jīng)驗(yàn)法則：可以在數(shù)據(jù)庫中做的事不要放在應(yīng)用程序中，數(shù)據(jù)庫比我們想象的要厲害的多导匣。這個(gè)經(jīng)驗(yàn)法則是在華夏基金使用Oracle編寫SQL時(shí)一位Oracle牛人告訴我的才菠，后來我把它使用到MySQL上，真是吃盡苦頭贡定。
當(dāng)然這其中的原因有Oracle和MySQL原本就不是一樣的處理邏輯赋访，并且現(xiàn)在的網(wǎng)絡(luò)通信、查詢解析和優(yōu)化的代價(jià)并沒有以前那么高啦。再次說明蚓耽，經(jīng)驗(yàn)法則有在某種特定籠子里才有效渠牲。
分解復(fù)雜的查詢：
可以將一個(gè)大查詢切分成多個(gè)小查詢執(zhí)行，每個(gè)小查詢只完成整個(gè)查詢?nèi)蝿?wù)的一小部分步悠，每次只返回一小部分結(jié)果签杈。
刪除舊的數(shù)據(jù)是一個(gè)很好的例子。
如果只用一條語句一次性執(zhí)行一個(gè)大的刪除操作鼎兽，則可能需要一次鎖住很多數(shù)據(jù)答姥，占滿整個(gè)事務(wù)日志，耗盡系統(tǒng)資源接奈、阻塞很多小的但重要的查詢踢涌。將一個(gè)大的刪除操作分解成多個(gè)較小的刪除操作可以將服務(wù)器上原本一次性的壓力分散到多次操作上，盡可能小地影響MySQL性能序宦，減少刪除時(shí)鎖的等待時(shí)間睁壁，同時(shí)也減少了MySQL主從復(fù)制的延遲。這個(gè)方法我一直在用互捌。
另一個(gè)例子是分解關(guān)聯(lián)查詢潘明，即對(duì)每個(gè)要關(guān)聯(lián)的表進(jìn)行單表查詢，然后將結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)秕噪。我在之前一家公司和一位在阿里待過很多年的同事一起編碼時(shí)钳降，他就是這么干的。后來我在心中默默地鄙視著他腌巾，因?yàn)槲倚睦镉羞@么一個(gè)經(jīng)驗(yàn)法則（可以在數(shù)據(jù)庫中做的事不要放在應(yīng)用程序中遂填，數(shù)據(jù)庫比我們想象的要厲害的多），并且我在行動(dòng)上也是保持能用一個(gè)SQL解決的事絕對(duì)不會(huì)用兩個(gè)SQL澈蝙。這么做當(dāng)然處理經(jīng)驗(yàn)法則的原因之外還有一個(gè)原因是：獲取數(shù)據(jù)的邏輯盡量與業(yè)務(wù)代碼分離吓坚，這樣以后在切換數(shù)據(jù)庫時(shí)也很方便。實(shí)際上是這樣嗎灯荧？未必啊礁击。那次的無知讓我吃盡苦頭啊，后來因?yàn)镾QL的性能問題再把我寫的大部分SQL進(jìn)行分解逗载。
用分解關(guān)聯(lián)查詢的方式重構(gòu)查詢有如下的優(yōu)勢(shì)：

1. 讓緩存的效率更高哆窿。許多應(yīng)用程序可以方便地緩存單表查詢對(duì)應(yīng)的結(jié)果對(duì)象。
2. 將查詢分解后厉斟，執(zhí)行單個(gè)查詢可以減少鎖的競(jìng)爭挚躯。
3. 在應(yīng)用層做關(guān)聯(lián)，可以更容易對(duì)數(shù)據(jù)庫進(jìn)行拆分擦秽，更容易做到高性能和可擴(kuò)展秧均。
4. 查詢本身效率也可能會(huì)有所提升食侮。
5. 可以減少冗余記錄的查詢。在應(yīng)用層做關(guān)聯(lián)查詢目胡，意味著對(duì)于某條記錄應(yīng)用只需要查詢一次，而在數(shù)據(jù)庫中做關(guān)聯(lián)查詢链快，則可能需要重復(fù)地訪問一部分?jǐn)?shù)據(jù)誉己。從這點(diǎn)看，這樣的重構(gòu)還可能會(huì)減少網(wǎng)絡(luò)和內(nèi)存的消耗域蜗。
6. 更進(jìn)一步巨双，這樣做相當(dāng)于在應(yīng)用中實(shí)現(xiàn)了哈希關(guān)聯(lián)，而不是使用MySQL的嵌套循環(huán)關(guān)聯(lián)霉祸。某些場(chǎng)景哈希關(guān)聯(lián)的效率要高很多筑累。

三、查詢執(zhí)行的基礎(chǔ)

當(dāng)向MySQL發(fā)送一個(gè)請(qǐng)求之后丝蹭，MySQL到底做了些什么慢宗？如下圖所示：

查詢執(zhí)行路徑

1. 客戶端發(fā)送一條查詢給服務(wù)器。
2. 服務(wù)器先檢查查詢緩存奔穿，如果命中了緩存镜沽，則立刻返回存儲(chǔ)在緩存中的結(jié)果。否則進(jìn)行下一階段贱田。
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é)果返回給客戶端耗拓。

上述的每一步都比想象的復(fù)雜拇颅。我們?cè)谙乱徽鹿?jié)來進(jìn)行分析。