MYSQL 深度分页问题的解决思路

深度分页问题描述

分页语法

LIMIT row_count; 返回前 row_count 条数据

LIMIT offset, row_count; 返回 从 offset 行开始，接下来的 row_count 行数据

LIMIT row_count OFFSET offset; 返回 从 offset 行开始，接下来的 row_count 行数据

为什么深度分页会变慢？

设置一个表结构

CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  name varchar(255) DEFAULT NULL COMMENT '账户名',
  balance int(11) DEFAULT NULL COMMENT '余额',
  create_time datetime NOT NULL COMMENT '创建时间',
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id),
  KEY idx_name (name),
  KEY idx_update_time (update_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

假设深度分页的执行SQL如下

select id,name,balance from account where update_time >= '2020-09-19' limit 100000,10;

执行流程：

通过**普通二级索引树 **idx_update_time，过滤 update_time 条件，找到满足条件的记录ID。
通过ID，回到主键索引树，找到满足记录的行，然后取出展示的列（回表）
扫描满足条件的100010行，然后扔掉前100000行，返回。

SQL变慢原因有两个：

limit 语句会先扫描 offset+n 行，然后再丢弃掉前 offset 行，返回后 n 行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。
limit 100000,10 扫描更多的行数，也意味着回表更多的次数。

笨笨的mysql不知道查询到的数据是第多少行的，所以只能笨笨的查询出 offset + n 条数据，然后丢掉前面的数据。

深度分页变慢的根本原因还是进行了多余的查询，那么优化深度分页的思路就是尽量减少多余的查询~

常见的解决办法

通过子查询优化

select id,name,balance FROM account where id in (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 10)

可以使用子查询来减少回表的次数，之前的普通查询，之所以会全表扫描，是因为没有用到索引，可以使用子查询先查出数据对应的主键索引 id 范围，在直接使用主键索引去查询，相当于 将查询条件转移到了主键索引上

优化对比：

全表扫描 + 回表 -> 子查询全表扫描 + 主查询走主键索引高效扫描，避免回表

节约下来的时间就是回表的时间

使用 INNER JOIN 延迟关联

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

本质上和子查询优化是相似的。通过连接主键避免过多的回表行为

优化对比:

全表扫描 + 回表 -> 驱动表全表扫描 + 被驱动表走主键索引高效扫描，避免回表

标签记录法

所谓标签记录法，其实就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。

假设上一次分页得到的数据集合的最后一条数据的主键是 100000

那么 SQL 可以修改为：

select  id,name,balance FROM account where id > 100000 order by id limit 10;

这种方式有局限性：需要一种类似连续自增的字段。

使用 BETWEEN AND

很多时候，可以将limit查询转换为已知位置的查询，这样MySQL通过范围扫描between...and，就能获得到对应的结果。

如果知道边界值为100000，100010后，就可以这样优化：

select  id,name,balance FROM account where id between 100000 and 100010 order by id desc;