MySQL如何破解limit 100w+的分页查询

ゝ一世哀愁。 2022-09-06 01:28 194阅读 0赞

一、问题背景

我们在业务开发的时候，经常会遇到table列表的需求，这也是最基本的需求之一。大多数都是根据输入条件查询对应数据，然后对数据进行分页显示。数据量小的时候基本没啥问题，但是如果数据量在千万级别以上，这个时候limit就非常慢了。

![Image][]

二、实验数据

我们以一个大概4亿的表来进行测试，实验表数据如下所示：

select count(1)
    from order_info
    
    数据量：441182739

MYSQL：

配置：64核256GB
    版本：MySQL 5.7

三、优化前

我们先不进行任何优化处理，直接采用最原始的limit方式查询，如下SQL：

select type
    from order_info
    where user_id = 17898735496
    limit 2000000,10

执行结果：

> OK
    > 时间: 42.698s

执行结果表明，虽然查询很简单，但是因为limit翻页数量太大，导致这个SQL查询时间非常慢。那有什么办法可以优化呢？

四、优化后

方案其实有很多，今天就给大家介绍最简单实用的一种：我们可以先查询id主键，然后通过in条件查询出分页所有数据。虽然一条SQL变成2条，但是执行时间却大大减少，我们接着看一下实验结果是怎么样的。

1.先查询主键id

select id
    from order_info
    where user_id = 17898735496
    limit 2000000,10

执行结果：

> OK
    > 时间: 0.607s

2.通过主键id查询table数据

select type
    from order_info
    where id in (2066774275,2067155197,2067217323
    ,2064637941,2062897537,2058347040,
    2057876987,2064711964,2067155181,2063957963)

执行结果

> OK
    > 时间: 0.037s

> 但是90%以上的table查询都不会涉及到，这么大数据的limit查询，所以我们可以在程序上面做一下处理。如果limit index小于1w，就直接查询所有的数据，如果limit index大于等于1w，就采用先查询id，后in条件查询所有数据。当然不一定是1w，这个index的大小主要取决于，你要查询的表的大小，要根据实际情况来设置这个值。

五、知识扩展

作为面试官,我最喜欢问这种实际应用开发问题了。很多面试者会回答采用id>定值，然后直接获取对应个数的数据。例如：

select type
    from order_info
    where id > 10000
    limit 10

但是这样有个前提就是，id需要是自增长，其次是需要是顺序查询，不能够进行跳页查询，而且还不能有按字段排查的情况，限制非常多。

这种方式最常见的应用场景：不需要任何排序，一页页的获取数据，直到获取完毕。

例如我需要获取一个用户一年内所有的订单数据，这个时候就可以采用这种方式了。通过每次请求都返回一个nextToken，然后下一次请求带上这个nextToken，这个nextToken其实就是上面对应的index坐标。

六、结论：

我们可以很明显的看到，通过只查询id的方式，可以快速查询出所有的id主键，因为MYSQL对查询主键是有进行特殊优化的，可以直接走主键索引，不需要回表操作。第二步根据id查询数据，那就更快了，基本上秒出来。

当然真实生产中，我们还需要根据实际业务适配对应逻辑，就比如：如果99%的分页不会到1w以上，那基本不会发生这种慢SQL了。

> 通过这个简简单单的优化，瞬间就可以提高10倍以上是性能，这么6的方法你学会了嘛。

**-----------------------**

**公众号：**林老师带你学编程

**网站：****www.wolzq.com**

代码无非增删改查，关注老师给你Coding

![图片][0715eb9d950e28e2ed8775addb9af017.png]

**林老师带你学编程**：[http://www.wolzq.com][http_www.wolzq.com]

[Image]: /images/20220829/62abbbe92eaa4bd28839165644012202.png
[0715eb9d950e28e2ed8775addb9af017.png]: /images/20220829/74999b72d2ab43cc9fc1b15b8dfa5ad4.png
[http_www.wolzq.com]: https://wolzq.com/