数据库-培训-如何提高查询性能剖析.docx

如何提高查询性能减少IO减少CPU运算使用内存索引、提示、查询优化器和执行计划提示是指定的强制选项或策略，由 SQL Server 查询处理器针对 SELECT、INSERT、UPDATE 或 DELETE 语句执行。提示将覆盖查询优化器可能为查询选择的任何执行计划。因为 SQL Server 查询优化器通常会为查询选择最优执行计划，因此我们建议，只有在万般无奈的情况下才由经验丰富的开发人员和数据库管理员使用 join_hint、query_hint 和 table_hint。--创建表Users 并主键约束聚集索引Createtable Users(UserIDintidentity,UserNamenvarchar(50), Age int, Gender bit,CreateTimedatetime,constraintPK_UserIDprimarykeyclustered (UserID))--在UserName创建非聚集索引IX_UserNamecreateindexIX_UserNameon Users(UserName)--插入示例数据insertinto Users(UserName,Age,Gender,CreateTime)selectNBob,20,1,2012-5-1unionallselectNJack,23,0,2012-5-2unionallselectNRobert,28,1,2012-5-3unionallselectNJanet,40,0,2012-5-9unionallselectNMichael,22,1,2012-5-2unionallselectNLaura,16,1,2012-5-1unionallselectNAnne,36,1,2012-5-7--查询1:selectUserID,UserNamefrom Users with(index(IX_UserName))whereUserName=RobertselectUserID,UserNamefrom Users whereUserName=Robert--查询2:selectUserID,UserName,Agefrom Users with(index(IX_UserName))whereUserName=RobertselectUserID,UserName,Agefrom Users whereUserName=Robert--查询3select*from Users with(index(IX_UserName))whereUserName=Robertselect*from users whereUserName=Robert如何书写高效SQL需要多少检索多少select*fromusers;(聚集索引中检索数据)select userid,username from users;(非聚集索引中检索数据,io少) 快where条件中不要使用 orwhere id=1 orid=5?Select1idUnion allSelect 2 id?避免使用 like ‘%abcd%’selecttop 10 UserID,CreateTimefromUserswhereUserNamelike%abcd%selecttop 10 UserID,CreateTimefromUserswhereUserNamelikeabcd%selecttop 10 UserID, CreateTimefromUserswhereUserNamelike%abcd%andCreateTime=2010-01-31 12:12:16.297如不能避免，一定要在其它列上有范围的限定分词也个解决CONTAINS(names,免考*)避免或简化order by索引列上orderby建议在代码内部排序改写 inselectuserid,usernamefrom userswhereUserIDin(selectUserIDfromorderformwhereOrderTpye=5)selecta.userid,a.usernamefrom users ainnerjoinorderformb ona.userid=b.useridwhere b.OrderTpye=5selecta.userid,a.usernamefrom users ainnerjoin(selectUserIDfromorderformwhereOrderTpye=5) b ona.userid=b.useridselecta.userid,a.usernamefrom users ainnerjoinorderformb ona.userid=b.user