索引假脱机开销大，改怎么优化？

Question

本帖最后由 u012956650 于 2014-08-26 22:44:12 编辑

Answer 1

In 后面是一个子查询，对应子查询具有不确定性，所以查询优化器判定，将子查询的结果放到tempdb中，并自己加上一个索引（索引假脱机），来进行查询更快。 解决的方法，可以在dep表上建立对应workid ，id的include索引即可。有个小疑问， top 1 的子查询，没有对应的order by ? 是随机的吗/？

Answer 2

可以参考看一下 http://technet.microsoft.com/zh-cn/library/ms190435(v=sql.105).aspx看起来有一点儿绕 ...

Answer 3

按照您说的建立了索引 执行计划看起来无任何改变。 
是这样的，我查找的是这个表中DepName ='customer' 的且workid不重复的数据，如果有重复的话 就取出一条。比如有3条workid='aa'，我只需要取出第一条就可以了。也就不用order by了

Answer 4

你看看这个语句的结果是不是和你的一样的SELECT  MAX(ID) ID ,
        DepName ,
        WorkId ,
        Counts ,
        UpdateTime ,
        IsValid ,
        IsOnline
FROM    Dep AS A
WHERE   DepName = 'customer'
        AND IsValid = 0
GROUP BY DepName ,
        WorkId ,
        Counts ,
        UpdateTime ,
        IsValid ,
        IsOnline
ORDER BY WorkId

Answer 5

有人对这个页面进行CC攻击，当然这个页面主要是用的这条语句检索的数据到页面。不断的执行这条语句 导致CPU飙升。但是CPU并没有到达100%。大概就是在50-70这个段徘徊。照理说网站不应该瘫痪，但是网站却瘫痪了!问题的关键在于，攻击者停止攻击后，无论怎么重启SQL服务CPU始终在50-70徘徊。一定要重启服务器才能让CPU回到正常。

Answer 6

按照您说的建立了索引 执行计划看起来无任何改变。 
是这样的，我查找的是这个表中DepName ='customer' 的且workid不重复的数据，如果有重复的话 就取出一条。比如有3条workid='aa'，我只需要取出第一条就可以了。也就不用order by了lz 是建立了这样的索引吗？CREATE INDEX ix_aaa ON dep(workid) INCLUDE(id,deptname,isvalid)
GO

Answer 7

按照您说的建立了索引 执行计划看起来无任何改变。 
是这样的，我查找的是这个表中DepName ='customer' 的且workid不重复的数据，如果有重复的话 就取出一条。比如有3条workid='aa'，我只需要取出第一条就可以了。也就不用order by了lz 是建立了这样的索引吗？CREATE INDEX ix_aaa ON dep(workid) INCLUDE(id,deptname,isvalid)
GOisvalid应该没必要建索引了吧

Answer 8

这个没有达到取出重复workid的目的

Answer 9

这样呢？--建一个索引：create index IX_test on dep(workid) include(depname)SELECT  *
FROM    dep a
WHERE   EXISTS ( SELECT 1
                 FROM   ( SELECT    WorkId
                          FROM      Dep AS A
                          WHERE     DepName = 'customer'
                                    AND IsValid = 0
                          GROUP BY  workid
                          HAVING    COUNT(1) <= 1
                        ) b
                 WHERE  a.workid = b.workid )

Answer 10

TO OrchidCat：
我之前索引建立的是：
CREATE INDEX Inx_Dep ON Dep(workid) INCLUDE(id,DepName)
执行计划和没有索引是一样的

Answer 11

id应该是聚集索引了吧，没必要include进去

Answer 12

写了一个方法，测试了一下，效率是你写法100倍，select distinct A.* from dep A left join dep B 
on A.WorkId=B.WorkId  
where A.ID>B.ID  and A.DepName ='customer' and A.IsValid=0 
ORDER BY A.WorkIdselect ID,DepName,WorkId,Counts,UpdateTime,IsValid,IsOnline  FROM Dep AS A  
where ID IN
(SELECT top 1  ID FROM  Dep  WHERE  WorkId =A.WorkId and DepName ='customer' and IsValid=0 ) 
ORDER BY WorkId

Answer 13

这个是检索表中所有的数据，表中所有的depName数据都被查出来了。

Answer 14

你这个没有达到目的啊 重复的workID也被查出来了

Answer 15

你这条语句相当于这条语句的功能
select ID,DepName,WorkId,Counts,UpdateTime,IsValid,IsOnline  FROM Dep where DepName ='customer'  order by  WorkId。

Answer 16

这个是检索表中所有的数据，表中所有的depName数据都被查出来了。外层再加个where

Answer 17

这个是检索表中所有的数据，表中所有的depName数据都被查出来了。外层再加个where SELECT  *
FROM    dep  a
WHERE   EXISTS ( SELECT 1
                 FROM   ( SELECT    WorkId
                          FROM      dep  AS A
                          WHERE     depName = 'customer'
                                    AND IsValid = 0
                          GROUP BY  workid
                          HAVING    COUNT(1) <= 1
                        ) b
                 WHERE  a.workid = b.workid ) and  depName = 'customer' 
结果是重复的数据一条也没取到

Answer 18

换一个
SELECT  ID ,
        DepName ,
        WorkId ,
        Counts ,
        UpdateTime ,
        IsValid ,
        IsOnline
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY workid ORDER BY id ) rn
          FROM      dep
          WHERE     DepName = 'customer'
                    AND IsValid = 0
        ) a
WHERE   rn = 1

Answer 19

我也写一个，不知对不对，效果如何。select ID, DepName, WorkId, Counts, UpdateTime, IsValid, IsOnline FROM Dep AS A
inner join (SELECT WorkId WorkId1, min(ID) ID1 FROM Dep WHERE DepName ='customer' and IsValid=0 group by WorkId) AS b on a.WorkId=b.WorkId1 and a.ID=b.ID1
order by WorkId

Answer 20

嗯，右键绿色的字，然后选择【缺少索引xxx】那个，会在新窗口出现语句，你改一下索引名直接执行即可

Answer 21

按照您的方法建立索引后 执行计划仍然提示 缺少索引是正常情况么
你是直接用它的脚本还是自己用图形化创建的？直接用的脚本USE [DepSys]
GO
CREATE NONCLUSTERED INDEX  IX_Task
ON dbo.Dep (DepName],IsValid)
GO

Answer 22

貌似和DBA_Huangzj的差不多？

Answer 23

缺少索引那个的完整定义就这样？
按照您的方法建立索引后 执行计划仍然提示 缺少索引是正常情况么
你是直接用它的脚本还是自己用图形化创建的？直接用的脚本USE [DepSys]
GO
CREATE NONCLUSTERED INDEX  IX_Task
ON dbo.Dep (DepName],IsValid)
GO

现在提示的还是同样的缺少那些列？

Answer 24

按照您的方法建立索引后 执行计划仍然提示 缺少索引是正常情况么
你是直接用它的脚本还是自己用图形化创建的？直接用的脚本USE [DepSys]
GO
CREATE NONCLUSTERED INDEX  IX_Task
ON dbo.Dep (DepName],IsValid)
GO

现在提示的还是同样的缺少那些列？是的 我检查了下 没有错啊  索引在表中也显示出来了 咋回事

Answer 25

这个情况偶尔遇到过，但是目前没有满意的回答，你先看看现在性能如何，可以用
SET STATISTICS IO ON ;
你的语句
SET STATISTICS IO OFF ;
对比一下你原有写法和现在这个写法，逻辑读的数量上是否有很大提升

Answer 26

这个是我之前的语句执行情况
表 'Worktable'。扫描计数 12614，逻辑读取 65434 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Dep。扫描计数 6，逻辑读取 875 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
===================================================
这个是您的语句结果
表 'Dep'。扫描计数 1，逻辑读取 220 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
=============================================================
貌似看起来提升不少？

Answer 27

这个是我之前的语句执行情况
表 'Worktable'。扫描计数 12614，逻辑读取 65434 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Dep。扫描计数 6，逻辑读取 875 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
===================================================
这个是您的语句结果
表 'Dep'。扫描计数 1，逻辑读取 220 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
=============================================================
貌似看起来提升不少？因为减少了假脱机，worktable是tempdb的中间表，“通常”意味着有性能问题

Answer 28

要不你删了那个索引试试逻辑读有多少。如果变化了，证明索引还是有效，只是SSMS的显示问题而已

Answer 29

把那个索引删了，另外用
sp_help 'dep'
看看你上面现在索引情况

Answer 30

为方便后来者遇到类似问题，我说下效果。在此非常感谢DBA_Huangzj 的热心帮助，帮了个大忙。
   昨天应用到项目中后，0点遭受CC攻击，由于这条语句的改进，这次没让攻击者得逞，攻击发生时CPU在40-50徘徊，攻击停止CPU立即得到释放，服务器不再像改进之前一旦攻击立马瘫痪需要重启服务器才能恢复，攻击中网站的访问不再受影响，攻击者攻击了一小时发现不再像以前那么容易，无法撼动而放弃。。不知道他还有没有后招，呵呵。
另外，zbdzjx的语句效率也不错，已经过实测。
唯一的疑问就是不知道怎么回事，明明根据执行计划建立了索引 还是提示缺少索引。删掉后 命令查看索引情况也只有个主键聚集索引啊。重建仍然提示。但不管建与不建反腐测试执行效率性能基本相差无几。不知道咋回事。

Answer 31

注重SQL执行效率是多么的重要啊，低质量的语句会导致不小的损失。

Answer 32

同行攻击啊  连续CC攻击了一个月了  其他机房都不接我了 目前放到阿里云。防CC攻击
编辑CC攻击可以归为DDoS攻击的一种。他们之间的原理都是一样的，即发送大量的请求数据来导致服务器拒绝服务，是一种连接攻击。CC攻击又可分为代理CC攻击，和肉鸡CC攻击。代理CC攻击是黑客借助代理服务器生成指向受害主机的合法网页请求，实现DDoS，和伪装就叫：cc（Challenge Collapsar）。而肉鸡CC攻击是黑客使用CC攻击软件，控制大量肉鸡，发动攻击，相比来后者比前者更难防御。因为肉鸡可以模拟正常用户访问网站的请求。伪造成合法数据包。
一个静态页面不需要服务器多少资源，甚至可以说直接从内存中读出来发给你就可以了，但是论坛之类的动态网站就不一样了，我看一个帖子，系统需要到数据库中判断我是否有读帖子的权限，如果有，就读出帖子里面的内容，显示出来——这里至少访问了2次数据库，如果数据库的体积有200MB大小，系统很可能就要在这200MB大小的数据空间搜索一遍，这需要多少的CPU资源和时间？如果我是查找一个关键字，那么时间更加可观，因为前面的搜索可以限定在一个很小的范围内，比如用户权限只查用户表，帖子内容只查帖子表，而且查到就可以马上停止查询，而搜索肯定会对所有的数据进行一次判断，消耗的时间是相当的大。
CC攻击就是充分利用了这个特点，模拟多个用户（多少线程就是多少用户）不停的进行访问（访问那些需要大量数据操作，就是需要大量CPU时间的页面，比如asp/php/jsp/cgi）。很多朋友问到，为什么要使用代理呢？因为代理可以有效地隐藏自己的身份，也可以绕开所有的防火墙，因为基本上所有的防火墙都会检测并发的TCP/IP连接数目，超过一定数目一定频率就会被认为是Connection-Flood。当然也可以使用肉鸡发动CC攻击。肉鸡的CC攻击效果更可观。致使服务器CPU%100，甚至死机的现象。
使用代理攻击还能很好的保持连接，这里发送了数据，代理转发给对方服务器，就可以马上断开，代理还会继续保持着和对方连接（我知道的记录是有人利用2000个代理产生了35万并发连接）。
当然，CC也可以利用这里方法对FTP、游戏端口、聊天房间等进行攻击，也可以实现TCP-FLOOD，这些都是经过测试有效的。
防御CC攻击可以通过多种方法，禁止网站代理访问，尽量将网站做成静态页面，限制连接数量，修改最大超时时间等。

Answer 33

对方是用的肉鸡进行的攻击，服务器装的有安全狗，安全狗设置了禁止代理IP访问网站。攻击发起后阿里云的云盾到达阀值后就开始清洗，但此时网站已经瘫痪。至少对于我来说云盾基本上算是无用的。
不知道，在前台页面做个防止刷新的功能会不会好些，就是判断访问者在某时间内刷新多少次就限制多少时间再访问，但存在一个难度就是如何查出谁是正常访问谁是恶意访问，用软件进行模拟访问和正常访问相差无几很难发觉。我觉得安全狗应该就是这种机制。但安全狗在防止CC攻击方面也不是很得力，攻击发生时我甚至找安全狗的工程师来帮忙设置了，也没用。目前前台SQL语句改进了暂时得到控制，但我想这可能没完，他会加大攻击成本的。
页面需要实时得到最新数据，这种怎么把他做成静态呢。

Answer 34

这种一般要在前端防御，当攻击到达SQL Server时，已经太晚了