小談mysql儲存引擎優化

vipshop_fin_dev發表於2018-07-16
MySql儲存引擎優化

小談mysql儲存引擎優化

平時我們在寫某個sql時，總會用Explain來檢視一下執行計劃，看看自己的sql是否和預期一樣。執行計劃分析也是我們常需要準備技能之一。無論哪種DBMS，決定查詢執行計劃的優化器是最複雜的部分。把握InnoDB引擎耗費很多時間處理查詢的原因，對查詢調優相當有用。

一、查詢執行過程

mysq伺服器中，查詢的執行過程大致分為如下3個階段：

(1). 從使用者接受SQL語句，切分語句並分析詞法語法，最後生成mysql伺服器可以理解的解析器。
(2).確認SQL的解析資訊，選擇從哪個資料表讀取，以及使用哪種索引讀取資料表
(3). 藉助第二階段確認的讀表順序或者所選索引，從儲存引擎讀取資料。

第二階段會依據第一階段形成的SQL解析樹處理如下內容。

  • 刪除不必要的條件，將複合運算簡單化
  • 若存在奪標連線，則確定讀表順序。
  • 根據用於各資料表的條件和索引統計資訊，確定要使用的索引。
  • 將獲取的記錄放入臨時表，確定是否需要再次加工

查詢解析與優化器圖如下：
這裡寫圖片描述

下面我們粗略介紹一下優化器、排序緩衝等

二、優化器種類

從作用而言，優化器相當於資料庫伺服器的大腦，大致分為：

1.” 基於規則的優化“預設不考慮目標資料表的記錄數、選擇度等，而根據優化器中內建的若干規則制定相應的執行計劃。該方式中，制定執行計劃不會調查統計資訊。所以對相同的查詢，其執行方式總是相同的
2.“基於代價的優化”查詢時會建立多種可用方法，然後根據各種處理的消耗資訊與目標表的統計資訊，計算各種執行計劃代價，然後從中選擇代價最小的執行計劃作為最終執行計劃。

目前大部分RDBMS幾乎都採用代價的優化器

三、優化器何時選擇何種掃描方式

3.1 全表掃描

innodb優化器主要在下列情況選用全表掃描：
  • 資料表中的記錄較少，使用全表掃描比通過索引讀取更快時（一般一個資料表由一個頁面組成）
  • Where或On語句中不具備使用索引的合適調價
  • 即使查詢可以使用索引範圍掃描，優化器判斷符合條件的記錄太多時（以通過對索引的B-Tree取樣而獲取的統計資訊為準）

大部分DBMS執行全表掃描時，1次可以讀取多個資料塊或者頁面，且可以對讀取多少進行調整控制。

在MyISAM中，執行全表掃描時，會從磁碟逐頁讀取。而在InnoDB或Xtra儲存引擎中，連續讀取特定資料表的資料頁面時，後臺執行緒會自動開始預讀作業。預讀是指先對將來要用到的資料區域進行預測，然後在對該區域資料的請求到來之前，先將輸資料從磁碟督導緩衝池。也就是說執行全表掃描時，最初幾個資料頁面由前臺執行緒（客戶執行緒）讀取，然後從某個時間點開始，讀取作業被交由後臺執行緒執行。後臺執行緒從接手讀取作業開始，每次讀取4-8個頁面，並且數量不斷增加，一次最大可以讀取64個資料頁，然後將其存入緩衝池。由於前臺執行緒可以獲取並且使用預先儲存在緩衝池中的資料，所以查詢的處理速度相當快。

3.2 ORDER BY 處理（Using filesort）

處理排序有兩種方法，一種是使用索引，另一種是執行查詢時，使用filesort處理
像使用索引排序一樣，對記錄排序時，並非總要進行Filesort排序處理。但在下面情形下，幾乎不可能使用索引進行排序。

  1. 排序基準太多，無法依據每個基準全部建立索引。
  2. 要對GROUP BY的結果或者DISTINCT等處理結果排序時
  3. 要對臨時表的結果（UNION的結果）重新排序時
  4. 要隨機獲取結果記錄時

3.2 排序緩衝

執行排序操作時，InnoDB會另外分配空間進行排序處理，該空間成為“排序緩衝”（sort buffer）.只有排序需要時，才開闢緩衝空間，其大小隨著要排序的記錄大小而變化。查詢執行完畢後，用於排序的空間會立即返還給系統。如果要排序的記錄非常少，只用分配的排序緩衝就能完成排序，那麼排序的處理速度非常快。但如果排序的記錄大於分配的空間時，會發生什麼呢。此時會將將要排序的記錄劃分為很多片段，並將其臨時儲存到磁碟中。排序時，先在記憶體中的排序換從中對記錄進行排序，然後將排序結果臨時儲存到磁碟中。之後再獲取下一批記錄，排序後臨時儲存到磁碟中，如此反覆。最後再根據緩衝哦大小合併並排好序的記錄，完成排序處理.

3.3 排序演算法：

3種排序處理方式

排序處理方法 執行計劃的Extra列
使用索引排序 無內容顯示
只對驅動表排序（含無連線情形） 顯示Using filesort
將連線結果儲存到臨時表後，在臨時表中排序 顯示Using temporary;Using filesort

在執行order by排序時，優化器會檢查是否可用索引進行排序處理。若可用索引，則不需FileSort過程，依照順序讀取索引返回結果即可。但若是索引不可用，則檢索Where條件的記錄，將其儲存到排序緩衝中，並進行排序處理。此時

1.對驅動表排序，然後進行連線
2.完成連線後，獲取所有符合條件的記錄，然後排序

平時所用的sql中大多含有Order by 與limit，處理ORDER BY 或者GROUP BY時，不能只從滿足WHERE條件的記錄中獲取LIMIT 制定的條數。首先要獲取所有滿足條件的記錄，進行排序或者分組後，才能進行limit記錄條數限制處理。

2種查詢處理方法：

1） 流處理方式：在流處理方式下，無論服務端有多少資料處理，每檢索到一條滿足條件的記錄就立即返回給客戶端。
2） 緩衝處理方式：處理帶有ORDER BY 或者GROUP BY子句時，由於先要獲取符合WHERE條件的所有記錄，再排序分組，然後依次傳送處理結果，所以無法對查詢的結果進行流處理。這種方式稱為緩衝（Buffering）處理，與流方式處理。

在ORDER BY的3種排序方式中，只有“使用索引排序方式”採用流處理，其他處理方式均需要先緩衝再進行排序處理。

四、後記

mysql儲存引擎還有很多優化機制，仍有待後續深入學習研究。

相關文章