MySQL在分页查询时的limit深分页问题

在平时业务中我们会发现当分页数据特别大的时候，会出现SQL很慢的情况，下面我们来分析下为什么会出现这种情况以及如何去解决

一、limit深分页问题解析

我们有如下一张表

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_create_time (create_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

当我们进行一下分页查询时

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

SQL的执行流程大致如下：

1、首先通过非族簇索引(idx_create_time )，过滤条件，查询出符合要求数据的主键id。

2、在通过主键Id索引，通过Id主键索引树。找到满足记录的行，然后取出需要展示的列（此过程发生了回表）

3、扫描到满足条件的100010行，然后扔掉前100000行，返回。

从以上流程我们就能理解为什么limit深分页问题了。这里主要2个流程导致查询慢

1、发生了回表查询，我们查询的数据越多，那么需要回表查询的次数也就也多。

2、limit 100000,10;在执行时，limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。

二、limit深分页优化方案

我们可以通过减少回表次数来优化，一般有种方法：标签记录法和延迟关联法

 1、标签记录法

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

实现方案呢就是记录我们上次查询到的记录id，在这次查询时把id作为查询条件带入

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

这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。

2、延迟关联法

延迟关联法，就是把条件转移到主键索引树，然后减少回表。如下：

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

优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。