月光博客 » 编程开发 » 数据库查询的分页优化技巧

数据库查询的分页优化技巧

  分页浏览功能是常见的Web应用功能,对于MySQL数据库来说可以很轻松的使用limit语句实现分页,而对于SQL Server数据库来说,常见的方法是使用数据集本身的游标实现分页,这种方法对于少量数据来说没什么问题,但是对于稍大一点的数据量,例如几十万条数据,则查询速度会降低很多,这里我介绍一种常用的技巧,只要简单的重新构造一下查询SQL语句,就能大幅提高查询性能的方法。

  在分页算法中,影响查询速度的关键因素在于返回数据集的大小,我们先在数据表中设置一个名为id的主键,数值为自增量的整数,然后通过重构查询SQL语句,就可以实现SQL查询的优化,重构的SQL如下所示

 

select top 页大小 *
from table1
where id<=
      (select min (id) from
      (select top ((页码-1)*页大小) id from table1 order by id desc) as T
       )   
order by id desc

 

  下面的JSP演示代码中,intPageSize为页大小,intPage为页码,id为主键,演示了操作一个t_Product表,并加入各类查询条件之后的重构SQL的主要语句,经过实际调试,经过这样简单优化后的SQL查询速度远远高于优化前的查询速度。

 

String sql=" from t_Product where 1=1 and ";
String ProductName = request.getParameter("ProductName");
if (ProductName!=null) sql=sql+"ProductName like '%" + ProductName + "%' and " ;
sql=sql.substring(0,sql.length()-4);  // 去掉尾部的 and 字符串
sql="select top " + String.valueOf(intPageSize) + " *" +sql+" and id <=(select min(id) from (select top " +  String.valueOf(intPage*intPageSize) + " id " + sql + " order by id desc) as T) "; //通过子查询加快速度
sql=sql+" order by id desc ";
 

 

数据库查询的分页优化技巧

顶一下 ▲()   踩一下 ▼()

相关文章

  1. 1
    sunxy89   说道:
    学习了。有很多东西值得学习。
    祝你工作顺利!
    wordba.cn 于 2009-5-14 20:30:12 回复
    好!!!!!!
    支持(21反对(10回复
  1. 2
    hipailiu   说道:
    请教下:
    腾讯的qq校友查询所有校友的时候只能看到第一千页,再点下一页就仍然显示当前内容;而如果在地址栏手动输入页数就可以显示下一页,能告诉一下是为什么吗?
    支持(17反对(6回复
  1. 3
    hardy   说道:
    作者只是说十万级数据上,但是数据量再大的话,给一个千万级的表做一个自增的ID字段,这是件很不现实的事情。我觉得使用select TOP N id from TB where Not in (select TOP N*(Pages-1) id from TB )已经足够了。大家可以观察一下GOOGLE的翻页算法。查询结果上百万,但是无论你怎么向后翻,也只能翻到50-80页左右。再往后就不显示了,因为没意义。没错。这么大的数据量根本就没必要看这么仔细。给用户开放翻到最后一页的功能就是多此一举的事情。
    hardy 于 2009-5-18 23:32:01 回复
    在现实查询结果前,要充分细化维度,将结果集数量级降到可接受的范围。
    支持(17反对(6回复
  1. 4
    百慧博客   说道:
    再来看一下月光,很多东西看不懂。呵呵。
    支持(18反对(10回复
  1. 5
    天使羽翼   说道:
    写的确实不错很实用 但是用的比较少 支持!
    支持(18反对(10回复
  1. 6
    freeram   说道:
    没事抢沙发做什么
    支持(17反对(11回复
  1. 7
    何忧   说道:
    只是针对sql server的?mysql不支持top吧……
    williamlong 于 2009-5-14 22:35:00 回复
    这方法可用于SQL Server和Access数据库,mysql不支持。
    49Degree 于 2009-5-15 14:02:35 回复
    mysql 直接有limit n,m
    就不需要这个了
    支持(13反对(7回复
  1. 8
    钢丸   说道:
    用得比较少~~
    支持(14反对(8回复
  1. 9
    Showfom   说道:
    WordPress也需要这样优化一下
    支持(16反对(10回复
  1. 10
    对啊博客   说道:
    很晦涩。受众不广。
    xxxxxx 于 2009-5-17 0:54:06 回复
    <PluginList>
    <Plugin enable='true' name='XLUser' path='XLUser.dll'/>
    <Plugin enable='true' name='XLBlog' path='XLBlog.dll'/>
    </PluginList>
    支持(14反对(9回复

发表留言