文档章节

MySQL分页的方法及优化

s
 sunsyu
发布于 2017/04/13 16:03
字数 2337
阅读 4
收藏 0

在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候我们就需要用到mysql为我们提供的分页功能了。分页在MySQL中使用的是LIMIT关键字,其用法如下:

SELECT * FROM TABLE LIMIT [OFFSET,] ROWS | ROWS OFFSET OFFSET

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。日常使用中,其格式一般为:limit 当前页码*页面容量-1 , 页面容量。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量,不能是负数。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。

#返回第6到15条数据
SELECT * FROM testinnodb LIMIT 5,10;
#返回前十条记录,相当于LIMIT 0,10
SELECT * FROM testinnodb LIMIT 10;
#返回第十条数据
SELECT * FROM testinnodb LIMIT 9,1;

上面就是MySQL中LIMIT的基本用法,下面分析一下其在实际使用中的效率。
测试表为testinnodb,建表语句如下:

CREATE TABLE yzc.testinnodb ( id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, time datetime DEFAULT NULL, type int(10) NOT NULL, title varchar(12345) NOT NULL DEFAULT '', PRIMARY KEY (id) ) ENGINE = INNODB

向其中插入150W条数据。
首先测试从其中取出15条数据,偏移量分别设为0.13W、1.3W、13W、130W后其时间分别为多少

SELECT * FROM testinnodb LIMIT 1300,15;------时间: 0.003s
SELECT * FROM testinnodb LIMIT 13000,15;------时间: 0.015s
SELECT * FROM testinnodb LIMIT 130000,15;------时间: 0.109s
SELECT * FROM testinnodb LIMIT 1300000,15;------时间: 1.603s

多次运行,其平均时间大致如上,通过以上的测试,可以发现LIMIT语句在偏移量比较小的时候效率比较好,但是当偏移量比较大的时候,其性能就会急剧下降。
对于LIMIT M,N来说,其中M为偏移量,N为需要取的数据的行数,其意思是扫描前面的M+N行,然后扔掉前M行,返回剩下的N行,这在一个高并发的应用里,如果M很大,每次查询需要扫描超过数万行,性能肯定大打折扣。
查看上面任一语句的执行计划,如下:
130W数据
可以很明显的发现,上面的所有语句都是全表扫描,并且未使用任何的索引


常见的分页优化方法:

还是对testinnodb表测试,假设每页数据为500条,总共有3000页,查询其中的第100页的下一页,也就是ID为50001-50500的记录。下面语句的执行时间都是在数据库中多次运行取时间的平均值。

方法一: 直接使用数据库提供的SQL语句
语句样式: MySQL中,可用如下方法:

SELECT * FROM testinnodb LIMIT 50000,500;------时间: 0.029s

适应场景: 适用于数据量较少的情况(元组百/千级)
缺点: 全表扫描,速度会很慢。
执行计划:
直接查询时

方法二: 基于索引再排序
语句样式: MySQL中,可用如下方法:

SELECT * FROM testinnodb WHERE id >(100*500) ORDER BY id ASC LIMIT 500;------时间: 0.002s

适应场景: 适用于数据量多的情况(元组数上万). ORDER BY后的列对象是主键或唯一,所以,使得ORDER BY操作能利用索引被消除
原因: 索引扫描,速度会很快. 但MySQL的排序操作,只有ASC没有DESC(DESC是假的,未来会做真正的DESC).
执行计划:
索引排序查询

方法三: MySQL自带的BETWEEN…AND… 语句查询优化

SELECT * FROM testinnodb WHERE id BETWEEN 50000 AND 50500;------时间: 0.006s

执行计划:
BETWEEN.

方法四: 如果需要查询 id 不是连续的一段,最佳的方法就是先找出 id ,然后用 in 查询
原理: MySQL 的索引对于in语句同样是有效的

SELECT * FROM testinnodb WHERE id IN(10000, 100000, 1000000...);

方法五: 利用”子查询/JOIN+索引”快速定位元组的位置,然后再读取元组
子查询示例:
原理:先找出第一条数据,然后大于等于这条数据的id的就是要获取的数据,减少了无用的IO操作,只对需要的数据执行SELECT *操作
缺点:数据必须是连续的,否则可能需要先查询第一行所在的位置
子查询:

SELECT * FROM testinnodb WHERE id >= (SELECT id FROM testinnodb LIMIT 50000,1) LIMIT 500;------时间: 0.018s

执行计划如下:
子查询加索引

JOIN联接两个表:

SELECT * FROM testinnodb INNER JOIN(SELECT id FROM testinnodb ORDER BY id LIMIT 50000, 500) AS t USING (id);   ------时间: 0.020s

执行计划:
JOIN联接两个表

方法六:存储过程类
原因: 存储过程中的SQL都是预先编译过的,这样子把操作封装在服务器,相对更快一些。

方法七: 反向查找优化法
原理:当偏移超过一半记录数的时候,先用排序,这样偏移就反转了
LIMIT偏移算法:

正向查找: (当前页 - 1) * 页长度
反向查找: 总记录 - 当前页 * 页长度

缺点: ORDER BY优化比较麻烦,要增加索引,索引影响数据的修改效率,并且要知道总记录数 ,偏移大于数据的一半
实例:
查询其中的第2001页的下一页,也就是ID为1000001-1000500的记录。
直接查找:

SELECT * FROM testinnodb LIMIT 1000000,500;------时间: 1.168s

反向查找:

SELECT * FROM testinnodb ORDER BY id DESC LIMIT 499500,500;------时间: 0.722s

网上有人写使用 SQL_CALC_FOUND_ROWS。 没有道理,勿模仿


其它:包含排序操作的分页需求
解决思路: 建立合适的索引,先找出对应的主键,在根据主键查找需要的数据,减少不必要的IO操作。
假设现在的查找需求为,跟据type升序排序,查找第100页。
创建的索引为:

ALTER TABLE testinnodb ADD INDEX idx_type(type) USING BTREE;

直接查找:

SELECT * FROM testinnodb ORDER BY type LIMIT 50000,500;
无索引:1.686s      有索引:1.581s

直接操作时,在有无索引来那个中条件下,虽然有优化,但是好像还是不太理想。
借鉴上面的方法,继续优化:

JOINSELECT * FROM testinnodb JOIN (SELECT id FROM testinnodb ORDER BY type LIMIT 50000,500) AS t USING(id);
无索引:1.510s      有索引:0.016s   
子查询:
SELECT * FROM testinnodb WHERE id >= (SELECT id FROM testinnodb ORDER BY type LIMIT 50000,1) LIMIT 500;
无索引:1.506s      有索引:0.017s

现在的执行效率就可以达到我们的需求了。


实际工作中对于LIMIT的需求

实际编码的过程中,我们经常会遇到类似于:“上一页 1 2 3 4 5 6 7 8 9 … 下一页”这样的翻页需求。这个时候我们又应该如何优化查询呢?
我们已经知道limit 10000,500的意思扫描满足条件的10500行,扔掉前面的10000行,返回最后的500行,大量的时间都浪费在前面的10000行上。所以对于上面的需求,如果LIMIT M,N不可避免的话,要优化效率,只有尽可能的让M小一些。
还是对testinnodb表测试,假设每页数据为500条,总共有3000页,假设当前是第10页,当前页的最大ID是5000,最小ID是4501。
当前的第10页的SQL如下:

SELECT * FROM testinnodb WHERE id >=4501 ORDER BY id ASC LIMIT 0,500;

比如要跳到第9页,SQL语句可以这样写:

SELECT * FROM testinnodb WHERE id <4501 ORDER BY id DESC LIMIT 0,500;

比如要跳到第8页,SQL语句可以这样写:

SELECT * FROM testinnodb WHERE id <4501 ORDER BY id desc LIMIT 500,500;

比如要跳到第7页,SQL语句可以这样写:

SELECT * FROM testinnodb WHERE id <4501 ORDER BY id desc LIMIT 1000,500;

跳转到第11页:

SELECT * FROM testinnodb WHERE id >5000 ORDER BY id ASC LIMIT 0,500;

跳转到第12页:

SELECT * FROM testinnodb WHERE id >5000 ORDER BY id ASC LIMIT 500,500;

原理还是一样,记录住当前页id的最大值和最小值,计算跳转页面和当前页相对偏移,由于页面相近,这个偏移量不会很大,这样的话M值相对较小,大大减少扫描的行数。其实传统的LIMIT M,N,相对的偏移一直是第一页,这样的话越翻到后面,效率越差,而上面给出的方法就没有这样的问题。


MYSQL的FOUND_ROWS()函数

Mysql FOUND_ROWS() 函数结合SQL_CALC_FOUND_ROWS在SELECT中可以得到两个结果:
1. 得到Limit的内容
2. 得到去除Limit以后所有行数

SELECT语句中经常可能用LIMIT限制返回行数。有时候可能想要知道如果没有LIMIT会返回多少行,但又不想再执行一次相同语句。那么,在SELECT查询中包含SQL_CALC_FOUND_ROWS选项,然后执行FOUND_ROWS()就可以了:

    select SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
    SELECT FOUND_ROWS();

其中SQL_CALC_FOUND_ROWS 告诉Mysql将sql所处理的行数记录下来,FOUND_ROWS() 则取到了这个纪录。 虽然也是两个语句,但是只执行了一次主查询,所以效率比原来要高很多。

  1. 如果在前一条语句中使用SQL_CALC_FOUND_ROWS选项,FOUND_ROWS()将返回第一条语句没有LIMIT时返回的行数。
  2. 如果在前一条语句中没有使用SQL_CALC_FOUND_ROWS选项,FOUND_ROWS()将返回前一条语句实际返回的行数。
    如果使用 SELECT SQL_CALC_FOUND_ROWS,MySQL必须计算所有结果集的行数。尽管这样,总比再执行一次不使用LIMIT的查询要快多了吧,因为那样结果集要返回客户端的。

© 著作权归作者所有

s
粉丝 0
博文 109
码字总数 135924
作品 0
深圳
私信 提问
[MySQL优化案例]系列 — 分页优化

通常,我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询。例如下面这个SQL: SELECT * FROM t1 WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10; 或者像下面这个不带任何条件的分...

运维技术
2016/08/08
58
0
☆MySQL order by limit 分页数据重复问题

0 问题描述 在MySQL中我们通常会采用limit来进行翻页查询,比如limit(0,10)表示列出第一页的10条数据,limit(10,10)表示列出第二页。但是,当limit遇到order by的时候,可能会出现翻到第二页...

猿码道
2018/05/14
0
0
MySQL · 答疑解惑 · MySQL Sort 分页

背景 6.5号,小编在 Aliyun 的论坛中发现一位开发者提的一个问题,说 RDS 发现了一个超级大BUG,吓的小编一身冷汗 = =!! 赶紧来看看,背景是一个RDS用户创建了一张表,在一个都是NULL值的非索...

阿里云RDS-数据库内核组
2015/06/05
0
0
mysql--------大数据量分页sql语句优化

分页程序原理很简单,这里就不多说了,本篇文章主要说的是在数据表记录量比较大的情况下,如何将分页SQL做到更优化,让MySQL执行的更快的方法。 一般的情况下,我们的分页SQL语句是这样的: ...

切切歆语
2018/01/25
0
0
数据分页资料汇总

1.数据库分页Java实现(http://www.cnblogs.com/ITtangtang/archive/2012/04/21/2462385.html) 2.Java分页代码的实现(http://www.open-open.com/lib/view/open1346772322162.html) 3. java分页......

IT追寻者
2016/08/13
41
0

没有更多内容

加载失败,请刷新页面

加载更多

我对算卦的认识

现在越来越认识到算卦其实就是帮自己尽快的做一个决定。 有一次看锵锵三人行,有一期一个嘉宾讲了他去算卦的事情。当时他有两个选择,一个是从商,一个是当演员。算卦的结果是当演员,他就放...

我是菜鸟我骄傲
14分钟前
1
0
获取小程序链接

https://zhidao.baidu.com/question/494453810708570772.html

Js_Mei
17分钟前
0
0
AIOps落地的前提条件探索

笔者根据自身的技术和行业理解,探索分析AIOps在企业落地的前提条件。 涉及关键字:自动化运维、AIOps、技术运营PaaS、蓝鲸等。 作者:张敏 AIOps概念 Gartner在2016年时便提出了AIOps的概念...

嘉为科技
27分钟前
0
0
Cookie起源与发展

上一篇我们在讲优酷弹幕爬虫的时候,引入了一个新的知识点:Cookie,由于篇幅有限当时只是简单的给大家介绍了一下它的作用,今天我们就来全面了解一下Cookie(小饼干)以及相关的知识! 相信...

猪哥66
29分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部