百万数据分页显示效率的有关问题

百万数据分页显示效率的问题

Java code

String sql="select * from users where userID between "+num1+" and "+num2;

userID为主键是以1,2,3,4,5...递增的形式增加 
优点：效率高的惊人，以100条数据为例，显示最后一页的数据时，只花了15毫秒的时间 
缺点：当userID中的某段被删除了，如userID＝11到20这段数据没了，那第2页就没有数据显示了， 
而且如果中间有被删除了，那本来一页显示10条的，就会变成显示9条，8条或更少 

Java code

String sql="select * from (" +"select top "+num1+" * from "+"(select top "+num2+" * from users order by userID) a "+"order by a.userID desc" +") b order by b.userID";

使用top来取 
优点：格式统一，每页显示数一致，最后一页也是一样 
缺点：效率一般，随着数据量的增加而降低，比如100W的数据量，第一页的时候，只要30毫秒左右，最后一页要2375毫秒左右， 
而且如果数据总量不是每页显示记录数的整数倍的话，最后一页与最后第二页会有相同的记录出现 

Java code

String sql="select * from " +"( " +"select t.* , (select count(userID) from users where userID < t.userID) + 1 px from users t" +") m " +"where px between "+num1+" and "+num2;

此方法是一网上朋友告诉我的，我在测试的时候，在数据量30W的时候，我用的sql server 2005数据库就卡住了，无法显示 
不知道是不是我哪里用错了 

Java code

String sql="select * from ("+"select *,px=(select count(1)+1 from users where userID<t.userID) from users t "+") tt "+"where px between "+num1+" and "+num2;

此方法与上面的方法差不多，效果也差不多，在数据量30W的时候，数据库就卡住了 
也请告知下 

Java code

String sql="select count(userID) as sum from users";

我用了这个方法来取得记录总数，以100W条数据为例，他要取一下要花1984毫秒左右的时间 
这里随便问一下 有没有更优的方法 


以上几种是我用的方法，想请问下，现在都用什么方式达到最优的效率 
请前辈们不吝赐教，先谢谢了

[解决办法]
。。。学习了

String sql="select count(1) as sum from users";

一般我们不进行全表的搜索，会根据索引字段搜索，速度会快些！在对付大数据时，索引才是王道

[解决办法]
配置数据库连接池会好一些
[解决办法]
这是一个调用存储过程的例子 仅供参考

Java code

package dao;import java.sql.CallableStatement;import java.sql.Connection;import util.MyConnection;public class GbDao {    private Connection conn = null;    private CallableStatement cs=null;    public void initConnection() throws Exception {        MyConnection myconn= new MyConnection();  //初始化MyConnection对象        conn = myconn.getConnection();              //调用MyConnection的getConnection()方法得到数据库连接    }    public String gbht(String gwm,String zmlm){        String msg=null;        String sql="{?=call F_HD_GBHT(?,?)}";        System.out.println("sql="+sql);        try{this.initConnection();        CallableStatement cs=conn.prepareCall(sql);        cs.registerOutParameter(1, java.sql.Types.VARCHAR);        cs.setString(2, gwm);        cs.setString(3, zmlm);        cs.execute();        msg=cs.getString(1);        }catch(Exception ex){            ex.printStackTrace();                    }finally{            try{                if(cs!=null){                   cs.close();                    }            }catch(Exception ex){                ex.printStackTrace();            }        }        return msg;    }} 
 
[解决办法]
不用都查出来吧，我建议就加一个top过滤一下，然后增加查询条件，过滤数据，根据条件建立索引优化
[解决办法]
MS SQL的分页使用MS的游标进行分页速度会比较快(hibernate就是使用这种分页)，TOP分页只在前几页的数据速度快点，后面的数据速度就会很慢。
  
CREATE procedure cursorPage  
@sqlstr nvarchar(4000), --查询字符串  
@CurrPage int, --第N页  
@pagesize int, --每页行数  
@RecordCount int output , --记录总数  
@PageCount int output, --页面总数  
@CurrCount int output --当前页面记录条数  
as  
set nocount on  
declare @P1 int --P1是游标的id  
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@RecordCount=@RecordCount output  
--select ceiling(1.0*@RecordCount/@pagesize) as PageCount,@RecordCount as RecordCount,@CurrPage as CurrPag  
set @PageCount = ceiling(1.0*@RecordCount/@pagesize)  
--set @CurrPage=(@CurrPage-1)*@pagesize+1  
if @CurrPage > @PageCount Set @CurrPage = @PageCount  
if @CurrPage - @PageCount >= 0 and (@RecordCount % @PageSize) > 0  
Begin  
Set @CurrCount = @RecordCount % @PageSize  
End  
Else  
Begin  
Set @CurrCount = @PageSize  
End  
exec sp_cursorfetch @P1,16,@CurrPage,@CurrCount  
exec sp_cursorclose @P1  
set nocount off  
GO  

[解决办法]
哪就建议直接使用hibernate的分页，因为Hibernate会针对不同的数据库产生不同的分页代码。

速度也不错
[解决办法]
SQL code
select top 8 * from message where m_id not in(select top 16 m_id from message ) 

 

查看更多 下一篇