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