MySQL大表分页查询优化技巧

超、凢脫俗 2023-10-03 22:34 25阅读 0赞

### 一、问题复现 ###

在实际的软件系统开发过程中，随着使用的用户群体越来越多，表数据也会随着时间的推移，单表的数据量会越来越大。

以订单表为例，假如每天的订单量在 4 万左右，那么一个月的订单量就是 120 多万，一年就是 1400 多万，随着年数的增加和单日下单量的增加，订单表的数据量会越来越庞大，**订单数据的查询不会像最初那样简单快速，如果查询关键字段没有走索引，会直接影响到用户体验，甚至会影响到服务是否能正常运行**！

下面我以某个电商系统的**客户表**为例，**数据库是 Mysql，数据体量在 100 万以上，详细介绍分页查询下，不同阶段的查询效率情况**（订单表的情况也是类似的，只不过它的数据体量比客户表更大）。

![e97a4af62702475d88ddaede2adf9c79.jpeg][]

下面我们一起来测试一下，每次查询客户表时最多返回 100 条数据，不同的起始下，数据库查询性能的差异。

*  **当起点位置在 0 的时候，仅耗时：18 ms**

![bbde877c91d644528ddddf59bfd28838.jpeg][]

*  **当起点位置在 1000 的时候，仅耗时：23 ms**

![37973ad74d0a440187378a61bc703671.jpeg][]

*  **当起点位置在 10000 的时候，仅耗时：54 ms**

![66f4b81a344d487bb5f27e99c00a9512.jpeg][]

*  **当起点位置在 100000 的时候，仅耗时：268 ms**

![629c797ff5104cb4923fe8bf1d31a89d.jpeg][]

*  **当起点位置在 500000 的时候，仅耗时：1.16 s**

![990cf74e0019413d8c65f2a9e41dc024.jpeg][]

*  **当起点位置在 1000000 的时候，仅耗时：2.35 s**

![b9bb8cfe26074d51a5fb4af1e0b8d7a7.jpeg][]

可以非常清晰的看出，随着起点位置越大，分页查询效率成倍的下降，当起点位置在 1000000 以上的时候，对于百万级数据体量的单表，查询耗时基本上以秒为单位。

而事实上，**一般查询耗时超过 1 秒的 SQL 都被称为慢 SQL**，有的公司运维组要求的可能更加严格，比如小编我所在的公司，如果 SQL 的执行耗时超过 0.2s，也被称为慢 SQL，**必须在限定的时间内尽快优化，不然可能会影响服务的正常运行和用户体验**。

对于千万级的单表数据查询，小编我刚刚也使用了一下分页查询，**起点位置在 10000000，也截图给大家看看，查询耗时结果：39 秒**！

![07be92f295f2454fbba47dc29b7980c9.jpeg][]

没有接触过这么大数据体量的同学，可能多少对这种查询结果会感到吃惊，事实上，这还只是数据库层面的耗时，还没有算后端服务的处理链路时间，以及返回给前端的数据渲染时间，以百万级的单表查询为例，如果数据库查询耗时 1 秒，再经过后端的数据封装处理，前端的数据渲染处理，以及网络传输时间，没有异常的情况下，差不多在 3～4 秒之间，可能有些同学对这个请求时长数值还不太敏感。

据互联网软件用户体验报告，当平均请求耗时在1秒之内，用户体验是最佳的，此时的软件也是用户留存度最高的；2 秒之内，还勉强过的去，用户能接受；当超过 3 秒，体验会稍差；超过 5 秒，基本上会卸载当前软件。

有的公司为了提升用户体验，会严格控制请求时长，当请求时长超过 3 秒，自动放弃请求，从而倒逼技术优化调整 SQL 语句查询逻辑，甚至调整后端整体架构，比如引入缓存中间件 redis，搜索引擎 elasticSearch 等等。

继续回到我们本文所需要探讨的问题，**当单表数据量到达百万级的时候，查询效率急剧下降，如何优化提升呢**？

### 二、解决方案 ###

> 下面我们一起来看看具体的解决办法。

#### 2.1、方案一：查询的时候，只返回主键 ID ####

我们继续回到上文给大家介绍的客户表查询，将`select *`改成`select id`，简化返回的字段，我们再来观察一下查询耗时。

*  **当起点位置在 100000 的时候，仅耗时：73 ms**

![453ee28304c84b2a872d2f4ce1570e32.jpeg][]

*  **当起点位置在 500000 的时候，仅耗时：274 ms**

![4c7d3346cda14f02a21ece4cea9a0a08.jpeg][]

*  **当起点位置在 1000000 的时候，仅耗时：471 ms**

![028fcac7150f4882846fafee7eb56fc8.jpeg][]

可以很清晰的看到，**通过简化返回的字段，可以很显著的成倍提升查询效率**。

实际的操作思路就是先通过分页查询满足条件的主键 ID，然后通过主键 ID 查询部分数据，可以显著提升查询效果。

-- 先分页查询满足条件的主键ID
    select id from bizuser order by id limit 100000,10;
    
    -- 再通过分页查询返回的ID，批量查询数据
    select * from bizuser where id in (1,2,3,4,.....);

#### 2.2、方案二：查询的时候，通过主键 ID 过滤 ####

这种方案有一个要求就是主键ID，**必须是数字类型，实践的思路就是取上一次查询结果的 ID 最大值，作为过滤条件，而且排序字段必须是主键 ID，不然分页排序顺序会错乱**。

*  **查询 100000～1000100 区间段的数据，仅耗时：18 ms**

![a63e66609c7045b9aa22ed512e5191a6.jpeg][]

*  **查询 500000～5000100 区间段的数据，仅耗时：18 ms**

![6ba9cf99932f4ef7be77735bd58d6f6e.jpeg][]

*  **查询 1000000～1000100 区间段的数据，仅耗时：18 ms**

![8b08769cceac4457bb920d7f540a50cf.jpeg][]

可以很清晰的看到，带上主键 ID 作为过滤条件，查询性能非常的稳定，基本上在`20 ms`内可以返回。

**这种方案还是非常可行的，如果当前业务对排序要求不多，可以采用这种方案，性能也非常杠**！

**但是如果当前业务对排序有要求，比如通过客户最后修改时间、客户最后下单时间、客户最后下单金额等字段来排序，那么上面介绍的【方案一】，比【方案二】查询效率更高**！

#### 2.3、方案三：采用 elasticSearch 作为搜索引擎 ####

当数据量越来越大的时候，尤其是出现分库分表的数据库，以上通过主键 ID 进行过滤查询，效果可能会不尽人意，例如订单数据的查询，**这个时候比较好的解决办法就是将订单数据存储到 elasticSearch 中**，通过 elasticSearch 实现快速分页和搜索，效果提升也是非常明显。

关于 elasticSearch 的玩法，以后有机会再给大家介绍。

### 三、小结 ###

不知道大家有没有发现，**上文中介绍的表主键 ID 都是数值类型的，之所以采用数字类型作为主键，是因为数字类型的字段能很好的进行排序**。

但如果当前表的主键 ID 是字符串类型，比如 uuid 这种，就没办法实现这种排序特性，而且搜索性能也非常差，因此不建议大家采用 uuid 作为主键ID，具体的数值类型主键 ID 的生成方案有很多种，比如自增、雪花算法等等，都能很好的满足我们的需求。

[e97a4af62702475d88ddaede2adf9c79.jpeg]: https://img-blog.csdnimg.cn/e97a4af62702475d88ddaede2adf9c79.jpeg
[bbde877c91d644528ddddf59bfd28838.jpeg]: https://img-blog.csdnimg.cn/bbde877c91d644528ddddf59bfd28838.jpeg
[37973ad74d0a440187378a61bc703671.jpeg]: https://img-blog.csdnimg.cn/37973ad74d0a440187378a61bc703671.jpeg
[66f4b81a344d487bb5f27e99c00a9512.jpeg]: https://img-blog.csdnimg.cn/66f4b81a344d487bb5f27e99c00a9512.jpeg
[629c797ff5104cb4923fe8bf1d31a89d.jpeg]: https://img-blog.csdnimg.cn/629c797ff5104cb4923fe8bf1d31a89d.jpeg
[990cf74e0019413d8c65f2a9e41dc024.jpeg]: https://img-blog.csdnimg.cn/990cf74e0019413d8c65f2a9e41dc024.jpeg
[b9bb8cfe26074d51a5fb4af1e0b8d7a7.jpeg]: https://img-blog.csdnimg.cn/b9bb8cfe26074d51a5fb4af1e0b8d7a7.jpeg
[07be92f295f2454fbba47dc29b7980c9.jpeg]: https://img-blog.csdnimg.cn/07be92f295f2454fbba47dc29b7980c9.jpeg
[453ee28304c84b2a872d2f4ce1570e32.jpeg]: https://img-blog.csdnimg.cn/453ee28304c84b2a872d2f4ce1570e32.jpeg
[4c7d3346cda14f02a21ece4cea9a0a08.jpeg]: https://img-blog.csdnimg.cn/4c7d3346cda14f02a21ece4cea9a0a08.jpeg
[028fcac7150f4882846fafee7eb56fc8.jpeg]: https://img-blog.csdnimg.cn/028fcac7150f4882846fafee7eb56fc8.jpeg
[a63e66609c7045b9aa22ed512e5191a6.jpeg]: https://img-blog.csdnimg.cn/a63e66609c7045b9aa22ed512e5191a6.jpeg
[6ba9cf99932f4ef7be77735bd58d6f6e.jpeg]: https://img-blog.csdnimg.cn/6ba9cf99932f4ef7be77735bd58d6f6e.jpeg
[8b08769cceac4457bb920d7f540a50cf.jpeg]: https://img-blog.csdnimg.cn/8b08769cceac4457bb920d7f540a50cf.jpeg