mysql深分页问题

10 分钟阅读 • 588 字 + 1150 词
把条件转移到主键索引树
mysql
select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;
我们先来看下这个SQL的执行流程：
  1. 通过 普通二级索引树 idx_update_time，过滤update_time条件，找到满足条件的记录ID。
  2. 通过ID，回到 主键索引树 ，找到满足记录的行，然后取出展示的列（ 回表 ）
  3. 扫描满足条件的100010行，然后扔掉前100000行，返回。
因为以上的SQL，回表了100010次，实际上，我们只需要10条数据，也就是我们只需要10次回表其实就够了。因此，我们可以通过 减少回表次数 来优化。
如果我们把查询条件，转移回到主键索引树，那就可以减少回表次数啦。转移到主键索引树查询的话，查询条件得改为 主键id 了，之前SQL的 update_time 这些条件咋办呢？抽到 子查询 那里嘛~
子查询那里怎么抽的呢？因为二级索引叶子节点是有主键ID的，所以我们直接根据 update_time 来查主键ID即可，同时我们把 limit 100000 的条件，也转移到子查询，完整SQL如下：
mysql
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;
INNER JOIN 延迟关联
延迟关联的优化思路， 跟子查询的优化思路其实是一样的 ：都是把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用了inner join代替子查询。
优化后的SQL如下：
mysql
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;
标签记录法
limit 深分页问题的本质原因就是： 偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的下降 。
其实我们可以采用 标签记录 法，就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。 就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦 。
假设上一次记录到100000，则SQL可以修改为：
mysql
select  id,name,balance FROM account where id > 100000 order by id limit 10;
这样的话，后面无论翻多少页，性能都会不错的，因为命中了 id 索引。但是这种方式 有局限性 ：需要一种类似 连续自增 的字段。
使用between...and...
很多时候，可以将 limit 查询转换为已知位置的查询，这样MySQL通过范围扫描 between...and ，就能获得到对应的结果。
如果知道边界值为100000，100010后，就可以这样优化：
mysql
select  id,name,balance FROM account where id between 100000 and 100010 order by id;

目录