Mysql之LIMIT-OFFSET查询时间过长处理办法

limit-offset查询时间过长原理

近期接触了一个百万级别的表,普通的单表列表查询竟然>10s,如下图,常见limit-offset分页查询一条数据竟然都高达2s,如下面所示,

MySQL [yuetong1]> SELECT * FROM pa_order LIMIT 1 OFFSET 800000;
//查询类容不展示了
1 row in set (2.04 sec)

interesting,仅仅是一个单条数据查询就高达2.04 sec。没办法,只能优化这一块的代码了。

首先,我们要明白的是一个问题就是为什么LIMIT-OFFSET要消耗这么长的时间。
我们先看下上面那条SQL语句的查询过程是怎么样的。

  1. 查询到索引叶子节点的数据
  2. 根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。

如下图所示
20200921-01.png

像上面这样,需要查询800001次索引节点,查询800001次聚簇索引的数据,最后再将结果过滤掉前800000条,取出最后1条。MySQL耗费了大量磁盘I/O在查询聚簇索引的数据上。所以查询时间就会变长。

优化

知道了原因,我们就可以目的性的优化了,最主要的目的就是不让它进行前面800000万次的无理由的I/O消耗就好了。

第一个假如是主键正序或者倒序排序的话就可以使用

MySQL [yuetong1]> SELECT * FROM pa_order WHERE id > 800000 LIMIT 1;
//查询类容不展示了
1 row in set (0.01 sec)

这种直接限定了主键的查询集合是800000往后的,所以只查询一次索引节点

第二个是假如有条件查询和排序各式各样的话推荐使用inner join查询

MySQL [yuetong1]> select * from pa_order as a inner join (select id from pa_order LIMIT 1 OFFSET 800000) as b on a.id=b.id;
//查询类容不展示了
1 row in set (0.12 sec)

这种呢也是inner查询直接把主键查询出来了,主查询直接根据这个id主键查询字段,当然啦也是只查询了一次索引节点哦。

----------end

本文为ctexthuang原创文章,转载请注明来自ctexthuang_blog

tag(s): none
show comments · back · home
Edit with markdown
召唤看板娘