文档章节

mysql 分页优化

zhsh
 zhsh
发布于 2015/05/31 00:13
字数 1111
阅读 24
收藏 1
点赞 0
评论 0

mysql分页就直接使用limit进行操作,limit如果我们直接不加任何处理可能数据大了就会很卡的,下面我来给大家介绍mysql分页实例与分页性能优化。

一,最常见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;

处理”下一页”的时候SQL语句可以是:

 代码:

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;

不管翻多少页,每次查询只扫描20行。

如果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;

 代码:

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

我们还可以使用分页存储过程代码

 代码:

DELIMITER $$;
DROP PROCEDURE IF EXISTS `sp_page`$$
CREATE PROCEDURE `sp_page`(
 in _pagecurrent int,/*当前页*/
 in _pagesize int,/*每页的记录数*/
 in _ifelse varchar(1000),/*显示字段*/
 in _where varchar(1000),/*条件*/
 in _order varchar(1000)/*排序*/
)
COMMENT '分页存储过程'
BEGIN
 if _pagesize<=1 then 
  set _pagesize=20;
 end if;
 if _pagecurrent < 1 then 
  set _pagecurrent = 1; 
 end if;
 
 set @strsql = concat('select ',_ifelse,' from ',_where,' ',_order,' limit ',_pagecurrent*_pagesize-_pagesize,',',_pagesize); 
 prepare stmtsql from @strsql; 
 execute stmtsql; 
 deallocate prepare stmtsql;
 set @strsqlcount=concat('select count(1) as count from ',_where);/*count(1) 这个字段最好是主键*/
 prepare stmtsqlcount from @strsqlcount; 
 execute stmtsqlcount; 
 deallocate prepare stmtsqlcount; 
END$$
DELIMITER ;$$

调用代码

调用例1  call sp_Page(1,3,'*','test','order by id desc');

调用例2  call sp_Page(1,3,'*','test where sort=1','order by id desc');

调用例3  call sp_Page(1,3,'id,title','test where sort=1','order by id desc');






© 著作权归作者所有

共有 人打赏支持
zhsh
粉丝 0
博文 3
码字总数 1642
作品 0
孝感
[MySQL优化案例]系列 — 分页优化

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

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

sql优化(二)

传送门 sql优化(一)http://my.oschina.net/zimingforever/blog/59515 今天突然发现昨天写的一个sql效率的博文上了OSC首页推荐。感谢国家。 明天又要开周会分享sql优化了。话说上次介绍这次...

王小明123 ⋅ 2012/06/06 ⋅ 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 ⋅ 0

☆MySQL order by limit 分页数据重复问题

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

猿码道 ⋅ 05/14 ⋅ 0

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

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

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

MySQL · 答疑解惑 · MySQL Sort 分页

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

阿里云RDS-数据库内核组 ⋅ 2015/06/05 ⋅ 0

SQL 左连接(left join) 排序 分页 中遇到的未按理想状态排序分页的解决方案

SELECT a.id AS "id", a.code AS "code", a.name AS "name", a.type AS "type", a.importancedegree AS "importanceDegree", a.techstate AS "techState", a.customerid AS "customerid", a.......

长沙大东家 ⋅ 2016/01/20 ⋅ 0

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

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

切切歆语 ⋅ 01/25 ⋅ 0

mysql大数据如何优雅地分页

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

ericSM ⋅ 2016/07/07 ⋅ 2

经典分享MySQL的limit查询优化

以下的文章主要是对MySQL limit查询优化的具体内容的介绍,我们大家都知道MySQL数据库的优化是相当重要的。其他最为常用也是最为需要优化的就是limit。MySQL的limit给分页带来了极大的方便,...

五味格子 ⋅ 2011/05/11 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Java序列化和反序列化

1、什么是序列化和反序列化 序列化:把对象转换为字节序列的过程。 反序列化:把字节序列恢复成对象的过程。 2、被序列化的类需要实现serializable接口,只是为了标注该对象是可以被序列化的...

IT-Mamba ⋅ 4分钟前 ⋅ 0

流式构建原理

流式构建需要达到分钟级的数据更新频率,Kylin采用类似于Spark Streaming的做法,每隔数分钟进行一次微构建。这边的构建需要考虑到一个延迟因素,分布式网络存在延迟等因素,该时间段的数据有...

无精疯 ⋅ 5分钟前 ⋅ 0

在maven项目工程编写solr代码,需要的依赖

solrJ <dependency> <groupId>org.apache.solr</groupId> <artifactId>solr-solrj</artifactId> <version>6.6.2</version> </dependency> <dependency> <groupId>org.apache.httpcomponents<......

爱运动的小乌龟 ⋅ 12分钟前 ⋅ 0

Spring Cloud Config

Spring Cloud Config application.yml 和 bootstrap.yml 区别 加载顺序 bootstrap.yml 先加载 application.yml 后加载 bootstrap.yml 用于应用程序上下文的引导阶段。 bootstrap.yml 由父 Sp...

施长成 ⋅ 22分钟前 ⋅ 0

MapReduce V2——YARN调度

一、MapReduce的局限性 1、JobTracker的单点问题 2、TaskTracker的作业信息分配的过于简单,当有资源消耗较大的或者时间较长的Task分配在同一个节点上的时候,容易失败或者等待时间过长 3、作...

cjxcloud ⋅ 23分钟前 ⋅ 0

EOS踩坑--脆弱的数据库

安装EOS我就不在这赘述了,来唠唠EOS踩到的坑,跟着官方文档走,然后到了同步数据的时候,Ctrl+Z结束了之后,再次运行报错 root@ubuntu:~# nodeos -e -p eosio --plugin eosio::chain_api_pl...

开源首席鉴黄师 ⋅ 23分钟前 ⋅ 0

对于程序员的招聘问题,作为软件人的一些吐槽和建议

作为软件人,找工作有时候似乎挺苦逼的。 说真的,让我去掉前面这句中“似乎”二字吧。就是苦逼!很多人都曾抱怨处在招聘的一方很糟糕——我们没有任何可靠的方式来甄别会写代码并且写得好的...

老道士 ⋅ 38分钟前 ⋅ 0

Linux下MySQL表名不区分大小写的设置方法(抄袭别人的)

Linux下MySQL表名不区分大小写的设置方法 MySQL表名不区分大小写的设置方法 在用centox安装mysql后,把项目的数据库移植了过去,发现一些表的数据查不到,排查了一下问题,最后发现是表名的大...

随风而浮沉 ⋅ 47分钟前 ⋅ 0

ubuntu下安装宋体simsun

sudo cp simsun.ttc /usr/share/fonts cd /usr/share/fonts sudo chmod 644 simsun.ttc 更新字体缓存: 代码: sudo mkfontscale 代码: sudo mkfontdir 代码: sudo fc-cache -fsv 安装chrome扩......

wangxuwei ⋅ 49分钟前 ⋅ 0

利用 ssh 传输文件

Linux 下一般可以用 scp 命令通过 ssh 传送文件: #把服务器上的 /home/user/a.txt 发送到本机的 /var/www/local_dir 目录下scp username@servername:/home/user/a.txt /var/www/local_dir...

大灰狼时间 ⋅ 59分钟前 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部