mysql 分页查询性能_性能优化之分页查询

一、背景

大部分开发和DBA同行都对分页查询非常非常了解,看帖子翻页需要分页查询,搜索商品也需要分页查询。那么问题来了,遇到上千万或者上亿的数据量怎么快速的拉取全量,比如大商家拉取每月千万级别的订单数量到自己独立的ISV做财务统计;或者拥有百万千万粉丝的公众大号,给全部粉丝推送消息的场景。本文讲讲个人的优化分页查询的经验,抛砖引玉。

二、分析

在讲如何优化之前我们先来看看一个比较常见错误的写法

SELECT * FROM table

where kid=1342 and type=1 order id asc limit 149420,20;

该SQL是一个非常典型的排序+分页查询:

order by col limit N,OFFSET M

MySQL 执行此类SQL时需要先扫描到N行,然后再去取 M行。对于此类操作,取前面少数几行数据会很快,但是扫描的记录数越多,SQL的性能就会越差,因为N越大,MySQL需要扫描越多的数据来定位到具体的N行,这样耗费大量的IO 成本和时间成本。一图胜千言,我们使用简单的图来解释为什么 上面的sql 的写法扫描数据会慢。

t 表是一个索引组织表,key idx_kid_type(kid,type) 。

b52d9c45cef19f39cc20237a206ccaf9.png

符合kid=3 and type=1 的记录有很多行,我们取第 9,10行。

select * from t where kid =3 and type=1 order by id desc 8,2;

MySQL 是如何执行上面的sql 的?对于Innodb表,系统是根据 idx_kid_type 二级索引里面包含的主键去查找对应的行。对于百万千万级别的记录而言,索引大小可能和数据大小相差无几,cache在内存中的索引数量有限,而且二级索引和数据叶子节点不在同一个物理块儿上存储,二级索引与主键的相对无序映射关系,也会带来大量的随机IO请求,N值越大越需要遍历大量索引页和数据叶,需要耗费的时间就越久。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值