乐趣区

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

jiezi

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

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

本文总结了三种优化计划，查问效率间接晋升 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 个别采纳瀑布流的模式，比方百度首页、头条首页，都是始终向下滑动翻页，并没有跳转到制订页数的需要。

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

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

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

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

知识点总结：

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

退出移动版