【无标题】

MySQL 深分页问题

MySQL 深分页问题

日常业务开发时，难免会遇到分页问题，按传统方式都是用limit来操作。当翻页过多的时候，就不得不面的深分页问题，导致查询性能急剧下降。

limit 深分页为什么会变慢

• 先准备一张实验用表

CREATE TABLE `tbl_user` (
  `id` bigint 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='用户表';

然后向数据表中插入100w条记录；当我们查询第一页的时候，速度很快，不到0.01秒就返回了

select * from tbl_user 
where create_time>'2022-07-04' 
limit 0,10;

当如果要翻到底10000页（每页10条）的时候，查询性能急剧下降，耗时0.16s

select * from tbl_user 
where create_time>'2022-07-04' 
limit 10000,10;

耗时问题分析

那么耗时主要花在哪里了？

1. 通过普通二级索引树idx_create_time，过滤create_time条件，找到满足条件的记录ID。
2. create_time是非聚簇索引，需要先查询出主键ID，再回表查询，通过主键ID查询出所有字段
3. 扫描满足条件的100010行，然后扔掉前100000行，返回。

索引回表流程

优化方案

子查询优化

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

select * from tbl_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，表示用到了覆盖索引，所以子查询无需回表查询，加快了查询效率。

使用分页游标

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

例如：首先查询第一页

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

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

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

这样相当于每次都是查询第一页，也就不存在深分页的问题了，这种场景比较适合于没有调整到第XX页的场景上。例如APP页面滚动的场景上，互联网APP一般采用瀑布流的形式，比如百度首页、头条首页，都是一直向下滑动翻页，并没有跳转到制定页数的需求。

INNER JOIN 延迟关联

延迟关联的优化思路，跟子查询的优化思路其实是一样的：都是把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用了inner join代替子查询。

SELECT  user1 .id,user1 .name FROM tbl_user user1 INNER JOIN (SELECT a.id FROM tbl_user a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 10) AS  user2 on user2 .id= user2 .id;