sqlserver 笔记性能优化和用法

A SQL 性能优化性能优化 1 1 查询的模糊匹配查询的模糊匹配 尽量避免在一个复杂查询里面使用 LIKE parm1 红色标识位置的百分号会导致 相关列的索引无法使用 最好不要用 解决办法 其实只需要对该脚本略做改进 查询速度便会提高近百倍 改进方法如下 a 修改前台程序 把查询条件的供应商名称一栏由原来的文本输入改为下拉列表 用户模糊输入供应商名称时 直接在前台就帮忙定位到具体的供应商 这样在调用后 台程序时 这列就可以直接用等于来关联了 b 直接修改后台 根据输入条件 先查出符合条件的供应商 并把相关记录保存在 一个临时表里头 然后再用临时表去做复杂关联 2 2 索引问题索引问题 在做性能跟踪分析过程中 经常发现有不少后台程序的性能问题是因为缺少合适索引 造成的 有些表甚至一个索引都没有 这种情况往往都是因为在设计表时 没去定义 索引 而开发初期 由于表记录很少 索引创建与否 可能对性能没啥影响 开发人 员因此也未多加重视 然一旦程序发布到生产环境 随着时间的推移 表记录越来越 多 这时缺少索引 对性能的影响便会越来越大了 这个问题需要数据库设计人员和开发人员共同关注 法则 不要在建立的索引的数据列上进行下列操作 避免对索引字段进行计算操作 避免在索引字段上使用 not 避免在索引列上使用 IS NULL 和 IS NOT NULL 避免在索引列上出现数据类型转换 避免在索引字段上使用函数 避免建立索引的列中使用空值 3 3 复杂操作复杂操作 部分 UPDATE SELECT 语句 写得很复杂 经常嵌套多级子查询 可以考虑适当拆 成几步 先生成一些临时数据表 再进行关联操作 4 4 在可以使用在可以使用 UNIONUNION ALLALL 的语句里 使用了的语句里 使用了 UNIONUNION UNION 查询出所有不重复的数据 可去重 UNION ALL 查询出所有数据 包括重复数据 UNION 因为会将各查询子集的记录做比较 故比起 UNION ALL 通常速度都会慢上许 多 一般来说 如果使用 UNION ALL 能满足要求的话 务必使用 UNION ALL 还有一种 情况大家可能会忽略掉 就是虽然要求几个子集的并集需要过滤掉重复记录 但由于 脚本的特殊性 不可能存在重复记录 这时便应该使用 UNION ALL 如 xx 模块的某个 查询程序就曾经存在这种情况 由于语句的特殊性 在这个脚本中几个子集的记录绝 对不可能重复 故可以改用 UNION ALL 5 5 避免在避免在 WHEREWHERE 子句中使用子句中使用 in in notnot in in oror 或者或者 havinghaving 可以使用 exist 和 not exist 代替 in 和 not in 可以使用表链接代替 exist Having 可以用 where 代替 如果无法代替可以分两步处理 例子 SELECT FROM ORDERS WHERE CUSTOMER NAME NOT IN SELECT CUSTOMER NAME FROM CUSTOMER 优化后 SELECT FROM ORDERS WHERE CUSTOMER NAME not exist SELECT CUSTOMER NAME FROM CUSTOMER 6 6 不要以字符格式声明数字 要以数字格式声明字符值不要以字符格式声明数字 要以数字格式声明字符值 日期同样 否则会使索引无效 产生全表扫描 例子使用 SELECT emp ename emp job FROM emp WHERE emp empno 7369 不要使用 SELECT emp ename emp job FROM emp WHERE emp empno 7369 7 7 对对 SelectSelect 语句的法则语句的法则 在应用程序 包和过程中限制使用 select from table 这种方式 看下面例子 使用 SELECT empno ename category FROM emp WHERE empno 7369 而不要使用 SELECT FROM emp WHERE empno 7369 8 8 慎重使用临时表可以极大的提高系统性能慎重使用临时表可以极大的提高系统性能 9 9 使用索引来更快地遍历表使用索引来更快地遍历表 缺省情况下建立的索引是非聚集索引 但有时它并不是最佳的 在非聚集索引下 数 据在物理上随机存放在数据页上 合理的索引设计要建立在对各种查询的分析和预测 上 一般来说 有大量重复值 且经常有范围查询 between i exec sp executesql s N con int output i int con output i select con 用exec declare i int s nvarchar 1000 set i 5 select s declare con int select con count 1 from sysobjects where ID rtrim i select con exec s 7 7 sqlsql 语句访问多台服务器数据语句访问多台服务器数据 跨多个服务器访问不同的表的方法一 先创建链接服务器TESTT 在MSSQL2008下不能直接修改链接服务器 TESTT 的RPC配置成TURE 可以通过语句 修改如下 USE master GO EXEC master dbo sp serveroption server N TESTT optname N rpc optvalue N true GO EXEC master dbo sp serveroption server N TESTT optname N rpc out optvalue N true GO 然后通过sql语句访问 select from TESTT ONLINE PAYMENT dbo TB USER 跨多个服务器访问不同的表的方法二 启用Ad Hoc Distributed Queries exec sp configure show advanced options 1 reconfigure exec sp configure Ad Hoc Distributed Queries 1 reconfigure 访问语句这样写 select from openrowset sqloledb 10 176 24 111 sa 123 ONLINE PAYMENT db o TB USER a 关闭Ad Hoc Distributed Queries exec sp configure Ad Hoc Distributed Queries 0 reconfigure exec sp configure show advanced options 0 reconfigure C 常用的常用的 sql 文文 1 1 分页分页 sqlsql 1 1 select top PageSize from UserInfo where Id select ISNULL MAX Id 0 from select top PageIndex 1 PageSize Id from UserInfo order by Id as T order by Id 1 2 select top PageSize from select ROW NUMBER over order by a Id as rowid a from UserInfo a as T where T rowid PageIndex 1 PageSize 2 2 分页存储过程分页存储过程 1 1 create procedure sp paging PageIndex int 页码 PageSize int 每页数据条数 PagesTotal int output 总页数 RecordTotal int output 总记录数 as begin 记录总数 select RecordTotal COUNT 1 from UserInfo a 总页数 set PagesTotal CEILING RecordTotal 1 0 PageSize 分页sql select top PageSize from select ROW NUMBER over order by a Id as rowid a from UserInfo a as T where T rowid PageIndex 1 PageSize end 删除存储过程 drop procedure sp paging 执行存储过程 declare records int declare pages int exec sp paging 1 10 pages output records output select records as 记录总数 pages as 总页数 结果 3 3 分页存储过程分页存储过程 2 2 分页 存储过程 create procedure sp paging2 TableName varchar 100 分页的表名称 TableColumn varchar 2000 要返回的字段名称 为全部返回 OrderColumn varchar 1000 排序 必填 不含 order by 字符 如id asc userid desc 必须指定asc或desc Where varchar 2000 筛选条件 不含where 如id 1 and len userid 10 PageIndex int 页码 PageSize int 每页数据条数 PagesTotal int output 总页数 RecordTotal int output 总记录数 as begin declare SqlCount nvarchar 2000 declare Sql varchar 8000 declare TempTop int set TempTop PageIndex 1 PageSize 判断查询条件是否为空 if ISNULL Where begin set Where where Where end else begin set Where end 记录总数 set SqlCount select RecordTotal count 1 from TableName Where print SqlCount exec sp executesql SqlCount N RecordTotal int output TableName varchar RecordTotal output TableName 总页数 set PagesTotal CEILING RecordTotal 1 0 PageSize 分页sql set Sql select top cast PageSize as varchar TableColumn from select ROW NUMBER over order by OrderColumn as rowid from TableName Where as T where T rowid cast TempTop as varchar print Sql print SqlCount exec Sql end 删除存储过程 drop procedure sp paging2 执行存储过程 declare records int declare pages int exec sp paging2 UserInfo UserId Email Address Birthday asc Birthday 1980 09 01 1 3 pages output records output select records as 记录总数 pages as 总页数 结果 4 4 分页存储过程分页存储过程 3 3 网上搜的 只能查询一个表 CREATE PROC sp paging nzperfect no mIss 高效通用分页存储过程 双向检索 敬告 适用于单一主键或存在唯一值列的表或视图 ps Sql语句为字节 调用时请注意传入参数及sql总长度不要超过指定范围 TableName VARCHAR 200 表名 FieldList VARCHAR 2000 显示列名 如果是全部字段则为 PrimaryKey VARCHAR 100 单一主键或唯一值键 Where VARCHAR 2000 查询条件不含 where 字符 如id 10 and len userid 9 Order VARCHAR 1000 排序不含 order by 字符 如id asc userid desc 必须指定asc或desc 注意当 SortType 3时生效 记住一定要在最后加上主键 否则会让你比较郁闷 SortType INT 排序规则1 正序asc 2 倒序desc 3 多列排序方法 RecorderCount INT 记录总数0 会返回总记录 PageSize INT 每页输出的记录数 PageIndex INT 当前页数 TotalCount INT OUTPUT 记返回总记录 TotalPageCount INT OUTPUT 返回总页数 AS SET NOCOUNT ON IF ISNULL TotalCount SET TotalCount 0 SET Order RTRIM LTRIM Order SET PrimaryKey RTRIM LTRIM PrimaryKey SET FieldList REPLACE RTRIM LTRIM FieldList WHILE CHARINDEX Order 0 OR CHARINDEX Order 0 BEGIN SET Order REPLACE Order SET Order REPLACE Order END IF ISNULL TableName OR ISNULL FieldList OR ISNULL PrimaryKey OR SortType 3 OR RecorderCount 0 OR PageSize 0 OR PageIndex 0 BEGIN SET new order1 ORDER BY Order SET new order2 Order SET new order2 REPLACE REPLACE new order2 ASC ASC DESC DESC SET new order2 REPLACE REPLACE new order2 ASC DESC DESC ASC SET new order2 ORDER BY SUBSTRING new order2 1 LEN new order2 1 IF FieldList BEGIN SET new order3 REPLACE REPLACE Order ASC DESC SET FieldList FieldList WHILE CHARINDEX new order3 0 BEGIN IF CHARINDEX SUBSTRING new order3 1 CHARINDEX new order3 FieldList 0 BEGIN SET FieldList FieldList SUBSTRING new order3 1 CHARINDEX new order3 END SET new order3 SUBSTRING new order3 CHARINDEX new order3 1 LEN new order3 END SET FieldList SUBSTRING FieldList 2 LEN FieldList END END SET SqlCount SELECT TotalCount COUNT TotalPageCount CEILING COUNT 0 0 CAST PageSize AS VARCHAR FROM TableName new where1 IF RecorderCount 0 BEGIN EXEC SP EXECUTESQL SqlCount N TotalCount INT OUTPUT TotalPageCount INT OUTPUT TotalCount OUTPUT TotalPageCount OUTPUT END ELSE BEGIN SELECT TotalCount RecorderCount END IF PageIndex CEILING TotalCount 0 0 PageSize BEGIN SET PageIndex CEILING TotalCount 0 0 PageSize END IF PageIndex 1 OR PageIndex CEILING TotalCount 0 0 PageSize BEGIN IF PageIndex 1 返回第一页数据 BEGIN SET Sql SELECT TOP STR PageSize FieldList FROM TableName new where1 new order1 END IF PageIndex CEILING TotalCount 0 0 PageSize 返回最后一 页数据 BEGIN SET Sql SELECT TOP STR PageSize FieldList FROM SELECT TOP STR ABS PageSize PageIndex TotalCount PageSize FieldList FROM TableName new where1 new order2 AS TMP new order1 END END ELSE BEGIN IF SortType 1 仅主键正序排序 BEGIN IF PageIndex SELECT MAX PrimaryKey FROM SELECT TOP STR PageSize PageIndex 1 PrimaryKey FROM TableName new where1 new order1 AS TMP new order1 END ELSE 反向检索 BEGIN SET Sql SELECT TOP STR PageSize FieldList FROM SELECT TOP STR PageSize FieldList FROM TableName new where2 PrimaryKey SELECT MIN PrimaryKey FROM SELECT TOP STR TotalCount PageSize PageIndex PrimaryKey FROM TableName new where1 new order2 AS TMP new order2 AS TMP new order1 END END IF SortType 2 仅主键反序排序 BEGIN IF PageIndex CEILING TotalCount 0 0 PageSize 2 正向检索 BEGIN SET Sql SELECT TOP STR PageSize FieldList FROM