一、慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問
-
查詢性能低下最基本的原因是訪問數(shù)據(jù)太多。大部分性能低下的查詢都可以通過減少訪問的數(shù)據(jù)量的方式進(jìn)行優(yōu)化。通過下面兩個(gè)步驟來分析總是有效的：
- 確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)。
- 確認(rèn)MySQL服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行。
取出全部列（SELECT *）操作，會(huì)讓優(yōu)化器無法完成索引覆蓋掃描這類優(yōu)化，還會(huì)為服務(wù)器帶來額外的IO、內(nèi)存和CPU的消耗。
-
對(duì)于MySQL，最簡(jiǎn)單的衡量查詢開銷的三個(gè)指標(biāo)是：
- 響應(yīng)時(shí)間
- 掃描的行數(shù)
- 返回的行數(shù)
“Using where” 表示MySQL將通過WHERE條件來篩選存儲(chǔ)引擎返回的記錄。
-
一般MySQL能夠使用如下兩種方式應(yīng)用WHERE條件，從好到壞依次是：
在索引中使用where條件來過濾不匹配的記錄（extra列顯示Using index）。這是存儲(chǔ)引擎層完成的。
從數(shù)據(jù)表中返回?cái)?shù)據(jù)，然后過濾不滿足條件的記錄（在extra列中出現(xiàn)Using where）。這在MySQL服務(wù)層完成的，MySQL需要先從數(shù)據(jù)表中讀出記錄然后過濾。
-
如果發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)，但只返回少數(shù)的行，那么通常可以嘗試如下方式進(jìn)行優(yōu)化：
- 使用索引覆蓋掃描。即，把所有需要用的列都放到索引中，這樣存儲(chǔ)引擎無需回表即可獲取對(duì)應(yīng)行的數(shù)據(jù)。
- 改變庫(kù)表結(jié)構(gòu)。如添加匯總表等。
- 優(yōu)化SQL語(yǔ)句。
二、重構(gòu)查詢的方式
-
很多高性能的應(yīng)用都會(huì)對(duì)關(guān)聯(lián)查詢進(jìn)行分解。簡(jiǎn)單地，可以對(duì)每一個(gè)表進(jìn)行一次單表查詢，然后將結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)。將關(guān)聯(lián)查詢進(jìn)行分解會(huì)有如下優(yōu)勢(shì)：
- 讓MySQL緩存更高效
- 將查詢分解后，執(zhí)行單個(gè)查詢可以減少鎖的競(jìng)爭(zhēng)。
- 在應(yīng)用層做關(guān)聯(lián)，可以更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分，更容易做到高性能和可擴(kuò)展。
- 查詢本身效率也會(huì)提升。
- 可以減少冗余記錄的查詢。
三、查詢執(zhí)行的基礎(chǔ)
-
執(zhí)行路徑：
image.png- 客戶端發(fā)送一條查詢給服務(wù)器
- 服務(wù)器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲(chǔ)在緩存中的結(jié)果。否則進(jìn)入下一階段
- 服務(wù)器進(jìn)行SQL解析、預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃
- MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的API來執(zhí)行查詢
- 將結(jié)果返回給客戶端
MySQL客戶端和服務(wù)端之間的通信協(xié)議是“半雙工”的，這意味著，在任何一個(gè)時(shí)刻，要么是由服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是由客戶端向服務(wù)端發(fā)送數(shù)據(jù)，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生。
【關(guān)注點(diǎn)】很多數(shù)據(jù)庫(kù)系統(tǒng)中，IN語(yǔ)句完全等同于多個(gè)OR條件的子句。但是在MySQL中這點(diǎn)是不成立的。MySQL將IN()列表中的數(shù)據(jù)先進(jìn)行排序，然后通過將數(shù)據(jù)行中對(duì)應(yīng)的列在排好序的列表中進(jìn)行二分查找的方式來確定值是否滿足條件。這是一個(gè)O(logn)復(fù)雜度的操作。若等價(jià)的轉(zhuǎn)換成OR查詢的復(fù)雜度為O(n)，因此對(duì)于IN列表中有大量取值的時(shí)候，MySQL的處理速度將會(huì)更快。
MySQL認(rèn)為任何一個(gè)查詢都是一次“關(guān)聯(lián)”，并不僅僅是一個(gè)查詢需要到兩個(gè)表匹配才叫關(guān)聯(lián)，所以在MySQL中，每一個(gè)查詢、每一個(gè)片段（包括子查詢、甚至于單表的SELECT）都可能是關(guān)聯(lián)。
對(duì)于Union查詢，MySQL先將一系列的單表查詢結(jié)果放到一個(gè)臨時(shí)表中，然后再重新讀出臨時(shí)表數(shù)據(jù)來完成Union查詢。（注意，臨時(shí)表是沒有索引的，所以后續(xù)對(duì)臨時(shí)表的操作都是全表掃描）
MySQL對(duì)于任何關(guān)聯(lián)都執(zhí)行嵌套循環(huán)關(guān)聯(lián)操作。即，多個(gè)for循環(huán)嵌套（回顧第五章最后的轉(zhuǎn)載）。
-
MySQL總是從一個(gè)表開始一直嵌套循環(huán)、回溯完成所有表的關(guān)聯(lián)，所以MySQL的執(zhí)行計(jì)劃總是如下所示，是一個(gè)左側(cè)深度優(yōu)先的樹。
image.png 無論如何，排序都是一個(gè)成本很高的操作，所以從性能的角度講，應(yīng)該盡量避免排序或者盡可能避免對(duì)大量數(shù)據(jù)進(jìn)行排序。
當(dāng)不能使用索引生成排序結(jié)果的時(shí)候，MySQL需要自己進(jìn)行排序，如果數(shù)量小則在內(nèi)存中進(jìn)行，如果數(shù)量大則需要使用磁盤，不過MySQL將這個(gè)過程統(tǒng)一成為文件排序（filesort）。
如果需要排序的數(shù)據(jù)量小于“排序緩沖區(qū)”，MySQL將使用內(nèi)存進(jìn)行“快速排序”操作。如果內(nèi)存不夠排序，那么MySQL會(huì)先將數(shù)據(jù)進(jìn)行分塊，對(duì)每個(gè)獨(dú)立的塊內(nèi)數(shù)據(jù)使用快速排序，并將各個(gè)塊的排序結(jié)果存放在磁盤上，然后將各個(gè)排好序的塊進(jìn)行合并，最后返回排序的結(jié)果。
-
在關(guān)聯(lián)查詢的時(shí)候如果需要排序，MySQL會(huì)分兩種情況來處理這樣的文件排序：
- 如果order by 子句的所有列都來自關(guān)聯(lián)的第一個(gè)表，那么MySQL在關(guān)聯(lián)處理第一個(gè)表的時(shí)候就會(huì)進(jìn)行文件排序。此時(shí)通過explain在extra列將會(huì)看到“Using filesort”。
- 此外的其他情況，MySQL都會(huì)先將關(guān)聯(lián)的結(jié)果存放到一個(gè)臨時(shí)表中，然后在所有的關(guān)聯(lián)都結(jié)束后，再進(jìn)行文件排序。此時(shí)通過explain在extra列中將會(huì)看到“Using temporary; Using filesort”。【Using temporary表示使用了臨時(shí)表】
在MySQL5.6以后的版本中，MySQL對(duì)只需要返回部分排序結(jié)果的查詢進(jìn)行了優(yōu)化，如果使用了limit子句，那么MySQL不再對(duì)所有的結(jié)果進(jìn)行排序，而是根據(jù)實(shí)際情況，選擇拋棄不滿足條件的結(jié)果，然后再進(jìn)行排序。
-
一個(gè)容易產(chǎn)生的誤解是：MyISAM的count()函數(shù)總是非?？?，不過這是有前提條件的：
- 只有沒有任何where條件的count(*)才非?？?，因此此時(shí)無需實(shí)際去去計(jì)算表的行數(shù)，可以直接利用存儲(chǔ)引擎的特性直接獲取這個(gè)值。
- 當(dāng)存在where子句的時(shí)候，MyISAM的count()和其他存儲(chǔ)引擎沒有任何不同，就不再有神話版的速度了。
-
優(yōu)化關(guān)聯(lián)查詢：
確保ON或者USING子句中的列上有索引。
確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個(gè)表中的列，這樣MySQL才有可能使用索引來優(yōu)化這個(gè)過程。
當(dāng)升級(jí)MySQL的時(shí)候需要注意關(guān)聯(lián)語(yǔ)法、運(yùn)算符優(yōu)先級(jí)等其他可能會(huì)發(fā)生變化的地方。因?yàn)橐郧笆瞧胀P(guān)聯(lián)的地方可能會(huì)變成笛卡爾積，不同類型的關(guān)聯(lián)可能會(huì)生成不同的結(jié)果。
優(yōu)化group by和distinct。MySQL優(yōu)化器會(huì)在內(nèi)部處理的時(shí)候相互轉(zhuǎn)化這兩類查詢。它們都可以使用索引來優(yōu)化，這也是最有效的方式。
當(dāng)無法使用索引的時(shí)候，group by使用兩種策略來完成：使用臨時(shí)表或者使用文件排序來做分組。
如果需要對(duì)關(guān)聯(lián)查詢做分組，并且是按照某個(gè)查找表中的某個(gè)列進(jìn)行分組，那么通常 采用查找表的標(biāo)識(shí)列分組的效率會(huì)比其他列更高。（參考第五章轉(zhuǎn)載部分）
不是所有的關(guān)聯(lián)語(yǔ)句的分組查詢都可以寫成在select中直接使用非分組列的形式的?？梢栽诜?wù)器上設(shè)置SQL_MODE來禁止這樣的寫法。但一定要清楚，select后面出現(xiàn)的非分組列一定是直接依賴分組列，并且在每個(gè)分組內(nèi)的值唯一。
19.單純使用 limit 10000,20 這樣的查詢，MySQL在執(zhí)行時(shí)需要掃描10020條記錄，然后只返回最后20條。因此偏移量非常大的時(shí)候，性能很低。因此在查詢條件中盡量使用索引列來減少掃描的行數(shù)。
MySQL總是通過創(chuàng)建并填充臨時(shí)表的方式來執(zhí)行union查詢。（[關(guān)于union查詢]https://www.w3school.com.cn/sql/sql_union.asp
）
