关闭

mysql大数据的分页

394人阅读 评论(0) 收藏 举报
分类:

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取数据,效果应该好点。




0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:17584次
    • 积分:507
    • 等级:
    • 排名:千里之外
    • 原创:25篇
    • 转载:18篇
    • 译文:0篇
    • 评论:0条