数据库-培训-如何提高查询性能

如何提高查询性能减少IO减少CPU运算使用内存1.索引、提示、查询优化器和执行计划提示是指定的强制选项或策略，由SQLServer查询处理器针对SELECT、INSERT、UPDATE或DELETE语句执行。提示将覆盖查询优化器可能为查询选择的任何执行计划。因为SQLServer查询优化器通常会为查询选择最优执行计划，因此我们建议，只有在万般无奈的情况下才由经验丰富的开发人员和数据库管理员使用join_hint、query_hint和table_hint。--创建表Users并主键约束聚集索引CreatetableUsers(UserIDintidentity,UserNamenvarchar(50),Ageint,Genderbit,CreateTimedatetime,constraintPK_UserIDprimarykeyclustered(UserID))--在UserName创建非聚集索引IX_UserNamecreateindexIX_UserNameonUsers(UserName)--插入示例数据insertintoUsers(UserName,Age,Gender,CreateTime)selectN'Bob',20,1,'2012-5-1'unionallselectN'Jack',23,0,'2012-5-2'unionallselectN'Robert',28,1,'2012-5-3'unionallselectN'Janet',40,0,'2012-5-9'unionallselectN'Michael',22,1,'2012-5-2'unionallselectN'Laura',16,1,'2012-5-1'unionallselectN'Anne',36,1,'2012-5-7'--查询1:selectUserID,UserNamefromUserswith(index(IX_UserName))whereUserName='Robert'selectUserID,UserNamefromUserswhereUserName='Robert'--查询2:selectUserID,UserName,AgefromUserswith(index(IX_UserName))whereUserName='Robert'selectUserID,UserName,AgefromUserswhereUserName='Robert'--查询3select*fromUserswith(index(IX_UserName))whereUserName='Robert'select*fromuserswhereUserName='Robert'2.如何书写高效SQL2.1.需要多少检索多少select*fromusers;(聚集索引中检索数据)selectuserid,usernamefromusers;(非聚集索引中检索数据,io少)快2.2.where条件中不要使用orwhereid=1orid=5?Select1idUnionallSelect2id?2.3.避免使用like‘%abcd%’selecttop10UserID,CreateTimefromUserswhereUserNamelike'%abcd%'selecttop10UserID,CreateTimefromUserswhereUserNamelike'abcd%'selecttop10UserID,CreateTimefromUserswhereUserNamelike'%abcd%'andCreateTime='2010-01-3112:12:16.297'如不能避免，一定要在其它列上有范围的限定分词也个解决CONTAINS(names,'免考*')2.4.避免或简化orderby索引列上orderby建议在代码内部排序2.5.改写inselectuserid,usernamefromuserswhereUserIDin(selectUserIDfromorderformwhereOrderTpye=5)selecta.userid,a.usernamefromusersainnerjoinorderformbona.userid=b.useridwhereb.OrderTpye=5selecta.userid,a.usernamefromusersainnerjoin(selectUserIDfromorderformwhereOrderTpye=5)bona.userid=b.useridselecta.userid,a.usernamefromusersainnerjoinorderformbona.userid=b.useridandb.OrderTpye=52.6.Where子句在索引列上避免使用函数selectCOUNT(*)fromorderformwhereDATEPART(YEAR,PostTime)='2010'selectCOUNT(*)fromorderformwhereDATEDIFF(YEAR,postTime,'2010')=0selectCOUNT(*)fromorderformwherepostTime='2010'andpostTime'2011'2.7.union/EXCEPT或INTERSECTselect*fromaunionallselect*frombselect*froma【hash】unionselect*fromboptin（MERGE|HASH|CONCATunion）合并、哈希或串联2.8.合理使用topNTopN可以减少大量IO提高查询效率如:判断是否存在记录快速返回指定行数2.9.避免使用游标DECLARE@iintselectReturnsCode,NoticeCode,AuditTimeINTO#tempfromReturnsFormwhereformCode=@formcodeandstatus=1andReturnsType=0orderbyAuditTimeascSELECT@i=@@ROWCOUNT-------------------------declare@codevarchar(20)WHILE(@i0)BEGINSELECTTOP1@code=ReturnsCodeFROM#tempORDERBYAuditTimeASC------------------------------------------------------select*fromReturnsDetailwhereReturnsCode=@code------------------------------------------------------DELETEFROM#tempWHEREReturnsCode=@codeSET@i=@i-1END2.10.合理使用临时表变量和临时表关于临时表和表变量的用法,需要注意:A如果语句很复杂,连接太多,可以考虑用临时表和表变量分步完成,B如果需要多次用到一个大表的同一部分数据,考虑用临时表和表变量暂存这部分数据,C如果需要合并多个表的数据,形成一个结果,可以考虑用临时表和表变量分步汇总这多个表的数据,D为了避免使用游标而使用关于临时表和表变量的选择,很多说法是表变量在内存,速度快,应该首选表变量,但是在实际使用中发现,这个选择需要考虑放在临时表的数据量,在数据量较多的情况下,临时表的速度反而更快2.11.CTE(common_table_expression)“Withtableas……”指定临时命名的结果集，这些结果集称为公用表表达式(CTE)。CTE之后必须跟随引用部分或全部CTE列的单条SELECT、INSERT、UPDATE或DELETE语句。也可以在CREATEVIEW语句中将CTE指定为视图中SELECT定义语句的一部分公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。-CreateanEmployeetable.CREATETABLEdbo.MyEmployees(EmployeeIDsmallintNOTNULL,FirstNamenvarchar(30)NOTNULL,LastNamenvarchar(40)NOTNULL,Titlenvarchar(50)NOTNULL,DeptIDsmallintNOTNULL,ManagerIDintNULL,CONSTRAINTPK_EmployeeIDPRIMARYKEYCLUSTERED(EmployeeIDASC));--Populatethetablewithvalues.INSERTINTOdbo.MyEmployeesVALUES(1,N'Ken',N'Sánchez',N'ChiefExecutiveOfficer',16,NULL),(273,N'Brian',N'Welcker',N'VicePresidentofSales',3,1),(274,N'Stephen',N'Jiang',N'NorthAmericanSalesManager',3,273),(275,N'Michael',N'Blythe',N'SalesRepresentative',3,274),(276,N'Linda',N'Mitchell',N'SalesRepresentative',3,274),(285,N'Syed',N'Abbas',N'PacificSalesManager',3,273),(286,N'Lynn',N'Tsoflias',N'SalesRepresentative',3,285),(16,N'David',N'Bradley',N'MarketingManager',4,273),(23,N'Mary',N'Gibson',N'MarketingSpecialist',4,16);WITHDirectReports(ManagerID,EmployeeID,Title,EmployeeLevel)AS(SELECTManagerID,EmployeeID,Title,0ASEmployeeLevelFROMdbo.MyEmployeesWHEREManagerIDISNULLUNIONALLSELECTe.ManagerID,e.EmployeeID,e.Title,EmployeeLevel+1FROMdbo.MyEmployeesASeINNERJOINDirectReportsASdONe.ManagerID=d.EmployeeID)SELECTManagerID,EmployeeID,Title,EmployeeLevelFROMDirectReports--WHEREEmployeeLevel=2;显示经理以及向经理报告的雇员3.分页查询3.1.sql2012~2016--2012select*fromsysobjectsorderbycrdateoffset5rows--跳过前五行fetchnext5rowsonly;仅返回5行记录--2005/2008withtempas(select*,row_number()over(orderbycrdate)asrowunmberfromsysobjects)select*fromtempwhererowunmberbetween6and10;--2000--执行计划中显示性能差，原因是crdate上没有索引造成。declare@pageNOintset@pageNO=2Selecttop5*FromsysobjectsWherecrdate=(selectmax(a.crdate)from(selecttop((@pageNO-1)*5+1)crdatefromsysobjectsorderbycrdate)a)orderbycrdate3.