ToB企服应用市场:ToB评测及商务社交产业平台

标题: 查询效率提升10倍！3种优化方案，帮你解决MySQL深分页问题 [打印本页]
作者: 海哥    时间: 2022-8-22 03:11
标题: 查询效率提升10倍！3种优化方案，帮你解决MySQL深分页问题
开发经常遇到分页查询的需求，但是当翻页过多的时候，就会产生深分页，导致查询效率急剧下降。
有没有什么办法，能解决深分页的问题呢？
本文总结了三种优化方案，查询效率直接提升10倍，一起学习一下。
1. 准备数据

先创建一张用户表，只在create_time字段上加索引：
  1. CREATE TABLE `user` (
  2.   `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  3.   `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  4.   `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  5.   PRIMARY KEY (`id`),
  6.   KEY `idx_create_time` (`create_time`)
  7. ) ENGINE=InnoDB COMMENT='用户表';
复制代码
然后往用户表中插入100万条测试数据，这里可以使用存储过程：
  1. drop PROCEDURE IF EXISTS insertData;
  2. DELIMITER $$
  3. create procedure insertData()
  4. begin
  5. declare i int default 1;
  6.    while i <= 100000 do
  7.          INSERT into user (name,create_time) VALUES (CONCAT("name",i), now());
  8.          set i = i + 1;
  9.    end while;
  10. end $$
  12. call insertData() $$
复制代码

在不到0.01秒内直接返回了，所以没显示出执行时间。
当我们翻到第10000页的时候，查询效率急剧下降：
  1. select * from user
  2. where create_time>'2022-07-03'
  3. limit 0,10;
复制代码

执行时间变成了0.16秒，性能至少下降了几十倍。
耗时主要花在哪里了？
画一下回表查询流程：
1. 先通过create_time查询出主键ID

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

别问为什么B+树的结构是这样的？问就是规定。
可以看一下前两篇文章。
然后我们就针对这两个耗时原因进行优化。
3. 优化查询

3.1 使用子查询

先用子查询查出符合条件的主键，再用主键ID做条件查出所有字段。
  1. select * from user
  2. where create_time>'2022-07-03'
  3. limit 100000,10;
复制代码
不过这样查询会报错，说是子查询中不支持使用limit。

我们加一层子查询嵌套，就可以了：
  1. select * from user
  2. where id in (
  3.   select id from user
  4.   where create_time>'2022-07-03'
  5.   limit 100000,10
  6. );
复制代码

执行时间缩短到0.05秒，减少了0.12秒，相当于查询性能提升了3倍。
为什么先用子查询查出符合条件的主键ID，就能缩短查询时间呢？
我们用explain查看一下执行计划就明白了：
  1. explain select * from user
  2. where id in (
  3.   select id from user
  4.   where create_time>'2022-07-03'
  5.   limit 100000,10
  6. );
复制代码

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

把子查询的结果当成一张临时表，然后和原表进行关联查询。
  1. explain select * from user
  2. where id in (
  3. select id from (
  4.     select id from user
  5.     where create_time>'2022-07-03'
  6.     limit 100000,10
  7. ) as t
  8. );
复制代码

查询性能跟使用子查询一样。
3.3 使用分页游标（推荐）

实现方式就是：当我们查询第二页的时候，把第一页的查询结果放到第二页的查询条件中。
例如：首先查询第一页
  1. select * from user
  2. inner join (
  3.    select id from user
  4.     where create_time>'2022-07-03'
  5.     limit 100000,10
  6. ) as t on user.id=t.id;
复制代码
然后查询第二页，把第一页的查询结果放到第二页查询条件中：
  1. select * from user
  2. where create_time>'2022-07-03'
  3. limit 10;
复制代码
这样相当于每次都是查询第一页，也就不存在深分页的问题了，推荐使用。

执行耗时是0秒，查询性能直接提升了几十倍。
这样的查询方式虽然好用，但是又带来一个问题，就是跳转到指定页数，只能一页页向下翻。
所以这种查询只适合特定场景，比如资讯类APP的首页。
互联网APP一般采用瀑布流的形式，比如百度首页、头条首页，都是一直向下滑动翻页，并没有跳转到制定页数的需求。
不信的话，可以看一下，这是头条的瀑布流：

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

响应数据中，返回了下一页查询条件。
所以这种查询方式的应用场景还是挺广的，赶快用起来吧。
知识点总结：


文章持续更新，可以微信搜一搜「 一灯架构 」第一时间阅读更多技术干货。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4