SQL CPU占用一直90%以上，居高不下，各位帮忙

[dlq_skj]

服务器配置：阿里云4核U 4G内存
数据库中有张表 里面有100多万的数据，客户端的所有应用程序都在同时且时刻刻的不断的随机查询这张表的一条数据
表结构字段：
ID
IDName
Username
UserPWD
Department
Isvalid
=================================



所有客户端的应用都是在不断的循环执行下面这条语句进行随机抽取一条数据
select top 1 UserName,UserPwd from AllIDManagerDetails where IDName='VIP' and Isvalid=0 order by newid()
===========================================
执行计划

=======================================================
昨天本版的版主帮助过我这个问题，原帖http://bbs.csdn.net/topics/390857776，当时我把执行计划贴出来后版主指点我建了个索引，当时索引查找开销降低了，但orderby 开销还是很大，CPU占有率也有所缓解。运行了一天后，客户端应用有所增加，相比昨天增加不是很多，但发现CPU又如初一直90%以上，下不去了。请问下各位这条语句有什么可以优化的地方能解决资源占用问题？随机抽取一条数据可以不用orderby来做到吗？有什么高效率的替代方案？
本人才疏学浅，黔驴技穷，望各位大佬帮忙，再不解决这个问题，要被炒鱿鱼了。

[發糞塗牆]

SELECT TOP 1
        UserName ,
        UserPwd
FROM    AllIDManagerDetails TABLESAMPLE(1 PERCENT)
WHERE   IDName = 'VIP'
        and Isvalid = 0  --order by newid()

这样呢？

[dlq_skj]

引用 11 楼 DBA_Huangzj 的回复:

SELECT TOP 1
        UserName ,
        UserPwd
FROM    AllIDManagerDetails TABLESAMPLE(1 PERCENT)
WHERE   IDName = 'VIP'
        and Isvalid = 0  --order by newid()

这样呢？

====================
这样看来是不是好些了？
能部署到应用中去吗

[發糞塗牆]

可以，但是你最好测试一下数据会不会很容易就重复，毕竟你的目的是为了“随机”

[Tiger_Zhao]

引用 8 楼 dlq_skj 的回复:

[quote=引用 6 楼 yenange 的回复:] [quote=引用 楼主 dlq_skj 的回复:] 所有客户端的应用都是在不断的循环执行下面这条语句进行随机抽取一条数据 select top 1 UserName,UserPwd from AllIDManagerDetails where IDName='VIP' and Isvalid=0 order by newid()

真心不明白，　这个有什么用？[/quote] 做设计的 客户有这样的需求 那就的按他需求来，不管他的需求有多么的变态。客户看的只是结果，并不会听你的理论。[/quote]

select top 1000 UserName,UserPwd from AllIDManagerDetails where IDName='VIP' and Isvalid=0 order by newid()

让前台程序一次取1000，用完这1000个随机记录再来取数。 同样满足用户“不停随机选取”的要求。访问频率只有 1/1000。 设计好没水平！

[魔者侠心之明星级程序员]

引用 17 楼 Tiger_Zhao 的回复:

[quote=引用 15 楼 yangb0803 的回复:] 需要 SQL 2012 ，程序生成随机数后传给过程，直接跳到随机页。不过这里有个问题，就是随机数不能超过合乎条件的记录条数。 create procedure [randrecord](@rand int) as select UserName,UserPwd from AllIDManagerDetails where IDName='VIP' and Isvalid=0 OFFSET @rand ROWS FETCH NEXT 1 ROWS ONLY

不如让程序随机 ID 好了，范围是 [ Min(ID), Max(ID) ]，中间有断续也没关系，只是命中率有些差异，但是速度没得说。

create procedure [randrecord](@randID int)
as
SELECT TOP 1  
       UserName , 
       UserPwd 
  FROM AllIDManagerDetails
 WHERE IDName = 'VIP'
   AND Isvalid = 0
   AND ID >= @randID

[/quote] exactly.

[魔者侠心之明星级程序员]

引用 4 楼 dlq_skj 的回复:

[quote=引用 1 楼 rockyljt 的回复:] 可以在id字段上做文章，随机产生一个数值，然后通过id查出来，不知道会不会快些 另外，数据越少越好，删除不用的数据肯定会快些了

1，在ID上下手的方案解决不了问题，因为是条件查询 客户端要查询的是限定IDName字段的东西，这样的话还要知道这个字段内的ID范围才行！而ID有可能并不是增值为1的连续的。增加复杂度 2，数据都是有用的，如果我删了，承担不起责任。如果多数是没用的，我直接删了，就不会有这个贴了。 [/quote] 可以用下面的方面去测试一下，这样做可以保证不对原表中大量的数据进行排列。 1,以IDName='VIP' and Isvalid=0 为条件建indexed view, indexed view包含列主键列，IDName，Isvalid，Username，UserPWD 2,select * from indexedview, ROW_NUMBER() OVER(ORDER BY clusteredIndexedId) AS Row 上面的clusteredIndexedId一定要为聚集索引列，以保证上面的语句不排列。 3,declare Int totalcount = select count (*) from indexedview 4, select * from indexedview where Row= cast(rand()*totalcount as Int)

[Q315054403]

从设计上改，比如加个AutoID，这是效率最高的 自身公司技术不足，可以外包出来喽

[Tiger_Zhao]

引用 15 楼 yangb0803 的回复:

需要 SQL 2012 ，程序生成随机数后传给过程，直接跳到随机页。不过这里有个问题，就是随机数不能超过合乎条件的记录条数。 create procedure [randrecord](@rand int) as select UserName,UserPwd from AllIDManagerDetails where IDName='VIP' and Isvalid=0 OFFSET @rand ROWS FETCH NEXT 1 ROWS ONLY

不如让程序随机 ID 好了，范围是 [ Min(ID), Max(ID) ]，中间有断续也没关系，只是命中率有些差异，但是速度没得说。

create procedure [randrecord](@randID int)
as
SELECT TOP 1  
       UserName , 
       UserPwd 
  FROM AllIDManagerDetails
 WHERE IDName = 'VIP'
   AND Isvalid = 0
   AND ID >= @randID

[zhendemo_13]

/*
确保索引覆盖
索引列IDName,Isvalid,包含列 UserName,UserPwd(如果ID不是聚集索引还需包含ID字段)
*/       
SELECT a.UserName,
        UserPwd
FROM 
(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) id,
        UserName,
        UserPwd
FROM    dbo.AllIDManagerDetails
WHERE   IDName='VIP' and Isvalid=0)a, 

(SELECT cast (RAND() * COUNT(*)+1 AS INT) id FROM dbo.AllIDManagerDetails
WHERE   IDName='VIP' and Isvalid=0)b
WHERE a.id=b.id

[xzb001]

/* 尝试直接在过滤条件里做随机处理，避免使用排序 */ select top 1 * from dbo.AllIDManagerDetails where convert(float, checksum(newid(),id) & 0x7fffffff) / convert(int, 0x7fffffff) <= 0.00001

[dlq_skj]

不好意思，这几天忙就只是来看了下各位提供的方法没有回复，各位的方法都考虑并选择性的作了实践， 首先版主的解决方案存在取随机重复度很严重的情况，并且取不到数据的情况很多。 Tiger_Zhao的方案 让程序随机 ID ，这种行不通，由于这张表的数据各IDname下均是在随时增加的！ 如果按照您的方案的话不是命中率有些差异，是差异很大，取不到数据的情况会很多。 SQL_Beginner的测试后性能没有多大提升，不知道是不是我理解太片面的问题 zhendemo_13的方案应用后相比oderby有很大提升，但是随着客户端的量增大也出现了瓶颈，不知道是不是我没有操作对 xzb001的提出在过滤条件里做随机处理 不知道有没有更详细的，还请明示。 目前暂时用的方案还是按照Tiger_Zhao说的第一种方案，尽量减少和数据库的交互，一次性取出一批数据存到客户端用，但这种情况无法实时得到isvalid的状态。因为isvalid的值随时在变动的。有局限性。

[xzb001]

select top 1 * from [表名] where convert(float, checksum(newid(),[字段名，最好有索引]) & 0x7fffffff) / convert(int, 0x7fffffff) <= 0.00001 只要填上表名、一个字段名，上面例子里，就是使用dbo.AllIDManagerDetails为表名，id为字段名，修改好就可以运行见到效果了，能够实现随机的原因是为每行生成一个随机值（ convert(float, checksum(newid(),id) & 0x7fffffff)），然后除以int的最大值（convert(int, 0x7fffffff)），再把返回结果集缩小到10行左右（ <= 0.00001），最后返回第一行（top 1）。 最后说明一下，convert(float, checksum(newid(),id) & 0x7fffffff)这个表达式，会生成一个随机的int型正整数，按随机几率是均等的来计算，100万行数据*0.00001就等于10，经测试可以返回几行到20行不等的数据，最后top1就完成了。0.00001这个值要根据测试结果调整，有可能设置得太小，导致没有返回结果。

[baidu_19797565]

看不明白，好自卑！

[Tiger_Zhao]

引用 22 楼 dlq_skj 的回复

Tiger_Zhao的方案 让程序随机 ID ，这种行不通，由于这张表的数据各IDname下均是在随时增加的！
如果按照您的方案的话不是命中率有些差异，是差异很大，取不到数据的情况会很多。

你没仔细看 SQL，条件是 ID >= @randID 而不是 ID=@randID。
如果把 MaxID 和 MinID 串连起来形成环，最终的查询语句是下面这样的（我认为理解了我的思路后，你应该能直接补完整的）

create procedure [randrecord](@randID int)
as
SELECT TOP 1 *
  FROM (
           SELECT TOP 1  
                  UserName , 
                  UserPwd 
             FROM AllIDManagerDetails
            WHERE IDName = 'VIP'
              AND Isvalid = 0
              AND ID >= @randID
         ORDER BY ID
        UNION ALL
           SELECT TOP 1  
                  UserName , 
                  UserPwd 
             FROM AllIDManagerDetails
            WHERE IDName = 'VIP'
              AND Isvalid = 0
         ORDER BY ID
        ) T
ORDER BY ID DESC

这样应该可以理解了吧！

[prcgolf]

@xzb001,如何能保证取得到数据，比如随机100行记录，根据条件查询出来的记录应该有10万行左右。怎么能保证肯定取到100条记录，而且是真正随机的 @Tiger_Zhao，同样，取100条，好像你的不是真正的随机啊

[prcgolf]

@xzb001,如何能保证取得到数据，比如随机100行记录，根据条件查询出来的记录应该有10万行左右。怎么能保证肯定取到100条记录，而且是真正随机的 @Tiger_Zhao，同样，取100条，好像你的不是真正的随机啊

[Tiger_Zhao]

[Quote=引用 28 楼 prcgolf 的回复:][/Quote]
@randID 是程序随机生成的，你凭什么说是不随机？
已知上一条的 ID，你来确定下一条是哪个 ID？

只能说各个ID的命中率有差异，但是在10万的基数下，你不取个上亿次是无法证明存在有规律的命中不均。

[最爱午夜]

LZ，降低并行度和并行阀值吧，CPU保准下来了，还有就是建索引，估计不止一个表

[rfq]

orer by newid() 种随机 取 数据 有问题。 不利用 索引 整张表的排序。如果并发量非常大 ， 性能肯定是不行的。 建议 客户端生成查找数据的号 ， 查找。 不会再有 这样那样的问题了

[专注or全面]

其实最重要的有两个问题， 第一：减小获取随机数的成本 第二：要完全做到随机，并且能取到数据 符合条件的数据单独存储在一个表中，增加一个GUID列做聚集索引，另外增加一个自增列 查询时候根据自增列的最大值， select top 1 * from tableName where id <= FLOOR(rand()*maxID)