曲演杂坛--蛋疼的ROW_NUMBER函数

使用ROW_NUMBER来分页几乎是家喻户晓的东东了,而且这东西简单易用,简直就是程序员居家必备之杀器,然而ROW_NUMBER也不是一招吃遍天下鲜的无敌BUG般存在,最近就遇到几个小问题,拿出来供大家娱乐下。

---======================================================

问题1:为什么加WHERE条件就慢,不加反而快?

查询SQL:

WITH Temp AS(
SELECT * ,
ROW_NUMBER()OVER(ORDER BY T2.C6 DESC) AS RID
FROM TB001 AS T1
INNER JOIN TB002 AS T2
ON T1.C1=T2.C1
WHERE T1.C2>1000
AND T2.C3<99999
AND T1.C4=5
)
SELECT *
FROM Temp
WHERE RID BETWEEN 0 AND 10

开发大哥很激动地问我,对上面类似的的查询,如果没有WHERE RID BETWEEN 0 AND 10的话,查询在1秒内完成,如果有WHERE条件,执行超过30秒未结束,不带WHERE条件返回300行左右数据,WHERE条件过滤后返回10行数据,返回的数据行长度较小,可以忽略由于返回数据大小对网络和显示的影响,那问题出在那呢?稍微有点DBA经验的人都会很快找到问题根源--执行计划不对。

让我们换个简单的SQL来分析下

WITH Temp AS(
SELECT * ,
ROW_NUMBER()OVER(ORDER BY T1.C1 DESC) AS RID
FROM TB001 AS T1
WHERE T1.C2>1000
)
SELECT *
FROM Temp
WHERE RID BETWEEN 0 AND 10

让我们揣测下上面查询如何实现,假设在T1.C1有索引IX_C1,在T1.C2上有索引IX_C2。

实现方式1:

A=>针对CTE内部的查询,先利用索引IX_C2找出满足条件T1.C2>1000的数据,得到结果集U1

B=>对结果集U1按T1.C1排序,计算出U1中每行RID列的值,得到结果集U2

C=>对结果集U2查找满足RID BETWEEN 0 AND 10过滤的行,得到结果集U3

D=>将结果集U3返回

实现方式2:

A=>利用索引IX_C1按ORDER BY T1.C1 DESC来依次访问T1数据

B=>检查步骤A得到的行是否满足T1.C2>1000条件,将满足条件的结果放入结果集U1中,然后一次递增RID

C=>检查步骤B得到的结果集UI,当得到足够数据行(RID BETWEEN 0 AND 10)后停止步骤A和B

D=>将结果集U1返回

以上两种方式都能得到正确的返回结果,但是那种更好呢?

对于实现方式1,假设表T1有100W数据,如果满足T1.C2>1000的行只有20行,那么使用索引IX_C2快速找出满足条件的20行数据,然后对这20行数据排序也只会消耗很轻微的CPU资源;但如果满足T1.C2>1000的行只有99W行,那么排序就消耗大量CPU资源,从而导致查询慢。

对于实现方式2,假设表T1有100W数据,按照索引IX_C1 倒序遍历C1的值,如果遍历前50行便能查找到满足T1.C2>1000的10行数据,那么查询可以很快结束,只消耗少量的逻辑读;但如果需要遍历前99W数据才能找到满足T1.C2>1000的10行数据,那么就会消耗大量的逻辑读,从而导致查询慢。

由此,我们不难得出一个结论:没有绝对正确的执行计划,只有相对高质量的执行计划。

--==================================================================

我们知道,在SQL SERVER生成执行计划时,会根据输入的参数和统计信息去预估一些步骤的影响行数和开销,寻找开销较小的执行计划,对于本篇开头提到的查询,SQL SERVER很容易受到RID BETWEEN 0 AND 10的诱惑,选择类似于实现方式2的的执行计划,而数据分布情况又恰好是针对该方式最坏的情况,就出现了我们遇到的结果,查询死慢死慢的。

类似的案例还有:

1. 查询返回数据20行,然后在此查询的基础上增加ORDER BY 和TOP(10), 结果执行效率慢了很多,于是就产生了为什么对20行数据排序取TOP会这么慢的疑惑?

2. 查询返回数据20行,在查询中分别增加SELECT TOP(20)和SELECT TOP(10000),结果SELECT TOP(10000)的比SELECT TOP(20)快很多倍,我遇到的案例有SELECT TOP(10000)在5ms内完成,然后SELECT TOP(1)的十分钟都没有结果

以上案例都有相同的操作ORDER BY+TOP,ROW_NUMBER本质上也是ORDER BY+TOP,我们知道CPU资源是服务器资源中最宝贵的资源,而对结果集排序又是一个很耗CPU资源的过程,SQL SERVER为节省CPU资源选择了一个“它”认为比较合适的执行计划,结果悲剧了。

--===============================================

针对哪位开发大哥的问题,我尝试了各种写法,在不动用临时表和索引提示的情况下,我还真搞不定这SQL,于是我来了个邪恶小招数:

WITH Temp AS(
SELECT * ,
ROW_NUMBER()OVER(ORDER BY T2.C6 DESC) AS RID
FROM TB001 AS T1
INNER JOIN TB002 AS T2
ON T1.C1=T2.C1
WHERE T1.C2>1000
AND T2.C3<99999
AND T1.C4=5
)
SELECT *
FROM Temp
WHERE RID+0 BETWEEN 0 AND 10

学术派们要开始叫嚣了,这种RID+0 BETWEEN 0 AND 10写法不科学啊,效率低下,初级程序员不懂SQL写的烂SQL啊。。。

使用RID+0来骗过查询优化器,让“它”无法估算出BETWEEN 0 AND 10需要返回的行数,这样“它”只能老老实实地“先”做CET内部的查询.

PS: 我骗得过查询优化器,骗不过开发大哥,他一直认为这个写法太BT,问了其他的DBA好几次,就是不采纳我的建议,悲催啊。

--==============================================

一个小建议:

不要见到类似WHEERE C1+10>20这种的就叫嚣不好,就喊着不能走索引的口号,看看场景再说么,万一C1上就压根没有索引呢?

--===========================================================================

ROW_NUMBER在实现分页行的确很好用,但是也不是所有场景都适用,这是一个真实的例子

一个查询只有两个参数@P1和@P2,代表取第@P1行到第@P2行之间的数

当@P1=0 AND @P2=1000时,消耗是这样的:

表 'XXXDetail'。扫描计数 186,逻辑读取 4922 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'XXX'。扫描计数 1,逻辑读取 809 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。 SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 73 毫秒。

当@P1=7241284 AND @P2=7240285时,消耗是这样的:

表 'XXXDetail'。扫描计数 1468817,逻辑读取 35838994 次,物理读取 1 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'XXX'。扫描计数 1,逻辑读取 5983509 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。 SQL Server 执行时间:
CPU 时间 = 45926 毫秒,占用时间 = 56816 毫秒。

真有份这么多页的,无语吧!!!
既然无语,我就不多做解释,说多就是眼泪,看看就好。

--=============================================================================

打完收工,妹子附上

曲演杂坛--蛋疼的ROW_NUMBER函数

上一篇:VS 统计代码行数


下一篇:MongoDB学习笔记03