查询效率提升10倍！3种优化方案，帮你解决MySQL深分页问题

红太狼 2023-10-01 22:29 9阅读 0赞

开发经常遇到分页查询的需求，但是当翻页过多的时候，就会产生深分页，导致查询效率急剧下降。

有没有什么办法，能解决深分页的问题呢？

本文总结了三种优化方案，查询效率直接提升10倍，一起学习一下。

### 1. 准备数据 ###

先创建一张用户表，只在create\_time字段上加索引：

CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
      `name` varchar(255) DEFAULT NULL COMMENT '姓名',
      `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
      PRIMARY KEY (`id`),
      KEY `idx_create_time` (`create_time`)
    ) ENGINE=InnoDB COMMENT='用户表';

然后往用户表中插入100万条测试数据，这里可以使用存储过程：

drop PROCEDURE IF EXISTS insertData;
    DELIMITER $$
    create procedure insertData()
    begin
     declare i int default 1;
       while i <= 100000 do
             INSERT into user (name,create_time) VALUES (CONCAT("name",i), now());
             set i = i + 1; 
       end while; 
    end $$
    
    call insertData() $$

### 2. 验证深分页问题 ###

每页10条，当我们查询第一页的时候，速度很快：

select * from user 
    where create_time>'2022-07-03' 
    limit 0,10;

![format_png][]

在不到0.01秒内直接返回了，所以没显示出执行时间。

当我们翻到第10000页的时候，查询效率急剧下降：

select * from user 
    where create_time>'2022-07-03' 
    limit 100000,10;

![format_png 1][]

执行时间变成了0.16秒，性能至少下降了几十倍。

耗时主要花在哪里了？

1.  需要扫描前10条数据，数据量较大，比较耗时
2.  create\_time是非聚簇索引，需要先查询出主键ID，再回表查询，通过主键ID查询出所有字段

画一下回表查询流程：

**1. 先通过create\_time查询出主键ID**

![format_png 2][]

**2. 再通过主键ID查询出表中所有字段**

![format_png 3][]

别问为什么B+树的结构是这样的？问就是规定。

可以看一下前两篇文章。

[MySQL索引底层实现为什么要用B+树？][MySQL_B]

[一篇文章讲清楚MySQL的聚簇/联合/覆盖索引、回表、索引下推][MySQL]

然后我们就针对这两个耗时原因进行优化。

### 3. 优化查询 ###

#### 3.1 使用子查询 ####

先用子查询查出符合条件的主键，再用主键ID做条件查出所有字段。

select * from user 
    where id in (
      select id from user 
      where create_time>'2022-07-03' 
      limit 100000,10
    );

不过这样查询会报错，说是子查询中不支持使用limit。

![format_png 4][]

我们加一层子查询嵌套，就可以了：

select * from user 
    where id in (
     select id from (
        select id from user 
        where create_time>'2022-07-03' 
        limit 100000,10
     ) as t
    );

![format_png 5][]

执行时间缩短到0.05秒，减少了0.12秒，相当于查询性能提升了3倍。

为什么先用子查询查出符合条件的主键ID，就能缩短查询时间呢？

我们用explain查看一下执行计划就明白了：

explain select * from user 
    where id in (
     select id from (
        select id from user 
        where create_time>'2022-07-03' 
        limit 100000,10
     ) as t
    );

![format_png 6][]

可以看到Extra列显示子查询中用到**Using index**，表示用到了**覆盖索引**，所以子查询无需回表查询，加快了查询效率。

#### 3.2 使用inner join关联查询 ####

把子查询的结果当成一张临时表，然后和原表进行关联查询。

select * from user 
    inner join (
       select id from user 
        where create_time>'2022-07-03' 
        limit 100000,10
    ) as t on user.id=t.id;

![format_png 7][]

查询性能跟使用子查询一样。

#### 3.3 使用分页游标（推荐） ####

实现方式就是：当我们查询第二页的时候，把第一页的查询结果放到第二页的查询条件中。

例如：首先查询第一页

select * from user 
    where create_time>'2022-07-03' 
    limit 10;

然后查询第二页，把第一页的查询结果放到第二页查询条件中：

select * from user 
    where create_time>'2022-07-03' and id>10 
    limit 10;

这样相当于每次都是查询第一页，也就不存在深分页的问题了，推荐使用。

![format_png 8][]

执行耗时是0秒，查询性能直接提升了几十倍。

这样的查询方式虽然好用，但是又带来一个问题，就是无法跳转到指定页数，只能一页页向下翻。

所以这种查询只适合特定场景，比如资讯类APP的首页。

互联网APP一般采用瀑布流的形式，比如百度首页、头条首页，都是一直向下滑动翻页，并没有跳转到制定页数的需求。

不信的话，可以看一下，这是头条的瀑布流：

![format_png 9][]

传参中带了上一页的查询结果。

![format_png 10][]

响应数据中，返回了下一页查询条件。

所以这种查询方式的应用场景还是挺广的，赶快用起来吧。

### 知识点总结： ###

![format_png 11][]

![17f6cb3804f64cdf90e093414871e6e2.jpeg][]

[format_png]: https://img-blog.csdnimg.cn/img_convert/58add6d1df39d0a553441253d222bd65.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/f10b920ac8adb859de5d9b015d8339c1.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/718211b0a4dffcda528b22c6c9e16277.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/683005f09ae86cf49896f26dd05aa22d.webp?x-oss-process=image/format,png
[MySQL_B]: https://link.juejin.cn?target=https%3A%2F%2Fmp.weixin.qq.com%2Fs%3F__biz%3DMzg3NjU3OTQyMA%3D%3D%26mid%3D2247483727%26idx%3D1%26sn%3Df96d05d1f268dbe8f3b892f87adb99ae%26chksm%3Dcf315e7cf846d76aab89264c3cbc3eb5bdee993a9509e422d40eac40ecaf22a3fad8c1e1c7de%26token%3D138873163%26lang%3Dzh_CN%23rd
[MySQL]: https://link.juejin.cn?target=https%3A%2F%2Fmp.weixin.qq.com%2Fs%3F__biz%3DMzg3NjU3OTQyMA%3D%3D%26mid%3D2247483750%26idx%3D1%26sn%3D006f399e2cd868e3bbc1ab7e0cd4e701%26chksm%3Dcf315e55f846d743bf1839f6f27aafac88c2d611adbaff4fb8831922f8be70a27decd85749a5%26token%3D138873163%26lang%3Dzh_CN%23rd
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/60f4bd2cc9a19a85d154eabe6432fa38.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/989b7ea9fb1a251a0add5d64aad012f2.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/36c1537d4586225046387f399561fdfd.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/b5364dc3301213aa2416117bb9ab66ec.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/8d6af618c87dc17789fb9ec92e03894d.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/be22fb906c4b44ca8073dc8090cc55c4.webp?x-oss-process=image/format,png
[format_png 10]: https://img-blog.csdnimg.cn/img_convert/e45ac6f826232f00de6daa965331bfff.webp?x-oss-process=image/format,png
[format_png 11]: https://img-blog.csdnimg.cn/img_convert/38a4a42818a2962e48508208673c7241.webp?x-oss-process=image/format,png
[17f6cb3804f64cdf90e093414871e6e2.jpeg]: https://img-blog.csdnimg.cn/17f6cb3804f64cdf90e093414871e6e2.jpeg