ROW_NUMBER() 分页查询性能解析

最新推荐文章于 2024-06-21 13:35:49 发布

Shawn

最新推荐文章于 2024-06-21 13:35:49 发布

阅读量2.2k

点赞数 1

分类专栏： CSDN.MSSQL 文章标签： statistics sql server table join testing insert

本文链接：https://blog.csdn.net/wwwwgou/article/details/6682324

版权

CSDN.MSSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

--==============================================
作者：王运亮(wwwwgou)
时间：2011-08-12
博客：http://blog.csdn.net/wwwwgou
--==============================================

IF object_id('dbo.Users', 'u') IS NOT NULL
DROP TABLE dbo.Users
GO
--#1.建表
CREATE TABLE dbo.Users
(
ID INT IDENTITY,
[Name] VARCHAR(20),
[test] VARCHAR(50),
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
ID
)ON [PRIMARY]
)ON [PRIMARY]
--#2. 建索引
CREATE UNIQUE NONCLUSTERED INDEX [Inx_Users_Name] ON dbo.Users
(
[Name]
) ON [PRIMARY]
--TRUNCATE TABLE users
--DROP INDEX [Inx_Users_Name] ON dbo.Users

--#3. 插入1百万条数据
;WITH cte_1 AS(SELECT rowno=1 UNION ALL SELECT 1),
cte_2 AS(SELECT rowno=1 FROM cte_1 a, cte_1 b),
cte_3 AS(SELECT rowno=1 FROM cte_2 a, cte_2 b),
cte_4 AS(SELECT rowno=1 FROM cte_3 a, cte_3 b),
cte_5 AS(SELECT rowno=1 FROM cte_4 a, cte_4 b),
cte_6 AS(SELECT rowno=1 FROM cte_5 a, cte_5 b),
cte_7 AS(SELECT rowno=1 FROM cte_6 a, cte_6 b)
INSERT INTO dbo.users(Name,test)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY GETDATE())-1, 'testing' FROM cte_7
GO

--#4. 2种SQL的性能对比
DECLARE
@StartRange VARCHAR(20),
@EndRange VARCHAR(20)
SELECT
@StartRange = '900001',
@EndRange = '900100'
--第一种查询方法
SET STATISTICS TIME ON
SELECT
id,
Name,
test
FROM
(
SELECT
rowno = ROW_NUMBER() OVER(ORDER BY Name),
*
FROM dbo.Users WITH(NOLOCK)
) T
WHERE rowno BETWEEN @StartRange AND @EndRange
SET STATISTICS TIME OFF

--第二种查询方法
SET STATISTICS TIME ON
SELECT
A.id,
A.Name,
A.test
FROM dbo.users A WITH(NOLOCK)
INNER JOIN
(
  SELECT
   rowno,
   id
  FROM
  (
   SELECT
    rowno = ROW_NUMBER() OVER(ORDER BY Name),
    ID
   FROM dbo.Users WITH(NOLOCK)
  ) T
  WHERE rowno BETWEEN @StartRange AND @EndRange
) B
  ON A.ID = B.ID
SET STATISTICS TIME OFF
GO
--结论
--#1. StartRange-EndRange越靠后,查询时间越长
--#2. 第二种方法比第一种方法效率高出许多. 分析如下(可从执行计划来分析):
/*
1解析: 扫描Name索引(Index Scan),每查询出的索引,根据索引键值用Key Lookup方式直接找出相应记录.
2解析: 扫描Name索引(Index Scan),查询出的所有索引,根据索引键值inner join表users的ID字段找出相应记录.
*/
--#3. 如果没有给Name字段加索引,第二种方法比第一种方法效率低(读者可根据执行计划自行分析^_^).

--本文参考:
--http://www.cnblogs.com/zanxiaofeng/archive/2011/03/26/1996185.html (SQL2005基于ROW_NUMBER()两种分页性能比较)
--http://ajava.org/course/mssql/14283.html (SQL Server语句优化详解)