开发常常遇到分页查问的需要，然而当翻页过多的时候，就会产生深分页，导致查问效率急剧下降。

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

本文总结了三种优化计划，查问效率间接晋升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;

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

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

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

执行工夫变成了0.16秒，性能至多降落了几十倍。

耗时次要花在哪里了？

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

画一下回表查问流程：

1. 先通过create_time查问出主键ID

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

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

能够看一下前两篇文章。

能够看一下前两篇文章。

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

一篇文章讲清楚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。

咱们加一层子查问嵌套，就能够了：

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

执行工夫缩短到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);

能够看到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;

查问性能跟应用子查问一样。

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;

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

执行耗时是0秒，查问性能间接晋升了几十倍。

这样的查问形式尽管好用，然而又带来一个问题，就是无奈跳转到指定页数，只能一页页向下翻。

所以这种查问只适宜特定场景，比方资讯类APP的首页。

互联网APP个别采纳瀑布流的模式，比方百度首页、头条首页，都是始终向下滑动翻页，并没有跳转到制订页数的需要。

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

传参中带了上一页的查问后果。

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

所以这种查问形式的利用场景还是挺广的，赶快用起来吧。

知识点总结：

文章继续更新，能够微信搜一搜「 一灯架构 」第一工夫浏览更多技术干货。