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

如何提高查询性能减少IO减少CPU运算使用内存索引、提示、查询优化器和执行计划提示是指定的强制选项或策略，由 SQL Server 查询处理器针对 SELECT、INSERT、UPDATE 或 DELETE 语句执行。提示将覆盖查询优化器可能为查询选择的任何执行计划。因为 SQL Server 查询优化器通常会为查询选择最优执行计划，因此我们建议，只有在万般无奈的情况下才由经验丰富的开发人员和数据库管理员使用 join_hint、query_hint 和 table_hint。--创建表Users 并主键约束聚集索引Create table Users( UserID int identity, UserName nvarchar(50), Age int, Gender bit, CreateTime datetime,constraint PK_UserID primary key clustered (UserID))--在UserName创建非聚集索引IX_UserNamecreate index IX_UserName on Users(UserName)--插入示例数据insert into Users(UserName,Age,Gender,CreateTime)select NBob,20,1,2012-5-1union allselect NJack,23,0,2012-5-2union allselect NRobert,28,1,2012-5-3union allselect NJanet,40,0,2012-5-9union allselect NMichael,22,1,2012-5-2union allselect NLaura,16,1,2012-5-1union allselect NAnne,36,1,2012-5-7--查询1:select UserID,UserName from Users with(index(IX_UserName)) where UserName=Robertselect UserID,UserName from Users where UserName=Robert--查询2:select UserID,UserName,Age from Users with(index(IX_UserName)) where UserName=Robertselect UserID,UserName,Age from Users where UserName=Robert--查询3select * from Users with(index(IX_UserName)) where UserName=Robertselect * from users where UserName=Robert如何书写高效SQL需要多少检索多少select * from users;(聚集索引中检索数据)select userid,username from users; (非聚集索引中检索数据,io少) 快where条件中不要使用 orwhere id=1 or id=5?Select 1 id Union allSelect 2 id?避免使用 like ‘%abcd%’select top 10 UserID, CreateTimefrom Users where UserName like %abcd%select top 10 UserID,CreateTime from Users where UserName like abcd%select top 10 UserID, CreateTimefrom Users where UserName like %abcd% and CreateTime =2010-01-31 12:12:16.297 如不能避免，一定要在其它列上有范围的限定分词也个解决CONTAINS(names,免考*)避免或简化order by索引列上order by建议在代码内部排序改写 inselect userid,username from userswhere UserID in (select UserID from orderform where OrderTpye=5)select a.userid,a.username from users a inner join orderform b on a.userid=b.useridwhere b. OrderTpye=5select a.userid,a.username from users a inner join (select UserID from orderform where Or