高效能mysql 第六章查詢效能最佳化 總結（上）查詢的執行過程

標籤：目的   參考   建立   成本   儲存   結合   不同的   ica   引擎   

6  查詢效能最佳化

6.1為什麼查詢會變慢

   這裡說明了的查詢執行循環，從用戶端到伺服器端，伺服器端解析，最佳化器產生執行計畫，執行（可以細分，大體過程可以通過show profile查看），從伺服器端返回用戶端結果。

   而執行部分作為最重要的一環，需要做的事情比較多，而不合適的query往往讓執行過程做了不必要的操作，或者不能使用更優秀的底層資料結構，從而用時更久。

 

6.2慢查詢基礎：最佳化資料訪問

   訪問資料量多大，超過實際所需是慢查詢的一個原因。導致這種情況的原因大致有兩個

1.應用程式向mysql服務需求的了超過需要的，或重複的資料，比如select * 。

2.mysql伺服器執行過程中訪問了超過需要的資料。

 

我們先來看第一種原因

6.2.1向資料庫請求了超過需要的資料

1.請求了超過需要的行，如沒有使用limit，請求了100行資料，但是只使用了10行。

2.使用select * 返回了不需要的列資料，尤其是多表關聯查詢是情況更突出，應該做的是只返回我們需要的列。

3.用戶端如果使用了緩衝，並且該部分緩衝被接下來的資料需求命中，那麼就沒有必要再向伺服器發送資料請求。

 

6.2.2mysql掃描了額外的紀錄

慢查詢日誌中的三個衡量查詢開銷的指標：1.回應時間，2。掃描行數，3.返回行數

1.回應時間

回應時間包括服務時間與排隊時間。

回應時間在不同的伺服器環境，應用環境下都會不同。文中提到使用‘快速上限法’（中文版譯名）：通過query的執行計畫，計算需要的順序io與隨機io次數，再結合當前環境下一次io的時間。最後做一次加總可以獲得一個‘參考值‘。（可以看出來，這樣的一種方法實操性並不強，因為並不是每一個query我們都能相對準確的估算io次數，不同伺服器環境下，甚至統一伺服器的不同時間，io的效率也會不同。     不過這裡我們可以看出來，io是執行過程中相對比較耗時的一步）

 

2.掃描行數與返回行數

 掃描行數與返回行數單獨都是不能體現出任何query的效率的，只有兩者的比值才能說明該query篩選資料的能力。

 

3.掃描行數與訪問類型

explain 中的type列列出了訪問類型：全表掃；索引掃描；範圍掃描；單值查詢

 

6.3重構查詢的方式

 

6.3.1一個複雜的查詢還是多個簡單的查詢

由於mysql的網路通訊協定是‘半雙工’的，串連斷開的花費並不大，所以很多時候分解查詢是最佳化的sql的一個有效方式。

 

6.3.2切分查詢

切分查詢是指對大的查詢採取分而治之的策略，分時段分但伺服器端的壓力。但次執行分段的sql都是一樣的，只是通過do_query()返回的資料與limit實現對資料條目的分批處理。

 

6.3.3分解關聯查詢

將關聯查詢分解成多個sql步驟。

這樣做是減小事務的粒度，能夠緩解鎖的爭用，也能讓緩衝的資料更加模組化，便於使用緩衝。

 

6.4查詢的執行步驟

用戶端請求資料-->伺服器端查詢快取,命中返回，未命中-->mysql解析sql，先行編譯，產生執行計畫-->調用資料庫引擎api執行sql-->返回結果到用戶端

 

6.4.1mysql通訊協定

‘半雙工’。即同一時間，只有一方在發送資料，只有在接受完對方發送的資料，才可以向對方發出響應。

這裡提到了用戶端擷取資料的兩種方式，在c#一種是dateset，即一次性返回資料，在用戶端儲存資料快照（緩衝），一種dateread是保持用戶端與伺服器端的串連，用戶端隨時查詢資料。--不同的語言提供了不同的調用方式。

 

查詢狀態

指的是查詢的進程的執行狀態：需要注意的是sort result對結果集排序；sanding date表示自產生結果集或者在向用戶端返回資料。

6.4.2查詢快取

就是查詢步驟中的第二步查詢的結構。第七章中有介紹。

 

6.4.3查詢最佳化

即是查詢步驟中的第三步

 

1.語言解析器與前置處理器

解析器對文法進行檢查，前置處理器檢查語義並檢查使用者是否有許可權。

 

2.查詢最佳化工具

最佳化器對前置處理器產生的預先處理樹進行執行計畫的測算，計算各種執行計畫的花費（有前面的知識我們知道，這裡的花費往往指的是查詢需要訪問的資料頁的多少）

可以明白：最佳化器對成本的測算並不是啊完全準確的，但是對於大多數sql，io都是它的執行瓶頸所在。

注意最佳化器並不會考慮緩衝（有緩衝的話不應該第二步就命中，返回給用戶端了嗎。

 

最佳化有靜態最佳化與動態最佳化的區分，考慮到第七章查詢快取部分與預存程序的描述，我在考慮mysql是否儲存執行計畫呢？如何儲存sql的執行計畫的呢？靜態動態最佳化時二選一還是並存呢？

 

書中列出了10多中最佳化器能夠最佳化的查詢，我不在一一列舉。

 

1. 資料索引的統計資訊

Myisam引擎儲存了count，而innodb只能提供估算值（innodb的資料與索引是糾纏的，插入刪除修改還可能導致頁分裂，頁合并等問題，所以儲存一個count之類的統計值維護不易）

 

4.關聯查詢的執行

Mysql的關聯查詢可以看做一個雙迴圈或者多迴圈，只有兩個表的關聯查詢，外側迴圈的表叫外表，內側迴圈的表叫內表。可以知道內側迴圈的查詢次數是遠多於外側的，所以內表非常需要一個合適的索引。

 

5.關聯查詢最佳化工具

但是內外表常常不是我們可以決定的，mysql最佳化器會在其中起作用，最佳化器會建立一顆深度優先樹，通過不同的組合得出花費最少的執行計畫。不過在關聯的表n過多時執行計畫樹的顆數會指數上升，n超過optimizer_search_depth就不再使用窮舉的方式了，而是使用別的搜尋方式擷取最優執行計畫。

 

上一段中mysql對關聯查詢的執行方式是嵌套迴圈，執行計畫表現為一顆深度優先樹。

 

1. 排序最佳化

第三章中介紹了索引排序，這裡介紹filesort。

Filesort檔案排序，filesort並不總是要用到磁碟，當資料量較小時可以再記憶體中進行。

資料量是否大於檔案緩衝區是兩者的分水嶺。當使用磁碟排序是相當於外排序。

 

Mysql filesort排序策略

兩次讀取資料(舊版):只使用行指標與排序欄位做排序，這樣排序結果需要通過行指標才能讀取到全部資料，由於是隨機io，io效率低。

 

單次讀取資料（新版）：先讀取所需要的列，然後在根據給定列進行排序。

兩個各有優劣，這個討論我們放在第8章中

5.6版本在這裡做出了改進,當使用limit時，mysql不再對所有結果排序，而是僅對需要的資料排序。

 

6.4.4查詢執行引擎

Mysql的執行計畫是一個資料結構，執行該執行計畫時，需要調用儲存引擎是響應的‘handler api’實現。如果是所有預存程序共有的特性，一般是伺服器層實現的，如日期函數，視圖，觸發器。

 

6.4.5返回資料

Mysql執行sql過程中，開始產生第一條結果時就開始返回資料，這樣是的伺服器不需要儲存大量的結果。（往後看可以發現union會到這個需要建立一個暫存資料表來儲存資料，前部union段執行完成才能返回資料）

高效能mysql 第六章查詢效能最佳化 總結（上）查詢的執行過程