合理的表結(jié)構(gòu)、索引對于高性能查詢來說是必不可少的。但是還需要合理的設(shè)計查詢。如果查詢寫得很糟糕，那么表結(jié)構(gòu)再合理、索引再合適，也無法實現(xiàn)高性能。
因此高性能的查詢依賴于這三點：查詢優(yōu)化、索引優(yōu)化、表結(jié)構(gòu)優(yōu)化。
為什么查詢速度會慢
如果把查詢看作是一個任務(wù)，那么它由一系列子任務(wù)組成，每個子任務(wù)都會消耗一定的時間。如果要優(yōu)化查詢，實際上要優(yōu)化其子任務(wù)，要么消除其中一些子任務(wù)，要么減少子任務(wù)的執(zhí)行次數(shù)，要么讓子任務(wù)運行得更快。
查詢的生命周期：從客戶端，到服務(wù)器，然后在服務(wù)器上進(jìn)行解析，生成執(zhí)行計劃，執(zhí)行（包括調(diào)用存儲引擎及調(diào)用后的排序、分組等數(shù)據(jù)處理），并返回結(jié)果給客戶端。
在完成這些任務(wù)時，查詢需要在不同地方花費時間，包括網(wǎng)絡(luò)，CPU 計算，生成統(tǒng)計信息和執(zhí)行計劃、鎖等待等操作。
優(yōu)化數(shù)據(jù)訪問
查詢性能低下的原因可能是以下兩種：
- 應(yīng)用程序可能檢索了超過需要的數(shù)據(jù)（訪問了太多的行或者太多的列）。
- MySQL 服務(wù)器層可能在分析大量超過需要的數(shù)據(jù)行。
請求了不需要的數(shù)據(jù)
- 應(yīng)用程序中做分頁查詢。
- 只查找確實需要的列，不用 SELECT *。
- 不要做重復(fù)查詢。
MySQL 在掃描額外的記錄
在確定了查詢只返回需要的數(shù)據(jù)以后，接下來應(yīng)該看查詢?yōu)榱朔祷亟Y(jié)果是否掃描了過多的數(shù)據(jù)。最簡單的衡量查詢開銷的三個指標(biāo)如下：
- 響應(yīng)時間
- 掃描的行數(shù)
- 返回的行數(shù)
一般 MySQL 使用如下三種方式應(yīng)用 WHERE 條件，從好到壞依次為：
- 在索引中使用 WHERE 條件來過濾不匹配的記錄。這是在存儲引擎層完成的。
- 使用覆蓋索引返回記錄，直接從索引中過濾不需要的記錄并返回命中的結(jié)果。這是在 MySQL 服務(wù)器層完成的，但無需再回表查詢記錄。
- 從數(shù)據(jù)表中返回數(shù)據(jù)，然后過濾不滿足條件的記錄。這是在 MySQL 服務(wù)器層完成的，MySQL 需要先從數(shù)據(jù)表讀出記錄然后過濾。
如果發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)但只返回少數(shù)的行，可以這么做：
- 使用覆蓋索引掃描
- 改變庫表結(jié)構(gòu)。例如使用單獨的匯總表。
- 重寫復(fù)雜的查詢。
重構(gòu)查詢的方式
一個復(fù)雜查詢還是多個簡單查詢
一般情況下，能用一個查詢解決的事就不要用多個查詢。因為每次查詢都有網(wǎng)絡(luò)通信、查詢解析和優(yōu)化的過程。但是也有例外。因為 MySQL 從設(shè)計上讓連接和斷開連接都很輕量級，在返回一個小的查詢結(jié)果方面很高效。
切分查詢
比如需要將某張表的全部數(shù)據(jù)做DML操作，可以分多次進(jìn)行。防止一次鎖住很多數(shù)據(jù)、占滿整個事務(wù)日志、耗盡系統(tǒng)資源、阻塞很多小的但重要的查詢。
分解關(guān)聯(lián)查詢
將一個關(guān)聯(lián)查詢分解為多個查詢的優(yōu)勢：
- 讓緩存效率更高
- 減少鎖的競爭
- 便于對數(shù)據(jù)庫進(jìn)行拆分
- 查詢本身效率可能會有所提高，如用 in（）代替關(guān)聯(lián)查詢，可以讓MySQL 按照 ID 順序查詢，這可能比隨機的關(guān)聯(lián)要更高效。
- 可以減少冗余記錄的查詢。應(yīng)用層做關(guān)聯(lián)查詢，意味著對于某條記錄應(yīng)用只需要查詢一次，而在數(shù)據(jù)庫中做關(guān)聯(lián)查詢，則可能需要重復(fù)地訪問一部分?jǐn)?shù)據(jù)。
查詢執(zhí)行的基礎(chǔ)
MySQL 執(zhí)行一個查詢的過程，到底做了些什么：
- 客戶端發(fā)送一條查詢給服務(wù)器。
- 服務(wù)器檢查查詢是否可以命中緩存，命中則直接返回。否則進(jìn)入下一階段。
- 服務(wù)器進(jìn)行 SQL 解析、預(yù)處理，再由優(yōu)化器生成對應(yīng)的執(zhí)行計劃。
- 調(diào)用存儲引擎的 API 來執(zhí)行查詢
- 返回結(jié)果給客戶端
客戶端／服務(wù)器通信協(xié)議
通過抓包軟件分析，可以看到 MySQL 客戶端和服務(wù)器之間是通過 TCP 和 mysql 協(xié)議進(jìn)行通信的。具體細(xì)節(jié)這里就不講了，我也沒仔細(xì)看。目前我們只要大致理解通信協(xié)議是如何工作的就夠了。
- 通信協(xié)議是“半雙工”的：任意一個時刻，要么是服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是客戶端向服務(wù)器發(fā)送數(shù)據(jù)。無法進(jìn)行流量控制，一旦一端開始發(fā)送消息，另一端要接受完整消息才能響應(yīng)它。
- max_allowed_packet 參數(shù)可以限制數(shù)據(jù)包的長度。
- 服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常很多，由多個數(shù)據(jù)包組成。
- 客戶端接受數(shù)據(jù)一般是將全部結(jié)果集緩存到內(nèi)存中。這樣可以減少服務(wù)器的壓力。
查詢緩存
在解析查詢語句之前，如果查詢緩存是打開的，那么 MySQL 會優(yōu)先檢查這個查詢是否命中查詢緩存中的數(shù)據(jù)。沒命中則走下一個階段。命中了，在返回結(jié)果之前還會檢查用戶權(quán)限。如果權(quán)限沒問題，則直接返回數(shù)據(jù)給客戶端。這種情況下，查詢不會被解析，不用生成執(zhí)行計劃，不會被執(zhí)行。
查詢優(yōu)化處理
查詢的生命周期下一步是將 SQL 轉(zhuǎn)化成執(zhí)行計劃。然后再按照執(zhí)行計劃和存儲引擎交互。這包括多個子階段：解析 SQL，預(yù)處理，優(yōu)化執(zhí)行計劃。一條查詢可能有多種執(zhí)行方式，最后都返回相同的結(jié)果。優(yōu)化器的作用就是找到其中最好的執(zhí)行計劃。
查詢執(zhí)行引擎
存儲引擎根據(jù)查詢計劃來完成查詢。
返回結(jié)果給客戶端
查詢執(zhí)行的最后一個階段是返回結(jié)果給客戶端。即使客戶端不需要結(jié)果，也會返回這個查詢的一些信息，如該查詢影響到的行數(shù)。MySQL 將結(jié)果集返回給客戶端是一個增量、逐步返回的過程。好處是服務(wù)器不需要緩存太多的結(jié)果，客戶端也能第一時間獲得返回的結(jié)果。
優(yōu)化特定類型的查詢
- COUNT()
- 關(guān)聯(lián)查詢
- 子查詢
- GROUP BY 和 DISTINCT
- LIMIT 分頁
- UNION
