文档章节

mysql分页与分页性能查询优化

mikiyonney
 mikiyonney
发布于 2015/04/20 20:57
字数 956
阅读 141
收藏 21
点赞 0
评论 0

mysql分页就直接使用limit进行操作,limit如果我们直接不加任何处理可能数据大了就会很卡的。

一. 最常见MYSQL最基本的分页方式:

    select * from content order by id desc limit 0, 10

在中小数据量的情况下,这样的SQL足够用了,唯一需要注意的问题就是确保使用了索引。随着数据量的增加,页数会越来越多,查看后几页的SQL就可能类似:

    select * from content order by id desc limit 10000, 10

一言以蔽之,就是越往后分页,LIMIT语句的偏移量就会越大,速度也会明显变慢。

MYSQL分页性能优化

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;

不管翻多少页,每次查询只扫描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 40,20;

还有网友说可以使用子查询来操作

一,子查询的分页方式来提高分页效率,飘易用的SQL语句如下:

 代码如下复制代码SELECT * FROM `content` WHERE id <= 

(SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) ORDER BY id desc LIMIT $pagesize

为什么会这样呢?因为子查询是在索引上完成的,而普通的查询时在数据文件上完成的,通常来说,索引文件要比数据文件小得多,所以操作起来也会更有效率。(via)通过explain SQL语句发现:子查询使用了索引!

 代码如下复制代码id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY content range PRIMARY PRIMARY 4 NULL 6264 Using where

2 SUBQUERY content index NULL PRIMARY 4 NULL 27085 Using index

经过飘易的实测,使用子查询的分页方式的效率比纯LIMIT提高了14-20倍!

二,JOIN分页方式

 代码如下复制代码SELECT * FROM `content` AS t1 

JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2 

WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

经过我的测试,join分页和子查询分页的效率基本在一个等级上,消耗的时间也基本一致。explain SQL语句:

 代码如下复制代码id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY <derived2> system NULL NULL NULL NULL 1  

1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where

2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index



本文转载自:

共有 人打赏支持
mikiyonney
粉丝 5
博文 6
码字总数 921
作品 0
武汉
高级程序员
mysql大数据如何优雅地分页

mysql大数据分页 针对mysql 在大数据条件下如何提高分页性能呢 问题当迁移量很大时,查询花费时间太多 解决:利用条件查询特性优雅地弥补 limit 性能: 看图 1.从 0-5,时间 0.0001 秒 ...

ericSM ⋅ 2016/07/07 ⋅ 2

mysql--------大数据量分页sql语句优化

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

切切歆语 ⋅ 01/25 ⋅ 0

SQL 常用优化手段总结 - 小技巧

中国有句古话叫做:欲速则不达。在一口气学完了分析 SQL 语句的一般步骤与索引的正确运用方式后小憩片刻。搭配上红茶与白兰地轻松享用下面这些小技巧吧! 系列文章回顾 SQL 常用优化手段总结...

给你添麻烦了 ⋅ 01/10 ⋅ 0

数据库查询返回特定结果即分页查询

1 几种不同数据库的不同的分页写法: a mysql a) 查询前n条记录select * from table_name limit 0,nb) 查询第n条到第m条select * from table_name limit n,m b oracle a)查询前n条记录selec...

王小明123 ⋅ 2013/06/08 ⋅ 0

MySQL的LIMIT与分页优化

select from table LIMIT 5,10; #返回第6-15行数据 select from table LIMIT 5; #返回前5行 select from table LIMIT 0,5; #返回前5行 性能优化: [sql] view plaincopyprint? 基于MySQL5.0中......

2k10 ⋅ 2015/03/23 ⋅ 0

MySQL 百万级分页优化(Mysql千万级快速分页)

一般刚开始学SQL的时候,会这样写复制代码 代码如下: 但在数据达到百万级的时候,这样写会慢死复制代码 代码如下: 也许耗费几十秒 网上很多优化的方法是这样的复制代码 代码如下: 是的,速度...

yiqifendou ⋅ 2016/10/11 ⋅ 0

mysql大数据量使用limit分页,随着页码的增大,查询效率越低下

测试实验 1. 直接用limit start, count分页语句, 也是我程序中用的方法: select * from product limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, ...

文文1 ⋅ 06/12 ⋅ 0

MySQL limit 优化,百万至千万级快速分页:复合索引

MySQL 性能到底能有多高?用了php半年多,真正如此深入的去思考这个问题还是从前天开始。有过痛苦有过绝望,到现在充满信心!MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇...

xrzs ⋅ 2012/12/19 ⋅ 2

[MySQL优化案例]系列 — 分页优化

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

运维技术 ⋅ 2016/08/08 ⋅ 0

Mysql 分页语句Limit用法

1、Mysql的limit用法 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能。 SELECT * FROM table LIMIT [off...

李有常 ⋅ 2015/08/22 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

在MySQL中设置主从复制入门实例

导读 MySQL复制是一个允许您通过自动从主数据库复制到从数据库来轻松维护MySQL数据的多个副本的过程。 这可能有助于许多原因,包括为数据提供备份,一种在不使用主数据库的情况下分析数据的方...

问题终结者 ⋅ 刚刚 ⋅ 0

聊聊spring cloud gateway的RedisRateLimiter

序 本文主要研究下spring cloud gateway的RedisRateLimiter GatewayRedisAutoConfiguration spring-cloud-gateway-core-2.0.0.RELEASE-sources.jar!/org/springframework/cloud/gateway/con......

go4it ⋅ 10分钟前 ⋅ 0

169. Majority Element - LeetCode

Question 169. Majority Element Solution 思路:构造一个map存储每个数字出现的次数,然后遍历map返回出现次数大于数组一半的数字. 还有一种思路是:对这个数组排序,次数超过n/2的元素必然在中...

yysue ⋅ 13分钟前 ⋅ 0

NFS

14.1 NFS介绍 NFS是Network File System的缩写 NFS最早由Sun公司开发,分2,3,4三个版本,2和3由Sun起草开发,4.0开始Netapp公司参与并主导开发,最新为4.1版本 NFS数据传输基于RPC协议,RPC...

派派菠菜 ⋅ 14分钟前 ⋅ 0

18.进入编辑模式 vim命令模式 实践

5.5 进入编辑模式 5.6 vim命令模式 5.7 vim实践 5.5 进入编辑模式: i 在当前字符前插入 I 在光标所在行的行首插入 a 在当前字符后插入 A 在光标所在行的行尾插入 o 在当前所在行的下一行插入...

王鑫linux ⋅ 23分钟前 ⋅ 0

阻塞队列(2)--LinkedBlockingDeque底层实现

2.1 LinkedBlockingQueue是什么? 1.1 LinkedBlockingQueue是一个阻塞式的队列,继承自AbstractBlockingQueue,间接的实现了Queue接口和Collection接口。底层以链表的形式保存数据(双向链表,...

yokol ⋅ 24分钟前 ⋅ 0

NFS介绍 NFS服务端安装配置 NFS配置选项

NFS介绍 NFS是Network File System的缩写;这个文件系统是基于网路层面,通过网络层面实现数据同步 NFS最早由Sun公司开发,分2,3,4三个版本,2和3由Sun起草开发,4.0开始Netapp公司参与并主导...

lyy549745 ⋅ 34分钟前 ⋅ 0

Spring AOP 源码分析 - 筛选合适的通知器

1.简介 从本篇文章开始,我将会对 Spring AOP 部分的源码进行分析。本文是 Spring AOP 源码分析系列文章的第二篇,本文主要分析 Spring AOP 是如何为目标 bean 筛选出合适的通知器(Advisor...

java高级架构牛人 ⋅ 57分钟前 ⋅ 0

HTML-标签手册

标签 描述 <!--...--> 定义注释。 <!DOCTYPE> 定义文档类型。 <a> 定义锚。超链接 <abbr> 定义缩写。 <acronym> 定义只取首字母的缩写。 <address> 定义文档作者或拥有者的联系信息。 <apple......

ZHAO_JH ⋅ 58分钟前 ⋅ 0

SylixOS在t_main中使用硬浮点方法

问题描述 在某些使用场景中,应用程序不使用动态加载的方式执行,而是跟随BSP在 t_main 线程中启动,此时应用代码是跟随 BSP 进行编译的。由于 BSP 默认使用软浮点,所以会导致应用代码中的浮...

zhywxyy ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部