MySQL查詢優化-SQL診斷調優原則、原理及思路

MySQL查詢優化SQL診斷調優原則、原理及思路演講人蘇坡關于我u花名：蘇坡u袋鼠云成立于2016年，是國內數據中臺領域創導者u云掣—袋鼠云旗下企業云服務品牌2課程大綱優化目的與目標優化流程及思路原理剖析MySQL的行為常規優化策略301優化目的與目標為什么要優化提高資源利用率避免短板效應提高系統吞吐量5減少磁盤IO全表掃描磁盤臨時表減少網絡帶寬返回太多數據交互次數過多降低CPU消耗聚合函數。max,min,sum...邏輯讀硬件系統配置庫表結構庫表結構602優化流程及思路關注的指標CPU使用率?數據掃描、顯式計算IOPS?物理讀寫關鍵資源指標QPS/TPS會話數/活躍會話數Innodb邏輯讀/物理讀?反映整體查詢效率的引擎指標臨時表?導致SQL執行效率下降的特殊行為C-8合理監控...C-9C-MySQLC-構建完備的監控體系?細致合理的告警?多維度圖形化指標?暴露性能缺陷，掌控大規模資源定性分析分析定位問題定性分析監控你的系統?監控你的系統分析業務邏輯?讀寫需求?資源調用關系SQL優化??參數調整SQL優化原則與方法l減少訪問量：數據存取是數據庫系統最核心功能，所以IO是數據庫系統中最容易出現性能瓶頸，減少SQL訪問IO量是SQL優化的第一步；數據塊的邏輯讀也是產生CPU開銷的因素之一。?減少訪問量的方法：創建合適的索引、減少不必訪問的列、使用索引覆蓋、語句改寫。l減少計算操作：計算操作進行優化也是SQL優化的重要方向。SQL中排序、分組、多表連接操作等計算操作?創建索引減少掃描量?調整索引減少計算量索引檢索過程 非主鍵查詢主鍵查詢覆蓋索引1.客戶提交一條語句2.先在查詢緩存查看是否存在對應的緩存數據，如有則直接返回(一般有的可能性極小，因此一般建議關閉查詢緩存)。3.交給解析器處理，解析器會將提交的語句生成一4.預處理器會處理解析樹，形成新的解析樹。這一階段存在一些SQL改寫的過程。5.改寫后的解析樹提交給查詢優化器。查詢優化器6.執行計劃交由執行引擎調用存儲引擎接口，完成執行過程。這里要注意，MySQL的Server層和7.最終的結果由執行引擎返回給客戶端，如果開啟查詢緩存的話，則會緩存。器與執行計劃l負責生成SQL語句的有效執行計劃的數據庫組件l優化器是數據庫的核心價值所在，它是數據庫的“大腦”l優化器工作的前提是了解數據，工作的目的是解析SQL，計劃1.詞法分析、語法分析、語義檢查2.預處理階段(查詢改寫等)3.查詢優化階段(可詳細劃分為邏輯優化、物理優化兩部分)4.查詢優化器優化依據，來自于代價估算器估算結果(它會調用統計信息作為計算依據)查看和干預執行計劃c里showvariableslike'information_cesslistcopytotmptable：出現在某些altertable語句的copytable操作Copyingtotmptableondisk：由于臨時結果集大于tmp_table_size，正在將臨時表從內存存儲轉為磁盤存儲以此節省內存convertingHEAPtoMyISAM：線程正在轉換內部MEMORY臨時表到磁盤MyISAM臨Creatingsortindex：正在使用內部臨時表處理select查詢Sortingindex：磁盤排序操作的一個過程Sendingdata：正在處理SELECT查詢的記錄，同時正在把結果發送給客戶端Waitingfortablemetadatalock:等待元數據鎖SELECT優化-orderbyc里?Usingindex。MySQL直接通過索引返回有序記錄，不需要額外的排序操作，操作效率較高?Usingfilesort。無法只通過索引獲取有序結果集，需要額外的排序，某些特殊情況下，會出現Usingtemporary優化目標：盡量通過索引來避免額外的排序，減少CPU資源的消耗where條件和orderby使用相同的索引orderby的字段同為升序或降序注：當where條件中的過濾字段為覆蓋索引的前綴列，而orderby字段是第二個索引列時，只有where條件是const匹配時，才可以通過索引消除排序，而between...and或>?、<?這種range匹配都無法避免filesort操作SELECT優化-orderbyc里當無法避免filesort操作時，優化思路就是讓filesort的操作更快?兩次掃描算法。兩次訪問數據，第一步獲取排序字段的行指針信息，在內存中排序，第二步根據行指針獲取記錄?一次掃描算法。一次性取出滿足條件的所有記錄，在排序區中排序后輸出結果集。是采用空間換注：需要排序的字段總長度越小，越趨向于第二種掃描算法，MySQL通過max_length_for_sort_data參數的值來進行參考選擇1、適當調大max_length_for_sort_data這個參數的值，讓優化器更傾向于選擇第二種掃描算法2、只使用必要的字段，不要使用select*的寫法3、適當加大sort_buffer_size這個參數的值，避免磁盤排序的出現（線程參數，不要設置過大）SELECT優化-Subqueryc里子查詢會用到臨時表，需盡量避免可以使用效率更高的join查詢來替代等價改寫、反嵌套如下SQL：SELECT優化-limit分頁查詢，就是將過多的結果在有限的界面上分好多頁來顯示。其實質是每次查詢只返回有限行，翻頁一次執行一次。2、避免掃描到大量不需要的記錄SQL場景（film_id為主鍵此時MySQL排序出前10020條記錄后僅僅需要返回第10001到10020條記錄，前10000條記錄造成額外的代價消耗覆蓋索引?僅適合查詢字段較少的情況selecta.film_id,a.descriptionfromfilmainnerjoin(selectfilm_idfromfilm?優化的前提是title字段有索引?思路是從索引中取出20條滿足條件記錄的主鍵值，然后回表獲取記錄SELECT優化-or/andcondition?and結果集為關鍵字前后過濾結果的交集?or結果集為關鍵字前后分別查詢的并集?and條件可以在前一個條件過濾基礎上過濾?or條件被處理為UNION，相當于兩個單獨條件的查詢?復合索引對于or條件相當于一個單列索引and子句多個條件中擁有一個過濾性較高的索引即可or條件前后字段均要創建索引為最常用的and組合條件創建復合索引C-BAjoin優化}}}?關聯字段索引：每層內部循環僅獲取需要關心的數據?小表驅動原則：減少循環次數?小表：返回結果集較少的表C-jo索引的必要性jo索引的必要性join優化忽略b表的索引，使b表作為驅動表：C-join優化