ROW_NUMBER() 性能探讨

十三笑兮兮 2012-02-29

展开全文

SQL Server 2005之后，令人困扰的分页问题似乎有了进展，那就是用ROW_NUMBER()，典型的语句如下SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY XXX) AS RowNo
FROM tbl
) AS A
WHERE RowNo >= 11 and RowNo <= 20;
但这里忽略了一个重要的问题，那就是中间那个SELECT语句，实际上相当于做了一个SELECT *，虽然没在结果集看到，但没看到不表示数据库没做。
为什么看起来查询很快，其实是因为后面的RowNo的范围，网上流传的示例RowNo都较小，所以看不出问题，在上例中，SQL Server发现后面的条件之后，中间的SELECT被作了类似TOP 20的处理，也就是只取了20行，所以查询非常快。但把RowNo的查询范围改为10000000到1000010之后，则相当于先TOP 10万行，这时速度就明显慢下来了。
所以说，这个表达式的本质只是先TOP X，再选出X-y,X这个小范围的行，相比以前并没有什么进步。

Quesqions:ROW_NUMBER() 真的性能不那么好吗？ROW_NUMBER()是不是用到了临时表？

Talk:

对40万条记录的表与2万条记录的两个表inner join 联合查询，应用

SQL code

SELECT TOP 页大小 *

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1

) A

WHERE RowNumber > 页大小*(页数-1)

试用结果：比top有很大性能提升，翻页到1万页（10万条）不再死掉，但4万页（40万）左右开始，照样经常死掉。
修改查询语句为如下形式，也是一样

SQL code

SELECT TOP 页大小 *

FROM

(

SELECT top 页大小*(页数) ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1

) A

WHERE RowNumber > 页大小*(页数-1)

修改查询语句为如下形式，也是一样

SQL code

SELECT TOP 页大小 * FROM ( SELECT top 页大小*(页数) ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,col1,col2,col3,col4 FROM table1 ) A WHERE RowNumber > 页大小*(页数-1)

在刚出来工作时! 大多数人只知道用rownum 来进行分页，或是sql嵌套来分页
后来觉得效率挺低的。而且rownum是先分配行号，再进行排序！
一句话麻烦！

后来我再对数据库特殊sql的研究之后，觉得我以前的3层嵌套实现分页的效率也是很低的，

直到用了 row_number() over(partition by xx order by xxx desc/asc)
函数之后，才觉得这种方法是最简单和效率最高的！

当然也举一个实例来比较一下

create table T_NEWS
(
ID      NUMBER,
N_TYPE VARCHAR2(20),
N_TITLE VARCHAR2(30),
N_COUNT NUMBER
)
;
prompt Disabling triggers for T_NEWS...
alter table T_NEWS disable all triggers;
prompt Loading T_NEWS...
insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
values (1, 'IT', '爱it1', 100);
insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
values (2, '体育', '爱体育1', 10);
insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
values (3, '体育', '爱体育2', 30);
insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
values (4, 'IT', '爱it2', 300);
insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
values (5, 'IT', '爱it3', 200);
insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
values (6, '体育', '爱体育3', 20);
insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
values (7, '体育', '爱体育4', 60);
commit;

第一步：我先用rownum

--分页 row_number，不是rownum
--根据n_count从大到小排列，每页3条
SELECT ROWNUM r,t.* FROM t_news t
WHERE ROWNUM<=3
ORDER BY t.n_count DESC
--问题：为什么order by以后，行号是乱的？
SELECT ROWNUM r,t.* FROM t_news t
--原因：先分配了行号，再根据n_count排序

--所以必须排序，再生成行号
SELECT ROWNUM r,t.* FROM (
   SELECT t.* FROM t_news t ORDER BY t.n_count DESC ) t

--分页
--err
SELECT ROWNUM r,t.* FROM (
   SELECT t.* FROM t_news t ORDER BY t.n_count DESC ) t
WHERE r between 1 AND 3

--第1页
SELECT ROWNUM r,t.* FROM (
   SELECT t.* FROM t_news t ORDER BY t.n_count DESC ) t
WHERE ROWNUM between 1 AND 3

--第2页
SELECT ROWNUM r,t.* FROM (
   SELECT t.* FROM t_news t ORDER BY t.n_count DESC ) t
WHERE ROWNUM between 4 AND 6
--error: ROWNUM必须从1开始！
SELECT k.* FROM (
SELECT ROWNUM r,t.* FROM (
   SELECT t.* FROM t_news t ORDER BY t.n_count DESC ) t
) k
WHERE r BETWEEN 4 AND 6

--麻烦，效率低！

***** 第二步:我用row_number() over()函数
select t2.* from
    (select t.*,row_number()over(order by t.n_count desc) orderNumber from t_news t order by t.n_count desc)t2 where orderNumber between 1and 3;

*****************************************************************************************************************************************88
SELECT * FROM (
SELECT t.*,row_number() over(ORDER BY n_count DESC) r
FROM t_news t
ORDER BY t.n_count DESC
) t
WHERE r BETWEEN 4 AND 6

--通用语法：解析函数() over(partition by 字段 order by 字段)

--话外题: 取n到m条记录的语句

select top m * from tablename where id not in (select top n id from tablename)

select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入

set rowcount n

select * from 表变量 order by columnname desc

select top n * from

(select top m * from tablename order by columnname) a

order by columnname desc

4.如果tablename里没有其他identity列，那么：

select identity(int) id0,* into #temp from tablename

取n到m条的语句为：

select * from #temp where id0 >=n and id0 <= m

如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：

exec sp_dboption 你的DB名字,'select into/bulkcopy',true

5.如果表里有identity属性，那么简单：

select * from tablename where identitycol between n and m

如果是sql server 2005 可以这样写：

select top 20 * from T order col

except

select top 2 * from T order col

--话外题2：--方法1：

--适用于 SQL Server 2000/2005

SELECT TOP 页大小 *

FROM table1

WHERE id NOT IN

          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id

ORDER BY id

--方法2：

--适用于 SQL Server 2000/2005

SELECT TOP 页大小 *

FROM table1

WHERE id >

          SELECT ISNULL(MAX(id),0)

          FROM

                SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id

) A

ORDER BY id

--方法3：

--适用于 SQL Server 2005

SELECT TOP 页大小 *

FROM

        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1

) A

WHERE RowNumber > 页大小*(页数-1)

--方法四 适用于 SQL Server 2005

DECLARE @pagenum AS INT, @pagesize AS INT

SET @pagenum = 2

SET @pagesize = 3

SELECT *

FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,

        newsid, topic, ntime, hits

      FROM news) AS D

WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

ORDER BY newsid DESC

说明，页大小：每页的行数；页数：第几页。使用时，请把“页大小”和“页大小*(页数-1)”替换成数字。

--题外话3

ROW_NUMBER()函数是Sql 2005中新添的一个函数。通常它被用在分页的SQL语句中。

微软官方的对此函数的描述是：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。

我对此的理解：利用此函数可以为表中的某个字段建立序列，从1开始。就是说，根据已存在的某列，利用此函数可建立一新列，新列是数字，按照已存在列的顺序从1开始。

上边可能说的不是很清楚，请结合下边这个例子来看吧！

Code
1USE Northwind
2GO
3
4--
5SELECT OrderID,
6    CustomerID,
7    OrderDate
8FROM dbo.Orders WITH(NOLOCK)
9
10SELECT OrderID,
11    CustomerID,
12    OrderDate,
13    ROW_NUMBER() OVER(ORDER BY orderID DESC) AS 字段编号
14FROM dbo.Orders WITH(NOLOCK)

第一个查询只是一个普通的查询，查询出Orders表中的3个字段的记录。其结果为：

第二个查询相比第一个查询仅仅是多了“ ROW_NUMBER() OVER(ORDER BY orderID DESC) AS 字段编号 ”这段语句。我们来分析一下这段语句。

这段语句的作用就是利用ROW_NUMBER()函数根据OrderID这列来生成一个新的数据列，这列的名称为字段编号。然后我们根据ORDER BY orderID DESC来指定字段编号这列按照 OrderID的逆序来生成，结果如下：

比较两个结果集，会发现第二个结果集是按照OrderID字段逆序的记录，其实也可看成是字段编号这个字段的正序排序，ROW_NUMBER()函数相当于为SELECT语句末尾加了ORDER BY子句，第二个SELECT语句其实等价于：

Code
1SELECT OrderID,
2    CustomerID,
3    OrderDate,
4    ROW_NUMBER() OVER(ORDER BY OrderID DESC) AS 字段编号
5FROM dbo.Orders WITH(NOLOCK)
6ORDER BY 字段编号ASC

下边来看一个对ROW_NUMBER()函数的简单分页应用。

Code
1--Partition Page Demo
2/**//* 取出第- 200条的记录（记录须按OrderID排序）*/
3
4WITH OrdersByOrderIDASC AS
5(
6    SELECT OrderID,
7        CustomerID,
8        OrderDate,
9        ROW_NUMBER() OVER(ORDER BY orderID ASC) AS 字段编号
10    FROM dbo.Orders WITH(NOLOCK)
11)
12
13SELECT *
14FROM OrdersByOrderIDASC WITH(NOLOCK)
15WHERE 字段编号BETWEEN 100 AND 110

首先把应用ROW_NUMBER()函数后的结果集存在一张临时表中，然后以字段编号这个字段为条件，使用BETWEEN关键字过滤相应的记录。

--题外话4：

日前為了解決公司系統效能的問題,真的是挖了不少解決方案出來,其中一個比較有意思的就是分頁處理.

分頁方式其實有非常的多種,例如把資料全部丟到Web,再分頁顯示,或是直接在DataAdapter.Fill時,下筆數,或是用Selete top 加上一些過濾及排序等動作.

但這些做法,都不能解決我現在的頻寬問題,因為分店連回總公司只有2M/256K的頻寬,如果一次倒回大量的資料,一定會打爆網路,而總公司的架構部份,WebService與DataBase也是不同台的伺服器,兩台伺服器也是需透過網路來傳輸,所以思考方向會是在如何在DataBase那,就做好分頁的動作. 不要有大量的資料在網路上傳輸.

為了這個問題,找了不少方法,像是Select top的方式,但因為程式查詢彈性的關係,造成這部份的邏輯相當的複雜,效能也不好. 最後找到SQL 2005才有的Row_number()這個Function可以用. 從一些參考網站所做的效能評比中,也算是數一數二的做法,所以這做法也是最後被公司採用的解決方案.

以下就是說明Row_number() 的基本做法.

select a.UserName,a.UserID from (select row_number() over (order by UserID) as UID,UserName,UserID from UserDataInfo where bmi>20) as a where a.UID between @SP and @EP

這段語法就是關鍵所在, 正常來說,我的語法只有紅字部份,帶出所有bmi>20的人員,一次把資料全部回傳, 但這裡卻多了一段藍字在裡面,Row_Number()就是將符合條件的結果,再從1開始, 依序給予編號,所以回傳的結果就會是