Oracle,SqlServer,MySql高性能分页方案

SqlServer和Oracle的分页方式，是我04年研究出来的。MySql的方式，是我从超群的博客看来的

http://www.fuchaoqun.com/2009/04/efficient-pagination-using-mysql/

 

所以，一并整理出来。

 

SQL Server 2000

PageCount：一页需要的数据条数
PageIndex：页索引

select top PageCount * from (
    select top PageCount * from (
        select top PageCount*PageIndex  *  from tableName order by ID) as tmp1 order by ID DESC
     ) as tmp2 order by ID
)

 

解释一下，比如在论坛中，有100条帖子，每一页显示20条帖子，如果显示第2页。则，先按照时间排序，取出前40条记录，然后40条记录按照时间反过来排序，取出前20条记录，再按照时间排序一次，按照正常的顺序读出来。所以，这个分页是三重top组合排序。

Oracle 9i：

SQL：普通的Select语句

FromIndex:从…条

ToIndex:到…条

通用的方式：

select * from (
     select row_.*, rownum rownum_ from (
       SQL
) row_ where rownum <= toIndex
) where rownum_ > fromIndex

 

解释一下，还是上面的例子，先按照时间顺序查询出来，rownum是查询结果的序号，如果是第2页的话，取出第0到40（<=toIndex）条帖子。然后取出>20的帖子。

 

有唯一标识符（ID字段）的情况排序

select * from tableName where ID in(
  select ID from (
    select rownum rownum_,ID from (
      select ID from tableName order by Code
    ) where rownum <= toIndex
  ) where rownum_ > fromIndex
)

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Mysql的分页

通用的情况是这样的：

SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20

 

文中提到一种”clue”的做法，给翻页提供一些”线索”，比如还是SELECT * FROM message ORDER BY idDESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，如果我们只提供”上一页”、”下一页”这样的跳转（不提供到第N页的跳转），那么在处理”上一页”的时候SQL语句可以是：

SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;

处理”下一页”的时候SQL语句可以是：

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;

不管翻多少页，每次查询只扫描20行。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝MySql这段来自超群博客

 

我认为这是在原有的分页基础上的一个突破。对于SqlServer和Oracle的分页也很借鉴意义。有条件的可以改写SqlServer和Oracle的翻页Sql，加上Where条件。

 

如果不加条件限制，可以看到越向后翻，性能是越低的，最后一页就是全表扫描了。如果加上限制，时间几乎是常数了。

 

分页中，索引也非常重要，在索引列上一定需要建索引，避免全表扫描。

 

总结起来就几句话：

SqlServer用top，

Oracle用rownum，

MySql用Limit，

排序列建Index，

加上Where条件，

分页高性能。