知識(shí)點(diǎn)：建立一張Person表如下：CREATETABLEPerson(IdintIDENTITY(1,1)NOTNULL,Namenvarchar(50)NULL,AgeintNULL,HeightintNULL,Areanvarchar(50)NULL,MarryHistorynvarchar(10)NULL,EducationalBackgroundnvarchar(10)NULL,[Address]nvarchar(50)NULL,InSiteIdintNULL)ON[PRIMARY]循環(huán)添加14萬條數(shù)據(jù)：declare@cnointdeclare@ccointset@cno=1set@cco=14000while@cno<@ccobegininsertintopersonselect'公子',25,170,'不詳','未婚','大專','不詳',5077198unionallselect'小櫻',24,160,'不詳','未婚','大專','不詳',5077199unionallselect'雨水我問你',23,178,'不詳','未婚','大專','不詳',5077200unionallselect'小魏',26,170,'不詳','未婚','大專','不詳',5077201unionallselect'kenny',32,170,'不詳','未婚','大專','不詳',5077202unionallselect'djphoebe',26,175,'廣東','未婚','大專','廣東',5077203unionallselect'似水柔情',25,164,'不詳','未婚','本科','不詳',5077204unionallselect'Cherry',25,166,'不詳','未婚','本科','不詳',5077205unionallselect'樂事',63,181,'不詳','未婚','大專','不詳',5077206unionallselect'專一小馬蹄',25,172,'不詳','未婚','大專','不詳',5077207set@cno=@cno+1end1、數(shù)據(jù)訪問操作1-01、全表掃描全表掃描：發(fā)生于堆表，并且在沒有可用的索引可用時(shí)，會(huì)發(fā)生全表掃描,表示整個(gè)表掃描一次。在查詢中，應(yīng)盡量避免全表掃描。執(zhí)行一條sql語句如下：SELECT*FromPersonWHEREName='公子'查看執(zhí)行計(jì)劃如下：備注：全表掃描，就是整張表掃描（逐行遍歷每條數(shù)據(jù)），找到你所需要的數(shù)據(jù)。1-02、聚集索引掃描聚集索引掃描：發(fā)生于聚集表，也相當(dāng)于全表掃描操作，但在針對(duì)聚集列的條件如(WHEREId>10)等操作時(shí)，效率會(huì)較好。在ID列，加一個(gè)聚集索引：CREATECLUSTEREDINDEXIX_IdONPerson(Id)再次執(zhí)行同樣的查詢語句：SELECT*FromPersonWHEREName='公子'執(zhí)行計(jì)劃如下：1-03、聚集索引查找聚集索引查找：掃描聚集索引中特定范圍的行?？磮?zhí)行以下SQL語句（id是聚集索引列）：select*frompersonwhereid=130025執(zhí)行計(jì)劃如下：1-04、非聚集索引掃描非聚集索引掃描：整體掃描非聚集索引。添加非聚集索引如下：CREATENONCLUSTEREDINDEXIX_NameONPerson(Name)--創(chuàng)建非聚集索引此時(shí)，Person表總共有聚集索引、非聚集索引兩種索引。執(zhí)行查詢語句如下（name是非聚集索引列）：SELECTNameFROMPerson執(zhí)行計(jì)劃如下：為什么此處執(zhí)行計(jì)劃會(huì)選擇非聚集索引掃描？因?yàn)榇颂幬覀儾樵冏侄蝞ame，非聚集索引能夠覆蓋所需要的數(shù)據(jù)。如果是非聚集索引不能覆蓋的數(shù)據(jù)呢?例如，我們將SELECT改為SELECT*再來看看。執(zhí)行查詢語句如下：SELECT*FROMPerson執(zhí)行計(jì)劃如下：很明顯，返回結(jié)果所包括的記錄太多，用非聚集索引反而不合算。因此使用了聚集索引。如果此時(shí)我們刪除聚集索引，在執(zhí)行select*查詢會(huì)如何？刪除聚集索引：DROPINDEXPerson.IX_Id此時(shí)，Person表只有非聚集索引。這時(shí)，執(zhí)行查詢?nèi)缦拢篠ELECT*FROMPerson執(zhí)行計(jì)劃如下：此時(shí)沒有聚集索引，執(zhí)行計(jì)劃并沒有選擇非聚集索引。所以只有使用全表掃描。1-05、書簽查找當(dāng)查詢優(yōu)化器使用非聚集索引進(jìn)行查找時(shí)，當(dāng)你使用的sql查詢條件和select返回的列沒有完全包含在索引列中時(shí)就會(huì)發(fā)生書簽查找。下面來看一個(gè)書簽查找的示例：SELECT*FROMPersonWHEREName='胖胖'--Name列有非聚集索引執(zhí)行計(jì)劃如下：上面的過程可以理解為：首先通過非聚集索引找到所求的行，但這個(gè)索引并不包含所有的列，因此還要額外去基本表中找到這些列，因此要進(jìn)行鍵查找，如果基本表是以堆進(jìn)行組織的，那么這個(gè)鍵查找(KeyLookup)就會(huì)變成RID查找(RIDLookup),鍵查找和RID查找統(tǒng)稱為書簽查找。不過有時(shí)當(dāng)非聚集索引返回的行數(shù)過多時(shí)，SQLServer可能會(huì)選擇直接進(jìn)行聚集索引掃描了。2、流聚合操作2-01、流聚合流聚合：在相應(yīng)排序的流中，計(jì)算多組行的匯總值。所有的聚合函數(shù)(如COUNT(),MAX())都會(huì)有流聚合的出現(xiàn)，但是其不會(huì)消耗IO，只有消耗CPU。開啟IO讀寫，該選項(xiàng)幫助我們獲得在語句執(zhí)行時(shí)，所發(fā)生IO數(shù)（頁讀/寫）：SETSTATISTICSIOON執(zhí)行以下查詢語句：SELECTMAX(Age)FROMPerson查詢執(zhí)行計(jì)劃如下：消息顯示如下：2-02、標(biāo)量聚合計(jì)算標(biāo)量：根據(jù)行中的現(xiàn)有值計(jì)算新值。比如COUNT()函數(shù)，多一行，行數(shù)就加1咯。除MIN和MAX函數(shù)之外，其他聚合函數(shù)都會(huì)同時(shí)出現(xiàn)標(biāo)量聚合和流聚合兩個(gè)操作。執(zhí)行查詢語句如下：SELECTCOUNT(*)FROMPerson執(zhí)行查詢計(jì)劃如下：2-03、散列聚合（哈希匹配）散列聚合(哈希匹配)：為了解決流聚合的不足,應(yīng)對(duì)大數(shù)據(jù)的操作而產(chǎn)生的。對(duì)于數(shù)據(jù)量比較大時(shí)，執(zhí)行GROUPBY操作，SQLServer選擇的是哈希匹配。在內(nèi)存中建立好散列表后，會(huì)按照groupby后面的值作為鍵，然后依次處理集合中的每條數(shù)據(jù)，當(dāng)鍵在散列表中不存在時(shí)，向散列表添加條目，當(dāng)鍵已經(jīng)在散列表中存在時(shí)，按照規(guī)則（規(guī)則是聚合函數(shù)，比如Sum,avg什么的）計(jì)算散列表中的值（Value）。執(zhí)行查詢語句如下：SELECTHeight,COUNT(Id)FROMPerson--查出各身高的認(rèn)輸GROUPBYHeight執(zhí)行查詢計(jì)劃如下：2-04、排序排序：資源消耗較高的操作。對(duì)于數(shù)據(jù)量比較小時(shí)，執(zhí)行GROUPBY操作會(huì)使用SORT。注意：Sort操作是占用內(nèi)存的操作，當(dāng)內(nèi)存不足時(shí)會(huì)占用Tempdb(SQLServer總是會(huì)在Sort操作和散列匹配中選擇成本最低的)。調(diào)優(yōu)時(shí)為排序字段建立索引可以更好的提高查詢效率，如果想按照添加時(shí)間倒序，那么OrderById(Identity)可以達(dá)到同樣的結(jié)果，而且效率還更高。添加一個(gè)Person2表，數(shù)據(jù)量100行以內(nèi)，執(zhí)行查詢語句如下：SELECT*INTOPerson2FROMPersonWHEREId<100當(dāng)數(shù)據(jù)量比較少時(shí)，執(zhí)行GROUPBY會(huì)使用SORT，例如執(zhí)行查詢Person2表如下：SELECTHeight,COUNT(Id)FROMPerson2--只是表換成了數(shù)據(jù)量比較少的表GROUPBYHeight執(zhí)行計(jì)劃如下：3、多表連接當(dāng)多表連接時(shí)(包括書簽查找，索引之間的連接)，SQLServer會(huì)采用三類不同的連接方式：循環(huán)嵌套連接，合并連接，散列連接。這幾種連接格式有適合自己的場(chǎng)景，不存在哪個(gè)更好的說法。新建新聞表Article、欄目表Col如下：CREATETABLEArticle( IDINTIDENTITY(1,1)notnull, TitleVARCHAR(100), BodyVARCHAR(100), ColIDINT)ALTERTABLEArticleADDCONSTRAINTPK_ColumnPRIMARYKEY(ID)CREATETABLECol( IDINTIDENTITY(1,1)notnull, NameVARCHAR(100))ALTERTABLEColADDCONSTRAINTPK_ColumnPRIMARYKEY(ID)3-01、嵌套循環(huán)嵌套循環(huán)：對(duì)于頂部（外部）輸入的每一行，掃描底部（內(nèi)部）輸入，然后輸出匹配的行。先看一個(gè)簡(jiǎn)單innerjoin查詢語句如下：SELECT*FROMColt1INNERJOINArticlet2ONt1.ID=t2.ColID執(zhí)行計(jì)劃如下：根據(jù)嵌套循環(huán)的原理不難看出，由于外部輸入是掃描，內(nèi)部輸入是查找，本例中外部掃描是Article表，內(nèi)部查找是Col表。當(dāng)兩個(gè)Join的表外部輸入結(jié)果集比較小，而內(nèi)部輸入所查找的表非常大時(shí)，查詢優(yōu)化器更傾向于選擇循環(huán)嵌套方式。3-02、合并連接不同于循環(huán)嵌套的是，合并連接是從每個(gè)表僅僅執(zhí)行一次訪問。從這個(gè)原理來看，合并連接要比循環(huán)嵌套要快了不少。從合并連接的原理不難想象，首先合并連接需要雙方有序，并且要求Join的條件為等于號(hào)。因?yàn)閮蓚€(gè)輸入條件已經(jīng)有序，所以從每一個(gè)輸入集合中取一行進(jìn)行比較，相等的返回，不相等的舍棄，從這里也不難看出Mergejoin為什么只允許Join后面是等于號(hào)。執(zhí)行查詢語句如下：SELECT*FROMColt1INNERJOINArticlet2ONt1.ID=t2.ColIDOPTION(MERGEjoin)執(zhí)行查詢計(jì)劃如下：如果輸入數(shù)據(jù)的雙方無序，則查詢分析器不會(huì)選擇合并連接，我們也可以通過索引提示強(qiáng)制使用合并連接，為了達(dá)到這一目的，執(zhí)行計(jì)劃必須加上一個(gè)排序步驟來實(shí)現(xiàn)有序。這也是上述SQL語句為什么要加OPTION(MERGEjoin)的原因。上述對(duì)Article表的ColID列進(jìn)行了排序。3-03、哈希連接散列連接同樣僅僅只需要只訪問1次雙方的數(shù)據(jù)。散列連接通過在內(nèi)存中建立散列表實(shí)現(xiàn)。這比較消耗內(nèi)存，如果內(nèi)存不足還會(huì)占用tempdb。但并不像合并連接那樣需要雙方有序。要實(shí)現(xiàn)哈希連接，不能把兩個(gè)表的聚集索引建在ID列，否則不會(huì)采用哈希連接。刪除Col主鍵、刪除聚集索引，創(chuàng)建聚集索引，然后重設(shè)主鍵ALTERTABLEColDROPCONSTRAINTPK_Col--刪除主鍵DROPINDEXCol.PK_Col--刪除聚集索引CREATECLUSTEREDINDEXPK_ColONCol(Name)--創(chuàng)建聚集索引刪除Article主鍵、刪除聚集索引，創(chuàng)建聚集索引，然后重設(shè)主鍵ALTERTABLEArticleDROPCONSTRAINTPK_Article--刪除主鍵DROPINDEXArticle.PK_Article--刪除聚集索引CREATECLUSTEREDINDEXPK_ArticleONArticle(Title)--創(chuàng)建聚集索引執(zhí)行以下查詢語句：SELECT*FROMColt1INNERJOINArticlet2ONt1.ID=t2.ColID執(zhí)行計(jì)劃如下：要?jiǎng)h除掉聚集索引，否則兩個(gè)有序輸入SQLServer會(huì)選擇代價(jià)更低的合并連接。SQLServer利用兩個(gè)上面的輸入生成哈希表。通常來說，所求數(shù)據(jù)在其中一方或雙方?jīng)]有排序的條件達(dá)成時(shí)，會(huì)選用哈希匹配。4、并行當(dāng)多個(gè)表連接時(shí)，SQLServer還允許在多CPU或多核的情況下允許查詢并行，這樣無疑提高了效率。5、擴(kuò)展知識(shí)點(diǎn)：setstatisticstimeon的作用顯示分析、編譯和執(zhí)行各語句所需的毫秒數(shù)。執(zhí)行查詢語句如下：SETSTATISTICSTIMEON執(zhí)行查詢語句如下：SELECT*FROMColt1INNERJOINArticlet2ONt1.ID=t2.ColID消息顯示如下：C