2024數(shù)據(jù)庫SQL優(yōu)化設(shè)計(jì)

目錄TOC\o"1-3"\h\u173931 491191.1 431043 430400 4117061.2 414957、 432670、 511679、 511406、 620445、 65329、 619391、 677502 6262862.1 670862.2 7101062.3 7172492.4 712984、 722714、 84879、 914582.5 95331、 93716 922437 95974 93573、 1013365、 11249732.6 12322332.7 13280582.8 13313373 144563.1 14240573.2 14162333.3 1646694CPU 17268254.1 1768434.2 1715380、sp_executesql 1727305、 19159954.3 197677、 20167684.4 20225454.5、In 20316225 2023644 2056702、SQL語句加Nolock 2023062 21265756 21270637SQLServer服務(wù)器系統(tǒng)參數(shù)配置與性能優(yōu) 21316737.1、CPU 21296187.2 21251497.3 21127727.4 22217688DB 22188209SQL 22219559.1、SQL 22106579.2 2225383、 2231336、 2332667、子查詢優(yōu)化方法一（子查詢合并 233021、非關(guān)聯(lián)子查詢優(yōu)化-- 2324009、關(guān)聯(lián)子查詢優(yōu)化-- 2426187、關(guān)聯(lián)子查詢優(yōu)化-- 2527343、子查詢優(yōu)化—-Update 2630424、子查詢優(yōu)化—- 2627377、子查詢優(yōu)化—-物化視圖（索引視圖 27223349.3 2817662、 2932376、 29147949.4、WithAs 30SQLServer18KB11個(gè)邏Feeling字段非常大，就會(huì)導(dǎo)致每個(gè)存儲(chǔ)塊存、boolint類型字段。、、縱向拆分方法：把不需要用于查詢的大字段，放到另外一個(gè)新建的附屬表中，如、分區(qū)，但問題是，是不是所有的查詢都能加入分區(qū)條件呢。只要進(jìn)行了表分區(qū)，那么SQLSQL都要加上分區(qū)條件，除非個(gè)別的匯總，統(tǒng)計(jì)類的SQL。、、IO讀取IO就會(huì)非常少，加快了查詢速度。、CPUCPU消耗不大，IO消耗很大系統(tǒng)中適BIID（SartTme時(shí)間（EndTime2個(gè)列誰的選擇性高呢？在where條件中用函數(shù)會(huì)導(dǎo)致表掃描，所以應(yīng)該設(shè)計(jì)成標(biāo)識(shí)字段，如IsEditFeeling(bit)，ID或車ID。但要注意索引列的可選擇性。、、、starttime列。Select*fromtwhereobject_id>=20andobject_id<2000andobject_type=’TABLE’;Createindexidx_id_typeon、、、SQL的排序是基于索引列進(jìn)行的，那么不需要再重新進(jìn)行排序，反之，系統(tǒng)會(huì)在temp庫中建立表變量或臨時(shí)表，然后在這個(gè)表變量或Createindexidx_tont(col1desc,col2asc)Select*fromtorderbycol1desc,col2ascSQLServer執(zhí)行時(shí)間CPU時(shí)間2182毫秒，占用時(shí)間231SQLServer分析和編譯時(shí)間CPU時(shí)間0毫秒，占用時(shí)間0SQLServer執(zhí)行時(shí)間CPU時(shí)間0毫秒，占用時(shí)間2SQLServer分析和編譯時(shí)間CPU時(shí)間0毫秒，占用時(shí)間010倍，而且數(shù)據(jù)表CREATENONCLUSTEREDINDEX[IX_Auto_Serial]ON[dbo].[Auto][Serial]ASC,[RowStatus]ASCWITH(ONLINE=通索引相反的是，online建索引的動(dòng)作是反過來被更新操作阻塞。AutoBoughtCityAuto表進(jìn)行查三大表連接的概要說明：1、NestedLoopsJoin。2、HashJoin。3、MergeSortJoinNestedLoopsJoin驅(qū)動(dòng)結(jié)果集的條數(shù)決定被驅(qū)動(dòng)表的訪問次數(shù)HashJoin10MergeSortJoinHashJoinHashJoin性能與驅(qū)動(dòng)順序有關(guān)（和NL相似MergeSortJoinNestedLoopsJoinHashJoin1OLAP2SQLHashJoin。3HashJoin1（看返回量）23PGAHashMergeSortJoin1（兩表限制條件有索引MergeSortJoin2（2ONAjoinBonA.CID=B.CBHash匹配。(1row(s)'Worktable'0000次，lob次，lob0次，lob0'EvaluationPurposeTerms'113833次，lob0次，lob0次，lob0'Evaluation'1110280次，lob邏輯讀取0次，lob物理讀取0次，lob預(yù)讀0次。(1row(s)SQLServer執(zhí)行時(shí)間CPU140155(1row(s)'EvaluationPurposeTerms'0163800lob0次，lob0次，lob0'Evaluation'12000次，lob0次，lob0次，lob0SQLServer執(zhí)行時(shí)間CPU04QualityProblem是一個(gè)數(shù)據(jù)表，QualityProblemDictionary是一個(gè)字典表，字典表的數(shù)據(jù)基本Hint中指出不要用索引掃描，要采用表掃描。代碼：with(index(0))SELECT[QualityId],[ItemId]AS[ID],d.Name,d.FullName,d.FullPath,d.Level,d.ParentIdFROM[QualityProblem]aspwith(nolock)join[QualityProblemDictionary]asdwith(nolock)ond.Id =p.ItemIdwherep.QualityId=(7row(s)lob物理讀取0次，lob預(yù)讀00次，lob預(yù)讀0SQLServer執(zhí)行時(shí)間CPU時(shí)間15毫秒，占用時(shí)間23asdwith(index(0),nolock)on =(7row(s)表'Worktable'。掃描計(jì)數(shù)0，邏輯讀取0次，物理讀取0次，預(yù)讀0次，lob邏輯讀取0次，lob物理讀取0lob預(yù)讀0lob物理讀取0次，lob預(yù)讀00次，lob預(yù)讀0SQLServer執(zhí)行時(shí)間CPU時(shí)間0毫秒，占用時(shí)間2CPUCPU時(shí)間也是包括編譯時(shí)間和運(yùn)行時(shí)間。SQLServer分析和編譯時(shí)間CPU時(shí)間0毫秒，占用時(shí)間0SQLServer執(zhí)行時(shí)間CPU時(shí)間2668毫秒，占用時(shí)間786SQLCPU的，SQLServer在生成查詢計(jì)劃之后，會(huì)將SQL時(shí)不用再重新生成一個(gè)全新的、sp_executesql普通的即席SQLsql、sp_executesql3sql查詢計(jì)劃就會(huì)被緩存，但也有、CPUDMV中查到編譯數(shù)過高，原因是開發(fā)人員為線上跟蹤代SQL中都加了一條注釋，該UrlUrl自然也SQL的查詢計(jì)劃無法重用。、InDeclare@User1int;Declare@User2int;Declare@User3int;Declare@User4int;declare@User5WhereuserId2、SQL語句加NolockSQLServer服務(wù)器系統(tǒng)參數(shù)配置與性能優(yōu)、CPU\hDBSQL、SQLSQL分析技巧，SQL33WhereIn/NotIn，Exists/Notin子查詢，有沒有外連接，有沒有在列上面有函數(shù)導(dǎo)致不能走索引，比如：wherelen(feeling)>0、、、子查詢優(yōu)化方法一（子查詢合并、非關(guān)聯(lián)子查詢優(yōu)化--unnesting evl.[AutoSeries]=@idandevl.EvaluationIDin(selectEvaluationIDfromEvaluationPurposeTermseptwith(nolock)whereept.PurposeID=1)INNERJOINEvaluationPurposeTermseptONevl.EvaluationID=ept.EvaluationID evl.[AutoSeries]=@idandept.PurposeID=1連接方法和連接順序可能被有效使用，使得查詢語句的層次盡可能的減少。在子查nnW條記WAnB返回0A返回1WBABA10A(，則AB執(zhí)行1W 修改方法：SQLProduct_SpecINNERJOINdbo.Product_Specprod_SpecONprod_Spec.SpecId=evl.AutoModelWHEREprod_Spec.SyearId=452ANDSpecState=(銷售狀態(tài))、關(guān)聯(lián)子查詢優(yōu)化--and((evl.[DrivenKiloms]>5000andevl.[DrivenKiloms]<=20000)orevl.EvaluationIDin(selectEvaluationIDfromEvaluationAppendingwith(nolock)whereDrivenKilometers>5000andDrivenKilometers<20000andRowStatus=0andEvaluationAppendingIDin(selectMAX(EvaluationAppendingID)asEvaluationAppendingIDfromEvaluationAppendingwhereRowStatus=0groupbyEvaluationID))))as上面的邏輯含義是口碑的驅(qū)動(dòng)公里數(shù)>5000And<=20000，或者最后一條追加口碑的驅(qū)動(dòng)公里數(shù)>5000And<=20000，我們可以一次性把所有口碑的最后一條追加口碑并且是符合驅(qū)動(dòng)公里數(shù)>5000And<20000CTE中，然后用這個(gè)臨時(shí)表或CTE與口碑表進(jìn)行關(guān)聯(lián)查詢，修改后的代碼如下：;WITHAppendEvalIDCTEAS( ( ROW_NUMBER()OVER(PARTITIONEvaluationIDORDERBYEvaluationAppendingIDDESC)ASRowNum RowStatus=0) T.RowNum=1ANDDrivenKilometers>5000ANDDrivenKilometers<=20000INNERJOINAppendEvalIDCTEevlAppendONevl.EvaluationID=evlAppend.EvaluationID evl.[DrivenKiloms]>5000ANDevl.[DrivenKiloms]<=20000、關(guān)聯(lián)子查詢優(yōu)化--，CBOSQLServer盡可能早的過濾掉無用的數(shù)據(jù)，從而提升查詢性能。SQL語句中，OR條件上面有一個(gè)為子查詢，并且子查詢上的表與源表不同，這個(gè)時(shí)候就unionOR或者你發(fā)現(xiàn)執(zhí)行計(jì)劃中的filteroror后面跟上子查詢(Exists)的;WITHAppendEvalIDCTEAS(SELECTEvaluationID ( ROW_NUMBER()OVER(PARTITIONBYEvaluationIDORDEREvaluationAppendingIDDESC)ASRowNumEvaluationID,DrivenKilometers EvaluationAppendingwith(nolock) RowStatus=0)WHERET.RowNum=1ANDDrivenKilometers>5000ANDDrivenKilometers<=,AllEvalIDCTEASSELECTHelpfulCount,LastAppend,Grade,AppendCount,EvaluationIDFROM evl.Grade,evl.AppendCount,evl.EvaluationIDFROMEvaluationINNERJOIN[Auto]atWITH(NOLOCK)ONat.AutoID=evl.AutoIDWHEREevl.[AutoSeries]=@idANDevl.[DrivenKiloms]>5000ANDevl.[DrivenKiloms]<= evl.Grade,evl.AppendCount,evl.EvaluationIDFROMEvaluationINNERJOIN[Auto]atWITH(NOLOCK)ONat.AutoID=INNERJOINAppendEvalIDCTEevlAppendONevl.EvaluationID=evlAppend.EvaluationIDWHEREevl.[AutoSeries]=@id--ANDevl.[DrivenKiloms5000ANDevl.[DrivenKiloms)、子查詢優(yōu)化—-Updateupdatetable_1setscore=score+5whereuidin(\hselectuidfromtable_2wheresid=10);updateleftjoin、innerjoinsqljoinupdatetable_1t1innerjointable_2t2ont1.uid=t2.uidsetscore=score+5wheret2.sid=10;、子查詢優(yōu)化—-用于冗余存放EvaluationAppending表中當(dāng)前口碑的最后一條追加數(shù)據(jù)的、子查詢優(yōu)化—-物化視圖（索引視圖

dbo.Product_SeriespsWITH(NOLOCK)ONps.SeriesId=

OR(tfw.Syear=@spToYearANDtfw.Smonth<@spToMonth)OR(tfw.Syear>@spFromYearANDtfw.Syear<@spToYear)GROUPBYps.BrandId)sONs.BrandId=sbf.BrandId 1=1ANDsbf.CarType=1ANDsbf.CountryIdIN(1,2,3,4,5,6)(8行受影響lob物理讀取0次，lob預(yù)讀0表'Product_Series'。掃描計(jì)數(shù)1，邏輯讀取10次，物理讀取0次，預(yù)讀0次，lob邏輯讀取0次，lob物理讀取0次，lob預(yù)讀0次。SQLServer執(zhí)行時(shí)間CPU時(shí)間15毫秒，占用時(shí)間15CREATEVIEWv_dxp_SaleData_Cleaned_CountWITHSCHEMABINDINGASSELECTid,SeriesId,SCount,(Syear*100+Smonth))ASYearMonthFROMCREATEUNIQUECLUSTEREDINDEXv_dxp_SaleData_Cleaned_Count_Idx_IDONv_dxp_SaleData_Cleaned_Count(idASC);CREATENONCLUSTEREDINDEXv_dxp_SaleData_Cleaned_Count_Idx_YearMonth ;WITHCTEASSELECTps.BrandId,SUM(tfw.SCount)SCountFROM[Replication].dbo.v_dxp_SaleData_Cleaned_CounttfwWITH(NOLOCK)INNERJOINkoubei.dbo.Product_SeriespsWITH(NOLOCK)ON=SELECTsbf.BrandIdId,sbf.BrandNameName,sbf.PPH,sbf.DefectiveSampleCount, koubei.dbo.Stat_Brand_FaultsbfWITH(NOLOCK)INNERJOINCTEONCTE.BrandId=sbf.BrandId 1=1ANDsbf.CarType=1ANDsbf.CountryId>=1sbf.CountryId<=6--次，lob物理讀取0次，lob預(yù)讀0次。表'Product_Series'。掃描計(jì)數(shù)1，邏輯讀取10次，物理讀取0次，預(yù)讀0次，lob邏輯讀取0次，lob物理讀取0次，lob預(yù)讀0次。SQLServer執(zhí)行時(shí)間CPU時(shí)間0毫秒，占用時(shí)間5、2selectid,namefromlx_comjoinuserInfobetween5000000and這個(gè)SQL500Wname500W5000010行數(shù)據(jù)，那么前500W行數(shù)據(jù)的name，又要扔掉，不返給客戶端，這樣就形成極大的資源浪費(fèi)。WithTas(selectidfromlx_combetween5000000andselectid,namefromUserInfojoinTon;WITHAllCTEAS(SELECTevl.EvaluationID,ROW_NUMBER()OVER(ORDERevl.Gradedesc,evl.AppendCountdesc,evl.LastAppenddesc)ASRowNumFROMEvaluationevlWITH(NOLOCK)WHEREevl.[AutoSeries]=@id),evlCTE(SELECTTOP100PERCENTEvaluationID,RowNumFROMAllCTEWHE