Sql Server分页分段查询百万级数据四种项目实例

实际项目中需要实现自定义分页，最关键第一步就是写分页SQL语句，要求语句效率要高。

那么本文的一个查询示例是查询第100000-100050条记录，即每页50条的结果集。查询的表名为infoTab，且id为整型自增主键字段。

 

第一种、使用NOT IN关键字

SELECT TOP 50 * FROM infoTab WHERE ID NOT IN(SELECT TOP 100000 ID FROM infoTab)

平均查询时间0.1s。这是非常简单的一种写法，效率还可以，只是使用默认ID排序，如果ID有间断，展示效果不好。

 

第二种、通过升序与降序方式实现分页查询

SELECT * FROM (SELECT TOP 50 * FROM (SELECT TOP 100050 * FROM infoTab ORDER BY ID ASC)TEMP1 ORDER BY ID DESC)TEMP2 ORDER BY ID ASC

平均查询时间0.22s。这种是利用先查询倒序的50个数据，最后又进行升序排序的方法很复杂，不推荐使用，也最浪费时间，效率最低。

 

第三种、采用MAX(ID)函数

实际是关于MIN()函数和MAX()函数的使用

SELECT TOP 50 * FROM infTab WHERE ID>(SELECT MAX(ID) FROM (SELECT TOP 100000 ID FROM infoTab ORDER BY ID)temp) 

平均查询时间0.13s。这种方法理解起来很简单，就是直接使用MAX()进行查找，然后id大于MAX()结果为100000的前50条记录即为结果。

 

第四种、最后一种推荐使用的方法，使用ROW_NUMBER

正如Oracle的rowid，解决了整型的ID字段不连续的问题。用行号（ROW_NUMBER）查询，比较高效的查询方式，只有在SQL Server2005或更高版本才支持。

注意的是，ROW_NUMBER后面必须跟着OVER(ORDER BY [FIELD])，我们来看具体的写法。

SELECT * FROM (SELECT TOP 100050 ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* FROM infoTab)t WHERE t.rowid > 100000

平均运行时间0.29s。这种方法的好处是实现连续的排序ROWID,避免了ID间断不连续的问题，展示更加美观。

　　在这里面需要注意的是OVER的括号里面可以写多个排序字段，比如：OVER(ORDER BY CreatedTime, ID)。

如果使用下面这条语句更高效，是上面语句运行时间的三分之一，平均运行时间仅0.08s。

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

 

总之，我们推荐使用第四种方法中的后面一条语句，查询效率高，而且展示结果美观，ROWID连续，尤其在千万级数据查询的时候，效率相差很大非常大。

（所有SQL语句实测通过，可直接在项目中使用。）