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

沐鼠儿

我们日常做分页需求时，一般会用limit实现，但是当偏移量特别大的时候，查询效率就变得低下。
本文将分4个方案，讨论如何优化MySQL百万数据的深分页问题.

一、limit深分页为什么会变慢

表结构

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

复制代码

执行的深分页SQL为

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

复制代码

这个SQL的执行时间如下：

执行完需要0.742秒，深分页为什么会变慢呢？如果换成

1. limit 0,10

复制代码

，只需要0.006秒哦

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

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

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

执行计划如下：

SQL变慢原因有两个：

• limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说
  1. limit 100000,10

  复制代码
  ，就会扫描100010行，而
  1. limit 0,10

  复制代码
  ，只扫描10行。
  
• 1. limit 100000,10

  复制代码
  扫描更多的行数，也意味着回表更多的次数。
  

二、优化方案


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

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

回顾B+树结构

如何减少回表次数呢？我们先来复习下B+树索引结构
InnoDB中，索引分主键索引（聚簇索引）和二级索引

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

覆盖索引

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。
如何确定数据库成功使用了覆盖索引呢？ —— 当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息

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

把条件转移到主键索引树

如果我们把查询条件，转移回到主键索引树，那就不就可以减少回表次数啦。转移到主键索引树查询的话，查询条件得改为

1. 主键id

复制代码

了，之前SQL的

1. update_time

复制代码

这些条件咋办呢？抽到子查询那里嘛~
子查询那里怎么抽的呢？因为二级索引叶子节点是有主键ID的，所以我们直接根据

1. update_time

复制代码

来查主键ID即可，同时我们把

1. limit 100000

复制代码

的条件，也转移到子查询，完整SQL如下：

1. 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秒！ 0.742秒 ——> 0.038秒

我们来看下执行计划

由执行计划得知，子查询 table a查询是用到了

1. idx_update_time

复制代码

索引。
首先在索引上拿到了聚集索引的主键ID，省去了回表操作，然后第二查询直接根据第一个查询的ID往后再去查10个就可以了!

所谓的覆盖索引就是从普通索引树中就能查到的想要数据，而不需要通过回表从主键索引中查询其他列，能够显著提升性能。
因此，这个方案是可以的~

2.2 INNER JOIN 延迟关联

延迟关联的优化思路，跟子查询的优化思路其实是一样的：都是把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用了inner join代替子查询。
优化后的SQL如下：

1. 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秒

执行计划如下：

查询思路就是，先通过

1. idx_update_time

复制代码

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

2.3 标签记录法（要求id是有序的）

limit 深分页问题的本质原因就是：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的下降。
其实我们可以采用标签记录法，就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。

1. select id,name,balance from account limit 1000000,10;

复制代码

假设上一次记录到100000，则SQL可以优化为：

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

复制代码

这样的话，后面无论翻多少页，性能都会不错的，因为命中了

1. id

复制代码

索引。但是你，这种方式有局限性：要求id是连续的、并且有序。
在有序的条件下，也可以使用比如创建时间等其他字段来代替主键id，但是前提是这个字段是建立了索引的。
id不是连续，我们可以通过

1. order by

复制代码

让它连续
总之，使用条件过滤的方式来优化 limit 是有诸多限制的，一般还是推荐使用覆盖索引的方式来优化。

2.4 使用between…and…

很多时候，可以将

1. limit

复制代码

查询转换为已知位置的查询，这样MySQL通过范围扫描

1. between...and

复制代码

，就能获得到对应的结果。

1. select id,name,balance from account limit 1000000,10;

复制代码

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

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

复制代码

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/328562q87.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！