日常分页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; mysql> select count(*) from Member; mysql> pager grep !~- mysql> select * from Member limit 10, 100; mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100; mysql> select * from Member limit 1000, 100; mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100; mysql> select * from Member limit 100000, 100; mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100; mysql> nopager
*************************** 2. row *************************** *************************** 4. row *************************** *************************** 6. row *************************** |
从结果中可以得知,当偏移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.只查索引法
from:http://www.111cn.net/database/mysql/50921.htm
相关推荐
以下是一些针对MySQL `LIMIT` 分页速度慢的优化方法: 1. **避免大`OFFSET`值**: 当`OFFSET`值很大时,MySQL会跳过很多行才能到达需要返回的数据。例如,`LIMIT 200000, 10`需要扫描200010行,这是非常低效的。...
Mysql的分页的两个参数 select * from user limit 1,2 ... 您可能感兴趣的文章:详解MySQL的limit用法和分页查询语句的性能分析MYSQL分页limit速度太慢的优化方法mysql limit分页优化方法分享Mysql limit
本文档针对mysql分页之limit慢的问题,使用联合索引在大数据量的情况下优化limit分页的性能
然而,随着偏移量(offset)的增长,LIMIT分页的性能会逐渐下降,因为MySQL需要遍历到偏移量位置才能开始返回结果,对于大数据集来说,这可能会变得非常慢。 为了解决LIMIT分页的性能问题,可以采用一些优化策略。...
在实际开发中,我们经常会遇到 MySQL 数据库的性能问题,特别是在处理千万级数据时,分页查询的性能会变得非常慢。在这篇文章中,我们将探讨如何优化 MySQL 千万级快速分页,详细介绍解决方案。 问题描述 我们有一...
【MySQL千万级大表深度分页慢的原因及优化方法】 在MySQL中,处理千万级大表的深度分页查询时,通常会遇到性能问题。这是因为MySQL的查询优化器在面对大量数据的分页请求时,可能选择全表扫描而不是利用索引来提高...
在MySQL数据库管理中,分页查询是常见的操作,特别是在数据量庞大的情况下,为了提高用户体验,分页能够有效地加载和展示数据。高效的分页查询对于优化数据库性能至关重要。本篇文章将探讨如何在MySQL中实现高效的...
【使用limit,offset分页场景时为什么会慢】 在SQL查询中,使用`LIMIT`和`OFFSET`进行分页操作在大数据量的情况下可能会变得极其缓慢。这是因为`OFFSET`需要跳过指定数量的行,而数据库必须读取并忽略这些行,这在...
这两天学习了nodejs mysql 实现分页,很重要,所以,今天添加一点小笔记。 代码如下 var express = require('express'); var router = express.Router(); var settings = require('../settings.js'); var mysql =...
三、优化分页性能 1. **避免全表扫描**:尽量使用索引,尤其是在`WHERE`子句中的字段,以提高查询效率。 2. **减少`OFFSET`的使用**:`OFFSET`值越大,查询性能越低,因为数据库需要跳过更多的记录。尽量减少页面...
本篇文章将深入探讨如何优化使用LIMIT进行分页查询的方法。 首先,让我们回顾LIMIT的基本语法。LIMIT关键字允许我们指定返回结果集的开始位置和结束位置。例如: ```sql SELECT * FROM table LIMIT offset, limit;...
本示例中,我们将探讨如何在WinForm应用程序中实现MySQL数据库的分页功能,这在处理大量数据时非常实用,可以提高用户体验并减少系统资源的消耗。 首先,我们需要在Visual Studio 2010中创建一个新的WinForm项目。...
### MySQL 百万级分页优化(Mysql千万级快速分页) #### 背景与挑战 在处理大规模数据集时,例如拥有数百万乃至数千万条记录的数据库表,传统的分页查询方法可能会遇到性能瓶颈。特别是使用`LIMIT`进行分页时,随着...
在 MySQL 中通常我们使用 limit 来完成页面上的分页功能,但是当数据量达到一个很大的值之后,越往后翻页,接口的响应速度就越慢。 本文主要讨论 limit 分页大偏移量慢的原因及优化方案,为了模拟这种情况,下面首先...
可以考虑使用`子查询`或`变量`来优化分页查询。 7. **用户交互**:提供良好的用户界面,如显示当前页码、总页数,以及是否显示“第一页”和“最后一页”的链接。 8. **安全性**:确保从客户端接收到的页码参数经过...
MySQL数据库在处理大数据量时,分页查询是一种非常常见的优化手段,它可以帮助用户按需加载数据,避免一次性加载大量数据导致的性能问题。本篇文章将深入探讨MySQL中的分页存储过程及其代码调用方法。 首先,理解...
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 ...