limit 查询变慢的原因以及优化

limit 查询变慢的原因及优化

limit深分页为什么会变慢：如下代码：

select id,name from tbl_user where create_time> '2022-09-30' limit 100000,10;

分析：

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

• limit 100000,10 扫描更多的行数，也意味着回表更多的次数。
  所以优化思路：我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。

标签记录法的sql如下 ： 这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引

select  id,name FROM tbl_user where id > 100000 limit 10;

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

select  acct1.id,acct1.name FROM tbl_user acct1 INNER JOIN (SELECT a.id FROM tbl_user a WHERE a.create_time > '2022-09-30' limit 100000, 10) AS acct2 on acct1.id= acct2.id;

附加：一般情况下一张表的B+tree的高度为1-3层。超过之后就会变慢。3层也就是2000万条数据左右。也就是3次IO就可以查到，大于3层的话就需要考虑分库分表了。