MSSQL Server查询优化 (整理修改)_mssql 网页查询修改-CSDN博客

本文链接：https://blog.csdn.net/xmlife/article/details/43984911

/***** 在SQLServer上查看SQL语句的执行时间的方法 *******

比较简单的查询方法，通过查询前的时间和查询后的时间差来计算的

代码如下:

 
 declare @begin_date datetime 
 
 declare @end_date datetime 
 
 select @begin_date = getdate() 
 
 <这里写上你的语句...> 
 
 select @end_date = getdate() 
 
 select datediff(ms,@begin_date,@end_date) as '用时/毫秒'  
 
 OR:select (@end_date-@begin_date) as execTime

2：方法二比较全面，将执行每个语句时采取的步骤作为行集返回，通过层次结构树的形式展示出来

代码如下:

 
 set statistics profile on 
 
 set statistics io on 
 
 set statistics time on 
 
 go 
 
 <这里写上你的语句...> 
 
 go 
 
 set statistics profile off 
 
 set statistics io off 
 
 set statistics time off

*************/

ＳＱＬ执行顺序

(1)、 FROM：对FROM子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表VT1。

(2)、 ON：对VT1应用ON筛选器，只有那些使为真才被插入到TV2。
(3)、 OUTER (JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN或INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到VT2，生成TV3。如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表位置。
(4)、 WHERE：对TV3应用WHERE筛选器，只有使为true的行才插入TV4。

(5)、 GROUP BY：按GROUP BY子句中的列列表对TV4中的行进行分组，生成TV5。

(6)、 CUTE|ROLLUP：把超组插入VT5，生成VT6。
(7)、 HAVING：对VT6应用HAVING筛选器，只有使为true的组插入到VT7。

(8)、 SELECT：处理SELECT列表，产生VT8。
(9)、 DISTINCT：将重复的行从VT8中删除，产品VT9。
(10)、ORDER BY：将VT9中的行按ORDER BY子句中的列列表顺序，生成一个游标(VC10)。
(11)、TOP：从VC10的开始处选择指定数量或比例的行，生成表TV11，并返回给调用者

1.用select top xx 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行

2.尽量不用　"IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'"，这些全表扫描，不会执行索引

SELECT ID FROM T WHERE NUM IS NULL
可以在NUM上设置默认值0，确保表中NUM列没有NULL值(　应该没什么影响　)，然后这样查询：
SELECT ID FROM T WHERE NUM=0

----------------------------------------------------------

SELECT ID FROM T WHERE NUM=10 OR NUM=20
可以这样查询：
SELECT ID FROM T WHERE NUM=10
UNION ALL
SELECT ID FROM T WHERE NUM=20”

-----------------------------------------------------------

对UNION ALL结果集添加Where条件

 select * from (
 [SQL 语句 1] 
 UNION 
 [SQL 语句 2] 
 ) tmp
 where 姓名 like 'xxx%'

注意：union子句不能直接加order by，应用如下方法
select * From (select top 5  1 as order1,* from ProContent where ProductName like 'a%') as Tb1
union
select * From (select top 5  2 as order1,* from ProContent where ProductName like 'b%' order by order1, id) as Tb2
order by order1,id desc 
相同字段，不必每个union 加order by xxx ,只需在任意一个union子句中添加一次即可，结果等价
如果子查询的排序与总查询相同，则在子查询无需排序order by

-----------------------------------------------------------

SELECT ID FROM T WHERE NUM IN(1,2,3)
对于连续的数值，能用 BETWEEN 不要用 IN ：
SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3”

3.不要在WHere字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代.

还可以变通写法：WHERE SUBSTRING(firstname,1,1) = 'm'改为WHERE firstname like 'm%'（索引扫描），

like 'a%' 使用索引

like '%a' 不使用索引

不要在一句话里再三的使用相同的函数，浪费资源,将结果放在变量里再调用更快

4.NOT IN会多次扫描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.

在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数

5.MIN() 和 MAX()能使用到合适的索引，

　SELECT COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快

   不要对索引字段进行运算，而要想办法做变换，比如

   SELECT ID FROM T WHERE NUM/2=100
   应改为:
   SELECT ID FROM T WHERE NUM=100*2

6.如果要插入大的二进制值到Image列，使用存储过程，不要用内嵌INsert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作:
方法：Create procedure p_insert as insert into table(Fimage) values (@image),
在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

7.Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。
select * from chineseresume where title in ('男','女')
Select * from chineseresume where between '男' and '女'
是一样的。由于in会在比较多次，所以有时会慢些

8.用OR的字句可以分解成多个sql查询，并且通过UNION 连接多个查询

9.一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快

10.一次更新多条记录比分多次更新每次一条快,

11.(1) IF 没有输入负责人代码 THEN
code1=0
code2=9999
ELSE
code1=code2= 负责人代码
END IF
执行SQL语句为:
SELECT 负责人名 FROM P2000 WHERE 负责人代码>=:code1 AND负责人代码 <=:code2

(2) IF 没有输入负责人代码 THEN
　 SELECT 负责人名 FROM P2000
ELSE
code= 负责人代码
SELECT 负责人代码 FROM P2000 WHERE 负责人代码=:code
END IF

第一种方法只用了一条SQL语句,第二种方法用了两条SQL语句。在没有输入负责人代码时,第二种方法显然比第一种方法执行效率高,因为它没有限制条件;在输入了负责人代码时,第二种方法仍然比第一种方法效率高,不仅是少了一个限制条件, 还因相等运算是最快的查询运算

12.数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。

尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。

SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。

13.日期查询的例子：

WHERE DATEDIFF(DAY, 日期,'2005-11-30')=0应改为:WHERE 日期 >='2005-11-30' AND 日期 <'2005-12-1‘

WHERE DATEDIFF(DAY, 日期,'2005-11-30')>0应改为:WHERE 日期 <'2005-11-30‘

WHERE DATEDIFF(DAY, 日期,'2005-11-30')>=0应改为:WHERE 日期 <'2005-12-01‘

WHERE DATEDIFF(DAY, 日期,'2005-11-30')<0应改为:WHERE 日期>='2005-12-01‘

WHERE DATEDIFF(DAY, 日期,'2005-11-30')<=0应改为:WHERE 日期>='2005-11-30‘

14.sql not in与left join百万级数据测试比较
select OrgId as 公司编码,OrgName as 公司名称
from Organise
where OrgLev=2
and item_id not in
(select OrgidS from WagesPerMonthHis
where WagesYear='2010' and WagesMonth=
'01' Group by OrgidS,OrgNameS)
order by Orgid

语句执行要33秒之久,内存和CPU在执行时都没有出现瓶颈,以为是
代码如下复制代码
(select OrgidS from WagesPerMonthHis
where WagesYear='2010' and WagesMonth=
'01' Group by OrgidS,OrgNameS)
这条语句执行缓慢所致,单独执行这条却发现执行速度很快,大约不到2秒就出来了,于是症结出来了,是not in 这个全扫描关键词带来的性能下降.最直接的是导致页面失去响应,一个关键功能使用不了.

试了not exist语句,发现效果是一样的,并不象网上所说可以提高很多性能.

于是重新优化语句如下
代码如下复制代码
select a.OrgId as 公司编码,a.OrgName as 公司名称,a.item_id
from Organise a
left outer join (select distinct b.OrgIdS from WagesPerMonthHis b
where WagesYear='2010' and WagesMonth='01') as b
on a.item_id = b.OrgidS
where a.OrgLev = 2
and b.OrgIdS is Null
order by 公司编码

改用左外连接(其实左连接也可以)后,整个语句执行速度为400ms

14.大数据量排序

在数据库中有一张表mytable，数据记录7000万条，有如下两条SQL语句

(1)select top 100 * from mytable order by operateDate

(2)select * from (select top 100 * from mytable ) a order by a.operateDate (快）

15.索引的创建规则

(1)、表的主键、外键必须有索引；
(2)、数据量超过300的表应该有索引；
(3)、经常与其他表进行连接的表，在连接字段上应该建立索引；
(4)、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
(5)、索引应该建在选择性高的字段上；
(6)、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
(7)、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
  (A)、正确选择复合索引中的主列字段，一般是选择性较好的字段；
  (B)、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
  (C)、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
  (D)、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
  (E)、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
(8)、频繁进行数据操作的表，不要建立太多的索引；
(9)、删除无用的索引，避免对执行计划造成负面影响；