深度分页

limit 200000, 10 到底会不会有问题呢？光说不练假把式，来，操作起来先！

mysql> CREATE TABLE `order_detail` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` varchar(50) NOT NULL DEFAULT '' COMMENT '用户ID',
  `order_id` bigint(20) DEFAULT NULL COMMENT '订单id',
  `sku_id` bigint(20) unsigned NOT NULL COMMENT '商品ID',
  `order_time` datetime DEFAULT NULL COMMENT '下单时间,格式yyyy-MM-dd HH:mm:ss',
  PRIMARY KEY (`id`),
  KEY `idx_time_user` (`order_time`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单详情';

执行计划Extra列可能出现的值

Using where：优化器需要通过索引回表查询
Using Index：覆盖索引，即根据索引树可直接检索列信息，无需额外的操作来读取实际的行
Using index condition：索引下推，在MySQL5.6后出现，充分利用了索引中的数据,尽量在查询出整行数据之前过滤掉无效的数据
Backward index scan：对索引列使用了降序操作
Using index for group_by：只查索引列，对索引列使用了 group by
Using filesort：文件排序，一般在 order by 数据量多大时出现，MySQL会将所有数据召回内存中排序，比较消耗资源
using temporary：使用了临时表，一般在使用group by、order by时会遇到

针对limit 100 和 limit 200000 分别执行查询及执行计划（mysql版本：8.0.26）：

limit优化前

结合上图，同样的语句，不同的偏移量，结果执行计划相差巨大。

limit 100, 10时，type列为range，表示范围扫描，性能比ref差一个级别，但是走了索引，使用了索引下推；
limit 200000, 10时，type列为all，做了全表扫描，Extra里Using where发生了回表，Using filesort表示order by时发生了文件排序。

这里limit 200000, 10由于limit偏移量过大，引起以下两个问题，进而影响查询性能：

文件排序耗时过大
根据条件筛选了相关的数据之后，需要根据偏移量回表获取全部值

对比上面两个sql的查询时间可知，分页深度越深，就越耗时。第2条sql，每次至少要查询9w多条数据，取其中的1条数据，其余的数据丢失掉，非常耗时，这就是深度分页的问题。

优化方案

针对主键自增型的分页查询优化

限制id法：取上页中最大id，然后根据最大id进行分页查询

mysql> select * from order_detail where id > 200000 limit 10;

优化后的sql执行耗时降低了很多，有明显的效率提升的。
这种方案的限制条件：

id单调性且无重复数据
不支持随机跳页

limit优化后

针对主键非自增型的分页查询优化

很多业务场景，不是通过主键id分页查询的，而是由多个组合条件构成分页查询条件，这种情况下我们可以通过延迟关联来处理。
延迟关联：通过子查询查询出当前页的id，然后再根据当前页id回表查询所需字段。查询当前页id用到了索引，遍历索引树还是很快的。该方案很好的避免了限制id法的使用条件，经常被用于后端的管理平台，来看个具体sql和执行计划：

mysql> select od.id,user_id,order_id,sku_id,order_time from order_detail as od inner join (select id FROM order_detail limit 200000, 10) as b on od.id = b.id;

从执行计划上能看出，子查询用到了覆盖索引，整个过程遍历索引还是很快的。

从业务角度规避深度分页

我们也可以换个角度，既然问题不好解决，那是不是也可以提前规避问题呢？
方法肯定是有的，技术上不好解决，那就通过业务来规避问题。比如，可以采用如下措施：

增加默认筛选条件，来减少展示的数据量。
滚动展示，避免跳页，例如：限制id法。

必应分页示例

如上图，必应搜索就默认展示5页，屏蔽掉了跳页功能（虽然底层实现不同，但是逻辑类似）。

最后编辑于：2022.11.18 12:56:49