MySQL深分页,limit 100000,10优化方式

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='账户表';

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

这个SQL的执行时间如下：

执行完需要0.742秒，深分页为什么会变慢呢？如果换成 limit 0,10，只需要0.006秒哦

我们先来看下这个SQL的执行流程：

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

（每一条select语句都会从1遍历至当前位置，若跳转到第10000页，则会遍历100000条记录）

执行计划如下：

SQL变慢原因有两个：

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

二、优化方案

2.1 通过子查询优化（覆盖索引）

因为以上的SQL，回表了100010次，实际上，我们只需要10条数据，也就是我们只需要10次回表其实就够了。因此，我们可以通过减少回表次数来优化。

回顾B+树结构

如何减少回表次数呢？我们先来复习下B+树索引结构

InnoDB中，索引分主键索引（聚簇索引）和二级索引

• 主键索引，叶子节点存放的是整行数据
• 二级索引，叶子节点存放的是主键的值。

覆盖索引

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

如何确定数据库成功使用了覆盖索引呢？ —— 当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息

可以看到Extra中的Using index，表明我们成功使用了覆盖索引

把条件转移到主键索引树

如果我们把查询条件，转移回到主键索引树，那就不就可以减少回表次数啦。转移到主键索引树查询的话，查询条件得改为主键id了，之前SQL的update_time这些条件咋办呢？抽到子查询那里嘛~

子查询那里怎么抽的呢？因为二级索引叶子节点是有主键ID的，所以我们直接根据update_time来查主键ID即可，同时我们把 limit 100000的条件，也转移到子查询，完整SQL如下：