共计 2259 个字符,预计需要花费 6 分钟才能阅读完成。
开发常常遇到分页查问的需要,然而当翻页过多的时候,就会产生深分页,导致查问效率急剧下降。
有没有什么方法,能解决深分页的问题呢?
本文总结了三种优化计划,查问效率间接晋升 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 秒,性能至多降落了几十倍。
耗时次要花在哪里了?
- 须要扫描前 10 条数据,数据量较大,比拟耗时
- 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 个别采纳瀑布流的模式,比方百度首页、头条首页,都是始终向下滑动翻页,并没有跳转到制订页数的需要。
不信的话,能够看一下,这是头条的瀑布流:
传参中带了上一页的查问后果。
响应数据中,返回了下一页查问条件。
所以这种查问形式的利用场景还是挺广的,赶快用起来吧。
知识点总结:
文章继续更新,能够微信搜一搜「一灯架构」第一工夫浏览更多技术干货。