一种数据库分页查询方案

                                                      一种数据库分页查询方案
【摘要】
本文旨在介绍一种对数据库中的大数据量表格进行分页查询的实现方法，该方法对应用服务器、数据库服务器、查询客户端的cpu和内存占用都较低，查询速度较快，是一个较为理想的分页查询实现方案。
1．问题的提出
在软件开发中，大数据量的查询是一个常见的问题，经常会遇到对大量数据进行查询的场景。常见的对大数据量查询的解决方案有以下两种：一、将全部数据先查询到内存中，然后在内存中进行分页，这种方式对内存占用较大，必须限制一次查询的数据量。二、采用存储过程在数据库中进行分页，这种方式对数据库的依赖较大，不同的数据库实现机制不通，并且查询效率不够理想。以上两种方式对用户来说都不够友好。
2．解决思路
通过在待查询的数据库表上增加一个用于查询的自增长字段，然后采用该字段进行分页查询，可以很好地解决这个问题。下面举例说明这种分页查询方案。
一、在待查询的表格上增加一个long型的自增长列，取名为“queryId”,mssql、sybase直接支持自增长字段，oracle可以用sequence和trigger来实现。然后在该列上加上一个索引。添加queryId列的语句如下：
Mssql: [QUERYID] [bigint] IDENTITY (1, 1)
Sybase: QUERYID  numeric(19)  identity
Oracle:
CREATE SEQUENCE queryId_S
         INCREMENT BY 1
         START WITH 1
            MAXVALUE 999999999999999 MINVALUE 1
            CYCLE
            CACHE 20
            ORDER;
CREATE OR REPLACE TRIGGER queryId_T BEFORE INSERT
             ON "test_table"
             FOR EACH ROW
BEGIN
      select queryId_S.nextval into :new.queryId from dual;
END;
二、在查询第一页时，先按照大小顺序的倒序查出所有的queryId，语句如下：select queryId from test_table where + 查询条件 +order by queryId desc 。因为只是查询queryId字段，即使表格中的数据量很大，该查询也会很快得到结果。然后将得到的queryId保存在应用服务器的一个数组中。
三、用户在客户端进行翻页操作时，客户端将待查询的页号作为参数传递给应用服务器，服务器通过页号和queyId数组算出待查询的queyId最大和最小值，然后进行查询。
算出queyId最大和最小值的算法如下,其中page为待查询的页号，pageSize为每页的大小，queryIds为第二步生成的queryId数组：
        int startRow = (page - 1) * pageSize;
        int endRow = page * pageSize - 1;
        if (endRow >=queryIds.length)
        {
            endRow = this.queryIds.length - 1;
        }
        long startId =queryIds[startRow];
        long endId =queryIds[endRow];
查询语句如下：
String sql = "select * from test_table" + 查询条件 + "(queryId <= " + startId + " and queryId >= " + endId + ")";
3．效果评价
该分页查询方法对所有数据库都适用，对应用服务器、数据库服务器、查询客户端的cpu和内存占用都较低，查询速度较快，是一个较为理想的分页查询实现方案。经过测试，查询4百万条数据，可以在3分钟内显示出首页数据，以后每一次翻页操作基本在2秒以内。内存和cpu占用无明显增长。