SQL Server的分页优化及Row_Number()分页存在的问题

系统管理员 2021-06-26 16:06 368阅读 0赞

最近有项目反应，在服务器`CPU`使用较高的时候，我们的事件查询页面非常的慢，查询几条记录竟然要4分钟甚至更长，而且在翻第二页的时候也是要这么多的时间，这肯定是不能接受的，也是让现场用`SQLServerProfiler`把语句抓取了上来。

## 用`ROW_NUMBER()`进行分页 ##

我们看看现场抓上来的分页语句：

SELECT TOP 20 a.*,
                  ag.NAME    AS AgentServerName,,
    d.NAME     AS MgrObjTypeName,
    l.username asuserName
    FROM   eventlog AS a
           LEFT JOIN mgrobj AS b
                  ON a.mgrobjid = b.id
                     AND a.agentbm = b.agentbm
           LEFT JOIN addrnode AS c
                  ON b.addrid = c.id
           LEFT JOIN mgrobjtype AS d
                  ON b.mgrobjtypeid = d.id
           LEFT JOIN eventdir AS e
                  ON a.eventbm = e.bm
           LEFT JOIN agentserver AS ag
                  ON a.agentbm = ag.agentbm
           LEFT JOIN loginuser AS l
                  ON a.cfmoper = l.loginguid
    WHERE  a.orderno NOT IN (SELECT TOP 0 orderno
                             FROM   eventlog AS a
                                    LEFT JOIN mgrobj AS b
                                           ON a.mgrobjid = b.id
                                    LEFT JOIN addrnode AS c
                                           ON b.addrid = c.id
                             WHERE  1 = 1
                                    AND a.alarmtime >= '2014-12-01 00:00:00'
                                    AND a.alarmtime <= '2014-12-26 23:59:59'
                                    AND b.addrid IN ( '02109000', ……, '02109002'
                                                    )
                             ORDER  BY alarmtime DESC)
           AND 1 = 1
           AND a.alarmtime >= '2014-12-01 00:00:00'
           AND a.alarmtime <= '2014-12-26 23:59:59'
           AND b.addrid IN ( '02109000', ……, '02109002' )
    ORDER  BY alarmtime DESC

这是典型的使用两次`top`来进行分页的写法，原理是：先查出`pageSize*(pageIndex-1)`(`T1`)的记录数，然后再`Top`出`PageSize`条不在`T1`中的记录，就是当前页的记录。这种查询效率不高主要是使用了`not in`。参考我之前文章《程序猿是如何解决`SQLServer`占`CPU100%`的》提到的：“对于不使用`SARG`运算符的表达式，索引是没有用的”。

那么改为使用`ROW_NUMBER`分页：

WITH cte
         AS (SELECT a.*,
                    ag.NAME                      AS AgentServerName,
                    d.NAME                       AS MgrObjTypeName,
                    l.username                   asuserName,
                    b.addrid,
                    Row_number()
                      OVER(
                        ORDER BY alarmtime DESC) AS RowNo
             FROM   eventlog AS a WITH(forceseek)
                    LEFT JOIN mgrobj AS b
                           ON a.mgrobjid = b.id
                              AND a.agentbm = b.agentbm
                    LEFT JOIN addrnode AS c
                           ON b.addrid = c.id
                    LEFT JOIN mgrobjtype AS d
                           ON b.mgrobjtypeid = d.id
                    LEFT JOIN eventdir AS e
                           ON a.eventbm = e.bm
                    LEFT JOIN agentserver AS ag
                           ON a.agentbm = ag.agentbm
                    LEFT JOIN loginuser AS l
                           ON a.cfmoper = l.loginguid
             WHERE  a.alarmtime >= '2014-12-01 00:00:00'
                    AND a.alarmtime <= '2014-12-26 23:59:59'
                    AND b.addrid IN ( '02109000', ……, '02109002' ))
    SELECT *
    FROM   cte
    WHERE  rowno BETWEEN 1 AND 20;

执行时间从`14`秒提升到`5`秒，这说明`Row_Number`分页还是比较高效的，而且这种写法比`top top`分页优雅很多。

## “欺骗”查询引擎让查询按你的期望去查询 ##

但是为什么查询20条记录竟然要`5`秒呢，尤其在这个表是加上了时间索引的情况下——参考《程序猿是如何解决`SQLServer`占`CPU100%`的》中提到的索引。

我尝试去掉这句`AND b.AddrId in ('02109000',……,'02109002')`，结果不到1秒就把538条记录查询出来了，而加上地点限制这句，结果是204行。为什么结果集不大，花费的时间却相差这么多呢？查看执行计划，发现走的是另外的索引，而非时间索引。

![2019060422333762.png][]把这个疑问放到了`SQLServer`群上，很快，高桑给了回复：要想达到跟去掉地点限制这句的效果，就使用AdddrId+’’ in。

什么意思？一时没看明白，是高桑没看懂我的语句？很快，有人补充，要欺骗查询引擎。“欺骗”?还是不懂，不过我照做了，把上述cte的语句原封不动的Copy出来，然后把这句`AND b.AddrId in ('02109000',……,'02109002')`更改为了`AND b.AddrId+'' in ('02109000',……,'02109002')`，一点执行，神了！！！不到1秒就执行完了。在把执行计划一对，果然走的是时间索引：  
![在这里插入图片描述][20190604223717302.png]  
后来回味了一下，记起之前看到的查询引擎优化原理，如果你的条件中带有运算符或者使用函数等，则查询引擎会放弃优化，而执行表扫描。脑袋突然转过来了，在使用`b.AddrId+''`前查询引擎尝试把`mgrObj`表加入一起做优化，那么两个表联查，会导致预估的记录数大大增加，而使用了`b.AddrId+''`，查询引擎则会先按时间索引把记录刷选出来，这样就达到了效果，即强制先做`cte`在执行`in`条件，而不是在`cte`中进行in条件刷选。原来如此！**有时候，查询引擎过度的优化，会导致相反的效果，而你如果能够知道优化的原理，那么就可以通过一些小的技巧让查询引擎按你的期望去进行优化。**

## `ROW_NUMBER()`分页在页数较大时的问题 ##

事情到这里，还没完。后面同事又跟我反应，查询到后面的页数，又卡了！what?我重新执行上述语句，把时间范围放到2011-12-01到2014-12-26，记录数限制为为`19981`到`20000`，果然，查询要30秒左右，查看执行计划，都是一样的，为什么？

高桑怀疑是`key lookup`过多导致的，建议先分页取出`rid` 再做`key lookup`。不懂这么一句是什么意思。把执行计划和`IO`打印出来：  
![在这里插入图片描述][20190604224232860.png]看看`IO`，很明显，主要是越到后面的页数，其他的几个关联表读取的页数就越多。我推测，在`Row_Number`分页的时候，如果有表连接，则按排序一致到返回的记录数位置，前面的记录都是要参与表连接的，这就导致了越到后面的分页，就越慢，因为要扫描的关联表就越多。

难道就没有了办法了吗？这个时候宋桑英勇的站了出来：“你给表后加一个`forceseek`提示可破”。这真是犹如天籁之音，马上进行尝试。

**使用`forceseek`提示可以强制表走索引**  
查了下资料：

> `SQL Server2008`中引入的提示`ForceSeek`，可以用它将索引查找来替换索引扫描

那么，就在`eventlog`表中加上这句看看会怎样?  
![在这里插入图片描述][2019060422434859.png]果然，查询计划变了，开始提示，缺少了包含索引。赶紧加上，果然，按这个方式进行查询之后查询时间变为18秒，有进步！但是查看`IO`，跟上面一样，并没有变少。不过，总算学会了一个新的技能，而宋桑也很热心说晚上再帮忙看看。

**把其他没参与`where`的表放到`cte`外面**  
根据上面的`IO`，很快，又有人提到，把其他`left join`的表放到`cte`外面。这是个办法，于是把除`eventlog`、`mgrobj`、`addrnode`的表放到外面，语句如下：

WITH cte AS
    (
              SELECT    a*,
                        b.addrid,
                        b.NAME AS mgrobjname,
                        b.mgrobjtypeid ,
                        row_number() OVER(ORDER BY alarmtime DESC) AS rowno
              FROM      eventlog                                   AS a
              LEFT JOIN mgrobj                                     AS b
              ON        a.mgrobjid=b.id
              AND       a.agentbm=b.agentbm
              LEFT JOIN addrnode AS c
              ON        b.addrid=c.id
              WHERE     a.alarmtime>='2011-12-01 00:00:00'
              AND       a.alarmtime<='2014-12-26 23:59:59'
              AND       b.addrid+'' IN ('02109000',
                                        ……,
                                        '02109002') )
    SELECT    a.* ,
              ag.NAME    AS agentservername ,
              d.NAME     AS mgrobjtypename,
              l.username AS username
    FROM      cte a
    LEFT JOIN eventdir AS e
    ON        a.eventbm=e.bm
    LEFT JOIN mgrobjtype AS d
    ON        a.mgrobjtypeid=d.id
    LEFT JOIN agentserver AS ag
    ON        a.agentbm=ag.agentbm
    LEFT JOIN loginuser AS l
    ON        a.cfmoper=l.loginguid
    WHERE     rowno BETWEEN 19980 AND       20000;

果然有效，IO大大减少了，然后速度也提升到了16秒。

表 'loginuser'。扫描计数 1，逻辑读取 63 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'agentserver'。扫描计数 1，逻辑读取 1617 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'mgrobjtype'。扫描计数 1，逻辑读取 126 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'eventdir'。扫描计数 1，逻辑读取 42 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'addrnode'。扫描计数 1，逻辑读取 119997 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'eventlog'。扫描计数 1，逻辑读取 5027 次，物理读取 3 次，预读 5024 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'mgrobj'。扫描计数 1，逻辑读取 24 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

我们看到，`addrNode`表还是扫描计数很大。那还能不能提升，这个时候，我想到了，先把`addrNode、mgrobj、mgrobjtype`三个表联合查询，放到一个临时表，然后再和`eventlog`做`inner join`，然后查询结果再和其他表做`left join`，这样还能减少`IO`。

**使用临时表存储分页记录在进行表连接减少IO**

IF Object_id('tmpMgrObj') IS NOT NULL
      DROP TABLE tmpmgrobj
    
    SELECT m.id,
           addrid,
           mgrobjtypeid,
           agentbm,
           m.NAME,
           a.NAME AS AddrName
    INTO   tmpmgrobj
    FROM   dbo.mgrobj m
           INNER JOIN dbo.addrnode a
                   ON a.id = m.addrid
    WHERE  addrid IN( '02109000', ……, '02109002' );
    
    WITH cte
         AS (SELECT a.*,
                    b.addrid,
                    b.mgrobjtypeid,
                    Row_number()
                      OVER(
                        ORDER BY alarmtime DESC) AS RowNo,
                    ag.NAME                      AS AgentServerName,
                    d.NAME                       AS MgrObjTypeName,
                    l.username                   AS userName
             FROM   eventlog AS a
                    INNER JOIN tmpmgrobj AS b
                            ON a.mgrobjid = b.id
                               AND a.agentbm = b.agentbm
                    LEFT JOIN mgrobjtype AS d
                           ON b.mgrobjtypeid = d.id
                    LEFT JOIN agentserver AS ag
                           ON a.agentbm = ag.agentbm
                    LEFT JOIN loginuser AS l
                           ON a.cfmoper = l.loginguid
             WHERE  alarmtime > '2011-12-01 00:00:00'
                    AND alarmtime <= '2014-12-26 23:59:59')
    SELECT *
    FROM   cte
    WHERE  rowno BETWEEN 19980 AND 20000
    
    IF Object_id('tmpMgrObj') IS NOT NULL
      DROP TABLE tmpmgrobj

这次查询仅用了10秒。我们来看看IO：

表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'mgrobj'。扫描计数 1，逻辑读取 24 次，物理读取 2 次，预读 23 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'addrnode'。扫描计数 1，逻辑读取 6 次，物理读取 3 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    
    ----------
    表 'loginuser'。扫描计数 0，逻辑读取 24 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'eventlog'。扫描计数 93，逻辑读取 32773 次，物理读取 515 次，预读 1536 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'tmpMgrObj'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'mgrobjtype'。扫描计数 1，逻辑读取 6 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'agentserver'。扫描计数 1，逻辑读取 77 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

除了`eventlog`之外，其他的表的`IO`大大减少，有木有？

**`inner join`和`left join`的区别**  
但是，多执行几次测试，发现上述语句还是有一点问题：查询第一页的时候，也竟然要用5秒，而查询时间在当前一个月份的，也接近5秒。这是为什么呢？这个时候，宋桑再伸援手，提供了另外一个SQL语句，在查询前面几页的时候1秒就出来了，而后面的页数，则变化不大。我仔细比较了两个语句，原来我用的是`inner join`，而宋桑给的是`left join`。这两个有什么区别呢。仔细对比查询计划之后发现，使用`inner join`的时候，查询引擎会先执行`inner join`而非子查询，而使用`left join`则查询引擎先执行子查询。因此如果使用了`inner join`会导致在查询1个月的数据时，没有有效利用了时间索引。最终，我研究出来的语句如下，在查询最新数据或者前面几页的数据，能够在1秒左右出来，而查询后面的页数，在10秒左右，基本解决了问题。

IF Object_id('tmpMgrObj') IS NOT NULL
      DROP TABLE tmpmgrobj
    
    SELECT m.id,
           addrid,
           mgrobjtypeid,
           agentbm,
           m.NAME,
           a.NAME AS AddrName,
           t.NAME AS MgrObjTypeName
    INTO   tmpmgrobj
    FROM   dbo.mgrobj m
           INNER JOIN dbo.addrnode a
                   ON a.id = m.addrid
           INNER JOIN dbo.mgrobjtype t
                   ON m.mgrobjtypeid = t.id
    WHERE  addrid + '' IN( '02109000', ……, '02109002' );
    
    SELECT tmp.*,
           ag.NAME    AS AgentServerName,
           l.username AS userName
    FROM   (SELECT a.*,
                   b.mgrobjtypename,
                   b.addrid,
                   Row_number()
                     OVER (
                       ORDER BY alarmtime DESC ) AS RowNo
            FROM   (SELECT *
                    FROM   eventlog
                    WHERE  alarmtime >= '2011-12-01 00:00:00'
                           AND alarmtime <= '2014-12-26 23:59:59') AS a
                   LEFT JOIN tmpmgrobj AS b
                          ON a.mgrobjid = b.id
                             AND a.agentbm = b.agentbm) tmp
           LEFT JOIN eventdir AS e
                  ON tmp.eventbm = e.bm
           LEFT JOIN agentserver AS ag
                  ON tmp.agentbm = ag.agentbm
           LEFT JOIN loginuser AS l
                  ON tmp.cfmoper = l.loginguid
    WHERE  tmp.rowno BETWEEN 1 AND 20;
    
    IF Object_id('tmpMgrObj') IS NOT NULL
      DROP TABLE tmpmgrobj

## 其他优化参考 ##

在另外的群上讨论时，发现使用`ROW_NUMBER`分页查询到后面的页数会越来越慢的这个问题的确困扰了不少的人。

有的人提出，谁会这么无聊，把页数翻到几千页以后?一开始我也是这么想的，但是跟其他人交流之后，发现确实有这么一种场景，我们的软件提供了最后一页这个功能，结果……当然，一种方法就是在设计软件的时候，就去掉这个最后一页的功能；另外一种思路，就是查询页数过半之后，就反向查询，那么查询最后一页其实也就是查询第一页。

还有一些人提出，把查询出来的内容，放到一个临时表，这个临时表中的加入自增`Id`的索引，这样，可以通过辨别Id来进行快速刷选记录。这也是一种方法，我打算稍后尝试。但是这种方法也是存在问题的，就是无法做到通用，必须根据每个表进行临时表的构建，另外，在超大数据查询时，插入的记录过多，因为索引的存在也是会慢的，而且每次都这么做，估计CPU也挺吃紧。但是不管怎么样，这是一种思路。

你有什么好的建议？不妨把你的想法在评论中提出来，一起讨论讨论。

## 总结 ##

现在，我们来总结下在这次优化过程中学习到什么内容：

*  在`SQLServer`中，`ROW_NUMBER`的分页应该是最高效的了，而且兼容`SQLServer2005`以后的数据库
 *  通过“欺骗”查询引擎的小技巧，可以控制查询引擎部分的优化过程
 *  `ROW_NUMBER`分页在大页数时存在性能问题，可以通过一些小技巧进行规避
    
     *  尽量通过`cte`利用索引
     *  把不参与`where`条件的表放到分页的`cte`外面
     *  如果参与`where`条件的表过多，可以考虑把不参与分页的表先做一个临时表，减少`IO`
 *  `inner join`会优先于子查询，而`left join`不会
 *  使用`with(forceseek)`可以强制查询因此进行索引查询

[2019060422333762.png]: /images/20210626/4b2ee8d7fb264277a5e8df35bb7b4b95.png
[20190604223717302.png]: /images/20210626/dcef8419dd964e65ad53a99da6c6f6b2.png
[20190604224232860.png]: /images/20210626/e65472083f904e24ab29093e92fdf686.png
[2019060422434859.png]: /images/20210626/07a10b053c034834a8c9d587a9d171d3.png