六星教育-mysql12explain實例與io操作

課程回.SQL語句的過強和過弱的使優(yōu)化SQL的思 (重點expln分 (重 不算 就是需要記記expln分析簡單實poflng分MySQL數(shù)據(jù)磁盤O操Btee結(jié)2.explain題目=》其余題目當做作業(yè)吧Page ?CopyrighThursday,Sep5,209,2:2PMby單s1dchar(20)NOTN,namevarchar(20)NOTUL,城市tyvarchar(10)NOT：：M（男），G（女genderini(NOTrhaedateNOT號mblechar(11)DEFAULT,moslryema0)NOTyearbonuscim(10)DEFAULTPRIMARYKEY(d)ENGINE=InnoDBDEFAULTCAEu8CREATETABLEcsomsdchar(20)NOTNU,ityvarchar(10)NOTU,gendertni(NOTNbrhddateNOTNUilchar(11)DEFAULTNUphotovarchar(20)DEFAULTUL,PRIMARYKEY(d))ENGINE=InnoDBDEFAULTCAEu8CREATETABLEslr(dchar(20)NOT,onlardecia102NOTNULea),PRIMARYKEY(d))ENGINE=InnoDBDEFAULTCAEu81.女性客戶數(shù)量和平均月showshowdxsfomusomrs;sm;showdeeoaaryeralelrydropidx_mosryresomsxi_n(i;單表操作ct*)a(nsalar)omreectcoun()aslarmonsalr)fmcsomsftoinlryoncsomesd=alidwherecsomesgdr=0兩表m>epanseetoun)a(slronhslar)fmcsomsftjinsryoncsomesd=layidwheressgr=0d slct_ypeao osil_kes_ln 11o lrIAIAmlcmi1sin1、單表msl>exaineecto*agmsr)romsomrs1wheregender=d slct_ypeao typeosil_kes _ln IPE NULLNULL10.00in1rowiset,1wri(0.00首先要做的就是分析結(jié)構(gòu)；通過xpai去分從lin分析type可以看出來這個時候的SQL是進行了全表的掃描；key(查詢實際用到的索引)與see( k_(而extra為enwhere簡單來說就是這個查詢就是對于整個數(shù)據(jù)表進行了全表的掃描，沒有使用到索引過濾；而主要的過濾條件就是here也就是gender=根據(jù)索引的建立的規(guī)則》把Q分解索引應該創(chuàng)建在搜索、排序、歸組等操作所涉及的數(shù)據(jù)列上，旨在輸出報告里出現(xiàn)的數(shù)據(jù)列不是好的候選。簡單點就是在whr字句、關聯(lián)檢索中的from字句、ordr與goup字句中出現(xiàn)的數(shù)據(jù)列最適合用來創(chuàng)建索引。只在seet關鍵字后面輸出列 例出現(xiàn)過的數(shù)據(jù)列并不是好的候選sltoun()v(on 考慮一smr1gender 這個不其他規(guī) 過過過之后細細分lteabcsomsaddiegender(gender);執(zhí)行之后這個時候還是需要通過xpai分析一下數(shù)據(jù)的情msl>exaineecto*agmsr)romsomrs1wheregender=d sl_tpe to typeoss_ks e_ln l IM gender 100.001rowiset,1wri(0.00從分析的結(jié) o_ ln gender1 使用了剛剛創(chuàng)建的索引,長度為1并且是為常量的方式查但是這里的extra為空，注意這個點；為空？？？先不糾結(jié)測試，但是測試的結(jié)果很尷l>ectut)ag(nsalar)romsomrs1wheregender=0;count(*)g(moslr) 1rowiset(41.58并沒有起到很好地效果？？？？？？？？為什么????其實這里的話就設計到了索引的設計以及查找的情況，主要的問題在于索引回表（之后解釋）了導致思路的話可以分解sect對于count(),a(monthsalar)進行單獨測試看看效果；找出是那個部分的因素引起的注意：單獨對于sltou()omsoms1測試一下l>enetomrd sl_tpe to typeoss_ks e_ln l IP usoms1 gender 100.00in1rowiset,1wri(0.00l>exinectag(mntsar)omsomr1wheregender=d sl_tpe to typeoss_ks e_ln l IM gender 100.001rowiset,1wri(0.00可以看出來是agontslr)引起的，也就是說我們只需要把eetagontsala)fomsomers1regender=0;這個優(yōu)化好了那么就可以創(chuàng)建了一個索引gender這個時候count(*)在操作的時候就會以gender作為輔助索引使用。而在上面的語句中僅僅只是根據(jù)whregdr=0過濾了查找的內(nèi)容，但是在進行數(shù)據(jù)v的時候這個時候就是需要去進行IO獲取數(shù)據(jù)具體的數(shù)據(jù)，MQ在輔助索引操作的時候如果無法從輔助索引中獲取數(shù)據(jù)這個時候就會再去查詢一級索引主鍵根據(jù)主鍵獲取數(shù)據(jù)再做計算；所以為了與更好的進行mothsaary的ag操作我們應該要給motsaary建立一個索引trabesoms1addidexmonthlr(monthlr;ms>pinseetav(mnslr)fmcsoms1d sl_tpe to oss_ks e_ln l IP usoms1 ex h NULL 100.00inn1rowiset,1wri(0.00msl>exainsletg(moslr)romsmrwheregender=d sl_tpe to typeoss_ks e_ln l IM gender 100.001rowiset,1wri(0.00o(▼皿▼メo(⊙⊙其實這就是MQMQ的索引，在上面的查詢中最為合適的就是gender這里最好的處理就是建立一個gender與mnsary的聯(lián)合索引；刪除之前的索引re1x;erbecstom1dropidxgender;ltealeomraddinee_monsaydrontaar)=>注意順序，gendermsl>exaineecto*agmsr)romsomrs1wheregender= sl_tpebe to typeoss_ks e_lnref IP usoms1NULL gdr_moslrygdr_moslry1 ingdex1rowiset,1wri(0.00實際執(zhí)l>ectut)ag(nsalar)romsomrs1wheregender=0;count(*)g(moslr) plai優(yōu)化指標mnsr_nd_muu_sqrn_uqnedl如果是根據(jù)主鍵或唯一索引只取出確定的一行數(shù)據(jù)。是最快的一種索引或主鍵，在某個范圍內(nèi)僅僅只有索引被掃l全表掃描，最令人要想有畫一條uey，就需要清楚這條uey的性能瓶頸到底在哪里，myq的rfr是一個使用非常方便的查詢診斷分析工具，通過該工具可以獲取一條查詢在整個執(zhí)行過程中多種資源的消耗情況，例如內(nèi)存消耗、/消耗和P消耗等。profe的 則如下showshowfle[type[,type]...][forqueryn]t其中type參數(shù)的可選項含義如a：顯示所有的信bocko：限時輸入輸出操作阻塞的數(shù)contextswtches：顯示自動或非自動contextswtches的數(shù)量pc：顯示和接受的數(shù)memoy：內(nèi)存的sowsowoMMD[omui]setprlin=1;QueryK,0roaffected(0.00sec)在開啟了queyprofer功能之后，mysq就會自動記錄所有執(zhí)行的quey的profe信息。下面執(zhí)行quetomrectcount(*)m通過執(zhí) 命令獲取當前系統(tǒng)中保存的多個quey的profe的概要信息riDB[omut]>showpol; D 1.43076000sctcount(*)omy0.35574100sctcount(*)om2rowsnset(0.00針對單個query獲取詳細的profile的信在獲取概要信息之后就可以根據(jù)概要信息中的queyd來獲取某一個quey在執(zhí)行過程中的profeMMDB[muni]>showpoleforqueryoni hingo ga gbs m ble piz eu dg query lsin Uocin frng ai eaing ri00i00gbs00gbs00m00le00000000i00i00ig0000query00oils00ongls00gi00pdig00eaing00iDBiDB[ommi]>showrfepubokiforqueryDrionCPU_usere lo_op_ilnnodb-磁盤的關對于MySQL來說數(shù)據(jù)是存在于磁盤中的，SQL主要就是通過MySQL的引擎的接口hander(sq/hander.cc)類進行數(shù)據(jù)的獲取和操作它提供了一些可現(xiàn)基本操作方法，如打和關閉表，續(xù)掃描記、照檢索記錄記錄及除記錄。每引擎都行處理的個類以實接口方法，一邊將處理器操作轉(zhuǎn)化為特定引擎的低層次檢索AP調(diào)用。nnodb引擎用于管理數(shù)據(jù)的最小磁盤單位。常見的頁類型有數(shù)據(jù)頁、Undo頁、系統(tǒng)頁、事務數(shù)據(jù)頁等，本文主要分析的是數(shù)據(jù)頁。默認的頁大小16KB，每個頁中至少有2條或以上的行記nnodb引擎對于數(shù)據(jù)表結(jié)構(gòu)表空間(abespace)》分片段(Segment)》盤區(qū)(Extents)》頁表空間表空間分為了兩種，這里簡單的概括一下獨立表空間：每一個表都將會生成以獨立的文件方式來進行，每一個表都有一個.frm表描述文件，還有一個.bd文件。其中這個文件包括了單獨一個表共享表空間：nnodb的所有數(shù)據(jù)保存在一個單獨的表空間里面，而這個表空間可以由很多個文件組成，一個表可以跨多個文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。從nnodb的文檔中可以看到，其表空間的最大限制為64B，也就是說，nnodb的單表限制基本上也在64B左nnDB把數(shù)據(jù)保存在表空間內(nèi)，表空間可以看作是nnDB引擎邏輯結(jié)構(gòu)的最。本質(zhì)上是一個由一個或多個磁盤文件組成的虛擬文件系統(tǒng)。nnDB用表空間并不只是表和索引，保存了回滾、雙寫緩沖等。分片段段是表空間文件中的主要組織結(jié)構(gòu)，它是一個邏輯概念，用來管理物理文件，是構(gòu)成索引、表、回滾段的基本元創(chuàng)建一個索+樹）時會同時創(chuàng)建兩個段，分別是內(nèi)節(jié)點段和葉子段，內(nèi)節(jié)點段