如何优化MySQL深分页

　　背景
　　我们日常做分页需求时，一般会用limit实现，但是当偏移量特别大的时候，查询效率就变得低下，接下来让我们一起了解mysql的查询过程。 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_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> ＂2021-09-19＂ limit 100000,10;
　　这个SQL的执行时间如下：
　　执行完需要   0.742   秒，深分页为什么会   变慢   呢？如果换成  limit 0,10 ，只需要   0.006   秒哦
　　我们先来看下这个SQL的执行流程： 通过 普通二级索引树 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扫描更多的行数，也意味着 回表 更多的次数。 通过子查询优化
　　因为以上的SQL，回表了100010次，实际上，我们只需要10条数据，也就是我们只需要10次回表其实就够了。因此，我们可以通过 减少回表次数 来优化。 回顾B+ 树结构
　　那么，如何减少回表次数呢？我们先来复习下B+树索引结构哈~
　　InnoDB中，索引分主键索引（聚簇索引）和二级索引 主键索引，叶子节点存放的是整行数据 二级索引，叶子节点存放的是 主键的值 。
　　把条件转移到主键索引树
　　如果我们把查询条件，转移回到主键索引树，那就可以减少回表次数啦。转移到主键索引树查询的话，查询条件得改为主键id了，之前SQL的update_time这些条件咋办呢？抽到 子查询 那里嘛~
　　子查询那里怎么抽的呢？因为二级索引叶子节点是有主键ID的，所以我们直接根据update_time来查主键ID即可，同时我们把limit 100000的条件，也转移到子查询，完整SQL如下： select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= ＂2020-09-19＂ limit 100000, 1) LIMIT 10;
　　查询效果一样的，执行时间只需要0.038秒！
　　我们来看下执行计划
　　由执行计划得知，子查询 table a查询是用到了idx_update_time索引。首先在索引上拿到了聚集索引的主键ID,省去了回表操作，然后第二查询直接根据第一个查询的 ID往后再去查10个就可以了!
　　通过上面的案例我们可以看出，减少回表操作，可以大幅度提升查询效率 INNER JOIN 延迟关联
　　延迟关联的优化思路， 跟子查询的优化思路其实是一样的 ：都是把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用了inner join代替子查询。
　　优化后的SQL如下： 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;
　　查询效果也是杠杆的，只需要0.034秒
　　执行计划如下：
　　查询思路就是，先通过 idx_update_time 二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。