当一个表数据有几百万的数据时,分页的时候成了问题
如 select * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢,可以按照一下方法解决
最近一个网站的服务器评论被人刷死,导致mysql数据库异常发生too many open connections
引发的SQL语句
代码如下复制代码
SELECT a.uid, a.veil, a.content, a.datetimes, a.audit, b.user_name, b.uc_id
FROM news_talkabout a
LEFT JOIN users_info b ON a.uid = b.id
WHERE infoid =11087
ORDER BY a.id DESC
LIMIT 451350 , 30
丢在phpmyadmin里执行一下,是很慢。
让人思考是什么因素
代码如下复制代码
SELECT a.uid, a.veil, a.content, a.datetimes, a.audit, b.user_name, b.uc_id
FROM news_talkabout a
LEFT JOIN users_info b ON a.uid = b.id
WHERE infoid =11087
ORDER BY a.id DESC
LIMIT 0 , 30
第一页会很快
PERCONA PERFORMANCE CONFERENCE 2009上,来自雅虎的几位工程师带来了一篇"EfficientPagination Using MySQL"的报告
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最大的,超过了可以另行处理,一般连续翻页过大,用户体验很差,则应该提供更优的用户体验给用户。
limit 分页优化方法
1.子查询优化法
先找出第一条数据,然后大于等于这条数据的id就是要获取的数据
缺点:数据必须是连续的,可以说不能有where条件,where条件会筛选数据,导致数据失去连续性
实验下
代码如下复制代码
mysql> set profiling=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.倒排表优化法
倒排表法类似建立索引,用一张表来维护页数,然后通过高效的连接得到数据
缺点:只适合数据数固定的情况,数据不能删除,维护页表困难
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.只查索引法
相关推荐
Mysql的分页的两个参数 select * from user limit 1,2 ... 您可能感兴趣的文章:详解MySQL的limit用法和分页查询语句的性能分析MYSQL分页limit速度太慢的优化方法mysql limit分页优化方法分享Mysql limit
在MySQL中,`LIMIT`子句常用于实现分页功能,但当表数据量非常大时,使用`LIMIT`进行分页可能会导致性能急剧下降。这是因为`LIMIT`语句需要扫描大量的行,尤其是在`OFFSET`值较大的情况下。针对这个问题,我们可以...
本文档针对mysql分页之limit慢的问题,使用联合索引在大数据量的情况下优化limit分页的性能
然而,随着偏移量(offset)的增长,LIMIT分页的性能会逐渐下降,因为MySQL需要遍历到偏移量位置才能开始返回结果,对于大数据集来说,这可能会变得非常慢。 为了解决LIMIT分页的性能问题,可以采用一些优化策略。...
在实际开发中,我们经常会遇到 MySQL 数据库的性能问题,特别是在处理千万级数据时,分页查询的性能会变得非常慢。在这篇文章中,我们将探讨如何优化 MySQL 千万级快速分页,详细介绍解决方案。 问题描述 我们有一...
分页可以有效提高页面的响应速度,并优化用户的浏览体验。本文主要介绍了在Node.js环境下,如何使用MySQL数据库实现分页功能。 首先,分页的基本思路是确定每页显示的数据条目数(即页面大小),然后根据当前页码...
在MySQL数据库管理中,分页查询是常见的操作,特别是在数据量庞大的情况下,为了提高用户体验,分页能够有效地加载和展示数据。高效的分页查询对于优化数据库性能至关重要。本篇文章将探讨如何在MySQL中实现高效的...
【MySQL千万级大表深度分页慢的原因及优化方法】 在MySQL中,处理千万级大表的深度分页查询时,通常会遇到性能问题。这是因为MySQL的查询优化器在面对大量数据的分页请求时,可能选择全表扫描而不是利用索引来提高...
【使用limit,offset分页场景时为什么会慢】 在SQL查询中,使用`LIMIT`和`OFFSET`进行分页操作在大数据量的情况下可能会变得极其缓慢。这是因为`OFFSET`需要跳过指定数量的行,而数据库必须读取并忽略这些行,这在...
三、优化分页性能 1. **避免全表扫描**:尽量使用索引,尤其是在`WHERE`子句中的字段,以提高查询效率。 2. **减少`OFFSET`的使用**:`OFFSET`值越大,查询性能越低,因为数据库需要跳过更多的记录。尽量减少页面...
本篇文章将深入探讨如何优化使用LIMIT进行分页查询的方法。 首先,让我们回顾LIMIT的基本语法。LIMIT关键字允许我们指定返回结果集的开始位置和结束位置。例如: ```sql SELECT * FROM table LIMIT offset, limit;...
### MySQL 百万级分页优化(Mysql千万级快速分页) #### 背景与挑战 在处理大规模数据集时,例如拥有数百万乃至数千万条记录的数据库表,传统的分页查询方法可能会遇到性能瓶颈。特别是使用`LIMIT`进行分页时,随着...
可以考虑使用`子查询`或`变量`来优化分页查询。 7. **用户交互**:提供良好的用户界面,如显示当前页码、总页数,以及是否显示“第一页”和“最后一页”的链接。 8. **安全性**:确保从客户端接收到的页码参数经过...
mysql分页查询方法及其优化方法一语句样式: mysql中,可用如下方法: select * from 表名称 limit offset, pagesize使
在MySQL中,最常用的分页查询方法是结合`LIMIT`和`OFFSET`关键字。`LIMIT`用于指定每页显示的记录数,而`OFFSET`则用于跳过前面的记录,达到定位到某一页的目的。例如,如果我们想获取第2页,每页10条记录的数据,...
在IT行业中,数据库查询的效率和用户体验是至关重要的,特别...通过对这些文件的学习和理解,开发者可以掌握如何在实际项目中实现基于JSP、JavaBean、JDBC和MySQL的分页功能,从而优化数据库查询并提供更好的用户体验。
在MySQL中,我们可以使用`LIMIT`和`OFFSET`关键字来实现分页。`LIMIT`用于指定每页显示的数据条数,`OFFSET`用于指定从哪一条记录开始取数据。例如,要获取第2页(每页5条数据)的信息,SQL语句如下: ```sql ...
在MySQL中,面对百万级数据量的分页查询,如何高效地进行操作并优化查询性能是数据库管理员和开发人员必须关注的问题。以下是一些常用的方法和优化建议: 1. **直接使用LIMIT语句**:这是最基础的分页查询方式,如`...
7. **构造分页SQL**:使用`LIMIT`子句构造带有分页条件的SQL查询,例如`SELECT * FROM table_name LIMIT start, limit`。 8. **执行SQL并获取数据**:使用`mysqli_query`或`PDO::query`执行SQL,获取当前页的数据。 ...