本文基于PERCONAPERFORMANCECONFERENCE2009上雅虎工程师的报告，介绍了一种使用SQL的LIMIT优化分页查询效率的方法。通过记录当前页ID的最大值和最小值，计算跳转页面与当前页面的相对偏移，使得每次查询只扫描有限的行数，从而提高查询效率。已实测在60万数据总量的表中效果明显。
一、前言<wbr style="line-height:25px"><span style="font-size:14px"> </span> <div style="line-height:25px"><span style="font-size:14px">如果你对sql的limit使用还不熟悉，请先阅读《<span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; line-height:24px; background-color:rgb(245,245,245); white-space:nowrap"><strong><a title="阅读全文" href="http://hubingforever.blog.163.com/blog/static/171040579201238103742788/" target="_blank" style="line-height:25px; color:rgb(245,149,19); text-decoration:none">sql的limit</a></strong></span>》</span></div> <div style="line-height:25px"><span style="line-height:25px"><span style="line-height:28px; font-size:16px">二、使用limit来翻页</span></span></div> <div style="line-height:25px"> <div style="line-height:25px"><span style="font-size:14px">PERCONA PERFORMANCE CONFERENCE 2009上，来自雅虎的几位工程师带来了一篇”Efficient Pagination Using MySQL“的报告，有很多亮点，本文是在原文基础上的进一步延伸。</span></div> <div style="line-height:25px"><span style="font-size:14px">文中提到一种”<span style="color:#800000; line-height:25px">clue</span>”的做法，给翻页提供一些”线索”，比如还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，如果我们只提供”上一页”、”下一页”这样 的跳转（不提供到第N页的跳转），那么在处理”上一页”的时候SQL语句可以是：</span></div> <div style="line-height:25px"><span style="font-size:14px; color:#0000ff; line-height:25px">SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;</span></div> <div style="line-height:25px"><span style="font-size:14px">处理”下一页”的时候SQL语句可以是：</span></div> <div style="line-height:25px"><span style="font-size:14px; color:#0000ff; line-height:25px">SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;</span></div> <div style="line-height:25px"><span style="font-size:14px">不管翻多少页，每次查询只扫描20行。</span></div> <div style="line-height:25px"><span style="font-size:14px">缺点是只能提供”上一页”、”下一页”的链接形式，但是我们通常”<上一页 1 2 3 4 5 6 7 8 9 下一页>”这样的链接方式，怎么办呢？</span></div> <div style="line-height:25px"><span style="font-size:14px">如果LIMIT m,n不可避免的话，要优化效率，只有尽可能的让m小一下，我们扩展前面的”clue”做法，还是<span style="color:#0000ff; line-height:25px">SELECT * FROM message ORDER BY id DESC</span>，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，比如要跳到第8页，我看的SQL语句可以这 样写：</span></div> <div style="line-height:25px"><span style="font-size:14px; color:#0000ff; line-height:25px">SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20,20;</span></div> <div style="line-height:25px"><span style="font-size:14px">跳转到第13页：</span></div> <div style="line-height:25px"><span style="font-size:14px; color:#0000ff; line-height:25px">SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 40,20;</span></div> <div style="line-height:25px"><span style="font-size:14px">原理还是一样，记录住当前页id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话m值相对较小，大大 减少扫描的行数。其实传统的limit m,n，相对的偏移一直是第一页，这样的话越翻到后面，效率越差，而上面给出的方法就没有这样的问题。</span></div> <div style="line-height:25px"><span style="font-size:14px; color:#000080; line-height:25px">注意SQL语句里面的ASC和DESC，如果是ASC取出来的结果，显示的时候记得倒置一下。</span></div> <div style="line-height:25px"><span style="font-size:14px">已在60W数据总量的表中测试，效果非常明显。</span></div> </div> </wbr>
一、前言<wbr style="line-height:25px"><span style="font-size:14px"> </span> <div style="line-height:25px"><span style="font-size:14px">如果你对sql的limit使用还不熟悉，请先阅读《<span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; line-height:24px; background-color:rgb(245,245,245); white-space:nowrap"><strong><a title="阅读全文" href="http://hubingforever.blog.163.com/blog/static/171040579201238103742788/" target="_blank" style="line-height:25px; color:rgb(245,149,19); text-decoration:none">sql的limit</a></strong></span>》</span></div> <div style="line-height:25px"><span style="line-height:25px"><span style="line-height:28px; font-size:16px">二、使用limit来翻页</span></span></div> <div style="line-height:25px"> <div style="line-height:25px"><span style="font-size:14px">PERCONA PERFORMANCE CONFERENCE 2009上，来自雅虎的几位工程师带来了一篇”Efficient Pagination Using MySQL“的报告，有很多亮点，本文是在原文基础上的进一步延伸。</span></div> <div style="line-height:25px"><span style="font-size:14px">文中提到一种”<span style="color:#800000; line-height:25px">clue</span>”的做法，给翻页提供一些”线索”，比如还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，如果我们只提供”上一页”、”下一页”这样 的跳转（不提供到第N页的跳转），那么在处理”上一页”的时候SQL语句可以是：</span></div> <div style="line-height:25px"><span style="font-size:14px; color:#0000ff; line-height:25px">SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;</span></div> <div style="line-height:25px"><span style="font-size:14px">处理”下一页”的时候SQL语句可以是：</span></div> <div style="line-height:25px"><span style="font-size:14px; color:#0000ff; line-height:25px">SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;</span></div> <div style="line-height:25px"><span style="font-size:14px">不管翻多少页，每次查询只扫描20行。</span></div> <div style="line-height:25px"><span style="font-size:14px">缺点是只能提供”上一页”、”下一页”的链接形式，但是我们通常”<上一页 1 2 3 4 5 6 7 8 9 下一页>”这样的链接方式，怎么办呢？</span></div> <div style="line-height:25px"><span style="font-size:14px">如果LIMIT m,n不可避免的话，要优化效率，只有尽可能的让m小一下，我们扩展前面的”clue”做法，还是<span style="color:#0000ff; line-height:25px">SELECT * FROM message ORDER BY id DESC</span>，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，比如要跳到第8页，我看的SQL语句可以这 样写：</span></div> <div style="line-height:25px"><span style="font-size:14px; color:#0000ff; line-height:25px">SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20,20;</span></div> <div style="line-height:25px"><span style="font-size:14px">跳转到第13页：</span></div> <div style="line-height:25px"><span style="font-size:14px; color:#0000ff; line-height:25px">SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 40,20;</span></div> <div style="line-height:25px"><span style="font-size:14px">原理还是一样，记录住当前页id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话m值相对较小，大大 减少扫描的行数。其实传统的limit m,n，相对的偏移一直是第一页，这样的话越翻到后面，效率越差，而上面给出的方法就没有这样的问题。</span></div> <div style="line-height:25px"><span style="font-size:14px; color:#000080; line-height:25px">注意SQL语句里面的ASC和DESC，如果是ASC取出来的结果，显示的时候记得倒置一下。</span></div> <div style="line-height:25px"><span style="font-size:14px">已在60W数据总量的表中测试，效果非常明显。</span></div> </div> </wbr>
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
