sqlserver 三种分页方式性能比较[图文]

Liwu_Items表,CreateTime列建立聚集索引
第一种,sqlserver2005特有的分页语法 复制代码 代码如下: declare @page int declare
@pagesize int set @page = 2 set @pagesize = 12 SET STATISTICS IO on
SELECT a.* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY b.CreateTime
DESC) AS [ROW_NUMBER], b.* FROM [dbo].[Liwu_Items] AS b ) AS a
WHERE a.[ROW_NUMBER] BETWEEN @pagesize + 1 AND (@page*@pagesize)
ORDER BY a.[ROW_NUMBER] 结果: (12 行受影响)表
‘Liwu_Items’。扫描计数 1,逻辑读取 7 次,物理读取 0 次,预读 0 次,lob
逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。 逻辑读是7次

测试sql: 复制代码 代码如下: SET
STATISTICS IO ON SET STATISTICS TIME ON SELECT COUNT(1) FROM dbo.tbtext
a INNER LOOP JOIN dbo.tbtext b ON a.id = b.id option (maxdop 1) SET
STATISTICS IO Off SET STATISTICS TIME Off 表结构: 复制代码 代码如下: CREATE TABLE
[dbo].[tbtext]( [id] [int] IDENTITY(1,1) NOT NULL, [VALUE]
[int] NULL ) ON [PRIMARY] 单这句测试,看执行计划根本看不出区别。
|–Compute
Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1009],0)))
|–Stream Aggregate(DEFINE:([Expr1009]=Count(*))) |–Nested
Loops(Inner Join, WHERE:([northwind].[dbo].[tbtext].[id] as
[b].[id]=[northwind].[dbo].[tbtext].[id] as [a].[id]))
|–Table Scan(OBJECT:([northwind].[dbo].[tbtext] AS [a]))
|–Table Spool |–Table Scan(OBJECT:([northwind].[dbo].[tbtext] AS
[b])) 2008r2: 复制代码 代码如下: /*
警告: 由于使用了本地联接提示,联接次序得以强制实施。 表
‘tbtext’。扫描计数 1,逻辑读取 46 次 (1 行受影响) 表
‘Worktable’。扫描计数 1,逻辑读取 290098 次,物理读取 0 次,预读 0
次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。 表
‘tbtext’。扫描计数 2,逻辑读取 262 次,物理读取 0 次,预读 0 次,lob
逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。 (1 行受影响) SQL
Server 执行时间: CPU 时间 = 32828 毫秒,占用时间 = 32846 毫秒。 SQL
Server 执行时间: CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。 */ 2000sp4:
复制代码 代码如下: /* 警告:
由于使用了局部联接提示,所以联接次序得以强制实施。 表 ‘tbtext’。扫描计数
1,逻辑读 131 次,物理读 0 次,预读 0 次。 SQL Server 执行时间: CPU 时间
= 0 毫秒,耗费时间 = 0 毫秒。 表 ‘Worktable’。扫描计数 9999,逻辑读
180001 次,物理读 0 次,预读 0 次。 表 ‘tbtext’。扫描计数 2,逻辑读 262
次,物理读 0 次,预读 138 次。 SQL Server 执行时间: CPU 时间 = 17188
毫秒,耗费时间 = 17261 毫秒。 (1 行受影响) SQL Server 执行时间: CPU 时间
= 0 毫秒,耗费时间 = 0 毫秒。 */ 比较2000 和 2008的执行就能发现 2008
的cpu 时间明显比 2000 高,2008 的worktable 逻辑读取量,比2000的高,
这个有个worktable
的扫描技术,2000的是9999,2008的是1,这个让人难免有的疑惑是什么情况,都是nest
loop,worktable 扫描不应该是1才对。 性能差怎么大会不会是 worktable
搞的鬼呢? 那么就开始调节,过滤id 会有啥发现呢? 复制代码 代码如下: SET STATISTICS IO ON SET
STATISTICS TIME ON SELECT COUNT(1) FROM dbo.tbtext a INNER LOOP JOIN
dbo.tbtext b ON a.id = b.id WHERE a.id = 1000 option (maxdop 1) SELECT
COUNT(1) FROM dbo.tbtext a SET STATISTICS IO Off SET STATISTICS TIME Off
2008r2: SELECT COUNT(1) FROM dbo.tbtext a INNER LOOP JOIN dbo.tbtext b
ON a.id = b.id WHERE a.id = 1000 option (maxdop 1) |–Compute
Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1009],0)))
|–Stream Aggregate(DEFINE:([Expr1009]=Count(*))) |–Nested
Loops(Inner Join, WHERE:([northwind].[dbo].[tbtext].[id] as
[b].[id]=[northwind].[dbo].[tbtext].[id] as [a].[id]))
|–Table Scan(OBJECT:([northwind].[dbo].[tbtext] AS [a]),
WHERE:([northwind].[dbo].[tbtext].[id] as [a].[id]=(1000)))
|–Table Spool |–Table Scan(OBJECT:([northwind].[dbo].[tbtext] AS
[b]), WHERE:([northwind].[dbo].[tbtext].[id] as
[b].[id]=(1000))) 复制代码 代码如下:
表 ‘Worktable’。扫描计数 1,逻辑读取 6006 次,物理读取 0 次,预读 0
次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。 表
‘tbtext’。扫描计数 2,逻辑读取 262 次,物理读取 0 次,预读 0 次,lob
逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。 2000sp4: |–Compute
Scalar(DEFINE:([Expr1002]=Convert([Expr1006]))) |–Stream
Aggregate(DEFINE:([Expr1006]=Count(*))) |–Nested Loops(Inner Join,
WHERE:([b].[id]=[a].[id])) |–Table
Scan(OBJECT:([Northwind].[dbo].[tbtext] AS [a]),
WHERE:([a].[id]=1000)) |–Table Spool |–Table
Scan(OBJECT:([Northwind].[dbo].[tbtext] AS [b])) 复制代码 代码如下: 表 ‘Worktable’。扫描计数
999,逻辑读 27001 次,物理读 0 次,预读 0 次。 表 ‘tbtext’。扫描计数
2,逻辑读 262 次,物理读 0 次,预读 0次。 进入 lazy
spool的数据完全不一样了,2008 只是进入了1000 条数据,但是2000
全都进去了。 在逻辑读上面 2008 明显低于 2000. cpu时间也明显比2000少。
通过调节id 的值,2000 我推出了一个公式 逻辑读= 10001+ ,
但是2008的算法十分奇怪, 当n 386 时 逻辑读=3+4 当 386=n=770 逻辑读=
1932+5(n-386) 2000的逻辑读是线性增长,2008 是分段的线性增长,每个分段 f
‘(x) 都不一样。 2008 的lazy
spool适合选择度高的,选择度低的时候完全不行。 从2000到2008
不单单是多了sqlos和表面上的一些功能,很多执行计划的操作符都被重写了,像lazy
spool 。 所以在升级到2008 之前, 各位朋友,是否都应该重写一下sql
呢?单单优化 索引 已经解决不了根本问题了。

按照本文操作和体会,会对sql优化有个基本最简单的了解,其他深入还需要更多资料和实践的学习:

执行计划:

  1. 建表: 复制代码 代码如下: create table
    site_user ( id int IDENTITY(1,1) PRIMARY KEY, [name] varchar(20),
    code varchar(20), date datetime ) 2. 插入8万条数据 复制代码 代码如下: declare @m int set @m=1
    while @m80000 begin INSERT INTO [demo].[dbo].[site_user] (
    [name] ,[code],date) VALUES (‘name’+CAST(@m AS VARCHAR(20))
    ,’code’+CAST(@m AS VARCHAR(20)),GETUTCDATE()) select @m=@m+1 END
    –小技巧:推荐使用类似sqlassist的工具来提高敲写sql语句的速度 3.
    设置打开一些参数的设置 复制代码 代码如下:
    SET STATISTICS IO on — 查看磁盘IO set statistics time on —
    查看sql语句分析编译和执行时间 SELECT * FROM site_user — 查看效果 4.
    查看表索引情况: sp_helpindex site_user

  2. 执行sql语句复制代码 代码如下: SELECT
    * FROM site_user su WHERE su.name=’name1’表 ‘site_user’。 扫描计数
    1,逻辑读取 446 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob
    物理读取 0 次,lob 预读 0 次

主要开销都在聚集索引扫描了。

ctrl+L 快捷键查看执行计划:

第二种,用两个top分别正序和倒序排列,共另个子查询来实现分页,复制代码 代码如下: declare @page int declare
@pagesize int set @page = 2 set @pagesize = 12 SET STATISTICS IO on
select * from ( select top (@pagesize) * from (select top
(@page*@pagesize) * from Liwu_Items order by CreateTime desc) a order
by CreateTime asc) b order by CreateTime desc 结果 (12 行受影响)表
‘Liwu_Items’。扫描计数 1,逻辑读取 7 次,物理读取 0 次,预读 317
次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。 执行计划

6.
优化第一步:聚集索引扫描开销占了100%,可以考虑优化为索引查找,在查询条件name上建立非聚集索引
复制代码 代码如下: create index
name_index on site_user(name) sp_helpindex site_user —
多出来我们新建立的索引