SQL优化案例—— RowNumber分页

时间：2023-12-09 20:57:19

将业务语句翻译成SQL语句不仅是一门技术，还是一门艺术。

下面拿我们程序开发工程师最常用的ROW_NUMBER()分页作为一个典型案例来说明。

先来看看我们最常见的分页的样子：

WITH CTE AS(
    SELECT    ROW_NUMBER() OVER ( ORDER BY (A.CreateTime ) AS OrderNo ,
        Table_A.ID ,    --主键
        Table_A.其它字段
    FROM      Table_A WITH ( NOLOCK )
    WHERE     RecID = 220051
) 
SELECT * FROM CTE
WHERE   OrderNo BETWEEN 1 AND 50;

的确，这样的写法很符合我们的思维逻辑，并且我们在RecID上建立非聚集索引，那么它的效率看上去也是不错的。当然根据这条SQL，最佳索引实践应该是:

CREATE INDEX IX_Table_A_RecID_CreateTime_Inc
ON Table_A(RecID,CreateTime)
INCLUDE(Table_A.其它字段)

但是，这真的是最佳的了吗？当SQL的Where条件变多，Table_A.其它字段变得越来越多，OVER()子句中的OrderBy字段越来越多或者变成Order By ColumnA/ColumnB这样的计算表达式，这条语句变得越来越不堪重负，最终性能问题凸现出来，另外，作为DBA，我们总是尽量维持索引的简单性、可重用度，而不想建立成为某个语句专用的索引。举例来说，在Include中，我们总不能把Table_A.其它字段中的所有字段都放进去吧，个数少还行，如果遇上几十个字段或者有大容量字符字段，维护成本将大大增加，那将是我们不愿意看到的。

这个时候就要求我们看看是否能对语句做出一些优化了。
在上面的SQL中，我们看它的执行计划，我已经建立了索引，该索引并未Include SELECT列表中的其它字段：
CREATE INDEX IX_Table_A_RecID_CreateTime_Inc
ON Table_A(RecID,CreateTime)
SQL优化案例—— RowNumber分页

根据上图的执行计划，可以看到，WHERE条件走的是我刚刚建立的索引，下面的键查找与其并行，我们先不讨论该执行计划的具体细节，下面我们来设想几个问题：

在WHERE条件简单，并且索引合适，统计信息正确的前提下，SQL Server可以很容易获得那50行，并且回到聚集索引中找到属于它的其它字段的数据，这是SQL Server的智能编译的结果，也是我们希望看到的返回方式。

但是，在WHERE条件较为复杂，多个WHERE条件均为范围字段或者状态字段时，执行计划也许并没有我们想象的那么智能了，比如它可能采用这样的方式：

SQL优化案例—— RowNumber分页

当SQL Server无法准确的取出你要的那些行时，那么它便会取回全部的行数后，再去聚集索引中找回属于它的其它字段的数据，当where条件可以返回几十万数据时，你可以想象它的效率有多低，它会仍然使用上文中类似的执行计划，这显然不是我们希望看到的。

我们想看到的是什么？

1、根据WHERE条件和排序规则，先取出那50条数据所属的主键。

SELECT ROW_NUMBER() OVER ( ORDER BY A.CreateTime ) AS OrderNo ,
Table_A.ID --主键

INTO #1
FROM Table_A WITH ( NOLOCK )
WHERE RecID = 220051

2、利用上个步骤中返回的主键，去原始表取回这50条记录的其它字段数据。

SELECT B.*,A.其它字段 FROM Table_A A WITH ( NOLOCK )
    INNER JOIN #1 B ON A.ID=B.ID
WHERE   B.OrderNo BETWEEN 1 AND 50;

那么，上面两个步骤合在一起：

WITH CTE AS(
    SELECT    ROW_NUMBER() OVER ( ORDER BY A.CreateTime ) AS OrderNo ,
        Table_A.ID    --主键
    FROM      Table_A WITH ( NOLOCK )
    WHERE     RecID = 220051
) 
SELECT CTE.*,A.其它字段 FROM Table_A A WITH ( NOLOCK )
    INNER JOIN CTE ON A.ID=CTE.ID
WHERE   CTE.OrderNo BETWEEN 1 AND 50;

很好，现在我们再来看一下这个SQL的执行计划：

SQL优化案例—— RowNumber分页

Binggo!这才是我们理想中的样子！

针对这个SQL，我们只需要建立一个合适的索引，而不用顾忌SELECT列表中那些烦人的其它列，因为他们回聚集索引取数据，也不过几百个IO而已（需要返回的行数*Index_Level）。它不需要再为过期的统计信息或者错误的执行计划而付出沉重的代价！

总结：SQL优化，是一门艺术。

相关文章