文档章节

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

mikiyonney
 mikiyonney
发布于 2015/04/20 20:57
字数 956
阅读 174
收藏 21

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
40
2
数据库查询返回特定结果即分页查询

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
0
mysql--------大数据量分页sql语句优化

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

切切歆语
01/25
0
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
1K
0
SQL 常用优化手段总结 - 小技巧

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

给你添麻烦了
01/10
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Sping之项目中pofile的应用

工程中,我们必须要面对的一件事就是, 开发环境中使用的数据库连接地址等与生产上的不同, 如果上线, 那么我们是否还要手动修改这些地址么, 这样做有很多弊端, 不方便, 这时我们就可以使用spr...

克虏伯
11分钟前
0
0
Linux中安装MySQL

Linux中安装MySQL 一、准备工作 此处准备的操作系统位CentOS 7。 MySQL安装包: MySQL-server-5.6.29-1.linux_glibc2.5.x86_64.rpm MySQL-client-5.6.29-1.linux_glibc2.5.x86_64.rpm 将准备......

星汉
15分钟前
0
0
深入理解Hadoop之HDFS架构

Hadoop分布式文件系统(HDFS)是一种分布式文件系统。它与现有的分布式文件系统有许多相似之处。但是,与其他分布式文件系统的差异是值得我们注意的: HDFS具有高度容错能力,旨在部署在低成...

架构师springboot
20分钟前
0
0
MaxCompute表设计最佳实践

MaxCompute表设计最佳实践 产生大量小文件的操作 MaxCompute表的小文件会影响存储和计算性能,因此我们先介绍下什么样的操作会产生大量小文件,从 而在做表设计的时候考虑避开此类操作。 使用...

阿里云官方博客
20分钟前
0
0
云上领跑,快人一步:华为云抢先发布Redis5.0

12月17日,华为云在DCS2.0的基础上,快人一步,抢先推出了新的Redis 5.0产品,这是一个崭新的突破。目前国内在缓存领域的发展普遍停留在Redis4.0阶段,华为云率先发布了Redis5.0,全面展现了...

中间件小哥
20分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部