阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL分页查询慢的原因及解决方案

215次阅读
没有评论

共计 915 个字符,预计需要花费 3 分钟才能阅读完成。

一、背景
我们在开发的过程中使用分页是不可避免的,通常情况下我们的做法是使用 limit 加偏移量:select * from table where column=xxx order by xxx limit 1,20。当数据量比较小时(100 万以内),无论你翻到哪一页,性能都是很快的。如果查询慢,只要在 where 条件和 order by 的列上加上索引就可以解决。但是,当数据量大的时候(小编遇到的情况是 500 万数据),如果翻到最后几页,即使加了索引,查询也是非常慢的,这是什么原因导致的呢?我们该如何解决呢?

二、limit 分页原理
当我们翻到最后几页时,查询的 sql 通常是:select * from table where column=xxx order by xxx limit 1000000,20。查询非常慢。但是我们查看前几页的时候,速度并不慢。这是因为 limit 的偏移量太大导致的。MySQL 使用 limit 时的原理是(用上面的例子举例):

MySQL 将查询出 1000020 条记录。
然后舍掉前面的 1000000 条记录。
返回剩下的 20 条记录。
上述的过程是在《高性能 MySQL》书中确认的。

三、解决方案
解决的方法就是尽量使用索引覆盖扫描,就是我们 select 后面检出的是索引列,而不是所有的列,而且这个索引的列最好是 id。然后再做一次关联查询返回所有的列。上述的 sql 可以写成:

SELECT
    *
FROM
    table t
INNER JOIN (
    SELECT
        id
    FROM
        table
    WHERE
        xxx_id = 143381
    LIMIT 800000,20
) t1 ON t.id = t1.id

我们在 mysql 中做的真实的实验:

MySQL 分页查询慢的原因及解决方案

上图是没有优化过的 sql,执行时间为 2s 多。经过优化后如下:

MySQL 分页查询慢的原因及解决方案

执行时间为 0.3s,性能有了大幅度的提升。虽然做了优化,但是随着偏移量的增加,性能也会随着下降,MySql 官方虽然也给出了
其他的解决方案,但是在实际开发中很难使用。

有的同学可能会问,能不能使用 IN 嵌套子查询,而不使用 INNER JOIN 的方式,答案是不可以,因为 MySql 在子查询中不能使用 LIMIT。

MySQL 分页优化就先介绍到这里了。

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计915字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中