【转】分页查询介绍及好处简介
1 背景概述
、sql Server 三种数据库进⾏分页查询的⽤法。
2 名词解释
分页查询
就是将将过多的结果在有限的界⾯上分多页来显⽰，⼀般将分页查询分为两类：逻辑分页、物理分页。
逻辑分页是在⽤户第⼀次访问时，将数据库的所有记录全部查询出来，添加到⼀个⼤集合中，然后存放在session对象，再通过页码计算出当前页需要显⽰的数据内容，存储到⼀
个⼩的list的集合中，并将其存储到request对象中，跳转到JSP页⾯，进⾏遍历显⽰。当⽤户第⼆次访问时，只要不关闭，还会从session中获取数据，来进⾏显⽰。因为此种⽅
法是在内存的session对象中进⾏计算分页显⽰的，⽽不是真正的将我们数据库进⾏分页的，所以叫做逻辑分页。
缺点：如果需要查询的数据量过⼤，session将耗费⼤量的内存；因为是在session中获取数据，如果第⼆次或者更多此的不关闭浏览器访问，会直接访问session，从⽽不能保证
数据是最新的。
优点：统⼀代码处理⽅式，较容易跨数据库做迁移。
物理分页，使⽤数据库⾃⾝所带的分页机制，例如，Oracle数据库的rownum，或者My中的limit等机制来完成分页操作。因为是对数据库的数据进⾏分页条件查询，所以叫物理
分页。每⼀次物理分页都会去连接数据库。
优点：数据能够保证最新，由于根据分页条件会查询出少量的数据，所以不会占⽤太多的内存。
CTE（Common Table Expression，公⽤表表达式）
该表达式源⾃简单查询，可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执⾏范围内定义的临时结果集。CTE 与派⽣表类似，具体表现
在不存储为对象，并且只在查询期间有效。与派⽣表的不同之处在于，CTE 可⾃引⽤，还可在同⼀查询中引⽤多次。
3 分页查询的好处
1，提升性能，减⼩内存的压⼒（减低宽带使⽤，提升访问速度）。⼀次查20个，⽐⼀次查20000个性能确定更好；另外若是数据量很⼤，⼀次性将内容都查询出来，查询出来的结果是放在内存⾥⾯的，内存没有这么⼤2，根据⽤户的须要，提供适当的数据。如新闻，通常⼈可能只看最近前20条；若是咱们将后⾯的也都查询出来了，就是浪费
3，展⽰层⾯的考虑：若是⼀次展⽰太多的数据，不论是排版，仍是美观上都很差
4，查询效率快，由于只显⽰每页的条数，⽽不是把全部的数据加载出来，另外页⾯好看，好⽐⼏⼗万的数据，你不分页会把页⾯撑爆
4 实现思路
通过物理分页的⽅法进⾏数据库查询。
5 实现步骤
⾸先通过开发平台新建⼀个⼯程，使⽤新⼯程中的⽇志模块作为样例，进⾏和oracle的分页查询功能
5.1 Mysql
在数据库中进⾏操作：
mysql的分页查询是最简单的，借助关键字limit即可实现查询，查询语句通⽤形式：
select o.* from (sql) o limit firstIndex,pageSize
其中的sql可以是单表查询的结果也可以是多表查询的结果
firstIndex为显⽰结果的起始位置（mysql是从0作为起始位置的）
pageSize为显⽰记录数
直接对表进⾏查询如下，我们可以看到查询时间是0.005s
采⽤分页查询，⼀页显⽰15条数据，查询时间是0.001s
在⼯程中体现：
⾸先在⼯程中到SystemLogQueryImpl.java这个类
分页预览其中有⼀点需要注意，⽅法的返回值需要是PageList⽽不是List
在开发平台中这个类是⽇志功能的服务实现类，
其中，ec_p为页数，ec_rd为显⽰记录数
staratNum为起始索引，endNum为结束索引
由于MySQL的起始索引是从0开始的，需要对得到的起始索引减⼀，显⽰记录数的计算⽅法为：结束索引-起始索引+1
在sqlMap中的体现如图：
在页⾯上的显⽰：
5.2 Oracle
Oracle的查询⽅法有两种：ROWNUM、row_number()
5.2.1 ROWNUM
在数据库中进⾏操作：
查询语句通⽤形式：
select * from(select o.*,ROWNUM num from(sql) o where ROWNUM<=(endIndex)) where num>=firstIndex
直接对表进⾏查询，耗时16msecs
采⽤分页算法进⾏查询是，耗时7msecs
在⼯程中体现：
到对应的服务实现类：
服务实现类的内容：
startNum为起始索引，endNum为结束索引
因为ROWNUM⽅法中使⽤的两个参数⼀个是起始索引，⼀个是结束索引，所以可以直接使⽤。
在sql中的体现：
在页⾯上的效果：
5.2.2 row_number()
查询语句通⽤形式：
select * from(select * from(select t.*,row_number() over(order by orderColumn) as rownumber from(sql) t) p wnumber>firstIndex) where rownum<=pageSize
直接对表进⾏查询，耗时22msecs
使⽤分页查询语句进⾏查询，耗时12msece
在⼯程中体现：
服务实现类的内容和ROWNUM⼀样，区别在sql中
由于在sql中添加了order by排序函数，查询速率会变慢，所以在开发平台中不采⽤这种⽅法。
对于oracle的分页查询，他们的主要区别是：使⽤rownum进⾏排序的时候是先对结果集加⼊伪列rownum然后再进⾏排序，⽽函数row_number()在包含排序从句后是先排序再计算⾏号码。
5.3 Sqlserver
由于sqlserver版本⽐较多，分页查询的⽅式也有不同之处
5.3.1 Sqlserver 2005/2008中使⽤row_number()
查询通⽤形式：
SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY orderColumn) AS RowNumber FROM tableName) EmployeePage WHERE RowNumber > =startIndex AND RowNumber <= endIndex ORDER BY orderColumn
GO
直接查询表，如图
使⽤分页查询，如图
5.3.2 SQL 2005/2008⽤CTE的⽅式实现
查询通⽤形式：
WITH EmployeePage AS (SELECT *,ROW_NUMBER() OVER (ORDER BY orderColumn) AS RowNumber FROM tableName)
SELECT *FROM EmployeePage WHERE RowNumber > =firstIndex AND RowNumber <= endIndex ORDER BY orderColumn
GO
使⽤分页查询，如图：
5.3.3 SQL SERVER 2012⽀持了OFFSET
查询通⽤形式，如下：
SELECT * FROM tableName
ORDER BY orderColumn
OFFSET （page-1） ROWS FETCH NEXT size ROWS ONLY
Page指需要显⽰的页数
Size指需要显⽰的记录数
发布评论