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

本文主要是介绍添加聚集索引后高效分页查询的效率，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

本人使用的是sql 2012数据库

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

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

GO
ALTER proc [dbo].[ex3](@count int --次数
)
as
begin
declare @i int
set @i = 0
while @i < @count
begin
SELECT * 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 DESC
set @i = @i + 1
end
end

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

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

GO
ALTER proc [dbo].[ex1](@count int --次数
)
as
begin
declare @i int
set @i = 0
while @i < @count
begin
SELECT * FROM product0 w1, 
(SELECT TOP 3 id FROM (SELECT TOP 1000 id, grade,createTime FROM product0where[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 desc
set @i = @i + 1
end
end

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

GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ex3](@count int --次数
)
as
begin
declare @i int
set @i = 0
while @i < @count
begin
SELECT * 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 DESC
set @i = @i + 1
end
end

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

GO
ALTER proc [dbo].[ex2](@count int --次数
)
as
begin
declare @i int
set @i = 0
while @i < @count
begin
SELECT * FROM  (SELECT TOP 3 * FROM (SELECT TOP 1000 * FROM product0where [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 Desc
set @i = @i + 1
end
end

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

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

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

这篇关于添加聚集索引后高效分页查询的效率的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---