大数据量分页查询方法（转）

      本文旨在介绍一种对数据库中的大数据量表格进行分页查询的实现方法，该方法对应用服务器、数据库服务器、查询客户端的cpu和内存占用都较低，查询速度较快，是一个较为理想的分页查询实现方案。 

1．问题的提出 
在软件开发中，大数据量的查询是一个常见的问题，经常会遇到对大量数据进行查询的场景。

常见的对大数据量查询的解决方案有以下两种：

（1）、将全部数据先查询到内存中，然后在内存中进行分页，这种方式对内存占用较大，必须限制一次查询的数据量。

（2）、采用存储过程在数据库中进行分页，这种方式对数据库的依赖较大，不同的数据库实现机制不通，并且查询效率不够理想。以上两种方式对用户来说都不够友好。

 
2．解决思路 
通过在待查询的数据库表上增加一个用于查询的自增长字段，然后采用该字段进行分页查询，可以很好地解决这个问题。下面举例说明这种分页查询方案。 

（1）、在待查询的表格上增加一个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; 

（2）、在查询第一页时，先按照大小顺序的倒序查出所有的queryId，

语句如下：select queryId from test_table where + 查询条件 +order by queryId desc 。

因为只是查询queryId字段，即使表格中的数据量很大，该查询也会很快得到结果。然后将得到的queryId保存在应用服务器的一个数组中。 

（3）、用户在客户端进行翻页操作时，客户端将待查询的页号作为参数传递给应用服务器，服务器通过页号和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占用无明显增长。

 

补充：

不久前我也开发过这样的一个数据库，解决的办法是： 1 硬件方面，提高内存容量（这是最重要的），将更多的内存给予ORACLE固定使用。 2 数据库方面，拆分大型表单，使用分时间段数据库，我单位有一个巨型数据也是采用了这种方法！（这非常重要，速度可以提高几十倍左右）。 3 编程方面，尽量不要使用ODBC，采用ORACLE驱动编程，用ODBC太慢。加入每日的统计，加入到你的日报表中去，月底可以加入每月的报表等。 4 看书，查看ORACLE的书籍，对这方面问题应该会有很好的回答（看的书，应该在700页以上，以清华的书为佳）。

 

我认为数据分区、分成多个表、增加内存、换更好的机器都是物理上的，当然她带来的速度的改善是有的。但是性能的改善一般比较少做多10倍到100倍之间。 对Oracle我不熟悉，但在SQL Server中最有效和可行的办法是优化数据库结构和索引。 对于优化数据库有根据事务型和数据仓库型分为两个方面。 偏重事务需要插入、更新速度快，所以一般这样的表索引比较少，字段数目也少 数据仓库需要查询速度快，他一般会根据查询可能出现的条件建立所有的索引，形成所谓的索引覆盖。在大数据量的数据库中，一旦某个查询不能完全利用索引，就会形成表扫描。这是最坏的情况，查询速度同数据量成正比。而如果能完全利用索引，查询速度只有在数据量变化几个等级才会有一些变化。我曾经测试过一个库存表150条记录，索引建立不好一个查询需要4分钟，对索引优化以后1秒不到。如果数据单纯作为查询可以取消对该表的日志功能。 我一般是分成两个库，一个处理事务，一个处理查询，然后建立一个定期事务把事务数据增加到查询库中。 总的来说，只有才所有软的手段不能解决问题的情况下才采用物理的方法。但是物理的方法也不是单纯增加应