mysql分页查询limit中偏移量offset过大导致性能问题

痛定思痛。 2021-06-26 16:06 669阅读 0赞

在业务中经常会遇到关于分页的需求，这就会经常会用到`MySQL`中的`limit offset`，`rows`来分段取出每页中需要的数据。但是当数据量足够大的时候，`limit`条件中的偏移量`offset`越大就越会导致性能问题，导致查询耗时增加严重。先看一下测试：

-- 本地库中只有二百多万条数据，访者可以在本地将数据写入到千万条做测试。效果会更明显
        select count(*) from notes;

![在这里插入图片描述][201906042320044.png]

-- 发送不同级别偏移量的查询
        set profiling=1;
        select * from notes limit 100,3;
        select * from notes limit 10000,3;
        select * from notes limit 1000000,3;
        select * from notes limit 10000000,3;

![在这里插入图片描述][20190605124447556.png]

-- 查看几条查询的耗时情况
        show profiles

![在这里插入图片描述][20190605124549883.png]

-- 单独查看偏移量为一百万和一千万的查询
        show profile for query 3;

![在这里插入图片描述][20190605124654513.png]  
![在这里插入图片描述][20190605125008237.png]  
从人正常的逻辑去考虑`limit` 偏移量去取数据的话大家可能会考虑到从`100`开始取，取三条数据，那我就直接跳过前一百条数据，然后取出后面三条数据。但实际上`MySQL`并不是这么做的，查看上面两图中可以看到当偏移量增大时，`Sending data`是耗时的主要原因，这是因为`MySQL`并没有那么智能，并非是跳过偏移量取后面的数据而是先把偏移量+要取得条数，然后再把偏移量这一段的数据抛弃掉再返回。关于优化的解决方案的话大致可以分为几条：

1> 在业务允许的情况下限制页数：

可以看看百度和谷歌的做法，百度搜索出来的结果集的话最大的分页数是`76`页，可以想到`76`页已经是一个足够大的页数了，哪里会有人在那里一直翻页翻到`76`页，而谷歌更狠，我所查到的谷歌搜索结果集最大页数是`28`页，而且谷歌搜索出来的页数和百度不一样的是谷歌并非将页数定死的。  
![在这里插入图片描述][20190605125058569.png]  
![在这里插入图片描述][20190605125114341.png]

2> 在大的数据量面前使用`where id>offset` 来代替使用`limit offset`：

-- 同样的效果
        select * from notes limit 1000000,3;
         
        select * from notes where id>1000000 limit 3;

就像上图两条语句同样的效果但是第二条能提升不小的效率，但是这却会导致一个问题就是，如果选择这样去优化`limit offset`，其前提必须是主键`id`必须是连续的，可以在表内写入标记为做逻辑删除，不做物理删除。

3> 假如说如果真的要物理删除，那解决方法的话就只能先取出前`offset`条数据的`ID`，再去做偏移取数据。

[201906042320044.png]: /images/20210626/dd7cfbc8da6344629db6148a891d701f.png
[20190605124447556.png]: /images/20210626/ad249e8e7b944ec892e210be08f1c203.png
[20190605124549883.png]: /images/20210626/c4f3b60ef51648b696a510a0c4e8a0cf.png
[20190605124654513.png]: /images/20210626/b04ad0c53ca14d66883fa263e2d925e4.png
[20190605125008237.png]: /images/20210626/893ab42e02134ad687cb817d10ca0486.png
[20190605125058569.png]: /images/20210626/0a635a11a4de4ee3a71d6bca45e76962.png
[20190605125114341.png]: /images/20210626/44b7e07a09014b87831548edee7e372c.png