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

一、问题背景

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

二、实验数据

我们以一个大概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

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