xml地图|网站地图|网站标签 [设为首页] [加入收藏]

热门视频

当前位置:美高梅游戏网站 > 热门视频 > mysql、mssql及oracle分页查询方法详解_数据库其它

mysql、mssql及oracle分页查询方法详解_数据库其它

来源:http://www.gd-chuangmei.com 作者:美高梅游戏网站 时间:2019-11-26 15:12

本文实例讲述了mysql、mssql及oracle分页查询方法。分享给大家供大家参考。具体分析如下:

MySql:

分页查询在web开发中是最常见的一种技术,最近在通过查资料,有一点自己的心得

MySQL数据库实现分页比较简单,提供了 LIMIT函数。一般只需要直接写到sql语句后面就行了。LIMIT子 句可以用来限制由SELECT语句返回过来的数据数量,它有一个或两个参数,如果给出两个参数, 第一个参数指定返回的第一行在所有数据中的位置,从0开始,第二个参数指定最多返回行数。例如:select * from table WHERE … LIMIT 10; #返回前10行select * from table WHERE … LIMIT 0,10; #返回前10行select * from table WHERE … LIMIT 10,20; #返回第10-20行数据

一、mysql中的分页查询

Oracle:

m=*pageSize;n= pageSize;

考虑mySql中的实现分页,select * from 表名 limit 开始记录数,显示多少条;就可以实现我们的分页效果。

pageNum是要查询的页码,pageSize是每次查询的数据量,

但是在oracle中没有limit关键字,但是有 rownum字段

select * from table order by id limit m, n;

rownum是一个伪列,是oracle系统自动为查询返回结果的每行分配的编号,第一行为1,第二行为2,以此类推。。。。

该语句的意思为,查询m+n条记录,去掉前m条,返回后n条记录。无疑该查询能够实现分页功能,但是如果m的值越大,查询的性能会越低,因为MySQL同样需要扫描过m+n条记录。

第一种:

select * from table where id > #max_id# order by id limit n;

复制代码 代码如下:SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40)WHERE RN >= 21

该查询每次会返回n条记录,却无需像方式1扫描过m条记录,在大数据量的分页情况下,性能可以明显好于方式1,但该分页查询必须要每次查询时拿到上一次查询。该查询的问题就在于,我们有时没有办法拿到上一次查询,比如当前在第3页,需要查询第5页的数据,该查询方法便爱莫能助了。

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

为了避免能够实现方式二不能实现的查询,就同样需要使用到limit m, n子句,为了性能,就需要将m的值尽力的小,比如当前在第3页,需要查询第5页,每页10条数据,当前第3页的最大id为#max_id#:

上面给出的这个分页查询语句,在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小,将结果尽快的返回。在上面的分页查询语句中,这种考虑主要体现在WHERE ROWNUM <= 40这句上。

select * from table where id > #max_id# order by id limit 20,10;

选择第21到40条记录存在两种方法,一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值,在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句,在查询的最外层控制分页的最小值和最大值。

其实该查询方式是部分解决了方式二的问题,但如果当前在第2页,需要查询第100页或1000页,性能仍然会较差。

第二种:

方法四:复制代码 代码如下:select * from table as a inner join (select id from table order by id limit m, n) as b on a.id = b.id order by a.id;该查询同方式一 一样,m的值可能很大,但由于内部的子查询只扫描了字段id,而不是整张表,所以性能要强于方式一查询,并且该查询能够解决方式二和方式三不能解决的问题。

复制代码 代码如下:select * from (select e.*,rownum r from (select * from emp order by sal desc) e ) e1 where e1.r>21 and e1.r<=40;

方式五:复制代码 代码如下:select * from table where id > (select id from table order by id limit m, 1) limit n;

红色部分:按照工资降序排序并查询所有的信息。

该查询方式同方式四,同样通过子查询扫描字段id,效果同方式四。至于性能的话,方式五的性能会略好于方式四,因为方式5不需要在进行表的关联,而是一个简单的比较。

棕色部分:得到红色部门查询的值,并查询出系统的rownum并指定上别名。这一句就比较关键,起了一个过渡的作用,首先要算出rownum来对红色部分指定上序号,也可以为蓝色外面部分用到这个变量。指定上查询的开始记录数和结束记录的条件。

二、Sql Server分页查询

蓝色部分:指定记录从第几条开始到第几条结束,取出棕色部门的值来作为查询条件的变量

适用于 SQL Server 2000/2005

总结:绝大多数的情况下,第一个查询的效率比第二个高得多。

SELECT TOP 页大小 * FROM table1 WHERE id NOT IN ( SELECT TOP 页大小* id FROM table1 ORDER BY id ) ORDER BY id

SqlServer:

适用于 SQL Server 2000/2005

分页方案一:(利用Not In和SELECT TOP分页)

 SELECT TOP 页大小 * FROM table1 WHERE id >= ( SELECT ISNULL FROM ( SELECT TOP 页大小*+1 id FROM table1 ORDER BY id ) A ) ORDER BY id

 SELECT TOP 页大小 * FROM table1 WHERE id <= ( SELECT ISNULL FROM ( SELECT TOP 页大小*+1 id FROM table1 ORDER BY id Desc ) A ) ORDER BY id Desc

语句形式:

适用于 SQL Server 2005

复制代码 代码如下:SELECT TOP 10 *

 SELECT TOP 页大小 * FROM  OVER  AS RowNumber,* FROM table1 ) A WHERE RowNumber > 页大小*

FROM TestTable

说明,页大小:每页的行数;页数:第几页。使用时,请把“页大小”和“页大小*”替换成数字。

WHERE (ID NOT IN

其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。建议优化的时候,加上主键和索引,查询效率会提高。

(SELECT TOP 20 id

通过SQL 查询分析器,显示比较:我的结论是:

FROM TestTable

分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句分页方案三: 效率最差,但是最为通用

ORDER BY id))

三、oracle分页查询

ORDER BY ID

SELECT * FROM ( SELECT A.*, ROWNUM RN FROM  A WHERE ROWNUM <= 40 ) WHERE RN >= 21;

SELECT TOP 页大小 *

这个分页比下面的执行时间少,效率高。当数据量较大时oracle会自动优化!

FROM TestTable

select * from (select c.*,rownum rn from tab c) where rn between 21 and 40

WHERE (ID NOT IN

对比这两种写法,绝大多数的情况下,第一个查询的效率比第二个高得多。

(SELECT TOP 页大小*页数 id

这是由于CBO优化模式下,Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率。

FROM 表

对于第一个查询语句,第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中,这样Oracle查询的结果一旦超过了ROWNUM限制条件,就终止查询将结果返回了。

ORDER BY id))

而第二个查询语句,由于查询条件BETWEEN 21 AND 40是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层

ORDER BY ID

(即使推到最内层也没有意义,因为最内层查询不知道RN代表什么)。因此,对于第二个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成,显然这个效率要比第一个查询低得多。

分页方案二:(利用ID大于多少和SELECT TOP分页)

上面分析的查询不仅仅是针对单表的简单查询,对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

语句形式:

本文由美高梅游戏网站发布于热门视频,转载请注明出处:mysql、mssql及oracle分页查询方法详解_数据库其它

关键词: