青青子衿, 悠悠我心, 但为君故, 沉吟至今
« 博客实现回复评论功能润乾报表的使用技巧 »

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

  分页浏览功能是常见的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 ";
 

 



  除非注明,月光博客文章均为原创,转载请以链接形式标明本文地址

  本文地址:http://www.williamlong.info/archives/1795.html
  • 文章排行:
  • 1.sunxy89
  • 学习了。有很多东西值得学习。
    祝你工作顺利!
    wordba.cn 于 2009-5-14 20:30:12 回复
    好!!!!!!
  • 2009/5/14 17:58:58   支持(21)反对(10) 回复
  • 2.hipailiu
  • 请教下:
    腾讯的qq校友查询所有校友的时候只能看到第一千页,再点下一页就仍然显示当前内容;而如果在地址栏手动输入页数就可以显示下一页,能告诉一下是为什么吗?
  • 2009/5/15 21:37:39   支持(17)反对(6) 回复
  • 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 回复
    在现实查询结果前,要充分细化维度,将结果集数量级降到可接受的范围。
  • 2009/5/18 23:29:43   支持(17)反对(6) 回复
  • 4.百慧博客
  • 再来看一下月光,很多东西看不懂。呵呵。
  • 2009/5/14 22:45:21   支持(18)反对(10) 回复
  • 5.天使羽翼
  • 写的确实不错很实用 但是用的比较少 支持!
  • 2009/5/15 21:08:25   支持(18)反对(10) 回复
  • 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
    就不需要这个了
  • 2009/5/14 19:37:25   支持(13)反对(7) 回复
  • 9.Showfom
  • WordPress也需要这样优化一下
  • 2009/5/15 21:58:10   支持(16)反对(10) 回复
  • 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>
  • 2009/5/14 18:51:17   支持(14)反对(9) 回复
  • 11.音乐天堂
  • SQL Server碰到没有设立主键的情况下就无法使用这种SQL语句进行分页了。
    如果有主键但是是复合主键也无法使用使用这样的SQL,那么这种情况下首先想到的是使用 composit-id-columns IN (composit-id-columns <= min(composit-id-columns)) 这种方式进行分页查询。之后就会碰到有一个新的问题,SQL Server 2000并不支持“多元数据 IN(多元数据列表)”这种查询方式。
    所以SQL Server使用这种方式进行分页还是会有不少问题的。
  • 2009/5/15 12:03:37   支持(11)反对(6) 回复
  • 12.Ivan
  • 适用范围有限,例如我有一个产品表,不按ID排序,要按产品名称排序,这种算法无法使用。
  • 2009/12/20 22:38:16   支持(13)反对(8) 回复
  • 13.黑白之间
  • 分页算法中,影响查询速度的关键因素在于返回数据集的大小,
  • 2009/5/14 19:14:31   支持(12)反对(9) 回复
  • 14.歪歪屋啊
  • 数据库??一个让人头痛的问题
  • 2009/5/14 20:54:12   支持(15)反对(12) 回复
  • 15.Wosoly
  • 这个方法在不带查询条件的情况下可以使用,如果要查询提的表有限制条件,该如何优化?
    williamlong 于 2009-5-15 10:46:00 回复
    上面的示例代码中就有查询条件的,有查询的字段,最好将字段设置索引。
  • 2009/5/15 9:27:55   支持(11)反对(8) 回复
  • 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)
  • 2009/5/19 0:35:15   支持(12)反对(9) 回复
  • 18.三脚猫
  • 貌似以前发表过的嘛,好像在blog.williamlong.info
    yoja 于 2009-5-14 21:26:45 回复
    是么?那就。。。。
  • 2009/5/14 18:15:16   支持(11)反对(9) 回复
  • 19.LoveecilA
  • ls的ls讲的不免有点刻薄,我是曾经把月光的博客从头到尾翻了很多遍的,月光的博客内容和形式从最初的长篇转载开始,到后来逐步形成自己的思想,从具体知识内容再到现在的点评,每一次转变的过程中都会有摇摆的过程,不过你说的有一点很重要,在写博客的时候,很容易向搜索引擎或者广告投放妥协,月光加油
    williamlong 于 2009-5-14 22:40:03 回复
    那SB为了炒作自己的博客而已,理他做什么呀。
  • 2009/5/14 22:02:01   支持(15)反对(14) 回复
  • 20.园子
  • 正在学习 mysql

    非常实用的技巧

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

发表评论:

 请勿发送垃圾信息、广告、推广信息或链接,这样的信息将会被直接删除。

订阅博客

  • 订阅我的博客:订阅我的博客
  • 关注新浪微博:关注新浪微博
  • 关注腾讯微博:关注腾讯微博
  • 关注认证空间:关注QQ空间
  • 通过电子邮件订阅
  • 通过QQ邮件订阅

站内搜索

热文排行


月度排行

本站采用创作共用版权协议, 要求署名、非商业用途和相同方式共享. 转载本站内容必须也遵循“署名-非商业用途-相同方式共享”的创作共用协议.
This site is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License.