添加聚集索引后高效分页查询的效率

本人使用的是sql 2012数据库

ALTER proc [dbo].[ex2](    @count int --次数)asbegindeclare @i intset @i = 0while @i < @countbeginSELECT * FROM product0       WHERE    [bName] like  '%23%'     Order by grade desc,createTime desc OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY ;set @i = @i + 1endend

100次 用时82秒，通过为grade添加聚集索引，用时 84秒

GOALTER proc [dbo].[ex3](    @count int --次数)asbegindeclare @i intset @i = 0while @i < @countbeginSELECT * FROM product0 w1       WHERE id in           (              SELECT top 3 id FROM               (                  SELECT top 7 ID, grade,createTime FROM product0 where [bName] like  '%23%' and    [pName] like  '%%' and    [find0] like  '%%' and    [find1] like  '%%' ORDER BY grade desc            ) w order by grade,createTime DESC        ) order by grade desc,createTime DESCset @i = @i + 1endend

100次 用时86秒，通过为grade添加聚集索引，用时 7秒

查询1000的后三条数据，效率比较

GOALTER proc [dbo].[ex1](    @count int --次数)asbegindeclare @i intset @i = 0while @i < @countbeginSELECT * FROM product0 w1, (    SELECT TOP 3 id FROM     (        SELECT TOP 1000 id, grade,createTime FROM product0        where            [bName] like  '%23%' and            [pName] like  '%%' and            [find0] like  '%%' and            [find1] like  '%%'         ORDER BY grade DESC    ) w ORDER BY grade ASC ) w2 WHERE w1.id = w2.id ORDER BY w1.grade DESC, w1.createTime descset @i = @i + 1endend

100次，添加gradle聚集索引,用时9秒

GOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[ex3](    @count int --次数)asbegindeclare @i intset @i = 0while @i < @countbeginSELECT * FROM product0 w1       WHERE id in           (              SELECT top 3 id FROM               (                  SELECT top 1000 ID, grade,createTime FROM product0 where [bName] like  '%23%' and    [pName] like  '%%' and    [find0] like  '%%' and    [find1] like  '%%' ORDER BY grade desc            ) w order by grade        ) order by grade desc,createTime DESCset @i = @i + 1endend

100次，添加gradle聚集索引,用时9秒

GOALTER proc [dbo].[ex2](    @count int --次数)asbegindeclare @i intset @i = 0while @i < @countbeginSELECT * FROM  (    SELECT TOP 3 * FROM (        SELECT TOP 1000 * FROM product0        where [bName] like  '%23%' and    [pName] like  '%%' and    [find0] like  '%%' and    [find1] like  '%%'         ORDER BY grade DESC          ) f         ORDER BY f.grade ASC    ) s ORDER BY s.grade DESC ,s.createTime Descset @i = @i + 1endend

100次，添加gradle聚集索引,用时4秒

goexecute sp_helpindex @objname = 'product0'  --查看索引go drop index product0.CLUSTER_id;  --删除索引goCREATE CLUSTERED INDEX CLUSTER_id ON product0(grade) --创建索引

本人通过添加和删除聚集索引发现：使用SQL server 2012提供的 OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY 的查询语句，在速率上没有明显的提示。