文档章节

mysql 分页优化

zhsh
 zhsh
发布于 2015/05/31 00:13
字数 1111
阅读 25
收藏 1

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
博文 5
码字总数 1931
作品 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
数据分页资料汇总

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
☆MySQL order by limit 分页数据重复问题

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

猿码道
05/14
0
0
SQL 常用优化手段总结 - 小技巧

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

给你添麻烦了
01/10
0
0
MySQL · 答疑解惑 · MySQL Sort 分页

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

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

没有更多内容

加载失败,请刷新页面

加载更多

下一页

day60-20180818-流利阅读笔记-待学习

钉钉:工作的归工作,生活的…也归工作? 毛西 2018-08-18 1.今日导读 你用过“钉钉”么?被公司要求使用钉钉的感受是如何的呢?这款阿里巴巴旗下的移动办公社交平台在弯道超车微信、为许多企...

aibinxiao
48分钟前
6
0
Kubernetes的HTTPS和证书问题,汇总

通过Kubernetes建立服务网站,需要干的事情和HTTPS和证书问题,汇总如下: 建立Nginx服务器 搞服务器第一步,Ubuntu 18.04设置静态IP 安装Nginx服务。 Kubernetes的deployment使用 创建服务,...

openthings
今天
2
0
php 使用redis锁限制并发访问类

1.并发访问限制问题 对于一些需要限制同一个用户并发访问的场景,如果用户并发请求多次,而服务器处理没有加锁限制,用户则可以多次请求成功。 例如换领优惠券,如果用户同一时间并发提交换领...

豆花饭烧土豆
今天
0
0
Linux环境搭建 | 手把手教你配置Linux虚拟机

在上一节 「手把你教你安装Linux虚拟机」 里,我们已经安装好了Linux虚拟机,在这一节里,我们将配置安装好的Linux虚拟机,使其达到可以开发的程度。 Ubuntu刚安装完毕之后,还无法进行开发,...

良许Linux
今天
0
0
(三)Nginx配置·续

概述 前文写了关于Nginx环境配置,但是还没有完,接下来将会继续讲三个相关的配置 主要是以下三个 1.Nginx访问日志 2.Nginx日志切割 3.静态文件不记录日志和过期时间 Nginx访问日志 1.先看看...

杉下
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部