转载:SQL分页查询总结
【转载】SQL分页查询总结
开发过程中经常遇到分页的需求,今天在此总结一下吧。
简单说来方法有两种,一种在源上控制,一种在端上控制。源上控制把分页逻辑放在SQL层;端上控制一次性获取所有数据,把分页逻辑放在UI上(如GridView)。显然,端上控制开发难度低,适于小规模数据,但数据量增大时性能和IO消耗无法接受;源上控制在性能和开发难度上较为平衡,适应大多数业务场景;除此之外,还可以根据客观情况(性能要求,源与端的资源占用等)在源和端之间加一层,应用特殊算法和技术进行处理。以下主要讨论源上,即SQL上的分页。
分页的问题其实就是在满足条件的一堆有序数据中截取当前所需要展示的那部分。实际上各种数据库都考虑到分页问题而内置了一些策略,比如MySql的LIMIT,Oracle的ROWNUM和ROW_NUMBER(),SqlServer的TOP和ROW_NUMBER(),基于此我们可以得到一系列分页的方法。
1、 基于MySql的LIMIT和Oracle的ROWNUM,可以直接限制返回区间(以MySql为例,注意使用Oracle的ROWNUM时要应用子查询):
方法一、直接限制返回区间
SELECT * FROM table WHERE 查询条件 ORDER BY 排序条件 LIMIT ((页码-1)*页大小),页大小;
优点:写法简单。
缺点:当页码和页大小过大时,性能明显下降。
适用:数据量不大。
2、基于LIMIT(MySql)、ROWNUM(Oracle)和TOP(SqlServer),他们可以限制返回的行数,因此可以得到以下两套通用的方法(以SqlServer为例):
方法二、NOT IN
SELECT TOP 页大小 * FROM table WHERE 主键 NOT IN
(
SELECT TOP (页码-1)*页大小 主键 FROM table WHERE 查询条件 ORDER BY 排序条件
)
ORDER BY 排序条件
优点:通用性强。
缺点:当数据量较大时向后翻页,NOT IN中的数据过大会影响性能。
适用:数据量不大。
方法三、MAX
SELECT TOP 页大小 * FROM table WHERE 查询条件 AND id >
(
SELECT ISNULL(MAX(id),0) FROM
(
SELECT TOP ((页码-1)*页大小) id FROM table WHERE 查询条件 ORDER BY id
) AS tempTable
)
ORDER BY id
优点:速度快,特别是当id为主键时。
缺点:适用面窄,要求排序条件单一且可比较。
适用:简单排序(特殊情况也可尝试转换成类似可比较值处理)。
3、基于SqlServer和Oracle的ROW_NUMBER(),可以得到返回数据的行号,基于此在限制返回区间得到如下方法(以SqlServer为例):
方法四、ROW_NUMBER()
SELECT TOP 页大小 * FROM
(
SELECT TOP (页码*页大小) ROW_NUMBER() OVER (ORDER BY 排序条件) AS RowNum, * FROM table WHERE 查询条件
) AS tempTable
WHERE RowNum BETWEEN (页码-1)*页大小+1 AND 页码*页大小
ORDER BY RowNum
优点:在数据量较大时相比NOT IN有优势。
缺点:小数据量时不如NOT IN。
适用:大部分分页查询需求。
以上是自己总结的拙见,性能比较来自网上资料及个人判断,并没有深入实验,不当之处请大家指正。
原文地址:https://www.cnblogs.com/bbgasj/archive/2012/11/06/2756567.html
最新文章
- 知方可补不足~用SqlProfiler来监视数据库死锁
- Linux命令详解之—less命令
- RT-Thread的CPU使用率计算
- oracle 12 C启动问题
- Response.Expires 属性 (转载于疯狂客的BLOG)
- ( ! ) Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in D:\demo\code\yolo\index\index.php on li
- java框架之SpringCloud(7)-Config分布式配置中心
- 印象深刻的bug
- 最新更新的Windows 10切换蓝牙连接开关会导致蓝屏死机
- iOS-Core-Animation-Advanced-Techniques/13-高效绘图 【没理解】
- js-函数柯里化
- Yearning v1.3.0 发布,Web 端 SQL 审核平台
- Python编写API接口
- shell-拷贝指定目录外其他全部目录
- REDIS线上问题
- MySQL server has gone away 问题的解决方法(转)
- Java多线程——不变性与安全发布
- ruby安装devkit
- 执行Java脚本firefox启动成功,不运行test方法,且提示NullPointerException
- A Corrupt Mayor's Performance Art