Sql Server多种分页性能的比较

比眉伴天荒 2021-06-26 16:06 383阅读 0赞

## 一.前言 ##

因为工作关系，遇到了非常大的数据量的分页问题，数据总共有8000万吧，这个显然不是简单的分页能够解决的，需要从多多方面考虑，从分表、分库等等。但是这个也让我考虑到了分页性能的问题，在不同数据量的情况下，不同的分页方法效率是否会有不同。我在这里用比较常见的几种分页方法在不同的数据量、不同页码下进行对比，分别是：`Top`、`Row_Number()`和`Offset Fetch`。这里只用它们分别最简单的语句，如下。

`Top`：

create proc Tops @pageindex int,@pagesize int
    AS
    BEGIN
    
    select top (@pagesize) * from Customers where CustomerID not in 
    (select top ((@pageindex - 1)* @pagesize) CustomerID from Customers order by CustomerID DESC) order by CustomerID DESC
    
    END

`Row_Number()`:

create proc RowNumber @pageindex int,@pagesize int
    AS
    BEGIN
    
    select * from 
    (select ROW_NUMBER() OVER(order by CustomerID desc) as px,* from Customers) as a
    where a.px between ((@pageindex - 1)* @pagesize + 1) and (@pageindex*@pagesize)
    
    END

`Offset Fetch`:

create proc Offset_Fetch @pageindex int,@pagesize int
    AS
    BEGIN
    
    select * from Customers order by CustomerID desc
    offset ((@pageindex - 1) * @pagesize) rows
    fetch next @pagesize rows only  
    
    END

三个存储过程都对数据进行了排序，设置的语句相对公平，这里默认每页10条数据。`Top`的分页是基本上所有的版本的`sql server`都可以使用的，`row_number()`是`sql 2005`以上，`offset fetch`需要`sql 2012`才支持了。

## 二.20W数据量 ##

### 1.`Top` ###

第1页十次执行平均时间29.1毫秒。  
![在这里插入图片描述][20190604204253140.png]第1万页十次执行平均时间109.2毫秒。  
![在这里插入图片描述][20190604204310201.png]第2万页十次执行平均时间126.8毫秒。  
![在这里插入图片描述][20190604204328969.png]

### 2.`Row_Number()` ###

第1页十次执行平均时间20.2毫秒。  
![在这里插入图片描述][20190604204401807.png]第1万页十次执行平均时间96.5毫秒。  
![在这里插入图片描述][20190604204428688.png]第2万页十次执行平均时间153.8毫秒。  
![在这里插入图片描述][20190604204447569.png]

### 3.`Offset Fecth` ###

第1页十次执行平均时间19.3毫秒。  
![在这里插入图片描述][20190604204512145.png]第1万页十次执行平均时间70毫秒。  
![在这里插入图片描述][2019060420464893.png]  
第2万页十次执行平均时间75.1毫秒。  
![在这里插入图片描述][20190604204704714.png]

## 三.200W数据量 ##

### 1.`Top` ###

第1页十次执行平均时间55.9毫秒。  
![在这里插入图片描述][20190604204744832.png]  
第10万页十次执行平均时间…毫秒。  
执行了好几十秒。。。。直接淘汰

### 2.`Row_Number()` ###

第1页十次执行平均时间25.5毫秒  
![在这里插入图片描述][20190604204840835.png]  
第10万页十次执行平均时间642.3毫秒  
![在这里插入图片描述][20190604204849524.png]  
第20万页十次执行平均时间1257毫秒  
![在这里插入图片描述][2019060420490068.png]

### 3.`Offset Fecth` ###

第1页十次执行平均时间24.7毫秒  
![在这里插入图片描述][20190604231515672.png]  
第10万页十次执行平均时间220.5毫秒  
![在这里插入图片描述][20190604231526815.png]  
第20万页十次执行平均时间396毫秒  
![在这里插入图片描述][20190604231546997.png]

## 三.2000W数据量 ##

### 1.Top ###

因为在上一轮被淘汰了，所以这一轮就算了。。

### 2.`Row_Number()` ###

第1页十次执行平均时间57毫秒  
![在这里插入图片描述][20190604231615557.png]  
第100W页十次执行平均时间6401.5毫秒  
![在这里插入图片描述][20190604231627166.png]  
第200W页十次执行平均时间14606.2毫秒  
![在这里插入图片描述][20190604231642933.png]

### 3.`Offset Fecth` ###

第1页十次执行平均时间27.5毫秒  
![在这里插入图片描述][20190604231707421.png]  
第100W页十次执行平均时间1778.9毫秒  
![在这里插入图片描述][20190604231717532.png]  
第200W页十次执行平均时间3523.2毫秒  
![在这里插入图片描述][20190604231725659.png]

## 四。总结 ##

可以看出来数据量越大、分页页码越大对分页效率影响就越大。`top`的分页方法早早出局，很明显是因为`not in` 的数据量太庞大了，所以要是有好的`top`分页方法可以下面留言，我也会进行测试。`row_number`的分页方法算是比较好的了，而且`sql2005`及以上的数据库都可以用，受众范围比较大，而`offset fecth`的性能更加优越，但是只有`sql2012`及以上的才支持。

[20190604204253140.png]: /images/20210626/8bf9937cc938433494f7c0f6d771d60f.png
[20190604204310201.png]: /images/20210626/5bf7b4ecfabd4f259effa5a71034fb22.png
[20190604204328969.png]: /images/20210626/4bc98d40181344acb681dda7436dd691.png
[20190604204401807.png]: /images/20210626/f3750404a66c426c8873b0a7a0385702.png
[20190604204428688.png]: /images/20210626/4e7d56a2cc284b459726187c497af834.png
[20190604204447569.png]: /images/20210626/7cfbd5483b494f61bf1b0d55fbe82753.png
[20190604204512145.png]: /images/20210626/a26b5c90dc52496badf8ab8e4a6352fc.png
[2019060420464893.png]: /images/20210626/7b76e4db95e4435aae5d0adb0272bb1e.png
[20190604204704714.png]: /images/20210626/fb4af002d929401498a3273a20a1d678.png
[20190604204744832.png]: /images/20210626/88f7fcba90704f87b39b15c9ea849506.png
[20190604204840835.png]: /images/20210626/90d8f662e31f44558bd3cd691313da63.png
[20190604204849524.png]: /images/20210626/a28d15afcf794118b26d60949da640f5.png
[2019060420490068.png]: /images/20210626/ee063f3d3b12432b947815426f13abb5.png
[20190604231515672.png]: /images/20210626/a61a2c2ede514b6894cbe0817d7cc42a.png
[20190604231526815.png]: /images/20210626/c0f73c80ca3a4c77992a675efb2856ea.png
[20190604231546997.png]: /images/20210626/105deae5f54f4846b1aa34d306b2881d.png
[20190604231615557.png]: /images/20210626/15f4a88e06fe40b783b10c24acb1fd27.png
[20190604231627166.png]: /images/20210626/901a9308eecf49929d3d07b30a015d6b.png
[20190604231642933.png]: /images/20210626/4f0cd9ca60754eebad5cfbe652feaca1.png
[20190604231707421.png]: /images/20210626/6a4b0d84d95c4794aeab03e056de1ed1.png
[20190604231717532.png]: /images/20210626/9baa7aff8d8e4c70bf4a5d90d5860f44.png
[20190604231725659.png]: /images/20210626/c4c17f988b6547ebb2dd411447f4c6c4.png