2024数据库SQL优化设计

目录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服务器系统参数配置与性能优 21316737.1、CPU 21296187.2 21251497.3 21127727.4 22217688DB 22188209SQL 22219559.1、SQL 22106579.2 2225383、 2231336、 2332667、子查询优化方法一（子查询合并 233021、非关联子查询优化-- 2324009、关联子查询优化-- 2426187、关联子查询优化-- 2527343、子查询优化—-Update 2630424、子查询优化—- 2627377、子查询优化—-物化视图（索引视图 27223349.3 2817662、 2932376、 29147949.4、WithAs 30SQLServer18KB11个逻Feeling字段非常大，就会导致每个存储块存、boolint类型字段。、、纵向拆分方法：把不需要用于查询的大字段，放到另外一个新建的附属表中，如、分区，但问题是，是不是所有的查询都能加入分区条件呢。只要进行了表分区，那么SQLSQL都要加上分区条件，除非个别的汇总，统计类的SQL。、、IO读取IO就会非常少，加快了查询速度。、CPUCPU消耗不大，IO消耗很大系统中适BIID（SartTme时间（EndTime2个列谁的选择性高呢？在where条件中用函数会导致表扫描，所以应该设计成标识字段，如IsEditFeeling(bit)，ID或车ID。但要注意索引列的可选择性。、、、starttime列。Select*fromtwhereobject_id>=20andobject_id<2000andobject_type=’TABLE’;Createindexidx_id_typeon、、、SQL的排序是基于索引列进行的，那么不需要再重新进行排序，反之，系统会在temp库中建立表变量或临时表，然后在这个表变量或Createindexidx_tont(col1desc,col2asc)Select*fromtorderbycol1desc,col2ascSQLServer执行时间CPU时间2182毫秒，占用时间231SQLServer分析和编译时间CPU时间0毫秒，占用时间0SQLServer执行时间CPU时间0毫秒，占用时间2SQLServer分析和编译时间CPU时间0毫秒，占用时间010倍，而且数据表CREATENONCLUSTEREDINDEX[IX_Auto_Serial]ON[dbo].[Auto][Serial]ASC,[RowStatus]ASCWITH(ONLINE=通索引相反的是，online建索引的动作是反过来被更新操作阻塞。AutoBoughtCityAuto表进行查三大表连接的概要说明：1、NestedLoopsJoin。2、HashJoin。3、MergeSortJoinNestedLoopsJoin驱动结果集的条数决定被驱动表的访问次数HashJoin10MergeSortJoinHashJoinHashJoin性能与驱动顺序有关（和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预读0次。(1row(s)SQLServer执行时间CPU140155(1row(s)'EvaluationPurposeTerms'0163800lob0次，lob0次，lob0'Evaluation'12000次，lob0次，lob0次，lob0SQLServer执行时间CPU04QualityProblem是一个数据表，QualityProblemDictionary是一个字典表，字典表的数据基本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预读00次，lob预读0SQLServer执行时间CPU时间15毫秒，占用时间23asdwith(index(0),nolock)on =(7row(s)表'Worktable'。扫描计数0，逻辑读取0次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0lob预读0lob物理读取0次，lob预读00次，lob预读0SQLServer执行时间CPU时间0毫秒，占用时间2CPUCPU时间也是包括编译时间和运行时间。SQLServer分析和编译时间CPU时间0毫秒，占用时间0SQLServer执行时间CPU时间2668毫秒，占用时间786SQLCPU的，SQLServer在生成查询计划之后，会将SQL时不用再重新生成一个全新的、sp_executesql普通的即席SQLsql、sp_executesql3sql查询计划就会被缓存，但也有、CPUDMV中查到编译数过高，原因是开发人员为线上跟踪代SQL中都加了一条注释，该UrlUrl自然也SQL的查询计划无法重用。、InDeclare@User1int;Declare@User2int;Declare@User3int;Declare@User4int;declare@User5WhereuserId2、SQL语句加NolockSQLServer服务器系统参数配置与性能优、CPU\hDBSQL、SQLSQL分析技巧，SQL33WhereIn/NotIn，Exists/Notin子查询，有没有外连接，有没有在列上面有函数导致不能走索引，比如：wherelen(feeling)>0、、、子查询优化方法一（子查询合并、非关联子查询优化--unnesting evl.[AutoSeries]=@idandevl.EvaluationIDin(selectEvaluationIDfromEvaluationPurposeTermseptwith(nolock)whereept.PurposeID=1)INNERJOINEvaluationPurposeTermseptONevl.EvaluationID=ept.EvaluationID evl.[AutoSeries]=@idandept.PurposeID=1连接方法和连接顺序可能被有效使用，使得查询语句的层次尽可能的减少。在子查nnW条记WAnB返回0A返回1WBABA10A(，则AB执行1W 修改方法：SQLProduct_SpecINNERJOINdbo.Product_Specprod_SpecONprod_Spec.SpecId=evl.AutoModelWHEREprod_Spec.SyearId=452ANDSpecState=(销售状态)、关联子查询优化--and((evl.[DrivenKiloms]>5000andevl.[DrivenKiloms]<=20000)orevl.EvaluationIDin(selectEvaluationIDfromEvaluationAppendingwith(nolock)whereDrivenKilometers>5000andDrivenKilometers<20000andRowStatus=0andEvaluationAppendingIDin(selectMAX(EvaluationAppendingID)asEvaluationAppendingIDfromEvaluationAppendingwhereRowStatus=0groupbyEvaluationID))))as上面的逻辑含义是口碑的驱动公里数>5000And<=20000，或者最后一条追加口碑的驱动公里数>5000And<=20000，我们可以一次性把所有口碑的最后一条追加口碑并且是符合驱动公里数>5000And<20000CTE中，然后用这个临时表或CTE与口碑表进行关联查询，修改后的代码如下：;WITHAppendEvalIDCTEAS( ( ROW_NUMBER()OVER(PARTITIONEvaluationIDORDERBYEvaluationAppendingIDDESC)ASRowNum RowStatus=0) T.RowNum=1ANDDrivenKilometers>5000ANDDrivenKilometers<=20000INNERJOINAppendEvalIDCTEevlAppendONevl.EvaluationID=evlAppend.EvaluationID evl.[DrivenKiloms]>5000ANDevl.[DrivenKiloms]<=20000、关联子查询优化--，CBOSQLServer尽可能早的过滤掉无用的数据，从而提升查询性能。SQL语句中，OR条件上面有一个为子查询，并且子查询上的表与源表不同，这个时候就unionOR或者你发现执行计划中的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)、子查询优化—-Updateupdatetable_1setscore=score+5whereuidin(\hselectuidfromtable_2wheresid=10);updateleftjoin、innerjoinsqljoinupdatetable_1t1innerjointable_2t2ont1.uid=t2.uidsetscore=score+5wheret2.sid=10;、子查询优化—-用于冗余存放EvaluationAppending表中当前口碑的最后一条追加数据的、子查询优化—-物化视图（索引视图

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预读0表'Product_Series'。扫描计数1，逻辑读取10次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。SQLServer执行时间CPU时间15毫秒，占用时间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预读0次。表'Product_Series'。扫描计数1，逻辑读取10次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。SQLServer执行时间CPU时间0毫秒，占用时间5、2selectid,namefromlx_comjoinuserInfobetween5000000and这个SQL500Wname500W5000010行数据，那么前500W行数据的name，又要扔掉，不返给客户端，这样就形成极大的资源浪费。WithTas(selectidfromlx_combetween5000000andselectid,namefromUserInfojoinTon;WITHAllCTEAS(SELECTevl.EvaluationID,ROW_NUMBER()OVER(ORDERevl.Gradedesc,evl.AppendCountdesc,evl.LastAppenddesc)ASRowNumFROMEvaluationevlWITH(NOLOCK)WHEREevl.[AutoSeries]=@id),evlCTE(SELECTTOP100PERCENTEvaluationID,RowNumFROMAllCTEWHE