mysql大数据的分页

原创 2013年12月03日 13:06:37

mysql数据库中有个limit字段可以很方便的实现分页,小数据量的时候可以直接使用,但是当数据量大的时候就会出现性能问题。

例如表user有四个字段 uid int,uname varchar(30),password varchar(30) 。uid是自增主键,中间可能有缺失,不连续的。

select * from user limit 0,10 和 select * from user limit 100000,10这两个的效率差距很大,原因在于 limit n, m 在取值的时候,会先查询出n+m rows的数据,然后把前n个舍弃,返回m条数据。

对于这个优化,网上有很多种方法,比如建立索引,表的自连接查询等。

select * from user a inner join (select uid from user order by score desc limit 100000,10) b on (a.uid=b.uid) 这个给出的解释是 优化前的SQL需要更多I/O浪费,因为先读索引,再读数据,然后抛弃无需的行。而优化后的SQL只读索引(Cover index)就可以了,然后通过uid读取需要的列。但是这种我测试的结果并不好,总量300w执行查询需要的时间mysql> select * from gameusers a inner join (select SeqId from gameusers order by LastLoginTime desc limit 1000000,10) b on (a.SeqId=b.SeqId); 10 rows in set (2 min 41.97 sec) 需要的时间还是很多的。这个方法的效果也不是很好,因为它的子查询中没有避免limit第一个参数很大,依旧要读取很多,然后再舍弃,取自己需要的。

优化最好方法就是让limit的第一个参数尽量小。在实际应用中都会有当前页currentPage,每页perpage条数据。相对于当前页查询中间某页的数据,可以先取当前页的最大big_uid和最小min_uid,那就会简单很多。

如果查询页比当前页大 sql可以如下:

select × fromuser where uid > big_uid orderby uid asclimit (page - currentPage - 1)*perpage, perpage;

如果查询页比当前页小 则sql:

select * from userwhere uid < min_id orderby uid desclimit (currentPage - page - 1)*perpage, perpage;

如果查询最后一页,则sql:

int total_rows =  select count(uid)  from user; #总行数

int last_page_num = total_rows % parpage  ;#最后一页的个数

select * from userwhere order by uid desc limit 0,  last_page_num ;


但是如果是一个关于商品的排序,例如表product有四个字段: pid int,name varchar(30), type tinyint, price int(6), sellnum int 。表里的数据有1000W,如下两个排序

select * from product where type=10 order by price asc limit 10000,10; #取类型10,按照价格升序排列的第10000页开始取10条数据

select * from product where type=10 order by sellnum asc limit 100000,10;#取类型10,按照销量降序排列的第100000页开始取10条数据

对于以上用户的行为你可以认为有病,不过如果真要做,应该怎么优化。

如果这种情况真要做,如何实现快速取值。如果单凭slq本身似乎有点不够了,这就需先对某些数据做一次缓存,排序,然后在按照对应的uid取数据,效果应该好点。




MySQL处理千万级数据查询、分页

MySQL数据库优化处理实现千万级快速分页分析,来看下吧。 数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用tex...
  • educast
  • educast
  • 2015年05月08日 14:23
  • 8210

mysql大数据量分页查询优化技巧

PS:我们知道mysql使用Limit实现了分页查询,在我们平常的Web开发中常用一些通用的分页查询jar包,比如说有名的中国开发者实现的java Pagehelper(Github地址https:/...
  • u011687186
  • u011687186
  • 2017年04月17日 16:23
  • 1273

mysql数据库大数据量的查询优化和分页测试

http://blog.sina.com.cn/s/blog_438308750100im0b.html 有什么问题:yubaojian0616@163.com 于堡舰   我原来的...
  • u014386474
  • u014386474
  • 2016年07月01日 17:08
  • 4621

详细讲解MySQL大数据量分页SQL语句优化

  • 2014年07月10日 15:27
  • 57KB
  • 下载

Mysql优化-大数据量下的分页策略

一。前言 通常,我们分页时怎么实现呢? SELECT * FROM table ORDER BY id LIMIT 1000, 10;  但是,数据量猛增以后呢? SELECT * FROM ta...
  • ty_hf
  • ty_hf
  • 2017年02月06日 22:40
  • 824

mysql数据库大数据量的查询优化和分页测试

http://blog.sina.com.cn/s/blog_438308750100im0b.html 有什么问题:yubaojian0616@163.com 于堡舰   我原来的...
  • u014386474
  • u014386474
  • 2016年07月01日 17:08
  • 4621

MySql 分页SQL 大数据量limit替代和优化(试验)

参考:https://my.oschina.net/cart/blog/354999 select SQL_NO_CACHE u.id, u.user_id, u.user_name, u...
  • yehuang_0801
  • yehuang_0801
  • 2017年12月07日 16:13
  • 46

MySQL大数据量分页SQL语句优化

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

MySQL大数据量分页SQL语句优化

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

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

原文转载:http://blog.csdn.net/lengyue1084/article/details/60868406 1. 直接用limit start, count分页语句, 也是我程...
  • yanguo110
  • yanguo110
  • 2017年12月01日 15:02
  • 84
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql大数据的分页
举报原因:
原因补充:

(最多只允许输入30个字)