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

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

  分页浏览功能是常见的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回复
  1. 11
    音乐天堂   说道:
    SQL Server碰到没有设立主键的情况下就无法使用这种SQL语句进行分页了。
    如果有主键但是是复合主键也无法使用使用这样的SQL,那么这种情况下首先想到的是使用 composit-id-columns IN (composit-id-columns <= min(composit-id-columns)) 这种方式进行分页查询。之后就会碰到有一个新的问题,SQL Server 2000并不支持“多元数据 IN(多元数据列表)”这种查询方式。
    所以SQL Server使用这种方式进行分页还是会有不少问题的。
    支持(11反对(6回复
  1. 12
    Ivan   说道:
    适用范围有限,例如我有一个产品表,不按ID排序,要按产品名称排序,这种算法无法使用。
    支持(13反对(8回复
  1. 13
    黑白之间   说道:
    分页算法中,影响查询速度的关键因素在于返回数据集的大小,
    支持(12反对(9回复
  1. 14
    歪歪屋啊   说道:
    数据库??一个让人头痛的问题
    支持(15反对(12回复
  1. 15
    Wosoly   说道:
    这个方法在不带查询条件的情况下可以使用,如果要查询提的表有限制条件,该如何优化?
    williamlong 于 2009-5-15 10:46:00 回复
    上面的示例代码中就有查询条件的,有查询的字段,最好将字段设置索引。
    支持(11反对(8回复
  1. 16
    tieqi   说道:
    select top 页大小 *
    from table1
    where id<=
    (select min (id) from
    (select top ((页码-1)*页大小+1) id from table1 order by id desc) as T
    )
    order by id desc


    该算法第一页无法显示,需要垓下这里 ((页码-1)*页大小+1)
    支持(12反对(9回复
  1. 17
    哈哈   说道:
    我是新手有些还是看不懂
    支持(10反对(7回复
  1. 18
    三脚猫   说道:
    貌似以前发表过的嘛,好像在blog.williamlong.info
    yoja 于 2009-5-14 21:26:45 回复
    是么?那就。。。。
    支持(11反对(9回复
  1. 19
    LoveecilA   说道:
    ls的ls讲的不免有点刻薄,我是曾经把月光的博客从头到尾翻了很多遍的,月光的博客内容和形式从最初的长篇转载开始,到后来逐步形成自己的思想,从具体知识内容再到现在的点评,每一次转变的过程中都会有摇摆的过程,不过你说的有一点很重要,在写博客的时候,很容易向搜索引擎或者广告投放妥协,月光加油
    williamlong 于 2009-5-14 22:40:03 回复
    那SB为了炒作自己的博客而已,理他做什么呀。
    支持(15反对(14回复
  1. 20
    园子   说道:
    正在学习 mysql

    非常实用的技巧

    谢谢您~
    支持(10反对(9回复
  1. 21
    BruceYo   说道:
    经常光临你的博客,向你学习。
    支持(12反对(11回复
  1. 22
    Hunter   说道:
    调试半天,怎么得到的结果集不正确呢,当页码=1的时候无返回结果,其他值时结果好像不对。
    支持(12反对(11回复
  1. 23
    fans   说道:
    以前在大学的时候学过,
    现在好久了没有接触都忘记了,
    只记得一些基础的。嘿嘿
    支持(8反对(7回复
  1. 24
    五笔字根查询   说道:
    很详细的教程!
    支持(12反对(11回复
  1. 25
    KWL   说道:
    mysql不支持 学习
    支持(12反对(13回复
  1. 26
    淡月清风   说道:
    月光做JSP呢,以前没注意,呵呵
    支持(9反对(10回复
  1. 27
    不懂   说道:
    技术文章看不懂。凑凑热闹
    brucelei 于 2009-5-15 17:33:24 回复
    凑热闹的跟帖
    支持(10反对(11回复
  1. 28
    天使羽翼   说道:
    对于新手来说可能会看不太懂 写的确实不错待会我也试试 SEO真的很深奥 确实有很多东西还需要大家一起摸索 有朝一日中国的SEO界也能在世界上排上名 这是我们的愿望 加油月光 支持你
    支持(8反对(9回复
  1. 29
    JJFOR   说道:
    学习学习了,,不错!!
    支持(8反对(9回复
  1. 30
    xx   说道:
    select top 页大小 * from table1 where id not in(select top ((页码-1)*页大小) id from table1 order by id desc) order by id desc
    支持(9反对(10回复
  1. 31
    常笑鹰   说道:
    投票功能有BUG,请修复。
    我不知道怎么修,刚发现的。
    本人QQ:928960522
    看看原来几星的评价,现在变很低了。
    一直按第一颗星可以降总评
    支持(8反对(9回复
  1. 32
    laila   说道:
    mysql怎么分页,能说说吗?
    支持(7反对(10回复
  1. 33
    希望高手帮忙   说道:
    我要做一个网站,需要用到数据库,可我还没有学,希望哪位高手能够帮忙!
    我想实现这样的效果,像现在我在发表评论,然后点提交,我所输入的内容就能在上面显示!
    我用Dreanmweaver做网页!
    希望高手给出相应的代码,还有怎样使用,越详细越好!
    支持(10反对(13回复
  1. 34
    宛川骄子   说道:
    这是让我痴迷的技术.到处都是数据库,就看你能不能好好的让他们工作.关键是技术.
    支持(9反对(14回复
  1. 35
    wordpress啦   说道:
    分頁功能,對于博客來說是個很重要的操作
    支持(7反对(12回复
  1. 36
    sunxy89   说道:
    无意中的一次光临让我每天都要来看看
    你的博客真的很好。
    向你学习。
    支持(6反对(12回复
  1. 37
    夜间工作者   说道:
    技术文章看不懂。凑凑热闹。〔好似趁虚甘〕
    支持(3反对(9回复
  1. 38
    okokok   说道:
    数据库来说可以很轻松的使用
    支持(10反对(17回复

发表留言