测试环境:
Intel PR Dual T3200 @2.00GHz
3G Mem
测试数据:
268,783,125条数据(~2.6亿条数据)
表结构(表未分区):
-------+--------------+----------------
ID int PK, 聚集
-------+--------------+----------------
namee navrchar(50), 唯一,非聚集
测试脚本:
DECLARE @setsegments AS INT--当前段:2100
SET @setsegments=2100
DECLARE @allcount AS INT--总数据量
SET @allcount=(SELECT rowcnt FROM [sysindexes] WHERE id=OBJECT_ID('Test') AND keycnt<2)
DECLARE @segmentsize AS INT--每段数据量:50
SET @segmentsize=50
DECLARE @begin AS INT
SET @begin=@segmentsize*@setsegments
DECLARE @end AS INT
SET @end=@segmentsize*@setsegments+@segmentsize
SELECT top(@segmentsize) ID,namee FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC)
AS rownum,ID,namee FROM Test WITH (NOLOCK))
AS t
WHERE
t.rownum between @begin and @end
ORDER BY ID DESC
测试结果:
Sql Server Profiler's Duration平均在:0.03s,可谓之瞬间。
注意点:
segmentsize不能开的太大,否则比较耗时。(无限扩大则认为全表)
结论:性能较好(并发资源占用未测试)。
对于大部分数据模型均适用,只要select字段索引适当,亿万级也是瞬间!
扩展:如查询字段较多,而且查询字段索引较差则可:
SELECT ID,namee,xx1,xx2,xx3. FROM Test where ID
IN
(
SELECT top(@segmentsize) ID FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS rownum,ID FROM Test WITH (NOLOCK)) AS t
WHERE
t.rownum between @begin and @end
)
ORDER BY ID DESC