mysql百万数据分页查询 mysql数据库分页查询sql语句

一、        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条记录，相当于每页10条，取第3页的语句

这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时间长，但事实相反。因为，子句执行后返回的是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

 

这种方法执行多少 始终没有大的降势，后劲仍然很足。尤其对于数据量大的时候，该方法执行速度一点也不会降低。

使用 TOP 要求主键必须唯一，不能是联合主键。如果是联合主键，则查询出的结果会乱序的。

ROW_NUMBER()  就是生成一个顺序的行号 , 而他生成顺序的标准 , 就是后面紧跟的 OVER(ORDER BY ReportID) ，其中 ReportID 可以是联合主键。下面 , 我们看看怎么具体应用这个 RowNo 进行分页 .

SELECT TOP 10 *  FROM
 
(
        SELECT top 10 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo 
        FROM TABLE 
       ) AS A
       WHERE RowNo > " + pageIndex*10

pageIndex 就是我们需要数据的页数 .

 

但对于 SQLServer2000 的话，如果是联合主键，我还没有解决办法，如果大家有可跟我联系。谢谢大家了。

 

三、        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中 ，会出现 会引起混乱，即相同记录会出现在不同页中。

四、 DB2分页查询

1. SELECT * FROM (Select 字段1,字段2,字段3,rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a1 WHERE a1.rn BETWEEN 10 AND 20

以上表示提取第10到20的纪录

1. select * from (select rownumber() over(order by id asc ) as rowid from table where rowid <=endIndex )   
2. where rowid > startIndex