PERCONA PERFORMANCE CONFERENCE 2009上,来自雅虎的几位工程师带来了一篇”Efficient Pagination Using MySQL“的报告,有很多亮点,本文是在原文基础上的进一步延伸。
首先看一下分页的基本原理:
mysql> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20\G
***************** 1. row **************
id: 1
select_type: SIMPLE
table: message
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 10020
Extra:
1 row in set (0.00 sec)
limit 10000,20的意思扫描满足条件的10020行,扔掉前面的10000行,返回最后的20行,问题就在这里,如果是limit 100000,100,需要扫描100100行,在一个高并发的应用里,每次查询需要扫描超过10W行,性能肯定大打折扣。文中还提到limit n性能是没问题的,因为只扫描n行。
文中提到一种”clue”的做法,给翻页提供一些”线索”,比如还是SELECT * FROM message ORDER BY id DESC,按id降序分页,每页20条,当前是第10页,当前页条目id最大的是9527,最小的是9500,如果我们只提供”上一页”、”下一页”这样的跳转(不提供到第N页的跳转),那么在处理”上一页”的时候SQL语句可以是:
SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;
处理”下一页”的时候SQL语句可以是:
SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;
不管翻多少页,每次查询只扫描20行。
缺点是只能提供”上一页”、”下一页”的链接形式,但是我们的产品经理非常喜欢”<上一页 1 2 3 4 5 6 7 8 9 下一页>”这样的链接方式,怎么办呢?
如果LIMIT m,n不可避免的话,要优化效率,只有尽可能的让m小一下,我们扩展前面的”clue”做法,还是SELECT * FROM message ORDER BY id DESC,按id降序分页,每页20条,当前是第10页,当前页条目id最大的是9527,最小的是9500,比如要跳到第8页,我看的SQL语句可以这样写:
SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20,20;
跳转到第13页:
SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 30,20;
原理还是一样,记录住当前页id的最大值和最小值,计算跳转页面和当前页相对偏移,由于页面相近,这个偏移量不会很大,这样的话m值相对较小,大大减少扫描的行数。其实传统的limit m,n,相对的偏移一直是第一页,这样的话越翻到后面,效率越差,而上面给出的方法就没有这样的问题。
注意SQL语句里面的ASC和DESC,如果是ASC取出来的结果,显示的时候记得倒置一下。
已在60W数据总量的表中测试,效果非常明显。
From:
http://www.fuchaoqun.com/2009/04/efficient-pagination-using-mysql/
分享到:
相关推荐
总结,MySQL的分页功能是处理大数据量查询的关键技术,通过合理使用`LIMIT`和`OFFSET`,配合索引优化,可以有效地提高查询效率,提升用户交互体验。同时,随着版本升级,MySQL提供了更多分页策略和技术,如窗口函数...
总结来说,实现高效的MySQL分页查询需要考虑索引优化、使用新的SQL特性,以及借助合适的开发工具。同时,持续关注数据库性能监控,及时调整和优化查询策略,对于保持系统稳定和提升用户体验具有重要意义。
在这个"mysql分页实例"中,我们将深入探讨MySQL的分页查询技术,并提供可以直接引用的分页类。 首先,我们要理解SQL中的基础分页查询语句。在MySQL中,最常用的分页查询方法是结合`LIMIT`和`OFFSET`关键字。`LIMIT`...
在分页查询时,MySQL的LIMIT子句特别有用,它可以高效地从大量数据中只取出需要的部分,从而显著提升性能。 下面是一个简单的分页流程概述: 1. 用户在网页上点击页码或使用导航按钮。 2. JSP接收到请求,解析出...
为了进一步提升分页效率,可以考虑以下策略: - **索引优化**:确保用于排序和分页的列上有合适的索引,以加快查询速度。 - **避免全表扫描**:如果可能,使用`IN`子查询或`JOIN`代替`OFFSET`,以减少全表扫描。 - ...
然而,随着数据量的增长,直接使用OFFSET进行分页可能会变得效率低下,因为MySQL需要遍历从第一条记录到OFFSET指定的位置,这在大数据量下极其耗时。为了解决这个问题,我们可以利用MySQL的ROW_NUMBER()函数或者自增...
### MySQL分页存储过程概述 分页存储过程是一种在数据库层面上实现数据分页的技术。相比应用程序级别的分页,它能更高效地处理数据,减少网络传输的数据量,提升系统性能。在MySQL中,我们可以通过创建存储过程来...
总结,MySQL分页是数据库查询的重要技巧,理解并掌握其原理和优化方法,能有效提升数据库操作效率,提供更好的服务体验。在实际开发中,应根据具体场景选择合适的分页策略,确保数据获取既高效又灵活。
在这些数据库中,当查询结果集过大时,我们不会一次性将所有数据加载到页面上,而是通过分页技术分批加载,减轻服务器压力,提升用户浏览效率。 ASP是一种服务器端脚本语言,用于创建动态交互式网页。在ASP中,我们...
MySQL是一种广泛使用的开源关系型数据库管理系统...ibator可以极大地提高开发效率,同时,理解并掌握动态SQL和分页查询原理对于提升数据库操作的性能至关重要。在实际应用中,还需要结合业务需求进行适当的调整和优化。
本实例将详细介绍如何在MyBatis中实现MySQL的分页查询,帮助开发者提高应用性能,提升用户浏览数据的体验。 首先,我们要理解分页的基本概念。分页是将大量数据分成多个小部分,每次只加载一部分到内存中,这样可以...
在互联网开发过程中,分页功能是一项常见的需求,特别是在数据库数据量较大时,为了提升查询效率和用户体验,开发者往往会将数据分批次显示。PHP和MySQL的结合,为实现WEB分页功能提供了简单而高效的方法。接下来,...
在本DEMO中,"Mysql+分页-DEMO.rar" 提供了一个使用Delphi7与MySQL结合实现分页查询的示例,这对于构建高效、用户友好的数据浏览界面非常有用。 首先,我们要理解分页的基本概念。分页是一种数据展示策略,它将大量...
本文将深入探讨MySQL分页查询的原理、方法以及在Java开发中的应用。 ### 分页查询的基本概念 分页查询是指从数据库中获取特定范围的数据,通常是以页为单位进行加载。这种方式可以有效地减少网络传输的数据量,...
MySQL分页原理与高效查询是数据库管理中的重要概念,尤其对于大数据量的Web应用,合理的分页策略可以显著提升用户体验并降低服务器负载。本篇文章将深入探讨这两种分页方法。 首先,基本的分页原理是利用`LIMIT`...
【标题】"jsp+mySql 真假分页"涉及了Web开发中的两种常见分页技术:真分页和假分页。这两种方法在处理大量数据时特别有用,能够提高网页...同时,考虑到性能优化,还可以结合缓存技术,如Redis,来进一步提升分页效率。
总的来说,Smart MySQL分页是一种优化大量数据查询的策略,它通过避免使用OFFSET关键字来提高查询效率。配合合理的前端分页展示和后端处理逻辑,可以为用户提供流畅的浏览体验,同时减轻服务器压力。在实际开发中,...
在MySQL数据库管理中,分页查询是不可或缺的功能,它允许我们从海量数据中按需获取一部分结果,提高用户体验,减少服务器负担。...在设计数据库时,合理设置索引也能对分页查询带来显著的性能提升。
MySQL分页的原理和效率提升方法涉及到的几个关键点如下: 1. 分页查询的原理:在MySQL中,分页查询通常使用LIMIT和OFFSET关键字来实现。基本格式为LIMIT offset, count。其中offset指的是从哪一行开始返回数据,...