MySQL、SqlServer、Oracle三大主流数据库分页查询

落日映苍穹つ 2021-09-12 04:00 390阅读 0赞

一、    MySQL 数据库分页查询 
MySQL数据库实现分页比较简单，提供了 LIMIT函数。一般只需要直接写到sql语句后面就行了。 
LIMIT子 句可以用来限制由SELECT语句返回过来的数据数量，它有一个或两个参数，如果给出两个参数， 第一个参数指定返回的第一行在所有数据中的位置，从0开始（注意不是1），第二个参数指定最多返回行数。例如： 
select \* from table WHERE … LIMIT 10; \#返回前10行 
select \* from table WHERE … LIMIT 0,10; \#返回前10行 
select \* from table WHERE … LIMIT 10,20; \#返回第10-20行数据 
 
二、    SQLServer数据库分页查询 
SQLServer数据库又分为 SQLServer2000和SQLServer2005。一般比较简单的方法是通过TOP函数来实现。如下： 
SELECT TOP 10 \* FROM sql WHERE ( 
code NOT IN   (SELECT TOP 20 code   FROM TestTable   ORDER BY id)) 
ORDER BY ID 
这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时 间长，但事实相反。因为，子句执行后返回的是20条记录，而整条语句仅返回10条语句，所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I /O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQL SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。 
以上语句的有一个致命的缺点，就是它含有NOT IN字样，要换成用not exists来代替not in，二者的执行效率实际上是没有区别的。 
在以上分页算法中，影响我们查 询速度的关键因素有两点：TOP和NOT IN。TOP可以提高我们的查询速度，而NOT IN会减慢我们的查询速度，所以要提高我们整个分页算法的速度，就要彻底改造NOT IN，同其他方法来替代它。 
我们知道，几乎任何字段，我们都可以通过max(字段)或min(字段)来提取某个字段中的最大 或最小值，所以如果这个字段不重复，那么就可以利用这些不重复的字段的max或min作为分水岭，使其成为分页算法中分开每页的参照物。在这里，我们可以 用操作符“>”或“<”号来完成这个使命。如： 
 
Select top 10 \* from table1 where id>200 
 
于是就有了如下分页方案： 
 
select top 页大小 \* 
 
from table1  
 
where id> 
 
(select max (id) from  
 
(select top ((页码-1)\*页大小) id from table1 order by id) as T 
 
)     
 
order by id

<table style="word-wrap:break-word; word-break:break-all; table-layout:fixed"> 
 <tbody style="word-wrap:break-word; word-break:break-all"> 
 <tr style="word-wrap:break-word; word-break:break-all"> 
 <td style="word-wrap:break-word; word-break:break-all; font:normal normal normal 12px/1.5em Verdana,'Lucida Grande',Arial,Helvetica,sans-serif; font-size:14px"> 这种方法执行 多少始终没有大的降势，后劲仍然很足。尤其对于数据量大的时候，该方法执行速度一点也不会降低。 使用TOP要求主键必须唯一，不能是联合主键。如果是联合主键，则查询出的结果会乱序的。 目前SQLServer2005提供了一个row_number()函数。ROW_NUMBER() 就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ReportID)，其中ReportID可 以是联合主键。下面,我们看看怎么具体应用这个RowNo进行分页. SELECT TOP 10 *&nbsp;&nbsp; FROM ( SELECT top 10 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo&nbsp; FROM TABLE&nbsp; ) AS A WHERE RowNo &gt; " + pageIndex*10 pageIndex就是我们需要数据的页数. 但对于SQLServer2000的话，如果是联合主键，我还没有解决办法，如果大家有可跟我联 系。谢谢大家了。 三、 &nbsp;&nbsp; ORCALE数据库分页查询 ORCALE数据库实现分页查询可以使用 row_number()函数或者使用rownum 虚列两种方法。 第一种：利用分析函数row_number() 方法 select * from( select t.*,row_number() over (order by t1.id) rowno from TABLE1 ) where rowno between 21 and 40; 第二种：直接 使用rownum 虚列 select * from (select t.*,rownum as rowno from TABLE1 ) where rowno between 10 and 20 这两种方法比较，显然第二种方法比较好。因为不用order by语句，会提高检索数据的速度的，尤其数据量越大时，第二种方法快速检索数据越明显。 最后提醒大家：oracle中慎用带有order by的分页。尤其是在oracle10g中，会出现会引起混乱，即相同记录会出现在不同页中。</td> 
 </tr> 
 </tbody> 
</table>