mysql 不用limit 分页_mysql sql优化之 limit 分页优化

在秦张良椎,在汉苏武节

LIMIT 分页性能问题

b4eb74b76f79

image.png

上图出自《高性能MYSQL》

问题sql: 普通limit

EXPLAIN SELECT * FROM emp ORDER BY id LIMIT 5000000,10

问题sql执行时间 8.231秒,存在性能问题。

业务上的优化

1、限制用户分页,不允许进行过大的分页。比如说百度的分页,它最多只能跳转到一定页数,用户再往下翻页也只显示最后一页。

2、禁用count(*),不去查询总条数,不计算总页数,不设计跳页的功能

sql层面优化方式

1、增加冗余的列 “页码” 来定位记录

对条件值可以进行估算,对于几百上千页的检索,往往不需要很精确。也可以专门增加冗余的列来定位记录,比如如下的 查询,有一个page列,指定记录所在的页,代价是在修改数据的时候需要维护这个列的数据,如下面的查询。

SELECT * FROM emp WHERE page = 100 ORDER BY name;

2、索引覆盖,延时关联

使用 索引覆盖,延时关联 的方式,将大大提高查询效率,它让mysql扫描尽可能少的页面,获取需要访问的记录后再根据关联列回原表查询需要所有的列。这个技术可以用于优化关联查询中的LIMIT字句。

1、我们可以使用 SELECT id FROM emp ORDER BY id LIMIT 5000000,10 充当子查询,这样就只在索引中查询id,而不用回表查;然后根据子查询得出来的id集合再去查对应的select * 数据即可;

EXPLAIN SELECT * FROM emp INNER JOIN (SELECT id FROM emp ORDER BY id LIMIT 5000000,10 )AS tb USING(id)

b4eb74b76f79

image.png

最后查询时间是 5.4 秒,效果在这里并不理想。但是索引覆盖,延时关联 的优化思想是非常好的!它不仅是可以用在这里。

3、还有注意一点: 能用 INNER JOIN 就不要使用IN。比如在这个limit优化中,我使用的是INNER JOIN 自关联查询。如果我使用IN呢?情况会如何?

EXPLAIN SELECT * FROM emp WHERE id IN ( SELECT id from ( SELECT id FROM emp ORDER BY id LIMIT 5000010, 10)tb )

首先,使用IN 的话最终的查询时间是15.7秒,是使用JOIN的三倍!再来看它的查询计划: 直接多了一个查询!

b4eb74b76f79

image.png

4、计算出边界值,直接 BETWEEN AND

可以将limit查询转换为已知位置的查询,让mysql通过范围扫描获得到对应的结果。

EXPLAIN SELECT * FROM emp WHERE id BETWEEN 5000001 AND 5000010 ORDER BY id

使用BETWEEN .. AND 最终的时间是 0.063秒,少去了大量的扫描操作;使用这种方式的前提是必须要知道id的边界值!同样id必须是连续单调递增的;如果之间有数据被删除掉,或者因为where条件过滤掉,那么最终得到的页数不会是10了吧;所以这种方式局限性太大了

5、得到上页最后一条记录的id,然后limit

假设查询返回的是 5000001 到 5000011 的记录,那么下一页查询就可以从这个5000011 开始拿10条数据。这样做无论翻多少也性能都会很好!

EXPLAIN SELECT * FROM emp WHERE id >= 5000001 ORDER BY id LIMIT 10

这种方式查询时间为 0.067。是limit优化的首选。不过它不能实现当前页(无法实现点击页码跳转),只能上一页,下一页的翻页。适合app端分页优化;它要求id是递增的(不能使用uuid等字符串充当id),但id可以不连续(可以使用where过滤、删除数据,都不会影响分页);我们来看看它的查询计划如下:

b4eb74b76f79

image.png

结合索引优化

最终是推荐使用第3个方法:得到上页最后一条记录的id,然后limit;但是若需要同时进行where条件检索、 order by排序的话就必须参加合适的联合索引了!

比如下面的语句,使用ename 进行like的模糊查询, 排序的时候指定id DESC,ename DESC

EXPLAIN SELECT

*

FROM

emp

WHERE

id >= 5000001

AND ename LIKE '%a%'

ORDER BY

id DESC,

ename DESC

LIMIT 10

那么先看看查询计划:出现了 Using filesort

b4eb74b76f79

image.png

然后执行下,执行时间为 4.6秒。需要优化!

所以,需要创建联合索引如下 index(id,ename)

SHOW INDEX FROM emp

b4eb74b76f79

image.png

最终查询时间为:0.089 秒,优化成功。再看看它的查询计划:

b4eb74b76f79

image.png

很好,已经没有 Using filesort 了

其他方法

1、在网上看到有人这样做

EXPLAIN SELECT

*

FROM

emp

WHERE

id >= ( SELECT id FROM emp ORDER BY id LIMIT 5000001, 1 )

LIMIT 10;

这个方法和上面讲的索引覆盖 是一样的,先从索引中找到id,然后通过id查数据。只不过上面是使用JOIN这里使用 >=。这样就必须让id是连续的了。最终查询时间是 4-5 秒。和索引覆盖差不多

b4eb74b76f79

image.png

2、使用Sphinx

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值