select * from table limit m,n
其中m是指记录开始的index,表示每次开始的索引。默认从0开始,表示第一条记录
n是指从第m+1条开始,取n条。
select * from tablename limit 2,4
即取出第3条至第6条,4条记录
---------------------------------------------------------------------------------------------------------
在mysql中limit可以实现快速分页,但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了,否则可能卡死你的服务器哦。
如 * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢。
limit10000,20的意思扫描满足条件的10020行,扔掉前面的10000行,返回最后的20行,问题就在这里。
LIMIT 451350,30 扫描了45万多行,怪不得慢的都堵死了。
但是limit 30 这样的语句仅仅扫描30行。
那么如果我们之前记录了最大ID,就可以在这里做文章
举个例子
日常分页SQL语句
select id,name,content from users order by id asc limit 100000,20
扫描100020行
如果记录了上次的最大ID
select id,name,content from users where id>100073 order by id asc limit 20
扫描20行。
总数据有500万左右,以下例子
select * from wl_tagindex where byname='f' order by id limit 300000,10 执行时间是 3.21s
优化后:
select * from (
select id from wl_tagindex
where byname='f' order by id limit 300000,10
) a
left join wl_tagindex b on a.id=b.id
执行时间为 0.11s 速度明显提升
这里需要说明的是 我这里用到的字段是 byname ,id 需要把这两个字段做复合索引,否则的话效果提升不明显
总结
当一个数据库表过于庞大,LIMIT offset, length中的offset值过大,则SQL查询语句会非常缓慢,你需增加order by,并且order by字段需要建立索引。
如果使用子查询去优化LIMIT的话,则子查询必须是连续的,某种意义来讲,子查询不应该有where条件,where会过滤数据,使数据失去连续性。
如果你查询的记录比较大,并且数据传输量比较大,比如包含了text类型的field,则可以通过建立子查询。
SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);
如果limit语句的offset较大,你可以通过传递pk键值来减小offset = 0,这个主键最好是int类型并且auto_increment
SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;
这条语句,大意如下:
SELECT * FROM users WHERE uid >= (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10;
如果limit的offset值过大,用户也会翻页疲劳,你可以设置一个offset最大的,超过了可以另行处理,一般连续翻页过大,用户体验很差,则应该提供更优的用户体验给用户。
------------------------------------------------------------------------------------------------------
数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引。这是一个基本的新闻系统的简单模型。现在往里面填充数据,填充10万篇新闻。
最后collect 为 10万条记录,数据库表占用硬盘1.6G。OK ,看下面这条sql语句:
select id,title from collect limit 1000,10; 很快;基本上0.01秒就OK,再看下面的
select id,title from collect limit 90000,10; 从9万条开始分页,结果?
8-9秒完成,my god 哪出问题了????其实要优化这条数据,网上找得到答案。看下面一条语句:
select id from collect order by id limit 90000,10; 很快,0.04秒就OK。 为什么?因为用了id主键做索引当然快。网上的改法是:
select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;
这就是用了id做索引的结果。可是问题复杂那么一点点,就完了。看下面的语句
select id from collect where vtype=1 order by id limit 90000,10;
很慢,用了8-9秒!
到了这里我相信很多人会和我一样,有崩溃感觉!vtype 做了索引了啊?怎么会慢呢?vtype做了索引是不错,你直接 select id from collect where vtype=1 limit 1000,10; 是很快的,基本上0.05秒,可是提高90倍,从9万开始,那就是0.05*90=4.5秒的速度了。和测试结果8-9秒到了一个数量级。从这里开始有人 提出了分表的思路,这个和discuz 论坛是一样的思路。思路如下:
建一个索引表: t (id,title,vtype) 并设置成定长,然后做分页,分页出结果再到 collect 里面去找info 。 是否可行呢?实验下就知道了。
10万条记录到 t(id,title,vtype) 里,数据表大小20M左右。用
select id from t where vtype=1 order by id limit 90000,10; 很快了。基本上0.1-0.2秒可以跑完。为什么会这样呢?我猜想是因为collect 数据太多,所以分页要跑很长的路。limit 完全和数据表的大小有关的。其实这样做还是全表扫描,只是因为数据量小,只有10万才快。OK, 来个疯狂的实验,加到100万条,测试性能。
加了10倍的数据,马上t表就到了200多M,而且是定长。还是刚才的查询语句,时间是0.1-0.2秒完成!分表性能没问题?错!因为我们的limit还是9万,所以快。给个大的,90万开始
select id from t where vtype=1 order by id limit 900000,10; 看看结果,时间是1-2秒!
why ?? 分表了时间还是这么长,非常之郁闷!有人说定长会提高limit的性能,开始我也以为,因为一条记录的长度是固定的,mysql 应该可以算出90万的位置才对啊? 可是我们高估了mysql 的智能,他不是商务数据库,事实证明定长和非定长对limit影响不大? 怪不得有人说 discuz到了100万条记录就会很慢,我相信这是真的,这个和数据库设计有关!
好了,我们的测试又回到 collect表,开始测试结论是: 30万数据,用分表法可行,超过30万他的速度会慢道你无法忍受!当然如果用分表+我这种方法,那是绝对完美的。但是用了我这种方法后,不用分表也可以完美解决!
答案就是:复合索引! 有一次设计mysql索引的时候,无意中发现索引名字可以任取,可以选择几个字段进来,这有什么用呢?开始的select id from collect order by id limit 90000,10; 这么快就是因为走了索引,可是如果加了where 就不走索引了。抱着试试看的想法加了 search(vtype,id) 这样的索引。然后测试
select id from collect where vtype=1 limit 90000,10; 非常快!0.04秒完成!
再测试: select id ,title from collect where vtype=1 limit 90000,10; 非常遗憾,8-9秒,没走search索引!
再测试:search(id,vtype),还是select id 这个语句,也非常遗憾,0.5秒。
综上:如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键!
----------------------------------------------------------------------------------------------------
limit 分页优化方法
1.子查询优化法
先找出第一条数据,然后大于等于这条数据的id就是要获取的数据
缺点:数据必须是连续的,可以说不能有where条件,where条件会筛选数据,导致数据失去连续性
这种:
select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100
实验下
mysql> set profi=1; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from Member; +———-+ | count(*) | +———-+ | 169566 | +———-+ 1 row in set (0.00 sec) mysql> pager grep !~- PAGER set to ‘grep !~-‘ mysql> select * from Member limit 10, 100; 100 rows in set (0.00 sec) mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100; 100 rows in set (0.00 sec) mysql> select * from Member limit 1000, 100; 100 rows in set (0.01 sec) mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100; 100 rows in set (0.00 sec) mysql> select * from Member limit 100000, 100; 100 rows in set (0.10 sec) mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100; 100 rows in set (0.02 sec) mysql> nopager PAGER set to stdout mysql> show profilesG *************************** 1. row *************************** Query_ID: 1 Duration: 0.00003300 Query: select count(*) from Member *************************** 2. row *************************** Query_ID: 2 Duration: 0.00167000 Query: select * from Member limit 10, 100 *************************** 3. row *************************** Query_ID: 3 Duration: 0.00112400 Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100 *************************** 4. row *************************** Query_ID: 4 Duration: 0.00263200 Query: select * from Member limit 1000, 100 *************************** 5. row *************************** Query_ID: 5 Duration: 0.00134000 Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100 *************************** 6. row *************************** Query_ID: 6 Duration: 0.09956700 Query: select * from Member limit 100000, 100 *************************** 7. row *************************** Query_ID: 7 Duration: 0.02447700 Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100
从结果中可以得知,当偏移1000以上使用子查询法可以有效的提高性能。
2、使用 id 限定优化
这种方式假设数据表的id是连续递增的,则我们根据查询的页数和查询的记录数可以算出查询的id的范围,可以使用 id between and 来查询:
select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;
查询时间:15ms 12ms 9ms
这种查询方式能够极大地优化查询速度,基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况,不过一般建立表的时候,都会添加基本的id字段,这为分页查询带来很多遍历。
还可以有另外一种写法:
select * from orders_history where id >= 1000001 limit 100;
当然还可以使用 in 的方式来进行查询,这种方式经常用在多表关联的时候进行查询,使用其他表查询的id集合,来进行查询:
select * from orders_history where id in (select order_id from trade_2 where goods = 'pen') limit 100;
这种 in 查询的方式要注意:某些 mysql 版本不支持在 in 子句中使用 limit。
2.倒排表优化法
倒排表法类似建立索引,用一张表来维护页数,然后通过高效的连接得到数据
缺点:只适合数据数固定的情况,数据不能删除,维护页表困难
3.反向查找优化法
当偏移超过一半记录数的时候,先用排序,这样偏移就反转了
缺点:order by优化比较麻烦,要增加索引,索引影响数据的修改效率,并且要知道总记录数
,偏移大于数据的一半
引用
limit偏移算法:
正向查找: (当前页 – 1) * 页长度
反向查找: 总记录 – 当前页 * 页长度
做下实验,看看性能如何
总记录数:1,628,775
每页记录数: 40
总页数:1,628,775 / 40 = 40720
中间页数:40720 / 2 = 20360
第21000页
正向查找SQL:
Sql代码
SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40
时间:1.8696 秒
反向查找sql:
Sql代码
SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40
时间:1.8336 秒
第30000页
正向查找SQL:
Sql代码
1.SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40
SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40
时间:2.6493 秒
反向查找sql:
Sql代码
1.SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40
SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40
时间:1.0035 秒
注意,反向查找的结果是是降序desc的,并且InputDate是记录的插入时间,也可以用主键联合索引,但是不方便。
4.limit限制优化法
把limit偏移量限制低于某个数。。超过这个数等于没数据,我记得alibaba的dba说过他们是这样做的
5.只查索引法
相关推荐
但缺点也很明显,当数据量大时,一次性加载所有数据可能导致网络延迟,服务器负担加重,且用户端也需要消耗更多的内存和计算资源。 相比之下,LIMIT分页是MySQL数据库内置的分页机制,它在服务器端直接处理分页,只...
前言 日常开发中,我们使用mysql来实现分页功能的时候,总是会用到mysql的limit语法.而怎么使用却很有讲究的,今天来总结一下. limit语法 ...实际使用中我们会发现,在分页的后面一些页,加载会变慢,也
3..limit 1000000(一百万) 加载很慢的话,你是怎么解决的呢? 4.介绍下 MySQL 的主从复制原理?产生主从延迟的原因? 5.使用索引查询一定能提高查询的性能吗?为什么? 6.什么是最左前缀原则?什么是最左匹配原则? 7...
如果没有索引,ORDER BY操作会很慢,进一步影响LIMIT的性能。创建适当的复合索引可以加速排序过程。 3. **减少返回列**:只选择必要的列,避免使用`SELECT *`。这不仅可以减少网络传输的数据量,还可以降低存储引擎...
* limit 20000 加载很慢的解决方案 * 选择合适的分布式主键方案 * 选择合适的数据存储方案 * ObjectId 规则 * MongoDB 使用场景 * 倒排索引 * ElasticSearch 使用场景 * 缓存使用:Redis 的类型、内部结构、持久化...
解决limit 1000000 加载很慢问题可以通过以下步骤:(1)使用索引;(2)使用LIMIT OFFSET语句;(3)使用游标;(4)使用分页查询;(5)使用数据缓存。 11.9. 如何选择合适的分布式主键方案呢? 选择合适的分布式主键方案需要...
这款组件不仅支持常见的异步分页,即数据动态加载,还能处理传统意义上的整页刷新跳页,同时它还具备信息流加载的能力,能够很好地适应大数据量、连续滚动加载的场景。此外,layPage的一大亮点是它的跨平台性,能够...
#### LIMIT 20000 加载很慢的解决方案 - **优化方法**:当使用LIMIT 20000这样的大数值进行分页查询时,如果速度较慢,可以通过以下方式进行优化: - 使用索引覆盖查询,确保LIMIT前面的WHERE条件与索引字段相匹配...
在很多脚本语言中,您不必担心内存是如何管理的,这并不能使得内存管理的重要性有一点点降低。对实际编程来说,理解您的内存管理器的能力与局限性至关重要。在大部分系统语言中,比如 C 和 C++,您必须进行内存管理...
在许多新闻应用中,由于信息量庞大,一次性加载所有内容可能导致页面加载速度变慢,甚至消耗大量用户流量。因此,通过“加载更多”按钮,用户可以按需加载新内容,这不仅优化了性能,也减少了用户的等待时间。这种...
- 记录总数优化:对于大数据集,计算总页数可能会很慢,可以考虑动态计算或者预估。 - 懒加载:初始加载少量数据,当用户滚动到页面底部时再加载更多内容。 - 缓存:对于频繁访问的分页数据,可以考虑使用缓存...
在PHP编程中,分页是处理大量数据时不可或缺的一个功能,尤其在网页显示数据库查询结果时,为了提高用户体验,避免一次性加载过多数据导致页面加载慢或浏览器崩溃。本压缩包提供的"php分页程序代码,以及用例",旨在...
2. **预计算总数**:对于大型数据集,计算总页数可能会很慢,可以考虑预计算并在首次访问时存储。 3. **使用索引**:确保用于分页的字段有索引,以提高查询性能。 4. **避免全表扫描**:在SQL查询中尽量避免使用...
在数据库查询中,如果一次性加载所有数据,对于大数据量来说会消耗大量内存,且页面加载速度慢。因此,分页通过限制每次请求的数据量,只加载当前页所需的数据,从而实现高效加载。PHP分页类就是用来封装这个过程,...
这样,用户可以逐页浏览,而不会因为数据过多导致页面加载慢或内存消耗大。 在SQL中,分页查询通常有两种主要方法:基于ROW_NUMBER()的窗口函数和基于LIMIT与OFFSET的语法(在MySQL、PostgreSQL等数据库中)。 1. ...
分页控件的主要工作原理是限制一次加载的数据量,只显示当前页的数据,从而避免一次性加载所有数据导致页面响应变慢。这涉及到数据库查询的分页,通常使用SQL的`OFFSET-FETCH`(SQL Server 2012及以上版本)或`LIMIT...
- 数据总量估算:对于非常大的数据集,获取准确的总记录数可能会很慢,可以采用近似估算或者在后台异步计算。 7. **注意事项** - 避免内存溢出:分页查询时要防止一次性加载过多数据,导致内存压力过大。 - 用户...
在网页上,如果一次性显示所有数据,可能会导致页面加载速度变慢,甚至对用户造成困扰。分页就是将大量数据分成若干小部分,每次只加载一部分到页面上,让用户能够逐步浏览。这样不仅可以提高页面加载速度,还能使...
它使得大量数据可以被有效地组织和展示,避免一次性加载所有数据导致页面响应慢或内存资源的过度消耗。下面将详细阐述分页的相关知识点。 1. **分页原理**: 分页的基本思想是将大数据集分割成小块,每次只加载一...
这样可以避免一次性加载大量数据导致的页面加载速度慢和内存消耗过大问题。在Web开发中,分页通常应用于数据库查询结果、用户评论、产品列表等场景。 **前端分页实现** 前端分页通常由JavaScript或其库(如jQuery...