MySQL--查詢效能最佳化

標籤：分而治之   大量資料   並行   協助   using   count   解析   標識   檢索   

• 為什麼查詢速度會慢
• 慢查詢基礎：最佳化資料訪問
• 確認應用程式是否在檢索大量超過需要的資料，這通常意味著訪問了太多的行，但有時候也可能是訪問了太多的列
• 查詢不需要的資料
• 多表關聯時返回全部列
• 總是取出全部列
• 重複查詢相同的資料
• 確認mysql伺服器層是否在分析大量超過需要的資料行
• 衡量查詢開銷的三個指標
• 回應時間
• 掃描的行數
• 返回的記錄數
• 回應時間
• 回應時間只是一個表面上的值
• 回應時間是兩個部分之和：服務時間和排隊時間
• 掃描的行數和返回的行數
• 分析查詢時，查看該查詢掃描的行數十分常有協助的，這在一定程度上能夠說明該查詢找到資料的效率高不高
• 掃描的行數和訪問類型
• 從慢到快：全表掃描、索引掃描、唯一索引掃描、常數引用，掃描行數也是大到小
• 一般mysql能夠使用如下三種方式應用where條件，從好到壞為：
• 在索引中使用where條件來過濾不匹配的記錄，這是在儲存引擎層完成的
• 使用索引覆蓋掃描來返回記錄，直接從索引中過濾不需要的記錄並返回命中的結果，這是在mysql引擎層完成的，但無需再回表查詢記錄
• 從資料表中返回資料，然後過濾不滿足條件的記錄（useng where），這是在mysql伺服器層完成的，mysql需要先從資料表中讀出記錄然後過濾
• 重構查詢的方式
• 一個複雜查詢還是多個簡單查詢
• 這是在設計查詢的時候一個需要考慮的問題，是否將一個查詢分成多個簡單的查詢
• 切分查詢
• 有時候對於一個大查詢我們要‘分而治之‘，將大查詢切分成小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果
• 分解關聯查詢
• 很多高效能的應用都會對關聯查詢進行分解。簡單地，可以對每一個表進行單次查詢，然後將結果在應用程式中進行關聯
• 優勢：
• 讓緩衝的效率更高
• 將查詢分解後，執行單個查詢可以減少鎖競爭
• 在應用程式層做關聯，可以更容易對資料庫行進拆分，更容易做到高效能和可擴充
• 查詢本身效率也可能有所提升
• 可以減少冗餘記錄的查詢
• 更進一步，這熊當於在應用中實現了hash關聯，而不是使用mysql的嵌套迴圈關聯
• 查詢執行的基礎

•  

• 客戶算首先發送一條查詢給伺服器
• 伺服器先檢查緩衝，如果命中緩衝，則立刻返回儲存在緩衝中的結果 ，否則進入下一階段
• 伺服器端進行SQL解析、預先處理，再由最佳化器產生對應的執行計畫
• mysql根據最佳化器產生的執行 計劃，條用儲存引擎的API來執行查詢
• 將結果返回給用戶端

 

• MySQL用戶端/伺服器通訊協定
• 查詢狀態
• sleep
• query
• locked
• analyzing and statistics 線程、正在手機儲存引擎的統計資訊，並產生查詢的執行計畫
• copying to tmp table　［on disk］
• sorting result
• sending data
• 查詢快取
• 查詢和緩衝中的查詢即使只用一個位元組不同，那麼也不會匹配緩衝結果
• 查詢最佳化處理
• 查詢的生命週期的下一步是將一個SQL轉換成一個執行計畫，mysql再依照這個執行計畫和儲存引擎進行互動。
• 文法解析器和預先處理 
• 查詢最佳化工具
• 有很多種原因會導致mysql最佳化器選擇錯誤的執行計畫：
• 統計資訊不準確
• 執行計畫中的成本估算不等同於實際執行的成本 
• mysql只是基於其成本模型選擇最優的執行計畫，而有些時候這些並不是最快的執行方式
• mysql從不考慮其他並發執行的查詢 ，這可能會影響當前查詢的速度
• mysql並不是任何時候都是基於成本的最佳化，有時也會基於一些固定的規則
• mysql不會考慮不受其控制的操作的成本
• 最佳化器有時候無法去估算所有可能的執行計畫，所以他可能錯過實際上最優的執行計畫
• 最佳化策略可以簡單地分為兩種，一種是靜態最佳化，一種是動態最佳化
• 靜態最佳化可以直接對解析數進行分析，並完成最佳化
• 靜態最佳化不依賴於特別的數值，如where條件中帶入的一些常數等
• 靜態最佳化在第一次完成後就一直有效，即使使用不同的參數重複執行查詢也不會發生變化
• 動態最佳化則和查詢上下文有關，也可能和很多 其他因素有關
• 在執行語句和預存程序的時候，動態最佳化和靜態最佳化的區別非常重要
• mysql能夠處理的最佳化類型
• 重新定義關聯表的順序
• 將外串連轉化成內串連
• 使用等價變換規則
• 最佳化count（），min（）和max（）
• 預估並轉化為常數運算式
• 覆蓋索引掃描
• 子查詢最佳化
• 提前終止查詢
• 等值傳播
• 列表IN（）的比較
• 資料和索引的統計資訊
• 統計資訊由儲存引擎實現，不同的儲存引擎可能會有儲存不同的統計資訊
• mysql如何執行關聯查詢（join）
• mysql中的關聯一次所包含的意義比一般意義上理解的要更廣泛，mysql認為任何一個查詢都是一次關聯--並不僅僅是一個查詢需要到兩個表匹配才叫關聯，所以在mysql中 ，每一個查詢，每一個片段（包括子查詢，基於單表的select）都可能是關聯
• mysql對任何關聯都執行嵌套迴圈關聯操作，即mysql現在一個表中迴圈取出單條資料，然後再嵌套到下一個表中尋找匹配的行，一次下去，直到找到所有表中匹配 的行為止

•  

• 執行計畫
• mysql產生查詢的一顆指令樹，然後通過儲存引擎執行完成這顆指令樹並返回結果
• mysql的執行計畫是一顆左側深度優先的樹

•  

• 關聯查詢最佳化工具
• 關聯查詢最佳化工具通過評估不同順序時的成本來選擇一個代價最小的關聯順序
• 排序最佳化
• 單次傳輸排序
• 先讀取查詢所需要的所有列，然後再根據給定的列進行排序，最後直接排序結果
• 查詢執行引擎
• 返回結果給用戶端
• 查詢最佳化工具的局限性
• 關聯子查詢
• union的限制
• 如果希望union的各個字句能夠根據limit只取部分結果集，或者希望能夠先排好序再合并結果集的話，就需要在union的各個子句中分別使用這些子句
• 索引合并最佳化
• 當where條件的子句中包含多個複雜條件的時候，mysql能夠訪問單個表的多個索引以合并和交叉過濾的方式來定位需要尋找的行
• 等值傳遞
• 並存執行
• 雜湊關聯
• 鬆散索引掃描
• 最大值和最小值最佳化
• 在同一個表上查詢和更新
• 查詢最佳化工具的提示（hint）
• 最佳化特定類型的查詢
• 最佳化count()查詢
• count()的作用
• count()是一個特殊的函數，有兩種不同的作用：它可以統計某個列值的數量也可以統計行數 count不統計null
• 最佳化關聯查詢
• 確保on或者using子句中的列上有索引
• 確保任何的group by和order by中的運算式只涉及到一個表中的列，這樣才有可能使用索引來最佳化這個過程
• 最佳化子查詢
• 最佳化group by 和distinct
• 最佳化limit分頁
• 最佳化sql_calc_found_rows
• 最佳化union查詢
• 靜態查詢分析
• 使用使用者自訂變數
• 使用者自訂變數是一個用來儲存內容的臨時容器，在串連mysql的整個過程中都一直存在
• 下列情境不能使用
• 使用自訂變數的查詢，無法使用查詢快取
• 不能在使用常量或者標識符的地方使用自訂變數
• 使用者自訂變數的生命週期是在一個串連中有效，所以不能用他們來做串連間的通訊
• 如果使用串連池或者持久化串連，自訂變數可能讓看起來毫無關聯的代碼發生互動
• 不能顯式地聲明自訂變數的類型
• mysql最佳化器在某些情境下可能會將這些變數最佳化掉，這可能導致代碼不按預想的方式運行
• 賦值的順序和賦值的時間點並不總是固定的
• 賦值符號：=的優先順序非常低，所以，賦值運算式應該使用明確的括弧
• 使用未定義的變數不會產生任何語法錯誤
• 最佳化排名語句
• 避免重複查詢剛剛更新的資料
• 統計更新插入的數值
• 確定取值的順序
• 編寫偷懶的union
• 使用者自訂變數的其他用處
• 查詢運行時計算總數和平均值
• 類比group 語句中的函數first（）和last（）
• 對大量資料做一些資料計算
• 計算一個大表的md5散列值
• 編寫一個樣本處理函數，當樣本中的數值超過某個邊界值的時候將其變成0
• 類比讀/寫遊標
• 在show語句的where子句中加入變數值

  

MySQL--查詢效能最佳化